hyman-海曼工具

一些简单的记录

0%

Oracle 11g2 DataGuard简单部署

发表于 更新于 分类于

环境：
centos 7
redhat 6.4
oracle 11.2.0.4

172.16.2.31 primary
172.16.2.32 standby
172.16.2.19 testdb

修改主库静态监听

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
vim listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
       (GLOBAL_DBNAME=primary)
       (SID_NAME=orcl)
     )
  )
ADR_BASE_LISTENER = /app/oracle11g
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
vim tnsnames.ora

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
  )
STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )

修改主库初始化参数文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
startup mount;
alter database archivelog;
alter database force logging;
alter database open;
alter system set db_unique_name=primary scope=spfile;
alter system set log_archive_config = 'DG_CONFIG=(primary,standby)' scope=spfile;
alter system set log_archive_dest_1 = 'LOCATION=/app/oracle11g/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=primary' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=standby lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=spfile;
alter system set log_archive_dest_state_1 = ENABLE;
alter system set log_archive_dest_state_2 = ENABLE;
alter system set fal_server = standby scope=spfile;
alter system set fal_client = primary scope=spfile;
alter system set log_archive_max_processes=5 scope=spfile;
alter system set standby_file_management=AUTO scope=spfile;
create pfile from spfile;

在主库pfile参数文件和密码文件，并且拷贝到备库相应位置

1
2
cd $ORACLE_HOME/dbs
scp initorcl.ora orapworcl oracle@172.16.2.32:/$ORACLE_HOME/dbs

修改备库静态监听

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
vim listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
       (GLOBAL_DBNAME=standby)
       (SID_NAME=orcl)
     )
  )
ADR_BASE_LISTENER = /app/oracle11g
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
vim tnsnames.ora

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
  )
STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )

修改备库初始化参数文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mkdir -p /app/oracle11g/fast_recovery_area/orcl
mkdir -p /app/oracle11g/admin/orcl/adump
mkdir -p /app/oracle11g/{standbylog,archivelog}
mkdir -p /app/oracle11g/oradata/orcl

startup nomount
create spfile from pfile='/app/oracle11g/product/11.2.0/dbhome_1/dbs/initorcl.ora';
shutdown abort;
alter system set db_unique_name=standby scope=spfile;
alter system set log_archive_config='DG_CONFIG=(primary,standby)' scope=spfile;
alter system set log_archive_dest_1 = 'LOCATION=/app/oracle11g/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=standby' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=primary lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=primary' scope=spfile;
alter system set fal_server=primary scope=spfile;
alter system set fal_client=standby scope=spfile;
shutdown abort;
startup nomount;

测试主库备库是否tnsping正常

1
2
3
4
sqlplus sys/123456@primary as sysdba
sqlplus sys/123456@standby as sysdba 
tnsping standby
tnsping primary

在主库通过Rman Duplicate创建备库

1
2
rman target sys/123456@primary auxiliary sys/123456@standby nocatalog
duplicate target database for standby from active database nofilenamecheck;

在主库和备库添加standby日志

1
2
3
4
alter database add standby logfile group 4 '/app/oracle11g/standbylog/standby_redo04.log' size 50m;
alter database add standby logfile group 5 '/app/oracle11g/standbylog/standby_redo05.log' size 50m;
alter database add standby logfile group 6 '/app/oracle11g/standbylog/standby_redo06.log' size 50m;
alter database add standby logfile group 7 '/app/oracle11g/standbylog/standby_redo07.log' size 50m;

在备库开启实时日志应用

1
SQL>recover managed standby database using current logfile disconnect from session;

取消备库实时日志

1
alter database recover managed standby database cancel;

查看备库状态

1
2
select group#,thread#,sequence#,archived,status from v$standby_log
select open_mode,database_role,db_unique_name from v$database;

查看standby启动进程状态

1
select process,client_process,sequence#,status from v$managed_standby;

查看数据库保护模式

1
select database_role,protection_mode,protection_level,open_mode from v$database;

新增DG

在主库pfile参数文件和密码文件，并且拷贝到备库相应位置

1
2
cd $ORACLE_HOME/dbs
scp initorcl.ora orapworcl oracle@172.16.2.32:/$ORACLE_HOME/dbs

修改备新库初始化参数文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mkdir -p /app/oracle11g/fast_recovery_area/orcl
mkdir -p /app/oracle11g/admin/orcl/adump
mkdir -p /app/oracle11g/{standbylog,archivelog}
mkdir -p /app/oracle11g/oradata/orcl

startup nomount
create spfile from pfile='/app/oracle11g/product/11.2.0/dbhome_1/dbs/initorcl.ora';
alter system set db_unique_name=testdb scope=spfile;
alter system set log_archive_config='DG_CONFIG=(primary,standby,testdb)' scope=spfile;
alter system set log_archive_dest_1 = 'LOCATION=/app/oracle11g/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=testdb' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=primary lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=primary' scope=spfile;
alter system set log_archive_dest_3 = 'SERVICE=testdb lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=testdb' scope=both;
alter system set log_archive_dest_state_3 = ENABLE;
alter system set fal_server=primary,standby scope=spfile;
alter system set fal_client=testdb scope=spfile;
shutdown abort;
startup nomount;

修改新备库静态监听

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
vim listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = testdb)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
       (GLOBAL_DBNAME=testdb)
       (SID_NAME=orcl)
     )
  )
ADR_BASE_LISTENER = /app/oracle11g
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
vim tnsnames.ora

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
  )
STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )
TESTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testdb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
  )

主库修改相关参数

修改主库参数文件

1
2
3
4
alter system set log_archive_config='DG_CONFIG=(primary,standby,testdb)' scope=both;
alter system set log_archive_dest_3 = 'SERVICE=testdb lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=testdb' scope=both;
alter system set log_archive_dest_state_3 = ENABLE;
alter system set fal_server=standby,testdb scope=both;

修改主库tnsnames.ora文件,新增如下

1
2
3
4
5
6
7
8
TESTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testdb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
  )

测试主库、新备库是否tnsping正常

1
2
3
4
sqlplus sys/123456@primary as sysdba
sqlplus sys/123456@testdb as sysdba 
tnsping primary
tnsping testdb

在主库通过Rman Duplicate创建新备库

1
2
rman target sys/123456@primary auxiliary sys/123456@testdb nocatalog
duplicate target database for standby from active database nofilenamecheck;

在新备库开启实时日志应用

1
2
3
alter database open;
recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database cancel;