How to check database startup and shutdown details in Alertlog

How to check database startup and shutdown details in Alertlog

Db startup time:

cat alert*log |awk 'BEGIN{buf=""} /[0-9]:[0-9][0-9]:[0-9]/{buf=$0} /Starting ORACLE/{print buf,$0}'

Db shutdown time:

cat alert*log |awk 'BEGIN{buf=""} /[0-9]:[0-9][0-9]:[0-9]/{buf=$0} /Shutting down instance/{print buf,$0}'

Manual Switchover In Oracle Database (19c)

Manual Switchover In Oracle Database (19c)

Primary Database:

tnsping QPROD
tnsping QPRODR

echo $ORACLE_SID

sqlplus / as sysdba

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

SQL> select switchover_status from v$database;
SQL> alter database commit to switchover to physical standby with session shutdown;

SQL> !ps -ef|grep pmon
SQL> exit

(or)
SQL> shutdown immediate;

SQL> startup mount;
SQL> select name,instance_name,status,database_role,open_mode from v$instance,v$database;

SQL> recover managed standby database disconnect from session;

SQL> !ps -ef | grep mrp

SQL> !lsnrctl status

Standby Database:

tnsping QPROD

tnsping QPRODR

echo $ORACLE_SID

sqlplus / as sysdba

SQL> select name,instance_name,status,database_role,open_mode from v$instance,v$database;
SQL> alter database commit to switchover to primary;

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

SQL> alter database open;
SQL> select name,instance_name,status,database_role,open_mode from v$instance,v$database;

Manual Switchover Activity in Oracle Database 12c

Manual Switchover In Oracle Database (12c)

Switchover Steps

Primary Database:

tnsping QPROD

tnsping QPRODR

echo $ORACLE_SID

sqlplus / as sysdba

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

SQL> select switchover_status from v$database;

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

SQL> shutdown immediate;

SQL> startup mount;

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

SQL> recover managed standby database disconnect from session;

SQL> !ps -ef | grep mrp

SQL> !lsnrctl status

Standby Database:

tnsping QPROD

tnsping QPRODR

echo $ORACLE_SID

sqlplus / as sysdba

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

SQL> alter database commit to switchover to primary;

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

SQL> alter database open;

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

SQL> !lsnrctl status

Switchback Steps:

Follow same steps in reverse order.

Actual Steps:

PRIMARY:

Check the database status:

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

NAME      STATUS       DATABASE_ROLE    OPEN_MODE

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

QPROD      OPEN         PRIMARY          READ WRITE

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1          23326

SQL>

Check the sync status:

SQL> @sync_p.sql

   THREAD# MAX(SEQUENCE#)

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

         1          23307

Check the switchover status:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

Issue the command:

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

Database altered.

SQL> shut immediate;

ORA-01012: not logged on

SQL> exit

Make sure the background processes of primary is down.

ps -ef|grep pmon

Startup the database mount state:

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3.2212E+10 bytes

Fixed Size                  5292336 bytes

Variable Size            4294975184 bytes

Database Buffers         2.7850E+10 bytes

Redo Buffers               61808640 bytes

SQL> alter database mount;

Database altered.

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

NAME      INSTANCE_NAME    STATUS       DATABASE_ROLE    OPEN_MODE

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

QPROD        QPROD        MOUNTED      PHYSICAL STANDBY MOUNTED

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL> !ps -ef|grep mrp

oraprod  5060  4931   0 22:22:09 pts/1       0:00 /usr/bin/bash -c ps -ef|grep mrp

oraprod  5062  5060   0 22:22:09 pts/1       0:00 grep mrp

oraprod  5025     1   0 22:21:54 ?           0:03 ora_mrp0_QPROD

Standby:

Check the database status:

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

NAME      INSTANCE_NAME    STATUS       DATABASE_ROLE    OPEN_MODE

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

QPROD     QPRODR           MOUNTED      PHYSICAL STANDBY MOUNTED

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)

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

         1          23326

Check the sync status:

SQL> @sync_s.sql

    Thread Last Sequence Received Last Sequence Applied Difference

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

         1                  23307                 23307          0

Issue the command:

SQL> alter database commit to switchover to primary;

Database altered.

Open the database:

SQL> alter database open;

Database altered.

Check the database status:

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

NAME      INSTANCE_NAME    STATUS       DATABASE_ROLE    OPEN_MODE

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

QPROD     QPRODR           OPEN         PRIMARY          READ WRITE

SQL> alter system switch logfile;

System altered.

Linux 6.7 Virtualbox Images

  Linux Fresh 6.7 Installed Image

Linux 7.8 Virtualbox Images

 Linux Fresh 7.8 Installed Image

RMAN Database Full Backup & Recovery

RMAN Database Full Backup & Recovery

-Take RMAN DB FULL Backup 

-Simulate Failure 

-Start Database Recovery 

nohup rman target / cmdfile=/u01/mqm/MQM_RMAN_Backup_script.sh log=/u01/mqm/rman_fullbackup_db.log &

cat mqm_rmanbackup.txt

run

{

allocate channel t1 type disk;

backup format '/u01/mqm/full_db_%t_%p%p' database;

backup format '/u01/mqm/archive_%t_%p%p' archivelog all;

backup format '/u01/mqm/cntrl_%s_%p_%t' current controlfile;

Backup format '/u01/mqm/spfile_%s_%p_%t' spfile;

release channel t1;

}

cat rman_exec.sh 

rman target / cmdfile=mqm_rmanbackup.txt

nohup ./rman_exec.sh &

RMAN> list backup of database summary;

RMAN> list backup summary;

Simulate Failure:

Take the location of spfile, datafile & control file

SQL> select name from v$datafile;

SQL> select member from v$logfile;

SQL> select name from v$controlfile;

SQL> show parameter spfile;

SQL> create pfile from spfile;

File created.

Delete all the spfile, datafiles & Control files from the server.

SQL> startup;

ORACLE instance started.

Total System Global Area  801112064 bytes

Fixed Size     8797872 bytes

Variable Size   423625040 bytes

Database Buffers   360710144 bytes

Redo Buffers     7979008 bytes

ORA-00205: error in identifying control file, check alert log for more info

Restore Controlfile:

run

{

allocate channel c1 type disk;

set until time "to_date('2022/02/26 15:47:00','yyyy/mm/dd HH24:MI:SS')";

restore controlfile from '/u01/mqm/cntrl_23_1_1097682448';

release channel c1;

}

Bring the database at mount state

RMAN> alter database mount;

Statement processed

(or)

sqlplus / as sysdba

SQL> alter database mount;

Database altered.

Restore Database:

run 

{

allocate channel c1 type disk;

set until time "to_date('2022/02/26 15:47:00','yyyy/mm/dd HH24:MI:SS')";

restore database;

recover database;

release channel c1;

}

RMAN> alter database open resetlogs;

Statement processed

(or)

sqlplus / as sysdba

SQL> alter database open resetlogs;

Database altered.