Creation of LOGICAL STANDBY on standalone database 12c

Creation of  LOGICAL STANDBY on standalone database 12c

Step-1 > On Standby First we have to cancel the recovery process

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Step-2 > Add the log_archive_dest_3 at primary database for future , when you make primary database to  logical standby SQL>ALTER SYSTEM SET log_archive_dest_3='LOCATION=/u01/app/oracle/oradata/archivelog/ VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=orcl'; Step-3 > Build a Dictionary in the Redo Data SQL> EXECUTE DBMS_LOGSTDBY.BUILD; The DBMS_LOGSTDBY.BUILD procedure uses Flashback Query to obtain a consistent snapshot  of the data dictionary that is then logged in the redo stream. Oracle recommends setting the UNDO_RETENTION  initialization parameter to 3600 on both the primary and logical standby databases. Step-4 > To continue applying redo data to the physical standby database until it is ready to convert to a  logical standby database , issue the following SQL statement: SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY orcldr; Step-5> Create a new password File  Step-6> On the logical standby database, shutdown the instance and issue the STARTUP MOUNT statement to start and mount the database. Do not open the database; it should remain closed to user access   until later in the creation process In pfile Change the ------------(db_name=orcldr) SQL> Startup mount pfile=’?/dbs/pfile.ora’ SQL> create spfile from pfile='?/dbs/pfile.ora'; SQL> shut immediate SQL> startup mount Step-7 > Add the log_archive_dest_3 at Logical standby database   SQL> ALTER SYSTEM SET log_archive_dest_3 ='LOCATION=/u01/app/oracle/oradata/archivelog/   VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=orcldr'; Step-8 > Open the Logical Database  SQL> ALTER DATABASE OPEN RESETLOGS; Step-9 > Issue the following statement to begin applying redo data to the logical standby database SQL>  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Dataguard : Switchover from Pluggable primary to standby databse 12c

• Points to be noted during switchover

1. Verify the primary database instance is open and the standby database instance is mounted

2. Verify there are no active users connected to the databases  SELECT username, status, logon_time   FROM v$session  WHERE status = 'ACTIVE'

3.Make sure the last redo data transmitted from the Primary database was applied on the standby database

Issue the following commands on Primary database and Standby database to find out redo's applied or not. SQL>select sequence#, applied from v$archvied_log; SQL>select sequence#, applied from v$archvied_log; (check on physical Standby) In order to apply redo data to the standby database as soon as it is received, use Real-time apply Step -1 > Command to execute on primary for Switchover to Standby  SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; Step-2> After step 1 finishes, Switch the original physical standby db orcldb to primary role; SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; Step-3> shut down and restart the former primary instance orcl SQL>SHUTDOWN IMMEDIATE; SQL>STARTUP MOUNT; Step-4 > After step 3 completes Note- If you are using oracle 10 g release 1 than you need to shutdown and restart the new primary  database orcldr SQL>SHUTDOWN IMMEDIATE; SQL>STARTUP; OR----- If you are using oracle 10 g release 2 than you need to open only the new primary  database orcldr SQL>ALTER DATABASE OPEN;  Step-4 > To Start the Recovery of new Standby ORCL SQL>alter database recover managed standby database disconnect from session; Step-5 > On the new primary database orcldr, perform a SWITCH LOGFILE to start sending redo  data to the standby database ORCL SQL>ALTER SYSTEM SWITCH LOGFILE; ------------------------------------------------------------------------------------------------------------- Switchover from Pluggable primary to standby database  example  Primary Instance  : rac1,rac2 Physical standby Instance : racdr In this example we have 2 node RAC with non rac DR physical standby database Check archive log sync between primary and standby database script used standby.sql and d.sql during switchover    select sequence#,applied from v$archived_log order by sequence# asc; SQL> !cat standby.sql select name,open_mode,database_role from v$database; select process,status,sequence# from v$managed_standby; SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (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, (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 WHERE ARCH.THREAD# = APPL.THREAD#; SQL> !cat d.sql set lines 200 select name, instance_name,db_unique_name, open_mode, database_role, flashback_on  current_scn,to_char(standby_became_primary_scn) failover_scn from v$database,v$instance; SQL> @d NAME   INSTANCE_NAME    DB_UNIQUE_NAME   OPEN_MODE        DATABASE_ROLE CURRENT_SCN    FAILOVER_SCN --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ---------------------------------------- RAC   rac1    rac   READ WRITE        PRIMARY NO    0 SQL> @standby                                                                       NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   READ WRITE        PRIMARY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- ARCH   CLOSING        57 ARCH   CLOSING        58 ARCH   CLOSING        56 ARCH   OPENING        47 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 LNS   CONNECTED 0 DGRD   ALLOCATED 0 LNS   WRITING        59 DGRD   ALLOCATED 0 10 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ----------  1        58      58   0  1        58      58   0  2        52      52   0  2        52      52   0 Switchover command for converting primary database to standby database  SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; Database altered. Shutdown database and start old primary in mount stage ( In case of RAC we can start srvctl start database -d rac -o mount ) , Here when type above command both instance 1 and 2 were shutdown.)  SQL> SHUTDOWN IMMEDIATE; ORA-01012: not logged on SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@srv1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 16 15:38:28 2021 Copyright (c) 1982, 2016, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 2432696320 bytes Fixed Size     8795664 bytes Variable Size   805308912 bytes Database Buffers  1610612736 bytes Redo Buffers     7979008 bytes Database mounted. SQL> @d NAME   INSTANCE_NAME    DB_UNIQUE_NAME   OPEN_MODE        DATABASE_ROLE CURRENT_SCN    FAILOVER_SCN --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ---------------------------------------- RAC   rac1    rac   MOUNTED        PHYSICAL STANDBY NO    0 Now apply switchover command for converting  standby database to primary database SQL> @standby NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   MOUNTED        PHYSICAL STANDBY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- ARCH   CLOSING        47 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 ARCH   CLOSING        58 ARCH   CLOSING        52 ARCH   CLOSING        57 RFS   IDLE 0 RFS   IDLE        59 RFS   IDLE 0 RFS   IDLE 0 RFS   IDLE        53 PROCESS   STATUS SEQUENCE# --------- ------------ ---------- RFS   IDLE 0 RFS   IDLE 0 RFS   IDLE 0 MRP0   APPLYING_LOG        53 15 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1        58      58 0 2        52      52 0 SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; Database altered. SQL> @d NAME   INSTANCE_NAME    DB_UNIQUE_NAME   OPEN_MODE        DATABASE_ROLE CURRENT_SCN    FAILOVER_SCN --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ---------------------------------------- RAC   racdr    racdr   MOUNTED        PRIMARY YES    3788307 Shutdown new primary database and start database in normal state SQL> SHUTDOWN IMMEDIATE; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2432696320 bytes Fixed Size     8795664 bytes Variable Size   738200048 bytes Database Buffers 1677721600 bytes Redo Buffers     7979008 bytes Database mounted. Database opened. SQL> @d NAME   INSTANCE_NAME    DB_UNIQUE_NAME   OPEN_MODE        DATABASE_ROLE CURRENT_SCN    FAILOVER_SCN --------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ---------------------------------------- RAC   racdr    racdr   READ WRITE        PRIMARY YES    3788307 Forcefully switching  log file for testing purpose so we can confirm MRP0 process running fine on new DR  SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> @standby NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   READ WRITE        PRIMARY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- DGRD   ALLOCATED 0 ARCH   CLOSING        64 DGRD   ALLOCATED 0 ARCH   CLOSING        61 ARCH   CLOSING        62 ARCH   CLOSING        63 LNS   WRITING        65 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 9 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1        64      64 0 1        64      64 0 2        54      54 0 2        54      54 0 Check new standby database and appy mediaa recovery  SQL> @standby NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   MOUNTED        PHYSICAL STANDBY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- ARCH   CONNECTED 0 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 ARCH   CLOSING        61 ARCH   CONNECTED 0 ARCH   CONNECTED 0 RFS   IDLE        62 RFS   IDLE 0 RFS   IDLE 0 RFS   IDLE 0 10 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ----------  1        61      58   3  2        54      52   2 Apply Media recovery process on new standby database. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> @standby NAME   OPEN_MODE        DATABASE_ROLE --------- -------------------- ---------------- RAC   MOUNTED        PHYSICAL STANDBY PROCESS   STATUS SEQUENCE# --------- ------------ ---------- ARCH   CONNECTED 0 DGRD   ALLOCATED 0 DGRD   ALLOCATED 0 ARCH   CLOSING        64 ARCH   CLOSING        62 ARCH   CLOSING        63 RFS   IDLE        65 RFS   IDLE 0 RFS   IDLE 0 RFS   IDLE 0 MRP0   APPLYING_LOG        65 11 rows selected.     Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ----------  1        64      64   0  2        54      54   0 New Primary PDB database SQL> sho pdbs     CON_ID CON_NAME   OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED   READ ONLY  NO 3 RACPDB   MOUNTED SQL> alter pluggable database all open; Pluggable database altered. SQL> sho pdbs     CON_ID CON_NAME   OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED   READ ONLY  NO 3 RACPDB   READ WRITE NO SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE; Pluggable database altered. SQL> !hostname srv4.example.com SQL>  New Physical Standby database  SQL> sho pdbs     CON_ID CON_NAME   OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED   MOUNTED 3 RACPDB   MOUNTED SQL> !hostname srv1.example.com SQL>

Creation of Physical Standby 12c

Questions to Ask before Creation of Standby

Ques1- OS version and DB version should be same

Ques2-Mount Points are same or Difference

Ques3-Same User and Groups  should be there as Primary

Oues4-Permissions should be same as Primary

Ques5-Physical or Logical

Ques6-Purpose of Making standby

Ques7-Which Mode(maximum Performance, maximum protection, maximum availability)

Ques8-Standard or Enterprise Edition(In standard archive should be send Manually)

Ques9-time gap between primary and standby.

PHYSICAL  STANDBY

Primary Database                                                       Standby Database

Name-ORCL                                                               Name-ORCLDR

step-I > Enable Forced Logging

This option ensure that  even in the event that a 'nologging' operation is done, force logging

takes precedence and all operations are logged into the redo logs.

SQL> ALTER DATABASE FORCE LOGGING;

step-2 > Create a Password File

A password file must be created on the Primary and copied over to the Standby site. The sys password

must be identical on both sites.

[oracle@localhost u01]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=sys123 force=y

To check whether password file is there or not, if not there then create it as above.

SQL> select * from v$pwfile_users;

Check the size of online redo logs

SQL> select group#, bytes/1024/1024 mb_size from v$log;

step-3 > Configure a Standby Redo Log

Standby Redo log is added to enable Data Guard Maximum Availability and Maximum

protection modes. It is important to configure the Standby Redo Logs (SRL)

with the same size as the online redo logs.

(maximum number of logfiles for each thread + 1) * maximum number of threads

ALTER DATABASE ADD STANDBY LOGFILE group 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;

ALTER DATABASE ADD STANDBY LOGFILE group 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M;

ALTER DATABASE ADD STANDBY LOGFILE group 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M;

Set Primary Database Initialization Parameters

Data Guard must use spfile, in order to configure it we create and configure the standby parameters

on a regular pfile, and once it is ready we convert it to an spfile.

SQL> create pfile='?/dbs/pfileorcl.ora' from spfile;

Step-4 > Edit the pfile to add the standby parameters, here shown:

db_unique_name='orcl'

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldr)'

LOG_ARCHIVE_DEST_2='SERVICE=orcldr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,

PRIMARY_ROLE) DB_UNIQUE_NAME=orcldr'

*.fal_server=orcldr

*.fal_client=orcl

*.standby_file_management=auto

*.log_file_name_convert='/u01/app/oracle/oradata/orcldr/','/u01/app/oracle/oradata/orcl/'

*.db_file_name_convert='/u01/app/oracle/oradata/orcldr/','/u01/app/oracle/oradata/orcl/'

Once the new parameter file is ready we create spfile from pfile

SQL> shutdown immediate;

SQL> startup nomount pfile='?/dbs/pfileorcl.ora';

SQL> create spfile from pfile='/?/dbs/pfileorcl.ora';

SQL> shutdown immediate;

SQL> startup;

Send pfile to standby server and rename it to initorcldr.ora and change following parameter Step-5 > Copying the pfile created in primary database and renames it to  initorcldr.ora, and changes these parameters: db_unique_name='orcldr' LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcldr,orcl)' LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES ,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' *.fal_server=orcl *.fal_client=orcldr *.LOG_ARCHIVE_DEST_STATE_2='ENABLE' *.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldr/' *.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldr/ A password file must be created on the Primary and copied over to the Standby site. The sys  password must be identical on both sites. Copy orapworcl to $ORACLE_HOME/dbs and  rename file to orapworcldr Step-6>Configure listeners for the primary On Primary system: use Oracle Net Manager to configure a listener for orcl database. Then  restart the listener. $lsnrctl stop  $lsnrctl start  On Primary system: use Oracle Net Manager to create network service names for orcl and orcldr.  Check tnsping to both services: $tnsping orcl $tnsping orcldr Configure listeners for the standby On Standby server: use Net Manager to configure a listener for orcldr database. Then  restart the listener. $lsnrctl stop  $lsnrctl start  On Standby system: use Oracle Net Manager to create network service names for orcl and orcldr. Check tnsping to both services: $tnsping orcl $tnsping orcldr Step-7> Backup database  NOTE-- Here We Make a backup folder in /u01 SQL> host rman target / RMAN> configure channel device type disk format '/u01/backup/%U'; RMAN > backup full database format '/u01/backup/ %d_%U.bckp' plus archivelog  format '/u01/backup/%d_%U.bckp'; Backup controlfile and Archive log for Standby RMAN> backup current controlfile for standby; RMAN> backup archivelog all; RMAN> exit

Send Backup to Orcldr

[oracle@localhost backup]$ scp * oracle@192.168.116.131:/u01/backup

scp * oracle@192.168.196.129:/u01/backup Step-8 > Create mount points for Oracle or Client Provide it Mount point should be created on standby server , this includes the location for controlfiles , redo logs, datafiles, archive log destination and alert logs mkdir -p /u01/app/oracle/oradata/orcldr/  mkdir -p /u01/app/oracle/flash_recovery_area/ mkdir -p /u01/app/oracle/admin/orcldr/adump  mkdir -p /u01/app/oracle/admin/orcldr/bdump  mkdir -p /u01/app/oracle/admin/orcldr/cdump  mkdir -p /u01/app/oracle/admin/orcldr/udump Use RMAN to restore backup and setup standby Connect to RMAN and execute the following command to create standby database,  this should be performed on standby server after copying backup and setting tnsnames. $ export ORACLE_SID=orcldr [oracle@localhost oradata]$ sqlplus '/ as sysdba' SQL> startup nomount pfile='?/dbs/initorcldr.ora'; SQL> create spfile from pfile='/?/dbs/initorcldr.ora'; SQL> host rman target=sys/sys@orcl auxiliary / Or $ rman target sys/sys@orcl auxiliary / Step-9 > Duplicate command for creating standby   RMAN> duplicate target database for standby nofilenamecheck; Step-10 > On same standby server START THE REDO APPLY SQL> alter database recover managed standby database disconnect from session; Check the Sequences are archived and applied on both sides.. SQL> select sequence#,applied from v$archived_log order by sequence#; SQL> select sequence#,applied from v$archived_log order by sequence#; If you ever need to stop log apply services SQL> alter database recover managed standby database cancel; If you want the redo data to be applied as it is received without waiting for the current standby   standby redo log file to be archived, enable the real-time apply. SQL> alter database recover managed standby database using current logfile disconnect;