Oracle9i Data Guard Concepts and Administration Release 2 (9.2) Part Number A96653-01 |
|
This chapter explains how to create a logical standby database and start applying redo logs to it. This chapter includes the following main topics:
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.
The following steps describe these tasks in more detail.
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.
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
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, REF
s, 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.
SYS
schemaTo 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 |
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:
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:
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.
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 |
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:
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 |
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:
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
SQL> ALTER DATABASE ARCHIVELOG;
SQL> SHUTDOWN SQL> STARTUP MOUNT PFILE=initPRIMARY1.ora
SQL> ALTER DATABASE OPEN;
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 |
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.
For example:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS; SQL> ALTER SYSTEM SWITCH LOGFILE;
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 |
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
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.
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 |
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.
Step | Task | Site |
---|---|---|
Primary |
||
Primary |
||
Modify the initialization parameter file for the logical standby site. |
Standby |
|
Standby |
||
Rename the datafiles and redo logs on the logical standby database. |
Standby |
|
Standby |
||
Standby |
||
Standby |
||
Standby |
||
Standby |
||
Drop the current temporary files from the logical standby database. |
Standby |
|
Create a new temporary file for the logical standby database. |
Standby |
|
Register the starting archived redo log with log apply services. |
Standby |
|
Start applying redo log data to the logical standby database. |
Standby |
|
Use Oracle Net Manager to configure a listener on the logical standby database. |
Standby |
|
Standby |
||
Primary |
||
Primary and standby |
||
Primary |
||
Primary |
||
Standby |
||
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.
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 . . .
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" .
Perform the following steps to create a copy of the primary database:
SQL> SHUTDOWN;
cp /oracle/oratmp/system01.dbf /disk1/stdbyhold/system01.dbf
This will reduce the amount of time that the primary database remains shut down.
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/disk1/stdbyhold/ctlbckup01.ctl';
SQL> SELECT CHECKPOINT_CHANGE# FROM V$DATABASE; CHECKPOINT_CHANGE# ------------------ 443582
SQL> ALTER DATABASE OPEN; SQL> ALTER SYSTEM SWITCH LOGFILE;
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.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."
SQL> ALTER TABLESPACE SYSTEM BEGIN BACKUP;
/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.SQL> ALTER TABLESPACE SYSTEM END BACKUP;
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/disk1/stdbyhold/ctlbckup01.ctl';
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.
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:
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.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.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.
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;
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';
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;
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;
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.
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
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;
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:
SQL> SELECT * FROM V$TEMPFILE;
SQL> ALTER DATABASE TEMPFILE 'tempfilename
' DROP;
Perform the following SQL statements on the logical standby database to add a new temporary file to the tablespace on the logical standby database:
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE 2> CONTENTS ='TEMPORARY';
SQL> ALTER TABLESPACEtablespacename
ADD TEMPFILE 'tempfilename
' 2> SIZE 20M REUSE;
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';
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;
On the logical standby site, use Oracle Net Manager to configure a listener for the logical standby 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.
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 |
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, 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;
This step is optional on a busy system. However, to ensure that your configuration is correctly set up:
SQL> ALTER SYSTEM SWITCH LOGFILE;
Perform the following tasks to define a database link to the primary database that will be used during switchover operations:
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.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.
Perform the following tasks to define a database link to the standby database that will be used during switchover operations:
SQL> CREATE DATABASE LINK standby CONNECT TO SYSTEM IDENTIFIED BY manager 2> USING 'prod2';
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.
|
Copyright © 1999, 2002 Oracle Corporation. All Rights Reserved. |
|