Database File name change

startup
set pages 0 lines 400
spool move.sh
select 'mv '|| file_name ||' /oracle/TEST/datafile/'||tablespace_name||'_'||file_id||'.dbf' from dba_data_files;
spool off


spool dbmove.sql
select 'alter database rename file '||''''|| file_name ||''''||' to '||''''||'/oracle/TEST/datafile/'||tablespace_name||'_'||file_id||'.dbf'||''''||';' from dba_data_files order by file_id;
spool off

shut immediate

sh move.sh

startup mount;

@dbmove.sql

alter database open;

Creating Standby Database using RMAN

For a convention primary db as ‘primary’ adn the standby db as ‘standby’

PRIMARY:

Make sure database is archive log mode, and enable force logging.

1-> Enable Force Logging(Already there)

SQL> ALTER DATABASE FORCE LOGGING;

PRIMARY > select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

2 –>Setup Standby Redo Log:

In standby setup, archives log from primary shipped to standby and applied there. But if the primary database crashes, then the online redo logs will stay in primary side. As these logs hasn’t been archives, means those won’t be applied to standby, Which results in data loss. So if we add standby logfile, Data guard writes the Primary’s current redo log to a “standby redo log” allowing complete recovery in case of Primary site is lost.

SQL > select 'alter database add standby logfile '''||

regexp_substr(MEMBER,'/.+/')||'stdby_'||

regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||

''' size '||bytes||';' "Create Standby redo"

from v$logfile lf , v$log l

where l.group# = lf.group#

/ 2 3 4 5 6 7

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11  '/oradata/prim/redo/sbredo11.log' SIZE 100M;

3 –> Create the Password File for the Primary Database:
You have to create the password file since the user sys will be used by oracle to access the standby db and also be accessed by the standby db.
Make sure the password used for SYS on the primary database is the same used on the standby database.
# orapwd file=orapwSID password=your_pwd entries=5

4 – >Setup instance parameters for the primary database:

 *.DB_UNIQUE_NAME='primary'
 *.DB_NAME='primary'
 *.INSTANCE_NAME='primary'
 *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
 *.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
 *.LOG_ARCHIVE_DEST_2='SERVICE=standby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
 *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
 *.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
 *.LOG_ARCHIVE_FORMAT='arch_%r_%t_%s.arc'
 *.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
 *.SERVICE_NAMES='primary'
 *.STANDBY_FILE_MANAGEMENT='AUTO'
 *.DB_FILE_NAME_CONVERT='standby','primary' --Only use this parameter if you have different path in the standby server
 *.FAL_SERVER='standby'
 *.FAL_CLIENT='primary'

5.>Configure listener and tns entries:

STANDBY:

6 – pfile and password file creation in $ORACLE_HOME/db

7 – Backup Primary Database, Controlfile and Archivelogs
8 – Move Files to Standby Server
Steps for Both Databases
9 – Listener and tns setup  
– Restart the listener in both servers

Steps for Standby Database

10 – Setup instance parameters for the standby database.
*.DB_UNIQUE_NAME='standby'
*.DB_NAME='primary'
*.INSTANCE_NAME='standby'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='arch_%r_%t_%s.arc'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='standby'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.DB_FILE_NAME_CONVERT='primary','standby'
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'

12 – Startup nomount the standby database
SQL> startup nomount;
13 – Recover database using RMAN
Start the automatically redo apply
SQL> alter database recover managed standby database disconnect from session;

14 – Checking if Standby database is working properly
To check if the database is working correctly, the current log sequence must be the same in both databases.

SQL> archive log list
Also check if logs are being applied
SQL> select sequence#, applied from v$archived_log order by sequence#;

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

What are the Steps to create Physical Standby database?

1.Take a full hot backup of Primary database

2. Enable force logging to the database and Enable archiving

3. Prepare parameter file for primary database

4.Create standby control file

5.Transfer full backup, init.ora, standby control file to standby node.

6.Modify init.ora file on standby node.

7.Restore & Recover Standby database

8.Put Standby database in Managed Recover mode

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)

Creating physical stanby: https://www.sultandba.com/2020/05/creating-single-instance-physical.html

Backgound process: https://www.sultandba.com/2021/11/oracle-dataguard-background-process.html

Switchover on Oracle Standby Database

A switchover consists of the change of roles between the primary and standby database. After the switch, the primary will turn into the standby database and the standby into the primary.
 To do switchover ensure standby database working properly, receiving and applying the redologs from the primary database.The primary db must be in open status and the standby in mounted mode.

To check the database open mode:
select open_mode from v$database;

Check the Switchover Status for both databases.
select switchover_status from v$database;
It’s ok to perform the switchover if the primary database has the status of “SESSIONS ACTIVE” or “TO STANDBY” and for the standby database the status “SESSIONS ACTIVE” or “NOT ALLOWED”.

SWITCHOVER_STATUS column details:
NOT ALLOWED –>Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.
SESSIONS ACTIVE –> Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.
SWITCHOVER PENDING –> This is a standby database and the primary database switchover request has been received but not processed.
SWITCHOVER LATENT –> The switchover was in pending mode, but did not complete and went back to the primary database.
TO PRIMARY –> This is a standby database, with no active sessions, that is allowed to switch over to a primary database.
TO STANDBY –> This is a primary database, with no active sessions, that is allowed to switch over to a standby database.
RECOVERY NEEDED –> This is a standby database that has not received the switchover request.

Swtichover steps:

1.Make the primary db the standby db (Do that on the primary database)
alter database commit to switchover to standby with session shutdown;

2.Shutdown and start the former primary database in standby mode.
shutdown immediate;
startup nomount;
alter database mount standby database;

3.Turn the standby database into the new primary;
alter database commit to switchover to primary;

4.Shutdown and startup open the new primary database
shutdown immediate;
startup;

5.On the new standby database, start the redo log application
recover managed standby database disconnect from session;

Once done switchover_status column from v$database must be in one of the acceptable status as mentioned earlier.Check if the archive is being applied correctly:
select sequence#, applied from v$archived_log order by sequence#;