准备工作:
主机名 | IP地址 | db_name/SID | db_unique_name | tnsnames | 备注 | ||
主库 | OL1 | 192.168.2.10 | LDGG | ZHU | OL10 | ||
备库 | OL2 | 192.168.2.20 | LDGG | BEI | OL20 | 只安装软件 |
【主库】数据库开启归档并强制生成日志;
[oracle@OL1 database]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 11:45:25 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select status from v$instance; STATUS ------------ OPEN SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 4 Current log sequence 6 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database force logging; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 4 Next log sequence to archive 6 Current log sequence 6 SQL> select force_logging from v$database; FOR --- YES SQL>
【主库】创建standby logfile;(添加standby logfile原则,最大日志组数+1)
SQL> select group#,member from v$logfile; GROUP# ---------- MEMBER --------------------------------------------- 3 /u01/app/oracle/oradata/LDGG/redo03.log 2 /u01/app/oracle/oradata/LDGG/redo02.log 1 /u01/app/oracle/oradata/LDGG/redo01.log SQL> select group#,bytes/1024/1024 as m from v$log; GROUP# M ---------- ---------- 1 50 2 50 3 50 SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/LDGG/sredo04.log') size 50m ; Database altered. SQL> alter database add standby logfile group 5('/u01/app/oracle/oradata/LDGG/sredo05.log') size 50m; Database altered. SQL> alter database add standby logfile group 6('/u01/app/oracle/oradata/LDGG/sredo06.log') size 50m; Database altered. SQL> alter database add standby logfile group 7('/u01/app/oracle/oradata/LDGG/sredo07.log') size 50m; Database altered. SQL> select group#,member from v$logfile; GROUP# ---------- MEMBER --------------------------------------------- 3 /u01/app/oracle/oradata/LDGG/redo03.log 2 /u01/app/oracle/oradata/LDGG/redo02.log 1 /u01/app/oracle/oradata/LDGG/redo01.log 4 /u01/app/oracle/oradata/LDGG/sredo04.log 5 /u01/app/oracle/oradata/LDGG/sredo05.log 6 /u01/app/oracle/oradata/LDGG/sredo06.log 7 /u01/app/oracle/oradata/LDGG/sredo07.log 7 rows selected. SQL>
【主库】修改参数;
追加内容可官方文档位置:Master Book List-->3 Creating a Physical Standby Database-->Example 3-1和Example 3-2中的内容,如下;
DB_NAME=chicago DB_UNIQUE_NAME=chicagoLOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)' CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl' LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/chicago/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago' LOG_ARCHIVE_DEST_2= 'SERVICE=boston ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=boston DB_FILE_NAME_CONVERT='boston','chicago' LOG_FILE_NAME_CONVERT='/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/' STANDBY_FILE_MANAGEMENT=AUTO
修改后追加内容:
DB_UNIQUE_NAME=ZHU
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ZHU,BEI)'
LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ZHU'
LOG_ARCHIVE_DEST_2='SERVICE=OL20 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=BEI'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4
FAL_SERVER=OL20
DB_FILE_NAME_CONVERT='LDGG','LDGG'
LOG_FILE_NAME_CONVERT='LDGG','LDGG'
STANDBY_FILE_MANAGEMENT=AUTO
SQL> create pfile from spfile; File created. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@OL1 ~]$ cd $ORACLE_HOME/dbs [oracle@OL1 dbs]$ ls hc_OL1.dat initOL1.ora init.ora lkOL1 orapwOL1 spfileOL1.ora [oracle@OL1 dbs]$ vi initOL1.ora
【主库】通过修改后pfile重新生成spfile并启动数据库;
[oracle@OL1 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 12:29:31 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile; File created. SQL> startup ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes Database mounted. Database opened. SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cell_offloadgroup_name string db_file_name_convert string LDGG, LDGG db_name string LDGG db_unique_name string ZHU global_names boolean FALSE instance_name string LDGG lock_name_space string log_file_name_convert string LDGG, LDGG processor_group_name string service_names string ZHU SQL>
【主库】配置tnsnames;(默认service_name与db_unique_name相同)
[oracle@OL1 dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin [oracle@OL1 admin]$ ls listener.ora samples shrept.lst tnsnames.ora [oracle@OL1 admin]$ vi tnsnames.ora OL10 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ZHU) ) ) OL20 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BEI) ) )
【主库】拷贝相关文件至备库;
[oracle@OL1 admin]$ cd $ORACLE_HOME/dbs [oracle@OL1 dbs]$ ls hc_LDGG.dat initLDGG.ora init.ora lkLDGG lkZHU orapwLDGG spfileLDGG.ora [oracle@OL1 dbs]$ scp orapwLDGG The authenticity of host '192.168.2.20 (192.168.2.20)' can't be established. RSA key fingerprint is c9:55:8e:3e:dd:f5:0b:05:f5:4e:1d:ab:92:9a:de:6d. Are you sure you want to continue connecting (yes/no)? y Please type 'yes' or 'no': yes Warning: Permanently added '192.168.2.20' (RSA) to the list of known hosts. password: orapwLDGG 100% 1536 1.5KB/s 00:00 [oracle@OL1 dbs]$ scp initLDGG.ora GG.ora oracle@192.168.2.20's password: initLDGG.ora 100% 1415 1.4KB/s 00:00 [oracle@OL1 dbs]$ scp /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora oracle@192.168.2.20's password: tnsnames.ora 100% 501 0.5KB/s 00:00 [oracle@OL1 dbs]$
【备库】创建静态监听并启动;
[oracle@OL2 admin]$ ls listener.ora samples shrept.lst tnsnames1707131PM0319.bak tnsnames.ora [oracle@OL2 admin]$ vi listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.20)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER = (sid_list= (sid_desc= (sid_name=LDGG) (oracle_home= /u01/app/oracle/product/11.2.0/db_1) (global_dbname= BEI))) [oracle@OL2 admin]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JUL-2017 13:15:52 Copyright (c) 1991, 2013, Oracle. All rights reserved. Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/OL2/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.20)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.20)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 13-JUL-2017 13:15:52 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/OL2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.20)(PORT=1521))) Services Summary... Service "BEI" has 1 instance(s). Instance "LDGG", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
【备库】修改pfile参数文件;
[oracle@OL2 admin]$ cd $ORACLE_HOME/dbs [oracle@OL2 dbs]$ ls initLDGG.ora init.ora orapwLDGG [oracle@OL2 dbs]$ vi initLDGG.ora
因主库备库DB_NAME和SID相同,修改后如下:
DB_UNIQUE_NAME=BEI
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ZHU,BEI)'
LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BEI'
LOG_ARCHIVE_DEST_2='SERVICE=OL10 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ZHU'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4
FAL_SERVER=OL10
DB_FILE_NAME_CONVERT='LDGG','LDGG'
LOG_FILE_NAME_CONVERT='LDGG','LDGG'
STANDBY_FILE_MANAGEMENT=AUTO
【备库】参照参数文件创建相应目录;
[oracle@OL2 dbs]$ mkdir -p /u01/app/oracle/admin/LDGG/adump [oracle@OL2 dbs]$ mkdir -p /u01/app/oracle/oradata/LDGG [oracle@OL2 dbs]$ mkdir -p /u01/app/oracle/FRA [oracle@OL2 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area [oracle@OL2 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/LDGG
【备库】通过pfile生成spfile启动数据库至nomount状态;
[oracle@OL2 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 13:33:29 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes SQL>
【主库】使用RMAN auxiliary恢复数据库;(主库为open状态,备库为nomount状态,主库监听为READY状态,备库监听为UNKNOWN状态。)
[oracle@OL1 ~]$ rman target sys/oracle@OL10 auxiliary sys/oracle@OL20 Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jul 13 15:03:09 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: LDGG (DBID=4049466052) connected to auxiliary database: LDGG (not mounted) RMAN> duplicate target database for standby from active database dorecover nofilenamecheck; 。。。。。。。。。。。。。。。。过程省略不粘贴了。。。。。。。。。。。。。。。。。 executing Memory Script executing command: SET until clause Starting recover at 13-JUL-17 using channel ORA_AUX_DISK_1 starting media recovery archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/FRA/1_8_94923162 0.arcarchived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/FRA/1_9_94923162 0.arcarchived log file name=/u01/app/oracle/FRA/1_8_949231620.arc thread=1 sequence=8 archived log file name=/u01/app/oracle/FRA/1_9_949231620.arc thread=1 sequence=9 media recovery complete, elapsed time: 00:00:00 Finished recover at 13-JUL-17 Finished Duplicate Db at 13-JUL-17 RMAN>
【备库】应用日志同步数据;
SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY MOUNTED SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database open; Database altered. SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY SQL>
【备库】切换物理备库为Snapshot Standby;
SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY MOUNTED SQL> alter database convert to snapshot standby; Database altered. SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- SNAPSHOT STANDBY MOUNTED SQL> alter database open; Database altered. SQL>
【备库】恢复至物理备库;
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes Database mounted. SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- SNAPSHOT STANDBY MOUNTED SQL> alter database convert to physical standby; Database altered. SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes Database mounted. SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY MOUNTED SQL>