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      NO

7 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.ora

oracle@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=DISK

allocated channel: prmy2
channel prmy2: SID=22 device type=DISK

allocated channel: stby
channel stby: SID=1 device type=DISK

Starting 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 Script

Starting backup at 2012-10-31 10:57:42
Finished backup at 2012-10-31 10:57:43

sql 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 Script

sql 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 started

Total 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=DISK

contents 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 Script

sql 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:57

sql 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 started

Total 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=DISK

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql 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 Script

executing 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:53

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=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