Oracle12c-ADG搭建

实验环境：

角色	IP	hostname	CDB name	db_unique_name	pdb name	版本
主	192.168.0.115	Node11	cdb1	cdb_p	pdb1	12.2.0.1.0
备	192.168.0.244	Node12	cdb1	cdb_s	pdb1	12.2.0.1.0

1. /etc/hosts配置

1192.168.0.115 Node11

1192.168.0.244 Node12

2. 主库force logging

sqlplus / as sysdba

SQL> alter database force logging;

SQL> select force_logging from v$database;

3. 主库添加standby redo logfile(连接到CDB$ROOT中执行)

SQL> select member from v$logfile;

/data/app/oracle/oradata/cdb1/redo03.log

/data/app/oracle/oradata/cdb1/redo02.log

/data/app/oracle/oradata/cdb1/redo01.log

Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数

假如只有一个节点，这个节点有三组redolog，
所以Standby redo log组数>=(3+1)*1 == 4
所以至少需要创建4组Standby redo log

添加4(3+1)个standby logfile

alter database add standby logfile group 4 '/data/app/oracle/oradata/standbylog/standby_redo04' size 50m;

alter database add standby logfile group 5 '/data/app/oracle/oradata/standbylog/standby_redo05' size 50m;

alter database add standby logfile group 6 '/data/app/oracle/oradata/standbylog/standby_redo06' size 50m;

alter database add standby logfile group 7 '/data/app/oracle/oradata/standbylog/standby_redo07' size 50m;

SQL> set pagesize 100

SQL> col member for a60

SQL> select group#,member from v$logfile order by group#;

4、主备配置tnsnames.ora

LISTENER_CDB1 =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.115)(PORT = 1521))

CDB_P =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.115)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cdb1)

)

)

CDB_S =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.244)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cdb1)

)

)

5. 主备配置 listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = cdb1)

(SID_NAME = cdb1)

(ORACLE_HOME = /data/app/oracle/product/12.2.0.1.0/db_1)

#(PROGRAM = extproc)

#(ENVS = "EXTPROC_DLLS=ONLY:/data/app/oracle/product/12.2.0.1.0/db_1/bin/oraclr12.dll")

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.244)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

ADR_BASE_LISTENER = /data/app/oracle

重启监听
　　lsnrctl reload

测试
　　tnsping cdb_p
　　tnsping cdb_s

6. 打开归档

SQL> archive log list;

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

7.修改pfile文件

create pfile='/data/app/oracle/product/12.2.0.1.0/db_1/dbs/cdb1pfile.ora' from spfile;

vim cdb1pfile.ora

*.db_name='cdb1'

*.db_unique_name='cdb_p'

*.log_archive_config='dg_config=(cdb_p,cdb_s)'

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=cdb_p'

*.log_archive_dest_2='service=cdb_s valid_for=(online_logfiles,primary_role)  lgwr affirm sync db_unique_name=cdb_s'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='cdb_s'

如果主备库CDB名称不同，还需要加如下参数：

*.DB_FILE_NAME_CONVERT='cdb1','dave'

*.LOG_FILE_NAME_CONVERT='cdb1','dave'

SQL> shutdown immediate

SQL> create spfile from pfile='/data/app/oracle/product/12.2.0.1.0/db_1/dbs/cdb1pfile.ora';

SQL> startup

8. 拷贝密码文件到备库

cd $ORACLE_HOME/dbs

scp orapwcdb1 oracle@Node12:$ORACLE_HOME/dbs

9. 拷贝pfile 到备库并修改

scp cdb1pfile.ora oracle@Node12:$ORACLE_HOME/dbs

vim cdb1pfile.ora

*.db_unique_name='cdb_s'

*.log_archive_config='dg_config=(cdb_p,cdb_s)'

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=cdb_s'

*.log_archive_dest_2='service=cdb_s valid_for=(online_logfiles,primary_role)  lgwr affirm sync db_unique_name=cdb_p'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.standby_file_management='auto'

*.fal_server='cdb_s'

注意修改控制文件的路径，也使用新路径。

注意参数：*.db_recovery_file_dest='/data/app/oracle/flash_recovery_area' ，文件夹若不存在，则手动新建。

根据编辑好的pfile生成spfile

create spfile from pfile='/data/app/oracle/product/12.2.0.1.0/db_1/dbs/cdb1pfile.ora';

启动到 nomount 状态

startup nomount

9.开始进行Active duplicate

主：

rman target sys/Sjtsoft123@cdb_p auxiliary sys/Sjtsoft123@cdb_s nocatalog

RMAN> duplicate target database for standby from active database nofilenamecheck;

duplicate target database for standby from active database nofilenamecheck dorecover;

备：

duplicate 完成之后，备库是mount的。

SQL> select open_mode from v$database;

SQL> show pdbs

SQL> alter database open;

SQL> alter pluggable database pdb1 open;

SQL> select open_mode,log_mode,open_mode ,database_role from v$database;

OPEN_MODE LOG_MODE OPEN_MODE DATABASE_ROLE

-------------------- ------------ -------------------- ----------------

READ ONLY ARCHIVELOG READ ONLY PHYSICAL STANDBY

10. 备库启动real-time apply：

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY

alter database recover managed standby database using current logfile disconnect from session;

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

11. 验证DG

主：

SQL> alter pluggable database pdb1 open;

SQL> alter session set container=pdb1;

SQL> create table cndba as select * from dba_users;

SQL> select count(*) from cndba;

SQL> alter system switch logfile;

备：

SQL> alter session set container=pdb1;

SQL> select count(*) from cndba;

END!

12. 查看日志
主：
　　tail -100f /data/app/oracle/diag/rdbms/cdb_p/cdb1/trace/alert_cdb1.log
从：
　　tail -100f /data/app/oracle/diag/rdbms/cdb_s/cdb1/trace/alert_cdb1.log

巴特西

Oracle12c-ADG搭建

最新文章

热门文章