Oracle database archive space utilization

 Query to check Archive space Utilization

set pagesize 0 feedback off verify off heading off echo off

SELECT decode( nvl( space_used, 0),0, 0, ceil ( ( space_used / space_limit) * 100) ) pct_used FROM v$recovery_file_dest;

Unix-Linux Shell Source of environment variables

Shell - Source of env variables

In many times, we need to know what are the script is being called while setting up the environment variables, this is very handy when we are getting errors like some env variables are not setup.

If zsh is the login shell:

zsh -xl

Execute the below command to get from which file the environment is setup:

PS4='+$BASH_SOURCE> ' BASH_XTRACEFD=7 bash -xl 7>&2

Best Wishes!!

Pause RMAN backup

How to Pause and resume RMAN backup

There are certain times when backup is running for long and critical applications are hanging for limited resources. During that time, rather than killing the whole backup session, it is a handy option to pause the backup from server end and then resume it after the application transactions are completed. 

This is performed in a Development/testing environment.

Check the details of the Currently running backup:

col START_TIME for a15

col END_TIME for a15

col TIME_TAKEN_DISPLAY for a10

col INPUT_BYTES_DISPLAY for a10

col OUTPUT_BYTES_DISPLAY for a10

col OUTPUT_BYTES_PER_SEC_DISPLAY for a10

col output_device_type for a10

SELECT to_char (start_time,'DD-MON-YY HH24:MI') START_TIME,to_char(end_time,'DD-MON-YY HH24:MI') END_TIME, time_taken_display, status,input_type, output_device_type,input_bytes_display, output_bytes_display,output_bytes_per_sec_display,COMPRESSION_RATIO COMPRESS_RATIO FROM v$rman_backup_job_details WHERE status like 'RUNNING%';

Dataguard - Archive Gaps

Dataguard essential queries

Query to check the log gap on primary/standby database:

 

set pagesize 300

set linesize 200

set wrap off

select thread#, sequence#, applied,to_char(first_time,'mm/dd/yy hh24:mi:ss') first,to_char(next_time, 'mm/dd/yy hh24:mi:ss') next,to_char(completion_time, 'mm/dd/yy hh24:mi:ss') completion from v$archived_log where thread# = 1 order by first_time;

 

Enable Oracle Diagnostics and Tuning pack

How to enable Diagnostics pack in Oracle Database

This is a licensed feature for Oracle Database. Without the proper licensing, this can not be done.

Parameter Name:  

control_management_pack_access

Steps to be followed:

SQL> show parameter control_management_pack_access

 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      NONE

SQL> ALTER SYSTEM SET control_management_pack_access="DIAGNOSTIC+TUNING";

 

System altered.

 

SQL> show parameter control_management_pack_access

 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING

Note: Before doing that, please make sure you have the corresponding licenses from Oracle.

Best Wishes!!!

Restart redo LOG shipping - Oracle Dataguard

How to restart the Redo log shipping to standby Database

To stop log apply on standby database:

SQL>  alter database recover managed standby database cancel;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY

To resume Log apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Alternate Method:

Cancel MRP:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Run the below in Dataguard instance:

DGMGRL> edit database <STBY_DB> set state='APPLY-OFF';

Succeeded.

Run the Below for primary database

DGMGRL> edit database  <PRIM_DB> set state='LOG-TRANSPORT-OFF';

Succeeded.

Now to initiate the process:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; -- Start MRP

Run the below in Dataguard instance:

DGMGRL> edit database <STBY_DB> set state='APPLY-ON';

Succeeded.

Run the Below for primary database:

DGMGRL> edit database  <PRIM_DB> set state='LOG-TRANSPORT-ON';

Succeeded.

 

Ref:  Steps to restart log transport process in data guard (Doc ID 2819878.1)