Physical Standby Swithover_status as UNRESOLVABLE GAP

On Data Guard site, I can see that Archive logs are copying but not applying to Physical Standby Database. When I query I see that SWITCHOVER_STATUS showing as “UNRESOLVABLE GAP”

SQL> select OPEN_MODE,LOG_MODE,DATABASE_ROLE, switchover_status from v$database;

OPEN_MODE LOG_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ------------------------------ ---------------------------------------------
MOUNTED ARCHIVELOG PHYSICAL STANDBY UNRESOLVABLE GAP

I didn’t see any errors when we query v$archive_Dest_status;
SQL> select DEST_NAME, ERROR from v$archive_Dest_status;

But when we query V$ARCHIVE_GAP we can see archive log Gap

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 47402 47409

I don’t see missing archive logs in the archive log destination, but when I query v$managed_standby I can see that MRP0 processes waiting for archive log sequence 47402

SQL> select PROCESS,STATUS,THREAD#,SEQUENCE# from v$managed_standby where PROCESS='MRP0';
PROCESS STATUS THREAD# SEQUENCE#
------------------------------------ ------------------------------------------------ ---------- ----------
MRP0 WAIT_FOR_GAP 1 47402

If the archive logs on the Physical Standby site removed by mistake then you can restore using RMAN
RMAN> restore archivelog from logseq 47402 until logseq 47409;

If you want to restore archive logs into different destination other than default use below command.
RMAN> set archivelog destination to '/tmp/archive_restore';

In case, if you need to restore specific archive log then use below command
RMAN> restore archivelog from logseq=47402;

Once the archive logs are restored, it will apply to standby site. In case if you have restored to non-default destination then you need to copy the archive logs into default destination.

If the archive logs are not applying on Physical Standby site then shut down and open the Physical Standby in recovery mode again. In case if the archive logs are missing and cannot able to restore from backup then you might get below error message. 

SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 2.0737E+10 bytes
Fixed Size 9923356 bytes
Variable Size 8680473632 bytes
Database Buffers 2040487392 bytes
Redo Buffers 6859032 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

Note that with missing archive logs you cannot able to recover the database.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Webinar: Oracle Data Guard New Features in Oracle 18c & 19c

This Webinar helps you to understand the benefits of Oracle Data Guard, Standby Database types, protection modes and new features in Oracle 18c and 19c.

Date and time: May 5th 2022 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• Introduction to Oracle Data Guard
• Oracle Data Guard Benefits
• Standby Database types
• Data Guard Protection Modes
• What's new in Oracle 18c
• What's new in Oracle 19c
• References
• Contact Info
• Q&A

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For presentation link Click here

Thanks & Regards,
http://www.oracleracexpert.com

How to roll forward image copies using RMAN

Recovery Manager (RMAN) is a utility that can be used to backup and recovery your database and it simplifies backing up, restoring, and recovering database or database files

Oracle RMAN can be used to roll forward the copy to the point in time of the most recent level 1 incremental backup by applying level 1 incremental backups to an older image copy. This will help reducing recovery time. To roll forward processes all changes between SCN of original image and incremental backup are applied to the image copy.

When using Roll forward image no need to restore the database instead just switch to updated image copy this will reduce restore time and recovery will be minimal.

run {
allocate channel c1 device type disk;
recover copy of database with tag 'rman_incr_backup';
backup incremental level 1 for recover of copy with tag ‘rman_incr_backup' database;
}

The TAG is used to identify which incremental backups are applying to which image copies.

Note that when the backup runs first time there is no level 0 exists to apply incremental backups. Therefore, it will create level 0 image copy of the database. Going forward it will create level 1 incremental backup and will update previous image copy.

You can also use compression during roll forward image copies using below example

run {
allocate channel c1 device type disk;
recover copy of database with tag ‘rman_incr_backup';
backup as compressed backupset incremental level 1 for recover of copy with tag ‘rman_incr_backup' database;
}

In case if you need to switch the database to image copy you then “shutdown database” and “startup mount”

SQL> shutdown immediate;
SQL> startup nomount;

By using below RMAN command, you can switch the database to image copy

run {
allocate channel c1 device type disk;
switch database to copy;
recover database;
alter database open;
}

When using FRA (Fast Recovery Area) make sure you have has enough space to keep take full image copy database backup.

The Block change tracking will help to improve performance of the incremental backups and you can check the status and enable using below command. 

SQL> select status from v$block_change_tracking;
SQL> alter database enable block change tracking using file '/oracle/oradata/TESTDB/block_change_tracking_file.dbf' reuse;

You can use below command to disable Block change tracking

SQL> alter database disable block change tracking;

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Automatic SQL Tuning Set in Oracle 21c

The SQL tuning sets introduced in Oracle 10g and DBMS_SQLTUNE package used to manage SQL Tuning. You can use SQL tuning sets to group statements into a single object and use as input to tuning tools.

The below command can be used to create a SQL Tuning set

EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'SQL_Tuning_Set1');

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name => 'SQL_Tuning_Set1',
description => 'SQL Tuning Set 1’);
END;

You can use UPDATE_SQLSET procedure to update the attributes of the SQL statements in the SQL tuning Sets

You can use below query to find the SQL Tuning sets owned by the user

SQL> SELECT NAME, STATEMENT_COUNT, DESCRIPTION FROM USER_SQLSET;

You can use DELETE_SQLSET procedure to deletes all the statements in SQL Tuning set

BEGIN
DBMS_SQLTUNE.DELETE_SQLSET ( sqlset_name => 'SQL_Tuning_Set1');
END;
/

You can use DROP_SQLSET procedure command the SQL Tuning set using below command

BEGIN
DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'SQL_Tuning_Set1');
END;
/

You can also transfer the SQL tuning sets following steps create, pack, transfer, and unpack by using use below procedures. You can use datapump or export/import to export/import from source to destination database.

DBMS_SQLTUNE.create_stgtab_sqlset – create a stage
DBMS_SQLTUNE.pack_stgtab_sqlset – To pack SQL tuning sets
DBMS_SQLTUNE.unpack_stgtab_sqlset – To unpack SQL Tuning Sets

In Oracle 11g, further enhancements added to use SQL tuning sets with SQL Performance Analyzer. The DBMS_SQLPA package helps to build and compare two different version of the workload performance.

You can use CREATE_ANALYSIS_TASK to create an analysis task for SQL tuning set or for a single statement or single statement from the workload repository with range of snapshots

You can use below examples…

variable v_task VARCHAR2(64);
variable v_tset_task VARCHAR2(64);

-- SQL Text

EXEC :v_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(

sql_text => select dname, count(*) from dept, emp where dept.deptno = emp.deptno);

-- SQL ID

EXEC :v_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( SQL_ID => 'cv1d34ds5kdd4');

--Workload repository

exec :v_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( BEGIN_SNAP => 1, END_SNAP => 2,

SQL_ID => 'cv1d34ds5kdd4');

-- SQL Tuning Set

EXEC :v_tset_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( SQLSET_NAME => 'SQL_Tuning_Set1', order_by => 'BUFFER_GETS' );

You can use CANCEL_ANALYSIS_TASK procedure to cancel the task.

EXEC DBMS_SQLPA.CANCEL_ANALYSIS_TASK(:v_task);

In Oracle 21c, Automatic SQL tuning automates the entire SQL tuning processes. The automated SQL Tuning sets (ASTS) is a system generated execution plan and performance metrics, it is useful for repairing SQL performance regression when using SQL Plan management.

This feature is introduced in 19c RU 19.7 onwards and available with AWR . You can use ASTS with SQL plan management to implement entire workflow without manual intervention.

You can run below query dba_sqlset_Statements to view statements in ASTS

SQL> SELECT SQL_TEXT FROM DBA_SQLSET_STATEMENTS WHERE SQLSET_NAME = 'SYS_AUTO_STS';

You can use below commands to enable/Disable ASTS

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE(

CLIENT_NAME => 'ASTS CAPTURE TASK',

OPERATION => NULL,

WINDOW_NAME => NULL);

END;

/

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

CLIENT_NAME => 'ASTS CAPTURE TASK',

OPERATION => NULL,

WINDOW_NAME => NULL);

END;

/

Thanks & Regards

http://oracleracexpert.com

PGA Memory are not eligible to receive ORA-4036 interrupts

Users may receive ORA-04036 errors when PGA_AGGREGATE_LIMIT has been exceeded but some processes using the most PGA and you will see the errors written in the trace files as well.

ARC1 (PID:42467): Archived Log entry 10265 added for T-1.S-10440 ID 0xb264618a LAD:1
2022-05-15T02:07:49.670543-07:00
PGA_AGGREGATE_LIMIT has been exceeded but some processes using the most PGA
memory are not eligible to receive ORA-4036 interrupts. Further occurrences
of this condition will be written to the trace file of the DBRM process.

When you encounter this issue, the sessions consuming the PGA will be terminated until the bottleneck is cleared . Note that Oracle can exceed the amount of RAM without PGA_AGGREGATE_LIMIT which may lead to RAM buffer paging and RAC node eviction errors

The V$PGA_TARGET_ADVICE will help to predict how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view

SQL> select pga_target_for_estimate, pga_target_factor, estd_time  from v$pga_target_advice;

The below query can help to get PGA Target advice by querying v$pga_target_advice_histogram

SQL> SELECT LOW_OPTIMAL_SIZE/1024 "LOW VALUE IN KB", (HIGH_OPTIMAL_SIZE+1)/1024 "HIGH VALUE IN KB", ESTD_OPTIMAL_EXECUTIONS "OPTIMAL VALUE IN KB ", ESTD_ONEPASS_EXECUTIONS "ONE PASS EXECUTION", ESTD_MULTIPASSES_EXECUTIONS "MULTI-PASS EXECUTION "

FROM  V$PGA_TARGET_ADVICE_HISTOGRAM

WHERE PGA_TARGET_FACTOR = 2 AND ESTD_TOTAL_EXECUTIONS != 0

ORDER BY 1;

By default PGA_AGGREGATE_LIMIT is set to 2GB, when you receive the errors, I would suggest to double the value or set the appropriate value required for your environment.

You can run below command to get PGA_AGGREGATE_LIMIT and increase the value

SQL> show parameter PGA_AGGREGATE_LIMIT
SQL> alter system set PGA_AGGREGATE_LIMIT=<xGB> scope=spfile;

Sometimes users may receive below errors when PGA_AGGREGATE_LIMIT set to zero. Make sure you set the non-zero and appropriate value.

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
REP-0069: Internal error
REP-57054: In-process job terminated:Terminated with error:
REP-300: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Users may experience "Database Crash" in Oracle 19c versions when USING DBMS_STATS.GATHER_TABLE_STATS . This is due to a Oracle product defect Bug:30846782 which is fixed in 21.1.

As a workaround you may try to reduce the memory usage, set hidden parameter "_fix_control"='20424684:OFF'.

At session level:
alter session set "_fix_control"='20424684:OFF';
At Instance level:
alter system set "_fix_control"='20424684:OFF';

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE.