RMAN Recovery Catalog

About Recovery Catalog

RMAN recovery catalog Is another database which is out of your normal databases or which is on a separate server and which will save only the details of your backups in terms of metadata of the backup.

Whenever you trigger the backup, RMAN will Update the information in the control file. Your control files can store the backup details for the last 30 days only which is the default 30 days retention period.

Suppose you want to have the details of the backups which are taken before 30 days. The recovery catalog will help you in this case to store the history backup details.

Importance of Recovery Catalog

• RMAN stores the backup information into the control file
• If we lose control file, we lose all the backup information
• Even though the backups are available, they are not usable. If you lose the control file, the backups are of no use as Oracle or RMAN doesn’t know which backup belongs to which database. The reason behind this is the information is stored in the control file which you lost
• To resolve all the above three issues, we use recovery catalog.
• Recovery catalog is a separate database that stores backup information
• One recovery catalog can store multiple target database backup information
• You don’t have to create multiple recovery catalog for multiple target databases. You can create one recovery catalog database for all the databases or servers

 

Benefits of Recovery catalog

• It helps in database recovery when you lose the control file
• You can use recovery catalog for backups reporting also
• You can write RMAN scripts in recovery catalog and save them
• You can store RMAN scripts in recovery catalog
• Store long term backup history
• Cloning with PITR (Point in time recovery) becomes easier as the recovery catalog knows which Control file is required

Note: These RMAN scripts are your backup scripts or your recovery scripts. You don’t have to use OS level scripts to take the database backup. The primary requirement to have the RMAN scripts is to have the recovery catalog

 

Recovery catalog configuration

Below are the steps to configure RMAN recovery catalog on a separate machine, install Oracle database. Then Create a small database with SID as rcat. 

 

Remember recovery catalog resides on a separate server and that is why you need to configure a separate machine

Create a default tablespace under rcat database which will be used for backup information storage

SQL> create tablespace rmantbs datafile '/u01/app/oracle/oradata/rcat/rmantbs01.dbf' size 50mb;

You can create a small tablespace and keep on adding the space when You get some alerts or when the database throws some errors. We are going with the very small size of 50 MB.

Next, Create a user which will be used to connect recovery catalog and store backup information. We will create the user rman_rc to connect to the recovery catalog. This user rman_rc will store the information into rmantbs tablespace

SQL> create user rman_rc identified by rman_rc default tablespace rmantbs temporary tablespace temp;

Grant permissions to rman_rc user

SQL> grant connect,resource,recovery_catalog_owner to rman_rc;

 

In this command, recovery_catalog_owner should be granted a permission to the user rman_rc otherwise we cannot use the user for the recovery catalog.

Next, Configure networking between target server and catalog server. Make sure to add tns entry details of catalog server on target server. In my case, below is the tns entry of catalog server

rcat = 
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521)
      )
    (CONNECT_DATA =
      (SERVICE_NAME = rcat)
    )
  )    

On target server, start RMAN utility and connect only the catalog database. We will not create catalog on the catalog database

rman catalog rman_rc/rman_rc@rcat

RMAN> create catalog;

This will create all the necessary tables and views which are required to store the backup information in the user rman_rc and into the database rman_rc@rcat

Exit RMAN and now connect both target and catalog database

rman target / catalog rman_rc/rman_rc@rcat

We need to register our target database to the recovery catalog database. Use below command to register target database into catalog database:

RMAN> register database;

You can see the statement in the above screenshot: starting full resync of recovery catalog. It means that whatever backup information available in the proddb file is now updated also in the recovery catalog.

Full resync complete means that your control files backup information and the recovery file backup information are updated.

 

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

https://shivanandarao-oracle.com/2012/12/05/rman-20207-until-time-or-recovery-window-is-before-resetlogs-time/

KB0013276

When you are performing a PITR (Point In Time Recovery), you might have undergone the error “RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time”. The reason that RMAN throws this error is when you are trying to perform a PITR of the database to a time before the database was last opened with RESETLOGS. In other words, the time you specify in the “until time” clause of RMAN is the time that the database was in its previous incarnation and not the CURRENT incarnation.

RMAN threw an error saying “RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time”.

executing command: SET until clause
Starting restore at 01-DEC-12
Starting implicit crosscheck backup at 01-DEC-12
RMAN-00571: ===================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =======
RMAN-00571: ===================================================
RMAN-03002: failure of restore command at 12/01/2012 20:26:41

RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

RMAN is trowing this error because the incarnations of the database not matching with PITR. 

Run the “list incarnation” command at RMAN prompt.

 

rman target / catalog /@<rman catalog>

RMAN>spool log to <caseid>_restore.log

RMAN>set echo on

RMAN>ALTER SESSION SET NLS_DATE_FORMAT = 'dd/mm/yyyy hh24:mi:ss';

RMAN>list incarnation of database;

 

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

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

294105469 294105470 AMALPRD1 1914393966       PARENT  1          21-AUG-2023 01:21:50

294105469 625758845 AMALPRD1 1914393966       CURRENT 9029509056 26-NOV-2024 15:39:15

** reset database to incarnation <n>;  <-- only if required; n = the appropriate INC Key that your restore until_time falls into, i.e. before resetlogs time)

RMAN> reset database to incarnation 1;

run {

  allocate channel ch00 type disk;

  set until time= "to_date('14/06/2022 14:30:00','dd/mm/yyyy hh24:mi:ss')";

  restore database preview summary;

  release channel ch00;

}

shutdown immediate;

** set dbid=<dbid>;  <-- only required if the DBID is different, otherwise no need to set it.

run {

startup nomount;

set until time= "to_date('14/06/2022 14:30:00','dd/mm/yyyy hh24:mi:ss')";

restore controlfile;

alter database mount;

restore database;

recover database;

}

alter database open resetlogs;

select * from v$instance;

select con_id, dbid, name, open_mode, restricted, open_time from v$containers;

spool log off;

exit;

Query to Check available Space of an Oracle Database

Login to the database as SYS user

sqlplus as / sysdba

Then copy and paste below sql query in the command line or PL/SQL editor

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size",

round(sum(used.bytes) / 1024 / 1024 / 1024 ) -

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space",

round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

from (select    bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used,

(select sum(bytes) as p from dba_free_space) free

group by free.p;

 

Windows: How To Delete and Recreate the TNS Listener Service When the Service Fails To Start Consistently At Machine Reboot (Doc ID 313250.1)

APPLIES TO:

Oracle Net Services - Version 8.1.7.4.0 and later
Microsoft Windows x64 (64-bit) - Version: 2008 R2
Microsoft Windows (32-bit)
Microsoft Windows (32-bit)Microsoft Windows

GOAL

Oracle TNS Listener service fails to start automatically at boot time (e.g. after moving Windows Server to new location) or fails to start manually.

SOLUTION

You need to re-create the Windows service associated to the Oracle listener by deleting the Windows service and creating a new service for the Oracle TNS Listener.

1. Deleting the service

At your earliest convenience, or next scheduled maintenance window, delete and recreate the Windows service associated to the Oracle TNS Listener. You can do this either with the help of a tool (recommended) or by hand-editing the registry. In both cases you need to stop the listener service before making any changes.

Using a tool
There are quite a few tools to manipulate Windows services, in the Resource Kit or from third parties. Also there is a tool called SC and which is available in the base Windows distribution (at least for Windows XP and Windows 2003).

1. Lookup the windows service name that you want to remove — use Properties in the Windows Services panel to copy the full name
2. Open a Command Prompt window and run the following:

   sc delete <your_windows_Service_name>

3. Check that the Windows service has been removed — use F5/Refresh in the Windows Services panel

In case you do not have the SC tool available then you need to download the Windows Resource Kit — there you will find the instsrv.exe tool.

1. Lookup the windows service name that you want to remove — use Properties in the Windows Services panel to copy the full name
2. Open a Command Prompt Window and run the following:

   instsrv /deleteService <your_windows_Service_name>

3. Check that the Windows service has been removed — use F5/Refresh in the Windows Services panel

Manually editing the Windows Registry

Warning: editing the Windows Registry by hand is dangerous and may render your system unusable. Proceed at your own risk !

You need to follow these steps:

• Launch the Registry Editor (e.g. Start / Run / regedit )
• Drill down to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
• Locate and delete the the key which contains your Oracle Home name and your listener name (e.g. OracleOraHomeTNSListenerName)
• Reboot the system

 

2. Creating the new service

When started and logged on as the Oracle user, go to a Command Prompt and start the listener:

lsnrctl start <listener_name>

Replace <listener_name> with the name of your listener; if you are operating on the default listener then you may use the value LISTENER or leave it empty.

An OS error 1060 will be seen (which is normal as the Windows service is missing and is being created); the listener should start correctly.

3. Test the listener

Once started the listener is started, check that a Windows service for the TNS Listener was created in the Window Services panel and set to startup automatically (change if necessary). Then test the automatic listener startup by performing another reboot.

 

How to recover missing datafiles?

ORA-01110: ORA-01565:  ORA-27041

Recovery of SID went wrong though the DB opened successfully because unfortunately we used old control file and missed almost 11 data files in controlfile.sql file and which created soft link in dbs path.

In order to overcome the situation below steps performed and database become consistent. If in case in the future if we face same issue, we can use below steps confidently.

1) Find the missing datafiles:

SQL> set line 250

SQL> col FILE_NAME for a45

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;

/oracle/SID/121/dbs/MISSING00268                     268 PSAPSID  ONLINE

/oracle/SID/121/dbs/MISSING00269                     269 PSAPSID ONLINE

 

2) Take tablespace or datafiles offline:

Come with below error because data file already in offline

SQL> alter database datafile ‘/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242’ offline;

Database altered.

SQL> alter database datafile ‘/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243’ offline;

Database altered.

 

3) Rename missing datafiles with original files: 

SQL> alter database rename file ‘/oracle/SID/121/dbs/MISSING00268’ to ‘/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242’;

SQL> alter database rename file ‘/oracle/SID121/dbs/MISSING00269’ to ‘/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243’;

4) Shutdown and mount the database:

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6.4425E+10 bytes

Fixed Size                  3729840 bytes

Variable Size            7381976656 bytes

Database Buffers         5.6908E+10 bytes

Redo Buffers              130486272 bytes

Database mounted.

5) Perform media recovery:

SQL> recover datafile 268;

Media recovery complete.

SQL> recover datafile 279;

Media recovery complete.

6) Open the database:

SQL> alter database open;

Database altered.

7) check the status of datafiles:

SQL> set line 250

SQL> col FILE_NAME for a45

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;  – After recovery data files online_status went recover to offline.

8) Bring the datafiles online:

SQL> alter database datafile ‘/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242’ online;

Database altered.

SQL> alter database datafile ‘/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243’ online;

Database altered.

9) check again the status of datafiles:

SQL> set line 250

SQL> col FILE_NAME for a45

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;

 

ORA-12557: TNS:protocol adapter not loadable

Question:  What causes the ORA-12577 error below?  I am running on Windows.

ORA-12557: TNS:protocol adapter not loadable

The ORA-12577 error is related to Windows Environment or Oracle Home PATH because sqlplus command works smoothly when I execute it inside ORACLE_HOME\bin.

Answer:  There are two solutions to this issue:

1 - put the Oracle DB Home in front of the other paths in the PATH environment variable.

2 - Remove ORACLE_HOME From environment Variable and re-boot PC

Oracle author Osama Mustafa notes a solution to the ORA-12577 error.

RUN: SYSDM.CPL to open Windows System Properties

Click on Advanced Tab > Environment Variables…

Click the Path variable under System Variable, then click Edit…

Change the order between Oracle Client Home and Oracle DB Home:

From: D:\oracle\product\10.2.0\client_1\bin;D:\oracle\product\10.2.0\db_1\bin;

To: D:\oracle\product\10.2.0\db_1\bin;D:\oracle\product\10.2.0\client_1\bin;

In other words, put the Oracle DB Home in front of the other path.

 How to set FILESYSTEMIO_OPTIONS for use with RSA Identity Governance and Lifecycle on a remote Oracle database where Automatic Storage Management (ASM) is not being used for better storage performance 

Article Number

000035132

Applies To

RSA Product Set: RSA Identity Governance and Lifecycle
RSA Version/Condition: All
Platform (Other): Non-ASM Oracle database instances (soft-appliance and/or remote databases)
 

Issue

For non-ASM (Oracle Automatic Storage Management) database implementations, RSA recommends the explicit setting of the FILESYSTEMIO_OPTIONS initialization parameter to SETALL in order to enable direct and asynchronous disk I/O. It can make a huge difference in storage performance. This is for non-ASM Oracle database implementations only.

To determine if your Oracle database is an ASM implementation:

Login as SYSDBA and run the following SQL command:

$ sqlplus / as SYSDBA
SQL> SELECT * FROM v$asm_client:

If this view returns no rows, then your system is a non-ASM Oracle implementation.


For more information on the FILESYSTEMIO_OPTIONS initialization parameter, visit the Oracle web site.  

• Click for information on Oracle 11g
• Click for more information on Oracle 12c.

Task

Check the current value of this parameter to see if it needs to be set. The default value is typically none.

Login as sysdba, then run the following:

SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS;

If the value is something other than SETALL, it needs to be modified.  For example,

SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none

Resolution

Modify the FILESYSTEMIO_OPTIONS setting to SETALL, restart Oracle, and check that the setting has been modified:

1. Modify the setting:

SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;

2. Restart Oracle

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

3. Check that the setting has been updated:

SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS

For example,

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 4 09:53:56 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SET LINE 300
SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
filesystemio_options                 string                           none
SQL>
SQL>
SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;

System altered.

SQL>
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> STARTUP
ORACLE instance started.

Total System Global Area 7063207936 bytes
Fixed Size                  2940568 bytes
Variable Size            1308623208 bytes
Database Buffers         5737807872 bytes
Redo Buffers               13836288 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
filesystemio_options                 string                           SETALL
SQL>