要求部署物理standby並進行主備切換測試官方文檔：Data Guard Concepts and Administration -> 3 Creating a physical Standby Database 和 7 Role Transitions 注意事項：（1）建議手工配置DG，手工配置和GC配置不可交叉（2）不使用real-time (實時)方式啟動自動恢復，采用性能最大化模式.LGWR ASYNC （3）使用LGWR傳輸日志模式，也可以ARCH傳輸日志模式（4）備庫啟動多個MRP恢復進程，加快日志應用效率（5）進行兩次主備切換
我的環境 OS:Oracle Linux 5.7U database:10.2.0.1
主庫： ip：192.168.1.155 主庫實例名：PROD hostname:ocm1.localdomain
備庫： ip:192.168.1.156 備庫實例名：PRODSTD hostname：ocm2.localdomain
參考官方文檔： Data Guard Concepts and Administration -> 3 Creating a physical Standby Database 和 7 Role Transitions http://docs.oracle.com/cd/B19306_01/server.102/b14239/toc.htm
准備工作：
設置sqlplus命令行提示符樣式：
ocm1
[oracle@ocm1 ~]$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql 最後一行添加以下內容 set sqlprompt "_user'@'_connect_identifier>"
[oracle@ocm1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 25 08:44:19 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SYS@PROD> SYS@PROD>select instance_name,version,status,archiver,database_status from v$instance;
INSTANCE_NAME VERSION STATUS ARCHIVE DATABASE_STATUS ---------------- ----------------- ------------ ------- ----------------- PROD 10.2.0.1.0 OPEN STARTED ACTIVE
檢查數據文件和表空間 SYS@PROD>select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME -------------------------------------------------- ------------------------------ /u01/app/oracle/oradata/PROD/Disk1/system01.dbf SYSTEM /u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf UNDOTBS1 /u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf SYSAUX /u01/app/oracle/oradata/PROD/Disk1/example01.dbf EXAMPLE /u01/app/oracle/oradata/PROD/Disk1/users01.dbf USERS
SYS@PROD>select * from v$tablespace;
TS# NAME INC BIG FLA ENC ---------- ------------------------------ --- --- --- --- 0 SYSTEM YES NO YES 1 UNDOTBS1 YES NO YES 2 SYSAUX YES NO YES 3 TEMPTS1 NO NO YES 7 EXAMPLE YES YES YES 10 USERS YES NO YES
6 rows selected.
ocm2也一起設置：
[oracle@ocm2 ~]$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql set sqlprompt "_user'@'_connect_identifier>"
這樣方便識別那個用戶登錄了那台數據庫。避免混淆。
開始建立物理備庫
1.Preparing the Primary Database for Standby Database Creation
Table 3-1 Preparing the Primary Database for Physical Standby Database Creation
vciBQaHlzaWNhbCBTdGFuZGJ5IERhdGFiYXNlIENyZWF0aW9u" width="100%">
Reference |
Task |
Section 3.1.1
Enable Forced Logging
Section 3.1.2
Create a Password File
Section 3.1.3
Configure a Standby Redo Log
Section 3.1.4
Set Primary Database Initialization Parameters
Section 3.1.5
Enable Archiving
1.1確認歸檔，打開forced logging
SYS@PROD>archive log list Database log mode Archive Mode Automatic archival
Enabled Archive destination
/u01/app/oracle/oradata/PROD/Disk2/arch Oldest online log sequence 7 Next log sequence to archive 11 Current log sequence 11 SYS@PROD>
alter database force logging;
Database altered.
SYS@PROD>select NAME,LOG_MODE,FORCE_LOGGING from v$database;
NAME LOG_MODE FOR --------- ------------ --- PROD
ARCHIVELOG YES
SYS@PROD>
1.2創建密碼文件 之前已經創建
orapwPROD了密碼文件
[oracle@ocm1 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/ [oracle@ocm1 dbs]$ ll total 6740 -rw-rw---- 1 oracle oinstall 1544 Mar 25 08:54 hc_PROD.dat -rw-r----- 1 oracle oinstall 12920 May 3 2001 initdw.ora -rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora -rw-r--r-- 1 oracle oinstall 658 Mar 18 10:09 initPROD.ora -rw-rw---- 1 oracle oinstall 24 Mar 18 10:18 lkPROD -rw-r----- 1 oracle oinstall 5120 Mar 18 09:54
orapwPROD -rw-r--r-- 1 oracle oinstall 318 Mar 17 20:37 pfile.ora -rw-r----- 1 oracle oinstall 6832128 Mar 19 19:51 snapcf_PROD.f -rw-r----- 1 oracle oinstall 3584 Mar 25 08:54 spfilePROD.ora
如果沒有需要重新創建，使用以下語句： [oracle@ocm1 dbs]$
orapwd file=orapwPROD password=oracle
1.3配置Standby Redo Log a.standby logfile是備庫組件，當成為備庫時用來接收來自主庫的redo日志內容; b.添加standby logfile大小需要與logfile 大小保持一致，數量最好n+1用於日志緩沖;
①查看主庫的日志組和日志成員 主庫有5組日志，每組2個成員，文件大小為100M
SYS@PROD>select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 9 104857600 2 YES INACTIVE 270814 25-MAR-14 2 1 10 104857600 2 YES INACTIVE 291251 25-MAR-14 3 1 11 104857600 2 NO CURRENT 311895 25-MAR-14 4 1 7 104857600 2 YES INACTIVE 238268 19-MAR-14 5 1 8 104857600 2 YES INACTIVE 244809 19-MAR-14
SYS@PROD>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------- --- 1 STALE ONLINE /u01/app/oracle/oradata/PROD/Disk1/redo01.log NO 2 STALE ONLINE /u01/app/oracle/oradata/PROD/Disk1/redo02.log NO 3 ONLINE /u01/app/oracle/oradata/PROD/Disk1/redo03.log NO 1 STALE ONLINE /u01/app/oracle/oradata/PROD/Disk2/redo01_1.log NO 2 STALE ONLINE /u01/app/oracle/oradata/PROD/Disk2/redo02_1.log NO 3 ONLINE /u01/app/oracle/oradata/PROD/Disk2/redo03_1.log NO 4 ONLINE /u01/app/oracle/oradata/PROD/Disk1/redo04.log NO 4 ONLINE /u01/app/oracle/oradata/PROD/Disk2/redo04_1.log NO 5 STALE ONLINE /u01/app/oracle/oradata/PROD/Disk1/redo05.log NO 5 STALE ONLINE /u01/app/oracle/oradata/PROD/Disk2/redo05_1.log NO
10 rows selected.
②即添加6組standby logfile
SYS@PROD>alter database add standby logfile group 6 ('/u01/app/oracle/oradata/PROD/Disk1/standby06.log','/u01/app/oracle/oradata/PROD/Disk2/standby06_1.log') size 100m;
Database altered.
SYS@PROD>alter database add standby logfile group 7('/u01/app/oracle/oradata/PROD/Disk1/standby07.log','/u01/app/oracle/oradata/PROD/Disk2/standby07_1.log') size 100m;
Database altered.
SYS@PROD>alter database add standby logfile group 8 ('/u01/app/oracle/oradata/PROD/Disk1/standby08.log','/u01/app/oracle/oradata/PROD/Disk2/standby08_1.log') size 100m;
Database altered.
SYS@PROD>alter database add standby logfile group 9('/u01/app/oracle/oradata/PROD/Disk1/standby09.log','/u01/app/oracle/oradata/PROD/Disk2/standby09_1.log') size 100m;
Database altered.
SYS@PROD>alter database add standby logfile group 10 ('/u01/app/oracle/oradata/PROD/Disk1/standby10.log','/u01/app/oracle/oradata/PROD/Disk2/standby10_1.log') size 100m;
Database altered.
SYS@PROD>alter database add standby logfile group 11 ('/u01/app/oracle/oradata/PROD/Disk1/standby11.log','/u01/app/oracle/oradata/PROD/Disk2/standby11_1.log') size 100m;
Database altered.
SYS@PROD>select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME ---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- 6 UNASSIGNED 0 0 104857600 512 YES UNASSIGNED 0 0 7 UNASSIGNED 0 0 104857600 512 YES UNASSIGNED 0 0 8 UNASSIGNED 0 0 104857600 512 YES UNASSIGNED 0 0 9 UNASSIGNED 0 0 104857600 512 YES UNASSIGNED 0 0 10 UNASSIGNED 0 0 104857600 512 YES UNASSIGNED 0 0 11 UNASSIGNED 0 0 104857600 512 YES UNASSIGNED 0 0
6 rows selected.
SYS@PROD>
1.4 Set Primary Database Initialization Parameters
①創建PROD的pfile，然後修改之
SYS@PROD>show parameter spfile;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/10.2.0 /db_1/dbs/spfilePROD.ora SYS@PROD>create pfile from spfile;
File created.
SYS@PROD>! [oracle@ocm1 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs [oracle@ocm1 dbs]$ ll total 6740 -rw-rw---- 1 oracle oinstall 1544 Mar 25 08:54 hc_PROD.dat -rw-r----- 1 oracle oinstall 12920 May 3 2001 initdw.ora -rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora -rw-r--r-- 1 oracle oinstall 1163 Mar 25 09:34 initPROD.ora -rw-rw---- 1 oracle oinstall 24 Mar 18 10:18 lkPROD -rw-r----- 1 oracle oinstall 5120 Mar 18 09:54 orapwPROD -rw-r--r-- 1 oracle oinstall 318 Mar 17 20:37 pfile.ora -rw-r----- 1 oracle oinstall 6832128 Mar 19 19:51 snapcf_PROD.f -rw-r----- 1 oracle oinstall 3584 Mar 25 08:54 spfilePROD.ora [oracle@ocm1 dbs]$ cat initPROD.ora PROD.__db_cache_size=348127232 PROD.__java_pool_size=4194304 PROD.__large_pool_size=4194304 PROD.__shared_pool_size=163577856 PROD.__streams_pool_size=0 *.BACKGROUND_DUMP_DEST='/u01/app/oracle/admin/PROD/bdump' *.control_files='/u01/app/oracle/oradata/PROD/Disk1/control01.ctl','/u01/app/oracle/oradata/PROD/Disk2/control02.ctl','/u01/app/oracle/oradata/PROD/Disk3/control03.ctl' *.CORE_DUMP_DEST='/u01/app/oracle/admin/PROD/cdump' *.DB_BLOCK_SIZE=8192 *.DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/PROD/Disk1' *.DB_CREATE_ONLINE_LOG_DEST_1='/u01/app/oracle/oradata/PROD/Disk1' *.DB_NAME='PROD' *.dispatchers='(PROTOCOL=TCP)(DISPATCHERS=3)'--------刪除 *.job_queue_processes=15 *.local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=ocm1.localdomain)(PORT=1526))'-----------刪除，
確保PRODSTD動態注冊到1521端口上 *.LOG_ARCHIVE_DEST_1='location=/u01/app/oracle/oradata/PROD/Disk2/arch'-------刪除 *.max_dispatchers=10 *.max_shared_servers=30 *.processes=135 *.sessions=300 *.SGA_TARGET=500M *.shared_server_sessions=200 *.shared_servers=10 *.UNDO_MANAGEMENT='auto' *.undo_retention=5400 *.UNDO_TABLESPACE='undotbs1' *.USER_DUMP_DEST='/u01/app/oracle/admin/PROD/udump' *.utl_file_dir='/home/oracle','/home/oracle/temp','/home/oracle/scripts' [oracle@ocm1 dbs]$
②修改參數，添加以下內容
##parameter for Primary Database DB_NAME=PROD DB_UNIQUE_NAME=PROD LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,PRODSTD)' CONTROL_FILES='/u01/app/oracle/oradata/PROD/Disk1/control01.ctl','/u01/app/oracle/oradata/PROD/Disk2/control02.ctl','/u01/app/oracle/oradata/PROD/Disk3/control03.ctl' LOG_ARCHIVE_DEST_1='location=/u01/app/oracle/oradata/PROD/Disk2/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD' LOG_ARCHIVE_DEST_2='SERVICE=PRODSTD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSTD' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc LOG_ARCHIVE_MAX_PROCESSES=30 ##parameter for Standby Database FAL_SERVER=PRODSTD FAL_CLIENT=PROD DB_FILE_NAME_CONVERT='PRODSTD','PROD' LOG_FILE_NAME_CONVERT='PRODSTD','PROD' STANDBY_FILE_MANAGEMENT=AUTO
[oracle@ocm1 dbs]$ vi initPROD.ora 修改後的pfile： [oracle@ocm1 dbs]$ cat initPROD.ora PROD.__db_cache_size=348127232 PROD.__java_pool_size=4194304 PROD.__large_pool_size=4194304 PROD.__shared_pool_size=163577856 PROD.__streams_pool_size=0 *.BACKGROUND_DUMP_DEST='/u01/app/oracle/admin/PROD/bdump' *.control_files='/u01/app/oracle/oradata/PROD/Disk1/control01.ctl','/u01/app/oracle/oradata/PROD/Disk2/control02.ctl','/u01/app/oracle/oradata/PROD/Disk3/control03.ctl' *.CORE_DUMP_DEST='/u01/app/oracle/admin/PROD/cdump' *.DB_BLOCK_SIZE=8192 *.DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/PROD/Disk1' *.DB_CREATE_ONLINE_LOG_DEST_1='/u01/app/oracle/oradata/PROD/Disk1' *.DB_NAME='PROD' *.job_queue_processes=15 *.max_dispatchers=10 *.max_shared_servers=30 *.processes=135 *.sessions=300 *.SGA_TARGET=500M *.shared_server_sessions=200 *.shared_servers=10 *.UNDO_MANAGEMENT='auto' *.undo_retention=5400 *.UNDO_TABLESPACE='undotbs1' *.USER_DUMP_DEST='/u01/app/oracle/admin/PROD/udump' *.utl_file_dir='/home/oracle','/home/oracle/temp','/home/oracle/scripts'
##parameter for Primary Database DB_NAME=PROD DB_UNIQUE_NAME=PROD LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,PRODSTD)' CONTROL_FILES='/u01/app/oracle/oradata/PROD/Disk1/control01.ctl','/u01/app/oracle/oradata/PROD/Disk2/control02.ctl','/u01/app/oracle/oradata/PROD/Disk3/control03.ctl' LOG_ARCHIVE_DEST_1='location=/u01/app/oracle/oradata/PROD/Disk2/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD' LOG_ARCHIVE_DEST_2='SERVICE=PRODSTD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSTD' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc LOG_ARCHIVE_MAX_PROCESSES=30 ##parameter for Standby Database FAL_SERVER=PRODSTD FAL_CLIENT=PROD DB_FILE_NAME_CONVERT='PRODSTD','PROD' LOG_FILE_NAME_CONVERT='PRODSTD','PROD' STANDBY_FILE_MANAGEMENT=AUTO [oracle@ocm1 dbs]$
③通過pfile重建spfile
[oracle@ocm1 dbs]$ exit exit
SYS@PROD> SYS@PROD>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
SYS@PROD>
create spfile from pfile;
File created.
SYS@PROD>
