This is a simple article on how to convert physical standby to logical standby.
A metalink note ID is provided in the reference section. I used the same metalink note and did the conversion. I will brief the steps that I have done and the outputs of those steps.
Pre-reqs before starting the setup – You have a physical standby functioning correctly and that all the redo logs sequences on primary has been applied on standby
I will list the steps which needs to be done on primary as well as on standby
On standby database
1. Make sure that all the sequences generated on primary side has been applied on standby.
SQL to check that is given below
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
2 FROM
3 (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
4 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
5 WHERE
6 ARCH.THREAD# = APPL.THREAD#
7 ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied
---------- ---------------------- ---------------------
1 177 177
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered.
SQL> show parameters archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
log_archive_config string
log_archive_dest string
log_archive_dest_1 string location=/slot/ems3079/oracle/
db_1/arch/sitst01/
log_archive_dest_10 string
log_archive_dest_2 string service=sitst02
log_archive_dest_3 string
On primary set the VALID_FOR parameter to make destination 1 as valid for online redo logs and role as all roles. This mean even if we change the role of primary server to standby server, online redo logs will get archived to archive destination 1.
SQL> alter system set log_archive_dest_1='location=/slot/ems3079/oracle/db_1/arch/sitst01/ valid_for=(ONLINE_LOGFILES,ALL_ROLES)';
System altered.
-bash-3.00$ mkdir -p /slot/ems3079/oracle/db_1/arch/standby/
-bash-3.00$ sqlplus "/as sysdba"
SQL> alter system set LOG_ARCHIVE_DEST_3='LOCATION=/slot/ems3079/oracle/db_1/arch/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)';
System altered.
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY testdb;ALTER DATABASE RECOVER TO LOGICAL STANDBY standby*ERROR at line 1:ORA-16254: change db_name to TESTDB in the client-side parameter file (pfile)ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3
SQL> shut immediateORA-01507: database not mountedORACLE instance shut down.SQL>change following parameters in init.ora*.db_name='testdb'db_unique_name='testdb'
SQL> STARTUP MOUNT pfile=initsitst02.ora ORACLE instance started. Total System Global Area 419430400 bytes Fixed Size 1267812 bytes Variable Size 130025372 bytes Database Buffers 281018368 bytes Redo Buffers 7118848 bytes Database mounted. SQL>
SQL> alter system set log_archive_dest_1='LOCATION=/slot/ems6826/oracle/db_1/dbs/arch/sitst02 VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)';System altered.SQL> alter system set LOG_ARCHIVE_DEST_2='service=sitst01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)';System altered.SQL> alter system set LOG_ARCHIVE_DEST_3='LOCATION=/slot/ems6826/oracle/db_1/dbs/arch/standby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)';System altered.
VALID_FOR Definition | Primary Role | Physical Standby Role | Logical Standby Role |
---|---|---|---|
|
Active |
Inactive |
Invalid |
|
Inactive |
Invalid |
Active |
|
Active |
Invalid |
Active |
|
Error |
Error |
Error |
|
Invalid |
Active |
Active |
|
Invalid |
Active |
Active |
|
Active |
Inactive |
Invalid |
|
Invalid |
Active |
Active |
|
Active |
Active |
Active |
SQL> ALTER DATABASE OPEN RESETLOGS; Database altered.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE*ERROR at line 1:ORA-16239: IMMEDIATE option not available without standby redo logsWe got the above error, because we didn't create standby redo log filesCreate standby redo logs as shown below.
SQL> alter database add standby logfile group 4 ('/slot/ems6826/oracle/oradata/sitst02/redo4.dbf') size 100M; Database altered. SQL> alter database add standby logfile group 5 ('/slot/ems6826/oracle/oradata/sitst02/redo5.dbf') size 100M; Database altered. SQL> alter database add standby logfile group 6 ('/slot/ems6826/oracle/oradata/sitst02/redo6.dbf') size 100M; Database altered. SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; Database altered.
SQL> select name, database_role from v$database;NAME DATABASE_ROLE--------- ----------------STANDBY LOGICAL STANDBY
SQL> select DEST_ID, THREAD#, SEQUENCE#, ARCHIVED, APPLIED from v$archived_log where DEST_ID = 2 ;DEST_ID THREAD# SEQUENCE# ARC APP---------- ---------- ---------- --- ---2 1 179 YES YES2 1 181 YES YES2 1 182 YES YES2 1 183 YES YES2 1 184 YES YES2 1 185 YES YES2 1 186 YES YES2 1 187 YES YES2 1 188 YES YES2 1 189 YES YES2 1 190 YES NOOn standbySQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME DIC DIC---------- ------------------ ------------------ --- ---189 02-MAY-10 02:17:59 02-MAY-10 02:20:32 NO NO190 02-MAY-10 02:20:32 02-MAY-10 02:20:33 NO NO
SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;
TYPE HIGH_SCN STATUS------------------------------ ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------COORDINATOR 5846054 ORA-16116: no work availableREADER 5846054 ORA-16240: Waiting for logfile (thread# 1, sequence# 191)BUILDER 5846050 ORA-16116: no work availablePREPARER 5846049 ORA-16116: no work availableANALYZER 5845790 ORA-16117: processingAPPLIER 5845733 ORA-16116: no work availableAPPLIER 5845739 ORA-16116: no work availableAPPLIER 5845784 ORA-16116: no work availableAPPLIER 5845790 ORA-16116: no work availableAPPLIER 5845429 ORA-16116: no work available
SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIME, COMMIT_SCN;
EVENT_TIM---------STATUS--------------------------------------------------------------------------------EVENT--------------------------------------------------------------------------------02-MAY-10ORA-16111: log mining and apply setting up
SQL> SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#, TIMESTAMP, DICT_BEGIN, DICT_END, THREAD# FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
FILE_NAME--------------------------------------------------------------------------------SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP DIC DIC THREAD#---------- ------------- ------------ --------- --- --- ----------/slot/ems6826/oracle/db_1/dbs/arch/standby/1_189_713333254.dbf189 5845702 5845832 02-MAY-10 NO NO 1
/slot/ems6826/oracle/db_1/dbs/arch/standby/1_190_713333254.dbf190 5845832 5846054 02-MAY-10 NO NO 1
SQL> SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
Session altered.
SQL> SQL> SELECT L.SEQUENCE#, L.FIRST_TIME, (CASE WHEN L.NEXT_CHANGE# < P.READ_SCN THEN 'YES'WHEN L.FIRST_CHANGE# < P.APPLIED_SCN THEN 'CURRENT' ELSE 'NO' END) APPLIED FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME APPLIED---------- ------------------ -------189 02-MAY-10 02:17:59 CURRENT190 02-MAY-10 02:20:32 CURRENT
SQL> SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'coordinator%' or NAME LIKE 'transactions%';
NAME----------------------------------------------------------------VALUE----------------------------------------------------------------coordinator stateIDLE
transactions ready26
transactions applied26
NAME----------------------------------------------------------------VALUE----------------------------------------------------------------coordinator uptime564References:Metalink note ID : 738643.1