- Standby Redo Logs (SRLs): The primary and standby databases both require SRLs. As the remote file server (RFS) process on the standby receives redo data, it writes directly to the standby redo logs.
- The Switchover Command: When you initiate the switchover command, Oracle forces the current active primary logs to be archived and verifies that the primary and standby databases are synchronized.
- Role Reversal: Once the logs have successfully cleared and applied, the primary database cleanly transitions to a standby role, and the standby transitions to a primary role.
- Continuous Application: When Real-Time Apply is active, the Redo Apply process pulls redo records straight out of the Standby Redo Log as the RFS process is writing to it.
- Zero Waiting: It does not wait for the SRL to fill up and undergo a log switch into an Archived Redo Log (which is the default "Archived Apply" behavior).
- Result: Because data is applied almost instantly, the switchover sequence pauses for only a brief moment to finish the final log switch, greatly limiting data loss and transition time
- SRLs on Both Sides: You must provision Standby Redo Logs on both the primary and standby databases. Once the switchover completes, the old primary becomes a standby and will need SRLs ready to receive redo from the new primary.
- Verify Configuration: Ensure you run the
ALTER DATABASE SWITCHOVER TO target_db VERIFY;command to guarantee your Standby Redo Logs are correctly configured before performing the actual switchover
1.
Take 4 putty sessions ( 2 for PRIMARY DATABASE and 2 for
DR)
2.
Tail alert log in PRIMARY DATABASE and DR.(show parameter
diag OR show parameter back OR show parameter dump)
ON PRIMARY DATABASE Prerequisite
3.
Check Database Status
select
name,open_mode,controlfile_type,database_role,switchover_status from
v$database;
set pagesize 24 linesize 87
col name format a12
col STATUS format a12
col WRL_PARAMETER format a40
select name, STATUS, WRL_PARAMETER from
v$database,v$encryption_wallet;
4.
Take below parameter value(From PRIMARY DATABASE And DR)
show parameter job_queue_processes
show parameter aq_tm_processes
show parameter archive_lag_target
5.
Check job running during that time (crontab ,RMAN
backup,RMAN archive backup)
! ps -ef|grep -i
rman=========>backup rman
SELECT * From dba_jobs_running;
select
owner,job_name,ELAPSED_TIME,SESSION_ID,CPU_USED from dba_scheduler_running_jobs;
SELECT OWNER, JOB_NAME, START_DATE, END_DATE,
ENABLED FROM DBA_SCHEDULER_JOBS WHERE ENABLED='TRUE' AND OWNER <> 'SYS';
If any session is running based
on confirmation. Please kill it
Select 'alter system kill session
'''||sid||','||serial#||',@'||inst_id||''' immediate;' from gv$session where username <>'SYS';
6. Check log gap using @dr and
takeout the screenshot, if there is no gap proceed to next step.
alter system switch logfile;
select max(sequence#) from v$loghist;
SWITCH OVER: -
Primary Database Server
1)
Check Database Status
select
name,open_mode,controlfile_type,database_role from v$database;
select
name,open_mode,database_role,switchover_status from v$database; ----
SELECT INST_ID, DEST_ID,
DEST_NAME, NAME_SPACE, STATUS,
LOG_SEQUENCE, TYPE, PROCESS,
REGISTER, ERROR
FROM GV$ARCHIVE_DEST
WHERE STATUS != 'INACTIVE'
AND TARGET='STANDBY'
ORDER BY INST_ID, DEST_ID;
2)
Check value and change value to zero
show parameter job_queue_processes
show parameter aq_tm_processes
show parameter archive_lag_target
alter
system set job_queue_processes=0 scope=both;
alter system set
aq_tm_processes=0 scope=both;
alter system set
archive_lag_target=0 scope=both;
show parameter job_queue_processes
show parameter aq_tm_processes
show parameter archive_lag_target
3)
switch over primary Database to Status database - On PRIMARY
DATABASE server
select
name,open_mode,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ----------------
--------------------
DB PRIMARY TO STANDBY
This should return to_standby
or sessions active.
alter database commit to switchover to
physical standby with session shutdown; -----
4)
Verify the DR
alert logs to ensure DR DB got switchover commands from PRIMARY DATABASE.
5)
check pmon status.
Database should be down.
6)
Start the database in mount stage and check status.
STARTUP MOUNT;
select
name,open_mode,controlfile_type,database_role from v$database;
set
pagesize 24 linesize 87
col
name format a12
col
STATUS format a12
col
WRL_PARAMETER format a40
select
name, STATUS, WRL_PARAMETER from v$database,v$encryption_wallet;
10. start the listener if not
started
11. check value and set value as
it was set earlier
show parameter job_queue_processes
show parameter aq_tm_processes
show parameter archive_lag_target
eg
alter system set
job_queue_processes=1000 scope=both;
alter system set
aq_tm_processes=10 scope=both;
ON DR server
1)
Check switchover status in DR.
select
name,open_mode,controlfile_type,database_role,switchover_status from
v$database; SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG_HISTORY GROUP BY
THREAD#;
show parameter aq_tm_processes
show parameter
job_queue_processes
show parameter archive_lag_target
select name, switchover_status from
v$database; ---
Note: This should return "TO
PRIMARY" or "SESSIONS ACTIVE”, Do not proceed if there is any other
status{Like Not allowed}
2) in DR, Cancel MRP Process and start Switch over
select switchover_status from
v$database
select
name,open_mode,database_role,switchover_status from v$database;
recover managed standby database
cancel; --
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
WITH SESSION SHUTDOWN; ----
select name,,open_mode, database_role,switchover_status from v$database;
2)
start Database if it is not started
STARTUP MOUNT;
set pagesize 24 linesize 87
col name format a12
col STATUS format a12
col WRL_PARAMETER format a40
select name, STATUS, WRL_PARAMETER
from v$database,v$encryption_wallet;
ALTER DATABASE OPEN;
3)
check Database Status
select
name,open_mode,controlfile_type,database_role,switchover_status from
v$database;
4)
start the listener if required.
ps -ef|grep tns
5)
Ensure that below
parameter are set to required value ( refer PRIMARY DATABASE values in step 3).
alter system set
job_queue_processes=<value> scope=both;
alter system set aq_tm_processes=<value>
scope=both;
alter system set
archive_lag_target =<value> scope=both;
show parameter aq_tm_processes
show parameter
job_queue_processes
show parameter archive_lag_target
6) Ensure reverse replication is working fine
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
6)
Check temp tablespace and undo Tablespace on Current PRIMARY
DATABASE.
select * from v$tempfile;
Final Step
In old PRIMARY DATABASE (current DR) enable recovery.
ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE DISCONNECT FROM SESSION;
===================================================================
SWITCH BACK: -
Current Primary
1. SELECT INST_ID, DEST_ID,
DEST_NAME, NAME_SPACE, STATUS,
LOG_SEQUENCE, TYPE, PROCESS,
REGISTER, ERROR
FROM GV$ARCHIVE_DEST
WHERE STATUS != 'INACTIVE' AND
TARGET='STANDBY'
ORDER BY INST_ID, DEST_ID;
2. SELECT LOCAL.THREAD#,
LOCAL.SEQUENCE#
FROM (SELECT THREAD#, SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE DEST_ID=1) LOCAL
WHERE LOCAL.SEQUENCE# NOT IN
(SELECT SEQUENCE# FROM
V$ARCHIVED_LOG
WHERE DEST_ID=2 AND THREAD# =
LOCAL.THREAD#);
3. SELECT THREAD#, SEQUENCE# FROM
V$LOG WHERE STATUS='CURRENT';
5. SELECT SWITCHOVER_STATUS FROM
V$DATABASE;
6. ALTER DATABASE COMMIT TO
SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
7. SHUTDOWN IMMEDIATE
8. STARTUP NOMOUNT
9. ALTER DATABASE MOUNT STANDBY DATABASE;
10. ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH;
11. RECOVER MANAGED STANDBY
DATABASE DISCONNECT;
-------------------------------------------------------------------------------------------------------------
Current Standby
4. SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG_HISTORY GROUP BY THREAD#;
12. SELECT SWITCHOVER_STATUS FROM
V$DATABASE;
13. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
14. SHUTDOWN IMMEDIATE
15. STARTUP NOMOUNT
16. ALTER DATABASE MOUNT STANDBY DATABASE;
17. ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
18. ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
19. SHUTDOWN IMMEDIATE
20. STARTUP
21. alter system switch logfile;
For More Details
https://www.oracle.com/technetwork/database/availability/maa-roletransitionbp-2621582.pdf
==================== EASYDB SWITCHOVER ACTIVITY =============
+++ check configuration
DGMGRL> show configuration verbose;
+++ Validate network configuration
DGMGRL> VALIDATE NETWORK CONFIGURATION FOR all;
+++ Validate primary and standby
DGMGRL> VALIDATE DATABASE 'Primary_unique_name';
DGMGRL> VALIDATE DATABASE 'Standby_unique_name';
Note:- EASYDB is configured with Dataguard
broker
Open a command Prompt in EASYDB DC server
Connect to DGMGRL
Command :-
dgmgrl sys/###@EASYDB
In above command (EASYDB) refers to the Primary database name
Now, Type :- show configuration
You will see the configuration details
===== Before switchover configuration
details are as below =====
Configuration - EASYDB_dg
Protection Mode: MaxPerformance
Members:
EASYDB - Primary database
Warning: ORA-16792: configurable property value is inconsistent with
database setting
EASYDB_dr - Physical standby
database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
(status updated 20 seconds ago)
Before Switchover the configuration is, EASYDB is primary database and
EASYDB_dr is standby database.
Now you can proceed for switchover
Command:-
switchover to EASYDB_dr;
Wait and check the alert logs.
==================== EASYDB SWITCHBACK ACTIVITY =======================
Note:- EASYDB is configured with Dataguard
broker
Open a command Prompt in EASYDB DC server
Connect to DGMGRL
Command :-
dgmgrl sys/####@EASYDB
In above command (EASYDB) refers to the database name
Now, Type :- show configuration
You will see the configuration details
===== Before switchover configuration
details are as below =====
Configuration - EASYDB_dg
Protection Mode: MaxPerformance
Members:
EASYDB_dr - Primary database
Warning: ORA-16792: configurable property value is inconsistent with
database setting
EASYDB - Physical standby
database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
(status updated 20 seconds ago)
After Switchover the configuration is, EASYDB_dr is primary database and
EASYDB is standby database.
Now you can proceed for switchover
Command:-
switchover to EASYDB;
Wait and check the alert logs.
=================================================================================
C:\Users\oracleadmin>dgmgrl
sys/welcome#123@EASYDB
DGMGRL for 64-bit Windows: Version
12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All
rights reserved.
Welcome to DGMGRL, type "help"
for information.
Connected as SYSDG.
DGMGRL>
DGMGRL>
DGMGRL> show configuration
Configuration - EASYDB_dg
Protection Mode: MaxPerformance
Members:
EASYDB - Primary database
Warning: ORA-16792: configurable property value is inconsistent with
database setting
EASYDB_dr
- Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
(status updated 20 seconds ago)
DGMGRL>
DGMGRL>
DGMGRL> show database EASYDB
Database - EASYDB
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
EASYDB
Warning: ORA-16714: the value of property ArchiveLagTarget is
inconsistent with the database setting
Database Status:
WARNING
DGMGRL>
DGMGRL>
DGMGRL> show database EASYDB_dr
Database - EASYDB_dr
Role: PHYSICAL
STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
(computed 0 seconds ago)
Apply Lag: 0 seconds
(computed 0 seconds ago)
Average Apply Rate: 63.00 KByte/s
Real Time Query: OFF
Instance(s):
EASYDB
Database Status:
SUCCESS
DGMGRL>
DGMGRL>
DGMGRL> validate database EASYDB
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
EASYDB: Off
DGMGRL>
DGMGRL>
DGMGRL> validate database EASYDB_dr
Database Role: Physical
standby database
Primary Database: EASYDB
Ready for Switchover: Yes
Ready for Failover: Yes
(Primary Running)
Temporary Tablespace File Information:
EASYDB
TEMP Files: 2
EASYDB_dr
TEMP Files: 3
Flashback Database Status:
EASYDB: Off
EASYDB_dr: Off
Current Log File Groups Configuration:
Thread # Online Redo Log
Groups Standby Redo Log Groups Status
(EASYDB) (EASYDB_dr)
1 3 3 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log
Groups Standby Redo Log Groups Status
(EASYDB_dr) (EASYDB)
1 3 3 Insufficient SRLs
DGMGRL>
DGMGRL>
DGMGRL> switchover to EASYDB_dr;
Performing switchover NOW, please wait...
Operation requires a connection to instance
"EASYDB" on database "EASYDB_dr"
Connecting to instance "EASYDB"...
Connected as SYSDBA.
New primary database "EASYDB_dr"
is opening...
Operation requires start up of instance
"EASYDB" on database "EASYDB"
Starting instance "EASYDB"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "EASYDB_dr"
DGMGRL>
Details
NOTE: Running DGDiagnostic.sh on both primary and standby will run diag script (appropriate for that database), it will collect sqlnet files and run basic network configuration checks.
Run this on Primary Database
REM srdc_DGPrimary_diag.sql - collect primary database information in dataguard environment
define SRDCNAME='DG_PRIM_DIAG'
set markup html on spool on
set TERMOUT off;
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(value)||'_'||
to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$parameter where lower(name)='instance_name';
REM
spool &&SRDCSPOOLNAME..htm
Set heading off;
select '+----------------------------------------------------+' from dual
union all
select '| Script version: '||'25-Aug-2021' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp: '|| to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/
set heading on;
set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
column output new_value dbname
set linesize 2000
set pagesize 50000
set numformat 999999999999999
set trim on
set trims on
set markup html on
set markup html entmap off
set feedback on
ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
SELECT sysdate time FROM dual;
select decode(count(cell_path),0,'Non-Exadata','Exadata') "System" from v$cell;
set echo on
-- In the following output the DATABASE_ROLE should be PRIMARY as that is what this script is intended to be run on.
-- PLATFORM_ID should match the PLATFORM_ID of the standby(s) or conform to the supported options in
-- Note: 413484.1 Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration
-- Note: 1085687.1 Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration
-- OPEN_MODE should be READ WRITE.
-- LOG_MODE should be ARCHIVELOG.
-- FLASHBACK can be YES (recommended) or NO.
-- If PROTECTION_LEVEL is different from PROTECTION_MODE then for some reason the mode listed in PROTECTION_MODE experienced a need to downgrade.
-- Once the error condition has been corrected the PROTECTION_LEVEL should match the PROTECTION_MODE after the next log switch;
SELECT database_role role, name, db_unique_name, checkpoint_change#, current_scn, resetlogs_change#, platform_id, open_mode, log_mode, flashback_on, protection_mode, protection_level FROM v$database;
SELECT dbid, name, cdb from v$database;
-- Check is using Enterprise or Standard Edition
SELECT BANNER from v$version where BANNER like '%Oracle%';
-- FORCE_LOGGING is not mandatory but is recommended.
-- REMOTE_ARCHIVE should be ENABLE.
-- SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI must be enabled if the standby associated with this primary is a logical standby.
-- During normal operations it is acceptable for SWITCHOVER_STATUS to be SESSIONS ACTIVE or TO STANDBY.
-- DG_BROKER can be ENABLED (recommended) or DISABLED.;
column force_logging format a13 tru
column remote_archive format a14 tru
column supplemental_log_data_pk format a24 tru
column supplemental_log_data_ui format a24 tru
column dataguard_broker format a16 tru
SELECT force_logging, remote_archive, supplemental_log_data_pk, supplemental_log_data_ui, switchover_status, standby_became_primary_scn, dataguard_broker FROM v$database;
-- The following query gives us information about catpatch. From this we can tell if the catalog version doesn''t match the image version it was started with.
column version format a10 tru
SELECT version, modified, status FROM dba_registry WHERE comp_id = 'CATPROC';
-- Check how many threads are enabled and started for this database. If the number of instances below does not match then not all instances are up.
SELECT thread#, instance, status FROM v$thread;
-- The number of instances returned below is the number currently running. If it does not match the number returned in Threads above then not all instances are up.
-- VERSION should match the version from CATPROC above.
-- ARCHIVER can be (STOPPED | STARTED | FAILED). FAILED means that the archiver failed to archive a log last time, but will try again within 5 minutes.
-- LOG_SWITCH_WAIT the ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for.
-- Note that if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online redo log, then the value is NULL.
column host_name format a32 wrap
SELECT thread#, instance_name, host_name, version, archiver, log_switch_wait, startup_time FROM gv$instance ORDER BY thread#;
-- Password file Users
col USERNAME format a30
SELECT USERNAME,SYSDBA,SYSOPER,SYSASM,U.ACCOUNT_STATUS "ACCOUNT_STATUS",U.AUTHENTICATION_TYPE "AUTHENTICATION_TYPE" FROM V$PWFILE_USERS JOIN DBA_USERS U USING (USERNAME);
select file_name, format, is_asm, con_id from v$passwordfile_info;
-- Check incarnation of the database, the standby and primary must have the same CURRENT incarnation:
select incarnation#, resetlogs_change#, resetlogs_time, prior_resetlogs_change#, prior_resetlogs_time,
status, resetlogs_id, prior_incarnation#, flashback_database_allowed from gv$database_incarnation;
-- Number of log switches in less than 20min in last 24hours
SELECT a.LOG_SWITCHES_UNDER_20_MINS "LOG_SWITCHES_UNDER_20_MINS", b.LOG_SWITCHES_OVER_20_MINS "LOG_SWITCHES_OVER_20_MINS" from
(select count(*) "LOG_SWITCHES_UNDER_20_MINS" from v$archived_log join v$archive_dest using (dest_id) where target='PRIMARY' AND destination IS NOT NULL and first_time > (sysdate -1) and (next_time-first_time) < 1/72) a ,
(select count(*) "LOG_SWITCHES_OVER_20_MINS" from v$archived_log join v$archive_dest using (dest_id) where target='PRIMARY' AND destination IS NOT NULL and first_time > (sysdate -1) and (next_time-first_time) > 1/72) b;
-- Details of time slot where number of log switches are more than 4 per hour in last 7 days
select Day||':00-59' "Day",LOG_SWITCHES_UNDER_20_MINS from ( with agg as( select to_char(First_time,'MON DD,YYYY HH24') Day,count(*) "LOG_SWITCHES_UNDER_20_MINS",dense_rank() over (partition by to_char(First_time,'MON DD,YYYY HH24') order by 2 desc) as rank from v$archived_log join v$archive_dest using (dest_id) where target='PRIMARY' AND destination IS NOT NULL
and (next_time-first_time) <= 1/72 and First_time > (sysdate-7)
group by to_char(First_time,'MON DD,YYYY HH24')
)
select Day,LOG_SWITCHES_UNDER_20_MINS from agg order by 1 desc ) where LOG_SWITCHES_UNDER_20_MINS >4;
-- History: log switches details of last 7days
SELECT Day,LOG_SWITCHES_UNDER_20_MINS,LOG_SWITCHES_OVER_20_MINS from (
select to_char(First_time,'MON DD,YYYY') Day,count(*) "LOG_SWITCHES_UNDER_20_MINS" from v$archived_log join v$archive_dest using (dest_id) where target='PRIMARY' AND destination IS NOT NULL and (next_time-first_time) <= 1/72 and First_time > (sysdate-7) group by to_char(First_time,'MON DD,YYYY')) a join (select to_char(First_time,'MON DD,YYYY') Day,count(*) "LOG_SWITCHES_OVER_20_MINS" from v$archived_log join v$archive_dest using (dest_id) where target='PRIMARY' AND destination IS NOT NULL and (next_time-first_time) > 1/72 and First_time > (sysdate-7) group by to_char(First_time,'MON DD,YYYY')) b using (Day)
order by Day desc;
-- Check the max redo generation speed of the day
-- and average speed of top 30 redo speed and recommendation of ORL size to ensure switch happening on every 20min
select time "Day" ,round(max(value/1024),2) "Max_Redo_KB/s",round(avg(value)/1024,2) "Avg_Redo(top30_mins)_KB/s",round(avg(value)*60*20/1024,0) "Recommended_ORL_size_KB"
from ( with agg as (
select to_char(BEGIN_TIME,'DD/MON/YYYY') time,value, dense_rank() over (partition by to_char(BEGIN_TIME,'DD/MON/YYYY') order by value desc) as rank from dba_hist_sysmetric_history where metric_name = 'Redo Generated Per Sec' and to_char(BEGIN_TIME,'DD/MON/YYYY') > sysdate -7) select time,value from agg where agg.rank <31
) group by time order by 1 desc;
column minutes format a12
SELECT (CASE WHEN bucket = 1 THEN '<= ' || TO_CHAR(bucket * 5) WHEN (bucket >1 AND bucket < 9) THEN TO_CHAR(bucket * 5 - 4) || ' TO ' || TO_CHAR(bucket * 5) WHEN bucket > 8 THEN '>= ' || TO_CHAR(bucket * 5 - 4) END) "MINUTES", switches "LOG_SWITCHES" FROM (SELECT bucket , COUNT(b.bucket) SWITCHES FROM (SELECT WIDTH_BUCKET(ROUND((b.first_time - a.first_time) * 1440), 0, 40, 8) bucket FROM v$archived_log a, v$archived_log b WHERE a.sequence# + 1 = b.sequence# AND a.dest_id = b.dest_id AND a.thread# = b.thread# AND a.dest_id = (SELECT MIN(dest_id) FROM gv$archive_dest WHERE target = 'PRIMARY' AND destination IS NOT NULL)) b GROUP BY bucket ORDER BY bucket);
-- Check the number and size of online redo logs on each thread.
SELECT thread#, group#, sequence#, bytes, archived ,status, blocksize FROM v$log ORDER BY thread#, group#;
-- To get the online redo log file location
SELECT thread#, group#, sequence#, bytes, archived ,l.status, blocksize, member FROM v$log l join v$logfile f using (GROUP#) ORDER BY thread#, group#;
-- The following query is run to see if standby redo logs have been created in preparation for switchover.
-- The standby redo logs should be the same size as the online redo logs.<br>There should be (( # of online logs per thread + 1) * # of threads) standby redo logs.
-- A value of 0 for the thread# means the log has never been allocated.
SELECT thread#, group#, sequence#, bytes, archived, status, blocksize FROM v$standby_log order by thread#, group#;
-- This query provides the number of standby redo logs, per instance
column count(*) new_value count
column CHECK_NAME format a40
select inst_id, dest_id, standby_logfile_count from gv$archive_dest_status where status <> 'INACTIVE' and db_unique_name <> 'NONE' order by dest_id, inst_id;
-- This query produces a list of defined archive destinations. It shows if they are enabled, what process is servicing that destination,
-- if the destination is local or remote.
column destination format a35 wrap
column process format a7
column ID format 99
column mid format 99
SELECT thread#, dest_id, destination, target, schedule, process FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;
-- This select will give further detail on the destinations as to what options have been set.
-- Register indicates whether or not the archived redo log is registered in the remote destination control fileOptions.
set numwidth 8
column archiver format a8
column affirm format a6
column error format a55 wrap
column register format a8
SELECT thread#, dest_id, gvad.archiver, transmit_mode, affirm, async_blocks, net_timeout, max_failure, delay_mins, reopen_secs reopen, register, binding FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;
-- This shows if redo transport is configured
col name format a20
col value format a2000
column CHECK_NAME format a40
select name, inst_id, value from gv$parameter where name like 'log_archive_dest_%' and upper(value) like '%SERVICE%' order by inst_id;
-- The following select will show any errors that occured the last time an attempt to archive to the destination was attempted.
-- If ERROR is blank and status is VALID then the archive completed correctly.
SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;
-- The query below will determine if any error conditions have been reached by querying the v$dataguard_status view (view only available in 9.2.0 and above).
column message format a80
SELECT instance, thread#, name, pid, action, client_role, client_pid, sequence#, block#, dest_id
from v$dataguard_process order by thread#, pid;
SELECT gvi.thread#, timestamp, message FROM gv$dataguard_status gvds, gv$instance gvi WHERE gvds.inst_id = gvi.inst_id AND severity in ('Error','Fatal') ORDER BY timestamp, thread#;
-- Query v$managed_standby to see the status of processes involved in the shipping redo on this system.
-- Does not include processes needed to apply redo.
SELECT inst_id, thread#, process, pid, status, client_process, client_pid, sequence#, block#, active_agents, known_agents FROM gv$managed_standby ORDER BY thread#, pid;
-- The following query will determine the current sequence number and the last sequence archived.
-- If you are remotely archiving using the LGWR process then the archived sequence should be one higher than the current sequence.
-- If remotely archiving using the ARCH process then the archived sequence should be equal to the current sequence.
-- The applied sequence information is updated at log switch time.
-- The "Last Applied" value should be checked with the actual last log applied at the standby, only the standby is guaranteed to be correct.
SELECT cu.thread#, cu.dest_id, la.lastarchived "Last Archived", cu.currentsequence "Current Sequence", appl.lastapplied "Last Applied" FROM (select gvi.thread#, gvd.dest_id, MAX(gvd.log_sequence) currentsequence FROM gv$archive_dest gvd, gv$instance gvi WHERE gvd.status = 'VALID' AND gvi.inst_id = gvd.inst_id GROUP BY thread#, dest_id) cu, (SELECT thread#, dest_id, MAX(sequence#) lastarchived FROM gv$archived_log WHERE resetlogs_change# = (SELECT resetlogs_change# FROM v$database) AND archived = 'YES' GROUP BY thread#, dest_id) la, (SELECT thread#, dest_id, MAX(sequence#) lastapplied FROM gv$archived_log WHERE resetlogs_change# = (SELECT resetlogs_change# FROM v$database) AND applied = 'YES' GROUP BY thread#, dest_id) appl WHERE cu.thread# = la.thread# AND cu.thread# = appl.thread# AND cu.dest_id = la.dest_id AND cu.dest_id = appl.dest_id ORDER BY 1, 2;
-- The following select will attempt to gather as much information as possible from the standby.
-- Standby redo logs are not supported with Logical Standby until Version 10.1.
-- The ARCHIVED_SEQUENCE# from a logical standby is the sequence# created by the apply, not the sequence# sent from the primary.
set numwidth 8
column dest_id format 99
column Active format 99
SELECT dest_id, database_mode, recovery_mode, protection_mode, standby_logfile_count, standby_logfile_active FROM v$archive_dest_status WHERE destination IS NOT NULL;
-- Non-default init parameters. For a RAC DB Thread# = * means the value is the same for all threads (SID=*)
-- Threads with different values are shown with their individual thread# and values.
column num noprint
SELECT num, '*' "THREAD#", name, value FROM v$PARAMETER WHERE NUM IN (SELECT num FROM v$parameter WHERE (isdefault = 'FALSE' OR ismodified <> 'FALSE') AND name NOT LIKE 'nls%'
MINUS
SELECT num FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvpa.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%')
UNION
SELECT num, TO_CHAR(thread#) "THREAD#", name, value FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%' ORDER BY 1, 2;
-- LAST APPLIED ARCHIVELOG FILES
--
select dest_id,thread#,sequence# "sequence#",first_time,first_change#,next_time,next_change# from v$archived_log
where (dest_id,thread#,sequence#) in
(select dest_id,thread#,max(sequence#) from v$archived_log where dest_id in
(select to_number(ltrim(name,'log_archive_dest_')) from v$parameter where lower(name) like 'log_archive_dest%' and upper(value) like '%SERVICE=%'
minus
select dest_id from v$archive_dest where VALID_ROLE in ('STANDBY_ROLE')) and applied='YES' group by dest_id,thread#)
and activation# in (select activation# from v$database)
and 'PRIMARY' =(select database_role from v$database)
union
select dest_id,thread#,sequence# "sequence#",first_time,first_change#,next_time,next_change# from v$archived_log
where (dest_id,thread#,sequence#) in
(select dest_id,thread#,max(sequence#) from v$archived_log where dest_id in (select dest_id from v$archive_dest where destination is not null and upper(VALID_ROLE) in ('ALL_ROLES','STANDBY_ROLE') and dest_id <> 32) and applied='YES' group by dest_id,thread#)
and activation# in (select activation# from v$database)
and 'PHYSICAL STANDBY' =(select database_role from v$database)
;
-- ARCHIVELOG APPLY RANGE
--
select b.dest_id,a.thread#,a.sequence# "latest archivelog" ,b.sequence# "latest archive applied",a.sequence# - b.sequence# "# Not Applied" from
(select dest_id,thread#,max(sequence#) sequence# from v$archived_log
where dest_id in (select to_number(ltrim(name,'log_archive_dest_')) from v$parameter where lower(name) like 'log_archive_dest%' and upper(value) like '%SERVICE=%'
minus
select dest_id from v$archive_dest where VALID_ROLE in ('STANDBY_ROLE'))
and applied='YES' group by dest_id,thread#) b,
(select thread#,max(sequence#) sequence# from v$archived_log where activation# in (select activation# from v$database) group by thread#) a
where a.thread#=b.thread#
and 'PRIMARY' =(select database_role from v$database)
union
select b.dest_id,a.thread#,a.sequence# "latest archivelog" ,b.sequence# "latest archive applied",a.sequence# - b.sequence# "# Not Applied" from
(select dest_id,thread#,max(sequence#) sequence# from v$archived_log where applied='YES' and dest_id in ( select dest_id from v$archive_dest where destination is not null and upper(VALID_ROLE) in ('ALL_ROLES','STANDBY_ROLE') and dest_id <> 32 ) group by dest_id,thread#) b,
(select thread#,max(sequence#) sequence# from v$archived_log where activation# in (select activation# from v$database) group by thread#) a
where a.thread#=b.thread#
and 'PHYSICAL STANDBY' =(select database_role from v$database);
-- DATABASE INFORMATION
--
select file#,tablespace_name tablespace,name datafile,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time,bytes from v$datafile_header order by file#;
select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header
group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy order by checkpoint_change# desc;
select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;
-- CONTAINER DATABASE INFORMATION
--
show pdbs;
select con_id,name,open_mode,restricted from v$containers;
select 'ROOT',file#,tablespace_name tablespace,name datafile,checkpoint_change#,checkpoint_time,
resetlogs_change#, resetlogs_time,bytes, con_id from v$datafile_header h
where h.con_id=1
union
select p.name, file#,tablespace_name tablespace,h.name datafile,checkpoint_change#,checkpoint_time,
resetlogs_change#, resetlogs_time,bytes,h.con_id
from v$datafile_header h, v$pdbs p
where h.con_id=p.con_id
order by con_id, file#;
select 'ROOT',status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy,h.con_id from v$datafile_header h
where h.con_id=1
group by status,checkpoint_change#,checkpoint_time, resetlogs_change#,
resetlogs_time, fuzzy,h.con_id
UNION
select p.name,status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy,h.con_id from v$datafile_header h, v$pdbs p
where h.con_id=p.con_id
group by p.name, status,checkpoint_change#,checkpoint_time, resetlogs_change#,
resetlogs_time, fuzzy,h.con_id
order by con_id;
-- Get statistics of Redo generation per hour of last 3 days in descending order
select Day||':00-59' "Day" ,round(max(value/1024),2) "Max_Redo_generation_KB/s",round(avg(value/1024),2) "Aveg_redo_in_hour_KB/s" from(
with agg as (
select to_char(BEGIN_TIME,'DD/MON/YYYY HH24') Day,value, dense_rank() over (partition by to_char(BEGIN_TIME,'DD/MON/YYYY HH24') order by value desc) as rank
from dba_hist_sysmetric_history where metric_name = 'Redo Generated Per Sec' and to_char(BEGIN_TIME,'DD/MON/YYYY') > sysdate -3 )
select Day,value from agg)
group by Day order by 1 desc;
-- Flashback Database and Restore Point Details
select scn,database_incarnation#,guarantee_flashback_database,storage_size,
time,restore_point_time,preserved,name from v$restore_point;
select * from v$restore_point;
select min(first_change#),min(first_time) from v$flashback_database_logfile;
select oldest_flashback_scn, oldest_flashback_time, retention_target from v$flashback_database_log;
-- GV$ARCHIVE_DEST full details
select * from gv$archive_dest;
-- Controlfile Details
show parameter control_file_record_keep;
select * from v$controlfile_record_section;
spool off
set markup html off entmap on
set echo on
exit
Run this on Standby Database
REM srdc_DGPhyStby_diag.sql - collect primary database information in dataguard environment
define SRDCNAME='DG_PHYSTBY_DIAG'
set markup html on spool on
set TERMOUT off;
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(value)||'_'||
to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$parameter where lower(name)='instance_name';
REM
spool &&SRDCSPOOLNAME..htm
Set heading off;
select '+----------------------------------------------------+' from dual
union all
select '| Script version: '||'25-Aug-2021' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp: '|| to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/
Set heading on;
set linesize 2000
set pagesize 50000
set numformat 999999999999999
set trim on
set trims on
set markup html on
set markup html entmap off
set feedback on
set echo on
ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
-- EXADATA
select decode(count(cell_path),0,'Non-Exadata','Exadata') "System" from v$cell;
-- MIRA
select decode (count(distinct inst_id),0,'NO_MRP',1,'SIRA','MIRA') "APPLYMODE" from gv$process where upper(pname) like 'PR%';
-- The following select will give us the generic information about how this standby is setup.
-- The DATABASE_ROLE should be STANDBY as that is what this script is intended to be run on.
-- PLATFORM_ID should match the PLATFORM_ID of the primary or conform to the supported options in
-- Note: 413484.1 Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration.
-- FLASHBACK can be YES (recommended) or NO.
-- If PROTECTION_LEVEL is different from PROTECTION_MODE then for some reason the mode listed in PROTECTION_MODE experienced a need to downgrade.
-- Once the error condition has been corrected the PROTECTION_LEVEL should match the PROTECTION_MODE after the next log switch.
SELECT database_role role, name, db_unique_name, checkpoint_change#, current_scn, resetlogs_change#,
platform_id, open_mode, log_mode, flashback_on, protection_mode, protection_level FROM v$database;
Select dbid, name, cdb container from v$database;
-- FORCE_LOGGING is not mandatory but is recommended.
-- REMOTE_ARCHIVE should be ENABLE.
-- SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI must be enabled if this standby is associated with a primary that has a logical standby.
-- During normal operations it is acceptable for SWITCHOVER_STATUS to be NOT ALLOWED.
-- DATAGUARD_BROKER can be ENABLED (recommended) or DISABLED.
column force_logging format a13 tru
column supplemental_log_data_pk format a24 tru
column supplemental_log_data_ui format a24 tru
SELECT force_logging, remote_archive, supplemental_log_data_pk, supplemental_log_data_ui, switchover_status, standby_became_primary_scn, dataguard_broker FROM v$database;
-- Check how many threads are enabled and started for this database. If the number of instances below does not match, then not all instances are up.
-- VERSION should match the version from the primary database.
-- ARCHIVER can be (STOPPED | STARTED | FAILED). FAILED means that the archiver failed to archive a log last time, but will try again within 5 minutes.
-- LOG_SWITCH_WAIT the ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for.
-- Note that if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online redo log, then the value is NULL.
column host_name format a32 wrap
SELECT thread#, instance_name, host_name, version, status, archiver, log_switch_wait, startup_time FROM gv$instance ORDER BY thread#;
-- Password file Users
col USERNAME format a30
SELECT USERNAME,SYSDBA,SYSOPER,SYSASM FROM V$PWFILE_USERS;
select file_name, format, is_asm, con_id from v$passwordfile_info;
-- Check incarnation of the database, the standby and primary must have the same CURRENT incarnation:
select incarnation#, resetlogs_change#, resetlogs_time, prior_resetlogs_change#, prior_resetlogs_time,
status, resetlogs_id, prior_incarnation#, flashback_database_allowed from gv$database_incarnation;
-- Check the number and size of online redo logs on each thread.
SELECT thread#, group#, sequence#, bytes, archived ,l.status, blocksize, member FROM v$log l join v$logfile f using (GROUP#) ORDER BY thread#, group#;
-- The following query is run to see if standby redo logs have been created.
-- The standby redo logs should be the same size as the online redo logs.
-- There should be (( # of online logs per thread + 1) * # of threads) standby redo logs.
-- A value of 0 for the thread# means the log has never been allocated.
select thread#, group#, sequence#, bytes, archived, s.status , blocksize, member FROM v$standby_log s join v$logfile using (GROUP#) order by thread#, group#;
-- This query produces a list of defined archive destinations.
-- It shows if they are enabled, what process is servicing that destination, if the destination is local or remote, and if remote what the current mount ID is.
-- For a physical standby we should have at least one remote destination that points the primary set.
column destination format a35 wrap
column process format a7
column ID format 99
column mid format 99
select thread#,dest_id,dest_name,destination,target,gvi.status instance_status,gvad.status archive_status,process, mountid,valid_now,valid_type,valid_role,db_unique_name,error
from gv$archive_dest gvad join gv$instance gvi using (inst_id) where gvad.status='VALID' and destination is NOT NULL order by thread#, dest_id;
-- If the protection mode of the standby is set to anything higher than max performance then we need to make sure the remote destination that points to the primary is set with the correct options else we will have issues during switchover.
set numwidth 8
column archiver format a8
column ID format 99
column error format a55 wrap
SELECT thread#, dest_id, gvad.archiver, transmit_mode, affirm, async_blocks, net_timeout, delay_mins, reopen_secs reopen, register, binding FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;
-- The query below will determine if any error conditions have been reached by querying the v$dataguard_status view (view only available in 9.2.0 and above).
column message format a80
select inst_id, thread#, facility, severity, dest_id, error_code, timestamp,message
from gv$dataguard_status join gv$instance using (inst_id)
where severity not in ('Informational') and message not like 'No SRLs created%' order by TIMESTAMP desc;
-- Query gv$managed_standby to see the status of processes involved in the shipping redo on this system.
-- Does not include processes needed to apply redo.
SELECT thread#, process, pid, status, client_process, client_pid, sequence#, block#, active_agents, known_agents FROM gv$managed_standby ORDER BY thread#, process;
select inst_id,thread#,sequence#,status from gv$managed_standby where process='MRP0';
SELECT instance, thread#, name, pid, action, client_role, client_pid, sequence#, block#, dest_id
from v$dataguard_process order by thread#, pid;
-- query gv$recovery_progress
select to_char(start_time,'dd-mon-yyyy HH:MI:SS') start_time, type, item, units, sofar, total, to_char(timestamp,'dd-mon-yyyy HH:MI:SS') timestamp from gv$recovery_progress where item like '%Apply Rate';
-- Verify the last sequence# received and the last sequence# applied to standby database.
SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;
-- Check the transport lag and apply lag from the V$DATAGUARD_STATS view. This is only relevant when LGWR log transport and real time apply are in use.
SELECT * FROM v$dataguard_stats WHERE name LIKE '%lag%';
-- Check how often and how far the apply lags.
SELECT name, time, unit, count, TO_DATE(last_time_updated, 'MM/DD/YYYY HH24:MI:SS') FROM v$standby_event_histogram ORDER BY unit DESC, time;
-- The V$ARCHIVE_GAP fixed view on a physical standby database only returns the next gap that is currently blocking redo apply from continuing.
-- After resolving the identified gap and starting redo apply, query the V$ARCHIVE_GAP fixed view again on the physical standby database to determine the next gap sequence, if there is one.
SELECT * FROM v$archive_gap;
-- Check RFS PERF STATE
select event, total_waits, total_timeouts, time_waited, average_wait*10 from v$system_event where event like '%RFS%' order by 5 desc;
-- Check datfile perf state
select event, total_waits, total_timeouts, time_waited, average_wait*10 from v$system_event where event like '%db file%' order by 5 desc;
-- order by time_waited and exclude the idle events
column event format a40 truncate
column name format a35 truncate
column opname format a35 truncate
column value format 99999999999999
select event, total_waits, time_waited, average_wait*10 from v$system_event where time_waited > 100 and event not like 'rdbms ipc %' and event not like '%timer%'and lower(event) not like '%idle%' and lower(event) not like 'sql%net%' and event not like 'ges%' order by time_waited;
-- Non-default init parameters.
-- For a RAC DB Thread# = * means the value is the same for all threads (SID=*)
-- Threads with different values are shown with their individual thread# and values.
column num noprint
SELECT num, '*' "THREAD#", name, value FROM v$PARAMETER WHERE NUM IN (SELECT num FROM v$parameter WHERE (isdefault = 'FALSE' OR ismodified <> 'FALSE') AND name NOT LIKE 'nls%'
MINUS
SELECT num FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvpa.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%')
UNION
SELECT num, TO_CHAR(thread#) "THREAD#", name, value FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num
FROM gv$parameter gvpa, gv$parameter gvpb
WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value
AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE')
AND gvp.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id
AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE')
AND gvp.name NOT LIKE 'nls%' ORDER BY 1, 2;
-- STANDBY LAST RECEIVED and LAST APPLIED
SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE
resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al,
(SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history WHERE
resetlogs_change#=(SELECT resetlogs_change#
FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd
and 'PHYSICAL STANDBY' =(select database_role from v$database);
-- DATABASE INFORMATION
-- Check the state of the datafiles
-- Note: datafiles must be ONLINE
-- Standby datafiles should never be *online* fuzzy.
select file#,tablespace_name tablespace,name datafile,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time,bytes from v$datafile_header order by file#;
select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header
group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy order by checkpoint_change# desc;
select min(fhrba_Seq) "Min Sequence", max(fhrba_Seq) "Max Sequence",min(fhscn) "Min SCN",max(fhscn) "Max SCN" from X$KCVFH;
-- CONTAINER DATABASE INFORMATION
--
show pdbs;
select con_id,name,open_mode,restricted from v$containers;
select 'ROOT',file#,tablespace_name tablespace,name datafile,checkpoint_change#,checkpoint_time,
resetlogs_change#, resetlogs_time,bytes, con_id from v$datafile_header h
where h.con_id=1
union
select p.name, file#,tablespace_name tablespace,h.name datafile,checkpoint_change#,checkpoint_time,
resetlogs_change#, resetlogs_time,bytes,h.con_id
from v$datafile_header h, v$pdbs p
where h.con_id=p.con_id
order by con_id, file#;
select 'ROOT',status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy,h.con_id from v$datafile_header h
where h.con_id=1
group by status,checkpoint_change#,checkpoint_time, resetlogs_change#,
resetlogs_time, fuzzy,h.con_id
UNION
select p.name,status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy,h.con_id from v$datafile_header h, v$pdbs p where h.con_id=p.con_id
group by p.name, status,checkpoint_change#,checkpoint_time, resetlogs_change#,
resetlogs_time, fuzzy,h.con_id
order by con_id;
-- Flashback Database and Restore Point Details
select scn,database_incarnation#,guarantee_flashback_database,storage_size,
time,restore_point_time,preserved,name from v$restore_point;
select * from v$restore_point;
select min(first_change#),min(first_time) from v$flashback_database_logfile;
select oldest_flashback_scn, oldest_flashback_time, retention_target from v$flashback_database_log;
-- GV$ARCHIVE_DEST full details
select * from gv$archive_dest;
-- Controlfile Details
show parameter control_file_record_keep;
select * from v$controlfile_record_section;
spool off
set markup html off entmap on
exit
No comments:
Post a Comment