4
Creating a Logical Standby Database

This chapter explains how to create a logical standby database and start applying redo logs to it. This chapter includes the following main topics:

• Preparing to Create a Logical Standby Database
• Creating a Logical Standby Database

  Note: Use the Create Configuration Wizard that comes with Oracle Data Guard Manager to automatically perform all of the steps described in this chapter.

4.1 Preparing to Create a Logical Standby Database

This section describes the steps that you must perform before you can create a logical standby database. Table 4-1 provides a checklist of tasks that you need to perform.

Table 4-1  Checklist of Preparatory Tasks to Create a Logical Standby Database

Step	Task	Site
1	Determine if the primary database contains datatypes or tables that are not supported by a logical standby database.	Primary
2	Ensure that table rows in the primary database can be uniquely identified.	Primary
3	Ensure that the primary database is running in ARCHIVELOG mode.	Primary
4	Enable supplemental logging on the primary database.	Primary
5	Start the Resource Manager if you plan to perform a hot backup.	Primary
6	Create an alternate tablespace in the primary database for logical standby system tables.	Primary

The following steps describe these tasks in more detail.

Step 1 Determine if the primary database contains datatypes or tables that are not supported by a logical standby database.

Before setting up a logical standby database, you must make sure the logical standby database can maintain the datatypes and tables in your primary database.

The following lists indicate which of the various database objects are supported in logical standby databases.

Supported Datatypes

CHAR

NCHAR

VARCHAR2 and VARCHAR

NVARCHAR2

NUMBER

DATE

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

RAW

CLOB

BLOB

Unsupported Datatypes

Logical standby databases do not support columns of the following datatypes: NCLOB, LONG, LONG RAW, BFILE, ROWID, and UROWID, and user-defined type (including object types, REFs, varrays, and nested tables). An error is returned if a logical standby database attempts to apply DML changes for a table that contains a column of an unsupported datatype.

Unsupported Tables and Sequences

Tables and sequences in the SYS schema

Tables with unsupported datatypes

Tables used to support functional indexes

Tables used to support materialized views

Global temporary tables

To determine whether your primary database contains unsupported objects, use the DBA_LOGSTDBY_UNSUPPORTED view. For example, enter the following SELECT statement on the primary database to list the names of primary database tables (and columns and datatypes in those tables) that are not supported by logical standby databases:

SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;

If the primary database contains unsupported tables, log apply services automatically exclude the tables when applying redo logs to the logical standby database.

See Also: Chapter 14, "Views" for more information about the DBA_LOGSTDBY_UNSUPPORTED view

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

To maintain data in a logical standby database, SQL apply operations must be able to identify the columns that uniquely identify each row that has been updated in the primary database. Oracle Corporation recommends that you use primary keys in your primary database to ensure that SQL apply operations can efficiently and correctly apply data updates to a logical standby database.

Most tables already have a primary key or a non-null unique index, and, if not, the supplemental logging feature (that you will enable in step 4) automatically gathers the information necessary to identify rows that have been updated in the primary database. With supplemental logging, information that will uniquely identify the row is added to every update transaction in the archived redo logs so that log apply services can properly maintain tables in the logical standby database.

To ensure that SQL apply operations can uniquely identify table rows, perform the following steps:

1. Use the DBA_LOGSTDBY_NOT_UNIQUE view to identify the tables that have no primary key or non-null unique constraints. For example:

   SQL> SELECT OWNER, TABLE_NAME, BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE;
   
   

   This query returns the schema name, the table name, and a Y or N value in the BAD_COLUMN column:

   • A value of Y indicates the table column is defined using an unbounded datatype, such as LONG. If two rows in the table match except in their LOB column, then the table cannot be maintained properly.

     SQL apply operations of log apply services maintain these tables, but you must take extra steps to ensure that the application does not provide uniqueness only in unbounded columns; applications must provide uniqueness in rows in columns other than in unbounded columns.

   • A value of N indicates the table contains enough column information to maintain the table in the logical standby database. However, the log transport services and SQL apply operations will run more efficiently if you add a primary key. You should consider adding a disabled RELY constraint to these tables.

     See Also: Chapter 14, "Views" for more information about the DBA_LOGSTDBY_NOT_UNIQUE view and Oracle9i SQL Reference for more information about creating RELY constraints

2. If necessary, define a primary key to improve performance.

   To maintain a table in a logical standby database, the supplemental logging function automatically adds column data to the redo log for every update performed on that table, as follows:

   • If the table has a primary key or a unique index with a non-null column, the amount of information added to the redo log is minimal.
   • If the table does not have a primary key, supplemental logging automatically creates a unique key by adding all scalar values for each row to the redo log. However, this automated key creation will result in an increase in the amount of information written to the redo logs.
3. If necessary, create a disabled RELY constraint on the table.

   If the tables identified by the DBA_LOGSTDBY_NOT_UNIQUE view are updated frequently, you can improve redo log performance by creating a disabled RELY constraint to avoid the associated overhead of maintaining a primary key. A disabled RELY constraint provides more information to SQL apply operations about the table, but the constraint does not incur the overhead cost of an index on the primary database.

   The following example shows how to create a disabled RELY constraint on a table named mytab where rows can be uniquely identified using the id and name columns.

   SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;
   
   

   The RELY constraint tells the system to log the id and name columns to identify rows in this table. Be careful to select columns for the disabled RELY constraint that will create a primary key. If the columns selected for the RELY constraint do not make a primary key for that table, SQL apply operations will fail to apply redo information to the logical standby database.

   See Also: Chapter 14, "Views" for more information about the DBA_LOGSTDBY_NOT_UNIQUE view and Oracle9i SQL Reference for more information about creating RELY constraints

   Step 3 Ensure that the primary database is running in ARCHIVELOG mode.

   Ensure that the primary database is in ARCHIVELOG mode and that archiving is enabled. Either issue the SQL*Plus ARCHIVE LOG LIST command or query the V$DATABASE view. Take the following steps:

   1. Issue the SQL*Plus ARCHIVE LOG LIST command to determine if the database is in ARCHIVELOG mode. For example:

      SQL> ARCHIVE LOG LIST;
      Database log mode              No Archive Mode
      Automatic archival             Disabled
      Archive destination            /oracle/dbs/arch
      Oldest online log sequence     0
      Current log sequence           1
      

   2. If the database is not in ARCHIVELOG mode, as shown in step 1, issue the following command to place the database in ARCHIVELOG mode:

      SQL> ALTER DATABASE ARCHIVELOG;
      
      

   3. Start and mount the primary database without opening it. For example:

      SQL> SHUTDOWN
      SQL> STARTUP MOUNT PFILE=initPRIMARY1.ora
      
      

   4. Open the database:

      SQL> ALTER DATABASE OPEN;
      

   5. You can issue the SQL*Plus ARCHIVE LOG LIST statement again to verify the database has been placed in ARCHIVELOG mode. For example:

      SQL> ARCHIVE LOG LIST;
      Database log mode              Archive Mode
      Automatic archival             Disabled
      Archive destination            /oracle/dbs/arch
      Oldest online log sequence     0
      Next log sequence to archive   1
      Current log sequence           1
      

   To enable automatic archiving of the online redo logs, you must set LOG_ARCHIVE_START=true in the initialization parameter file.

   See Also: SQL*Plus User's Guide and Reference for additional information about the ARCHIVE LOG LIST command and Oracle9i Database Administrator's Guide for additional information about the ALTER DATABASE ARCHIVELOG statement and the LOG_ARCHIVE_START initialization parameter

   Step 4 Enable supplemental logging on the primary database.

   The supplemental information helps SQL apply operations to correctly maintain your tables in the logical standby database. To verify whether 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>
   

   View the SUPPLEMENTAL_LOG_DATA_PK, and SUPPLEMENTAL_LOG_DATA_UI columns. If supplemental logging is not enabled, execute the following statements on the primary database to add primary key and unique index information to the archived redo logs, and to switch to a new redo log.

   Note: You must enable supplemental logging before you create the logical standby database. This is because the logical standby database cannot use archived redo logs that contain both supplemental log data and nonsupplemental log data.

   For example:

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

   Note: If you enable supplemental logging on your primary database and you have already created physical standby databases, then you must perform the same command on each physical standby database to ensure that future switchovers work correctly.

   In addition to supplemental logging, you must also ensure that the LOG_PARALLELISM initialization parameter is set to 1 (this is the default). Set this parameter as shown in the following example to make sure that the correct redo information is sent to the logical standby database:

   SQL> ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=BOTH;
   
   

   If you are not using the server parameter file (SPFILE) option, you must modify your initialization parameter file after executing the ALTER SET statement. You do not have to restart your primary database.

   See Also: Chapter 14, "Views" for more information about the V$DATABASE view and the Oracle9i SQL Reference for more information about the ALTER DATABASE ADD SUPPLEMENTAL LOG DATA and ALTER SET statements

   Step 5 Start the Resource Manager if you plan to perform a hot backup.

   If you plan to create the logical standby database using a hot backup copy of the primary database, you must start the Resource Manager. To do this, define the RESOURCE_MANAGER_PLAN initialization parameter to use a resource plan and then shut down and start up the primary database.

   If you do not have a resource plan, you can use one of the supplied plans by defining the SYSTEM_PLAN attribute. If the RESOURCE_MANAGER_PLAN initialization parameter was not defined when you started the instance, you must restart your primary database to ensure that the Resource Manager is running. However, this can still be faster than performing a cold backup procedure.

   The following example shows how to set the RESOURCE_MANAGER_PLAN initialization parameter:

   SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=SYSTEM_PLAN SCOPE=BOTH;
   SQL> SHUTDOWN
   SQL> STARTUP
   

   Step 6 Create an alternate tablespace in the primary database for logical standby system tables.

   This step is necessary only if you expect to perform switchover operations in which the primary site changes roles with a standby site in the configuration.

   Logical standby databases use a number of tables defined in the SYS and SYSTEM schemas. By default, these tables are created in the SYSTEM tablespace.

   Note: Some of these tables can rapidly become very large. To prevent these tables from filling the entire SYSTEM tablespace, you must move the tables to a separate tablespace. Move the tables to the new tablespace before they are populated during the logical standby creation process.

   On the primary database, use the SQL CREATE TABLESPACE statement to create a new tablespace for the logical standby tables and use the DBMS_LOGMNR_D.SET_TABLESPACE procedure to move the tables into the new tablespace. The following example shows how to create a new tablespace named logmnrts$ and move the logical standby tables into that tablespace:

   SQL> CREATE TABLESPACE logmnrts$ DATAFILE '/usr/prod1/dbs/logmnrts.dbf' 
     2> SIZE 25 M AUTOEXTEND ON MAXSIZE UNLIMITED;
   SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts$');
   

   See Also: Oracle9i SQL Reference for complete information about the CREATE TABLESPACE statement

   4.2 Creating a Logical Standby Database

   This section describes the tasks you must perform to set up and create a logical standby database. Table 4-2 provides a checklist of the tasks that you perform to create a logical standby database and the site on which you perform each step.

   Table 4-2  Checklist of Tasks to Create a Logical Standby Database

   Step	Task	Site
   1	Identify primary database datafiles.	Primary
   2	Make a copy of the primary database.	Primary
   3	Modify the initialization parameter file for the logical standby site.	Standby
   4	Start and mount the logical standby database.	Standby
   5	Rename the datafiles and redo logs on the logical standby database.	Standby
   6	Create the online log files.	Standby
   7	Recover the logical standby database.	Standby
   8	Turn on the database guard.	Standby
   9	Reset the database name of the logical standby database.	Standby
   10	Open the logical standby database.	Standby
   11	Drop the current temporary files from the logical standby database.	Standby
   12	Create a new temporary file for the logical standby database.	Standby
   13	Register the starting archived redo log with log apply services.	Standby
   14	Start applying redo log data to the logical standby database.	Standby
   15	Use Oracle Net Manager to configure a listener on the logical standby database.	Standby
   16	Create a net service name that the logical standby database can use to connect to the primary database.	Standby
   17	Create a net service name that the primary database can use to connect to the logical standby database.	Primary
   18	Stop and restart the listeners on both systems.	Primary and standby
   19	Enable archiving to the logical standby database.	Primary
   20	Start archiving the current redo logs.	Primary
   21	Create a database link to the primary database.	Standby
   22	Create a database link to the logical standby database.	Primary

   The tasks to create a logical standby database are presented in step-by-step order, with steps alternating between the primary and logical standby databases.

   On the primary database:

   Step 1 Identify primary database datafiles.

   Query the V$DATAFILE view to get a list of files that will be used to create the logical standby database.

   SQL> SELECT NAME FROM V$DATAFILE;
   NAME 
   ----------------------------------------------------------------------------
   /oracle/oratmp/system01.dbf
   /oracle/oratmp/undotbs01.dbf
   /oracle/oratmp/cwmlite01.dbf
   .
   .
   .
   

   Step 2 Make a copy of the primary database.

   If you can afford to shut down the primary database for the length of time it takes to copy the datafiles, perform a cold backup operation using the steps described in the following "Using a Cold Backup of the Primary Database" section. Otherwise, skip to "Using a Hot Backup of the Primary Database" .

   Using a Cold Backup of the Primary Database

   Perform the following steps to create a copy of the primary database:

   1. Shut down the primary database:

      SQL> SHUTDOWN;
      
      

   2. Copy the files that you identified in step 1 to a temporary location. For example:

      cp /oracle/oratmp/system01.dbf /disk1/stdbyhold/system01.dbf
      
      

      This will reduce the amount of time that the primary database remains shut down.

   3. Restart the primary database in mount mode:

      SQL> STARTUP MOUNT;
      
      

   4. Create a backup copy of the control file. For example:

      SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/disk1/stdbyhold/ctlbckup01.ctl';
      
      

   5. Determine the current SCN in the primary database and record the number for future use:

      SQL> SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;
      CHECKPOINT_CHANGE#
      ------------------
                  443582
      
      

   6. Open the primary database and switch log files:

      SQL> ALTER DATABASE OPEN;
      SQL> ALTER SYSTEM SWITCH LOGFILE;
      
      

   7. Use an operating system copy utility to copy the following files to the logical standby site:
      • Copy the files from the temporary location on the primary database site to the proper locations on the logical standby site that you identified in step 1 "Identify primary database datafiles."
      • Copy the database initialization parameter file and database password files from the primary database site to the proper locations on the logical standby site.
      • Copy the last archived redo log file generated by the ALTER SYSTEM SWITCH LOGFILE statement in step 6 to the archived redo log directory on the standby site. If you change the archived redo log format for the standby database at a later time, you must also rename this file on the standby site to match.
   8. Go to step 3 "Modify the initialization parameter file for the logical standby site."

   Using a Hot Backup of the Primary Database

   To perform a hot backup copy of the primary database, you must first ensure that the Resource Manager is running on the primary database when the instance is started. For information about starting the Resource Manager, see step 5 "Start the Resource Manager if you plan to perform a hot backup."

   1. Back up your datafiles online by placing each tablespace in backup mode and copying the datafiles. For example;

      SQL> ALTER TABLESPACE SYSTEM BEGIN BACKUP;
      
      

   2. Copy the datafile /oracle/oratmp/system01.dbf from the primary database site to the proper location on the logical standby site. Use an operating system copy utility to copy the files.
   3. Issue the following statement for each tablespace:

      SQL> ALTER TABLESPACE SYSTEM END BACKUP;
      
      

   4. Create a backup copy of the control file. For example:

      SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/disk1/stdbyhold/ctlbckup01.ctl';
      
      

   5. Copy the backup control file, the database initialization parameter file, and the database password files from the primary database site to the proper locations on the logical standby site using an operating system copy utility.
   6. Put the primary database in a quiesced state to obtain a starting point for building the standby database.

      Putting the database in a quiesced state allows you to perform administrative operations that cannot be safely performed in the presence of concurrent transactions, queries, or PL/SQL operations. This step may take some time depending on how long it takes the system to put all active sessions in this state. (See the Oracle9i SQL Reference for more information about the ALTER SYSTEM statement.)

      The following example shows the ALTER SYSTEM QUIESCE statement and a query being performed on the V$DATABASE view to obtain the starting SCN. Record the SCN number for later use. Then, take the database out of the quiesce state and switch log files, as shown:

      SQL> ALTER SYSTEM QUIESCE;
      SQL> SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;
       
      CHECKPOINT_CHANGE#
      ------------------
                  443582
      
      SQL> ALTER SYSTEM UNQUIESCE;
      SQL> ALTER SYSTEM SWITCH LOGFILE;
      
      

      In addition to the datafiles copied previously, you must also copy all archived redo logs that were created during the hot backup procedure (up to the end of the database quiesce) to the standby location. Use an operating system copy utility to copy the archived redo logs to the standby system and apply them to the backup copy of the database.

   7. Go to step 3 "Modify the initialization parameter file for the logical standby site."

   On the standby database:

   Step 3 Modify the initialization parameter file for the logical standby site.

   Most of the initialization parameters settings in the file that you copied from the primary database are also appropriate for the logical standby database. (Chapter 11 lists the initialization parameters that are specific to the Data Guard environment, most of which pertain to both physical and logical standby databases.) However, you should modify the initialization parameter file for the logical standby site to:

   1. Change the path names and filenames of the primary control files to identify the locations of the control files for the logical standby database.
   2. Add the STANDBY_ARCHIVE_DEST initialization parameter to specify a valid directory on the standby system for the archived redo logs that will be transmitted from the primary database.
   3. Format the filenames of the archived redo logs to include a thread and sequence number. Specify the LOG_ARCHIVE_FORMAT initialization parameter to make the sequence number part of the filename for the archived redo log. For example, you might specify LOG_ARCHIVE_FORMAT=log%t_%s.arc. Later in the creation process, you will be asked to specify this filename as the starting archived redo log to be applied to the new logical standby database.
   4. Configure initialization parameters properly for SQL apply operations. Refer to the initialization parameters described in Chapter 11.

      If you are creating your logical standby database on the same system as your primary database, you must change the INSTANCE_NAME parameter to something other than its original value and add in the LOCK_NAME_SPACE initialization parameter, setting it to the same value that you specified for the INSTANCE_NAME initialization parameter. You can later remove these parameters after you have completed step 9 "Reset the database name of the logical standby database." At this point, you cannot change the DBNAME initialization parameter.

   Step 4 Start and mount the logical standby database.

   On the standby database, use the STARTUP statement to start and mount the logical standby database. The database should remain closed to user access.

   SQL> STARTUP PFILE=init$Log1.ora EXCLUSIVE MOUNT;
   

   Step 5 Rename the datafiles and redo logs on the logical standby database.

   Rename the datafiles that were copied from the primary database and the online redo logs to indicate their new location. On the logical standby database, use the ALTER DATABASE RENAME FILE statement to rename all datafiles and log files that were copied from the primary database:

   SQL> ALTER DATABASE RENAME FILE '/oracle/oratmp/system01.dbf' 
     2> TO '/oraHome/oratmp/system01.dbf';
   SQL> ALTER DATABASE RENAME FILE '/oracle/oratmp/redo01.dbf'
     2> TO '/oraHome/oratmp/redo01.dbf';
   

   Step 6 Create the online log files.

   Because the online redo log files were not copied over from the primary database system, the following command will create them in the directory specified by the RENAME statement. Execute the following statement for each log file group in the database:

   SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
   

   Step 7 Recover the logical standby database.

   You must recover the archived redo logs that you copied from the primary site in step 2, including the last archived redo log that was generated by the ALTER SYSTEM SWITCH LOGFILE statement. For example, if you copied the archived redo log to the `/fs1/logi/arch' directory on the standby site, then perform the following command using the SCN you recorded previously:

   SQL> ALTER DATABASE RECOVER AUTOMATIC FROM '/fs1/logi/arch' DATABASE
     2> UNTIL CHANGE 443582 USING BACKUP CONTROLFILE;
   

   Step 8 Turn on the database guard.

   Turn on the database guard to prevent users from updating objects in the logical standby database using the following SQL statements:

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

   You can bypass the database guard by using the DBMS_LOGSTDBY.GUARD_BYPASS_ON PL/SQL procedure.

   Step 9 Reset the database name of the logical standby database.

   Use the DBNEWID (nid) utility to change the database name of the logical standby database. Changing the name prevents any interaction between this copy of the primary database and the original primary database. The following example starts and mounts the database, changes the database name, and shuts down the standby database:

   SQL> STARTUP PFILE=init$Log1.ora EXCLUSIVE MOUNT;
   nid TARGET=SYS/CHANGE_ON_INSTALL DBNAME=Log1 SETNAME=YES
   Connected to database PRIM (DBID=1456557175)
   
   Control Files in database:
       /private2/ade/oradata/log2/t_cf1.f
   Change database ID and database name PRIM to LOG2? (Y/[N]) => y
   
   Proceeding with operation
   Changing database ID from 1456557175 to 416458362
   Changing database name from PRIM to LOG2
       Control File /private2/ade/oradata/log2/t_cf1.f - modified
       Datafile /private2/ade/oradata/log2/t_db1.f - dbid changed, wrote new name
       Datafile /private2/ade/oradata/log2/log_mnrts.f-dbid changed, wrote new name
       Control File /private2/ade/oradata/log2/t_cf1.f-dbid changed, wrote new name
   
   Database name changed to LOG2.
   Modify parameter file and generate a new password file before restarting.
   Database ID for database LOG2 changed to 416458362.
   All previous backups and archived redo logs for this database are unusable.
   Shut down database and open with RESETLOGS option.
   Successfully changed database name and ID.
   DBNEWID - Completed successfully.
   
   SQL> SHUTDOWN IMMEDIATE
   

   Note: You must change the DBNAME initialization parameter to match the changed database name.

   Step 10 Open the logical standby database.

   Enter the following statement to start and open the database to user access:

   SQL> STARTUP PFILE=init$Log1.ora EXCLUSIVE MOUNT;
   SQL> ALTER DATABASE OPEN RESETLOGS;
   

   Step 11 Drop the current temporary files from the logical standby database.

   This step removes the current temporary files (tempfiles). The tempfiles, which were included as a part of the cold backup operation of the primary database, are not viable on the logical standby database. To identify and drop obsolete tempfiles, perform the following steps on the logical standby database:

   1. Identify the current temporary files for the standby database:

      SQL> SELECT * FROM V$TEMPFILE;
      
      

   2. Drop each current temporary file from the standby database:

      SQL> ALTER DATABASE TEMPFILE 'tempfilename' DROP;
      

   Step 12 Create a new temporary file for the logical standby database.

   Perform the following SQL statements on the logical standby database to add a new temporary file to the tablespace on the logical standby database:

   1. Identify the tablespace that should contain the tempfile:

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

   2. Add a new tempfile:

      SQL> ALTER TABLESPACE tablespacename ADD TEMPFILE 'tempfilename' 
        2> SIZE 20M REUSE;
      

   Step 13 Register the starting archived redo log with log apply services.

   You must register the first redo log at which SQL apply operations of log apply services can begin to apply data to the logical standby database. Do this by executing the ALTER DATABASE REGISTER LOGICAL LOGFILE statement on the logical standby database. When you execute this statement, specify the filename and location of the most recently archived redo log that you copied to the logical standby site. For example:

   SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '/Oracle/remote_arc/db1/arc0.arc';
   

   Step 14 Start applying redo log data to the logical standby database.

   Use the following ALTER DATABASE statement and include the INITIAL keyword to begin SQL apply operations for the first time on the logical standby database. Include the starting SCN that you obtained during step 5. For example:

   SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL 443582;
   

   Step 15 Use Oracle Net Manager to configure a listener on the logical standby database.

   On the logical standby site, use Oracle Net Manager to configure a listener for the logical standby database.

   See Also: Oracle9i Net Services Administrator's Guide

   Step 16 Create a net service name that the logical standby database can use to connect to the primary database.

   On the logical standby site, use Oracle Net Manager to create a net service name that the logical standby database can use to connect to the primary database.

   The net service name must resolve to a connect descriptor that uses the same protocol, host address, port, and SID that you specified when you configured the listener on the primary database site. If you are unsure what values to use for these parameters, run Oracle Net Manager on the primary database site to display the listener configuration.

   See Also: Oracle9i Net Services Administrator's Guide

   On the primary database:

   Step 17 Create a net service name that the primary database can use to connect to the logical standby database.

   On the primary database, use Oracle Net Manager to create a net service name that the primary database can use to connect to the logical standby database. The net service name must resolve to a connect descriptor that uses the same protocol, host address, port, and SID that you specified when you configured the listener on the logical standby database site. If you are unsure what values to use for these parameters, run Oracle Net Manager on the logical standby database site to display the listener configuration.

   See Also: Oracle9i Net Services Administrator's Guide and the Oracle9i Database Administrator's Guide

   On both the primary and standby databases:

   Step 18 Stop and restart the listeners on both systems.

   First, start the standby listener by entering the following command on the standby site:

   % LSNRCTL START standby1_listener
   
   

   Normally, your default listener is already started on your primary site. Restart the default listener on the primary database to pick up the new definitions. Enter the following commands on your primary site:

   % LSNRCTL STOP
   % LSNRCTL START
   

   On the primary database:

   Step 19 Enable archiving to the logical standby database.

   On the primary database, define and enable archiving by setting the LOG_ARCHIVE_DEST_n and the LOG_ARCHIVE_DEST_STATE_n initialization parameters. For example:

   SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=Log1, LGWR'; 
   SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
   

   Step 20 Start archiving the current redo logs.

   This step is optional on a busy system. However, to ensure that your configuration is correctly set up:

   1. Issue the following statement on the primary database to start archiving redo logs:

      SQL> ALTER SYSTEM SWITCH LOGFILE;
      
      

   2. Monitor the initialization step of log apply services to the logical standby database.

   On the standby database:

   Step 21 Create a database link to the primary database.

   Perform the following tasks to define a database link to the primary database that will be used during switchover operations:

   1. On the logical standby database, create a database link to the primary database. Begin by using the DBMS_LOGSTDBY.GUARD_BYPASS_ON procedure to bypass the database guard and allow modifications to the tables in the logical standby database. For example:

      SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON;
      SQL> CREATE DATABASE LINK primary CONNECT TO SYSTEM IDENTIFIED BY manager
        2> USING 'prod1';
      SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;
      

      See Also: Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the DBMS_LOGSTDBY package

   2. Verify that the database link has been configured correctly by executing the DBMS_LOGSTDBY.UNSKIP procedure, as follows:

      SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP@primary('DML','test','test');
      
      

   If an error message is returned, it indicates there is a problem with the configuration.

   On the primary database:

   Step 22 Create a database link to the logical standby database.

   Perform the following tasks to define a database link to the standby database that will be used during switchover operations:

   1. On the primary database, create a database link to the standby database:

      SQL> CREATE DATABASE LINK standby CONNECT TO SYSTEM IDENTIFIED BY manager
        2> USING 'prod2';
      

   2. Verify that the database link has been configured correctly by executing the DBMS_LOGSTDBY.UNSKIP procedure, as follows:

      SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP@standby('DML','test','test');
      
      

      If an error message is returned, it indicates there is a problem with the configuration.