How to Check Oracle Database and SYNC status ?

 
select name,open_mode,database_role from v$database;

select pr.thread# "Node", pr.primary "Primary",dr.Standby "DR",pr.primary-dr.Standby "Difference"
from (select thread#,max(sequence#) as primary from v$archived_log where
resetlogs_change#=(select resetlogs_change# from v$database) group by thread#) Pr,
(select thread#,max(sequence#) as Standby from v$archived_log where
resetlogs_change#=(select resetlogs_change# from v$database) and applied='YES' group by thread#) dr
where pr.thread#=dr.thread#;

RMAN Backup completion check query in %

 From the below query, you can check how much RMAN backup completed in %.

set echo on timing on
set lines 200
col username        format a10
col OPNAME          format a35
col SOFAR           format 999,999,999
col TOTALWORK       format 999,999,999
col 'Work Done %'   format 999.99
col Start_time      format a20
select SID,username,OPNAME,SOFAR,TOTALWORK,SOFAR/TOTALWORK*100 "Work Done %",
to_char(START_TIME,'DD-MON-YY hh24:mi:ss') "Start_time",
to_char(sysdate + TIME_REMAINING/3600/24,'DD-MON-YY hh24:mi:ss') "End_at"
from  v$session_longops
where sofar!=TOTALWORK and totalwork!=0
      and OPNAME like 'RMAN%';

Query to Check RMAN backup

 Following Query is used to check rman backup:

set lines 200 pages 200
col STATUS format a15
col hrs format 999.99
col start_time for a15
col INPUT_TYPE for a12
col END_TIME for a15
select
SESSION_KEY,SESSION_RECID,SESSION_STAMP ,INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs,
OUTPUT_DEVICE_TYPE
from V$RMAN_BACKUP_JOB_DETAILS
order by 1;

Put session_recid and session_stamp from above query and you will get log for that RMAN backup:

set lines 200
set pages 1000
select output
from GV$RMAN_OUTPUT
where session_recid =11038
and session_stamp =1070757497
order by recid;

Oracle FULL Database Compressed backup on Disk using RMAN

connect target /

RUN

{

ALLOCATE CHANNEL c1 DEVICE TYPE disk;

ALLOCATE CHANNEL c2 DEVICE TYPE disk;

ALLOCATE CHANNEL c3 DEVICE TYPE disk;

ALLOCATE CHANNEL c4 DEVICE TYPE disk;

sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';

sql 'ALTER SYSTEM SWITCH LOGFILE';

sql 'ALTER SYSTEM SWITCH LOGFILE';

sql 'ALTER SYSTEM SWITCH LOGFILE';

CROSSCHECK ARCHIVELOG ALL;

backup AS COMPRESSED BACKUPSET full database tag PRD_FULL format '/location/%d_%T_%s_%p_FULL';

backup as compressed backupset archivelog all format 'location/%d_%s_%p_%c_%t.arc.rman';

backup current controlfile format '/location/%d_%T_%s_%p_CONTROL.ctl';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

Drop Oracle Database

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup restrict;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 3557658624 bytes
Fixed Size                  2251448 bytes
Variable Size            1795163464 bytes
Database Buffers         1744830464 bytes
Redo Buffers               15413248 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
QAS       READ WRITE           PRIMARY

SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> start mount restrict
SP2-0310: unable to open file "mount.sql"
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
eccqas:oraqas 111>
eccqas:oraqas 111>
eccqas:oraqas 111>
eccqas:oraqas 111> sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 17 15:02:03 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount restrict
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 3557658624 bytes
Fixed Size                  2251448 bytes
Variable Size            1795163464 bytes
Database Buffers         1744830464 bytes
Redo Buffers               15413248 bytes
Database mounted.
SQL>  select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
QAS       MOUNTED              PRIMARY

SQL> drop database;

Database dropped.