Posts Tagged ‘RMAN’

TSPITR Issue By Doing Mount Clone Database

November 15th, 2020, posted in Oracle EBS Application, Oracle Queries
Share

alter database mount clone database is a statement that forces all datafiles to be put OFFLINE when mounting the target instance.ORA-19588: archived log RECID 1003 STAMP 2001986 is no longer valid,oracle error,imam dba,dba imam,immam dba,dba immam,rman error
You’ll see it’s being used in the background when you run RMAN commands like:

transport tablespace
recover tablespace .. auxiliary destination ..
“recover table”  (12c feature)

In my case, i was trying to recover an 11.2.0.4 dropped table using “Tablespace Point In Time Recovery” by following the MOS note “How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN (Doc ID 223543.1)“. The basic steps are:

– create pfile
– restore controlfile
– mount with “alter database mount clone database;”
– restore and recover a subset of the database with an RMAN script similar to:

RMAN> connect target /
        run {
        allocate channel c1 device type DISK;
        set until time "to_date( '13-01-2016 13:50', 'DD-MM-YYYY HH24:MI')";
        set newname for datafile 1 to "+RECO";
        set newname for datafile 3 to "+RECO";
        set newname for datafile 4 to "+RECO";
        set newname for datafile 23 to "+RECO";
        restore tablespace SYSTEM, UNDOTBS01, UNDOTBS02, TOOLS;
        switch datafile all;
        sql "alter database datafile 1,3,4,23 online";
        recover database skip forever tablespace TEMP,INDX,USERS,etc.;
        sql "alter database rename file ''+RECO/xxx/onlinelog/group_1.281.739547347'' to ''+RECO''";
        sql "alter database rename file ''+RECO/xxx/onlinelog/group_2.282.739547353'' to ''+RECO''";
        sql "alter database rename file ''+RECO/xxx/onlinelog/group_3.283.739547359'' to ''+RECO''";
        sql "alter database rename file ''+RECO/xxx/onlinelog/group_4.284.739547365'' to ''+RECO''";
release channel c1;
}

After completing the required steps, recovery raised the following error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup

Of course i got the error when tried to open with resetlogs. After struggling with possible causes, i found an internal support note similar with my issue: “ORA-1547 After Recovery When Database Was Mounted As Clone (Doc ID 252181.1)“. The interesting part was this note was published at Oct 23, 2003 for versions 8.1.7.4 to 9.2.0.4 and the fix was:

Use the following workaround until the bug gets resolved :
Do not mount the database as CLONE as in :
SQL> alter database mount clone database;
but mount is as normal database with :
SQL> alter database mount;

But there’s no bug number stated in the note. Even i thought that mysterious bug must have been fixed many years ago, desperation made me try the workaround with mounting the instance with “alter database mount” and manually taking the required datafiles offline. Suprisingly it worked and i was able to open the database with resetlogs.

As a result, it’s fine for me that RMAN uses “mount clone database” statement internally, however when I’ll do TSPITR again you can be sure that I’ll mount the instance with the way I used to !!

Share

RMAN-08137: WARNING: archived log not deleted, needed for standby

November 6th, 2018, posted in Oracle Queries
Share

Recently I met with an Oracle error “RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process” while deleting the archived logs through RMAN prompt.
I simulated the same on my test machine.Note. I had configured standby for my primary database but not the upstream.Followings are the excerpts from the RMAN output.

RMAN> delete noprompt archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/admin/db1/arch/1_146_812203684.dbf thread=1 sequence=146
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/admin/db1/arch/1_147_812203684.dbf thread=1 sequence=147
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/admin/db1/arch/1_148_812203684.dbf thread=1 sequence=148
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/admin/db1/arch/1_149_812203684.dbf thread=1 sequence=149

 

I could delete those archivelogs using “force” option as well.
RMAN > delete noprompt force archivelog all;

 

But I dont want to do the same as those archivelogs have not shipped yet to the standby server.
So I changed the rman configuration to do the same.
RMAN> show all;

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default


RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

new RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

new RMAN configuration parameters are successfully stored

RMAN-08591: WARNING: invalid archived log deletion policy
RMAN> show all;

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

 

Now I was able to delete those archivelogs.
RMAN> delete noprompt archivelog all;




released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=147 device type=DISK

List of Archived Log Copies for database with db_unique_name DB1

=====================================================================




Key     Thrd Seq     S Low Time

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

239     1    146     A 24-MAY-13

        Name: /u01/app/oracle/admin/db1/arch/1_146_812203684.dbf




247     1    147     A 24-MAY-13

        Name: /u01/app/oracle/admin/db1/arch/1_147_812203684.dbf




249     1    148     A 24-MAY-13

        Name: /u01/app/oracle/admin/db1/arch/1_148_812203684.dbf




250     1    149     A 24-MAY-13
        Name: /u01/app/oracle/admin/db1/arch/1_149_812203684.dbf

..........

..........

deleted archived log

archived log file name=/u01/app/oracle/admin/db1/arch/1_146_812203684.dbf RECID=239 STAMP=816273514

deleted archived log

.........

………

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK
specification does not match any archived log in the repository

 

Once I deleted those logs, I changed the rman configuration by the default one.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY CLEAR;

 

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

 

RMAN configuration parameters are successfully reset to default value
Share

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

November 4th, 2018, posted in Oracle Queries
Share

While RMAN full backup, today i have encountered “RMAN-06059: expected archived log not found, loss of archived log compromises recoverability”

RMAN error.
Solution for the mentioned error is to Crosscheck your archive logs from RMAN prompt and delete those expired logs who are not needed anymore.

While crosscheck archivelogs, RMAN will marked archives as Expired in his repository (i.e. controlfile or catalog) those who are not physically present there. So we can easily delete expired archives from controlfile or catalog repository.

 

Error log 1 :

RMAN> RUN
 2> {
 3> ALLOCATE CHANNEL backup1 DEVICE TYPE DISK FORMAT 'Z:\RMAN\%U';
 4> BACKUP DATABASE PLUS ARCHIVELOG;
 5> }
using target database control file instead of recovery catalog
 allocated channel: backup1
 channel backup1: SID=70 device type=DISK
Starting backup at 10-SEP-14
 current log archived
 released channel: backup1
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of backup plus archivelog command at 09/10/2014 18:22:35
 RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
 ORA-19625: error identifying file E:\ORACLE\--ORACLE_SID--\ORAARCH\1_35338_778192583.DBF
 ORA-27041: unable to open file
 OSD-04002: unable to open file
 O/S-Error: (OS 2) The system cannot find the file specified.

Error log 2 :

Starting backup at 26-JUL-12
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/26/2012 22:55:32
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /app/oracle/product/10.2/dbs/arch1_1_781981525.dbf
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


Cause:

This can happen for a variety of reasons; the file has been manually moved or deleted,
the archive log destination has recently been changed,
the file has been compressed, etc

Solution:

Your options are either to restore the missing file(s), or to perform a crosscheck.
To perform a crosscheck, run the following command from within RMAN:

RMAN> CROSSCHECK ARCHIVELOG ALL;

Above command will give information of expired RMAN repository after verification. (i.e. in catalog or controlfile)

Above will marked archives as Expired who are not available physically and who are not required for any kind of recovery.

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

Above command will delete all archive logs who are marked as expired while crosscheck.

 

Enabling And Checking the Status of Flashback On Database,Oracle Database,Oracle DBA,Enabling Flashback On Database,Checking the Status of Flashback On Database, Status of Flashback On Database, Enable Flashback On Database, Enabling Flashback On Database,Enable Flashback On Oracle Database, Enabling Flashback On Oracle Database,ORA-01153: an incompatible media recovery is active,ORA-01153,Oracle Error,Ora Error,Oracle DBA,Oracle Database Error,Oracle Database Solution,an incompatible media recovery is active,Ora : an incompatible media recovery is active,Oracle an incompatible media recovery is active,Oracle Error Solution,Database DBA,oracle data guard,oracle data guard issues,oracle data guard error

Details :

What? When using RMAN to backup archivelog files, an error “RMAN-06059: Errror identifying file ” is returned. Prior to this the backups have been running successfully.

RMAN-03002: failure of backup plus archivelog command
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
RMAN-6059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625 “error identifying file %s”

Why? Oracle is trying to backup an archivelog and is expecting for it to be available as it could be required for recovery.

Cause: The archive log is no longer accessible. One of the common reasons is that the archive log has been removed manually to free up space on the system or the permissions on the file was changed.

Resolution: The most important thing is to determine what caused the file to not be available.

Then to get around this error, to allow RMAN to continue backing up the database first connect to the RMAN repository. The run the “CROSSCHECK ARCHIVELOG ALL”.

RMAN will continue to backup the rest of the archivelogs.

Caution: Ensure that you also take a full backup since one f the archivelogs is not available in the backupsets and will limit your ability to do a complete recovery without it.

Share

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

September 7th, 2018, posted in Oracle Queries
Share

When we are taking archive log backup with delete then got an error.

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=/pac/lci9t1/arch/redo_1_16470_842608348.arc thread=1 sequence=16470

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=/pac/lci9t1/arch/redo_1_16471_842608348.arc thread=1 sequence=16471

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

SOLUTION:

Cause: The archive logs seems that it is not yet applied in the standby database.

                        Select sequence# , applied from v$archived_log;

Action: Check the standby database and apply the logs manually and the start the deletion process.

              I can delete the archive logs by force using the below command.

      RMAN > delete noprompt force archivelog all;

But I don’t want to delete the archives which are not applied in standby  so i’m changing the configuration

RMAN> show all;

Old configuration:

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

Change as Below:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

new RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

new RMAN configuration parameters are successfully stored

RMAN-08591: WARNING: invalid archived log deletion policy


Now check the configuration
RMAN> show all;

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

Now I am able to delete those archivelogs now.

RMAN> delete noprompt archivelog all;

RMAN> crosscheck archivelog all;

Once I deleted those logs, I changed the rman configuration by the default one.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY CLEAR;

RMAN> Show all;
Share