Manually create physical standby DG ,Switchover, Convert to Logical standby(Scripts and Short Descriptions)

The short post just as Outline ,be just as expressive,record some of my Configure DG method.The following example contains:
Manually create physical standby with rman;
Primary role and Primary standby role switchover;
Converting Physical Standby to Logical Standby ;
1,create physical standby with rman
Enable Forced Logging

Create a Password File

orapwd file=orapwicme password=oracle entries=10 ignorecase=y

Enable Archiving

shutdown immediate;
startup mount;
alter database archivelog;

Configure tnsnames on primary and standby both.
vi $ORACLE_HOME/network/admin/tnsnames.ora
In my case primary db IP is 192.168.168.230 and standby db IP 192.168.168.231. (tnsnames can be different service_name,tnsnames will be used in log_archive_dest_N SERVICE AND fal_client or fal_server)

primarydb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.230)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pora230)
)
)
standbydb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.231)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sora231)
)
)

Set Primary Database Initialization Parameters

alter system set log_archive_config='DG_CONFIG=(pora230,sora231)';
alter system set log_archive_dest_1='LOCATION=/data/oracle/pora230/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pora230';
alter system set log_archive_dest_2='SERVICE=standbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sora231';
alter system set fal_client=primarydb;
alter system set fal_server=standbydb;
alter system set log_archive_format='%d_%t_%s_%r.arc' scope=spfile;
alter system set DB_FILE_NAME_CONVERT='/oradata/sora231/','/oracle/oradata/pora230/' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/oradata/sora231/','/oracle/oradata/pora230/' scope=spfile;
alter system set  STANDBY_FILE_MANAGEMENT=AUTO;

Configure a Standby Redo Log
A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone.
The synchronous and asynchronous redo transport modes require that a redo transport destination have a standby redo log. A standby redo log is used to store redo received from another Oracle database.Each standby redo log file must be at least as large as the largest redo log file in the redo log of the redo source database.The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database.
for example.

ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/oradata/icme/slog1.rdo') SIZE 100M;

CREATE Standby Controlfile

alter database CREATE STANDBY CONTROLFILE AS '/tmp/control01.ctl';

CREATE Standby init file;

SQL> create pfile from spfile;

BACKUP DATABASE ON Primary DB

backup database format '/oradata/oracle/full_%d_%T_%s' ;

#scp control ,pfile ,rman backup to standby db
#################Summary of PRIMARY db site############################

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /oradata/sora231/, /oracle/oradata/pora230/
db_name string pora230
db_unique_name string PORA230
global_names boolean FALSE
instance_name string pora230
lock_name_space string
log_file_name_convert string /oradata/sora231/, /oracle/oradata/pora230/
service_names string PORA230
log_archive_dest_1 string LOCATION=/data/oracle/pora230/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pora230
log_archive_dest_10 string
log_archive_dest_2 string SERVICE=standbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sora231
log_archive_dest_3 string LOCATION=/data/oracle/pora230/
lgarchivelog/ VALID_FOR=(STAND
BY_LOGFILES,STANDBY_ROLE) DB_U
NIQUE_NAME=pora230

####################Summary of standby db site############################
#modify pfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /oracle/oradata/pora230/, /oradata/sora231/
db_name string STDB
db_unique_name string sora231
global_names boolean FALSE
instance_name string sora231
lock_name_space string
log_file_name_convert string /oracle/oradata/pora230/, /oradata/sora231/
service_names string sora231
log_archive_dest_1 string LOCATION=/oradata/sora231/archlog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sora231
log_archive_dest_10 string
log_archive_dest_2 string SERVICE=primarydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pora230
log_archive_dest_3 string LOCATION=/oradata/sora231/stbarchlog/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=sora231

 
##########################################################

ON standby db:

create pfile from spfile;

-- or manual create pfile just add "db_name=xxx" ,startup nomount ,create pfile from memory;
move standby control file to specified path in standby spfile or pfile

startup mount;

#restore standby database from rman backupset of primary db

rman target /
catalog backuppiece '/home/oracle/full_PORA230_20130521_1';
catalog backuppiece '/home/oracle/full_PORA230_20130521_2';
RMAN> restore database;
Starting restore at 2013-05-22 03:32:43
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/sora231/system01.dbf
restoring datafile 00002 to /oradata/sora231/undotbs01.dbf
restoring datafile 00003 to /oradata/sora231/sysaux01.dbf
restoring datafile 00004 to /oradata/sora231/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/full_PORA230_20130521_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/full_PORA230_20130521_1 tag=TAG20130521T164617
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2013-05-22 03:32:51

-- if hava backup archivelog backupset

restore archivelog all;
alter database recover managed standby database disconnect from session;

############# some manage commands about redo apply #####

–start redo apply
alter database recover managed standby database disconnect from session;
–start real-time redo apply
alter database recover managed standby database using current logfile disconnect from session;
--stop redo apply
alter database recover managed standby database cancel;

############# Some scripts used for check datagard #####

select t.*,arched-applied gap,sysdate etime  from (select thread#,max(sequence#) arched, max(decode(applied,'YES',sequence#,1)) applied, max(decode(DELETED,'YES',sequence#,1)) DELETED 
from v$archived_log group by thread#) t;		 
select name,value,unit,time_computed from v$dataguard_stats;
select process,pid,status,thread#,sequence#,delay_mins from v$managed_standby; 
select to_char(start_time,'yyyymmdd hh24:mi') start_time,type,item,units,total, to_char(timestamp,'yyyymmdd hh24:mi') timestap from v$recovery_progress; 
select * from GV$ARCHIVE_GAP;

SWITCH OVER

# PRIMARY SWITCH TO PRIMARY STANDBY

select db_unique_name,switchover_status,database_role from v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE
------------------------------ -------------------- ----------------
pora230 TO STANDBY PRIMARY
alter database commit to switchover to physical standby;
Database altered.
# PHYSICAL STANDBY SWITCH TO PRIMARY (Make Sure SWITCHOVER_STATUS is "TO PRIMARY")
select db_unique_name,switchover_status,database_role from v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE
------------------------------ -------------------- ----------------
sora231 SESSIONS ACTIVE PHYSICAL STANDBY
select db_unique_name,switchover_status,database_role from v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE
------------------------------ -------------------- ----------------
sora231 TO PRIMARY PHYSICAL STANDBY
alter database commit to switchover to primary;
select db_unique_name,switchover_status,database_role ,open_mode from v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE
------------------------------ -------------------- ---------------- ----------
sora231 TO STANDBY PRIMARY MOUNTED
alter database open;
Database altered.
select db_unique_name,switchover_status,database_role ,open_mode from v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE
------------------------------ -------------------- ---------------- ----------
sora231 NOT ALLOWED PRIMARY READ WRITE

#Switch back method same above.

Converting Physical Standby to Logical Standby

# some check UNSUPPORTED with logical standby

SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = 'Y';
select * from DBA_LOGSTDBY_UNSUPPORTED;
# ON PRIMARY DB(Modify some initialization paramters ,Do not need to restart the database)
alter system SET log_archive_dest_3='LOCATION=/data/oracle/pora230/lgarchivelog/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=pora230';
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
ALTER SYSTEM SWITCH LOGFILE;
create tablespace tbs_logidg datafile '/oracle/oradata/pora230/tbs_logidg01.dbf' size 20m autoextend on maxsize 5g;
EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('TBS_LOGIDG');
EXECUTE DBMS_LOGSTDBY.BUILD;
# ON Physical standby db
alter database recover managed standby database cancel;
#Converting Physical Standby to Logical Standby
ALTER DATABASE RECOVER TO LOGICAL STANDBY stdb;

tip:
if the step is hang ,try do EXECUTE DBMS_LOGSTDBY.BUILD again on primary database
# re-create password
[oracle@db231 dbs]$ orapwd file=orapwsora231 entries=5 password=oracle

SQL> SHUTDOWN;
SQL> STARTUP MOUNT;
alter system set log_archive_dest_1='LOCATION=/oradata/sora231/archlog/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sora231';
alter system set log_archive_dest_3='LOCATION=/oradata/sora231/stbarchlog/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=sora231';
idle>ALTER DATABASE OPEN RESETLOGS;

# Stop or Start the SQL apply on logical standby

alter database stop logical standby apply;
alter database start logical standby apply immediate;
sys@STDB>select db_unique_name,switchover_status,database_role ,open_mode from v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE
------------------------------ -------------------- ---------------- ----------
sora231 NOT ALLOWED LOGICAL STANDBY READ WRITE

##################### know issue ##########
1,DB_FILE_NAME_CONVERT
Note:
The DB_FILE_NAME_CONVERT initialization parameter is not honored once a physical standby database is converted to a logical standby database. If necessary, you should register a
skip handler and provide SQL Apply with a replacement DDL string to execute by converting the path names of the primary database datafiles to the standby datafile path names.
See theDBMS_LOGSTDBYpackage inOracle Database PL/SQL Packages and Types Reference. for information about the SKIP procedure.

SQL> alter database stop logical standby apply;
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(stmt=>'CREATE TABLESPACE', schema_name=>'SYS', object_name=>'%');
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(stmt=>'DROP TABLESPACE', schema_name=>'SYS', object_name=>'%');
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

or

SQL> alter database stop logical standby apply;
SQL> alter session disable guard;
SQL> create tablespace ts_test1 datafile '/orahome/oradata/LOGSTDBY/ts_test1.dbf' size 10m reuse;
SQL> drop tablespace ts_test2 including contents and datafiles cascade constraints;
SQL> alter session enable guard;
SQL> alter database start logical standby apply immediate skip failed transaction;

or

SQL> alter database start logical standby apply immediate;

2,Privileges
# Apply fails with ORA-01031: Insufficient Privileges
# ON STANDBY CREATE TABLE

ALTER DATABASE STOP LOGICAL STANDBY APPLY;
ALTER SESSION DISABLE GUARD;
create table xxx
ALTER SESSION ENABLE GUARD;
ALTER DATABASE START LOGICAL STANDBY APPLY;

OR

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL',-
schema_name => 'HR', -
object_name => 'TESTEMP%');
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGSTDBY.SKIP('DML','HR','TESTEMP%');
PL/SQL procedure successfully completed.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.

# modify guard_status

sys@STDB>select db_unique_name,switchover_status,database_role ,open_mode, guard_status from v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE GUARD_S
------------------------------ -------------------- ---------------- ---------- -------
sora231 NOT ALLOWED LOGICAL STANDBY READ WRITE ALL
sys@STDB>alter database guard standby;
Database altered.
sys@STDB>select db_unique_name,switchover_status,database_role ,open_mode, guard_status from v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE GUARD_S
------------------------------ -------------------- ---------------- ---------- -------
sora231 NOT ALLOWED LOGICAL STANDBY READ WRITE STANDBY

3,Materialized view
New materialized views that are created, altered, or dropped on the primary database after the logical standby database has been created will not be created on the logical standby database. However, materialized views created on the primary database prior to the logical standby database being created will be present on the logical standby database.
Logical Standby supports the creation and maintenance of new materialized views locally on the logical standby database in addition to other kinds of auxiliary data structure.
IF An ON-DEMAND fast refreshe MView in logical standby database has been created before physical standby convert to logical standby, when primary db has DML On had mview table,on logical standby Materialized view logs are synchronized, but exec dbms_mview.refresh Mview on primary db , Materialized view is not refreshed on standby side. you need to manually refresh the standby side or create a job.
## Some frequently used check commands about Logical standby db #####

SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS FCHANGE#, -
     NEXT_CHANGE# AS NCHANGE#, TIMESTAMP, -
     DICT_BEGIN AS BEG, DICT_END AS END, -
     THREAD# AS THR# ,applied FROM DBA_LOGSTDBY_LOG -
     ORDER BY SEQUENCE#;
COLUMN LID FORMAT 9999
COLUMN SERIAL# FORMAT 9999
COLUMN SID FORMAT 9999
SELECT SID, SERIAL#, LOGSTDBY_ID AS LID, SPID, TYPE, HIGH_SCN FROM V$LOGSTDBY_PROCESS;
SELECT TYPE, STATUS_CODE, STATUS FROM V$LOGSTDBY_PROCESS;
select * from v$logstdby_state;

#related views

    DBA_LOGSTDBY_EVENTS View
    DBA_LOGSTDBY_LOG View
    V$LOGSTDBY_STATS View
    V$LOGSTDBY_PROCESS View
    V$LOGSTDBY_PROGRESS View
    V$LOGSTDBY_STATE View
    V$LOGSTDBY_STATS View

# to check if redo apply enabled use "v$archive_dest_status" On standby

select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

-- update 20180823 --
for example to fix archivelog miss

RMAN> run{
allocate channel ch1 type 'SBT_TAPE';
restore archivelog from sequence 268797  until sequence 268803 thread 2;
release channel ch1;
}
RMAN> list backup of archivelog sequence between 268795  and 268803 thread 2;
RMAN> backup as copy archivelog sequence between 202430  and 202440 format '+datadg' delete input;	
RMAN> delete archivelog sequence between 202430  and 202440 backed up 1 times to device type SBT_TAPE;
SQL> select t.*,arched-applied gap,sysdate etime  from (select thread#,max(sequence#) arched, max(decode(applied,'YES',sequence#,1)) applied from v$archived_log group by thread#) t;
   THREAD#     ARCHED    APPLIED        GAP ETIME
---------- ---------- ---------- ---------- -----------------
         1     202571     202326        245 20180821 20:02:41
         2     269297     268835        462 20180821 20:02:41
RMAN> delete archivelog sequence between 202306  and 202326 thread 1;
RMAN> delete archivelog sequence between 268790  and 268835 thread 2;
		 
SQL>select t.*,arched-applied gap,sysdate etime  from (select thread#,max(sequence#) arched, max(decode(applied,'YES',sequence#,1)) applied, max(decode(DELETED,'YES',sequence#,1)) DELETED from v$archived_log group by thread#) t;