Create Physical Standby Database
Framework
1.Configure the Primary Database
1.1 Enable Forced Logging--设置成强制记录日志模式
1 SQL> alter database force logging; 2 Database altered.
1 SQL> select force_logging,flashback_on from v$database; 2 FOR FLASHBACK_ON 3 --- ------------------ 4 YES NO
1.2 Enable Archiving and Flashback Database--设置成归档模式和闪回(因为后面有fast start-failover提前设置)
1 SQL> shutdown immediate; 2 Database closed. 3 Database dismounted. 4 ORACLE instance shut down.
1 SQL> startup mount; 2 ORACLE instance started. 3 Total System Global Area 184549376 bytes 4 Fixed Size 1218412 bytes 5 Variable Size 75499668 bytes 6 Database Buffers 104857600 bytes 7 Redo Buffers 2973696 bytes 8 Database mounted.
1 SQL> alter database archivelog; 2 Database altered
1 SQL> alter database flashback on; 2 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 0 Next log sequence to archive 1 Current log sequence 1
1 SQL> select force_logging,flashback_on from v$database; 2 FOR FLASHBACK_ON 3 --- ------------------ 4 YES YES
1.3 Create a Passowrd File--创建密码文件
1 [oracle@node1 dbs]$ orapwd file=orapwYFT1 password=oracle entries=5 2 [oracle@node1 u01]$ mkdir arch
1.4 Create Standby Redo Logs--创建备库的redo文件
1 SQL> alter database open; 2 Database altered. 3 SQL>alter database add standby logfile group 4 '/u01/app/oracle/oradata/YFT/standbyredo04.rdo' size 50m; 4 Database altered. 5 SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/YFT/standbyredo05.rdo' size 50m; 6 Database altered. 7 SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/YFT/standbyredo06.rdo' size 50m; 8 Database altered. 9 SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/YFT/standbyredo07.rdo' size 50m; 10 Database altered.
1.5 Configuration Oracle Net Components on Primary Database--修改主库的listener.ora和tnsnames.ora文件
1 /u01/app/oracle/product/10.2.0/db_1/network/admin 2 3 [oracle@node1 admin]$ vi listener.ora 4 SID_LIST_LISTENER = 5 (SID_LIST = 6 (SID_DESC = 7 (SID_NAME = PLSExtProc) 8 (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) 9 (PROGRAM = extproc) 10 ) 11 ( SID_DESC = 12 (GLOBAL_DBNAME = YFT.DG) 13 (SID_NAME = YFT1) 14 (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) 15 ) 16 ) 17 18 LISTENER = 19 (DESCRIPTION_LIST = 20 (DESCRIPTION = 21 (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521)) 22 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) 23 ) 24 ) 25 26 [oracle@node1 admin]$ vi tnsnames.ora 27 YFT1 = 28 (DESCRIPTION = 29 (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521)) 30 (CONNECT_DATA = 31 (SERVER = DEDICATED) 32 (SERVICE_NAME = YFT.DG) 33 ) 34 ) 35 YFT2 = 36 (DESCRIPTION = 37 (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521)) 38 (CONNECT_DATA = 39 (SERVER = DEDICATED) 40 (SERVICE_NAME = YFT.DG) 41 ) 42 )
1.6 Configuration the Primary Database Initialization Parameters--修改主库的pfile文件
1 SQL> create pfile from spfile; 2 [oracle@node1 dbs]$ vi initYFT1.ora 3 # primary database role initialization parameter # 4 service_names='YFT.DG' 5 DB_UNIQUE_NAME=YFTA 6 LOG_ARCHIVE_CONFIG='DG_CONFIG=(YFTA,YFTB)' 7 #LOG_ARCHIVE_CONFIG='DG_CONFIG=(YFTA,YFTB,YFTC)' 8 LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch 9 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=YFTA' 10 LOG_ARCHIVE_DEST_2= 'SERVICE=YFT2 LGWR ASYNC 11 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=YFTB' 12 #LOG_ARCHIVE_DEST_3= 'SERVICE=YFT3 LGWR ASYNC 13 #VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=YFTC' 14 LOG_ARCHIVE_DEST_STATE_1=ENABLE 15 LOG_ARCHIVE_DEST_STATE_2=ENABLE 16 #LOG_ARCHIVE_DEST_STATE_3=ENABLE 17 LOG_ARCHIVE_FORMAT=%t_%s_%r.arc 18 LOG_ARCHIVE_MAX_PROCESSES=4 19 20 #standby database role initialization parameter # 21 FAL_SERVER=YFT2 22 #FAL_SERVER=YFT2,YFT3,YFT1 23 FAL_CLIENT=YFT1 24 STANDBY_FILE_MANAGEMENT=AUTO 25 db_file_name_convert='/u01/app/oracle/oradata/YFT','/u01/app/oracle/oradata/YFT' 26 #db_file_name_convert='/YFT2/','/YFT1/','/YFT3/','/YFT1/' 27 log_file_name_convert='/u01/app/oracle/oradata/YFT','/u01/app/oracle/oradata/YFT' 28 #log_file_name_convert='/YFT2/','/YFT1/','/YFT3/','/YFT1/' 29 (备注：如果主物逻配置的话需要使用#的参数。)
备注：上面的直接粘贴进去就OK，个别参数因环境修改！
1.7 Update /etc/hosts file on all nodes and Verify Network--修改hosts文件
1 [root@node1 ~]# vi /etc/hosts 2 # Do not remove the following line, or various programs 3 # that require network functionality will fail. 4 127.0.0.1 tnode1 localhost.localdomain localhost 5 192.168.2.191 node1 6 192.168.2.193 node2 7 #192.168.2.195 node3
2. Step-by-Step Instructions for Creating a Physical Standby Database
2.1 Create a Control File for the Standby Database--创建备库的控制文件
1 SQL> shutdown immediate; 2 Database closed. 3 Database dismounted. 4 ORACLE instance shut down. 5 SQL> startup mount; 6 ORACLE instance started. 7 Total System Global Area 184549376 bytes 8 Fixed Size 1218412 bytes 9 Variable Size 75499668 bytes 10 Database Buffers 104857600 bytes 11 Redo Buffers 2973696 bytes 12 Database mounted. 13 SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/YFT/standby.ctl'; 14 Database altered. 15 SQL> alter database open; 16 Database altered. 17 SQL> shutdown immediate; 18 Database closed. 19 Database dismounted. 20 ORACLE instance shut down.
2.2 Shutdown the Standby Database and Rename the files
1 SQL> shutdown immediate; 2 Database closed. 3 Database dismounted. 4 ORACLE instance shut down.
1 [oracle@node2 dbs]$ mv spfileYFT2.ora spfileYFT2.ora.bak 2 [oracle@node2 dbs]$ rm orapwYFT2 3 [oracle@node2 u01]$ mkdir arch 4 [oracle@node2 oracle]$ mv admin admin.bak 5 [oracle@node2 oracle]$ mv flash_recovery_area flash_recovery_area.bak 6 [oracle@node2 oracle]$ mv oradata oradata.bak
备注：这里因为之前两边都创建了数据库的，所以要把备库那边的数据库先停止，再把一些文件给重命名一下。
2.3 Copy Files from the Primary System to the Standby System--从主库那边把文件拷贝到备库
1 [oracle@node1 oracle]$ scp -r admin node2:/u01/app/oracle 2 [oracle@node1 oracle]$ scp -r flash_recovery_area node2:/u01/app/oracle 3 [oracle@node1 oracle]$ scp -r oradata node2:/u01/app/oracle 4 [oracle@node1 dbs]$ scp initYFT1.ora orapwYFT1 node2:/u01/app/oracle/product/10.2.0/db_1/dbs
2.4 Prepare an Initialization Parameter File for the Standby Database--修改备库的pfile
1 [oracle@node2 dbs]$ mv orapwYFT1 orapwYFT2 2 [oracle@node2 dbs]$ mv initYFT1.ora initYFT2.ora 3 [oracle@node2 dbs]$ vi initYFT2.ora 4 [oracle@node2 dbs]$vi initYFT2.ora 5 YFT2.__db_cache_size=104857600 6 YFT2.__java_pool_size=4194304 7 YFT2.__large_pool_size=4194304 8 YFT2.__shared_pool_size=67108864 9 YFT2.__streams_pool_size=0 10 *.audit_file_dest='/u01/app/oracle/admin/YFT/adump' 11 *.background_dump_dest='/u01/app/oracle/admin/YFT/bdump' 12 *.compatible='10.2.0.1.0' 13 *.control_files='/u01/app/oracle/oradata/YFT/control01.ctl','/u01/app/oracle/oradata/YFT/control02.ctl','/u01/app/oracle/oradata/YFT/control03.ctl' 14 *.core_dump_dest='/u01/app/oracle/admin/YFT/cdump' 15 *.db_block_size=8192 16 *.db_domain='DG' 17 *.db_file_multiblock_read_count=16 18 *.db_name='YFT' 19 *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' 20 *.db_recovery_file_dest_size=2147483648 21 *.dispatchers='(PROTOCOL=TCP) (SERVICE=YFT2XDB)' 22 *.job_queue_processes=10 23 *.open_cursors=300 24 *.pga_aggregate_target=60817408 25 *.processes=150 26 *.remote_login_passwordfile='EXCLUSIVE' 27 *.sga_target=184549376 28 *.undo_management='AUTO' 29 *.undo_tablespace='UNDOTBS1' 30 *.user_dump_dest='/u01/app/oracle/admin/YFT/udump' 31 32 # primary database role initialization parameter # 33 service_names='YFT.DG' 34 DB_UNIQUE_NAME=YFTB 35 LOG_ARCHIVE_CONFIG='DG_CONFIG=(YFTA,YFTB)' 36 LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch 37 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=YFTB' 38 LOG_ARCHIVE_DEST_2= 'SERVICE=YFT1 LGWR ASYNC 39 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=YFTA' 40 LOG_ARCHIVE_DEST_STATE_1=ENABLE 41 LOG_ARCHIVE_DEST_STATE_2=ENABLE 42 LOG_ARCHIVE_FORMAT=%t_%s_%r.arc 43 LOG_ARCHIVE_MAX_PROCESSES=4 44 45 #standby database role initialization parameter # 46 FAL_SERVER=YFT1 47 FAL_CLIENT=YFT2 48 STANDBY_FILE_MANAGEMENT=AUTO 49 db_file_name_convert='/u01/app/oracle/oradata/YFT','/u01/app/oracle/oradata/YFT' 50 log_file_name_convert='/u01/app/oracle/oradata/YFT','/u01/app/oracle/oradata/YFT'
2.5 Set Up the Environment to Support the Standby Database--修改备库的listener.ora和tnsnames.ora文件
1 /u01/app/oracle/product/10.2.0/db_1/network/admin 2 3 [oracle@node2 admin]$ vi listener.ora 4 SID_LIST_LISTENER = 5 (SID_LIST = 6 (SID_DESC = 7 (SID_NAME = PLSExtProc) 8 (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) 9 (PROGRAM = extproc) 10 ) 11 (SID_DESC = 12 (GLOBAL_DBNAME = YFT.DG) 13 (SID_NAME = YFT2) 14 (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) 15 ) 16 ) 17 18 LISTENER = 19 (DESCRIPTION_LIST = 20 (DESCRIPTION = 21 (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521)) 22 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) 23 ) 24 ) 25 26 [oracle@node2 admin]$ vi tnsnames.ora 27 YFT2 = 28 (DESCRIPTION = 29 (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521)) 30 (CONNECT_DATA = 31 (SERVER = DEDICATED) 32 (SERVICE_NAME = YFT.DG) 33 ) 34 ) 35 YFT1 = 36 (DESCRIPTION = 37 (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521)) 38 (CONNECT_DATA = 39 (SERVER = DEDICATED) 40 (SERVICE_NAME = YFT.DG) 41 ) 42 )
1 [oracle@node2 YFT]$ rm control0* 2 [oracle@node2 YFT]$ mv standby.ctl control01.ctl 3 [oracle@node2 YFT]$ cp control01.ctl control02.ctl 4 [oracle@node2 YFT]$ cp control01.ctl control03.ctl
2.6 Start the Physical Standby Database
1 SQL> conn /as sysdba 2 Connected to an idle instance. 3 SQL> create spfile from pfile; 4 File created. 5 SQL> startup nomount; 6 ORACLE instance started. 7 Total System Global Area 184549376 bytes 8 Fixed Size 1218412 bytes 9 Variable Size 75499668 bytes 10 Database Buffers 104857600 bytes 11 Redo Buffers 2973696 bytes 12 SQL> alter database mount; 13 Database altered.
2.7 Start the Primary Database
1 SQL> create spfile from pfile; 2 File created. 3 SQL> startup 4 ORACLE instance started. 5 Total System Global Area 184549376 bytes 6 Fixed Size 1218412 bytes 7 Variable Size 75499668 bytes 8 Database Buffers 104857600 bytes 9 Redo Buffers 2973696 bytes 10 Database mounted. 11 Database opened.
2.8 Verify the Physical Standby Database Is Performing Properly
1 Step 1 Identify the existing archived redo log files. 2 SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#; 3 Step 2 Force a log switch to archive the current online redo log file. 4 SQL> ALTER SYSTEM SWITCH LOGFILE; 5 Step 3 Verify the new redo data was archived on the standby database. 6 SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#; 7 Step 4 Verify new archvied redo log files were applied. 8 SQL> select sequence#,applied from v$archived_log order by sequence#;
