oracle 12.2.0.1 搭建 active dataguard
os: centos 7.4
database:12.2.0.1 + dbf
本次是以 oracle database 12.2.0.1 + dbf 的形式部署的,后面會記錄 rac + asm 的形式。
任何時候都要說下三種模式:
最大保護:maximize protection
最高性能:maximize performance
最高可用:maximize availability
alter database set standby to maximize protection;
alter database set standby to maximize performance;
atler database set standby to maximize availability;
oradb-node1 192.168.56.101 master
adb-node1 192.168.56.101 master
安裝好了12.2.0.1 的軟件,并創建了數據庫
dataguard 所有節點的 db_name 是相同的,通過db_unique_name來區分彼此。
主庫name相關
db_name: orcl db_unique_name: orclp net service name: tns_orclp修改 db_unique_name
SQL> alter system set db_unique_name='orclp' scope=spfile;System altered.修改 net service name
$ vi listener.ora SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)(SID_NAME = rac01))) $ vi tnsnames.ora tns_orclp =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node1)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))tns_orcls1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node2)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))主庫啟用 force logging
SQL> alter database force logging;Database altered.主庫啟用 archivelog
SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/12.2.0/db_1/dbs/arch Oldest online log sequence 1 Current log sequence 2SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog' scope=spfile; SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open;執行 open pdb,確保處于 read write
SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 RAC01PDB MOUNTED SQL> alter pluggable database RAC01PDB open;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 RAC01PDB READ WRITE NO主庫創建 standby redo logfile
SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------1 1 4 209715200 512 1 NO CURRENT 1572343 2018-07-07 14:47:20 1.8447E+19 02 1 2 209715200 512 1 YES INACTIVE 1429048 2018-07-07 11:45:32 1472300 2018-07-07 14:44:53 03 1 3 209715200 512 1 YES INACTIVE 1472300 2018-07-07 14:44:53 1572343 2018-07-07 14:47:20 0SQL> col MEMBER format a40; SQL> select * from v$logfile;GROUP# STATUS TYPE MEMBER IS_ CON_ID ---------- ------- ------- ---------------------------------------- --- ----------3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO 02 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO 01 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO 0最少添加n+1個standby redo logfile,完全可以比n+1多,如下面
alter database add standby logfile '/u01/app/oracle/oradata/standby_redo01.log' size 200M; alter database add standby logfile '/u01/app/oracle/oradata/standby_redo02.log' size 200M; alter database add standby logfile '/u01/app/oracle/oradata/standby_redo03.log' size 200M; alter database add standby logfile '/u01/app/oracle/oradata/standby_redo04.log' size 200M; alter database add standby logfile '/u01/app/oracle/oradata/standby_redo05.log' size 200M;主庫創建 pfile,并修改
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='12.2.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' *.db_block_size=8192 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=rac01XDB)' *.enable_pluggable_database=true *.local_listener='LISTENER_RAC01' *.log_archive_dest_1='location=/u01/app/oracle/archivelog' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=597m *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=1788m *.undo_tablespace='UNDOTBS1'*.db_name='orcl' *.db_unique_name='orclp' *.log_archive_config='dg_config=(orclp,orcls1)' *.log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orclp' *.log_archive_dest_2='service=tns_orcls1 valid_for=(online_logfiles,primary_role) lgwr affirm sync db_unique_name=orcls1' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.log_archive_format='%t_%s_%r.dbf' *.standby_file_management='auto' *.fal_server='orcls1' *.fal_client='orclp' *.db_file_name_convert='/u01/app/oracle/','/u01/app/oracle/' *.log_file_name_convert='/u01/app/oracle/','/u01/app/oracle/' *.remote_login_passwordfile='EXCLUSIVE'oradb-node2 192.168.56.102 physical standby
安裝好了12.2.0.1 的軟件,不創建數據庫,用duplicate命令從master拉過來
備庫name相關
db_name: orcl db_unique_name: orcls1 net service name: tns_orcls1修改 net service name
$ vi listener.ora SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)(SID_NAME = rac01))) $ vi tnsnames.ora tns_orclp =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node1)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))tns_orcls1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node2)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))上面的 listener.ora、tnsnames.ora 需要和master 保持一致。
備庫目錄相關
參考主庫創建必要的目錄
mkdir -p $ORACLE_BASE/admin/orcl/adump mkdir -p $ORACLE_BASE/archivelog mkdir -p $ORACLE_BASE/audit mkdir -p $ORACLE_BASE/oradata/orcl mkdir -p $ORACLE_BASE/oradata/pdbseed mkdir -p $ORACLE_BASE/oradata/rac01pdb備庫拷貝master 的password file
$scp ./orapwrac01 oracle@192.168.56.102:/u01/app/oracle/product/12.2.0/db_1/dbs備庫spfile內容如下
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='12.2.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' *.db_block_size=8192 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=rac01XDB)' *.enable_pluggable_database=true *.local_listener='LISTENER_RAC01' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=597m *.processes=300 *.sga_target=1788m *.undo_tablespace='UNDOTBS1'*.db_name='orcl' *.db_unique_name='orcls1' *.log_archive_config='dg_config=(orclp,orcls1)' *.log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcls1' *.log_archive_dest_2='service=tns_orclp valid_for=(online_logfiles,primary_role) lgwr affirm sync db_unique_name=orclp' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.log_archive_format='%t_%s_%r.dbf' *.standby_file_management='auto' *.fal_server='orclp' *.fal_client='orcls1' *.db_file_name_convert='/u01/app/oracle/','/u01/app/oracle/' *.log_file_name_convert='/u01/app/oracle/','/u01/app/oracle/' *.remote_login_passwordfile='EXCLUSIVE'備庫啟動到nomount狀態
SQL> startup nomount; ORACLE instance started.Total System Global Area 1879048192 bytes Fixed Size 8794072 bytes Variable Size 553648168 bytes Database Buffers 1308622848 bytes Redo Buffers 7983104 bytes備庫開始active duplicate
$ rman target sys/oracleoracle@tns_orclp auxiliary sys/oracleoracle@tns_orcls1RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;備庫打開
SQL> select open_mode from v$database;OPEN_MODE ------------------------------------------------------------ MOUNTEDSQL> alter database open;Database altered.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此時查看pdb
SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 RAC01PDB MOUNTED SQL> alter pluggable database RAC01PDB open;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 RAC01PDB READ ONLY NO備庫開始real-time apply
SQL> alter database recover managed standby database using current logfile disconnect from session;驗證
備庫查看日志
$ tail -f -n 1000 /u01/app/oracle/diag/rdbms/orcls1/rac01/trace/alert_rac01.log主庫查看
selectdbms_flashback.get_system_change_number() as master_current_scn,ad.APPLIED_SCN,ad.* from v$archive_dest ad where 1=1 ;select * from v$archive_dest_status ;select * from v$archive_gap ;select * from v$archive_processes ;整體來說和11g手動搭建dataguard基本類似,需要注意的是記得操作pdb
參考:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/high-availability.html
下面是 duplicate 時輸出,自己分析,博友可以忽略
$rman target sys/oracleoracle@tns_orclp auxiliary sys/oracleoracle@tns_orcls1Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jul 7 17:21:13 2018Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1508635741) connected to auxiliary database: ORCL (not mounted)RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;Starting Duplicate Db at 2018-07-07 17:21:30 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=36 device type=DISK current log archivedcontents of Memory Script: {backup as copy reusetargetfile '/u01/app/oracle/product/12.2.0/db_1/dbs/orapwrac01' auxiliary format '/u01/app/oracle/product/12.2.0/db_1/dbs/orapwrac01' ; } executing Memory ScriptStarting backup at 2018-07-07 17:21:31 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=69 device type=DISK Finished backup at 2018-07-07 17:21:32contents of Memory Script: {restore clone from service 'tns_orclp' standby controlfile; } executing Memory ScriptStarting restore at 2018-07-07 17:21:32 using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/orcl/control01.ctl output file name=/u01/app/oracle/oradata/orcl/control02.ctl Finished restore at 2018-07-07 17:21:34contents of Memory Script: {sql clone 'alter database mount standby database'; } executing Memory Scriptsql statement: alter database mount standby databasecontents of Memory Script: {set newname for tempfile 1 to "/u01/app/oracle/oradata/orcl/temp01.dbf";set newname for tempfile 2 to "/u01/app/oracle/oradata/orcl/pdbseed/temp012018-07-07_11-44-06-982-AM.dbf";set newname for tempfile 3 to "/u01/app/oracle/oradata/orcl/rac01pdb/temp01.dbf";switch clone tempfile all;set newname for datafile 1 to "/u01/app/oracle/oradata/orcl/system01.dbf";set newname for datafile 3 to "/u01/app/oracle/oradata/orcl/sysaux01.dbf";set newname for datafile 4 to "/u01/app/oracle/oradata/orcl/undotbs01.dbf";set newname for datafile 5 to "/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf";set newname for datafile 6 to "/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf";set newname for datafile 7 to "/u01/app/oracle/oradata/orcl/users01.dbf";set newname for datafile 8 to "/u01/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf";set newname for datafile 9 to "/u01/app/oracle/oradata/orcl/rac01pdb/system01.dbf";set newname for datafile 10 to "/u01/app/oracle/oradata/orcl/rac01pdb/sysaux01.dbf";set newname for datafile 11 to "/u01/app/oracle/oradata/orcl/rac01pdb/undotbs01.dbf";set newname for datafile 12 to "/u01/app/oracle/oradata/orcl/rac01pdb/users01.dbf";restorefrom nonsparse from service 'tns_orclp' clone database;sql 'alter system archive log current'; } executing Memory Scriptexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMErenamed tempfile 1 to /u01/app/oracle/oradata/orcl/temp01.dbf in control file renamed tempfile 2 to /u01/app/oracle/oradata/orcl/pdbseed/temp012018-07-07_11-44-06-982-AM.dbf in control file renamed tempfile 3 to /u01/app/oracle/oradata/orcl/rac01pdb/temp01.dbf in control fileexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 2018-07-07 17:21:39 using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/undotbs01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/pdbseed/system01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orcl/users01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/orcl/rac01pdb/system01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/orcl/rac01pdb/sysaux01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/orcl/rac01pdb/undotbs01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/orcl/rac01pdb/users01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 2018-07-07 17:22:29sql statement: alter system archive log current current log archivedcontents of Memory Script: {restore clone force from service 'tns_orclp' archivelog from scn 1585893;switch clone datafile all; } executing Memory ScriptStarting restore at 2018-07-07 17:22:30 using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=6 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service tns_orclp channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=7 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 2018-07-07 17:22:32datafile 1 switched to datafile copy input datafile copy RECID=4 STAMP=980875352 file name=/u01/app/oracle/oradata/orcl/system01.dbf datafile 3 switched to datafile copy input datafile copy RECID=5 STAMP=980875352 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=6 STAMP=980875352 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf datafile 5 switched to datafile copy input datafile copy RECID=7 STAMP=980875352 file name=/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf datafile 6 switched to datafile copy input datafile copy RECID=8 STAMP=980875352 file name=/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf datafile 7 switched to datafile copy input datafile copy RECID=9 STAMP=980875352 file name=/u01/app/oracle/oradata/orcl/users01.dbf datafile 8 switched to datafile copy input datafile copy RECID=10 STAMP=980875352 file name=/u01/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf datafile 9 switched to datafile copy input datafile copy RECID=11 STAMP=980875352 file name=/u01/app/oracle/oradata/orcl/rac01pdb/system01.dbf datafile 10 switched to datafile copy input datafile copy RECID=12 STAMP=980875352 file name=/u01/app/oracle/oradata/orcl/rac01pdb/sysaux01.dbf datafile 11 switched to datafile copy input datafile copy RECID=13 STAMP=980875353 file name=/u01/app/oracle/oradata/orcl/rac01pdb/undotbs01.dbf datafile 12 switched to datafile copy input datafile copy RECID=14 STAMP=980875353 file name=/u01/app/oracle/oradata/orcl/rac01pdb/users01.dbfcontents of Memory Script: {set until scn 1586097;recoverstandbyclone databasedelete archivelog; } executing Memory Scriptexecuting command: SET until clauseStarting recover at 2018-07-07 17:22:33 using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/archivelog/1_6_980855007.dbf archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/archivelog/1_7_980855007.dbf archived log file name=/u01/app/oracle/archivelog/1_6_980855007.dbf thread=1 sequence=6 archived log file name=/u01/app/oracle/archivelog/1_7_980855007.dbf thread=1 sequence=7 media recovery complete, elapsed time: 00:00:00 Finished recover at 2018-07-07 17:22:34 Finished Duplicate Db at 2018-07-07 17:22:43轉載于:https://www.cnblogs.com/ctypyb2002/p/9792947.html
總結
以上是生活随笔為你收集整理的oracle 12.2.0.1 搭建 active dataguard的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: js表单验证 - 数字
- 下一篇: 逆向篇 导读