D Online Database Upgrade and Maintenance with Oracle Streams

This appendix describes how to use Oracle Streams to perform a database upgrade to the current release of Oracle Database from one of the following releases:

Oracle Database 10g Release 2 (10.2)

Oracle Database 11g Release 1 (11.1)

This appendix also describes how to perform some maintenance operations with Oracle Streams on an Oracle Database 11g Release 2 (11.2) database. These maintenance operations include migrating an Oracle database to a different platform or character set, upgrading user-created applications, and applying Oracle Database patches or patch sets.

The upgrade and maintenance operations described in this appendix use the features of Oracle Streams to achieve little or no database down time.

Overview of Using Oracle Streams for Upgrade and Maintenance Operations

Database upgrades can require substantial database down time. The following maintenance operations also typically require substantial database down time:

Migrating a database to a different platform

Migrating a database to a different character set

Modifying database schema objects to support upgrades to user-created applications

Applying an Oracle Database software patch or patch set

You can achieve these upgrade and maintenance operations with little or no down time by using the features of Oracle Streams. To do so, you use Oracle Streams to configure a replication environment with the following databases:

Source Database: The original database that is being maintained.

Capture Database: The database where a capture process captures changes made to the source database during the maintenance operation.

Destination Database: The copy of the source database where an apply process applies changes made to the source database during the maintenance operation.

Specifically, you can use the following general steps to perform the upgrade or maintenance operation while the database is online:

Configure an Oracle Streams replication environment where the original database is the source database and a copy of the database is the destination database. The PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures in the DBMS_STREAMS_ADM package configure the Oracle Streams replication environment.

Perform the upgrade or maintenance operation on the destination database. During this time the original source database is available online, and changes to the original source database are being captured by a capture process.

Use Oracle Streams to apply the changes made to the source database at the destination database.

When the destination database has caught up with the changes made at the source database, take the source database offline and make the destination database available for applications and users.

The Capture Database During the Upgrade or Maintenance Operation

During the upgrade or maintenance operation, the capture database is the database where the capture process is created. A local capture process can be created at the source database during the maintenance operation, or a downstream capture process can be created at the destination database or at a third database. If the destination database is the capture database, then a propagation from the capture database to the destination database is not needed. A downstream capture process reduces the resources required at the source database during the maintenance operation.

Note:

Before you begin the database upgrade or maintenance operation with Oracle Streams, decide which database will be the capture database.

If the RMAN DUPLICATE or CONVERTDATABASE command is used for database instantiation, then the destination database cannot be the capture database.

Assumptions for the Database Being Upgraded or Maintained

The instructions in this appendix assume that all of the following statements are true for the database being upgraded or maintained:

The database is not part of an existing Oracle Streams environment.

The database is not part of an existing logical standby environment.

The database is not part of an existing Advanced Replication environment.

No tables at the database are master tables for materialized views in other databases.

No messages are enqueued into user-created queues during the upgrade or maintenance operation.

Considerations for Job Slaves and PL/SQL Package Subprograms

If possible, ensure that no job slaves are created, modified, or deleted during the upgrade or maintenance operation, and that no Oracle-supplied PL/SQL package subprograms are invoked during the operation that modify both user data and data dictionary metadata at the same time. The following packages contain subprograms that modify both user data and data dictionary metadata at the same time: DBMS_RLS, DBMS_STATS, and DBMS_JOB.

These values exclude any database objects that are not supported by Oracle Streams. The asterisk (*) specified for exclude_schemas indicates that some database objects in every schema in the database might be excluded from the replication environment. The value specified for the exclude_flags parameter indicates that DML and DDL changes for all unsupported database objects are excluded from the replication environment. Rules are placed in the negative rule sets for the capture processes to exclude these database objects.

To list unsupported database objects, query the DBA_STREAMS_UNSUPPORTED data dictionary view at the source database. If you use these parameter settings, then changes to the database objects listed in this view are not maintained by Oracle Streams during the maintenance operation. Therefore, Step 7 in "Task 1: Beginning the Operation" instructs you to ensure that no changes are made to these database objects during the database upgrade or maintenance operation.

Preparing for Downstream Capture

If you decided that the destination database or a third database will be the capture database, then you must prepare for downstream capture by configuring log file copying from the source database to the capture database. If you decided that the source database will be the capture database, then log file copying is not required. See "The Capture Database During the Upgrade or Maintenance Operation" for information about the decision.

Complete the following steps to prepare the source database to copy its redo log files to the capture database, and to prepare the capture database to accept these redo log files:

Configure Oracle Net so that the source database can communicate with the capture database.

Configure authentication at both databases to support the transfer of redo data.

Redo transport sessions are authenticated using either the Secure Sockets Layer (SSL) protocol or a remote login password file. If the source database has a remote login password file, then copy it to the appropriate directory on the downstream capture database system. The password file must be the same at the source database and the downstream capture database.

At the source database, set the following initialization parameters to configure redo transport services to transmit redo data from the source database to the downstream database:

LOG_ARCHIVE_DEST_n - Configure at least one LOG_ARCHIVE_DEST_n initialization parameter to transmit redo data to the downstream database. To do this, set the following attributes of this parameter:

SERVICE - Specify the network service name of the downstream database.

ASYNC or SYNC - Specify a redo transport mode.

The advantage of specifying ASYNC is that it results in little or no effect on the performance of the source database. ASYNC is recommended to avoid affecting source database performance if the downstream database or network is performing poorly.

The advantage of specifying SYNC is that redo data is sent to the downstream database faster then when ASYNC is specified. Also, specifying SYNCAFFIRM results in behavior that is similar to MAXIMUMAVAILABILITY standby protection mode. Note that specifying an ALTERDATABASESTANDBYDATABASETOMAXIMIZEAVAILABILITY SQL statement has no effect on an Oracle Streams capture process.

NOREGISTER - Specify this attribute so that the location of the archived redo log files is not recorded in the downstream database control file.

TEMPLATE - Specify a directory and format template for archived redo logs at the downstream database. The TEMPLATE attribute overrides the LOG_ARCHIVE_FORMAT initialization parameter settings at the downstream database. The TEMPLATE attribute is valid only with remote destinations. Ensure that the format uses all of the following variables at each source database: %t, %s, and %r.

DB_UNIQUE_NAME - The unique name of the downstream database. Use the name specified for the DB_UNIQUE_NAME initialization parameter at the downstream database.

The following example is a LOG_ARCHIVE_DEST_n setting that specifies a capture database (DBS2.EXAMPLE.COM):

Specify a value for the TEMPLATE attribute that keeps log files from a remote source database separate from local database log files. In addition, if the downstream database contains log files from multiple source databases, then the log files from each source database should be kept separate from each other.

LOG_ARCHIVE_DEST_STATE_n - Set this initialization parameter that corresponds with the LOG_ARCHIVE_DEST_n parameter for the downstream database to ENABLE.

For example, if the LOG_ARCHIVE_DEST_2 initialization parameter is set for the downstream database, then set the LOG_ARCHIVE_DEST_STATE_2 parameter in the following way:

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_CONFIG - Set the DB_CONFIG attribute in this initialization parameter to include the DB_UNIQUE_NAME of the source database and the downstream database.

For example, if the DB_UNIQUE_NAME of the source database is dbs1, and the DB_UNIQUE_NAME of the downstream database is dbs2, then specify the following parameter:

LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbs1,dbs2)'

By default, the LOG_ARCHIVE_CONFIG parameter enables a database to both send and receive redo.

At the downstream database, set the DB_CONFIG attribute in the LOG_ARCHIVE_CONFIG initialization parameter to include the DB_UNIQUE_NAME of the source database and the downstream database.

For example, if the DB_UNIQUE_NAME of the source database is dbs1, and the DB_UNIQUE_NAME of the downstream database is dbs2, then specify the following parameter:

LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbs1,dbs2)'

By default, the LOG_ARCHIVE_CONFIG parameter enables a database to both send and receive redo.

If you reset any initialization parameters while the instance is running at a database in Step 3 or Step 4, then you might want to reset them in the initialization parameter file as well, so that the new values are retained when the database is restarted.

If you did not reset the initialization parameters while the instance was running, but instead reset them in the initialization parameter file in Step 3 or Step 4, then restart the database. The source database must be open when it sends redo log files to the capture database because the global name of the source database is sent to the capture database only if the source database is open.

Preparing for Upgrade or Maintenance of a Database with User-Defined Types

User-defined types include object types, REF values, varrays, and nested tables. Currently, Oracle Streams capture processes and apply processes do not support user-defined types. This section discusses using Oracle Streams to perform an upgrade or maintenance operation on a database that has user-defined types.

One option is to ensure that no data manipulation language (DML) or data definition language (DDL) changes are made to the tables that contain user-defined types during the operation. In this case, these tables are instantiated at the destination database, and no changes are made to these tables during the entire operation. After the operation is complete, make the tables that contain user-defined types read/write at the destination database.

However, if tables that contain user-defined types must remain open during the operation, then the following general steps can be used to retain changes to these types during the operation:

At the source database, create one or more logging tables to store row changes to tables that include user-defined types. Each column in the logging table must use a data type that is supported by Oracle Streams.

At the source database, create a DML trigger that fires on the tables that contain the user-defined data types. The trigger converts each row change into relational equivalents and logs the modified row in a logging table created in Step 1.

Ensure that the capture process and propagation are configured to capture and, if necessary, propagate changes made to the logging table to the destination database. Changes to tables that contain user-defined types should not be captured or propagated. Therefore, ensure that the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures include the logging tables and exclude the tables that contain user-defined types.

At the destination database, configure the apply process to use a DML handler that processes the changes to the logging tables. The DML handler reconstructs the user-defined types from the relational equivalents and applies the modified changes to the tables that contain user-defined types.

For instructions, go to the My Oracle Support (formerly OracleMetaLink) Web site using a Web browser:

Preparing for Upgrades to User-Created Applications

This section is relevant only if the operation entails upgrading user-created applications. During an upgrade of user-created applications, schema objects can be modified, and there might be logical dependencies that cannot be detected by the database alone. The following sections describe handling these issues during an application upgrade:

Handling Modifications to Schema Objects

If you are upgrading user-created applications, then, typically, schema objects in the database change to support the upgraded applications. In Oracle Streams, row logical change records (LCRs) contain information about row changes that result from DML statements. A declarative rule-based transformation or DML handler can modify row LCRs captured from the source database redo log so that the row LCRs can be applied to the altered tables at the destination database.

A rule-based transformation is any modification to a message that results when a rule in a positive rule set evaluates to TRUE. Declarative rule-based transformations cover a common set of transformation scenarios for row LCRs. Declarative rule-based transformations are run internally without using PL/SQL. You specify such a transformation using a procedure in the DBMS_STREAMS_ADM package. A declarative rule-based transformation can modify row LCRs during capture, propagation, or apply.

A DML handler is either a collection of SQL statements or a user procedure that processes row LCRs resulting from DML statements at a source database. An Oracle Streams apply process at a destination database can pass row LCRs to a DML handler, and the DML handler can modify the row LCRs.

The process for upgrading user-created applications using Oracle Streams can involve modifying and creating the schema objects at the destination database after instantiation. You can use one or more declarative rule-based transformations and DML handlers at the destination database to process changes from the source database so that they apply to the modified schema objects correctly. Declarative rule-based transformations and DML handlers can be used during application upgrade to account for differences between the source database and destination database.

In general, declarative rule-based transformations are easier to use than DML handlers. Therefore, when modifications to row LCRs are required, try to configure a declarative rule-based transformation first. If a declarative rule-based transformation is not sufficient, then use a DML handler. If row LCRs for tables that contain one or more LOB columns must be modified, then you should use a procedure DML handler and LOB assembly.

Before you begin the database upgrade or maintenance operation, you should complete the following tasks to prepare your declarative rule-based transformations or DML handlers:

Determine the declarative rule-based transformations and DML handlers you will need at your destination database. Your determination depends on the modifications to the schema objects required by your upgraded applications.

Create the SQL statements or the PL/SQL procedures that you will use for any DML handlers during the database maintenance operation. See "Managing a DML Handler" for information about creating the PL/SQL procedures.

Custom rule-based transformation can also be used to modify row LCRs during application upgrade. However, these modifications can be accomplished using DML handlers, and DML handlers are more efficient than custom rule-based transformations.

Handling Logical Dependencies

In some cases, an apply process requires additional information to detect dependencies in row LCRs that are being applied in parallel. During application upgrades, an apply process might require additional information to detect dependencies in the following situations:

The application, rather than the database, enforces logical dependencies.

Schema objects have been modified to support the application upgrade, and a DML handler will modify row LCRs to account for differences between the source database and destination database.

A virtual dependency definition is a description of a dependency that is used by an apply process to detect dependencies between transactions at a destination database. A virtual dependency definition is not described as a constraint in the destination database data dictionary. Instead, it is specified using procedures in the DBMS_APPLY_ADM package. Virtual dependency definitions enable an apply process to detect dependencies that it would not be able to detect by using only the constraint information in the data dictionary. After dependencies are detected, an apply process schedules LCRs and transactions in the correct order for apply.

If virtual dependency definitions are required for your application upgrade, then learn about virtual dependency definitions and plan to configure them during the application upgrade.

The PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures in the DBMS_STREAMS_ADM package configure the Oracle Streams replication environment during the upgrade or maintenance operation. These procedures can configure the Oracle Streams replication environment directly, or they can generate a script that configures the environment.

Using a procedure to configure replication directly is simpler than running a script, and the environment is configured immediately. However, you might choose to generate a script for the following reasons:

You want to review the actions performed by the procedure before configuring the environment.

You want to modify the script to customize the configuration.

To configure Oracle Streams directly when you run one of these procedures, set the perform_actions parameter to TRUE. The examples in this appendix assume that the procedures will configure Oracle Streams directly.

To generate a configuration script when you run one of these procedures, complete the following steps when you are instructed to run a procedure in this appendix:

In SQL*Plus, connect as the Oracle Streams administrator to database where you will run the procedure.

Create a directory object to store the script that will be generated by the procedure. For example:

CREATE DIRECTORY scripts_dir AS '/usr/scripts';

Run the procedure. Ensure that the following parameters are set to generate a script:

Set the perform_actions parameter to FALSE.

Set the script_name parameter to the name of the script you want to generate.

Set the script_directory_object parameter to the directory object into which you want to place the script. This directory object was created in Step 2.

Review or modify the script, if necessary.

In SQL*Plus, connect as the Oracle Streams administrator to database where you will run the procedure.

Run the generated script. For example:

@/usr/scripts/pre_instantiation.sql;

Deciding Which Utility to Use for Instantiation

Before you begin the database upgrade or maintenance operation, decide whether you want to use Export/Import utilities (Data Pump or original) or the Recovery Manager (RMAN) utility to instantiate the destination database during the operation. Consider the following factors when you make this decision:

If you are migrating the database to a different platform, then you can use either Export/Import or the RMAN CONVERTDATABASE command. The RMAN DUPLICATE command does not support migrating a database to a different platform.

If you are migrating the database to a different character set, then you must use Export/Import, and the new character set must be a superset of the old character set. The RMAN DUPLICATE and CONVERTDATABASE commands do not support migrating a database to a different character set.

If you are upgrading from a prior release of Oracle Database to Oracle Database 11g Release 2 (11.2), then consider these additional factors:

If RMAN is supported for the operation, then using RMAN for the instantiation might be faster than using Export/Import, especially if the database is large.

Oracle recommends that you do not use RMAN for instantiation in an environment where distributed transactions are possible. Doing so might cause in-doubt transactions that must be corrected manually.

If the RMAN DUPLICATE or CONVERTDATABASE command is used for database instantiation, then the destination database cannot be the capture database.

If you are upgrading from a prior release of Oracle Database to Oracle Database 11g Release 2 (11.2), then consider these additional factors:

If you use Export/Import, then you can make the destination database an Oracle Database 11g Release 2 (11.2) database at the beginning of the operation. Therefore, you do not need to upgrade the destination database after the instantiation.

If you use the RMAN DUPLICATE, then the database release of the destination database must be the same as the source database.

If you use the RMAN CONVERTDATABASE, then the database release of the destination database must be the equal to or later than the source database.

Table D-1 describes when each instantiation method is supported based on whether the platform at the source and destination databases are the same or different, and whether the character set at the source and destination databases are the same or different.

Modifying database schema objects to support upgrades to user-created applications

Applying an Oracle Database software patch or patch set

You can use Oracle Streams to achieve little or no downtime during these operations. During the operation, the source database is the existing database on which you are performing the database operation. The capture database is the database on which the Oracle Streams capture process runs. The destination database is the database that will replace the source database at the end of the operation.

Complete the following tasks to perform a database maintenance operation using Oracle Streams:

Task 1: Beginning the Operation

Complete the following steps to begin the upgrade or maintenance operation using Oracle Streams:

Create an empty destination database. If you are migrating the database to a different platform, then create the database on a computer system that uses the new platform. If you are migrating the database to a different character set, then create a database that uses the new character set.

Ensure that the destination database has a different global name than the source database. This example assumes that the global name of the source database is orcl.example.com and the global name of the destination database during the database maintenance operation is stms.example.com. The global name of the destination database is changed when the destination database replaces the source database at the end of the maintenance operation.

If you are not upgrading from a prior release of Oracle Database, then create an Oracle Database 11g Release 2 (11.2) database. See the Oracle installation guide for your operating system if you must install Oracle, and see the Oracle Database Administrator's Guide for information about creating a database.

This database will be the destination database during the upgrade process. Both the source database that is being upgraded and the destination database must be the same release of Oracle when you start the upgrade process.

See the Oracle installation guide for your operating system if you must install Oracle, and see the Oracle Database Administrator's Guide for the release for information about creating a database.

If you decided to use RMAN CONVERTDATABASE for instantiation, then create an empty Oracle database that is a release equal to or later than the database you are upgrading.

If you are upgrading from a prior release of Oracle Database, then for the source database, see the Oracle Streams documentation for the source database release.

Configure an Oracle Streams administrator at each database, including the source database, destination database, and capture database (if the capture database is a third database). This example assumes that the name of the Oracle Streams administrator is strmadmin at each database.

At the source database, ensure that no changes are made to the database objects that are not supported by Oracle Streams during the upgrade or maintenance operation. To list unsupported database objects, query the DBA_STREAMS_UNSUPPORTED data dictionary view.

In Oracle Database 11g Release 1 (11.1) and later databases, you can use the ALTERTABLE statement with the READONLY clause to make a table read-only.

Task 2: Setting Up Oracle Streams Prior to Instantiation

The specific instructions for setting up Oracle Streams prior to instantiation depend on which database is the capture database. The PRE_INSTANTIATION_SETUP procedure always configures the capture process on the database where it is run. Therefore, this procedure must be run at the capture database.

When the PRE_INSTANTIATION_SETUP procedure is running with the perform_actions parameter set to TRUE, metadata about its configuration actions is recorded in the following data dictionary views: DBA_RECOVERABLE_SCRIPT, DBA_RECOVERABLE_SCRIPT_PARAMS, DBA_RECOVERABLE_SCRIPT_BLOCKS, and DBA_RECOVERABLE_SCRIPT_ERRORS. If the procedure stops because it encounters an error, then you can use the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package to complete the configuration after you correct the conditions that caused the error. These views are not populated if a script is used to configure the replication environment.

Notice that the propagation_name parameter is omitted because a propagation is not necessary when the destination database is the capture database and the downstream capture process and apply process use the same queue at the destination database.

Also, notice that the capture process and apply process will share a queue named streams_q at the destination database.

A Third Database Is the Capture Database

This example assumes that the global name of the third database is thrd.example.com. Complete the following steps to set up Oracle Streams prior to instantiation when a third database is the capture database:

The returned SCN value is specified for the FLASHBACK_SCN Data Pump export parameter in Step 4. Specifying the FLASHBACK_SCN export parameter, or a similar export parameter, ensures that the export is consistent to a single SCN. In this example, assume that the query returned 876606.

After you perform this query, ensure that no DDL changes are made to the objects being exported until after the export is complete.

On a command line, use Data Pump to export the source database.

Perform the export by connecting as an administrative user who is granted EXP_FULL_DATABASE role. This user also must have READ and WRITE privilege on the directory object created in Step 2. This example connects as the Oracle Streams administrator strmadmin.

In SQL*Plus, connect to the destination database as the Oracle Streams administrator.

Create a directory object to hold the import dump file and import log file. For example:

CREATE DIRECTORY dpump_dir AS '/usr/dpump_dir';

Transfer the Data Pump export dump file orc1.dmp to the destination database. You can use the DBMS_FILE_TRANSFER package, binary FTP, or some other method to transfer the file to the destination database. After the file transfer, the export dump file should reside in the directory that corresponds to the directory object created in Step 6.

On a command line at the destination database, use Data Pump to import the export dump file orc1.dmp. Ensure that no changes are made to the database tables until the import is complete. Performing the import automatically sets the instantiation SCN for the destination database and all of its objects.

Perform the import by connecting as an administrative user who is granted IMP_FULL_DATABASE role. This user also must have READ and WRITE privilege on the directory object created in Step 6. This example connects as the Oracle Streams administrator strmadmin.

Instantiating the Database Using the RMAN DUPLICATE Command

If you use the RMAN DUPLICATE command for instantiation on the same platform, then complete the following steps:

Create a backup of the source database if one does not exist. RMAN requires a valid backup for duplication. In this example, create a backup of orcl.example.com if one does not exist.

Note:

A backup of the source database is not necessary if you use the FROMACTIVEDATABASE option when you run the RMAN DUPLICATE command. For large databases, the FROMACTIVEDATABASE option requires significant network resources. This example does not use this option.

In SQL*Plus, connect as an administrative user to the source database. In this example, the source database is orcl.example.com.

Use the RMAN DUPLICATE command with the OPENRESTRICTED option to instantiate the source database at the destination database. The OPENRESTRICTED option is required. This option enables a restricted session in the duplicate database by issuing the following SQL statement: ALTERSYSTEMENABLERESTRICTEDSESSION. RMAN issues this statement immediately before the duplicate database is opened.

You can use the UNTILSCN clause to specify an SCN for the duplication. Use the until SCN determined in Step 3 for this clause. Archived redo logs must be available for the until SCN specified and for higher SCN values. Therefore, Step 4 archived the redo log containing the until SCN.

Ensure that you use TOdatabase_name in the DUPLICATE command to specify the database name of the duplicate database. In this example, the database name of the duplicate database is stms. Therefore, the DUPLICATE command for this example includes TOstms.

In SQL*Plus, connect to the destination database as a system administrator. In this example, the destination database is stms.example.com.

Rename the global name. After an RMAN database instantiation, the destination database has the same global name as the source database, but the destination database must have its original name until the end of the operation. Rename the global name of the destination database back to its original name with the following statement:

ALTER DATABASE RENAME GLOBAL_NAME TO stms.example.com;

If you are upgrading the database from a prior release to Oracle Database 11g Release 2, then upgrade the destination database. See the Oracle Database Upgrade Guide for instructions. If you are not upgrading the database, then skip this step and proceed to the next step.

In SQL*Plus, connect to the destination database as the Oracle Streams administrator.

This database link is required because the POST_INSTANTIATION_SETUP procedure runs the SET_GLOBAL_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package at the destination database, and the SET_GLOBAL_INSTANTIATION_SCN procedure requires the database link.

If the source database and the capture database are the same database, then while still connected as the Oracle Streams administrator in SQL*Plus to the destination database, drop the database link from the source database to the destination database that was cloned from the source database:

Ensure that you use NEWDATABASEdatabase_name in the CONVERTDATABASE command to specify the database name of the destination database. In this example, the database name of the destination database is stms. Therefore, the CONVERTDATABASE command for this example includes NEWDATABASEstms.

The following example is an RMAN CONVERTDATABASE command for a destination database that is running on the LinuxIA(64-bit) platform:

Transfer the data files, PFILE, and SQL script produced by the RMAN CONVERTDATABASE command to the computer system that is running the destination database.

On the computer system that is running the destination database, modify the SQL script so that the destination database always opens with restricted session enabled.

An example script follows with the necessary modifications in bold font:

-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT PFILE='init_00gd2lak_1_0.ora'
CREATE CONTROLFILE REUSE SET DATABASE "STMS" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/tmp/convertdb/archlog1' SIZE 25M,
GROUP 2 '/tmp/convertdb/archlog2' SIZE 25M
DATAFILE
'/tmp/convertdb/systemdf',
'/tmp/convertdb/sysauxdf',
'/tmp/convertdb/datafile1',
'/tmp/convertdb/datafile2',
'/tmp/convertdb/datafile3'
CHARACTER SET WE8DEC
;
-- NOTE: This ALTER SYSTEM statement is added to enable restricted session.ALTER SYSTEM ENABLE RESTRICTED SESSION;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- No tempfile entries found to add.
--
set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt * or the global database name for this database. Use the
prompt * NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SHUTDOWN IMMEDIATE
-- NOTE: This startup has the UPGRADE parameter.-- The startup already has restricted session enabled, so no change is needed.
STARTUP UPGRADE PFILE='init_00gd2lak_1_0.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
-- NOTE: The startup below is generated without the RESTRICT clause.-- Add the RESTRICT clause.
STARTUP RESTRICT PFILE='init_00gd2lak_1_0.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;

Other changes to the script might be necessary. For example, the data file locations and PFILE location might need to be changed to point to the correct locations on the destination database computer system.

In SQL*Plus, connect to the destination database as a system administrator.

Rename the global name. After an RMAN database instantiation, the destination database has the same global name as the source database, but the destination database must have its original name until the end of the maintenance operation. Rename the global name of the destination database back to its original name with the following statement:

ALTER DATABASE RENAME GLOBAL_NAME TO stms.example.com;

If you are upgrading the database from a prior release to Oracle Database 11g Release 2, then upgrade the destination database. See the Oracle Database Upgrade Guide for instructions. If you are not upgrading the database, then skip this step and proceed to the next step.

Connect to the destination database as the Oracle Streams administrator using the new global name.

This database link is required because the POST_INSTANTIATION_SETUP procedure runs the SET_GLOBAL_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package at the destination database, and the SET_GLOBAL_INSTANTIATION_SCN procedure requires the database link.

If the source database and the capture database are the same database, then while still connected as the Oracle Streams administrator in SQL*Plus to the destination database, drop the database link from the source database to the destination database that was cloned from the source database:

The parameter values specified in the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures must match, except for the values of the following parameters: perform_actions, script_name, script_directory_object, and start_processes. In this example, all of the parameter values match in the two procedures.

It is important to set the instantiation_scn parameter in the POST_INSTANTIATION_SETUP procedure correctly. Follow these instructions when you set this parameter:

The source database and third database examples in this section set the instantiation_scn parameter to 748044 for the following reasons:

If the RMAN DUPLICATE command was used for instantiation, then the command duplicates the database up to one less than the SCN value specified in the UNTILSCN clause. Therefore, you should subtract one from the until SCN value that you specified when you ran the DUPLICATE command in Step 7 in "Instantiating the Database Using the RMAN DUPLICATE Command". In this example, the until SCN was set to 748045. Therefore, the instantiation_scn parameter should be set to 748045 - 1, or 748044.

If Export/Import was used for instantiation, then the instantiation SCN was set during import, and the instantiation_scn parameter must be set to NULL. The destination database example in this section sets the instantiation_scn to NULL because RMAN cannot be used for database instantiation when the destination database is the capture database.

The specific instructions for setting up Oracle Streams after instantiation depend on which database is the capture database. Follow the instructions in the appropriate section:

When the POST_INSTANTIATION_SETUP procedure is running with the perform_actions parameter set to TRUE, metadata about its configuration actions is recorded in the following data dictionary views: DBA_RECOVERABLE_SCRIPT, DBA_RECOVERABLE_SCRIPT_PARAMS, DBA_RECOVERABLE_SCRIPT_BLOCKS, and DBA_RECOVERABLE_SCRIPT_ERRORS. If the procedure stops because it encounters an error, then you can use the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package to complete the configuration after you correct the conditions that caused the error. These views are not populated if a script is used to configure the replication environment.

A Third Database Is the Capture Database

This example assumes that the global name of the third database is thrd.example.com. Complete the following steps to set up Oracle Streams after instantiation when a third database is the capture database:

In SQL*Plus, connect to the third database as the Oracle Streams administrator. In this example, the third database is thrd.example.com.

Complete the following steps to finish the upgrade or maintenance operation and remove Oracle Streams components:

At the destination database, disable any imported jobs that modify data that will be replicated from the source database. Query the DBA_JOBS data dictionary view to list the jobs.

If you are applying a patch or patch set, then apply the patch or patch set to the destination database. Follow the instructions included with the patch or patch set. If you are not applying a patch or patch set, then skip this step and proceed to the next step.

If you are upgrading user-created applications, then, at the destination database, you might need to complete the following steps:

Modify the schema objects in the database to support the upgraded user-created applications.

Configure one or more declarative rule-based transformations and procedure DML handlers that modify row LCRs from the source database so that the apply process applies these row LCRs to the modified schema objects correctly. For example, if a column name was changed to support the upgraded user-created applications, then a declarative rule-based transformation should rename the column in a row LCR that involves the column.

When the two values returned by this query are nearly equal, most of the changes from the source database have been applied at the destination database, and you can proceed to the next step. At this point in the process, the values returned by this query might never be equal because the source database still allows changes.

When the state for all apply servers is IDLE, you can proceed to the next step.

Connect to the destination database as the Oracle Streams administrator.

Ensure that there are no apply errors by running the following query:

SELECT COUNT(*) FROM DBA_APPLY_ERROR;

If this query returns zero, then move on to the next step. If this query shows errors in the error queue, then resolve these errors before continuing. See "Managing Apply Errors" for instructions.

Disconnect all applications and users from the source database.

Connect to the source database as an administrative user.

Restrict access to the database. For example:

ALTER SYSTEM ENABLE RESTRICTED SESSION;

While connected as an administrative user in SQL*Plus to the source database, repeat the query you ran in Step 10a. When the two values returned by the query are equal, all of the changes from the source database have been applied at the destination database, and you can move on to the next step.

Connect to the destination database as the Oracle Streams administrator.

Repeat the query you ran in Step 12. If this query returns zero, then move on to the next step. If this query shows errors in the error queue, then resolve these errors before continuing. See "Managing Apply Errors" for instructions.

If you invoked any Oracle-supplied PL/SQL package subprograms at the source database during the upgrade or maintenance operation that modified both user data and dictionary metadata at the same time, then invoke the same subprograms at the destination database. See "Considerations for Job Slaves and PL/SQL Package Subprograms" for more information.

Remove the Oracle Streams components that are no longer needed from both databases, including the ANYDATA queues, supplemental logging specifications, the capture process, the propagation if one exists, and the apply process. Connect as the Oracle Streams administrator in SQL*Plus to the capture database, and run the CLEANUP_INSTANTIATION_SETUP procedure to remove the Oracle Streams components at both databases.

If the capture database is the source database or a third database, then run the following procedure:

Notice that the propagation_name parameter is omitted because a propagation is not necessary when the destination database is the capture database.

Both sample procedures in this step rename the global name of the destination database to orc1.example.com because the change_global_name parameter is set to TRUE.

Shut down the source database. This database should not be opened again.

At the destination database, enable any jobs that you disabled earlier.

Make the destination database available for applications and users. Redirect any applications and users that were connecting to the source database to the destination database. If necessary, reconfigure your network and Oracle Net so that systems that communicated with the source database now communicate with the destination database. See Oracle Database Net Services Administrator's Guide for instructions.

If you no longer need the Oracle Streams administrator at the destination database, then connect as an administrative user in SQL*Plus to the destination database, and run the following statement: