|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 18.104.22.168.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/，如需转载，请注明出处，否则将追究法律责任。