Oracle 11g 線上建立 active dataguard config 測試
以往DataGuard 的建立(8i,9i) 須使用RMAN 或hot backup. 在11g 相當方便.
可以直接用網路對傳. 只要參數檔寫好 即可用以下指令完成
RMAN>
duplicate target database for standby from active database nofilenamecheck;
在Oracle 也有相關的demo
http://www.oracle.com/webfolder/technetwork/tutorials/demos/db/11g /r2/dg/04_dg_rman_create_phys_sby/04_dg_rman_create_phys_sby_viewlet_swf.html
Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:
SQL> ALTER DATABASE FORCE LOGGING;
Oracle recommends that a standby redo log be created on the primary database in a Data Guard configuration so that it is immediately ready to receive redo data following a switchover to the standby role.
SQL>
SELECT GROUP#, BYTES/1024/1024 MB FROM V$LOG;
GROUP# BYTES
---------- ----------
1 52428800
2 52428800
3 52428800
SQL> select member from v$logfile;
MEMBER
----------------------------------------
/oracle/oradata/orcl/redo03.log
/oracle/oradata/orcl/redo02.log
/oracle/oradata/orcl/redo01.log
SQL>
-- performing the steps described in Section 6.2.3 .
ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/oradata/orcl/redo01_stdby.log') SIZE 50M ;
ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/oradata/orcl/redo02_stdby.log') SIZE 50M ;
ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/oradata/orcl/redo03_stdby.log') SIZE 50M ;
ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/oradata/orcl/redo04_stdby.log') SIZE 50M ;
Example 3-2 Primary Database: Standby Role Initialization Parameters
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
Specifying the initialization parameters shown in Example 3-2 sets up the primary database to resolve gaps, converts new datafile and log file path names from a new primary database, and archives the incoming redo data when this database is in the standby role. With the initialization parameters for both the primary and standby roles set as described, none of the parameters need to change after a role transition.
SQL>
alter system set DB_NAME=orcl scope=spfile;
alter system set DB_UNIQUE_NAME=orclprim scope=spfile;
alter system set log_archive_dest_1=
'LOCATION=/oracle/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=orclprim' scope=spfile;
System altered.
SQL>
alter system set log_archive_dest_2=
'SERVICE=STD_DB ASYNC
VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=orclstdby' scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;
alter system set FAL_SERVER='STD_DB' scope=both;
alter system set FAL_CLIENT='PRI_DB' scope=both;
alter system set service_names=orcl scope=spfile;
alter system set log_file_name_convert='/oracle/oradata/orcl/','/oracle/oradata/orcl/' scope=spfile;
==> Use RMAN or cold backup files
use any backup copy of the primary database to create the physical standby database
==>
SQL> create pfile ='/tmp/pfilestdby.ora' from spfile;
File created.
scp pfile to standby machine...
alter system set DB_NAME=orcl scope=spfile;
alter system set DB_UNIQUE_NAME=orclstdby scope=spfile;
alter system set service_names=orcl scope=spfile;
alter system set DB_FILE_NAME_CONVERT='orclprim','orclstdby'
SQL>
alter system set log_archive_dest_1=
'LOCATION=/oracle/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=orclstdby' scope=spfile;
System altered.
SQL>
alter system set log_archive_dest_2=
'SERVICE=PRI_DB ASYNC
VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=orclprim' scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;
alter system set FAL_SERVER='PRI_DB' scope=both;
alter system set FAL_CLIENT='STD_DB' scope=both;
Backup datafiles created in Section 3.2.1
Standby control file created in Section 3.2.2
Initialization parameter file created in Section 3.2.3
Copy the remote login password file from the primary database system to the standby database system
Configure listeners for the primary and standby databases.
$lsnrctl start
Create a server parameter file for the standby database.
SQL> CREATE SPFILE FROM PFILE='initboston.ora';
Step 1 Start the physical standby database.
SQL> STARTUP NOMOUNT pfile='/tmp/pfilestdby.ora';
Step 2 Prepare the Standby Database to Receive Redo Data
performing the steps described in Section 6.2.3 .(此步驟可被忽略,重點為 Step3 須建立相同大小的standby redo log)
Step 3 Create an Online Redo Log on the Standby Database
SQL>
ALTER DATABASE ADD STANDBY LOGFILE group 16 size 512M ;
ALTER DATABASE ADD STANDBY LOGFILE group 17 size 512M ;
ALTER DATABASE ADD STANDBY LOGFILE group 18 size 512M ;
Step 4 Start Redo Apply.On the standby database
SQL>startup;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Actual Tests:
Listener.ora
# LISTENER.ORA Network Configuration File: /u01/db920/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby )(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /oracle/db112 )
(SID_NAME=orcl)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/db112 )
(PROGRAM = extproc)
)
)
|
primary db 參數檔
orcl.__db_cache_size=121634816
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=100663296
orcl.__sga_target=301989888
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=159383552
orcl.__streams_pool_size=4194304
*.audit_file_dest='/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='
*.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='ORCLPRIM'
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='PRY_DB'
*.fal_server='STD_DB'
*.log_archive_dest_1='LOCATION=/oracle/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=orclprim'
*.log_archive_dest_2='SERVICE=STD_DB ASYNC
VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=orclstdby'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='/oracle/oradata/orcl/','/oracle/oradata/orcl/'
*.nls_language='TRADITIONAL CHINESE'
*.nls_territory='
*.open_cursors=300
*.pga_aggregate_target=100663296
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='ORCL'
*.sga_target=301989888
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
|
standby db 參數檔
orcl.__db_cache_size=134217728
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=100663296
orcl.__sga_target=301989888
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=146800640
orcl.__streams_pool_size=4194304
*.audit_file_dest='/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='
*.control_files='/oracle/oradata/orcl/control01.ctl','/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='STD_DB'
*.fal_server='PRI_DB'
*.log_archive_dest_1='LOCATION=/oracle/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=orclstdby'
*.log_archive_dest_2='SERVICE=PRI_DB ASYNC
VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=orclstdby'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.nls_language='TRADITIONAL CHINESE'
*.nls_territory='
*.open_cursors=300
*.pga_aggregate_target=100663296
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=301989888
*.undo_tablespace='UNDOTBS1'
*.DB_FILE_NAME_CONVERT='orclprim','orclstdby'
*.log_file_name_convert='/oracle/oradata/orcl/','/oracle/oradata/orcl/'
*.DB_UNIQUE_NAME=orclstdby
*.SERVICE_NAMES=orcl
*.STANDBY_FILE_MANAGEMENT=AUTO
|
tnsnames.ora
PRI_DB=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=primary)
(PORT=1521)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=ORCL)
)
)
STD_DB=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=standby)
(PORT=1521)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=ORCL)
)
)
|
重點
啟動primary 資料庫至open mode , enable archive log
啟動primary , standby 之監聽器(listener)
啟動standby 至nomount mode.
|
查詢primary DBID
SQL> select dbid from v$database;
DBID
----------
1276492205
SQL>
[oracle@ora11gstdby tmp]$
rman target sys/oracle@PRI_DB nocatalog auxiliary sys/oracle@STD_DB ==>這邊可以在standby DB 或primary DB 發動皆可
Recovery Manager: Release 11.2.0 .1.0 - Production on Thu Jan 12 23:16:15 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1276492205)
using target database control file instead of recovery catalog
RMAN>
duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 20120112 23:18:44
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=55 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/12/2012 23:18:54
RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE
command
RMAN>
重新讓standby 進入nomount mode
RMAN>
duplicate target database for standby from active database nofilenamecheck;
.
.
.
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must
be renamed
ORA-00312: online log 7 thread 0: '/oracle/oradata/orcl/redo04_stdby.log' ==>alter system set log_file_name_convert='/oracle/oradata/orcl/','/oracle/oradata/orcl/' scope=spfile;
RMAN-05535: WARNING: All redo log files were not defined properly.
Finished Duplicate Db at 20120112 23:56:13
RMAN> exit
SQL> select status , host_name from v$instance;
STATUS HOST_NAME
------------ ------------------------------------------------
MOUNTED ora11gstdby
SQL>
shutdown immediate;
startup pfile='/tmp/pfilestdby.ora';
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;
MESSAGE
--------------------------------------------------------------------------------
destination database instance is 'started' not 'mounted'
destination database instance is 'started' not 'mounted'
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARC4: Archival started
ARC5: Archival started
ARC6: Archival started
ARC7: Archival started
ARC8: Archival started
MESSAGE
--------------------------------------------------------------------------------
ARC9: Archival started
ARCa: Archival started
ARCb: Archival started
ARCc: Archival started
ARCd: Archival started
ARCe: Archival started
ARCf: Archival started
ARCg: Archival started
ARCh: Archival started
ARCi: Archival started
ARCj: Archival started
MESSAGE
--------------------------------------------------------------------------------
ARCk: Archival started
ARCl: Archival started
ARCm: Archival started
ARCn: Archival started
ARCo: Archival started
ARCp: Archival started
ARCq: Archival started
ARCr: Archival started
ARCs: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
MESSAGE
--------------------------------------------------------------------------------
ARCt: Archival started
RFS[1]: Assigned to RFS process 4211
RFS[1]: Identified database type as 'physical standby': Client is ARCH pid 4227
ARC3: Beginning to archive thread 1 sequence 27 (1082530-1083661)
ARC3: Completed archiving thread 1 sequence 27 (0-0)
RFS[2]: Assigned to RFS process 4217
RFS[2]: Identified database type as 'physical standby': Client is ARCH pid 4231
Attempt to start background Managed Standby Recovery process
MRP0: Background Managed Standby Recovery process started
Managed Standby Recovery starting Real Time Apply
WARN: ARC4: Terminating ARCH (pid 4134) hung on a disk operation
MESSAGE
--------------------------------------------------------------------------------
Media Recovery Log /oracle/oradata/arch/1_27_748711471.dbf
krsv_proc_kill: Killing 240518168577 processes (Process by index)
Media Recovery Waiting for thread 1 sequence 28
ARC4: Detected ARCH process failure
48 rows selected.
SQL>
@primary
SQL> desc test.tab1;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL DATE
SQL> insert into test.tab1 values (sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test.tab1;
A
-----------------
20120113 00:33:12
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/oradata/arch
Oldest online log sequence 29
Next log sequence to archive 31
Current log sequence 31
@standby
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/oradata/arch
Oldest online log sequence 28
Next log sequence to archive 0
Current log sequence 30
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Selected log 4 for thread 1 sequence 30 dbid 1276492205 branch 748711471
Fri Jan 13 00:39:59 2012
Archived Log entry 2 added for thread 1 sequence 30 ID 0x4c 1584ad dest 1:
Fri Jan 13 00:39:59 2012
Fetching gap sequence in thread 1, gap sequence 28-29
RFS[2]: Selected log 4 for thread 1 sequence 31 dbid 1276492205 branch 748711471
Fri Jan 13 00:40:09 2012
RFS[3]: Assigned to RFS process 4568
RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 4235
RFS[3]: Opened log for thread 1 sequence 28 dbid 1276492205 branch 748711471
Archived Log entry 3 added for thread 1 sequence 28 rlc 748711471 ID 0x4c 1584ad
dest 2:
Fri Jan 13 00:40:10 2012
Media Recovery Log /oracle/oradata/arch/1_28_748711471.dbf
Media Recovery Waiting for thread 1 sequence 29
Fetching gap sequence in thread 1, gap sequence 29-29
Fri Jan 13 00:40:19 2012
RFS[4]: Assigned to RFS process 4573
RFS[4]: Identified database type as 'physical standby': Client is ARCH pid 4239
RFS[4]: Opened log for thread 1 sequence 29 dbid 1276492205 branch 748711471
Archived Log entry 4 added for thread 1 sequence 29 rlc 748711471 ID 0x4c 1584ad
dest 2:
Fri Jan 13 00:40:21 2012
Media Recovery Log /oracle/oradata/arch/1_29_748711471.dbf
Media Recovery Log /oracle/oradata/arch/1_30_748711471.dbf ==>在這邊可以看到 已經apply sequence 30 , ok
Media Recovery Waiting for thread 1 sequence 31 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 31 Reading mem 0
Mem# 0: /oracle/oradata/orcl/redo01_stdby.log
Fri Jan 13 00:40:29 2012
RFS[5]: Assigned to RFS process 4575
RFS[5]: Identified database type as 'physical standby': Client is ARCH pid 4243
Errors in file /oracle/diag/rdbms/orclstdby/orcl/trace/orcl_rfs_4575.trc:
ORA-16401: 存檔日誌遭到遠端檔案伺服器 (RFS) 拒絕
Fri Jan 13 00:40:39 2012
SQL> r
1* select * from test.tab1
A
-----------------
20120113 00:33:12
PS.
1.如果是ASM DB , standby 和primary 的control file 路徑會不相同. 還需要在standby修改pfile 中controlfile 的位置.
2.還會發現ORA-01186 錯誤, standby db 會自動建立
3.記得在Primary/ Standby db 加上 CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY; 否則archive log 在standby db : ASM DISK RECO 會滿, 無法自動清除
4.記得在Primary/ Standby db 加上_log_deletion_policy='ALL'; 第三點才會正常的運作work.
4.記得在Primary/ Standby db 加上_log_deletion_policy='ALL'; 第三點才會正常的運作work.
5.standby redo log 須與primary redo log 大小相同
6.記得設定 log_archive_max_processes=30 不然 alert log 會有 RFS[4]: No standby redo logfiles of size 1048576 blocks available ,
且online redo apply 會很慢, 須等primary switch logfile 才會過去standby.
Ref. http://www.dba-oracle.com/t_alert_log_shows_no_standby.htm
7.如果想設定多個channel 可以用以下方法
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate channel prmy5 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database
nofilenamecheck;
}
---
note for 2.
Fri Jan 13 11:53:08 2012
Incomplete Recovery applied until change 38393848497 time 01/13/2012 11:37:25
Completed standby crash recovery.
Fri Jan 13 11:53:09 2012
SMON: enabling cache recovery
Dictionary check beginning
Fri Jan 13 11:53:10 2012
Errors in file /u01/app/oracle/diag/rdbms/orclstandby/orcl/trace/orcl_dbw0_4487.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '+DATA'
File 201 not verified due to error ORA-01157
Errors in file /u01/app/oracle/diag/rdbms/orclstandby/orcl/trace/orcl_dbw0_4487.trc:
ORA-01186: file 202 failed verification tests
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '+DATA'
File 202 not verified due to error ORA-01157
Errors in file /u01/app/oracle/diag/rdbms/orclstandby/orcl/trace/orcl_dbw0_4487.trc:
ORA-01186: file 203 failed verification tests
ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
ORA-01110: data file 203: '+DATA'
File 203 not verified due to error ORA-01157
Dictionary check complete
Re-creating tempfile +DATA as +DATA/orclstandby/tempfile/temp.306.772458791
Re-creating tempfile +DATA as +DATA/orclstandby/tempfile/tempfile.307.772458797
Fri Jan 13 11:53:19 2012
--
note for 4.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
512MB per standby log
SQL> --SQL> alter database drop standby logfile group 16;
ALTER DATABASE ADD STANDBY LOGFILE group 16 size 512M ;
ALTER DATABASE ADD STANDBY LOGFILE group 17 size 512M ;
ALTER DATABASE ADD STANDBY LOGFILE group 18 size 512M ;
SQL>
shutdown immediate;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
沒有留言:
張貼留言