Tuesday, January 21, 2014

Fix RMAN-06214

Today I found in my backup log file strange error.

RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy   /u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f


RMAN> Report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 3 days
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     1      13-JAN-14          /u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f

--try to delete obsolete backup

RMAN> Delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 3 days
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     1      13-JAN-14          /u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f

Do you really want to delete the above objects (enter YES or NO)? YES
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_1 channel at 01/21/2014 08:56:58
ORA-19606: Cannot copy or restore to snapshot control file

RMAN> exit

--I tried to delete it with noprompt too but result was same

delete noprompt obsolete;


RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

RMAN configuration parameters for database with db_unique_name FCDBPROD are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f'; # default

--provide new location

RMAN> configure snapshot controlfile name to '/home/oracle/scripts/snapcf_FCDBPROD.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/scripts/snapcf_FCDBPROD.f';
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name FCDBPROD are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/scripts/snapcf_FCDBPROD.f';

--now crosscheck and delete

RMAN> crosscheck controlfilecopy '/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f';

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
validation failed for control file copy
control file copy file name=/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f RECID=1 STAMP=836734569
Crosschecked 1 objects


RMAN> delete expired controlfilecopy '/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f';

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
List of Control File Copies
===========================

Key     S Completion Time Ckp SCN    Ckp Time    
------- - --------------- ---------- ---------------
1       X 13-JAN-14       10156665859609 13-JAN-14    
        Name: /u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f
        Tag: TAG20140113T101609


Do you really want to delete the above objects (enter YES or NO)? yes
deleted control file copy
control file copy file name=/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f RECID=1 STAMP=836734569
Deleted 1 EXPIRED objects


--now set old location, default

RMAN> configure snapshot controlfile name to '/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f';

old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/scripts/snapcf_FCDBPROD.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f';
new RMAN configuration parameters are successfully stored

--check

RMAN> show SNAPSHOT CONTROLFILE NAME;

RMAN configuration parameters for database with db_unique_name FCDBPROD are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_FCDBPROD.f';


Check location if file not created yet, take current control file backup for creating snapshot control file

RMAN> backup current controlfile;

That`s all.
at No comments:

Wednesday, January 8, 2014

ORA-38754: FLASHBACK DATABASE not started & ORA-38761

Yesterday I suddenly faced ORA-38761 and ORA-38754 errors.
I checked all logs and backups which were available, but why I could not flashback db.

Let`s simulate.

-- First I will create new user and will create table under new user. This is for checking purpose after flashback.
SQL> create user ulfet identified by ulfet;

User created.

SQL> grant dba to ulfet;

Grant succeeded.

SQL> conn ulfet/ulfet
Connected.

-- Note date. If you desire you may note SCN
SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-JAN-14 09.13.23.721393 AM +04:00

SQL>


SQL> create table test (id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-JAN-14 09.15.10.917401 AM +04:00

SQL>



-- Now create restore point for flashback db, but you may flashaback db using timestamp or scn too.
SQL> create restore point first_point_09102014_0917;

Restore point created.

-- Now drop table
SQL> drop table test;

Table dropped.



SQL> conn /as sysdba
Connected.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size    2217992 bytes
Variable Size  499124216 bytes
Database Buffers  335544320 bytes
Redo Buffers    2396160 bytes
Database mounted.

SQL> flashback database to restore point first_point_09102014_0917;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

--After flashback db we have to open db with resetlogs option.

-- Now check table and data

SQL> conn ulfet/ulfet
Connected.

SQL> select * from test;

ID
----------
1

SQL> 



-- Take backup of db and archivelog all and delete input

RMAN> backup incremental level 0 device type disk tag 'FULL_BACKUP_0' database;

Starting backup at 09-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/DB11GR2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/DB11GR2/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DB11GR2/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DB11GR2/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DB11GR2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 09-JAN-14
channel ORA_DISK_1: finished piece 1 at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_nnnd0_FULL_BACKUP_0_9dwdltvh_.bkp tag=FULL_BACKUP_0 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
Finished backup at 09-JAN-14

Starting Control File and SPFILE Autobackup at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/autobackup/2014_01_09/o1_mf_s_836385904_9dwdojxc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-14


RMAN> backup  device type disk tag "ARCHIVELOG_BACKUP" archivelog all not backed up delete all input;

Starting backup at 09-JAN-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=3 STAMP=836385564
input archived log thread=1 sequence=3 RECID=4 STAMP=836385564
input archived log thread=1 sequence=4 RECID=2 STAMP=836385562
channel ORA_DISK_1: starting piece 1 at 09-JAN-14
channel ORA_DISK_1: finished piece 1 at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwdtrhj_.bkp tag=ARCHIVELOG_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_2_9dwdbtld_.arc RECID=3 STAMP=836385564
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_3_9dwdbwp4_.arc RECID=4 STAMP=836385564
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_3_9dwcdmhp_.arc RECID=1 STAMP=836384595
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_4_9dwdbtjj_.arc RECID=2 STAMP=836385562
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=5 STAMP=836386072
channel ORA_DISK_1: starting piece 1 at 09-JAN-14
channel ORA_DISK_1: finished piece 1 at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwdtvmf_.bkp tag=ARCHIVELOG_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_1_9dwdtr7r_.arc RECID=5 STAMP=836386072
Finished backup at 09-JAN-14

Starting Control File and SPFILE Autobackup at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/autobackup/2014_01_09/o1_mf_s_836386076_9dwdtww2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-14

RMAN> 



-- Now make dml and ddl statement 

SQL> conn ulfet/ulfet
Connected.

SQL> select * from test;

ID
----------
1

SQL> update test set id=111 where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> create table test2 as select * from test where 1=2;

Table created.

--Generate Archivelogs

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> 


-- Take again archivelogs backup
[oracle@dbserver Desktop]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 9 09:33:39 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB11GR2 (DBID=1660417640)

RMAN> backup  device type disk tag "ARCHIVELOG_BACKUP" archivelog all not backed up delete all input;

Starting backup at 09-JAN-14
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=6 STAMP=836386263
input archived log thread=1 sequence=3 RECID=7 STAMP=836386270
input archived log thread=1 sequence=4 RECID=8 STAMP=836386427
channel ORA_DISK_1: starting piece 1 at 09-JAN-14
channel ORA_DISK_1: finished piece 1 at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwf5w15_.bkp tag=ARCHIVELOG_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_2_9dwf0qnp_.arc RECID=6 STAMP=836386263
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_3_9dwf0y20_.arc RECID=7 STAMP=836386270
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_4_9dwf5vld_.arc RECID=8 STAMP=836386427
Finished backup at 09-JAN-14

Starting Control File and SPFILE Autobackup at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/autobackup/2014_01_09/o1_mf_s_836386429_9dwf5xcm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-14

RMAN> 

[oracle@dbserver 2014_01_09]$ pwd
/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09

[oracle@dbserver 2014_01_09]$ ls
[oracle@dbserver 2014_01_09]$ 

-- Now try to restore using flashback db

Jan 9 09:32:00 2014 -- before backup of archivelog



SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size    2217992 bytes
Variable Size  499124216 bytes
Database Buffers  335544320 bytes
Redo Buffers    2396160 bytes
Database mounted.

SQL> FLASHBACK DATABASE TO TIMESTAMP timestamp '2014-01-09 09:32:00';
FLASHBACK DATABASE TO TIMESTAMP timestamp '2014-01-09 09:32:00'
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 989641 to SCN 990218
ORA-38761: redo log sequence 2 in thread 1, incarnation 3 could not be accessed


SQL> 


[oracle@dbserver ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 9 09:43:22 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB11GR2 (DBID=1660417640, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB11GR2  1660417640       PARENT  1          15-AUG-09
2       2       DB11GR2  1660417640       PARENT  945184     11-SEP-13
3       3       DB11GR2  1660417640       CURRENT 989483     09-JAN-14

RMAN> 



-- Take which archivelog need

SQL> SELECT NAME
  FROM V$ARCHIVED_LOG A, V$DATABASE_INCARNATION I 
 WHERE A.RESETLOGS_ID = I.RESETLOGS_ID
   AND A.SEQUENCE# = 2 
   AND A.THREAD# = 1
   AND I.INCARNATION# = 3  2    3    4    5    6  ;

NAME
--------------------------------------------------------------------------------



-- Try restore archivelog and select again

RMAN> restore archivelog from sequence 1;

Starting restore at 09-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwdtvmf_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwdtvmf_.bkp tag=ARCHIVELOG_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwf5w15_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwf5w15_.bkp tag=ARCHIVELOG_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-JAN-14

RMAN> 


SQL> SELECT NAME
  FROM V$ARCHIVED_LOG A, V$DATABASE_INCARNATION I 
 WHERE A.RESETLOGS_ID = I.RESETLOGS_ID
   AND A.SEQUENCE# = 2 
   AND A.THREAD# = 1
   AND I.INCARNATION# = 3  2    3    4    5    6  ;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_2_9dwg
1v3r_.arc



-- Needed archivelog is available now try to flashback

SQL> FLASHBACK DATABASE TO TIMESTAMP timestamp '2014-01-09 09:32:00';

Flashback complete.


SQL> alter database open resetlogs;

Database altered.

SQL> 

-- Now check 
SQL> conn ulfet/ulfet
Connected.

SQL> select * from tab;

TNAME       TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST       TABLE
TEST2       TABLE

SQL> select * from test;

ID
----------
       111

SQL> select * from test2;

no rows selected

SQL> 

Thats all
at 2 comments:
Subscribe to: Posts (Atom)

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...