Oracle Cheat Sheet

Data Guard Cheatsheet

Startup and Open Standby Database

Startup commands

startup nomount

alter database mount standby database;
alter database recover managed standby database disconnect; 
select severity, error_code,message,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') from v$dataguard_status;

Open standby read only

alter database recover managed standby database cancel;

select OPEN_MODE from v$database;

Back to redo apply (it only works when users are disconnect from the database)

alter database recover managed standby database disconnect from session;

Errors when users are connecting:

SQL> alter database recover managed standby database disconnect from session;

Download ms teams for mac. alter database recover managed standby database disconnect from session

*

Standalone updater mac. Macos computer. ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

Check Primary and Standby Status

Check role and status (both primary and standby)

select NAME, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;

select NAME, OPEN_MODE, DATABASE_ROLE from v$database; --9i db

Check protection mode on primary database

select protection_mode, protection_level from v$database;

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

Check processes and statuses

SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;

Log Apply

Start log apply in standby

alter database recover managed standby database disconnect from session;

Remove a delay from a standby

alter database recover managed standby database cancel;

alter database recover managed standby database nodelay disconnect;

Cancel managed recovery/stop log apply

alter database recover managed standby database cancel;

Disable/Enable archive log destinations

alter system set log_archive_dest_state_2 = 'defer';
alter system set log_archive_dest_state_2 = 'enable';

Logical standby apply stop/start

Logs

Check which logs are missing and log apply gap

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select sequence#, archived, applied, first_time, next_time from v$archived_log order by sequence#;

Run this on both primary and standby

select * from v$archive_gap;

, local.sequence# from

, sequence#

where dest_id=1) local

(select sequence#

where dest_id=2 and

/

See how up to date a physical standby is

selectmax(sequence#) current_seq fromv$log;

selectmax(applied_seq#) last_seq fromv$archive_dest_status;

Switch logs

alter system archive log current;

Register a missing log file

alter database register physical logfile '<fullpath/filename>';

If FAL doesn't work and it says the log is already registered

alter database register or replace physical logfile '<fullpath/filename>';

If that doesn't work, try this..

startup nomount

alter database recover automatic standby database;

wait for the recovery to finish - then cancel

startup nomount

alter database recover managed standby database disconnect;

Display info about all log destinations

To be run on the primary

set lines 100
set numwidth 15
column ID format 99
column 'SRLs' format 99
column active format 99
col type format a4
selectds.dest_id id
,ad.status
,ds.database_mode db_mode
,ad.archiver type
,ds.recovery_mode
,ds.protection_mode
,ds.standby_logfile_count 'SRLs'
,ds.standby_logfile_active active
,ds.archived_seq#
fromv$archive_dest_statusds
,v$archive_destad
whereds.dest_id = ad.dest_id
andad.status != 'INACTIVE'
order by
ds.dest_id
/

Display log destinations options

column id format 99

,archiver

,affirm

,net_timeout net_time

,reopen_secs reopen

fromv$archive_dest

dest_id

col member format a70

,st.sequence#

,lf.member

,v$logfilelf

Misc

Turn on fal tracing on the primary db

In this article we will provide Brief definition of Oracle with couple of cheat sheet from web. Brief definition of Oracle: Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is a proprietary multi-model database management system produced and marketed by Oracle Corporation.

Oracle Sql Commands Cheat Sheet

Oracle Apex Interactive Grid Cheat Sheet

Oracle Cheat Sheet.pdf - Free download as PDF File (.pdf), Text File (.txt) or view presentation slides online. Oracle Cheat Sheet.
Oracle PL/SQL Cheat Sheet. PL/SQL ( Procedural Language/Structured Query Language) is Oracle Corporation’s procedural language extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7) and IBM DB2 (since version 9.7). Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.