使用RMAN建立物理Standby資料庫

系統資訊：Linux localhost.localdomain 2.6.18-164.el5 #1 SMP Thu Sep 3 03:33:56 EDT 2009 i686 i686 i386 GNU/Linux

資料庫版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

資料庫名：PHYPRIMA

Primary db_unique_name : phyprimary

standby db_unique_name :- phystandby

1. Make the necessary changes to the primary database. 
    a. Enable force logging.
    b. Creating the password file if one does not exist.
    c. Create standby redologs.
    d. Modify the parameter file suitable for Dataguard.

2. Ensure that the sql*net connectivity is working fine.

3. Create the standby database over the network using the active(primary) database files.
    a. Create the password file
    b. Create the initialization parameter file for the standby database (auxiliary database)
    c. Create the necessary mount points or the folders for the database files
    d. Run the standby creation ON STANDBY by connecting to primary as target database.

 

實驗流程如下：

While creating the standby database we use the active database files i.e., this command will be useful in creating the physical standby database using active database files over the network.

1. Prepare the production database to be the primary database

a. Ensure that the database is in archivelog mode .

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

b. Enable force logging

SQL> ALTER DATABASE FORCE LOGGING;

c. Create standby redologs

SQL> alter database add standby logfile '/data/oracle/oradata/phyprimary/standbyredo01.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/data/oracle/oradata/phyprimary/standbyredo02.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/data/oracle/oradata/phyprimary/standbyredo03.log' size 50M;

Database altered.

d. Modify the primary initialization parameter for dataguard on primary,

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(phyprimary,phystandby)';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/data/oracle/oradata/phyprimary/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=phyprimary';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=phystandby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=phystandby';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.

SQL> alter system set FAL_SERVER=phystandby;
System altered.

SQL> alter system set FAL_CLIENT=phyprimary;
System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='/data/oracle/oradata/phystandby','/data/oracle/oradata/phyprimary' scope=spfile;

System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='/data/oracle/oradata/phystandby/','/data/oracle/oradata/phyprimary/' scope=spfile;

System altered.

2. Ensure that the sql*net connectivity is working fine.

Insert a static entry for Boston in the listener.ora file of the standby system.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = phystandby)
     (ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
     (SID_NAME = phystandby)
    )
   )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.248.139)(PORT = 1521))
  )

 

TNSNAMES.ORA for the Primary and Standby should have BOTH entries

phyprimary =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.248.140)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = phyprimary ))
  ) 

phystandby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.248.139)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = phystandby))
  )

Check with the SQL*Net configuration using the following commands on the Primary AND Standby

% tnsping phyprimary
% tnsping phystandby

3. Create the standby database

a. Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.

The username is required to be SYS and the password needs to be the same on the Primary and Standby. 
The best practice for this is to copy the passwordfile as suggested. 
The password file name must match the instance name/SID used at the standby site, not the DB_NAME.

b. Create a initialization parameter with only one parameter DB_NAME.

DB_NAME=PHYPRIMA
DB_UNIQUE_NAME=phystandby
DB_BLOCK_SIZE=8192

c. Create the necessary directories in the standby location to place the datafiles and the trace files in the $ADR_HOME.

d. Set the environment variable ORACLE_SID to the standby service and start the standby-instance.

% export ORACLE_SID=phystandby
% sqlplus "/ as sysdba"

SQL> startup nomount pfile='/data/oracle/product/11.2.0/db_1/dbs/initphystandby.ora';

NOTE : Use either PFILE or SPFILE 

# Addtl. comment
# If DUPLICATE without TARGET connection is used you cannot use SPFILE 
# else getting

RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

e. Verify if the connection 'AS SYSDBA' is working

% sqlplus /nolog
SQL> connect sys/ SQL> connect sys/@chicago AS SYSDBA

f. On the primary system invoke the RMAN executable and connect to the primary and the auxiliary database ( i.e., the standby)

[oracle@localhost diag]$ rman target sys/gaoxu@phyprimary auxiliary sys/gaoxu@phystandby

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 6 02:59:28 2014

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

connected to target database: PHYPRIMA (DBID=324097933)
connected to auxiliary database: PHYPRIMA (not mounted)

RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate auxiliary channel stby type disk;
7>
8> duplicate target database for standby from active database
9> spfile
10>   parameter_value_convert 'phyprimary','phystandby'
11>   set db_unique_name='phystandby'
12>   set DB_FILE_NAME_CONVERT='/phyprimary/','/phystandby/'
13>   set LOG_FILE_NAME_CONVERT='/phyprimary/','/phystandby/'
14>   set control_files='/data/oracle/oradata/control01.ctl'
15>   set log_archive_max_processes='5'
16>   set fal_client='phystandby'
17>   set fal_server='phyprimary'
18>   set standby_file_management='AUTO'
19>   set LOG_ARCHIVE_CONFIG='DG_CONFIG=(phyprimary,phystandby)'
20>   set log_archive_dest_2='service=phyprimary ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=phystandby'
21> ;
22> }

此處略去一萬字 詳情請見附件

5. If you are licensed to use Active Dataguard (ADG) than open the Standby Database in READ ONLY and start the recovery.