4
Creating a Logical Standby Database

This chapter steps you through the process of creating a logical standby database and then configuring log apply services to maintain the standby database using SQL apply technology. It includes the following main topics:

• Preparing the Primary Database for Standby Database Creation
• Creating a Logical Standby Database
• Verify the Logical Standby Database

The steps described in this chapter configure a logical standby database for maximum performance mode, which is the default data protection mode. Chapter 5 provides information about configuring the different data protection modes.

4.1 Preparing the Primary Database for Standby Database Creation

Before performing the tasks described in this chapter, you must ensure that the user account you use on the primary database during the logical standby database creation process is configured to have the following database roles:

• LOGSTDBY_ADMINISTRATOR role (to use the logical standby functionality)
• SELECT_CATALOG_ROLE role (to have SELECT privileges on all data dictionary views)

Also, the discussions in this chapter assume that you specify initialization parameters in a server parameter file (SPFILE), instead of a text initialization parameter file (PFILE).

Table 4-1 provides a checklist of the tasks that you perform on the primary database to prepare for logical standby database creation. There is also a reference to the section that describes the task in more detail.

Table 4-1 Preparing the Primary Database for Logical Standby Database Creation

4.1.1 Enable Forced Logging

Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:

SQL> ALTER DATABASE FORCE LOGGING;

This statement can take a considerable amount of time to complete because it waits for all unlogged direct write I/O operations to finish.

4.1.2 Enable Archiving and Define a Local Archiving Destination

Ensure that the primary database is in ARCHIVELOG mode, that automatic archiving is enabled, and that you defined a local archiving destination.

Set the local archive destination using the following SQL statement:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/disk1/oracle/oradata/payroll
  2> MANDATORY' SCOPE=BOTH;

4.1.3 Verify the LOG_PARALLELISM Initialization Parameter

On the primary database, use the SHOW PARAMETER parameter_name statement to determine the current values of the LOG_PARALLELISM initialization parameter. Logical standby databases require that you set this initialization parameter to one, which is the default value. If the LOG_PARALLELISM initialization parameter is already set to one, then skip to Section 4.1.4. Otherwise, set LOG_PARALLELISM=1 by issuing the SQL ALTER SYSTEM SET statement on the primary database and include the SCOPE=SPFILE clause to ensure the value is updated in the server parameter file. For example:

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

If you change the LOG_PARALLELISM initialization parameter, you must shut down and restart the primary database so that the new initialization parameter value will take effect. For example:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

4.1.4 Determine Support for Datatypes or Tables

Before setting up a logical standby database, ensure the logical standby database can maintain the datatypes and tables in your primary database.

The following lists the various database objects that are supported and unsupported 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

NCLOB

LONG

LONG RAW

BFILE

ROWID

UROWID

user-defined types

object types REFs

varrays

nested tables

Unsupported Tables, Sequences, and Views

User-defined tables and sequences in the SYS schema

Tables with unsupported datatypes

Tables using data segment compression

Index-organized tables

To determine if the primary database contains unsupported objects, query the DBA_LOGSTDBY_UNSUPPORTED view. For example, use the following query on the primary database to list the schema and table names of primary database tables that are not supported by logical standby databases:

SQL> SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED 
  2> ORDER BY OWNER,TABLE_NAME;

OWNER        TABLE_NAME
-----------  --------------------------
HR           COUNTRIES
OE           ORDERS
OE           CUSTOMERS
OE           WAREHOUSES
.
.
.

To view the column names and datatypes for one of the tables listed in the previous query, use a SELECT statement similar to the following:

SQL> SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED
  2> WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS';

COLUMN_NAME                      DATA_TYPE
-------------------------------  -------------------
CUST_ADDRESS                     CUST_ADDRESS_TYP
PHONE_NUMBERS                    PHONE_LIST_TYP
CUST_GEO_LOCATION                SDO_GEOMETRY

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

Skipped SQL Statements on a Logical Standby Database

By default, all SQL statements except those in the following list are applied to a logical standby database if they are executed on a primary database:

ALTER DATABASE

ALTER SESSION

ALTER SNAPSHOT

ALTER SNAPSHOT LOG

ALTER SYSTEM SWITCH LOG

CREATE CONTROL FILE

CREATE DATABASE

CREATE DATABASE LINK

CREATE PFILE FROM SPFILE

CREATE SCHEMA AUTHORIZATION

CREATE SNAPSHOT

CREATE SNAPSHOT LOG

CREATE SPFILE FROM PFILE

CREATE TABLE AS SELECT FROM A CLUSTER TABLE

DROP DATABASE LINK

DROP SNAPSHOT

DROP SNAPSHOT LOG

EXPLAIN

LOCK TABLE

RENAME

SET CONSTRAINTS

SET ROLE

SET TRANSACTION

Determine Support for Objects and Operations

PL/SQL procedures that modify metadata are not applied on the standby database, therefore their effects are not visible on the standby database. An example of this is the DBMS_AQADM advanced queuing package, which is not supported by logical standby databases. Another example is DBMS_MVIEW_REFRESH,which when executed on the primary database, is not maintained by SQL apply operations on the standby database.

The only exception to this is the DBMS_JOB package. Jobs metadata is applied to the logical standby database, but jobs are not executed.

4.1.5 Ensure That Table Rows in the Primary Database Can Be Uniquely Identified

Because the ROWIDs on a logical standby database might not be the same as the ROWIDs on the primary database, another mechanism must be used to match the updated row on the primary database to its corresponding row on the standby database. You can use one of the following to match up the corresponding rows:

• Primary key
• Unique index

Oracle Corporation recommends that you add a primary key or a unique index to tables on the primary database, whenever appropriate and possible, to ensure that SQL apply operations can efficiently apply data updates to the logical standby database.

Perform the actions described in Section 4.1.5.1 and Section 4.1.5.2 to ensure that log apply services can uniquely identify table rows.

4.1.5.1 Finding Tables Without a Unique Identifier in the Primary Database

Query the DBA_LOGSTDBY_NOT_UNIQUE view to identify tables in the primary database that do not have a primary key or unique index. The following query displays a list of tables that SQL apply operations might not be able to uniquely identify:

SQL> SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE
  2> WHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED);

Some of the tables displayed in the DBA_LOGSTDBY_NOT_UNIQUE view can still be supported because supplemental logging (that you will enable in Section 4.1.6) adds information that uniquely identifies the row in the redo logs. The presence or absence of a primary key or unique index can affect supplemental logging as follows:

• If the table has a primary key or a unique index, the amount of information added to the redo log during supplemental logging is minimal.
• If the table does not have a primary key or a unique index, supplemental logging will automatically log all scalar values for each row to the redo log.

The value of the BAD_COLUMN column will be either Y or N, as described in the following list:

• Y

  Indicates that a table column is defined using an unbounded datatype, such as CLOB or BLOB. SQL apply operations attempt to maintain these tables, but you must ensure that the application provides uniqueness in unbounded columns only. Note that if two rows in the table match except for in the LOB column, then the table cannot be maintained properly.

• N

  Indicates the table contains enough column information to maintain the table in a logical standby database.

4.1.5.2 Adding a Disabled Primary Key RELY Constraint

If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table. This will avoid the overhead of maintaining a primary key on the primary database.

To create a disabled RELY constraint on a primary database table:

Use the ALTER TABLE statement with a RELY DISABLE clause. The following example creates 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 assume the rows are unique. Be careful to select columns for the disabled RELY constraint that will uniquely identify a row. If the columns selected for the RELY constraint do not uniquely identify the row, log apply services fail to apply data from the redo logs to the logical standby database.

To improve the performance of SQL apply operations, add an index to the columns that uniquely identify the row on the logical standby database. Failure to do this results in full table scans.

4.1.6 Ensure That Supplemental Logging Is Enabled

Supplemental logging must be enabled on the primary database before you create the logical standby database. Because Oracle only logs the columns that were modified, this is not always sufficient to uniquely identify the row that changed and additional (supplemental) information must be put into the redo log. The supplemental information that is added to the redo logs helps log apply services to correctly identify and maintain tables in the logical standby database.

To determine if supplemental logging is enabled on the primary database, query the V$DATABASE fixed view. For example:

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

In this example, the NO values indicate that supplemental logging is not enabled on the primary database.

If supplemental logging is enabled, then go to Section 4.1.7. If supplemental logging is not enabled, then perform the steps in the following sections to enable supplemental logging.

4.1.6.1 Enable Supplemental Logging

On the primary database, issue the following statement to add primary key and unique index information to the archived redo logs:

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

This SQL statement adds the information to uniquely identify the row that changed on the primary database so that log apply services can correctly identify and maintain the same row on the standby database.

4.1.6.2 Switch to a New Redo Log

On the primary database, issue the following statement to switch to a new redo log:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

By switching to a new log file, you ensure that the redo logs do not contain both supplemental log data and nonsupplemental log data. Logical standby databases cannot use a redo log that contains both supplemental and nonsupplemental log data.

4.1.6.3 Verify That Supplemental Logging Is Enabled

On the primary database, verify that supplemental logging is enabled by issuing the same query used previously. For example:

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

In this example, the YES values indicate that supplemental logging is enabled on the primary database. For all tables with a primary key (SUPPLEMENTAL_LOG_DATA_PK) or unique index (SUPPLEMENTAL_LOG_DATA_UI), all columns of the primary key and unique index are placed into the redo log whenever an update operation is performed.

4.1.7 Create an Alternate Tablespace

If you expect to perform switchover operations between the primary database and a logical standby database, you should create an alternate tablespace in the primary database and move the logical standby system tables to that separate tablespace.

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. Some of these tables can rapidly become very large. By preparing an alternate tablespace in advance and moving the logical standby system tables to a separate tablespace, you will prevent these tables from filling the entire SYSTEM tablespace. Move the tables to the new tablespace before they are populated during the logical standby creation process described in Section 4.2.

To create a new tablespace for the logical standby tables, issue the SQL CREATE TABLESPACE statement. Then, use the DBMS_LOGMNR_D.SET_TABLESPACE procedure to move the tables into the new tablespace on the primary database. For example, the following statements create a new tablespace named logmnrts and move the LogMiner tables into that tablespace:

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

Note that creating an alternate tablespace can take several minutes to complete.

By creating an alternate tablespace on the primary database, any standby databases created from the primary database will contain the new tablespace. If the primary database later becomes a standby database, it will be set up correctly.

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 database on which you perform each step. There is also a reference to the section that describes the task in more detail.

Table 4-2 Create a Logical Standby Database

4.2.1 Identify the Primary Database Datafiles and Log Files

On the primary database, query the V$DATAFILE view to list the files that will be used to create the logical standby database. For example:

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

On the primary database, query the V$LOGFILE view to list the primary database logs. (This information will be used in later steps.) For example:

SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;
GROUP#            TYPE       MEMBER
----------------------------------------------------------------------------
1          ONLINE           /disk1/oracle/oradata/payroll/redo01.log
2          ONLINE           /disk1/oracle/oradata/payroll/redo02.log
3          ONLINE           /disk1/oracle/oradata/payroll/redo03.log
.
.
.

4.2.2 Make a Copy of the Primary Database

Perform the following steps to make a closed backup copy of the primary database.

Step 1 Shut down the primary database.

On the primary database, use the following SQL*Plus statement to shut it down:

SQL> SHUTDOWN IMMEDIATE;

Step 2 Copy the datafiles to a temporary location.

On the primary database, copy the datafiles that you identified in Section 4.2.1 to a temporary location using an operating system utility copy command. The following example uses the UNIX cp command:

cp /disk1/oracle/oradata/payroll/system01.dbf 
/disk1/oracle/oradata/payroll/standby/system01.dbf

cp /disk1/oracle/oradata/payroll/undotbs01.dbf
/disk1/oracle/oradata/payroll/standby/undotbs01.dbf

cp /disk1/oracle/oradata/payroll/cwmlite01.dbf
/disk1/oracle/oradata/payroll/standby/cwmlite01.dbf
.
.
.

Copying the datafiles to a temporary location reduces the amount of time that the primary database must remain shut down.

Step 3 Restart the primary database.

On the primary database, use the following SQL*Plus command to restart and mount the primary database:

SQL> STARTUP MOUNT;

Step 4 Create a backup copy of the control file for the standby database.

On the primary database, create a backup copy of the control file for the standby database:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO  
  2> '/disk1/oracle/oradata/payroll/standby/payroll3.ctl';

Step 5 Enable restricted session mode on the primary database.

On the primary database, enable restricted session mode to reduce the likelihood of users or applications performing any DML or DDL operations.

The following statement enables restricted session mode:

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; 

Step 6 Build the LogMiner dictionary.

To create a logical standby database, you must manually build the dictionary for the logical standby database. On the primary database, issue the following statements to build the LogMiner dictionary:

SQL> ALTER DATABASE OPEN;
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

Step 7 Disable restricted session mode on the primary database.

On the primary database, disable restricted session mode using the following SQL statement:

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Step 8 Identify the latest archived redo log.

To obtain a starting point for building the logical standby database, query the V$ARCHIVED_LOG view, identify the latest archived redo log, and record its name for use later in the creation process. The following query includes the DICTIONARY_BEGIN clause to find the name of the new dictionary and the STANDBY_DEST clause to show information only for the local destination. (Without the STANDBY_DEST clause, the information will include output for both the local and standby destinations.) For example:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> SELECT NAME FROM V$ARCHIVED_LOG 
  2> WHERE (SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG 
  3> WHERE DICTIONARY_BEGIN = 'YES' AND STANDBY_DEST= 'NO'));

NAME                                          
-----------------------------------------    
/disk1/oracle/oradata/payroll/arc0004.001

Remember to record the name of the archived redo log for use later in the creation process.

4.2.3 Prepare the Initialization Parameter File to Be Copied to the Standby System

Create a text initialization parameter file from the server parameter file used by the primary database; a text initialization parameter file can be copied to the standby location and modified. The following example shows the statement you use on the primary database to create a text initialization parameter file from an spfile:

SQL> CREATE PFILE='/disk1/oracle/dbs/initpayroll3.ora' FROM SPFILE;

Later, in Section 4.2.15, you will convert this file back to a server parameter file after 
it is modified to contain the parameter values appropriate for use with the logical 
standby database.

4.2.4 Copy Files from the Primary Database Location to the Standby Location

On the primary database, use an operating system copy utility to copy the following binary files from the primary database site to the standby site:

4.2.5 Set Initialization Parameters on the Logical Standby Database

Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the logical standby database, some modifications need to be made.

Example 4-1 shows the portion of the standby text initialization parameter file where values have been modified for the logical standby database. Parameter values that were changed are shown in bold typeface.

Example 4-1 Modifying Initialization Parameters for a Logical Standby Database

.
.
.
db_name=PAYROLL
compatible=9.2.0.1.0
control_files='/disk1/oracle/oradata/payroll/standby/payroll3.ctl' 
log_archive_start=TRUE 
standby_archive_dest='/disk1/oracle/oradata/payroll/standby'
log_archive_format=log%d_%t_%s.arc
log_archive_dest_1='LOCATION=/disk1/oracle/oradata/payroll/arch/'
log_parallelism=1
parallel_max_servers=9
instance_name=PAYROLL3
# The following parameter is required only if the primary and standby databases
# are located on the same system.
lock_name_space=PAYROLL3
.
.
.

The following list provides a brief explanation about the setting of the parameters shown in Example 4-1:

• db_name - Not modified. The same name as the primary database.
• compatible - Not modified. The same as the primary database, 9.2.0.1.0.
• control_files - Specify the path name and filename for the standby control file.
• log_archive_start - Not modified. The same as the setting for the primary database, TRUE.
• standby_archive_dest - Specify the location of the archived redo logs that will be received from the primary database.
• log_archive_format - Specify the format for the archived redo logs using a DBID (%d), thread (%t), and sequence number (%s).
• log_archive_dest_1 - Specify the location where the redo logs are to be archived.
• log_parallelism - Not modified. The same value as 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.
• lock_name_space - Specify the standby database instance name.

  Use this parameter when you create the logical standby database on the same system as the primary database. You must change the INSTANCE_NAME parameter to a value other than its primary database value, and set this LOCK_NAME_SPACE initialization parameter to the same value that you specified for the standby database INSTANCE_NAME initialization parameter.

  Caution: Review the initialization parameter file for additional parameters that may need to be modified. For example, you may need to modify the dump destination parameters (background_dump_dest, core_dump_dest, user_dump_dest) if the directory location on the standby database is different from those specified on the primary database. In addition, you may have to create some directories on the standby system if they do not already exist.

  See Also: Chapter 11 for a complete explanations of all the initialization parameters that can be used to modify a Data Guard environment

4.2.6 Create a Windows Service

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 payroll3 -STARTMODE auto 

4.2.7 Configure the Listener for Both the Primary and Standby Databases

On both the primary and standby sites, use Oracle Net Manager to configure a listener for the respective databases. If you plan to manage the configuration using the Data Guard broker, you must configure the listener to use the TCP/IP protocol and statically register service information for each database using the SID for the database instance.

To restart the listeners (to pick up the new definitions), enter the following LSNRCTL utility commands on both the primary and standby systems:

% lsnrctl stop
% lsnrctl start

4.2.8 Enable Dead Connection Detection on the Standby System

Enable dead connection detection by setting the SQLNET.EXPIRE_TIME parameter to 2 in the SQLNET.ORA parameter file on the standby system. For example:

SQLNET.EXPIRE_TIME=2

4.2.9 Create Oracle Net Service Names

On both the primary and standby systems, use Oracle Net Manager to create a network service name for the primary and standby databases that will be used by log transport services.

The Oracle 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 listeners for the primary and standby databases. The connect descriptor must also specify that a dedicated server be used.

4.2.10 Start and Mount the Logical Standby Database

Use the STARTUP statement to start and mount the logical standby database. Do not open the database; it should remain closed to user access until later in the creation process. For example:

SQL> STARTUP MOUNT PFILE=initpayroll3.ora;

4.2.11 Rename the Datafiles on the Logical Standby Database

On the logical standby database, rename all of the datafiles that were identified in Section 4.2.1 and copied from the primary database. For example:

SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/system01.dbf' 
  2> TO '/disk1/oracle/oradata/payroll/standby/system01.dbf';

SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/undotbs01.dbf'
  2> TO '/disk1/oracle/oradata/payroll/standby/undotbs01.dbf'

SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/cwmlite01.dbf'
  2> TO '/disk1/oracle/oradata/payroll/standby/cwmlite01.dbf'
.
.
.

These statements specify the datafile names that are in the control file, it does not rename the actual datafiles.

On the standby database, query the NAME column in the V$DATAFILE view to verify that all datafile locations are correct. (This is the same query shown in Section 4.2.1.)

4.2.12 Rename Online Redo Logs on the Logical Standby Database

Although the online redo logs were not copied from the primary database, they must all be renamed so that the pointer in the control file is updated to point to the correct location. The location and name of the online redo logs were identified in Section 4.2.1. For example:

SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/redo01.log' 
  2> TO '/disk1/oracle/oradata/payroll/standby/redo01.log';

On the standby database, query the NAME column in the V$DATAFILE view to verify that all log locations are correct. (This is the same query shown in Section 4.2.1.)

4.2.13 Turn On the Database Guard

To prevent users from updating objects in the logical standby database, turn on the database guard by issuing the following SQL statements on the standby database:

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

4.2.14 Reset the Database Name of the Logical Standby Database

Run the Oracle 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.

Before you run the DBNEWID (nid) utility, you must shut down the database, and then start and mount it. For example:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT PFILE=initpayroll3.ora;

Now, run the Oracle DBNEWID utility on the standby database to change the database name and shut it down:

nid TARGET=SYS/password@PAYROLL3 DBNAME=PAYROLL3
Connected to database PAYROLL (DBID=1456557175)

Control Files in database:
    /disk1/oracle/oradata/payroll/standby/stdby.ctl
Change database ID and database name PAYROLL to PAYROLL3? (Y/[N]) => y

Proceeding with operation
Changing database ID from 1456557175 to 416458362
Changing database name from PAYROLL to PAYROLL3
    Control File /disk1/oracle/oradata/payroll/standby/payroll3.ctl - modified
    Datafile /disk1/oracle/oradata/payroll/standby/system01.dbf - dbid changed, 
wrote new name
    Datafile /disk1/oracle/oradata/payroll/standby/undotbs01.dbf -dbid changed, 
wrote new name
    .
    .
    .
    Control File /disk1/oracle/oradata/payroll/standby/payroll3.ctl-dbid 
changed, wrote new name

Database name changed to PAYROLL3.
Modify parameter file and generate a new password file before restarting.
Database ID for database PAYROLL3 change 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.

If you use a password file for authenticatison, you must re-create the password file after running the Oracle DBNEWID (nid) utility.

4.2.15 Change the Database Name in the Parameter File

The output from the DBNEWID utility states that you must update the initialization parameter file. The following steps describe how to perform this task.

Step 1 Modify the DB_NAME parameter.

Set the DB_NAME initialization parameter in the text initialization parameter file to match the new name:

.
.
.
db_name=PAYROLL3
.
.
.

Step 2 Shut down the standby database.

On the standby database, issue the following SQL statement:

SQL> SHUTDOWN IMMEDIATE;

Step 3 Create a server parameter file for the standby database.

Connect to an idle instance of the standby database, and create a server parameter file for the standby database from the text initialization parameter file that was edited in Section 4.2.5 and in step 1 of this section. For example:

SQL> CREATE SPFILE FROM PFILE=initpayroll3.ora;

Step 4 Restart the logical standby database.

Start and open the database to user access, as follows:

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

4.2.16 Create a New Temporary File for the Logical Standby Database

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. (It is not necessary to copy temporary files from the primary database to the logical standby database.)

To identify and drop obsolete temporary files, perform the following steps on the logical standby database.

Step 1 Identify the current temporary files.

On the logical standby database, issue the following query to identify the current temporary files for the standby database:

SQL> SELECT * FROM V$TEMPFILE;
no rows selected

If this query returns "no rows selected" as shown in the example, skip step 2 and go to step 3.

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

Drop each current temporary file from the standby database, as follows:

SQL> ALTER DATABASE TEMPFILE 'tempfilename' DROP;

Step 3 Add a new temporary file.

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

1. 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
   

2. Add a new temporary file, for example:

   SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 
     2> '/disk1/oracle/oradata/payroll/standby/temp01.dbf' 
     3> SIZE 40M REUSE;
   

4.2.17 Register the Archived Redo Log and Start SQL Apply Operations

To register the most recently archived redo log and begin applying data from the redo logs to the standby database, perform the following steps.

Step 1 Register the most recently archived redo log with log apply services.

Register the archived redo log that was identified in step 8 of Section 4.2.2. The following example specifies the filename and location of the most recently archived redo log that you copied to the logical standby site:

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE 
  2> '/disk1/oracle/oradata/payroll/standby/arc0004.001';

Step 2 Start applying redo logs to the logical standby database.

Specify the following SQL statement to begin applying redo logs to the logical standby database. For example:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

4.2.18 Enable Archiving to the Logical Standby Database

This section describes the minimum amount of work you must do on the primary database to set up and enable archiving on the logical standby database.

To configure archive logging from the primary database to the standby site the LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n parameters must be defined.

The following example sets the initialization parameters needed to enable archive logging on the standby site:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=payroll3' SCOPE=BOTH; 
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;

Step 3 Start remote archiving.

Archiving of redo logs to the remote standby location does not occur until after a log switch. A log switch occurs, by default, when an online redo log becomes full. To force the current redo logs to be archived immediately, use the SQL ALTER SYSTEM statement on the primary database. For example:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

4.3 Verify the Logical Standby Database

Once you create a logical standby database and set up log transport services and log apply services, you might want to verify that redo logs are being transmitted from the primary database and applied to the standby database. To check this, perform the following steps.

Step 1 Verify that the redo logs have been registered.

To verify that the redo logs were registered on the logical standby system, connect to the logical standby database and query the DBA_LOGSTDBY_LOG view. For example:

SQL> ALTER SESSION SET NLS_DATE_FORMAT  = 'DD-MON-YY HH24:MI:SS';
Session altered.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END
  2> FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME         NEXT_TIME          DIC DIC
---------- ------------------ ------------------ --- ---
        24 23-JUL-02 18:19:05 23-JUL-02 18:19:48 YES YES
        25 23-JUL-02 18:19:48 23-JUL-02 18:19:51 NO  NO
        26 23-JUL-02 18:19:51 23-JUL-02 18:19:54 NO  NO
        27 23-JUL-02 18:19:54 23-JUL-02 18:19:59 NO  NO
        28 23-JUL-02 18:19:59 23-JUL-02 18:20:03 NO  NO
        29 23-JUL-02 18:20:03 23-JUL-02 18:20:13 NO  NO
        30 23-JUL-02 18:20:13 23-JUL-02 18:20:18 NO  NO
        31 23-JUL-02 18:20:18 23-JUL-02 18:20:21 NO  NO

8 rows selected.

Step 2 Archive some redo logs.

Connect to the primary database and archive some redo logs. For example:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.

Step 3 Query the DBA_LOGSTDBY_LOG view again.

Connect to the logical standby database and query the DBA_LOGSTDBY_LOG view again:

SQL> ALTER SESSION SET NLS_DATE_FORMAT  = 'DD-MON-YY HH24:MI:SS';
Session altered.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END
  2  FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME         NEXT_TIME          DIC DIC
---------- ------------------ ------------------ --- ---
        24 23-JUL-02 18:19:05 23-JUL-02 18:19:48 YES YES
        25 23-JUL-02 18:19:48 23-JUL-02 18:19:51 NO  NO
        26 23-JUL-02 18:19:51 23-JUL-02 18:19:54 NO  NO
        27 23-JUL-02 18:19:54 23-JUL-02 18:19:59 NO  NO
        28 23-JUL-02 18:19:59 23-JUL-02 18:20:03 NO  NO
        29 23-JUL-02 18:20:03 23-JUL-02 18:20:13 NO  NO
        30 23-JUL-02 18:20:13 23-JUL-02 18:20:18 NO  NO
        31 23-JUL-02 18:20:18 23-JUL-02 18:20:21 NO  NO
        32 23-JUL-02 18:20:21 23-JUL-02 18:32:11 NO  NO
        33 23-JUL-02 18:32:11 23-JUL-02 18:32:19 NO  NO

10 rows selected.

By checking the files on the standby database, archiving a few redo logs, and then checking the standby database again, you can see that the new redo logs were registered. These logs are now available for log apply services to begin applying them.

Step 4 Verify that data from the redo logs is being applied correctly.

On the logical standby database, query the DBA_LOGSTDBY_STATS view to verify that redo data is being applied correctly. For example:

SQL> COLUMN NAME FORMAT A30
SQL> COLUMN VALUE FORMAT A30
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';

NAME                           VALUE
------------------------------ ------------------------------
coordinator state              INITIALIZING

In the example, the output from the DBA_LOGSTDBY_STATS view shows the coordinator process is in the initialization state. When the coordinator process is initializing, log apply services are preparing to begin SQL apply operations, but data from the redo logs is not being applied to the logical standby database.

Knowing the state of the coordinator process is of particular importance because it is the LSP background process that instructs all of the other logical standby processes.

Step 5 View the V$LOGSTDBY view to see current SQL apply activity.

On the logical standby database, query the V$LOGSTDBY view to see a current snapshot of SQL apply activity. A text message describing the current activity of each process involved in reading and applying changes is displayed.

Example 4-2 shows typical output during the initialization phase.

Example 4-2 V$LOGSTDBY Output During the Initialization Phase

SQL> COLUMN STATUS FORMAT A50

SQL> COLUMN TYPE FORMAT A12

SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;
TYPE           HIGH_SCN STATUS
------------ ---------- --------------------------------------------------
COORDINATOR             ORA-16115: loading Log Miner dictionary data
READER                  ORA-16127: stalled waiting for additional transact
                        ions to be applied
BUILDER                 ORA-16117: processing
PREPARER                ORA-16116: no work available

SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;
TYPE           HIGH_SCN STATUS
------------ ---------- --------------------------------------------------
COORDINATOR             ORA-16126: loading table or sequence object number
READER                  ORA-16116: no work available
BUILDER                 ORA-16116: no work available
PREPARER                ORA-16116: no work available

Once the coordinator process begins applying redo data to the logical standby database, the V$LOGSTDBY view indicates this by showing the APPLYING state.

Example 4-3 shows typical output during the applying phase. Notice that the values in the HIGH_SCN column continue to increment. The numbers in this column will continue to increase as long as changes are being applied. The HIGH_SCN column serves only as an indicator of progress.

Example 4-3 V$LOGSTDBY Output During the Applying Phase

SQL> COLUMN NAME FORMAT A30
SQL> COLUMN VALUE FORMAT A30
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';
NAME                           VALUE
------------------------------ ------------------------------
coordinator state              APPLYING

SQL> COLUMN STATUS FORMAT A50
SQL> COLUMN TYPE FORMAT A12
SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;
TYPE           HIGH_SCN STATUS
------------ ---------- --------------------------------------------------
COORDINATOR             ORA-16117: processing
READER                  ORA-16127: stalled waiting for additional transact
                        ions to be applied

BUILDER          191896 ORA-16116: no work available
PREPARER         191902 ORA-16117: processing
ANALYZER         191820 ORA-16120: dependencies being computed for transac
                        tion at SCN 0x0000.0002ed4e

APPLIER          191209 ORA-16124: transaction 1 16 1598 is waiting on ano
                        ther transaction

APPLIER          191205 ORA-16116: no work available
APPLIER          191206 ORA-16124: transaction 1 5 1603 is waiting on anot
                        her transaction

APPLIER          191213 ORA-16117: processing
APPLIER          191212 ORA-16124: transaction 1 20 1601 is waiting on ano
                        ther transaction

APPLIER          191216 ORA-16124: transaction 1 4 1602 is waiting on anot
                        her transaction

11 rows selected.

Step 6 Check the overall progress of log apply services.

To check the overall progress of log apply services, query the DBA_LOGSTDBY_PROGRESS view on the standby database. For example:

SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;

APPLIED_SCN NEWEST_SCN
----------- ----------
     180702     180702

When the numbers in the APPLIED_SCN and NEWEST_SCN columns are equal (as shown in the query example), it means that all of the available data in the redo log was applied. These values can be compared to the values in the FIRST_CHANGE# column in the DBA_LOGSTDBY_LOG view to see how much log information has to be applied and how much remains.