Forgot your password?    
+ Reply to Thread
Results 1 to 3 of 3

Thread: Create and configure physical Standby Database 10g PART I

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Posts
    120

    Create and configure physical Standby Database 10g PART I

    Configure the Primary Database
    1. Enable Archivelog Mode
    SQL> shutdown immediate
    SQL> startup mount
    SQL> alter database archivelog;
    SQL> alter database open;
    SQL> archive log list

    2. Create a Password File
    If a password file does not exist for the primary database, create one using the following steps:
    [oracle@vmlinux1 ~]$ cd $ORACLE_HOME/dbs
    [oracle@vmlinux1 dbs]$ orapwd file=orapwprod password=MySysPassword
    SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;
    System altered.
    SQL> shutdown immediate
    SQL> startup open

    3. Enable Force Logging (optional)
    To place the primary database in forced logging mode, connect as SYS and run the following:
    SQL> alter database force logging;
    To verify force logging is enabled for the database:
    SQL> select force_logging from v$database;
    FORCE_LOGGING
    --------------
    YES

    4. Create Standby Redo Logs (optional)
    SQL> select group#, thread#, bytes, members from v$log;
    GROUP# THREAD# BYTES MEMBERS
    ---------- ---------- ---------- ----------
    1 1 52428800 2
    2 1 52428800 2
    3 1 52428800 2
    The number of standby redo logs required for the physical standby database in this example is (3 + 1) * 1 = 4 at 50MB each.
    From the primary database, connect as SYS and run the following to create four standby redo log file groups:
    SQL> alter database add standby logfile thread 1 group 4 size 50m;
    SQL> alter database add standby logfile thread 1 group 5 size 50m;
    SQL> alter database add standby logfile thread 1 group 6 size 50m;
    SQL> alter database add standby logfile thread 1 group 7 size 50m;
    To verify the new standby redo log files:
    SQL> select group#, type, member from v$logfile order by group#, member;

    5. Configure the Primary Database Initialization Parameters
    The parameters listed below should be placed in the initialization file for the primary database.
    # ---[ Dump Destination Parameters ] --- #
    audit_file_dest='/u01/app/oracle/admin/prod/adump'
    background_dump_dest='/u01/app/oracle/admin/prod/bdump'
    core_dump_dest='/u01/app/oracle/admin/prod/cdump'
    user_dump_dest='/u01/app/oracle/admin/prod/udump'

    # ---[ Role-independent Parameters ] --- #
    archive_lag_target=900
    compatible='10.2.0.5.0'
    control_files='/u02/oradata/PROD/controlfile/o1_mf_6hc6stn9_.ctl',
    '/u03/flash_recovery_area/PROD/controlfile/o1_mf_6hc6styy_.ctl'
    db_name='prod'
    db_create_file_dest='/u02/oradata'
    db_recovery_file_dest='/u03/flash_recovery_area'
    dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
    instance_name='prod'
    log_archive_config='dg_config=(prod,stby)'
    log_archive_max_processes=4
    remote_login_passwordfile='exclusive'

    # ---[ Primary Role Parameters ] --- #
    db_unique_name='prod'
    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=stby.info valid_for=(online_logfiles,primary_role) db_unique_name=stby'
    log_archive_dest_state_1='enable'
    log_archive_dest_state_2='defer'
    service_names='prod.info, prod'

    # ---[ Standby Role Parameters ] --- #
    db_file_name_convert='/STBY/','/PROD/'
    log_file_name_convert='/STBY/','/PROD/'
    fal_server='prod','stby'
    fal_client='prod'
    standby_file_management='auto'
    Several of the initialization parameters listed above can not be dynamically modified and therefore will require the primary database to be bounced:
    SQL> shutdown immediate
    SQL> startup

    6. Create a Backup of the Primary Database
    A physical standby database can be created using either a hot or cold backup of the primary as long as all of the necessary archivelogs are available to bring the standby database to a consistent state.
    [oracle@vmlinux1 ~]$ mkdir -p /u04/oracle/dg_staging
    [oracle@vmlinux2 ~]$ mkdir -p /u04/oracle/dg_staging
    From the primary host, perform an RMAN backup of the primary database that places the backupset into the staging directory:
    [oracle@vmlinux1 ~]$ rman target sys/MySysPassword@prod

    RMAN> backup device type disk format '/u04/oracle/dg_staging/%U' database plus archivelog;

    7. Create a Standby Controlfile
    Using the same process as above, create a standby controlfile in the staging directory using RMAN:

    [oracle@vmlinux1 ~]$ rman target sys/MySysPassword@prod

    RMAN> backup device type disk format '/u04/oracle/dg_staging/%U' current controlfile for standby;

    8. Prepare an Initialization Parameter for the Standby Database
    From the primary database, create a pfile in the staging directory:
    SQL> create pfile='/u04/oracle/dg_staging/initstby.ora' from spfile;
    File created.
    Next, modify the necessary parameters in the new pfile to allow the database to operate in the standby role.

    # ---[ Memory Parameters ] --- #
    stby.__db_cache_size=905969664
    stby.__java_pool_size=16777216
    stby.__large_pool_size=16777216
    stby.__shared_pool_size=285212672
    stby.__streams_pool_size=0

    # ---[ Dump Destination Parameters ] --- #
    audit_file_dest='/u01/app/oracle/admin/stby/adump'
    background_dump_dest='/u01/app/oracle/admin/stby/bdump'
    core_dump_dest='/u01/app/oracle/admin/stby/cdump'
    user_dump_dest='/u01/app/oracle/admin/stby/udump'

    # ---[ Role-independent Parameters ] --- #
    archive_lag_target=900
    compatible='10.2.0.5.0'
    control_files='/u02/oradata/STBY/controlfile/o1_mf_6hc6stn9_.ctl',
    '/u03/flash_recovery_area/STBY/controlfile/o1_mf_6hc6styy_.ctl'
    db_name='prod'
    db_create_file_dest='/u02/oradata'
    db_recovery_file_dest='/u03/flash_recovery_area'
    dispatchers='(PROTOCOL=TCP) (SERVICE=STBYXDB)'
    instance_name='stby'
    log_archive_config='dg_config=(prod,stby)'
    log_archive_max_processes=4
    remote_login_passwordfile='exclusive'

    # ---[ Primary Role Parameters ] --- #
    db_unique_name='stby'
    log_archive_dest_1='location=use_db_recovery_file_ dest valid_for=(all_logfiles,all_roles) db_unique_name=stby'
    log_archive_dest_2='service=prod.info valid_for=(online_logfiles,primary_role) db_unique_name=prod'
    log_archive_dest_state_1='enable'
    log_archive_dest_state_2='enable'
    service_names='stby.info, stby'

    # ---[ Standby Role Parameters ] --- #
    db_file_name_convert='/PROD/','/STBY/'
    log_file_name_convert='/PROD/','/STBY/'
    fal_server='prod','stby'
    fal_client='stby'
    standby_file_management='auto'

    9. Transfer Files to the Standby Host
    Using an OS remote copy utility, transfer the backup of the primary database, standby controlfile, and standby initialization parameter file to the standby host (vmlinux2):

    [oracle@vmlinux1 ~]$ scp /u04/oracle/dg_staging/* vmlinux2:/u04/oracle/dg_staging/

    Configure Oracle Net Components
    Oracle Net Listener

    Although not mandatory, create a named static listener entry in the listener.ora file for each database in the Data Guard configuration:
    Primary Host
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = PROD.INFO)
    (SID_NAME = prod)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    )
    )

    INBOUND_CONNECT_TIMEOUT_LISTENER = 0

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux1.info)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    )
    Standby Host

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = STBY.INFO)
    (SID_NAME = stby)
    (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    )
    )

    INBOUND_CONNECT_TIMEOUT_LISTENER = 0

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux2.info)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    )
    Restart Oracle Net Listener Service
    After making changes to the listener.ora, restart the Oracle Net listener service on the primary and standby host:

    [oracle@vmlinux1 ~]$ lsnrctl reload

    [oracle@vmlinux2 ~]$ lsnrctl reload

    Oracle Net Aliases
    The primary and standby host should contain an Oracle Net alias in the tnsnames.ora file for all primary and standby net service names:

    PROD.INFO =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux1.info)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = prod.info)
    )
    )

    STBY.INFO =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmlinux2.info)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = stby.info)
    )
    )


    Continued in : Create-and-configure-physical-Standby-Database-10g-PART-II
    Last edited by Hemant; 11-02-2011 at 06:45 PM.

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427
    Thanks for the brilliant post Masood

  3. #3
    ThanX, Dear Masood Ji, Before creating the backup,Database should be startup nomount with pfile,and then create spfile from the given pfile. After that backup should be taken.

+ Reply to Thread

Similar Threads

  1. Create and configure physical Standby Database 10g PART II
    By mdmasood in forum DataGuard Setup and Management
    Replies: 2
    Last Post: 11-25-2012, 09:34 PM
  2. Oracle 10g Using RMAN to Roll Forward a Physical Standby Database.
    By ajaychandi in forum DataGuard Setup and Management
    Replies: 0
    Last Post: 01-25-2012, 02:56 PM
  3. Oracle 10g Enable Read Write On Physical Standby Database.
    By ajaychandi in forum DataGuard Setup and Management
    Replies: 0
    Last Post: 01-25-2012, 02:45 PM
  4. Create and configure logical Standby Database 10g PART I
    By mdmasood in forum DataGuard Setup and Management
    Replies: 1
    Last Post: 11-03-2011, 06:30 PM
  5. Create and configure logical Standby Database 10g PART II
    By mdmasood in forum DataGuard Setup and Management
    Replies: 0
    Last Post: 11-03-2011, 05:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics