SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY ORCL2; Database altered.
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.
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
.
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE; SQL> SHUTDOWN; SQL> STARTUP MOUNT;
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.
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';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
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
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.
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.