//
you're reading...
Administration, Data Guard, High Availibility

Configuring Data Guard in Heterogeneous Environment

With the release of Oracle database 11g, the primary and standby database in Data Guard configuration can be created in cross-platform environment. They can have different operating systems, os binaries (32/64 bit), oracle binaries (32/64 bit) and different CPU architecture.

Following is the step-by-step approach to create cross-plateform (Linux-Windows) DG configuration.

Environment:

The following table is the summary of Hardware, OS and Database configuration used:

PRIMARY STANDBY
Operaing System Red Hat Enterprise Linux 5.2 [32-bit] Windows XP Professional SP-II [32-bit]
Database Oracle Database 11g Release 2 [11.2.0.1.0] Oracle Database 11g Release 2 [11.2.0.1.0]
Memory 1.5 GB 1.0 GB
Hostname prod.home.com [192.168.200.21] support [192.168.200.11]
Oracle SID prod support
DB Unique Name prod support
TNS prod support
Service Name prod.home.com support
ORACLE BASE /u01/app/oracle E:\app\mprakash
ORACLE HOME /u01/app/oracle/product/11.2.0/db_1 E:\app\mprakash\product\11.2.0\dbhome_1
Data Files /u01/app/oracle/oradata/prod E:\app\mprakash\oradata\support
FRA /u02/fast_recovery_area/prod F:\FRA\support

Preparing the Primary Database

sys:prod@prod.home.com> SELECT name FROM v$database;

   NAME      
   --------- 
   PROD 

sys:prod@prod.home.com> SELECT * FROM v$version;

   BANNER
   ----------------------------------------------------------------------------
   Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
   PL/SQL Release 11.2.0.1.0 - Production
   CORE 11.2.0.1.0 Production
   TNS for Linux: Version 11.2.0.1.0 - Production
   NLSRTL Version 11.2.0.1.0 – Production

STEP 1. Check if database is in ARCHIVELOG mode.

Since data guard is dependent on redo data to maintain standby database, the first step to make sure is you are running database in Archive Log mode.

    sys:prod@prod.home.com> SELECT log_mode FROM v$database;
    
         LOG_MODE
         ---------
         ARCHIVELOG
    
    sys:prod@prod.home.com> ARCHIVE LOG LIST;
    
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence     5
    Next log sequence to archive   6
    Current log sequence           6
    
    sys:prod@prod.home.com> SHOW PARAMETER db_recovery_file_dest
    
       NAME                                 TYPE        VALUE
       ------------------------------------ ----------- ------------------------------
       db_recovery_file_dest                string      /u02/fast_recovery_area
       db_recovery_file_dest_size           big integer 12G
    

If database is in NOARCHIVELOG mode, use the following step to bring it in ARCHIVELOG mode.

    sys:prod@prod.home.com> SHUTDOWN IMMEDIATE;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    
    sys:prod@prod.home.com> STARTUP MOUNT;
    ORACLE instance started.
    
    Total System Global Area  891383808 bytes
    Fixed Size                  1340104 bytes
    Variable Size             553651512 bytes
    Database Buffers          331350016 bytes
    Redo Buffers                5042176 bytes
    Database mounted.
    
    sys:prod@prod.home.com> ALTER DATABASE ARCHIVELOG;
    
    Database altered.
    
    sys:prod@prod.home.com> ALTER DATABASE OPEN;
    
    Database altered.
    

STEP 2. Enable FORCE LOGGING

Enabling Force logging causes database to logged all the data changes. It is recommended and one of the Data Guard best practices.

    sys:prod@prod.home.com> SELECT force_logging FROM v$database;
    
       FORCE_LOGGING
       --------------
       NO
    
    sys:prod@prod.home.com> ALTER DATBASE FORCE LOGGING;
    
    Database altered.
    
    sys:prod@prod.home.com> SELECT force_logging FROM v$database;
    
       FORCE_LOGGING
       --------------
       YES
    

STEP 3. Configure FLASHBACK DATABASE (optional).

Though it is optional but one of the DG configuration best practices to configure flashback database on primary as well as standby database(s). In case of failover or human error it can be used to revert the database to earlier point-in-time. It can also be useful in the case of delaying the changes.

    sys:prod@prod.home.com>SELECT flashback_on FROM v$database;
    
       FLASHBACK_ON
       ------------------
       NO
    
    sys:prod@prod.home.com> SHOW PARAMETER db_flashback
    
       NAME                                 TYPE        VALUE
       ------------------------------------ ----------- ------------------------------
       db_flashback_retention_target        integer     1440
    
    sys:prod@prod.home.com> ALTER SYSTEM SET db_flashback_retention_target = 2880;
    
    System altered.
    
    sys:prod@prod.home.com> SHUTDOWN IMMEDIATE;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    
    sys:prod@prod.home.com> STARTUP MOUNT;
    ORACLE instance started.
    
    Total System Global Area  891383808 bytes
    Fixed Size                  1340104 bytes
    Variable Size             553651512 bytes
    Database Buffers          331350016 bytes
    Redo Buffers                5042176 bytes
    Database mounted.
    
    sys:prod@prod.home.com> ALTER DATABASE FLASHBACK ON;
    
    Database altered.
    
    sys:prod@prod.home.com> ALTER DATABASE OPEN;
    
    Database altered.
    
    sys:prod@prod.home.com> SELECT flashback_on FROM v$database;
    
       FLASHBACK_ON
       ------------------
       YES
    

STEP 4. Add Standby Redo Log

Create standby log files on primary database. Standby Redo logs are used to store redo information received from another database (in this case primary). SRL needs to be of the same size as online redo logs (ORL). It is recommened to have one more SRL group than the online redo log.

    sys:prod@prod.home.com> select * from v$logfile;
    
        GROUP# STATUS  TYPE    MEMBER                                                            IS_
    ---------- ------- ------- ----------------------------------------------------------------- ---
             3         ONLINE  /u02/fast_recovery_area/prod/redo03.log                           NO
             2         ONLINE  /u02/fast_recovery_area/prod/redo02.log                           NO
             1         ONLINE  /u02/fast_recovery_area/prod/redo01.log                           NO
    
    sys:prod@prod.home.com> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
         ('/u02/fast_recovery_area/prod/redo04.log') SIZE 102400K;
    
    Database altered.
    
    sys:prod@prod.home.com> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
         ('/u02/fast_recovery_area/prod/redo05.log') SIZE 102400K;
    
    Database altered.
    
    sys:prod@prod.home.com> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
         ('/u02/fast_recovery_area/prod/redo05.log') SIZE 102400K;
    
    Database altered.
    
    sys:prod@prod.home.com> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7
         ('/u02/fast_recovery_area/prod/redo06.log') SIZE 102400K;
    
    Database altered.
    
    sys:prod@prod.home.com> SELECT * FROM v$logfile;
    
        GROUP# STATUS  TYPE    MEMBER                                                            IS_
    ---------- ------- ------- ----------------------------------------------------------------- ---
             3         ONLINE  /u02/fast_recovery_area/prod/redo03.log                           NO
             2         ONLINE  /u02/fast_recovery_area/prod/redo02.log                           NO
             1         ONLINE  /u02/fast_recovery_area/prod/redo01.log                           NO
             4         STANDBY /u02/fast_recovery_area/prod/redo04.log                           NO
             5         STANDBY /u02/fast_recovery_area/prod/redo05.log                           NO
             6         STANDBY /u02/fast_recovery_area/prod/redo06.log                           NO
             7         STANDBY /u02/fast_recovery_area/prod/redo07.log                           NO
    
    7 rows selected.
    
    sys:prod@prod.home.com> SELECT group#, bytes FROM v$log;
    
        GROUP#       BYTES
    ---------- -----------
             1   104857600
             2   104857600
             3   104857600
    
    
    sys:prod@prod.home.com> SELECT group#, bytes FROM v$standby_log;
    
        GROUP#       BYTES
    ---------- -----------
             4   104857600
             5   104857600
             6   104857600
             7   104857600
    

STEP 5. Add entries in the init.ora file

Create a text initialization parameter file (pfile) from server parameter file(spfile).

    sys:prod@prod.home.com> CREATE pfile='/u01/initPROD.ora' FROM spfile;
    File created.
    

Add the necessary parameter to the file to control the data guard environment.

    #existing parameter
    prod.__db_cache_size=331350016
    prod.__java_pool_size=4194304
    prod.__large_pool_size=4194304
    prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
    prod.__pga_aggregate_target=360710144
    prod.__sga_target=532676608
    prod.__shared_io_pool_size=0
    prod.__shared_pool_size=184549376
    prod.__streams_pool_size=0
    *.audit_file_dest='/u01/app/oracle/admin/prod/adump'
    *.audit_trail='db'
    *.compatible='11.2.0.0.0'
    *.control_files='/u01/app/oracle/oradata/prod/control01.ctl','/u02/fast_recovery_area/prod/control02.ctl'
    *.db_block_size=8192
    *.db_domain='home.com'
    *.db_flashback_retention_target=2880
    *.db_name='prod'
    *.db_recovery_file_dest='/u02/fast_recovery_area'
    *.db_recovery_file_dest_size=12884901888
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
    *.log_archive_format='LOG_%t_%s_%r.arc'
    *.memory_target=891289600
    *.open_cursors=300
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.undo_tablespace='UNDOTBS1'
    
    #Added Parameter
    #Primary role related parameter incl. LOG_ARCHIVE parameter.
    DB_UNIQUE_NAME=prod
    LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,support)'
    LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'
    LOG_ARCHIVE_DEST_2='SERVICE=support LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SUPPORT'
    LOG_ARCHIVE_DEST_STATE_1=ENABLE
    LOG_ARCHIVE_DEST_STATE_2=ENABLE
    REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
    LOG_ARCHIVE_MAX_PROCESSES=5
    
    #Standby role related parameter
    FAL_SERVER=prod
    FAL_CLIENT=support
    DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod/','E:\app\mprakash\oradata\support\'
    LOG_FILE_NAME_CONVERT='/u02/fast_recovery_area/prod/','F:\FRA\support\'
    STANDBY_FILE_MANAGEMENT=AUTO
    

Now create server parameter file from pfile and startup database using spfile.

    sys:prod@prod.home.com> shutdown immediate;
    
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    
    sys:prod@prod.home.com> create spfile from pfile='/u01/initPROD.ora';
    
    File created.
    
    sys:prod@prod.home.com> startup;
    ORACLE instance started.
    
    Total System Global Area  891383808 bytes
    Fixed Size                  1340104 bytes
    Variable Size             553651512 bytes
    Database Buffers          331350016 bytes
    Redo Buffers                5042176 bytes
    Database mounted.
    Database opened.
    
    sys:prod@prod.home.com> 
    

Prepare Standby Database

STEP 6. Prepare Standby

In order to connect to the standby database create a password file on the physical standby database (using ORAPWD) with the same password used by primary SYS account.

    E:\>cd app\mprakash\product\11.2.0\dbhome_1\database
    
    E:\app\mprakash\product\11.2.0\dbhome_1\database> orapwd file=PWDsupport.ora password=DGdb_11g
    

Create an init.ora file for standby (support) database with a single parameter DB_NAME.

    db_name=support
    

Set the OARCLE_SID variable and start the physical standby in NOMOUNT mode using parameter file.

    C:\Documents and Settings\mprakash> set ORACLE_SID=support
    
    C:\Documents and Settings\mprakash> ORADIM -NEW -SID support -STARTMODE manual
    Instance created.
    
    C:\Documents and Settings\mprakash> sqlplus /nolog
    
    SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 13 15:46:25 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    idle> connect sys as sysdba
    Enter password:
    Connected to an idle instance.
    
    
    idle> startup nomount pfile='C:\initsupport.ora';
    ORACLE instance started.
    
    Total System Global Area  146472960 bytes
    Fixed Size                  1373152 bytes
    Variable Size              92277792 bytes
    Database Buffers           50331648 bytes
    Redo Buffers                2490368 bytes
    
    idle> 
    

Create the necessory directory for data file, Fast Recoovery area etc.

    C:\Documents and Settings\mprakash> mkdir F:\FRA\support
    C:\Documents and Settings\mprakash> mkdir E:\app\mprakash\oradata\support
    C:\Documents and Settings\mprakash> mkdir E:\app\mprakash\admin\support\adump
    

Oracle NET Configuration

STEP 7. Configure Oracle Net

Configure lisneters and service names on both the primary and standby so that they can ccommunicate with each other.

The following listener.ora file on Primary Databse.

    ADR_BASE_LISTENER_PROD = /u01/app/oracle
    
    LISTENER_PROD =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = prod.home.com)(PORT = 1521))
        )
      )
    
    SID_LIST_LISTENER_PROD =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = prod.home.com)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
          (SID_NAME = prod)
        )
      )
    

listener.ora file on Standby Databse.

    SID_LIST_LISTENER_SUPPORT =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = CLRExtProc)
          (ORACLE_HOME = E:\app\mprakash\product\11.2.0\dbhome_1)
          (PROGRAM = extproc)
          (ENVS = "EXTPROC_DLLS=ONLY:E:\app\mprakash\product\11.2.0\dbhome_1\bin\oraclr11.dll")
        )
    	(SID_DESC =
          (GLOBAL_DBNAME = support)
          (ORACLE_HOME = E:\app\mprakash\product\11.2.0\dbhome_1)
          (SID_NAME = support)
        )
      )
    
    LISTENER_SUPPORT =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = support)(PORT = 1521))
        )
      )
    
    ADR_BASE_LISTENER_SUPPORT = E:\app\mprakash
    

Add or update the following entry to the tnsnames.ora file on both machine.

    support =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.11)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = support)
        )
      )
    
    prod.home.com =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.21)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = prod.home.com)
        )
      )
    

Use lsnrctl (on both end) to reload or stop/start lisnerer to pick up the new definitions.
Not reloading new definition may results in ORA-12528: TNS:listener: all appropriate instances are blocking new connections.

Use tnsping to check the whether they can talk to each other.

RMAN

STEP 8. Use RMAN to create standby database

The following script is used for duplication process-

    [oracle@standb ~]$ cat /u02/script.sql
    run 
     { 
       allocate channel prd1 type disk; 
       allocate channel prd2 type disk; 
       allocate auxiliary channel spt1 type disk; 
       duplicate target database 
       for standby
       from active database 
       nofilenamecheck
         spfile 
            parameter_value_convert 'prod','support'
            set 'db_unique_name'='support'
            set standby_file_management='AUTO'
            set diagnostic_dest='E:\app\mprakash'
            SET AUDIT_FILE_DEST='E:\app\mprakash\admin\support\adump'
            set db_recovery_file_dest='F:\FRA'
            set db_recovery_file_dest_size='12884901888'
            set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod','E:\app\mprakash\oradata\support'
            set LOG_FILE_NAME_CONVERT='/u02/fast_recovery_area/prod','F:\FRA\support'
            set control_files='E:\app\mprakash\oradata\support\control01.ctl'
            set fal_client='support' 
            set fal_server='prod'
            set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=support'
            set LOG_ARCHIVE_DEST_2='SERVICE=prod.home.com LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'
            set LOG_ARCHIVE_DEST_STATE_1='ENABLE'
            set LOG_ARCHIVE_DEST_STATE_2='ENABLE'
            set log_archive_config='dg_config=(prod,support)' 
    	;
         }
    

Before running the script make sure all the directory(s)(mentioned in the script) availble on the standby.

    [oracle@support ~]$ rman | tee /u02/info.log
    
    Recovery Manager: Release 11.2.0.1.0 - Production on Sat Apr 13 16:00:20 2013
    
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
    RMAN> connect target sys@prod
    
    target database Password: 
    connected to target database: PROD (DBID=224430644)
    
    RMAN> connect auxiliary sys@support
    
    auxiliary database Password: 
    connected to auxiliary database: SUPPORT (not mounted)
    
    RMAN> @/u02/script.sql 
    RMAN> 
    RMAN> 
    RMAN> run 
    2>  { 
    3>    allocate channel prd1 type disk; 
    4>    allocate channel prd2 type disk; 
    5>    allocate auxiliary channel spt1 type disk; 
    6>    duplicate target database 
    7>    for standby
    8>    from active database 
    9>    nofilenamecheck
    10>      spfile 
    11>         parameter_value_convert 'prod','support'
    12>         set 'db_unique_name'='support'
    13>         set standby_file_management='AUTO'
    14>         set diagnostic_dest='E:\app\mprakash'
    15>         SET AUDIT_FILE_DEST='E:\app\mprakash\admin\support\adump'
    16>         set db_recovery_file_dest='F:\FRA'
    17>         set db_recovery_file_dest_size='12884901888'
    18>         set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod','E:\app\mprakash\oradata\support'
    19>         set LOG_FILE_NAME_CONVERT='/u02/fast_recovery_area/prod','F:\FRA\support'
    20>         set control_files='E:\app\mprakash\oradata\support\control01.ctl'
    21>         set fal_client='support' 
    22>         set fal_server='prod'
    23> 	    set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=support'
    24>         set LOG_ARCHIVE_DEST_2='SERVICE=prod.home.com LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'
    25> 	    set LOG_ARCHIVE_DEST_STATE_1='ENABLE'
    26>         set LOG_ARCHIVE_DEST_STATE_2='ENABLE'
    27> 	    set log_archive_config='dg_config=(prod,support)' 
    28> 	;
    29>      }
    using target database control file instead of recovery catalog
    allocated channel: prd1
    channel prd1: SID=36 device type=DISK
    
    allocated channel: prd2
    channel prd2: SID=42 device type=DISK
    
    allocated channel: spt1
    channel spt1: SID=20 device type=DISK
    
    Starting Duplicate Db at 13-APR-13
    
    contents of Memory Script:
    {
       backup as copy reuse
       targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwprod' auxiliary format 
     'E:\app\mprakash\product\11.2.0\dbhome_1\DATABASE\PWDsupport.ORA'   targetfile 
     '/u01/app/oracle/product/11.2.0/db_1/dbs/spfileprod.ora' auxiliary format 
     'E:\APP\MPRAKASH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESUPPORT.ORA'   ;
       sql clone "alter system set spfile= ''E:\APP\MPRAKASH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESUPPORT.ORA''";
    }
    executing Memory Script
    
    Starting backup at 13-APR-13
    Finished backup at 13-APR-13
    
    sql statement: alter system set spfile= ''E:\APP\MPRAKASH\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESUPPORT.ORA''
    
    contents of Memory Script:
    {
       sql clone "alter system set  dispatchers = 
     ''(PROTOCOL=TCP) (SERVICE=supportXDB)'' comment=
     '''' scope=spfile";
       sql clone "alter system set  db_unique_name = 
     ''support'' comment=
     '''' scope=spfile";
       sql clone "alter system set  standby_file_management = 
     ''AUTO'' comment=
     '''' scope=spfile";
       sql clone "alter system set  diagnostic_dest = 
     ''E:\app\mprakash'' comment=
     '''' scope=spfile";
       sql clone "alter system set  AUDIT_FILE_DEST = 
     ''E:\app\mprakash\admin\support\adump'' comment=
     '''' scope=spfile";
       sql clone "alter system set  db_recovery_file_dest = 
     ''F:\FRA'' comment=
     '''' scope=spfile";
       sql clone "alter system set  db_recovery_file_dest_size = 
     12884901888 comment=
     '''' scope=spfile";
       sql clone "alter system set  db_file_name_convert = 
     ''/u01/app/oracle/oradata/prod'', ''E:\app\mprakash\oradata\support'' comment=
     '''' scope=spfile";
       sql clone "alter system set  LOG_FILE_NAME_CONVERT = 
     ''/u02/fast_recovery_area/prod'', ''F:\FRA\support'' comment=
     '''' scope=spfile";
       sql clone "alter system set  control_files = 
     ''E:\app\mprakash\oradata\support\control01.ctl'' comment=
     '''' scope=spfile";
       sql clone "alter system set  fal_client = 
     ''support'' comment=
     '''' scope=spfile";
       sql clone "alter system set  fal_server = 
     ''prod'' comment=
     '''' scope=spfile";
       sql clone "alter system set  LOG_ARCHIVE_DEST_1 = 
     ''LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=support'' comment=
     '''' scope=spfile";
       sql clone "alter system set  LOG_ARCHIVE_DEST_2 = 
     ''SERVICE=prod.home.com LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'' comment=
     '''' scope=spfile";
       sql clone "alter system set  LOG_ARCHIVE_DEST_STATE_1 = 
     ''ENABLE'' comment=
     '''' scope=spfile";
       sql clone "alter system set  LOG_ARCHIVE_DEST_STATE_2 = 
     ''ENABLE'' comment=
     '''' scope=spfile";
       sql clone "alter system set  log_archive_config = 
     ''dg_config=(prod,support)'' comment=
     '''' scope=spfile";
       shutdown clone immediate;
       startup clone nomount;
    }
    executing Memory Script
    
    sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=supportXDB)'' comment= '''' scope=spfile
    
    sql statement: alter system set  db_unique_name =  ''support'' comment= '''' scope=spfile
    
    sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile
    
    sql statement: alter system set  diagnostic_dest =  ''E:\app\mprakash'' comment= '''' scope=spfile
    
    sql statement: alter system set  AUDIT_FILE_DEST =  ''E:\app\mprakash\admin\support\adump'' comment= '''' scope=spfile
    
    sql statement: alter system set  db_recovery_file_dest =  ''F:\FRA'' comment= '''' scope=spfile
    
    sql statement: alter system set  db_recovery_file_dest_size =  12884901888 comment= '''' scope=spfile
    
    sql statement: alter system set  db_file_name_convert =  ''/u01/app/oracle/oradata/prod'', ''E:\app\mprakash\oradata\support'' comment= '''' scope=spfile
    
    sql statement: alter system set  LOG_FILE_NAME_CONVERT =  ''/u02/fast_recovery_area/prod'', ''F:\FRA\support'' comment= '''' scope=spfile
    
    sql statement: alter system set  control_files =  ''E:\app\mprakash\oradata\support\control01.ctl'' comment= '''' scope=spfile
    
    sql statement: alter system set  fal_client =  ''support'' comment= '''' scope=spfile
    
    sql statement: alter system set  fal_server =  ''prod'' comment= '''' scope=spfile
    
    sql statement: alter system set  LOG_ARCHIVE_DEST_1 =  ''LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=support'' comment= '''' scope=spfile
    
    sql statement: alter system set  LOG_ARCHIVE_DEST_2 =  ''SERVICE=prod.home.com LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'' comment= '''' scope=spfile
    
    sql statement: alter system set  LOG_ARCHIVE_DEST_STATE_1 =  ''ENABLE'' comment= '''' scope=spfile
    
    sql statement: alter system set  LOG_ARCHIVE_DEST_STATE_2 =  ''ENABLE'' comment= '''' scope=spfile
    
    sql statement: alter system set  log_archive_config =  ''dg_config=(prod,support)'' comment= '''' scope=spfile
    
    Oracle instance shut down
    
    connected to auxiliary database (not started)
    Oracle instance started
    
    Total System Global Area     535662592 bytes
    
    Fixed Size                     1375792 bytes
    Variable Size                167772624 bytes
    Database Buffers             360710144 bytes
    Redo Buffers                   5804032 bytes
    allocated channel: spt1
    channel spt1: SID=1 device type=DISK
    
    contents of Memory Script:
    {
       backup as copy current controlfile for standby auxiliary format  'E:\APP\MPRAKASH\ORADATA\SUPPORT\CONTROL01.CTL';
    }
    executing Memory Script
    
    Starting backup at 13-APR-13
    channel prd1: starting datafile copy
    copying standby control file
    output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prod.f tag=TAG20130413T160140 RECID=5 STAMP=812649702
    channel prd1: datafile copy complete, elapsed time: 00:00:03
    Finished backup at 13-APR-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 
     "E:\APP\MPRAKASH\ORADATA\SUPPORT\TEMP01.DBF";
       switch clone tempfile all;
       set newname for datafile  1 to 
     "E:\APP\MPRAKASH\ORADATA\SUPPORT\SYSTEM01.DBF";
       set newname for datafile  2 to 
     "E:\APP\MPRAKASH\ORADATA\SUPPORT\SYSAUX01.DBF";
       set newname for datafile  3 to 
     "E:\APP\MPRAKASH\ORADATA\SUPPORT\UNDOTBS01.DBF";
       set newname for datafile  4 to 
     "E:\APP\MPRAKASH\ORADATA\SUPPORT\USERS01.DBF";
       set newname for datafile  5 to 
     "E:\APP\MPRAKASH\ORADATA\SUPPORT\EXAMPLE01.DBF";
       backup as copy reuse
       datafile  1 auxiliary format 
     "E:\APP\MPRAKASH\ORADATA\SUPPORT\SYSTEM01.DBF"   datafile 
     2 auxiliary format 
     "E:\APP\MPRAKASH\ORADATA\SUPPORT\SYSAUX01.DBF"   datafile 
     3 auxiliary format 
     "E:\APP\MPRAKASH\ORADATA\SUPPORT\UNDOTBS01.DBF"   datafile 
     4 auxiliary format 
     "E:\APP\MPRAKASH\ORADATA\SUPPORT\USERS01.DBF"   datafile 
     5 auxiliary format 
     "E:\APP\MPRAKASH\ORADATA\SUPPORT\EXAMPLE01.DBF"   ;
       sql 'alter system archive log current';
    }
    executing Memory Script
    
    executing command: SET NEWNAME
    
    renamed tempfile 1 to E:\APP\MPRAKASH\ORADATA\SUPPORT\TEMP01.DBF 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 13-APR-13
    channel prd1: starting datafile copy
    input datafile file number=00001 name=/u01/app/oracle/oradata/prod/system01.dbf
    channel prd2: starting datafile copy
    input datafile file number=00002 name=/u01/app/oracle/oradata/prod/sysaux01.dbf
    output file name=E:\APP\MPRAKASH\ORADATA\SUPPORT\SYSAUX01.DBF tag=TAG20130413T160150
    channel prd2: datafile copy complete, elapsed time: 00:01:56
    channel prd2: starting datafile copy
    input datafile file number=00005 name=/u01/app/oracle/oradata/prod/example01.dbf
    output file name=E:\APP\MPRAKASH\ORADATA\SUPPORT\SYSTEM01.DBF tag=TAG20130413T160150
    channel prd1: datafile copy complete, elapsed time: 00:02:23
    channel prd1: starting datafile copy
    input datafile file number=00003 name=/u01/app/oracle/oradata/prod/undotbs01.dbf
    output file name=E:\APP\MPRAKASH\ORADATA\SUPPORT\EXAMPLE01.DBF tag=TAG20130413T160150
    channel prd2: datafile copy complete, elapsed time: 00:00:26
    channel prd2: starting datafile copy
    input datafile file number=00004 name=/u01/app/oracle/oradata/prod/users01.dbf
    output file name=E:\APP\MPRAKASH\ORADATA\SUPPORT\UNDOTBS01.DBF tag=TAG20130413T160150
    channel prd1: datafile copy complete, elapsed time: 00:00:16
    output file name=E:\APP\MPRAKASH\ORADATA\SUPPORT\USERS01.DBF tag=TAG20130413T160150
    channel prd2: datafile copy complete, elapsed time: 00:00:15
    Finished backup at 13-APR-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=5 STAMP=812656739 file name=E:\APP\MPRAKASH\ORADATA\SUPPORT\SYSTEM01.DBF
    datafile 2 switched to datafile copy
    input datafile copy RECID=6 STAMP=812656740 file name=E:\APP\MPRAKASH\ORADATA\SUPPORT\SYSAUX01.DBF
    datafile 3 switched to datafile copy
    input datafile copy RECID=7 STAMP=812656741 file name=E:\APP\MPRAKASH\ORADATA\SUPPORT\UNDOTBS01.DBF
    datafile 4 switched to datafile copy
    input datafile copy RECID=8 STAMP=812656741 file name=E:\APP\MPRAKASH\ORADATA\SUPPORT\USERS01.DBF
    datafile 5 switched to datafile copy
    input datafile copy RECID=9 STAMP=812656741 file name=E:\APP\MPRAKASH\ORADATA\SUPPORT\EXAMPLE01.DBF
    Finished Duplicate Db at 13-APR-13
    released channel: prd1
    released channel: prd2
    released channel: spt1
    
    RMAN> 
    
    Recovery Manager complete.
    

STEP 9. Check standby status

    idle> connect sys as sysdba
    Enter password:
    ERROR:
    ORA-03113: end-of-file on communication channel
    Process ID: 0
    Session ID: 1 Serial number: 3
    
    Connected.
    
    idle> connect sys as sysdba
    Enter password:
    Connected.
    
    sys:support@SUPPORT> SELECT name, db_unique_name, open_mode, protection_mode, database_role 
                FROM v$database;
    
    NAME      DB_UNIQUE_NAME                 OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
    --------- ------------------------------ -------------------- -------------------- ----------------
    PROD      support                        MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY
    
    sys:support@SUPPORT> SELECT * FROM v$logfile;
    
        GROUP# STATUS  TYPE    MEMBER                                                            IS_
    ---------- ------- ------- ----------------------------------------------------------------- ---
             3         ONLINE  F:\FRA\SUPPORT\REDO03.LOG                                         NO
             2         ONLINE  F:\FRA\SUPPORT\REDO02.LOG                                         NO
             1         ONLINE  F:\FRA\SUPPORT\REDO01.LOG                                         NO
             4         STANDBY F:\FRA\SUPPORT\REDO04.LOG                                         NO
             5         STANDBY F:\FRA\SUPPORT\REDO05.LOG                                         NO
             6         STANDBY F:\FRA\SUPPORT\REDO06.LOG                                         NO
             7         STANDBY F:\FRA\SUPPORT\REDO07.LOG                                         NO
    7 rows selected.
    

Since we have created SRL before the standby, they are automatically added to standby database (when using DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE).

You can also enable FLASHBACK DATABAE on standby by simply using ALTER DATABASE FLASHBACK ON (since it is in mount state).

STEP 10. Verify the primary databse status and a little test

    sys:prod@prod.home.com> SELECT name, database_role, open_mode, protection_mode 
                           FROM v$database;
    
    NAME      DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE                 
    --------- ---------------- -------------------- --------------------            
    PROD      PRIMARY          READ WRITE           MAXIMUM PERFORMANCE     
    

Now create a tablespace TEST and a user.

    sys:prod@prod.home.com> CREATE TABLESPACE test
               DATAFILE '/u01/app/oracle/oradata/prod/test01.dbf'
               SIZE 50m;
    
    Tablespace created.
    
    sys:prod@prod.home.com> CREATE USER madhurendra
               IDENTIFIED BY hil43Xol
               DEFAULT TABLESPACE test
               QUOTA UNLIMITED ON test;
    
    User created.
    
    sys:prod@prod.home.com> GRANT CREATE SESSION, CREATE TABLE TO madhurendra;
    
    Grant succeeded.
    
    sys:prod@prod.home.com> connect madhurendra/hil43Xol
    Connected.
    
    madhurendra@PROD.HOME.COM> CREATE TABLE t
                               (id NUMBER,
                                name VARCHAR2(10));
    
    Table created.
    
    madhurendra@PROD.HOME.COM> INSERT INTO t VALUES(1,'ID 1');
    
    1 row created.
    
    madhurendra@PROD.HOME.COM> COMMIT;
    
    Commit complete.
    
    madhurendra@PROD.HOME.COM> connect sys as sysdba
    Connected.
    
    sys:prod@prod.home.com> SELECT * FROM madhurendra.t;
            ID NAME
    ---------- ----------
             1 ID 1
    
    sys:prod@prod.home.com> ALTER SYSTEM SWITCH LOGFILE;
    
    System altered.
    

STEP 11. Start and verify Redo Apply on Standby

    sys:support@SUPPORT> shutdown immediate;
    ORA-01109: database not open 
    
    
    Database dismounted.
    ORACLE instance shut down.
    
    
    sys:support@SUPPORT> startup;
    ORACLE instance started.
    
    Total System Global Area  535662592 bytes
    Variable Size             167772624 bytes
    Database Buffers          360710144 bytes
    Redo Buffers                5804032 bytes
    
    Database mounted.
    Database opened.
    
    sys:support@SUPPORT> select sequence#, applied from v$archived_log order by 1;
    
     SEQUENCE# APPLIED
    ---------- --------- 
            12 NO        
            13 NO        
            14 NO        
            15 NO        
            16 NO        
            17 NO
            18 NO        
    
    6 rows selected.
    
    sys:support@SUPPORT> select * from madhurendra.t;
    select * from madhurendra.t
                              *
    ERROR at line 1:
    ORA-00942: table or view does not exist 
    
    
    sys:support@SUPPORT> connect madhurendra/hil43Xol
    ERROR:
    ORA-01017: invalid username/password; logon denied 
    
    
    Warning: You are no longer connected to ORACLE.
    
    sys:support@SUPPORT> connect sys as sysdba
    Connected.
    
    --Start Redo with Real-Time Apply
    
    sys:support@SUPPORT> alter database recover managed standby database
         using current logfile disconnect from session;
    
    Database altered.
    
    sys:support@SUPPORT> select max(sequence#) from v$archived_log;
    
    MAX(SEQUENCE#) 
    -------------- 
                18 
    
    sys:support@SUPPORT> select max(sequence#) from v$log;
    
    MAX(SEQUENCE#) 
    -------------- 
                19 
    
    sys:support@SUPPORT> SELECT sequence#, applied FROM v$archived_log order by 1;
    
     SEQUENCE# APPLIED  
    ---------- ---------
            12 YES      
            13 YES
            14 YES      
            15 YES      
            16 YES      
            17 YES      
            18 IN-MEMORY
    
    7 rows selected.
    
    sys:support@SUPPORT> connect madhurendra/hil43Xol
    Connected.
    
    madhurendra@PROD.HOME.COM> SELECT * FROM t;
            ID NAME
    ---------- ----------
             1 ID 1
    
    madhurendra@PROD.HOME.COM> connect sys as sysdba
    Connected.
    
    sys:support@SUPPORT> SELECT database_role, open_mode FROM v$database;
    
    DATABASE_ROLE    OPEN_MODE
    ---------------- --------------------
    PHYSICAL STANDBY READ ONLY WITH APPLY
    
    sys:support@SUPPORT> SELECT sequence#, first_time, next_time FROM v$archived_log;
    
     SEQUENCE# FIRST_TIM NEXT_TIME
    ---------- --------- ---------
            12 13-APR-13 13-APR-13
            13 13-APR-13 13-APR-13
            14 13-APR-13 13-APR-13
            15 13-APR-13 13-APR-13
            16 13-APR-13 13-APR-13
            17 13-APR-13 13-APR-13
            18 13-APR-13 13-APR-13
            20 15-APR-13 15-APR-13
            19 13-APR-13 15-APR-13
            21 15-APR-13 15-APR-13
            22 15-APR-13 15-APR-13
    
     SEQUENCE# FIRST_TIM NEXT_TIME
    ---------- --------- ---------
            23 15-APR-13 15-APR-13
            25 20-APR-13 20-APR-13
            26 20-APR-13 20-APR-13
            24 15-APR-13 20-APR-13
            27 20-APR-13 20-APR-13
    
    16 rows selected.
    

I hope this post will help you to configure data guard in cross-plateform (or same) environment.
Happy Learning 🙂

REFERENCE:

Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: