Setting up 11g Active Dataguard(ADG)
环境：
主：
IP: 172.18.130.55 DB:11.2.0.1 SID: wsqtest
ORACLE_BASE: /apps/oracle
ORACLE_HOME:/apps/oracle/product/11.2.0.1
备：
IP: 172.18.130.52 DB:11.2.0.1 SID:wsqteststd
ORACLE_BASE:/apps/oracle
ORACLE_HOME:/apps/oracle/product/11.2.0.1
55上安装好oracle软件、建好数据库,52上装好oracle软件，不装库
1、主库55上，开启归档，force logging
SQL> conn /as sysdba
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 20
Next log sequence to archive 22
Current log sequence 22
SQL> alter database force logging;
Database altered.
2、配置监听：
主：
--静态注册
listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /apps/oracle/product/11.2.0.1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME =wsqtest)
(ORACLE_HOME = /apps/oracle/product/11.2.0.1)
(SID_NAME =wsqtest)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = wsqtest)(PORT = 1521))
)
)
--tnsnames.ora:
WSQTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.130.55)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wsqtest)
)
)
WSQTESTSTD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.130.52)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wsqteststd)
)
)
3、配置参数文件：
主库添加以下参数：
*.log_archive_config='dg_config=(wsqtest,wsqteststd)'
*.log_archive_dest_1='location=/apps/oracle/flash_recovery_area/wsqtest/archivelogs valid_for=(all_logfiles,all_roles) db_unique_name=wsqtest'
*.log_archive_dest_2='service=wsqteststd reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=wsqteststd'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='wsqteststd'
*.fal_client='wsqtest'
*.db_unique_name='wsqtest'
4、备库端创建密码文件
$cd $ORACLE_HOME/dbswho
$orapwd file=orapwwsqteststd password=oracle entries=5
5、创建相应的目录
mkdir /apps/oracle/oradata/wsqteststd
mkdir /apps/oracle/oradata/wsqteststd/onlinelogs /apps/oracle/oradata/wsqteststd/datafiles /apps/oracle/oradata/wsqteststd/controlfiles
mkdir /apps/oracle/admin/wsqteststd
mkdir /apps/oracle/admin/wsqteststd/adump /apps/oracle/admin/wsqteststd/bdump /apps/oracle/admin/wsqteststd/cdump /apps/oracle/admin/wsqteststd/udump
mkdir /apps/oracle/flash_recovery_area/wsqteststd
6、修改备库参数文件
从主库参数文件复制过来，修改响应的路径，添加响应的参数：
*.log_archive_config='dg_config=(wsqteststd,wsqtest)'
*.log_archive_dest_1='location=/apps/oracle/flash_recovery_area/wsqteststd/archivelogs valid_for=(all_logfiles,all_roles) db_unique_name=wsqteststd'
*.log_archive_dest_2='service=wsqtest reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=wsqtest'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='wsqtest'
*.fal_client='wsqteststd'
*.log_file_name_convert='/apps/oracle/oradata/wsqtest/onlinelogs/','/apps/oracle/oradata/wsqteststd/onlinelogs/'
*.db_file_name_convert='/apps/oracle/oradata/wsqtest/datafiles/','/apps/oracle/oradata/wsqteststd/datafiles/'
7、备库启动到nomount
[oracle@localhost dbs]$ env | grep ORACLE_SID
ORACLE_SID=wsqteststd
[oracle@localhost dbs]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 4 14:43:21 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 450953216 bytes
Fixed Size 2214256 bytes
Variable Size 142608016 bytes
Database Buffers 301989888 bytes
Redo Buffers 4141056 bytes
SQL>
8、备库开始复制数据库：
[oracle@localhost ~]$ rman target sys/oracle@wsqtest auxiliary sys/oracle@wsqteststd
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 4 14:54:06 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: WSQTEST (DBID=1260868535)
connected to auxiliary database: WSQTEST (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 2013-12-04 14:55:18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/apps/oracle/product/11.2.0.1/dbs/orapwwsqtest' auxiliary format
'/apps/oracle/product/11.2.0.1/dbs/orapwwsqteststd' ;
}
executing Memory Script
Starting backup at 2013-12-04 14:55:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
Finished backup at 2013-12-04 14:55:21
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/apps/oracle/oradata/wsqteststd/controlfiles/control01.ctl';
restore clone controlfile to '/apps/oracle/flash_recovery_area/wsqteststd/controlfiles/control02.ctl' from
'/apps/oracle/oradata/wsqteststd/controlfiles/control01.ctl';
}
executing Memory Script
Starting backup at 2013-12-04 14:55:21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/apps/oracle/product/11.2.0.1/dbs/snapcf_wsqtest.f tag=TAG20131204T145308 RECID=1 STAMP=833295189
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2013-12-04 14:55:22
Starting restore at 2013-12-04 14:55:22
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2013-12-04 14:55:23
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/apps/oracle/oradata/wsqteststd/datafiles/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/apps/oracle/oradata/wsqteststd/datafiles/system01.dbf";
set newname for datafile 2 to
"/apps/oracle/oradata/wsqteststd/datafiles/sysaux01.dbf";
set newname for datafile 3 to
"/apps/oracle/oradata/wsqteststd/datafiles/undotbs01.dbf";
set newname for datafile 4 to
"/apps/oracle/oradata/wsqteststd/datafiles/data01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/apps/oracle/oradata/wsqteststd/datafiles/system01.dbf" datafile
2 auxiliary format
"/apps/oracle/oradata/wsqteststd/datafiles/sysaux01.dbf" datafile
3 auxiliary format
"/apps/oracle/oradata/wsqteststd/datafiles/undotbs01.dbf" datafile
4 auxiliary format
"/apps/oracle/oradata/wsqteststd/datafiles/data01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /apps/oracle/oradata/wsqteststd/datafiles/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 2013-12-04 14:55:29
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/apps/oracle/oradata/wsqtest/datafiles/undotbs01.dbf
output file name=/apps/oracle/oradata/wsqteststd/datafiles/undotbs01.dbf tag=TAG20131204T145316
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/apps/oracle/oradata/wsqtest/datafiles/system01.dbf
output file name=/apps/oracle/oradata/wsqteststd/datafiles/system01.dbf tag=TAG20131204T145316
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/apps/oracle/oradata/wsqtest/datafiles/sysaux01.dbf
output file name=/apps/oracle/oradata/wsqteststd/datafiles/sysaux01.dbf tag=TAG20131204T145316
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/apps/oracle/oradata/wsqtest/datafiles/data01.dbf
output file name=/apps/oracle/oradata/wsqteststd/datafiles/data01.dbf tag=TAG20131204T145316
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2013-12-04 14:55:57
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=833295358 file name=/apps/oracle/oradata/wsqteststd/datafiles/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=833295358 file name=/apps/oracle/oradata/wsqteststd/datafiles/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=833295358 file name=/apps/oracle/oradata/wsqteststd/datafiles/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=833295358 file name=/apps/oracle/oradata/wsqteststd/datafiles/data01.dbf
Finished Duplicate Db at 2013-12-04 14:55:59
RMAN>
9、备库添加standbylog：比主库redolog组数多一组
SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqteststd/onlinelogs/stdbyredo01.log') size 50m;
Database altered.
SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqteststd/onlinelogs/stdbyredo02.log') size 50m;
Database altered.
SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqteststd/onlinelogs/stdbyredo03.log') size 50m;
Database altered.
SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqteststd/onlinelogs/stdbyredo04.log') size 50m;
Database altered.
10、主库添加standbylog：
SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqtest/onlinelogs/stdbyredo01.log') size 50m;
Database altered.
SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqtest/onlinelogs/stdbyredo02.log') size 50m;
Database altered.
SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqtest/onlinelogs/stdbyredo03.log') size 50m;
Database altered.
SQL> alter database add standby logfile ('/apps/oracle/oradata/wsqtest/onlinelogs/stdbyredo04.log') size 50m;
Database altered.
11、开启redo real-time apply
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01531: a database already open by the instance
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY