Time for action – converting a physical standby database into a logical standby database

  1. Execute the following special recovery command on the standby database in order to recover it until the SCN that the dictionary was built:
    SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY ORCL2;

Database altered.
  2. At the same time, if you check the standby database alert log you'll see the following lines:
    Media Recovery Log /u01/app/oracle2/archive/1_106_791552282.arc
Media Recovery Log /u01/app/oracle2/archive/1_107_791552282.arc
Incomplete Recovery applied until change 1873735 
Media Recovery Complete (INDIA)
...
RESETLOGS after incomplete recovery UNTIL CHANGE 1873735
Resetting resetlogs activation ID 1319360408 (0x4ea3d798)
standby became primary SCN: 1873733
...
RECOVER TO LOGICAL STANDBY: Complete - Database shutdown required after NID finishes
*** DBNEWID utility started ***
DBID will be changed from 1319333016 to new DBID of 773141456 for database ORCL
DBNAME will be changed from ORCL to new DBNAME of ORCL2
Starting datafile conversion
Datafile conversion complete
Database name changed to ORCL2.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL2 changed to 773141456.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open with RESETLOGS option.
Succesfully changed database name and ID.
*** DBNEWID utility finished succesfully ***
Completed: ALTER DATABASE RECOVER TO LOGICAL STANDBY ORCL2

    We can see that the MRP applied the changes until a specific SCN. This SCN is the point at which the LogMiner dictionary was built. Then the standby database was activated and became the primary database. The rest of the lines show the process of changing the DB_NAME of the database. If you look at the recovery command, you'll see that we specified the name ORCL2 at the end. The database name needs to be changed for the physical standby database to become a logical standby and ORCL2 will be the new name of the standby database. All of these changes were applied to the database by the recovery command we ran.

    Tip

    In the alert log, we can see the following line:

    modify parameter file and generate a new password file before restarting.

    If spfile is being used, the DB_NAME parameter will be changed automatically after this command. If pfile is in use, we need to manually change the DB_NAME to the new value in the init.ora file.

    Prior to 11g it was necessary to create a new password file, but it's not required in 11g. So we can ignore this line of the alert.log.

  3. If the standby database is RAC, we can enable the cluster again using the following query:
    SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
SQL> SHUTDOWN;
SQL> STARTUP MOUNT;
  4. There are two kinds of archived redo logfiles on the logical standby databases. The first one is created from the online redo logs and the second is created from the standby redo logs. We'll create separate destinations for these archived logfiles using the following query:
    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archive VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=INDIA_UN';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=TURKEY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TURKEY_UN'; 

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/archive_std VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=INDIA_UN';

System altered.

    Here, the first destination will be used for archiving the online redo logs of the logical standby database. The second destination was already set in physical standby setup and was defined in order to be used in a switchover (PRIMARY_ROLE option is used). The last destination, LOG_ARCHIVE_DEST_3 will be used for archiving the standby redo logs that contains the redo generated and transferred from the primary database.

  5. We used specific and different destinations for the archived logs for a better understanding in this example. However, using fast recovery area for this purpose with the LOCATION=USE_DB_RECOVERY_FILE_DEST option is a good practice. In Oracle 10g, the logical standby database was not supported to keep the foreign archived logfiles (archived logs that were generated from standby redo logs) in the flash recovery area (FRA). In 11g, this is supported. In order to use FRA for archiving, you should first enable FRA by setting the following parameters:
    SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=10G;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/U01/APP/ORACLE/FRA';
  6. Then set LOG_ARCHIVE_DEST_1 as follows:
    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
  7. LOG_ARCHIVE_DEST_1 will be enough to archive both online and standby logfiles and we will not need LOG_ARCHIVE_DEST_3 in this case. The directory structure will be automatically created as follows:
    /u01/app/oracle2/fra/INDIA_UN/foreign_archivelog à for the files archived from standby logs
/u01/app/oracle2/fra/INDIA_UN/archivelog à for the files archivedfrom online logs
  8. Now restart the standby database and open it with the resetlogs option as shown in the following query:
    SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

    The database is now read/write opened for user connections. We only need to start SQL Apply to finish the logical standby configuration.

  9. Start SQL Apply on the logical standby database by executing the following statement:
    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

Let's check what happened behind when we executed this statement, by reading the alert logfile for the standby database as follows:

alter database start logical standby apply immediate
LOGSTDBY: Creating new session for dbid 1319333016 starting at scn 0x0000.00000000
LOGSTDBY: Created session of id 1
...
LSP0 started with pid=33, OS id=15629 
Completed: alter database start logical standby apply immediate
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
LOGMINER: SpillScn 0, ResetLogScn 0

When the statement executed, a new session was created for the SQL Apply, and then the LSP0 process was started, which is the Logical Standby Coordinator Process responsible for managing the LogMiner and Apply processes. Along with LSP0, miner processes were also started.

LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 105, /u01/app/oracle/archive_std/1_105_791552282.arc
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 3 seq# 3 mem# 0: /u01/app/oracle2/datafile/ORCL/redo03.log
Archived Log entry 2 added for thread 1 sequence 2 ID 0x2e14f3f9 dest 1
LOGMINER: End mining logfiles during dictionary load for session 1

At this point, we can see that SQL Apply mines the redo in order to find the dictionary and build it on the standby. If it's not able to find the necessary archived log sequences, it requests them from the primary database.

RFS LogMiner: Registered logfile [/u01/app/oracle/archive_std/1_106_791552282.arc] to LogMiner session id [1]
...
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 106, /u01/app/oracle/archive_std/1_106_791552282.arc
LOGMINER: End   mining logfile for session 1 thread 1 sequence 106, /u01/app/oracle/archive_std/1_106_791552282.arc

Now the configuration is over and logical standby starts the apply processes and applies all the logs to be synchronized with the primary database.

What just happened?

We have finished all the required steps to create a logical standby database. Now it's time to verify if the logical standby services are working properly.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.226.251.70