oracle 11g dataguard 创建过程
主备库均采用ASM作为存储，主备库的环境如下。
| primary | dataguard | |
| hostname | solora11g | solora11sty |
| oracle version | 11.2.0.1 | 11.2.0.1 |
| db_name | ora11db | ora11sty |
| db_unique_name | ora11db | ora11sty |
| SID | ora11db | ora11sty |
| datafile and logfile存储位置 | +DATA | +DATA |
| flashback存储位置 | +FLASH | +FLASH |
| archivelog存储位置 | +ARCH | +ARCH |
| Grid软件安装用户 | grid | grid |
| Oracle软件安装用户 | oracle | oracle |
1. 主库准备工作
1.1开启force logging
SQL> alter database force logging;
Database altered.
1.2增加standby logfile
SQL> alter database add standby logfile '+data' size 50M;
Database altered.
SQL> alter database add standby logfile '+data' size 50M;
Database altered.
SQL>
SQL> alter database add standby logfile '+data' size 50M;Database altered.
SQL> alter database add standby logfile '+data' size 50M;
Database altered.
SQL> col member for a50
SQL> set lines 120
SQL> select * from v$logfile;GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE +DATA/ora11db/onlinelog/group_1.260.795013107 NO
2 ONLINE +DATA/ora11db/onlinelog/group_2.262.795013107 NO
3 ONLINE +DATA/ora11db/onlinelog/group_3.259.795013109 NO
4 STANDBY +DATA/ora11db/onlinelog/group_4.267.798109593 NO
5 STANDBY +DATA/ora11db/onlinelog/group_5.268.798109603 NO
6 STANDBY +DATA/ora11db/onlinelog/group_6.269.798109623 NO
7 STANDBY +DATA/ora11db/onlinelog/group_7.270.798109631 NO7 rows selected.
1.3修改初始化参数
SQL> alter system set log_archive_config='dg_config=(ora11db,ora11sty)';
System altered.
SQL> alter system set log_archive_dest_2='service=ora11sty lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=ora11sty';
System altered.
SQL> alter system set standby_file_management=auto;
System altered.
1.4修改tnsname.ora文件
在文件加入主备库的连接串，tnsnames在oracle用户的$ORACLE_HOME/network/admin下或$TNS_ADMIN目录下
ORA11DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.22.130)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11db)
)
)ORA11STY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.22.144)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11sty)
)
)
1.5将tnsnames.ora文件拷贝到dataguard主机
oracle@solora11g:/app/oracle/product/11.2.0/dbhome_1/network/admin $> scp tnsnames.ora oracle@10.0.22.144:/app/oracle/product/11.2.0/dbhome_1/network/admin
Password:
tnsnames.ora 100% |************************************************************| 515 00:00
2.备库准备工作
2.1修改listener.ora文件，增加对备库的静态注册
注意，备库必须采用静态注册，否则后面rman将连接不上备库。
在listener.ora文件中增加以下内容，listener.ora位于grid用户的$ORACLE_HOME/network/admin目录下
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora11sty)
(ORACLE_HOME = /app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = ora11sty)
)
)
2.2拷贝主库的密码文件到备库
oracle@solora11gsty:~ $> cd $ORACLE_HOME/dbs
oracle@solora11gsty:/app/oracle/product/11.2.0/dbhome_1/dbs $> scp 10.0.22.130:/app/oracle/product/11.2.0/dbhome_1/dbs/orapwora11db orapwora11sty
2.3创建必要的目录
oracle@solora11gsty:~ $> mkdir -p /app/oracle/admin/ora11sty/adump
oracle@solora11gsty:~ $> mkdir -p /app/oracle/admin/ora11sty/dpdump
oracle@solora11gsty:~ $> mkdir -p /app/oracle/admin/ora11sty/pfile
oracle@solora11gsty:~ $> mkdir -p /app/oracle/diag/rdbms/ora11sty/ora11sty/cdump
oracle@solora11gsty:~ $> mkdir -p /app/oracle/diag/rdbms/ora11sty/ora11sty/alert
oracle@solora11gsty:~ $> mkdir -p /app/oracle/diag/rdbms/ora11sty/ora11sty/incident
oracle@solora11gsty:~ $> mkdir -p /app/oracle/diag/rdbms/ora11sty/ora11sty/incpkg
oracle@solora11gsty:~ $> mkdir -p /app/oracle/diag/rdbms/ora11sty/ora11sty/hmn
oracle@solora11gsty:~ $> mkdir -p /app/oracle/diag/rdbms/ora11sty/ora11sty/hm
oracle@solora11gsty:~ $> mkdir -p /app/oracle/diag/rdbms/ora11sty/ora11sty/trace
2.4修改oracle用户的$ORACLE_HOME/bin/oracle权限
如果没有使用dbca创建过数据库，此文件的属主一般是oracle:oinstall，必须使用setasmgidwrap命令改为oracle:asmadmin，否则oracle将没有访问ASM磁盘组的权限。
root@solora11gsty # ls -ltr /app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 256511080 Oct 30 16:33 /app/oracle/product/11.2.0/dbhome_1/bin/oracle
root@solora11gsty # su - grid
Oracle Corporation SunOS 5.10 Generic Patch January 2005
grid@solora11gsty:~ $> $ORACLE_HOME/bin/setasmgidwrap o=/app/oracle/product/11.2.0/dbhome_1/bin/oracle
grid@solora11gsty:~ $> exit
logout
root@solora11gsty # ls -ltr /app/oracle/product/11.2.0/dbhome_1/bin/oracle
-r-sr-s--x 1 oracle asmadmin 256511080 Oct 30 16:33 /app/oracle/product/11.2.0/dbhome_1/bin/oracle
2.5创建初始化参数文件，将数据库启动到nomount状态
oracle@solora11gsty:~ $> cd $ORACLE_HOME/dbs
oracle@solora11gsty:/app/oracle/product/11.2.0/dbhome_1/dbs $> echo DB_NAME=ora11sty > initora11sty.oraoracle@solora11gsty:~ $> export ORACLE_SID=ora11sty
oracle@solora11gsty:~ $> sqlplus "/as sysdba"SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 31 10:30:29 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/app/oracle/product/11.2.0/dbhome_1/dbs/initora11sty.ora
ORACLE instance started.Total System Global Area 217157632 bytes
Fixed Size 2209600 bytes
Variable Size 159385792 bytes
Database Buffers 50331648 bytes
Redo Buffers 5230592 bytes
3.用rman创建dataguard
在备库上执行rman命令连接target和auxiliary
oracle@solora11gsty:~ $> rman target sys/BIIpass01@ora11db auxiliary sys/BIIpass01@ora11sty
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 31 10:52:43 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11DB (DBID=4244942958, not open)
connected to auxiliary database: ORA11STY (not mounted)
然后执行以上rman脚本
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'ora11db','ora11sty'
set 'db_unique_name'='ora11sty'
set 'db_file_name_convert'='+data/ora11db','+data/ora11sty'
set log_file_name_convert='+data/ora11db','+data/ora11sty'
set control_files='+data'
set log_archive_max_processes='5'
set fal_client='ora11sty'
set fal_server='ora11db'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(ora11db,ora11sty)'
set log_archive_dest_2='service=ora11db LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=ora11db';
}
以下是执行结果：
RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate auxiliary channel stby type disk;
5> duplicate target database for standby from active database
6> spfile
7> parameter_value_convert 'ora11db','ora11sty'
8> set 'db_unique_name'='ora11sty'
9> set 'db_file_name_convert'='+data/ora11db','+data/ora11sty'
10> set log_file_name_convert='+data/ora11db','+data/ora11sty'
11> set control_files='+data'
12> set log_archive_max_processes='5'
13> set fal_client='ora11sty'
14> set fal_server='ora11db'
15> set standby_file_management='AUTO'
16> set log_archive_config='dg_config=(ora11db,ora11sty)'
17> set log_archive_dest_2='service=ora11db LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=ora11db';
18> }
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=144 device type=DISKallocated channel: prmy2
channel prmy2: SID=22 device type=DISKallocated channel: stby
channel stby: SID=1 device type=DISKStarting Duplicate Db at 2012-10-31 10:57:42
contents of Memory Script:
{
backup as copy reuse
targetfile '/app/oracle/product/11.2.0/dbhome_1/dbs/orapwora11db' auxiliary format
'/app/oracle/product/11.2.0/dbhome_1/dbs/orapwora11sty' targetfile
'+DATA/ora11db/spfileora11db.ora' auxiliary format
'/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11sty.ora' ;
sql clone "alter system set spfile= ''/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11sty.ora''";
}
executing Memory ScriptStarting backup at 2012-10-31 10:57:42
Finished backup at 2012-10-31 10:57:43sql statement: alter system set spfile= ''/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11sty.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/app/oracle/admin/ora11sty/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=ora11styXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''ora11sty'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''+data/ora11db'', ''+data/ora11sty'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''+data/ora11db'', ''+data/ora11sty'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''+data'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
5 comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''ora11sty'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''ora11db'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(ora11db,ora11sty)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=ora11db LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=ora11db'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Scriptsql statement: alter system set audit_file_dest = ''/app/oracle/admin/ora11sty/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=ora11styXDB)'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''ora11sty'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''+data/ora11db'', ''+data/ora11sty'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''+data/ora11db'', ''+data/ora11sty'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''+data'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''ora11sty'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''ora11db'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(ora11db,ora11sty)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=ora11db LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=ora11db'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance startedTotal System Global Area 1052233728 bytes
Fixed Size 2217704 bytes
Variable Size 608176408 bytes
Database Buffers 436207616 bytes
Redo Buffers 5632000 bytes
allocated channel: stby
channel stby: SID=23 device type=DISKcontents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/ora11sty/controlfile/current.256.798116275'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DATA/ora11sty/controlfile/current.257.798116275';
sql clone "alter system set control_files =
''+DATA/ora11sty/controlfile/current.257.798116275'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Scriptsql statement: alter system set control_files = ''+DATA/ora11sty/controlfile/current.256.798116275'' comment= ''Set by RMAN'' scope=spfile
Starting backup at 2012-10-31 10:57:54
channel prmy1: starting datafile copy
copying standby control file
output file name=/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ora11db.f tag=TAG20121031T105830 RECID=1 STAMP=798116311
channel prmy1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2012-10-31 10:57:57sql statement: alter system set control_files = ''+DATA/ora11sty/controlfile/current.257.798116275'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance startedTotal System Global Area 1052233728 bytes
Fixed Size 2217704 bytes
Variable Size 608176408 bytes
Database Buffers 436207616 bytes
Redo Buffers 5632000 bytes
allocated channel: stby
channel stby: SID=23 device type=DISKcontents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Scriptsql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.contents of Memory Script:
{
set newname for tempfile 1 to
"+data";
switch clone tempfile all;
set newname for datafile 1 to
"+data";
set newname for datafile 2 to
"+data";
set newname for datafile 3 to
"+data";
set newname for datafile 4 to
"+data";
set newname for datafile 5 to
"+data";
backup as copy reuse
datafile 1 auxiliary format
"+data" datafile
2 auxiliary format
"+data" datafile
3 auxiliary format
"+data" datafile
4 auxiliary format
"+data" datafile
5 auxiliary format
"+data" ;
sql 'alter system archive log current';
}
executing Memory Scriptexecuting command: SET NEWNAME
renamed tempfile 1 to +data in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 2012-10-31 10:58:13
channel prmy1: starting datafile copy
input datafile file number=00001 name=+DATA/ora11db/datafile/system.263.795013111
channel prmy2: starting datafile copy
input datafile file number=00002 name=+DATA/ora11db/datafile/sysaux.265.795013121
output file name=+DATA/ora11sty/datafile/sysaux.259.798116295 tag=TAG20121031T105850
channel prmy2: datafile copy complete, elapsed time: 00:01:55
channel prmy2: starting datafile copy
input datafile file number=00003 name=+DATA/ora11db/datafile/undotbs1.261.795013129
output file name=+DATA/ora11sty/datafile/system.258.798116295 tag=TAG20121031T105850
channel prmy1: datafile copy complete, elapsed time: 00:02:11
channel prmy1: starting datafile copy
input datafile file number=00005 name=+DATA/ora11db/datafile/test.266.796407207
output file name=+DATA/ora11sty/datafile/test.261.798116425 tag=TAG20121031T105850
channel prmy1: datafile copy complete, elapsed time: 00:00:03
channel prmy1: starting datafile copy
input datafile file number=00004 name=+DATA/ora11db/datafile/users.257.795013139
output file name=+DATA/ora11sty/datafile/users.262.798116429 tag=TAG20121031T105850
channel prmy1: datafile copy complete, elapsed time: 00:00:03
output file name=+DATA/ora11sty/datafile/undotbs1.260.798116409 tag=TAG20121031T105850
channel prmy2: datafile copy complete, elapsed time: 00:00:44
Finished backup at 2012-10-31 11:00:53sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Scriptdatafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=798116454 file name=+DATA/ora11sty/datafile/system.258.798116295
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=798116454 file name=+DATA/ora11sty/datafile/sysaux.259.798116295
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=798116454 file name=+DATA/ora11sty/datafile/undotbs1.260.798116409
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=798116454 file name=+DATA/ora11sty/datafile/users.262.798116429
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=798116454 file name=+DATA/ora11sty/datafile/test.261.798116425
Finished Duplicate Db at 2012-10-31 11:00:59
released channel: prmy1
released channel: prmy2
released channel: stby
RMAN>exit
4.DataGuard创建完成后工作
4.1将spfile迁移到ASM上
从内存中创建spfile
SQL> create spfile='+data/ora11sty/spfileora11sty.ora' from memory;
File created.
修改ORACLE_HOME/dbs/initora11sty.ora文件为以下内容
oracle@solora11gsty:~ $> more $ORACLE_HOME/dbs/initora11sty.ora
spfile='+data/ora11sty/spfileora11sty.ora'关闭数据库
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.删除$ORACLE_HOME/dbs下的spfileora11sty.ora文件
oracle@solora11gsty:~ $> rm -rf $ORACLE_HOME/dbs/spfileora11sty.ora
重启数据库到mount状态
SQL> startup mount;
ORACLE instance started.Total System Global Area 1052233728 bytes
Fixed Size 2217704 bytes
Variable Size 612370712 bytes
Database Buffers 432013312 bytes
Redo Buffers 5632000 bytes
Database mounted.启动自动恢复进程
SQL> alter database recover managed standby database disconnect from session;
Database altered.
4.2检查dataguard
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY
