Problem: Unable to find/lock the archivelog 'XXXX"
Scenario:
Archive log we manually backedup to tape and deleted from the source.
We configured a catalog for that database and then gave the below command;
rman target / catalog rmanrep/rmanrep@RMAN4
Recovery Manager: Release 9.2.0.3.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: RMAN4 (DBID=4109780682)connected to recovery catalog database
RMAN> backup format '/u01/app/oracle/sandy/proddb/1.arc' archivelog all
Unable to identift/locg logfile '/u01/app/oracle/sandy/proddb/9.2.0/arch/REOPENPROD_1_42.arc'
This error occured as 42 was deleted before creating the catalog.
the solution for this proble is
rman target / catalog rmanrep/rmanrep@RMAN4
Recovery Manager: Release 9.2.0.3.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights reserved.connected to target database: RMAN4 (DBID=4109780682)connected to recovery catalog database
RMAN> crosscheck archivelog all;
RMAN> backup format '/u01/app/oracle/sandy/proddb/1.arc' archivelog all
allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=18 devtype=DISKchannel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinputarchive log thread=1 sequence=43 recid=3 stamp=629043047input archive log thread=1 sequence=44 recid=4 stamp=629047148input archive log thread=1 sequence=45 recid=5 stamp=629051293input archive log thread=1 sequence=46 recid=6 stamp=629055443input archive log thread=1 sequence=47 recid=7 stamp=629059581input archive log thread=1 sequence=48 recid=8 stamp=629063680input archive log thread=1 sequence=49 recid=9 stamp=629067845input archive log thread=1 sequence=50 recid=10 stamp=629071975input archive log thread=1 sequence=51 recid=11 stamp=629076089input archive log thread=1 sequence=52 recid=12 stamp=629080236input archive log thread=1 sequence=53 recid=13 stamp=629084368input archive log thread=1 sequence=54 recid=14 stamp=629088490input archive log thread=1 sequence=55 recid=15 stamp=629092638input archive log thread=1 sequence=56 recid=16 stamp=629096758input archive log thread=1 sequence=57 recid=17 stamp=629100873
The above command "crosscheck archivelog all;" will actually delete the archivelog entry from controlfile. Thus Catalog will not try backingup the archivelog which is not present(or deleted)
Saturday, July 28, 2007
Saturday, July 21, 2007
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
When you clone a Instance from a DB which is non rac but a clone of RAC
Eg:
PROD--RAC
DEV-Non Rac cloned from PROD
No You want to clone DEV to TEST.
After restoring the DB from the backup of DEV when you issue the below command
SQL>Alter database open resetlogs;
*ERROR at line 1:ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
This is because of the RAC component which is still valid in Source.
Solution:
1) Solution:-Edit the init.ora file and put the parameter “_no_recovery_through_resetlogs”.Startup mount the instance.SQL>startup mount.SQL>alter database open resetlogs;
2)On the Source (DEV)
sqlplus / as sysdba
sql>dbms_registry.invalid("RAC");
SQL>exit;
Now take a backup of database and then clone TEST from this backup.
Thanks,
Sandarsh
Eg:
PROD--RAC
DEV-Non Rac cloned from PROD
No You want to clone DEV to TEST.
After restoring the DB from the backup of DEV when you issue the below command
SQL>Alter database open resetlogs;
*ERROR at line 1:ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
This is because of the RAC component which is still valid in Source.
Solution:
1) Solution:-Edit the init
2)On the Source (DEV)
sqlplus / as sysdba
sql>dbms_registry.invalid("RAC");
SQL>exit;
Now take a backup of database and then clone TEST from this backup.
Thanks,
Sandarsh
When to rebuild an Index .
We will follow the below procedure to find which index to rebuild.
analyze index INDEXMANEvalidate structure;
Once it is complete run the below three queries
Select del_lf_rows / lf_rows from index_stats;
Select HEIGHT from index_stats;
Select LF_ROWS, LF_BLKS from index_stats;
Now an index should be rebuilt if
1) percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3.
2) If the ‘HEIGHT’ is greater than 4.
3) If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.
For EXample
1)
INDEX_NAME: WF_ITEM_ATTRIBUTE_VALUES_PK
Size in MB:2000m
Del%(EL_LF_ROWS/LF_ROWS)%:17
HEIGHT: 4
LF_ROWS:31399384
LF_BLKS:265071
REBUILD: NO
2)
INDEX_NAME: CCIX_PLSQL_LOG_N1
Size in MB:1500
Del%(EL_LF_ROWS/LF_ROWS)%:0
HEIGHT: 4
LF_ROWS:57220
LF_BLKS:164476
REBUILD: YES
As LF_BLKS: is considerably higher thn LF_ROWS condition number 3.
Eg 3:
INDEX_NAME: CCIX_PLSQL_LOG_N1
Size in MB:1500
Del%(EL_LF_ROWS/LF_ROWS)%:35
HEIGHT: 4
LF_ROWS:17674676
LF_BLKS:154250
REBUILD: YES
Del%(EL_LF_ROWS/LF_ROWS)% > 30 condition 1.
Thanks,
Sandarsh
We will follow the below procedure to find which index to rebuild.
analyze index INDEXMANE
Once it is complete run the below three queries
Select del_lf_rows / lf_rows from index_stats;
Select HEIGHT from index_stats;
Select LF_ROWS, LF_BLKS from index_stats;
Now an index should be rebuilt if
1) percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3.
2) If the ‘HEIGHT’ is greater than 4.
3) If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.
For EXample
1)
INDEX_NAME: WF_ITEM_ATTRIBUTE_VALUES_PK
Size in MB:2000m
Del%(EL_LF_ROWS/LF_ROWS)%:17
HEIGHT: 4
LF_ROWS:31399384
LF_BLKS:265071
REBUILD: NO
2)
INDEX_NAME: CCIX_PLSQL_LOG_N1
Size in MB:1500
Del%(EL_LF_ROWS/LF_ROWS)%:0
HEIGHT: 4
LF_ROWS:57220
LF_BLKS:164476
REBUILD: YES
As LF_BLKS: is considerably higher thn LF_ROWS condition number 3.
Eg 3:
INDEX_NAME: CCIX_PLSQL_LOG_N1
Size in MB:1500
Del%(EL_LF_ROWS/LF_ROWS)%:35
HEIGHT: 4
LF_ROWS:17674676
LF_BLKS:154250
REBUILD: YES
Del%(EL_LF_ROWS/LF_ROWS)% > 30 condition 1.
Thanks,
Sandarsh
Tuesday, July 17, 2007
RMAN restore from a particular Days TAPE
Hi,
We got a scenario where we were required to restore database from a particular tape. Client asked us to find out the tape which should be used for that restore. We faced lots of issues in getting those tapes in place.
1) We were trying point in time recovery till 1 PM for restoring(time was not a constrain)
2)Restore command was not able to find the required backup and as it was 10g it failed after trying to access all the backups taken before that time.
3)Customer complained that he did not find any RMAN request was not coming to TAPE device.
4)We wasted about 8 hours.
Below are the couple of things we learnt during this activity.
1) RMAN always tries to restore from the backup taken prior to the time until which we try to restore
For Example:
suppose Until time is set till 13:00:00
Daily backup happens at 5 PM
And we are required to restore say till 29:07:07
In this scenario rman will restore from backup taken on 28th and apply all the archives generated after the backup till 29:07:07 13:00:00.
So the we must mount the tape of 28th .
Client wanted us to restore from tape containing the 29th backup. In this case you cannot use until time before 5:30Pm (Backup completion time)
Also one more thing we need to keep in mind is RMAN Always restores from the prevoius backup, it first tries to restore from 28th backup , if it is not there it sarches for 27th backup (and apply all the archive log till 29) so on..
The above feature is only present in 10G and later releases.
This is how we need to find out the backup and time for till which we can restore so that we remain within the available options.
Assuming that we have a CATALOG. Use the below select statements and extract the time from the catalog.
select to_char(COMPLETION_TIME,'MM/DD/YYYY HH24:MI:SS') from RC_BACKUP_DATAFILE where trunc(completion_time) >= trunc(sysdate-9);
.
. TO_CHAR(COMPLETION_
-------------------
06/29/2007 16:47:50
06/29/2007 16:47:50
06/29/2007 16:47:50
06/29/2007 16:45:09
06/29/2007 16:28:10
06/29/2007 15:18:42
06/29/2007 14:29:03
06/29/2007 14:19:37
06/29/2007 14:45:31
06/29/2007 14:48:36
06/29/2007 15:02:08
TO_CHAR(COMPLETION_
-------------------
06/29/2007 15:04:45
06/29/2007 15:17:04
06/29/2007 15:19:37
06/29/2007 15:21:37
06/29/2007 15:20:57
06/29/2007 15:20:19
06/29/2007 15:20:51
06/29/2007 15:21:20
06/29/2007 15:21:27
06/29/2007 15:21:35
06/29/2007 15:21:28
This Confirms the backup completion time. We can select any time after 15:21:28 which will confirm that the rman will restore from 29th backup.
Now till what time to recover , Since it is a Tape , if we give the restore time as 15:21:28 ,Then Rman will restore from the proper backup but DB open will fail saying some DBf's need more recovery. In such case you need to restore all the archive logs and apply them (or recover using RMAN) which will take plenty of time.
Its better we give a proper time for restore.
Use the below query.
select DB_NAME,to_char(COMPLETION_TIME,'MM/DD/YYYY HH24:MI:SS'),THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from rc_backup_redolog where trunc(completion_time) >= trunc(sysdate-9) and DB_NAME='PROD' order by 3,4,2;
.
.
PROD 06/29/2007 17:05:02 1 22239 2445236995 2445476389
DB_NAME TO_CHAR(COMPLETION_ THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
-------- ------------------- ---------- ---------- ------------- ------------
PROD 06/29/2007 17:05:02 1 22240 2445476389 2445739485
PROD 06/29/2007 17:05:02 1 22241 2445739485 2446147163
PROD 06/29/2007 17:05:02 1 22242 2446147163 2446554642
PROD 06/29/2007 17:05:02 1 22243 2446554642 2446899303
PROD 06/29/2007 17:05:02 1 22244 2446899303 2446936172
PROD 06/29/2007 17:05:02 1 22245 2446936172 2447040692
PROD 06/29/2007 17:05:02 1 22246 2447040692 2447308086
PROD 06/29/2007 17:05:02 1 22247 2447308086 2447610092
PROD 06/29/2007 17:05:02 1 22248 2447610092 2447611059
PROD 06/30/2007 16:56:09 1 22249 2447611059 2447876345
PROD 06/30/2007 16:56:09 1 22250 2447876345 2448128725
This is the archive log which is in the TAPE "PROD 06/30/2007 16:56:09 1 22250 2447876345 2448128725"
Now we can use the time "06/30/2007 16:56:09 " as the candidate for until time.
But good practice is to use until scn 2448128725 which is very accurate OR time when archive with sequence 22250 was created.
Now if we use "06/30/2007 16:56:09 " as until time then RMAN thinks that it must recover till "06/30/2007 16:56:09 " but this time is actually the archivelog backup completion time.
This archive must be generated before '06/30/2007 16:56:09 ".
The RMAN recovery will fail saying that it could not find archivelog 22251 .
In the above case do the below steps.
on the DB where rman restore failed
Connect as RMAN
rman target /
recover database until cancel
.
.
Just enter Cancel.
then open the database using restelogs (if required use "alter database open resetlogs using backup controlfile"
The above scenario will not occur if we use the until SCN or "Archive log ceration time"
Also in 10G as told earlier the RMAN will will fail over to previous backup if it does not find the exact backup.
eg :
it must restore from 29Th backup. If that tape is not mounted then it will try to restore from 28th backup . If 28th backup is available then it will restore the DBF's but recover will fail . It will now search for archive logs generated on 28'th after backup till "'06/30/2007 16:56:09" .
so some archives are in 28th tape and rest in 29th tape.
To avoid this make sure the RMAN restores from proper backup set.
We will use the above time for restore. Also list backup of database gives the information about the backup set which should be used for restore
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136394 Incr 0 191G SBT_TAPE 03:18:06 29-JUN-07
BP Key: 136397 Status: AVAILABLE Compressed: NO Tag: TAG20070629T133039
Handle: bk_u6lilgak0_s2261_p1_t626535040 Media:
List of Datafiles in backup set 136394
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 2447390136 29-JUN-07 /u02/oradata/PROD/system01.dbf
2 0 Incr 2447390136 29-JUN-07 /u02/oradata/PROD/undotbs01.dbf
3 0 Incr 2447390136 29-JUN-07 /u02/oradata/PROD/sysaux01.dbf
Rman must restore from bk_u6lilgak0_s2261_p1_t626535040 which is the first backupset created on 29th . This is present in the restore logfile. Make sure that RMAN does not fail over to previous days backup if so our untilTime should be the Culprit Or TAPE is not the correct one ot it May not be mounted properly.
Thanks,
Sandarsh.
We got a scenario where we were required to restore database from a particular tape. Client asked us to find out the tape which should be used for that restore. We faced lots of issues in getting those tapes in place.
1) We were trying point in time recovery till 1 PM for restoring(time was not a constrain)
2)Restore command was not able to find the required backup and as it was 10g it failed after trying to access all the backups taken before that time.
3)Customer complained that he did not find any RMAN request was not coming to TAPE device.
4)We wasted about 8 hours.
Below are the couple of things we learnt during this activity.
1) RMAN always tries to restore from the backup taken prior to the time until which we try to restore
For Example:
suppose Until time is set till 13:00:00
Daily backup happens at 5 PM
And we are required to restore say till 29:07:07
In this scenario rman will restore from backup taken on 28th and apply all the archives generated after the backup till 29:07:07 13:00:00.
So the we must mount the tape of 28th .
Client wanted us to restore from tape containing the 29th backup. In this case you cannot use until time before 5:30Pm (Backup completion time)
Also one more thing we need to keep in mind is RMAN Always restores from the prevoius backup, it first tries to restore from 28th backup , if it is not there it sarches for 27th backup (and apply all the archive log till 29) so on..
The above feature is only present in 10G and later releases.
This is how we need to find out the backup and time for till which we can restore so that we remain within the available options.
Assuming that we have a CATALOG. Use the below select statements and extract the time from the catalog.
select to_char(COMPLETION_TIME,'MM/DD/YYYY HH24:MI:SS') from RC_BACKUP_DATAFILE where trunc(completion_time) >= trunc(sysdate-9);
.
. TO_CHAR(COMPLETION_
-------------------
06/29/2007 16:47:50
06/29/2007 16:47:50
06/29/2007 16:47:50
06/29/2007 16:45:09
06/29/2007 16:28:10
06/29/2007 15:18:42
06/29/2007 14:29:03
06/29/2007 14:19:37
06/29/2007 14:45:31
06/29/2007 14:48:36
06/29/2007 15:02:08
TO_CHAR(COMPLETION_
-------------------
06/29/2007 15:04:45
06/29/2007 15:17:04
06/29/2007 15:19:37
06/29/2007 15:21:37
06/29/2007 15:20:57
06/29/2007 15:20:19
06/29/2007 15:20:51
06/29/2007 15:21:20
06/29/2007 15:21:27
06/29/2007 15:21:35
06/29/2007 15:21:28
This Confirms the backup completion time. We can select any time after 15:21:28 which will confirm that the rman will restore from 29th backup.
Now till what time to recover , Since it is a Tape , if we give the restore time as 15:21:28 ,Then Rman will restore from the proper backup but DB open will fail saying some DBf's need more recovery. In such case you need to restore all the archive logs and apply them (or recover using RMAN) which will take plenty of time.
Its better we give a proper time for restore.
Use the below query.
select DB_NAME,to_char(COMPLETION_TIME,'MM/DD/YYYY HH24:MI:SS'),THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from rc_backup_redolog where trunc(completion_time) >= trunc(sysdate-9) and DB_NAME='PROD' order by 3,4,2;
.
.
PROD 06/29/2007 17:05:02 1 22239 2445236995 2445476389
DB_NAME TO_CHAR(COMPLETION_ THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
-------- ------------------- ---------- ---------- ------------- ------------
PROD 06/29/2007 17:05:02 1 22240 2445476389 2445739485
PROD 06/29/2007 17:05:02 1 22241 2445739485 2446147163
PROD 06/29/2007 17:05:02 1 22242 2446147163 2446554642
PROD 06/29/2007 17:05:02 1 22243 2446554642 2446899303
PROD 06/29/2007 17:05:02 1 22244 2446899303 2446936172
PROD 06/29/2007 17:05:02 1 22245 2446936172 2447040692
PROD 06/29/2007 17:05:02 1 22246 2447040692 2447308086
PROD 06/29/2007 17:05:02 1 22247 2447308086 2447610092
PROD 06/29/2007 17:05:02 1 22248 2447610092 2447611059
PROD 06/30/2007 16:56:09 1 22249 2447611059 2447876345
PROD 06/30/2007 16:56:09 1 22250 2447876345 2448128725
This is the archive log which is in the TAPE "PROD 06/30/2007 16:56:09 1 22250 2447876345 2448128725"
Now we can use the time "06/30/2007 16:56:09 " as the candidate for until time.
But good practice is to use until scn 2448128725 which is very accurate OR time when archive with sequence 22250 was created.
Now if we use "06/30/2007 16:56:09 " as until time then RMAN thinks that it must recover till "06/30/2007 16:56:09 " but this time is actually the archivelog backup completion time.
This archive must be generated before '06/30/2007 16:56:09 ".
The RMAN recovery will fail saying that it could not find archivelog 22251 .
In the above case do the below steps.
on the DB where rman restore failed
Connect as RMAN
rman target /
recover database until cancel
.
.
Just enter Cancel.
then open the database using restelogs (if required use "alter database open resetlogs using backup controlfile"
The above scenario will not occur if we use the until SCN or "Archive log ceration time"
Also in 10G as told earlier the RMAN will will fail over to previous backup if it does not find the exact backup.
eg :
it must restore from 29Th backup. If that tape is not mounted then it will try to restore from 28th backup . If 28th backup is available then it will restore the DBF's but recover will fail . It will now search for archive logs generated on 28'th after backup till "'06/30/2007 16:56:09" .
so some archives are in 28th tape and rest in 29th tape.
To avoid this make sure the RMAN restores from proper backup set.
We will use the above time for restore. Also list backup of database gives the information about the backup set which should be used for restore
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136394 Incr 0 191G SBT_TAPE 03:18:06 29-JUN-07
BP Key: 136397 Status: AVAILABLE Compressed: NO Tag: TAG20070629T133039
Handle: bk_u6lilgak0_s2261_p1_t626535040 Media:
List of Datafiles in backup set 136394
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 2447390136 29-JUN-07 /u02/oradata/PROD/system01.dbf
2 0 Incr 2447390136 29-JUN-07 /u02/oradata/PROD/undotbs01.dbf
3 0 Incr 2447390136 29-JUN-07 /u02/oradata/PROD/sysaux01.dbf
Rman must restore from bk_u6lilgak0_s2261_p1_t626535040 which is the first backupset created on 29th . This is present in the restore logfile. Make sure that RMAN does not fail over to previous days backup if so our untilTime should be the Culprit Or TAPE is not the correct one ot it May not be mounted properly.
Thanks,
Sandarsh.
Subscribe to:
Posts (Atom)