搭建11g data guard(duplicate from active database方式)

1)   安裝備庫伺服器oracle 軟體 略

2)   配置主庫伺服器和備庫伺服器網路

[root@db11g01 ~]# more /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1       localhost.localdomain localhost

::1             localhost6.localdomain6 localhost6

172.16.94.33 db11g01.oracle.com db11g01

172.16.94.34 db11g02.oracle.com db11g02

 

[root@db11g02 ~]# more /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1       localhost.localdomain localhost

::1             localhost6.localdomain6 localhost6

172.16.94.34 db11g02.oracle.com db11g02

172.16.94.33 db11g01.oracle.com db11g01

3)   主庫Enable Forced Logging

SQL>  ALTER DATABASE FORCE LOGGING;

 

Database altered.

4)   主庫設定歸檔

mkdir -p /u01/app/oracle/archivelog

[oracle@db11g01 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 8 06:49:32 2013

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> show parameter dest_1

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_create_online_log_dest_1          string

log_archive_dest_1                   string

log_archive_dest_10                  string

log_archive_dest_11                  string

log_archive_dest_12                  string

log_archive_dest_13                  string

log_archive_dest_14                  string

log_archive_dest_15                  string

log_archive_dest_16                  string

log_archive_dest_17                  string

log_archive_dest_18                  string

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_19                  string

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog';

 

System altered.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 1707446272 bytes

Fixed Size                  1345408 bytes

Variable Size            1291847808 bytes

Database Buffers          402653184 bytes

Redo Buffers               11599872 bytes

Database mounted.

SQL> alter database archivelog;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/archivelog

Oldest online log sequence     154

Next log sequence to archive   156

Current log sequence           156

SQL>

SQL> alter system switch logfile;

 

System altered.

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@db11g01 ~]$ ls -l /u01/app/oracle/archivelog/

total 2876

-rw-r----- 1 oracle oinstall 2939392 Aug  8 06:51 1_156_821520350.dbf

 

5)   設定主庫初始化引數

新增如下引數

DB_NAME=orcl

DB_UNIQUE_NAME=orcl

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,stdby)'

LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'

LOG_ARCHIVE_DEST_2='SERVICE=stdby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

 

 

 

FAL_SERVER=stdby

DB_FILE_NAME_CONVERT='stdby','orcl'

LOG_FILE_NAME_CONVERT='stdby','orcl'

STANDBY_FILE_MANAGEMENT=AUTO

 

6)   配置主庫listener和tnsname

修改主庫listener

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = stdby)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = stdby)

    )

  )

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = db11g02.oracle.com)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

 

ADR_BASE_LISTENER = /u01/app/oracle

 

注意配置靜態註冊(主庫和備庫都設定)

 

修改主庫tnsnames

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = db11g01.oracle.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

stdby =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = db11g02.oracle.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = stdby)

    )

  )

7)   建立備庫目錄

mkdir -p /u01/app/oracle/oradata/stdby

mkdir -p /u01/app/oracle/fast_recovery_area/stdby

mkdir -p /u01/app/oracle/admin/stdby/adump

8)   配置備庫listener和tnsname

從主庫copy過來 把偵聽的機器名進行修改即可

啟動偵聽

9)   建立備庫初始化引數

*.audit_file_dest='/u01/app/oracle/admin/stdby/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/stdby/control01.ctl','/u01/app/oracle/fast_recovery_area/stdby/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=5218762752

*.diagnostic_dest='/u01/app/oracle'

*.log_checkpoints_to_alert=TRUE

*.memory_target=1698693120

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

 

DB_UNIQUE_NAME=stdby

LOG_ARCHIVE_CONFIG='DG_CONFIG=(stdby,orcl)'

LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'

LOG_ARCHIVE_DEST_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

 

 

 

FAL_SERVER=orcl

DB_FILE_NAME_CONVERT='orcl','stdby'

LOG_FILE_NAME_CONVERT='orcl','stdby'

STANDBY_FILE_MANAGEMENT=AUTO

10)        建立備庫

[oracle@db11g02 dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 8 08:18:46 2013

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> create spfile from pfile;

 

File created.

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area 1707446272 bytes

Fixed Size                  1345408 bytes

Variable Size             989857920 bytes

Database Buffers          704643072 bytes

Redo Buffers               11599872 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@db11g02 dbs]$

[oracle@db11g02 dbs]$ rman target sys/oracle@orcl auxiliary sys/oracle@stdby

 

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Aug 8 08:46:53 2013

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORCL (DBID=1349301086)

connected to auxiliary database: ORCL (not mounted)

 

RMAN> duplicate target database for standby from active database;

 

Starting Duplicate Db at 08-AUG-13

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  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format

 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstdby'   ;

}

executing Memory Script

 

Starting backup at 08-AUG-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=44 device type=DISK

Finished backup at 08-AUG-13

 

contents of Memory Script.:

{

   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/stdby/control01.ctl';

   restore clone controlfile to  '/u01/app/oracle/fast_recovery_area/stdby/control02.ctl' from

 '/u01/app/oracle/oradata/stdby/control01.ctl';

}

executing Memory Script

 

Starting backup at 08-AUG-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20130808T074216 RECID=1 STAMP=822901338

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 08-AUG-13

 

Starting restore at 08-AUG-13

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 08-AUG-13

 

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

 "/u01/app/oracle/oradata/stdby/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/stdby/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oracle/oradata/stdby/sysaux01.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/stdby/undotbs01.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/stdby/users01.dbf";

   backup as copy reuse

   datafile  1 auxiliary format

 "/u01/app/oracle/oradata/stdby/system01.dbf"   datafile

 2 auxiliary format

 "/u01/app/oracle/oradata/stdby/sysaux01.dbf"   datafile

 3 auxiliary format

 "/u01/app/oracle/oradata/stdby/undotbs01.dbf"   datafile

 4 auxiliary format

 "/u01/app/oracle/oradata/stdby/users01.dbf"   ;

   sql 'alter system archive log current';

}

executing Memory Script

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /u01/app/oracle/oradata/stdby/temp01.dbf in control file

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting backup at 08-AUG-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

output file name=/u01/app/oracle/oradata/stdby/sysaux01.dbf tag=TAG20130808T074232

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:48

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

output file name=/u01/app/oracle/oradata/stdby/system01.dbf tag=TAG20130808T074232

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:51

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

output file name=/u01/app/oracle/oradata/stdby/undotbs01.dbf tag=TAG20130808T074232

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:53

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

output file name=/u01/app/oracle/oradata/stdby/users01.dbf tag=TAG20130808T074232

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02

Finished backup at 08-AUG-13

 

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=822905467 file name=/u01/app/oracle/oradata/stdby/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=822905467 file name=/u01/app/oracle/oradata/stdby/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=3 STAMP=822905467 file name=/u01/app/oracle/oradata/stdby/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=4 STAMP=822905467 file name=/u01/app/oracle/oradata/stdby/users01.dbf

Finished Duplicate Db at 08-AUG-13

 

RMAN>

11)        啟動備庫:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

Database altered.

 

12)        檢查是否成功:

 

 

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 

 SEQUENCE# FIRST_TIM NEXT_TIME

---------- --------- ---------

       159 08-AUG-13 08-AUG-13

       160 08-AUG-13 08-AUG-13

       161 08-AUG-13 08-AUG-13

       162 08-AUG-13 08-AUG-13

 

[oracle@db11g01 dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 8 07:54:23 2013

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter system switch logfile;

 

System altered.

 

 

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 

 SEQUENCE# FIRST_TIM NEXT_TIME

---------- --------- ---------

       159 08-AUG-13 08-AUG-13

       160 08-AUG-13 08-AUG-13

       161 08-AUG-13 08-AUG-13

       162 08-AUG-13 08-AUG-13

       163 08-AUG-13 08-AUG-13

 

檢查是日誌是否被APPLIED

 

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 

 SEQUENCE# APPLIED

---------- ---------

       159 YES

       160 YES

       161 YES

       162 YES

       163 YES