Backup Based Physical Standby Configuration

Backup Based Physical Standby Configuration Manually

Description:
In this blog we are going to see step by step configuration and demo of oracle database primary to physical standby.

Backup Based Standby Configuration:
Mostly we have configure standby database in active data guard method. using level 0 backup we configure physical standby database.

Overall Steps:

1. check the role in primary database.
2. Take level 0 backup using RMAN.
3. Primary database parameter configuration.
4. Control and Pfile creation for standby database.
5. Create directory in standby database.
6. Copy archivelogs,control file,pfile, backupsets to standby database.
7. Standby configuration.
8. Bounce database and set the standby control file.
9. Connect RMAN and restore ,recover the database.
10. Check the database can be created.
11. Start the MRP process.
12. Check the Log sync status.

Step 1:- Check the database role in primary database.
SQL> select name,open_mode,database_role from v$database;
Step 2:- Take level 0 backup using RMAN.
RMAN> backup incremental level 0 database plus archivelog;
Step 3:- Primary database parameter configuration.

SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(cloud,local)’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_1=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=cloud’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_2=’service=local async valid_for=(online_logfiles,primary_role) db_unique_name=local’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET fal_server=’local’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET fal_client=’c’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=both;

System altered.

Step 4:- Create control file and Pfile for standby database.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/u01/app/oracle/oradata/CLOUD/standby.ctl’;

Database altered.

SQL> CREATE PFILE=’$ORACLE_HOME/dbs/initcompany.ora’ FROM SPFILE;

File created.

Step 5:- Create directory in standby database.
[oracle@standby ~]$ mkdir -p /u01/app/oracle/oradata/COMPANY/controlfile
[oracle@standby ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
mkdir -p /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17
[oracle@standby ~]$ mkdir -p /u01/app/oracle/admin/COMPANY/adump
/u01/app/oracle/fast_recovery_area/CLOUD/backupset/

Step 6:- Copy archivelogs,control file,pfile, backupsets to standby database.
Pfile and password file:
[oracle@primary dbs]$ scp orapwcloud [email protected]:$ORACLE_HOME/dbs
[email protected]'s password:
orapwcloud 100% 2048 2.1MB/s 00:00
[oracle@primary dbs]$ scp initcompany.ora [email protected]:$ORACLE_HOME/dbs
[email protected]'s password:
initcompany.ora 100% 1512 1.3MB/s 00:00
[oracle@primary dbs]$ cd
[oracle@primary ~]$ cd /u01/app/oracle/fast_recovery_area/
[oracle@primary fast_recovery_area]$ ls

Archivelogs:
/u01/app/oracle/fast_recovery_area/CLOUD/archivelog/2021_12_17
[oracle@primary 2021_12_17]$ ll
total 24288
-rw-rw----. 1 oracle oracle 22827008 Dec 17 07:38 o1_mf_1_2_jvqw80t4_.arc
-rw-rw----. 1 oracle oracle 2004480 Dec 17 07:39 o1_mf_1_3_jvqwcpbc_.arc
-rw-rw----. 1 oracle oracle 35840 Dec 17 07:41 o1_mf_1_4_jvqwglmx_.arc
[oracle@primary 2021_12_17]$ scp o1_mf_1_2_jvqw80t4_.arc [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17
[email protected]'s password:
o1_mf_1_2_jvqw80t4_.arc 100% 22MB 42.4MB/s 00:00
[oracle@primary 2021_12_17]$ scp o1_mf_1_3_jvqwcpbc_.arc [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17
[email protected]'s password:
Permission denied, please try again.
[email protected]'s password:
o1_mf_1_3_jvqwcpbc_.arc 100% 1958KB 63.2MB/s 00:00
[oracle@primary 2021_12_17]$ scp o1_mf_1_4_jvqwglmx_.arc [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17
[email protected]'s password:
o1_mf_1_4_jvqwglmx_.arc 100% 35KB 20.1MB/s 00:00
[oracle@primary 2021_12_17]$ cd ..
[oracle@primary archivelog]$ cd ..

Backup sets:
[oracle@primary backupset]$ cd 2021_12_17
[oracle@primary 2021_12_17]$ ll
total 2049948
-rw-rw----. 1 oracle oracle 519864832 Dec 17 07:40 o1_mf_annnn_TAG20211217T073959_jvqwcqrw_.bkp
-rw-rw----. 1 oracle oracle 196301312 Dec 17 07:40 o1_mf_annnn_TAG20211217T073959_jvqwdkp0_.bkp
-rw-rw----. 1 oracle oracle 24836608 Dec 17 07:40 o1_mf_annnn_TAG20211217T073959_jvqwds4r_.bkp
-rw-rw----. 1 oracle oracle 38912 Dec 17 07:41 o1_mf_annnn_TAG20211217T074130_jvqwgls4_.bkp
-rw-rw----. 1 oracle oracle 1358094336 Dec 17 07:41 o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp
[oracle@primary 2021_12_17]$ scp o1_mf_annnn_TAG20211217T073959_jvqwcqrw_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_annnn_TAG20211217T073959_jvqwcqrw_.bkp 100% 496MB 30.3MB/s 00:16 
[oracle@primary 2021_12_17]$ scp o1_mf_annnn_TAG20211217T073959_jvqwdkp0_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_annnn_TAG20211217T073959_jvqwdkp0_.bkp 100% 187MB 37.4MB/s 00:05 
[oracle@primary 2021_12_17]$ scp o1_mf_annnn_TAG20211217T073959_jvqwds4r_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_annnn_TAG20211217T073959_jvqwds4r_.bkp 100% 24MB 42.7MB/s 00:00 
[oracle@primary 2021_12_17]$ scp o1_mf_annnn_TAG20211217T074130_jvqwgls4_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_annnn_TAG20211217T074130_jvqwgls4_.bkp 100% 38KB 2.3MB/s 00:00 
[oracle@primary 2021_12_17]$ scp o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp 100% 1295MB 37.2MB/s 00:34 
[oracle@primary 2021_12_17]$ cd ../..
[oracle@primary CLOUD]$ ls

Step 7:- Standby Configuration.

1. Pfile parameter changes:
*.audit_file_dest='/u01/app/oracle/admin/cloud/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/CLOUD/controlfile/o1_mf_jvbn3nsp_.ctl','/u01/app/oracle/fast_recovery_area/CLOUD/controlfile/o1_mf_jvbn3nxt_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='cloud'
*.db_unique_name='company'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cloudXDB)'
*.fal_client='company'
*.fal_server='cloud'
*.log_archive_config='dg_config=(cloud,company)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=cloud'
*.log_archive_dest_2='service=company async valid_for=(online_logfiles,primary_role) db_unique_name=cloud'
*.log_archive_dest_state_2='ENABLE'
*.open_cursors=300
*.pga_aggregate_target=379m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1136m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

2. Start the Database using spfile:

[oracle@local dbs]$ export ORACLE_SID=company
[oracle@local dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Dec 17 08:17:06 2021
Version 19.3.0.0.0

Connected to an idle instance.

SQL> CREATE SPFILE FROM PFILE=’initcompany.ora’;

File created.

Step 8:- Bounce database and set the standby control file.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1191181696 bytes
Fixed Size 8895872 bytes
Variable Size 318767104 bytes
Database Buffers 855638016 bytes
Redo Buffers 7880704 bytes
Database mounted.
SQL> exit

SQL> alter system set control_files=’/u01/app/oracle/oradata/COMPANY/standby.ctl’ scope=spfile;

System altered.

Step 9:-Connect RMAN and restore ,recover the database.

RMAN> restore database;

Starting restore at 17-DEC-21
Starting implicit crosscheck backup at 17-DEC-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
Crosschecked 18 objects
Finished implicit crosscheck backup at 17-DEC-21

Starting implicit crosscheck copy at 17-DEC-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-DEC-21

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T073959_jvqwcqrw_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T073959_jvqwdkp0_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T073959_jvqwds4r_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T074130_jvqwgls4_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_2_jvqw80t4_.arc
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_3_jvqwcpbc_.arc
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_4_jvqwglmx_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_system_jvbmzxk8_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_sysaux_jvbn1c3g_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_undotbs1_jvbn24dz_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_users_jvbn25hj_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp tag=TAG20211217T074035
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 17-DEC-21

RMAN> recover database;

Starting recover at 17-DEC-21
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_4_jvqwglmx_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_4_jvqwglmx_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-DEC-21

RMAN> exit

Step 10:- Check the database can be created.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
CLOUD MOUNTED

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

NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
CLOUD MOUNTED PHYSICAL STANDBY

Step 11:- Start the MRP process.

SQL> alter database recover managed standby database disconnect nodelay;

Database altered.

Step 12:- Log sync status.

switch logs in primary, defer and enable the dest.

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
SQL>

check the status in standby.

SQL> alter database recover managed standby database disconnect nodelay;

Database altered.

SQL> 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# ORDER BY 1; 2 3 4

Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1 9 9 0

SQL>

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

ASM Configuration for Standalone Database

Oracle ASM standalone configuration

Description:
In this blog, we are going to see the step-by-step configuration for standalone ASM using the oracle restart method.

Standalone Database:
The Oracle Grid Infrastructure for a standalone server is the Oracle software that provides system support for an Oracle database including volume management, file system, and automatic restart capabilities.

ASM server:
Automatic Storage Management (ASM) is an integrated, high-performance database file system and disk manager. ASM is based on the principle that the database should manage storage instead of requiring an administrator to do it.

High Level Steps

Adding disk group in Virtual Machine
Grid software Installation
Oracle software installation
ASM standalone DB using the DBCA tool.

Adding Disk Group in Virtual Machine:

Step 1: open virtual machine hardware menu clicks add button:

Step 2:- Select an independent option and click next:

Step 3:- select create a new virtual disk and click next:

Step 4:- select allocate all disk space now option:

Step 5:- Select the folder and create the Disk:

ASM Hard Disk added:

Step 6:- OS-level disk partition :
Power on the virtual machine and check fdisk -l
Partition the disks:
fdisk/dev/sdb
Configure ASMLib using the following command.
oracleasm configure -i
create disk in OS level
/usr/sbin/oracleasm init
/usr/sbin/oracleasm createdisk DISK1 /dev/sdb1

GRID installation pre requests:

Check pre-install
Yum search preinstall

yum install oracle-database-preinstall-19c.x86_64 -y

yum install kmod-oracleasm.x86_64

yum install oracleasm-support.x86_64

Create a grid home directory and unzip the grid home

Add asm groups
[root@asm ~]# groupadd -g 54327 asmdba
[root@asm ~]# groupadd -g 54328 asmoper
[root@asm ~]# groupadd -g 54329 asmadmin
usermod -g oinstall -G dba,oper,backupdba,dgdba,kmdba,asmdba,asmoper,asmadmin,racdba oracle

Install the package cvudisk :
rpm -ivh /u01/app/grid/cv/rpm/cvuqdisk-1.0.10-1.rpm

Run the script:

Oracle Grid software installation:

Click the oracle restart method:

Give any disk group name and redundancy as external change the path as /dev/oracleasm/disks

Select the disk path

Set the password for ASM
Click next to continue.
Click next to continue
check the installation path
check the inventory path

Give root password to run the script automatically:

Summary of grid installation:
Running the installation
completed

Oracle Software installation

Unzip the Oracle 19c software in-home location

Run the ./runInstaller

Step1:- select set up software only option

Step 2:- select single instance database installation

Choose enterprise edition:
specify the location

Summary of installation

oracle software installation completed

ASM Standalone Database creation using DBCA Tool:

Select create database:

Give sid name and create admin password also select container database and mention the db name
check the asm configuration

Summary of db creation
db creation in progress

DB creation complete

Check the ASM standalone database created :

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Standby Out Off Sync

Standby Out Off syncDescription:
In this blog, we are going to see standby log out off sync how to check and sync the primary and standby logs.

Standby Log Sync:
The primary database logs are synced with the standby database in case any issue delay happens in log sync we restart the MRP process and defer enable the log_dest_2 location in the primary database.

Overall Steps:-

1. Bring Down the standby database.
2. Create a user and do multiple log switches in the primary database.
3. Start the standby database, and check the log difference.
4. primary database defers and enables the dest 2.
5. Restart the MRP process in the standby database.
6. Check the log Sync status.

Step 1:- Bring down the standby database.

SQL> shut immediate
ORA-01109: database not openDatabase dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Step 2:- Create user and do multiple log switches in the primary database.

Check current archive log status:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11

Create a user and insert the table.
SQL> create user agent identified by agent;
SQL> alter user agent default tablespace users quota unlimited on users;
SQL> grant connect, resource to agent;
SQL> conn agent/agent
Connected.
SQL> show user
USER is “AGENT”
SQL> create table agent1 as select * from user_tables;
SQL> conn / as sysdba
Connected.

Do multiple log switches.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

Check the archive log status.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
SQL>

Step 3:- Start the standby database, and check the log difference.

Startup standby database.
SQL> startup
ORACLE instance started.

Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 738197504 bytes
Database Buffers 503316480 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.

Check the log sync different.
SQL> 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# ORDER BY 1;

Step 4:-Primary database defer and enable the dest 2.

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

Step 5:- Restart the MRP process in the standby database.

SQL> alter database recover managed standby database disconnect nodelay;

Database altered.

Step 6:- Check the log sync status.

SQL> 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# ORDER BY 1; 2 3 4

**********************************************************************************

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Data Guard Manual Reinstate Using Flashback

Data Guard Manual Reinstate Using Flashback

Description:
In this blog, we are going to see step by step manual data guard reinstate process using the flashback database method.

Data Guard Reinstate:
After done failover to your Standby database so it becomes the new Primary. the old primary database can be reinstated as a standby database. this convert happens only in the database in flashback mode.

Overall Steps:

Check the flashback enabled.
Get the SCN number from the new primary database.
Bounce the old primary database open in the mount stage.
Flashback the database in SCN number of the new primary database.
Convert the database to physical standby.
Bounce the database and check the database role.

Step 1:- Check the flashback enabled after failover.
SELECT FLASHBACK_ON FROM V$DATABASE;

Step 2:- Get SCN number from the new primary database.
SQL> select to_char(standby_became_primary_scn) from v$database;

Step 3:- Bounce the Old primary database and open as mount stage.

SQL> shut immediate
SQL> startup nomount;
SQL> alter database mount;
Database altered.

Step 4:- Flashback the database in SCN number of the new primary database.

flashback database to scn 2370985;

flashback complete.

Step 5:- Convert the database to physical standby.

SQL> Alter Database Convert To Physical Standby;

Database altered.

Step 6:- Bounce the database and check the database role.

SQL> shut immediate

SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
SYSTEM READ ONLY PHYSICAL STANDBY

*******************************************************************************

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Data Guard Configuration Parameters

DATA GUARD CONFIGURATION PARAMETERS

Description :
In this blog, we are going to see oracle Data Guard Configuration parameters. and how to view and set the parameters.

Types of parameter configuration.


Independent parameters.
Primary database parameters.
Standby database parameters.

Independent parameters:
DB_UNIQUE_NAME:
DB_UNIQUE_NAME specifies a globally unique name for the database. must have a unique DB_UNIQUE_NAME for every database.it can be up to 30 characters and is case insensitive. The following characters are valid in a database name: alphanumeric characters, underscore (_), the number sign (#), and dollar sign ($).
db_unique_name=’unique_name’

SQL> show parameter db_unique_name
NAME                                 TYPE        VALUE
—————————–       ———– —————
db_unique_name              string      orcl

LOG_ARCHIVE_CONFIG:
        LOG_ARCHIVE_CONFIG enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs, and specifies the unique database names (DB_UNIQUE_NAME) for each database in the Data Guard configuration.
          We can add send,nosend,receive,noreceive,dgconfig,nodgconfig to this log_archive_parameter.
log_archive_config=’db_config=(Primary,Standby)’
SQL> show parameter LOG_ARCHIVE_CONFIG
NAME                                 TYPE        VALUE
——————————— ———– ——————-
log_archive_config            string      dg_config=(orcl,stand)
change value to log_archive_config:
SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(orcl,stand)’ SCOPE=both;
System altered.

LOG_ARCHIVE_MAX_PROCESSES:
LOG_ARCHIVE_MAX_PROCESSES specifies the number of archiver background processes (ARC0 through ARC9) Oracle initially invokes.
log_archivemax_processes=’4′

DB_CREATE_FILE_DEST:
DB_CREATE_FILE_DEST specifies the default location for Oracle-managed datafiles.
db_create_file_dest=’+DATA’
SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST=’/u02/oradata’;

Primary database parameters:
LOG_ARCHIVE_DEST_n:
The LOG_ARCHIVE_DEST_n initialization parameter defines up to ten destinations, each of which must specify either the LOCATION or the SERVICE attribute to specify where to archive the redo data. Location and service attributes are mandatory for the configuration.
SERVICE – it can be a tnsnames entry to identify the database.
SYNC – synchronous redo transmission.
ASYNC – Asynchronous redo transmission.

LOGARCHIVE_DEST_STATE_n:
Specify the availability state of the corresponding destination.
enabled:
Specifies that a valid log archive destination can be used for a subsequent archiving operation
defer:
Specifies that valid destination information and attributes are preserved, but the destination is excluded from archiving operations until re-enabled.
alternate:
Specifies that a log archive destination is not enabled but will become enabled if communications to another destination fail.

Standby Database Parameters:
DB_FILE_NAME_CONVERT:
            It converts the filename of a new data file on the primary database to a filename on the standby database.
SQL> show parameter DB_FILE_NAME_CONVERT
NAME                                            TYPE         VALUE
———————————    ———– ——————————
db_file_name_convert                 string      /u01/app/oracle/oradata/ORCL/
                                                                        , /u01/app/oracle/oradata/STAND/

LOG_FILE_NAME_CONVERT:
          LOG_FILE_NAME_CONVERT converts the filename of a new log file on the primary database to the filename of a log file on the standby database.
SQL> show parameter LOG_FILE_NAME_CONVERT
NAME                                                     TYPE         VALUE
———————————                        ——–        ————
log_file_name_convert                       string          /u01/app/oracle/oradata/ORCL/
                                                                        , /u01/app/oracle/oradata/STAND
alter system set db_file_name_convert=’/u02/oradata/orcl/datafile’ scope=spfile;
alter system set log_file_name_convert=’orcl’,’stand’ scope=spfile;

FAL_SERVER:
FAL means fetch archive log. It clearly indicates the name that fetches the archive log.
FAL_SERVER is used to fetch an archive log server for a standby database.
Value in FAL_SERVER parameter act as Oracle Net Service name which points to the standby database.
ALTER SYSTEM SET fal_server=’stand ‘SCOPE=both;
ALTER SYSTEM SET fal_client=’orcl ‘SCOPE=both;

FAL_CLIENT:
FAL_CLIENT specifies the FAL(fetch archive log) client name that is used by the FAL service.
It is configured for point the FAL Client.
Value in FAL_CLIENT is also an Oracle Net Service Name.
ALTER SYSTEM SET fal_server=’orcl ‘SCOPE=both;
ALTER SYSTEM SET fal_client=’stand’SCOPE=both;

STANDBY_FILE_MANAGEMENT:
STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.
MANUAL:
Disables automatic standby file management.
AUTO:
Enables automatic standby file management.
SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=both;

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Data Guard Architecture

Description:
In this blog, we are going to see oracle Data Guard architecture and a clear description of the data guard overall process.

What is Oracle Data Guard:

Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.
Data Guard maintains these standby databases as transactionally consistent copies of the production database.
If the production database becomes unavailable, Data Guard can switch any standby database to the production role.

Data Guard configuration pre-system checks:
Hardware :
       Hardware can be different for the standby and primary databases. Like CPU, hard disk capacity.
Operating system :
The operating system should be the same as both the primary and standby databases, if you use Linux on the primary side the same os will be the standby. OS versions can be different but the platform should be the same.
Software :
The same release of oracle database version in primary and standby side.
         Standby database enables archive and force logging method.
          Sysdba privilege required.

ORACLE DATA GUARD ARCHITECTURE:
Primary Database Process:

LGWR(log writer)
            Log writer collects transaction log information and updates to the online redo logs.
      LGWR modes:
            SYNC– in synchronous mode, it sends redo information directly to the RFS(remote file server) process on the standby database its waits for the confirmation before proceeding.
            ASYNC – in asynchronous mode its also sends redo information directly it does not wait before proceeding.
            In ASYNC mode LGWR submits the network I/O request to the network server (LNSn) process for the destination.

Archiver process(ARCn):
            ARCn or a SQL session performing an archival operation, create a copy of online redo logs locally for primary database recovery.
            ARCn also send simultaneously online logs to the RFS(remote file server)
            It’s also responsible for GAP solving in the standby database.

FAL (Fetch Archive Logs):
FAL provides a client/server mechanism for resolving gaps detected in the range of archive logs generated in the primary database and received to the standby database.

Standby Database Process:

RFS (remote file server process):
RFS it receives redo information from the primary database. RFS writes redo to standby redo logs or archived logs
Each primary LSN, ARCn has their own RFS

ARC archiver process;
ARCn process archives all standby redo logs.

Managed Recovery Process(MRP):
            For the physical standby database, only MRP applies archived log information to the physical standby database.
            MRP process commands:
                        Alter database recover managed standby database disconnect no delay.
                        Alter database recover managed standby database disconnect from the session.
                        Alter database recover managed standby database cancel.

Logical standby database:
LSP is controlling the applies of archived redo log information to the logical standby database.

Standby redo log files:
Standby redo log files are used only when the database is in the standby role to store redo data received from the primary database.
Standby redo logs from a separate pool of log filegroup.

********************************************************************************

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Dataguard Broker Configuration

DATA GUARD BROKER CONFIGURATION

Data Guard Broker:
An Oracle Data Guard Broker configuration is a logical definition of an Oracle Data Guard configuration allowing for centralized management and configuration of the physical resources involved in the configuration.

Step 1: Connect to both Databases (primary and standby) and issue the following command:-

SQL>alter system set dg_broker_start=true;
System altered.

Step 2: On the primary server, issue the following command to Register the Primary Server with the Broker.

[oratest@oracle dbs]$ dgmgrl sys/oracle@orcl
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Mon Nov 8 17:14:30 2021
Version 19.9.0.0.0

Welcome to DGMGRL, type “help” for information.
Connected to “orcl”
Connected as SYSDBA.

DGMGRL> create configuration 'dg_my_config' as primary database is 'orcl' connect 
identifier is orcl;

Configuration “dg_my_config” created with primary database “orcl”

Step 3: Now add the standby database:-

DGMGRL> add database stand as connect identifier is stand maintained as physical;

Database "stand" added

Step 4: Now we enable the new configuration;-

DGMGRL> enable configuration;

Enabled.
DGMGRL>

Step 5 ; The following commands show how to check the configuration and status of the databases from the broker:-

DGMGRL> show configuration;

Configuration - my_dg_config
Protection Mode: MaxPerformance
Members:
orcl     - Primary database
stand- Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 26 seconds ago)

DGMGRL> show database orcl;

   Database - orcl
  
   Role:       PRIMARY
   
  Intended State:  TRANSPORT-ON
  
   Instance(s):    orcl
   
   Database Status:
  
   SUCCESS

  DGMGRL> show database stand;

  Database - stand

  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      0 seconds (computed 1 second ago)

  Apply Lag:          0 seconds (computed 1 second ago)

  Average Apply Rate: 5.00 KByte/s

  Real Time Query:    OFF

 Instance(s):
    orcl
  Database Status:
   SUCCESS

Step 6: Stop/Start Managed Recovery:-

Stop managed recovery.

SQL> alter database recover managed standby database cancel;

Start managed recovery.

SQL> alter database recover managed standby database disconnect;

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Tag Archives: enable configuration

Backup Based Physical Standby Configuration Manually

DATA GUARD CONFIGURATION PARAMETERS

Data Guard Architecture

DATA GUARD BROKER CONFIGURATION

19c RMAN CONCEPTS:

Backup-based Cloning of a database using RMAN