ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Data Guard 9i Creating a Logical Standby Database [ID 186150.1]

Data Guard 9i Creating a Logical Standby Database [ID 186150.1]

原创 Linux操作系统 作者:lhl1212 时间:2012-04-17 11:36:49 0 删除 编辑
Data Guard 9i Creating a Logical Standby Database [ID 186150.1]

修改时间 19-OCT-2010 类型 BULLETIN 状态 PUBLISHED

PURPOSE
-------------

The procedures below contain information about creating a logical standby. 
 
SCOPE & APPLICATION
-------------------

These procedures are to be used in conjunction with those contained within
the Oracle9i Data Guard Concepts and Administration Release 2 (9.2) manual, 
Chapter 4 Creating a Logical Standby Database. This document compliments and 
supplements the existing Oracle9i Data Guard Concepts and Administration
documentation; it does not replace the current documentation.

If you are creating a logical standby on a system that has Real Application 
Clusters installed, then most of the actions described should be performed on 
only one node of the system. You should perform. the actions described on only 
one node unless instructed otherwise in a particular step.


Procedure for creating a Logical Standby
-----------------------------------------

Prior to creating the Logical Standby please ensure the following:

1.  Determine if the primary database contains datatypes or tables that are not
    supported by a logical standby database.  See Oracle9i Data Guard Concepts 
    and Administration Release 2 Chapter 4 for further information.

2.  Ensure that table rows in the primary database can be uniquely identified.

3.  Ensure that the primary database is in ARCHIVELOG mode and that archiving 
    is enabled. 

4.  Ensure supplemental logging is enabled on the primary database. To see if 
    supplemental logging is enabled, start a SQL session and query the 
    V$DATABASE fixed view. For example, enter: 

               SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI 
                    FROM V$DATABASE;
               
               SUP SUP
               --- ---
               YES YES

               SQL>

    If supplemental logging is not enabled, execute the following statements:

               SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, 
                    UNIQUE INDEX) COLUMNS;
               SQL> ALTER SYSTEM SWITCH LOGFILE;

5.  Ensure LOG_PARALLELISM init.ora parameter is set to 1 (default value).

6.  If you plan to be performing switchover operations with the logical standby
    then you must create an alternate tablespace in the primary database for 
    logical standby system tables. Use the DBMS_LOGMNR_D.SET_TABLESPACE procedure
    to move the tables into the new tablespace.  For example:

               SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logical_tblsp');



STEPS TO CREATE THE LOGICAL STANDBY
-----------------------------------

On the primary database:

1.  Perform. cold backup of production database.  If you must perform. a hot 
    backup then see 
    Note 278371.1 'Creating a Logical Standby with Minimal Production Downtime'
    for the required steps.  

2.  Bring the primary database to the mount state to create a backup of 
    the controlfile.
	
        SQL> ALTER DATABASE BACKUP CONTROLFILE to
             '/u01/oradata/backup/bkup.ctl';

3.  Open the primary database and build the logminer dictionary.
		
        SQL> ALTER system enable restricted session; 	
        SQL> ALTER DATABASE OPEN; 
        SQL> EXECUTE DBMS_LOGSTDBY.BUILD; 
	
4.  Archive the current online redo log and disable restricted session.
	
        SQL> ALTER SYSTEM archive log current;               
        SQL> ALTER system disable restricted session;

5.  Identify the archived redo log that contains the logminer dictionary for 
    use alter in the creation process.
       
        SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES'
             and STANDBY_DEST='NO';

6.  Copy backup datafiles and control files created in steps 1 and 2, latest 
    archived redo log that was identified in step 5, and a copy of the primary
    initialization parameter file to the standby host.

On the secondary/logical standby site:

7.  Modify the copy of the primary initialization file to support the logical 
    standby on the standby host.  Some parameters that might require 
    modification are: 

    control_files          Specify the path name and filename for the standby 
                           control file. 

    standby_archive_dest   Specify the location of the archived redo logs 
                               that will be received from the primary database. 

    parallel_max_servers   Set to 9. 

    instance_name          If this parameter is defined, specify a different 
                           value for the standby database than the primary 
                           database when the primary and standby databases 
                           reside on the same host. 


8.  If the standby system is on a Windows system, then you must create a 
    Windows Service. Run the ORADIM utility to create both the Windows Service
    and a password file. For example: 

        WINNT> oradim -NEW -SID LGSTBY -STARTMODE auto 

9.  Start and mount the logical standby database in exclusive mode. 
	
        SQL> STARTUP MOUNT EXCLUSIVE;

     Rename the datafiles that were copied from the primary database 
     and the online redo logs to indicate their new location:

	SQL> alter database rename file
 	2  '/u01/oradata/sys.dbf'
  	3  to
  	4 '/u01/oradata/stby/logical/sys.dbf';

10. Turn on the database guard.
	
        SQL> ALTER DATABASE GUARD ALL;
	 	
11. Open the logical Standby followed by a shutdown with immediate or 
    normal.

        SQL> ALTER DATABASE OPEN RESETLOGS;
        SQL> SHUTDOWN IMMEDIATE;

12. Mount the logical standby in exclusive mode and reset the database 
    name.
	
        SQL> startup mount exclusive
        SQL> exit
               
       $ nid target=sys/change_on_install dbname=DB2 setname=yes
               DBNEWID: Release 9.2.0.1.0 - Production
               Copyright (c) 1995, 2002, Oracle Corporation.  All ghts 
               Connected to database DB1 (DBID=3602029126)

               Control Files in database:
               /u01/oradata/logical/control.ctl
              Change database name of database DB1 to DB2? 
              (Y/[N]) =>

              Proceeding with operation
              Changing database name from DB1 to DB2
              Control File /database/9i/LGSTBY/control.ctl - modified
              Datafile /u01/oradata/stby/system01.dbf - wrote new 
              Datafile /u01/oradata/stby/l_t.dbf - wrote new name
              Control File /u01/oradata/stby/control.ctl - wrote new 
              Database name changed to DB2.
    Modify parameter file and generate a new password
    file before restarting.
               Succesfully changed database name.
               DBNEWID - Completed succesfully.

13. Shutdown the database and change the DB_NAME init.ora parameter to 
    the new name in the initialization file and change your ORACLE_SID.
	
        SQL> SHUTDOWN IMMEDIATE

14. Open the logical standby database in exclusive mode.

        SQL> STARTUP MOUNT EXCLUSIVE;	
        SQL> ALTER DATABASE OPEN;

15.  The temporary files, which were included as a part of the closed backup 
     operation on the primary database, are not viable on the logical standby 
     database. To identify and drop obsolete temporary files, perform. the 
     following steps on the logical standby database. 

        SQL> SELECT * FROM V$TEMPFILE;

     Drop each current temporary file from the standby database that was 
     returned in the above query.

        SQL> ALTER DATABASE TEMPFILE 'tempfilename' DROP;

     On the logical standby database, perform. the following tasks to add a new
     temporary file to the tablespace: 

     Identify the tablespace that should contain the temporary file, for example: 

        SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE 
          2> CONTENTS ='TEMPORARY';

        TABLESPACE_NAME
        --------------------------------
        TEMP

     Add a new temporary file, for example: 

         SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 
           2> '/u01/oradata/stby/temp01.dbf' 
           3> SIZE 250M REUSE;

    Note that adaquate temporary space is essential for the initial load of the
    logminer dictionary.

16. On the logical standby register the archive log identified in step 5. Run the
    following on the standby making sure to convert to the proper path:
		
         SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE 
              '/u01/oradata/stby/arch/arc1_28.arc';

17. Use the following ALTER DATABASE statement and include the INITIAL 
    keyword to begin SQL apply operations for the first time on the 
    logical standby.
    
         SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;

18. Configure a listener on the logical standby database.
	
               LISTENER =
                 (ADDRESS = 
                    (PROTOCOL = TCP)
                    (HOST = hasunclu2)
                    (PORT = 1521)
                 )
	

19. Start the listener on the standby host.  Once both standby and production 
    listeners are running manually register the service names to ensure listener
    registration.

               SQL> ALTER SYSTEM REGISTER;

20. Create a net service name that the logical standby database can use to 
    connect to the primary database within the tnsnames.ora on the standby
    host.
	
               DB1 =
                (DESCRIPTION =
                  (ADDRESS = 
                    (PROTOCOL = TCP)
                    (HOST = hasunclu1)
                    (PORT = 1521)
                  )
                 (CONNECT_DATA =
                   (SERVER = DEDICATED)
                   (SERVICE_NAME = DB1)
                 )
                )


On the primary database:

21. Create a net service name that the primary database can use to connect to 
    the logical standby database within the tnsnames.ora file on the primary
    host.
	
               DB2 =
                (DESCRIPTION =
                  (ADDRESS = 
                    (PROTOCOL = TCP)
                    (HOST = hasunclu2)
                    (PORT = 1521)
                  )
                 (CONNECT_DATA =
                   (SERVER = DEDICATED)
                   (SERVICE_NAME = DB2)
                 )
                )


22. Enable archiving to the logical standby database.
	
   	SQL> ALTER SYSTEM SET
          2> log_archive_dest_2='service=DB2_hasunclu2 lgwr';
   
        SQL> ALTER SYSTEM SET log_archive_dest_state_2=enable;

    Remember to set these values in your init.ora also.
	
23. Start archiving the current redo logs.
	
	SQL> ALTER SYSTEM SWITCH LOGFILE;

     Verify that the remote archiving succeeded by running the following
     select:

        SQL> SELECT STATUS, ERROR FROM V$ARCHIVE_DEST;

24.  Verifying archived redo logs are being applied

     To verify that the archived redo logs are being applied, query the 
     V$LOGSTDBY view. This view provides information about the processes that 
     are reading redo log information and applying it to the logical standby 
     databases. You can also query the DBA_LOGSTDBY_PROGRESS view to find out 
     the progress of SQL apply operations. The V$LOGSTDBY_STATS view shows the
     state of the coordinator process and information about the SQL transactions
     that have been applied to the logical standby database.

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

上一篇: linux LVM RAW
请登录后发表评论 登录
全部评论

注册时间:2008-12-08

  • 博文量
    252
  • 访问量
    493978