Showing posts with label Dataguard. Show all posts

Tuesday, May 7, 2024

physical standby without duplicate command

create a physical standby database using RMAN without using duplicate command

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

Step 1: Backup the database that includes backup of datafiles, archivelogs and controlfile for standby
Step 2: Move the backups to the standby server
Step 3: Make proper changes in the parameter files of both primary and standby database
Step 4: Do the restore and recover on standby database

Step 5: Put the standby database in recover managed mode

Step 1:

=>> Primary:

rman target /
RMAN> run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup database plus archivelog;
}

Backup controlfile for standby

rman target /
backup device type disk format 
'/fast_recovery_area/TSTDB/backup/%U' current controlfile for standby;

Step 2:

Move the backups and standby controlfile to the standby server

-- parameter setting on Standby DB:
-- Standby steps

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;

alter system set db_recovery_file_dest='/fast_recovery_area' scope=spfile;

alter system set db_recovery_file_dest_size = 50G scope=spfile sid='*';

alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST' 
scope=spfile; 

Standby DB

rman target /
startup nomount;

-- RMAN> set dbid=3959250272; if need it. In my case no need

restore standby controlfile from 
'/fast_recovery_area/TSTDB/backup/fb2q6pco_7659_1_1';
sql 'alter database mount standby database';
restore database;

RMAN> exit

SQL> alter database recover managed standby database disconnect 
from session;

SQL> ALTER DATABASE RECOVER  managed standby database  cancel;  
-- cancel managed standby DB

-- Primany DB:

-- check the parameter

-- with 3 hour delay
alter system set log_archive_dest_2='SERVICE=tst_stb_01 compression=DISABLE delay=180';  

-- no delay
alter system set log_archive_dest_2='SERVICE=tst_stb_01 compression=DISABLE';  


alter system set log_archive_dest_state_2=defer;

alter system set log_archive_dest_state_2=enable;

-- Standby DB

Wait and check the alert.log files

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

You can also check the last log apply date and time:

SELECT 
    CASE 
        WHEN TO_CHAR(MAX(COMPLETION_TIME), 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD') 
        THEN TO_CHAR(MAX(COMPLETION_TIME), 'YYYY-MM-DD HH24:MI:SS')
        ELSE 'ERROR'
    END AS last_apply_time
FROM V$ARCHIVED_LOG
WHERE DEST_ID = (SELECT MIN(DEST_ID) FROM V$ARCHIVE_DEST WHERE STATUS = 'VALID');

Wednesday, June 16, 2021

How to open physical standby database

How to open physical standby database in read only mode

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------

TSTDB MOUNTED PHYSICAL STANDBY

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.

ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2.7917E+10 bytes
Fixed Size 8636632 bytes
Variable Size 4294969128 bytes
Database Buffers 2.3555E+10 bytes
Redo Buffers 58470400 bytes

Database mounted.

SQL> alter database open read only;

Database altered.

SQL> select * from t1;

D
-

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------

TSTDB READ ONLY PHYSICAL STANDBY

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0

Current log sequence 0

SQL> shutdown immediate;
Database closed.
Database dismounted.

ORACLE instance shut down.

Database mounted.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------

TSTDB MOUNTED PHYSICAL STANDBY

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 123 1 406
ARCH CONNECTED 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 124 57 1
MRP0 APPLYING_LOG 1 124 57 409600

9 rows selected.

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
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,
2 (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
3 4 5 6 ARCH.THREAD# = APPL.THREAD#
ORDER BY 1; 7

Thread Last Sequence Received Last Sequence Applied
---------- ---------------------- ---------------------

1 123 123

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)

--------------

122

Handling Standby Redo Logs

Handling Online Redo logs and Standby Redo logs

Handling Online Redo logs (ORL) and Standby Redo logs (SRL) on Primary and Physical Standby

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

On Standby:

SQL> select sg.group#,sg.thread#,sg.sequence#,sg.bytes/1024/1024 "sizeMB",sg.archived,sg.status,le.type,le.member from V$STANDBY_LOG sg,v$logfile le where le.group#=sg.group#

GROUP# THREAD# SEQUENCE# sizeMB ARC STATUS TYPE MEMBER

---------- ---------- ---------- ---------- --- ---------- ------- --------------------------------------------------

40 1 120 200 YES ACTIVE STANDBY /fast_recovery_area/TSTDB02/onlinelog/o1_mf_40_jdj

stm2v_.log

40 1 120 200 YES ACTIVE STANDBY +DATA01/TSTDB02/ONLINELOG/group_40.266.1075286387

41 0 0 200 NO UNASSIGNED STANDBY /fast_recovery_area/TSTDB02/onlinelog/o1_mf_41_jdj

stow9_.log

41 0 0 200 NO UNASSIGNED STANDBY +DATA01/TSTDB02/ONLINELOG/group_41.263.1075286389

42 0 0 200 NO UNASSIGNED STANDBY /fast_recovery_area/TSTDB02/onlinelog/o1_mf_42_jdj

stqxg_.log

SQL> show parameter standby_file_management;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO

SQL> alter system set standby_file_management=manual;

System altered.

SQL> show parameter standby_file_management

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

standby_file_management string MANUAL

SQL> alter database recover managed standby database cancel;

Database altered.

Online standby redolog:

SQL> alter database clear logfile group 41;
Database altered.

SQL> alter database drop logfile group 41;
Database altered.

SQL> alter database add standby logfile group 41 '+DATA01' size 200m;
Database altered.

Execute the same steps for the group 40,42 and 43:

Online redologs

SQL> alter database clear logfile group 1;
Database altered.

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database add logfile group 1 '+DATA01' size 200m;
Database altered.

Execute the same steps for the group 2 and 3:

SQL> set linesize 200

col MEMBER format a50

select lg.group#,lg.thread#,sequence#,lg.bytes/1024/1024 "sizeMB",lg.archived,lg.status,le.type,le.member from v$log lg,v$logfile le

where lg.group#=le.group#

/SQL> SQL> 2 3

GROUP# THREAD# SEQUENCE# sizeMB ARC STATUS TYPE MEMBER

---------- ---------- ---------- ---------- --- ---------------- ------- --------------------------------------------------

1 1 0 200 YES UNUSED ONLINE +DATA01/TSTDB02/ONLINELOG/group_1.260.1075303171

3 1 0 200 YES UNUSED ONLINE +DATA01/TSTDB02/ONLINELOG/group_3.269.1075303213

2 1 0 200 YES UNUSED ONLINE +DATA01/TSTDB02/ONLINELOG/group_2.261.1075303183

SQL> select sg.group#,sg.thread#,sg.sequence#,sg.bytes/1024/1024 "sizeMB",sg.archived,sg.status,le.type,le.member

from V$STANDBY_LOG sg,v$logfile le

where le.group#=sg.group#

/ 2 3 4

GROUP# THREAD# SEQUENCE# sizeMB ARC STATUS TYPE MEMBER

---------- ---------- ---------- ---------- --- ---------- ------- --------------------------------------------------

40 1 120 200 YES ACTIVE STANDBY +DATA01/TSTDB02/ONLINELOG/group_40.266.1075302753

41 0 0 200 YES UNASSIGNED STANDBY +DATA01/TSTDB02/ONLINELOG/group_41.258.1075302551

42 0 0 200 YES UNASSIGNED STANDBY +DATA01/TSTDB02/ONLINELOG/group_42.264.1075302763

43 0 0 200 YES UNASSIGNED STANDBY +DATA01/TSTDB02/ONLINELOG/group_43.263.1075302771

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 119 1 447
ARCH CLOSING 1 118 2048 239
RFS IDLE 0 0 0 0
RFS IDLE 1 120 50536 1
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 120 50536 409600

12 rows selected.

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------

119

On Primary:

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------

119

Wednesday, March 10, 2021

Upgrade Database with Physical Standby

How to Upgrade Database with Physical Standby

How do I upgrade or patch my physical standby database

Which steps are required for Physical Standby DB, when you want it to upgrade with Primary DB or you want to path the both Primary and Standby DB's

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

Those are the highlevel steps:

REFERENCE

===========

Oracle Reference Guide

Patching, Upgrading, and Downgrading Databases in an Oracle Data Guard Configuration

ACTION PLAN

=============

You can upgrade in TWO different ways

1. Using a Physical Standby database as your backup

== > Verify the Primary and Standby databases are in SYNC by comparing results of these 3 queries

Primary: SQL > alter system archive log current;

Primary: SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

PhyStdby:SQL> select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

PhyStdby:SQL>select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;

== > Standby: stop Managed Recovery == > SQL > recover managed standby database CANCEL

select name, open_mode, database_role from v$database;

select process, status, sequence# from v$managed_standby;

alter database recover managed standby database cancel;

== > Standby: shut down the database

== > Primary : DEFER log shipping

alter system set log_archive_dest_state_2=DEFER SCOPE=BOTH;

== > Primary : shut down the database instance

== > Primary : change your environmental variables ==> Variables that point to the current ORACLE_HOME == > $ORACLE_HOME . $TNS_ADMIN as well all files you need to have in the new $OH

tnsnames.ora

listener.ora

sqlnet.ora

parameter file

password file

== > Primary : bring up the database instance == > SQL > startup

== > Primary : execute post-patching steps

== > Primary : verify the process is SUCCESSFULLY completed

== > Standby: change your environmental variables

tnsnames.ora

listener.ora

sqlnet.ora

parameter file

password file

== > Standby: mount the database instance == > SQL > startup mount

== > Standby: start Managed Recovery == > SQL > recover managed standby database DISCONNECT

== > online redolog apply via redologs

alter database recover managed standby database using current logfile disconnect from session;

== > logapply via arcive log

ALTER DATABASE RECOVER managed standby database parallel 10 disconnect;

== > Primary : ENABLE log shipping

alter system set log_archive_dest_state_2=enable scope=both;

== > Verify the Primary and Standby databases are in SYNC by comparing results of these 3 queries

+++++++++++++++++++

2. Patching the databases at the SAME TIME

== > Primary : DEFER log shipping

== > Standby: stop Managed Recovery == > SQL > recover managed standby database CANCEL

== > Standby: shut down the database

== > Standby: change your environmental variables

== > Standby: mount the database instance == > SQL > startup mount

== > Standby: start Managed Recovery == > SQL > recover managed standby database DISCONNECT

== > Primary : shut down the database instance

== > Primary : change your environmental variables

== > Primary : bring up the database instance == > SQL > startup

== > Primary : ENABLE log shipping

== > Primary : execute post-patching steps

== > Verify the Primary and Standby databases are in SYNC by comparing results of these 3 queries

+++++++++++++++

Of course, you shall have all necessary files under your new $ORCALE_HOME

To apply a patch you might need to execute ONE or TWO steps

== > Patch Oracle binaries < == On BOTH databases : Primary AND Standby

== > Execute post patching step(s) < == On the Primary ONLY

== > A Physical Standby can NOT be modified DIRECTLY == > Only by applying changes received from the Primary database via redo log files

Saturday, June 27, 2020

Switchover oracle database 19c

Oracle 19c-Step by Step Manual Switchover

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

Use full commands you may need throw the process.

-- shutdown Applications which are running and connection to the DB.

-- backup crontab

-- select database_role from v$database; #check DB role

-- select name, open_mode, database_role from v$database; # above or this command

-- ALTER SYSTEM SWITCH LOGFILE;

-- SELECT SWITCHOVER_STATUS FROM V$DATABASE; -- check --

-- alter system set log_archive_dest_state_2='ENABLE' scope=both;

-- select process, status, sequence# from v$managed_standby; #check the MRP0 or MRP process is running

-- ALTER DATABASE RECOVER managed standby database cancel; #Cancel managed recovery operations.

-- alter database recover managed standby database using current logfile disconnect from session; # online redolog apply via redologs

-- ALTER DATABASE RECOVER managed standby database parallel 10 disconnect; #logapply via arcive log

-- lsnrctl start LISTENER

-- Always see the logfile when you start DB to check whether the parameters are correct or not.

-- NOTE: when you add tempfiles in the Primary database, new tempfiles will not added automatically in the Physical Standby database.

-- The tempfile will NOT automatically be created in the physical standby.

Following parameters are related to the Primary and Standby database. Verify the parameter before you start with Switch Over.

show parameter service_names;

show parameter instance_name;

show parameter log_archive_dest_2;

show parameter fal_server;

show parameter fal_client;

show parameter db_file_name_convert;

show parameter log_file_name_convert;

select name, open_mode, database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

--------- -------------------- ----------------

EDUDB READ WRITE PRIMARY

select name, open_mode, database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

--------- -------------------- ----------------

EDUDB MOUNTED PHYSICAL STANDBY

STEP 1

Starting with Switchover.

Run the following steps on Primary:

Alter system switch logfile;

select switchover_status from v$database;

alter database commit to switchover to physical standby with session shutdown;

shutdown immediate;

startup mount;

STEP 2

Run this on standby:

select switchover_status from v$database;

alter database commit to switchover to primary with session shutdown;

shutdown immediate;

startup

NOTE:

You can stop listener and modify it and start it again, If need it. Make sure that you have correct entry in tnsname.ora. Better if you test with command tnsping.

STEP 3

Execute following command on Standby:

alter database recover managed standby database using current logfile disconnect from session ;

select name,db_unique_name,database_role from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE

--------- ------------------------------ ----------------

EDUDB EDUDB02 PHYSICAL STANDBY

select process, status, sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#

--------- ------------ ----------

ARCH CONNECTED 0

DGRD ALLOCATED 0

ARCH CONNECTED 0

RFS IDLE 274

RFS IDLE 0

MRP0 WAIT_FOR_LOG 274

At this Point, we are done with switchover:

Extra steps:

STEP 4

Check whether the standby is in sync:

On both Primary and Standby:

On Primary:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"

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#

ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied

---------- ---------------------- ---------------------

1 273 273

select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

--------------

273

On Standby:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"

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#

ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied

---------- ---------------------- ---------------------

1 273 273

select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

--------------

273

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"

FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)

ORDER BY 1; 2 3 4

Thread Last Sequence Generated

---------- -----------------------

1 273

Trouble shooting standard check:

Make sure the parameter is enabled on both Primary and Standby.

--alter system set log_archive_dest_state_2=defer;

alter system set log_archive_dest_state_2=enable;

-- required for jdbc connection. You can set the value anyway it doesn't harm

SQL> alter system set instance_name='edudb' scope=spfile;

Test connection with tnsping on both direction.

-- check the service_name parameter before and edit the value as follow. You can alter multiple service_name as follow, if required.

alter system set service_names=' edudb01',' edudb01.th.com',' edudb02' ,' edudb02.th.com' scope = both;

Saturday, April 11, 2020

Creating a Physical Standby Database Oracle Version 19.6.0.0.0

Creating a Physical Standby Database on Oracle Version 19.6.0.0.0

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

Basic high level steps:

1. Check on Primary DB the FORCE_LOGGING is enabled.

2. Primary DB need to be in archivelog mode.

3. Create pfile from PRIMARY DB and copy to the STANDBY DB.

4. Modify required parameter for PRIMARY DB.

5. Copy password file from PRIMARY DB to STANDBY DB.

6. Edit required parameter on pfile on STANDBY DB and Create from the pfile spfile.

7. Standby DB need to be in nomount stage to create STANDBY DB.

8. Size of redo on standby must be same as primary.

9. Create the same number of Standby Redo Logfile for both production and standby databases.

10. Configure LISTENER and entry in TNSNAMES.ORA for both PRIMARY DB and STANDBY DB.

11. Start Listener on STANDBY DB and check the connection to PRIMARYDB. For example tnsping etc.

12. Prepare RMAN script and Check the RMAN connection from STANDBY DB. Whereby PRIMARY_DB=TARGET and STANDBY_DB=AUXILIARY.

13. Enable recovery process MRP.

14. Check Standby is in sync.

15. If required any scrips, cronjob etc. on both side.

Following are the Environment:

Hostname : edu01.th.com

Primary DB : EDUDB01

DB VERSION : Version 19.6.0.0.0

DB Home Path : /oracle19c/product/19.6.0.0/db

OS: : Red Hat Enterprise Linux Server release 7.7

Hostname : edu02.th.com

Standby DB : EDUDB02

DB VERSION : Version 19.6.0.0.0

DB Home Path : /oracle19c/product/19.6.0.0/db

OS: : Red Hat Enterprise Linux Server release 7.7

NOTE: You can keep same UNIQUE NAME and DB NAME on Primary.

PRIMARY:

DB UNIQUE NAME=EDUDB

DB NAME=EDUDB

STANDBY:

DB UNIQUE NAME=EDUDB02

DB NAME=EDUDB

On PrimaryDB:

1. Check Archive log mode enable and force logging enabled.

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /oracle19c/product/19.6.0.0/db/dbs/arch <<===== I am going to change the Archive destination as well as.

Oldest online log sequence 24

Current log sequence 26

# This parameter log_archive_dest_1 can be changed online.

SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST' scope=spfile;

SQL> show parameter log_archive_dest_1;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_DEST

...

SQL>shutdown immediate;

SQL>startup mount;

SQL>alter database archivelog;

SQL> alter database open;

SQL>archive log list;

-- Automatic archival Enabled

2. Enable Force Logging on primary.

SQL> select FORCE_LOGGING from v$database;

SQL> ALTER DATABASE FORCE LOGGING;

3. Change required parameter on Primary DB

LOG_ARCHIVE_DEST_2 = is valid only for the primary role. This destination transmits redo data to the remote physical standby destination

FAL_SERVER= Add both standby server, that oracle knows from where to get arch files in case of switchover.

FAL_SERVER=EDUDB02

FAL_CLIENT= This is always the primary server. The parameter is ignored when the DB is in Primary mode.

FAL_CLIENT=EDUDB01

Db_name=edudb # Same across all DB’s

Db_unique_name=EDUDB01 # unique on each databases

SQL> show parameter DB_UNIQUE_NAME;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_unique_name string EDUDB01

SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string edudb

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(EDUDB01, EDUDB02)' SCOPE=both;;

alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(all_logfiles,all_roles) DB_UNIQUE_NAME=EDUDB01' SCOPE=both;

alter system set LOG_ARCHIVE_DEST_2='SERVICE=EDUDB02 LGWR async valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=EDUDB02';

alter system set log_archive_dest_state_2=defer/enable;

You can increase the parameter value based on your requirement. I am going to keep as it is.

log_archive_max_processes=4

alter system set log_archive_max_processes=16; <<== If you need to increase the value.

alter system set FAL_SERVER=EDUDB02;# standbydb

alter system set FAL_CLIENT=EDUDB01; # it's a primary self

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

alter system set db_file_name_convert='+DATA','+DATA' scope=spfile;

alter system set LOG_FILE_NAME_CONVERT='+DATA','+DATA' scope=spfile;

You can create multiple service_name, if needed.

alter system set service_names='edudb01','edudb','edudb02' scope = both;

NOTE: Size of redo on standby must be same as primary. You can find the size of redo log as follow.

Create the same number of SRLs for both production and standby databases. The SRLs should exist on both production and standby databases.

The Size should match on both side of the ONLINE LOGFILES.

set linesize 200

col MEMBER format a50

select lg.group#,lg.thread#,sequence#,lg.bytes/1024/1024 "sizeMB",lg.archived,lg.status,le.type,le.member from v$log lg,v$logfile le

where lg.group#=le.group#

You can create standby redo log file on primary DB as follow.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '+DATA' size 200m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '+DATA' size 200m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '+DATA' size 200m;

Check the online and standby redolog file status.

SQL> col MEMBER format a50

select lg.group#,lg.thread#,sequence#,lg.bytes/1024/1024 "sizeMB",lg.archived,lg.status,le.type,le.member from v$log lg,v$logfile le where lg.group#=le.group#

GROUP# THREAD# SEQUENCE# sizeMB ARC STATUS TYPE MEMBER

---------- ---------- ---------- ---------- --- ---------------- ------- --------------------------------------------------

3 1 24 200 NO INACTIVE ONLINE +DATA/EDUDB/ONLINELOG/group_3.264.1034096339

2 1 26 200 NO CURRENT ONLINE +DATA/EDUDB/ONLINELOG/group_2.263.1034096339

1 1 25 200 NO INACTIVE ONLINE +DATA/EDUDB/ONLINELOG/group_1.262.1034096339

SQL> select sg.group#,sg.thread#,sg.sequence#,sg.bytes/1024/1024 "sizeMB",sg.archived,sg.status,le.type,le.member

from V$STANDBY_LOG sg,v$logfile le

where le.group#=sg.group#

GROUP# THREAD# SEQUENCE# sizeMB ARC STATUS TYPE MEMBER

---------- ---------- ---------- ---------- --- ---------- ------- --------------------------------------------------

11 0 0 200 YES UNASSIGNED STANDBY +DATA/EDUDB/ONLINELOG/redo11.log

12 0 0 200 YES UNASSIGNED STANDBY +DATA/EDUDB/ONLINELOG/redo12.log

13 0 0 200 YES UNASSIGNED STANDBY +DATA/EDUDB/ONLINELOG/redo13.log

Establish the connectivity,

Create net services on both primary and standby

cd $ORACLE_HOME/network/admin

vi tnsnames.ora

EDUDB01 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = edu01.th.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = edudb01)

)

EDUDB02 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = edu02.th.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = edudb02)

)

IMPORTENT:

Add following line in the listener.ora file, if you see tns connection issue with service_name

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=edudb)

(ORACLE_HOME=/oracle19c/product/19.6.0.0/db)

(SID_NAME=EDUDB01)

)

7. Create pfile from spfile.

SQL> create pfile='/oracle19c/tmp/initEDUDB01.ora' from spfile;

File created.

scp initEDUDB01.ora to the standbyDB

scp orapwEDUDB01 file to the standby and rename it mv orapwEDUDB01 orapwEDUDB02

On StandbyDB:

copy the initfile to the standby DB, which we created erlier on primary DB.

scp initEDUDB01.ora from primaryDB to standbyDB and edit the required parameters.

LOG_ARCHIVE_DEST_STATE_2 Is ignored; valid only when “EDUDB02” is running in a primary role.

db_name=EDUDB

db_unique_name=EDUDB02

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

standby_file_management=AUTO

FAL_SERVER=EDUDB01; # other server

FAL_CLIENT=EDUDB02; # it's a server DB self

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(EDUDB01, EDUDB02)';

alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EDUDB02';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=EDUDB01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=EDUDB01';

alter system set log_archive_dest_state_2=defer/enable;

alter system set DB_FILE_NAME_CONVERT='+DATA','+DATA' scope=spfile;

alter system set LOG_FILE_NAME_CONVERT='+DATA','+DATA' scope=spfile;

You may need set also the following parameter. Otherwise, you could see the following error.

ORACLE error from target database:

ORA-38757: Database must be mounted and not open to FLASHBACK.

Check the value first:

SELECT name, value FROM v$parameter WHERE name LIKE 'db_recovery%';

change the value

ALTER SYSTEM SET db_recovery_file_dest='/oracle19c/flashback' SCOPE=BOTH;

ALTER SYSTEM SET db_recovery_file_dest_size=2g SCOPE=BOTH;

on standby:

startup nomount pfile='/oracle19c/product/19.6.0.0/db/dbs/initEDUDB01.ora';

create spfile='/oracle19c/product/19.6.0.0/db/dbs/spfileEDUDB02.ora' from memory;

shutdown immediate;

startup nomount;

show parameter spfile;

As DB:

srvctl add database -d ${ORACLE_SID} -o /oracle19/dbbase/db -p '/oracle19/dbbase/db/dbs/spfile'"${ORACLE_SID}"'.ora' -pwfile '/oracle19/dbbase/db/dbs/orapw'"${ORACLE_SID}"''

4. Create passwd file:

NOTE:

If auxiliary database has following error:

RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

No worry, because the DB is not open.

The blocked status is being set by the instance, indicating that the database is not able to accept connections.

This status can be caused by the instance not being mounted or being mounted in a restricted state.

Make sure, that the orapwd file is created. The file must present on both side.

-- orapwd file='$ORACLE_HOME/dbs/orapwEDUDB01' entries=10 force=y password='xxxxx' force=y format=12.2

orapwd file=$ORACLE_HOME/dbs/orapwEDUDB01 password=oracle format=12 force=y

Before you start RMAN script make sure that connections works.

tnsping EDUDB01

tnsping EDUDB02

sqlplus sys/password@EDUDB01 as sysdba

sqlplus sys/password@EDUDB02 as sysdba

rman target sys/password@EDUDB01 auxiliary sys/password@EDUDB02

rman target sys/password@EDUDB01 auxiliary /

From Standby DB:

[oracle@edu02 ~]$ rman target sys/oracle@EDUDB01 auxiliary sys/oracle@EDUDB02

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Mar 16 10:08:21 2020

Version 19.3.0.0.0

connected to target database: EDUDB (DBID=3803574607)

connected to auxiliary database: EDUDB (not mounted)

From Primary DB:

[oracle@edu01 admin]$ rman target sys/oracle@EDUDB02 auxiliary sys/oracle@EDUDB02

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Mar 13 17:24:58 2020

Version 19.6.0.0.0

PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.03.00.00 in TARGET database is not current

PL/SQL package SYS.DBMS_RCVMAN version 19.03.00.00 in TARGET database is not current

connected to target database: EDUDB (not mounted)

PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.03.00.00 in AUXILIARY database is not current

PL/SQL package SYS.DBMS_RCVMAN version 19.03.00.00 in AUXILIARY database is not current

connected to auxiliary database: EDUDB (not mounted)

[oracle@edu02 ~]$ rman target sys/oracle@EDUDB01 auxiliary sys/oracle@EDUDB02

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Mar 16 14:36:24 2020

Version 19.3.0.0.0

connected to target database: EDUDB (DBID=3803574607)

connected to auxiliary database: EDUDB (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

-- This is log output.

Starting Duplicate Db at 16-MAR-20

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=265 device type=DISK

contents of Memory Script:

{

backup as copy reuse

passwordfile auxiliary format '/oracle19c/product/19.6.0.0/db/dbs/orapwEDUDB02' ;

}

executing Memory Script

Starting backup at 16-MAR-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=153 device type=DISK

Finished backup at 16-MAR-20

duplicating Online logs to Oracle Managed File (OMF) location

duplicating Datafiles to Oracle Managed File (OMF) location

contents of Memory Script:

{

sql clone "alter system set control_files =

''+DATA/EDUDB02/CONTROLFILE/current.260.1035210993'' comment=

''Set by RMAN'' scope=spfile";

restore clone from service 'EDUDB01' standby controlfile;

}

executing Memory Script

sql statement: alter system set control_files = ''+DATA/EDUDB02/CONTROLFILE/current.260.1035210993'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 16-MAR-20

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service EDUDB01

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=+DATA/EDUDB02/CONTROLFILE/current.261.1035210995

Finished restore at 16-MAR-20

contents of Memory Script:

{

sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

set newname for clone tempfile 1 to new;

set newname for clone tempfile 2 to new;

set newname for clone tempfile 3 to new;

switch clone tempfile all;

set newname for clone datafile 1 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 5 to new;

set newname for clone datafile 6 to new;

set newname for clone datafile 7 to new;

set newname for clone datafile 8 to new;

set newname for clone datafile 9 to new;

set newname for clone datafile 10 to new;

set newname for clone datafile 11 to new;

set newname for clone datafile 12 to new;

restore

from nonsparse from service

'EDUDB01' clone database

;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

renamed tempfile 2 to +DATA in control file

renamed tempfile 3 to +DATA in control file

executing command: SET NEWNAME

Starting restore at 16-MAR-20

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service EDUDB01

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service EDUDB01

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service EDUDB01

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service EDUDB01

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service EDUDB01

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service EDUDB01

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service EDUDB01

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00008 to +DATA

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service EDUDB01

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00009 to +DATA

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service EDUDB01

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00010 to +DATA

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service EDUDB01

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00011 to +DATA

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service EDUDB01

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00012 to +DATA

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 16-MAR-20

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=15 STAMP=1035211016 file name=+DATA/EDUDB02/DATAFILE/system.266.1035211001

datafile 3 switched to datafile copy

input datafile copy RECID=16 STAMP=1035211016 file name=+DATA/EDUDB02/DATAFILE/sysaux.267.1035211003

datafile 4 switched to datafile copy

input datafile copy RECID=17 STAMP=1035211016 file name=+DATA/EDUDB02/DATAFILE/undotbs1.259.1035211007

datafile 5 switched to datafile copy

input datafile copy RECID=18 STAMP=1035211016 file name=+DATA/EDUDB02/9FF6693B4B0B13A0E0531F4614ACC2BF/DATAFILE/system.258.1035211007

datafile 6 switched to datafile copy

input datafile copy RECID=19 STAMP=1035211016 file name=+DATA/EDUDB02/9FF6693B4B0B13A0E0531F4614ACC2BF/DATAFILE/sysaux.268.1035211009

datafile 7 switched to datafile copy

input datafile copy RECID=20 STAMP=1035211016 file name=+DATA/EDUDB02/DATAFILE/users.265.1035211009

datafile 8 switched to datafile copy

input datafile copy RECID=21 STAMP=1035211016 file name=+DATA/EDUDB02/9FF6693B4B0B13A0E0531F4614ACC2BF/DATAFILE/undotbs1.263.1035211011

datafile 9 switched to datafile copy

input datafile copy RECID=22 STAMP=1035211016 file name=+DATA/EDUDB02/9FF684209EBE1D04E0531F4614AC9F7F/DATAFILE/system.270.1035211013

datafile 10 switched to datafile copy

input datafile copy RECID=23 STAMP=1035211016 file name=+DATA/EDUDB02/9FF684209EBE1D04E0531F4614AC9F7F/DATAFILE/sysaux.262.1035211013

datafile 11 switched to datafile copy

input datafile copy RECID=24 STAMP=1035211016 file name=+DATA/EDUDB02/9FF684209EBE1D04E0531F4614AC9F7F/DATAFILE/undotbs1.264.1035211015

datafile 12 switched to datafile copy

input datafile copy RECID=25 STAMP=1035211016 file name=+DATA/EDUDB02/9FF684209EBE1D04E0531F4614AC9F7F/DATAFILE/users.272.1035211015

Finished Duplicate Db at 16-MAR-20

Enable the log_archive_dest_2 on primary DB, which we have set as defer earlier.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> select name,database_role,open_mode from v$database;

NAME DATABASE_ROLE OPEN_MODE

--------- ---------------- --------------------

EDUDB PHYSICAL STANDBY MOUNTED

You can test whether the lofile shipping to the Physical DB

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

--------- ------------ ---------- ---------- ---------- ----------

ARCH CONNECTED 0 0 0 0

DGRD ALLOCATED 0 0 0 0

ARCH CONNECTED 0 0 0 0

MRP0 WAIT_FOR_LOG 1 49 0 0

RFS IDLE 1 0 0 0

RFS IDLE 1 49 21945 1

RFS IDLE 0 0 0 0

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

--------- ------------ ---------- ---------- ---------- ----------

RFS IDLE 0 0 0 0

12 rows selected.

Execute few times following commands on primary DB.

alter system switch logfile；

alter system switch logfile;

Check on Stand by DB again.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

--------- ------------ ---------- ---------- ---------- ----------

ARCH CONNECTED 0 0 0 0

DGRD ALLOCATED 0 0 0 0

ARCH CONNECTED 0 0 0 0

MRP0 WAIT_FOR_LOG 1 51 0 0

RFS IDLE 1 0 0 0

RFS IDLE 1 51 24 1

RFS IDLE 0 0 0 0

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

--------- ------------ ---------- ---------- ---------- ----------

RFS IDLE 0 0 0 0

12 rows selected.

-- On standby DB

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"

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#

ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied

---------- ---------------------- ---------------------

1 50 50

Completed.

Oracle DBA's

Tags

Tuesday, May 7, 2024

physical standby without duplicate command

physical standby without duplicate command

create a physical standby database using RMAN without using duplicate command

Wednesday, June 16, 2021

How to open physical standby database

How to open physical standby database in read only mode

Handling Standby Redo Logs

Handling Online Redo logs and Standby Redo logs

Wednesday, March 10, 2021

Upgrade Database with Physical Standby

Upgrade Database with Physical Standby

How to Upgrade Database with Physical Standby

How do I upgrade or patch my physical standby database

Those are the highlevel steps:

Saturday, June 27, 2020

Switchover oracle database 19c

Switchover oracle database 19c

Oracle 19c-Step by Step Manual Switchover

Saturday, April 11, 2020

Creating a Physical Standby Database Oracle Version 19.6.0.0.0

physical standby without duplicate command

Search This Blog