ORACLE 11G Active DataGuard搭建

主备库环境情况
OS：Linux
DB：Oracle 11.2.0.4
主库SID：test
备库SID：dgtest

主库：
TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )
备库：
DGTEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.18)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dgtest)
    )
  )
1、查看主库参数
SQL>  select name,open_mode,database_role,log_mode,force_logging from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE     FOR
--------- -------------------- ---------------- ------------ ---
TEST     READ WRITE           PRIMARY          ARCHIVELOG   YES

Elapsed: 00:00:00.00

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      test
global_names                         boolean     FALSE
instance_name                        string      test
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      test

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch/
Oldest online log sequence     408
Next log sequence to archive   410
Current log sequence           410

2.主库开启force logging (在参数查看force_logging为NO时才需要）
SQL> alter database force logging;

3、配置主库和备库的TNSNAMES.ORA文件
保证此文件中有如下配置
TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )

DGTEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.18)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dgtest)
    )
  )

4.创建主库密码文件，并传输到备库
[oracle@ora11g dbs]$ scp orapwtest oracle@192.168.1.18:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

并在备库修改此文件的名称
[oracle@ora11gdg dbs]$ mv orapwtest orapwdgtest

5、修改主库参数
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,DGTEST)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch/ LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DGTEST ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgtest';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1='ENABLE';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='DEFER';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
ALTER SYSTEM SET FAL_SERVER=dgtest;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/oradata/dgtest','/u01/oradata/test/' scope=spfile;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/arch','/u01/arch/' scope=spfile;

6、主库创建pfile，传输到备库，并修改备库的参数文件
SQL> create pfile='/u01/dpdir/test_pfile.ora' from spfile;
[oracle@ora11g dpdir]$ scp test_pfile.ora oracle@192.168.1.18:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
再修改备库的参数文件
*.control_files='/u01/oradata/dgtest/control01.ctl','/u01/app/oracle/fast_recovery_area/dgtest/control02.ctl'
*.db_file_name_convert='/u01/oradata/test','/u01/oradata/dgtest/'
*.db_name='test'
*.db_unique_name='dgtest'
*.service_names='dgtest'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.fal_server='TEST'
*.log_archive_config='DG_CONFIG=(DGTEST,TEST)'
*.log_archive_dest_1='LOCATION=/u01/arch/ LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgtest'
*.log_archive_dest_2='SERVICE=DGTEST ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/u01/arch','/u01/arch/'

7、全备主库,并传输备份集到备库的相同位置
run
{
 allocate channel c0 device type disk;
 CONFIGURE CONTROLFILE AUTOBACKUP ON;
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/dpdir/%F';
 backup database format '/u01/dpdir/test_full_db_%d_%T_%u.bak';
 BACKUP ARCHIVELOG ALL FORMAT '/u01/dpdir/test_arc_%s_%p_%t.bak';
 }
 
8. 修改备库监听配置并启动监听
添加静态监听参数(如果不添加静态监听的参数，后面连接会出现 ORA-12528 错误）
[oracle@ora11gdg diag]$  vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dgtest)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = dgtest)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora11gdg)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


ADR_BASE_LISTENER = /u01/app/oracle
启动监听 lsnrctl start


9.启动备库oracle进程 (nomount)
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdgtest.ora';
ORACLE instance started.


Total System Global Area 8017100800 bytes
Fixed Size                  2269072 bytes
Variable Size            1577058416 bytes
Database Buffers         6425673728 bytes
Redo Buffers               12099584 bytes


10.主库duplicate target database for standby
[oracle@ora11g dpdir]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 14 11:27:02 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TEST (DBID=2877367900)
RMAN> connect auxiliary sys/oracle @dgtest
connected to auxiliary database: TEST (not mounted)
RMAN> duplicate target database for standby;
Starting Duplicate Db at 14-JAN-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=407 device type=DISK

contents of Memory Script:
{
   restore clone standby controlfile;
}
executing Memory Script

Starting restore at 14-JAN-16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/dpdir/c-2877367900-20160114-00
channel ORA_AUX_DISK_1: piece handle=/u01/dpdir/c-2877367900-20160114-00 tag=TAG20160114T100448
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/dgtest/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/dgtest/control02.ctl
Finished restore at 14-JAN-16

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 
........................................../此处省略大量日志
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=901108284 file name=/u01/oradata/dgtest/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=901108284 file name=/u01/oradata/dgtest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=901108284 file name=/u01/oradata/dgtest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=901108284 file name=/u01/oradata/dgtest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=901108284 file name=/u01/oradata/dgtest/cti_data01
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=901108284 file name=/u01/oradata/dgtest/transgd_data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=901108284 file name=/u01/oradata/dgtest/transgd_index01.dbf
Finished Duplicate Db at 14-JAN-16??


11.将主库的LOG_ARCHIVE_DEST_STATE_2设置为ENABLE
SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;


12.添加standby redo log
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/oradata/test/redo03.log
/u01/oradata/test/redo02.log
/u01/oradata/test/redo01.log


SQL>SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
no rows selected
#FOR PRIMARY
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/oradata/test/redo04.log' size 128M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/oradata/test/redo05.log' size 128M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/oradata/test/redo06.log' size 128M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/oradata/test/redo07.log' size 128M;
#FOR STANDBY
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/oradata/test/redo03.log
/u01/oradata/test/redo02.log
/u01/oradata/test/redo01.log
#ADD logfile
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/oradata/dgtest/redo04.log' size 128M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/oradata/dgtest/redo05.log' size 128M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/oradata/dgtest/redo06.log' size 128M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/oradata/dgtest/redo07.log' size 128M;


13.备库开启active dataguard
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/205377/viewspace-1976584/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/205377/viewspace-1976584/