Resolving Archive Gap using incremental SCN RMAN backup

An archive gap is a range of archived redo logs created whenever the standby system is unable to receive the next archived redo log generated by the primary database. For example, an archive gap occurs when the network becomes unavailable and automatic archiving from the primary database to the standby database stops. When the network is available again, automatic transmission of the redo data from the primary database to the failed standby database resumes.
Causes:-
The steps in this section can used to resolve problems if a physical standby database has
1) Loss of Archive log
2) Corrupted Archive Redo Data
3) Unresolvable Archive GAP’s
4) Out of Retention policy

WorkArounds:-
1) If archive exists on primary Troubleshoot the issue, It will automatically shipped if the archive is not corrupted.
2) If archive is corrupted at OS level, if we do have backup, Then Restore archive logs from Backup.
3) Incremental SCN for standby/ Roll Forward.

 Let us assume that the archive log deleted by the user accidentally from primary database, which is not shipped to standby database.
In this case either we need to use workaround 2 or 3.
In this post we will learn about workaround 3 which will take less time to resolve this issue.

Incremental SCN for standby/ Roll Forward :

1. Check if any sequence gap present in standby database
        SQL> Select * from v$archive_gap;

THREAD#  LOW_SEQUENCE#  HIGH_SEQUENCE#

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

                        1          69320                          69331

Note: If there are no gaps then it will show "no rows selected".

     2.       From the above we see the archive log gap is present then our next step is to find the  SCN no. form standby database;

            SQL> select current_scn from v$database;

            CURRENT_SCN
            -----------
            287279617

     3.       On the primary database we need to take the incremental backup from the above SCN no.

RMAN>backup incremental from SCN "287279617" database format 'I: \oradata  

             \Incremental\bkup_%U';

The above RMAN command will backup the data starting from the SCN 287279617which we need to apply on standby database.

4.    Backup current control file for standby.

RMAN>Backup current controlfile for standby format 'I:   

              \oradata\Incremental\control_stndby.ctl';

      5.       Cancel managed recovery at the standby database

SQL>recover managed standby database cancel;

we need to stop the MRP process before we start to apply the incremental backup.

6.   Shutdown the standby database and startup at no mount.

SQL>Shudown immediate;

SQL>Startup nomount;

7.   Restore the standby control file which you have taken backup.

RMAN>restore  standby  controlfile from 'I:   

              \oradata\Incremental\control_stndby.ctl';

8.    Mount the standby database

RMAN>sql ‘alter database mount standby database’;

9.       Copy the backup files to a new folder in standby server so that the folder contains only backup files. Now Catalog the Incremental Backup Files at the Standby Database

RMAN> catalog start with 'I:\oradata\Incremental’;

10.       Apply the Incremental Backup to the Standby Database

 RMAN> recover database noredo;

      11.       Put the standby database back to managed recovery mode.

           SQL> recover managed standby database current log disconnect;

That’s it! You have successfully resolved the archive log gap issue.
Please make sure the remaining archive logs are applied by checking the view v$archive_gap.

SQL> Select * from v$archive_gap;

The out put should be “no rows selected.”

How to check Instance is RAC enabled or not

This post will help full to know Instance is RAC enabled or Not.

SQL> show parameter CLUSTER_DATABASE;

NAME                                 TYPE        VALUE
--------------------------------------------------------------               
cluster_database                     boolean     TRUE
cluster_database_instances      integer       2

SQL> select * from v$active_instances;

INST_NUMBER   INST_NAME
---------------------------------------
  1                          R1
  2                          R2

Or  you can check in parameter file from $ORACLE_HOME/dbs

*.cluster_database=TRUE

If the cluster database set to "TRUE" instance is RAC is enabled.

New features in Oracle Database 12c

Oracle Database 12c, c for cloud, a multi-tenant database management system, with nearly 500 new features.

• Increased size limit for VARCHAR2, NVARCHAR2, and RAW datatypes to 32K.
• Oracle Database 12c has new feature called "Identity Columns" which are auto-incremented at the time of insertion (like in MySQL).
• Temporary undo (for global temporary tables) will not generate undo.
• Oracle Database 12c Data Pump will allow turning off redo for the import operation only.
• Enhanced statistics (Hybrid histograms for more than 254 distinct values, dynamic sampling up to eleven, and stats automatically gathered during load).
• No need to shutdown database to take out of archive log mode.
• Row pattern matching - "MATCH_RECOGNIZATION" (identification of patterns within a table ordered/sorted by the SQL statement).
• Adaptive execution plans (change of the plan at runtime).
• Duplicate Indexes - Create duplicate indexes on the same set of columns. Till Oracle 11.2, if we try to create an index using the same columns, in the same order, as an existing index, we'll get an error. In some cases, we might want two different types of index on the same data (such as in a datawarehouse where we might want a bitmap index on the leading edge of a set of columns that exists in a Btree index).
• Centralised patching.
• We can test patches on database copies, rolling patches out centrally once testing is complete.
• PL/SQL inside SQL: this new feature allows to use DDL inside SQL statements (i.e.: to create a one shot function)
• The object DEFAULT clause has been enhanced. Adding a column to an existing table with a default value (much faster with Oracle 12c and it consumes less space than before, pointer to the Oracle Data Dictionary), applies also to sequences, identity types etc...
• Partitioning enhancements (partition truncate, cascading, global index cleanup, online moving of a partition, ...)
• Interval-Ref Partitions - we can create a ref partition (to relate several tables with the same partitions) as a sub-partition to the interval type.
• Oracle 12c includes database level redaction, allowing granular control of access to sensitive data.
• Pagination query, SQL keywords (LIMIT) to replace ROWNUM records.
• Moving and Renaming datafile is now ONLINE.
• RMAN TABLE Point-In-Time Recovery (combination of data pump and RMAN, auxiliary instance required).
• Oracle Enterprise Manage Express (lightweight EM Cloud Control 12c version) replaces the Oracle Database console and is installed automatically.
• The TRUNCATE command has been enhanced with a CASCADE option which follows child records.
• Oracle introduced the parameter PGA_AGGREGATE_LIMIT which is a real memory limit.

PL/SQL

• PL/SQL Unit Security - A role can now be granted to a code unit. That means you can determine at a very fine grain, who can access a specific unit of code.
• SQL WITH Clause Enhancement - In 12c, we can declare PL/SQL functions in the WITH Clause of a select statement. 
• Implicit Result Sets - create a procedure, open a ref cursor, return the results. No types, not muss, no mess. Streamlined data access (kind of a catch up to other databases).
• MapReduce in the Database - MapReduce can be run from PL/SQL directly in the database.
• We can use Booleans values in dynamic PL/SQL. Still no Booleans as database types.

Compression:
Automated compression with heat map.

Optimisation can be run on live databases with no disruption. Data optimization will monitor the data usage and with policy archive old data and hot data will be compressed for faster access. Inactive data can be more aggressively compressed or archived, greatly reducing storage costs.

Advanced Row compression (for Hot Data).
Columnar Query compression (for Warm Data).
Columnar Archive compression (for Archive Data).

Data Guard:
Oracle Database 12c introduces a new redo transportation method which omits the acknowledgement (to primary) of the transaction on the standby. This feature is called "Fast Sync" redo transport.

Creating a new type of redo destination called "Far Sync Standby". A "Far Sync Standby" is composed only of the standby control files, the standby redo logs and some disk space for archive logs which shall be sent to the Standby database. Failover & Switchover operations are totally transparent as the "Far Sync Standby" cannot be used as the target.

Data Guard Broker commands have been extended. The "validate database" command to checks whether the database is ready for role transition or not.

Global Temporary Tables can now be used on an Active Guard standby database.

Pluggable Databases:
In Oracle 12c, in a pluggable database environment, we can create a single database container, and plug multiple databases into this container. All these databases then share the exact same oracle server/background processes and memory, unlike the previous versions where each database has its own background processes and shared memory. This helps in database consolidation and reduces the overhead of managing multiple desperate databases.

Container Database (CDB): Are the core data dictionary objects that come after an Oracle database installation.
Pluggable Database (PDB): Data dictionary objects and data related to the application. We can have many PDB plugged into a single CDB.

A new admin role "CDB Administrator" has been introduced in Oracle 12.1 release databases.
Multiple databases can then share a master LGWR process, but have their own dedicated LGWR process within the container.

All Oracle database options/features are available on the PDB level.

RMAN backup at CDB level.

We can unplug a PDB from a CDB to another CDB.
PDB's can be cloned inside the CDB.
Management of PDB (clone/creation/plug/unplug/drop) are implemented as SQLs.
Extremely fast PDB-provisioning (clone inside the CDB), because each CDB comes with a “PDB Seed”.
Database patch/upgrade management very quick as CDB is a single point of installation.
Each PDB has its own data dictionary.
Data Guard configuration on CDB as whole.
RMAN point-in-time recovery at PDB level (while other PDB's remains open).
Resource Manager is extended for creating, unplugging, plugging in, and cloning, dropping or even setting up for the open mode of the PDB.Flashback of a PDB should be available for Oracle 12c Release 2.

Entire containers can be backed up in single run, regardless of how many databases they contain.
Upgrade one container database and all pluggable databases are upgraded.

New Commands
create pluggable database ...
alter pluggable database ...
drop pluggable database ...

New Views/Packages in Oracle 12c Release1
dba_pdbs
v$pdbs
cdb_data_files

dbms_pdb

ORA-19804: cannot reclaim string bytes disk space from string limit

Hi


Have you ever encounter the error ORA-19804? if yes then the below solution will help you.

Reason: Oracle cannot reclaim disk space of specified bytes from the DB_RECOVERY_FILE_DEST_SIZE limit.

Action: There are five possible solutions:

1) Take frequent backup of recovery area using RMAN.
2) Consider changing RMAN retention policy.
3) Consider changing RMAN archivelog deletion policy.
4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
5) Delete files from recovery area using RMAN.

I found this error ORA-198 when i am taking backup.
when i check the DB_RECOVERY_FILE_DEST_SIZE  using "show parameter DB_RECOVERY_FILE_DEST_SIZE" if found it is 10GB. but i have 500GB free space.

So i have increased the space using the below command.
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=100G;
system altered.

After changing parameter my backup completed successfully.

 

RMAN-08512: waiting for snapshot controlfile enqueue

                 

RMAN-08512 error genrally will occur when snapshot control file header is locked by one process and the other processes is requesting the enqueue.

For example if you have multiple RMAN Backup Jobs and you trying to run the jobs parrallely on same database with two different RMAN session then this kind of error may occur.

To investigate if multiple RMAN clients are running or not Please use the below query.

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

SELECT s.sid, username AS "User", program, module, action, logon_time "Logon", l.*
FROM v$session s, v$enqueue_lock l
WHERE l.sid=s.sid and l.type='CF' AND l.id1=0 and l.id2=2;

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

Have a good day :)

RMAN Backup Progress

             Hi All, the belwo SQL query will help to identifiy the RMAN backup progress.

         SQL>SELECT SID, SERIAL#, CONTEXT,opname, SOFAR, TOTALWORK,
                  ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE"
                  FROM V$SESSION_LONGOPS
                 WHERE OPNAME LIKE 'RMAN%'
                 AND OPNAME NOT LIKE '%aggregate%'
                 AND TOTALWORK !=0
                 AND SOFAR <> TOTALWORK
                 /

You need to run the above query at target database.

hi

sample post

testing blog