If you ever receive the following error while running RMAN duplicate command, that could be because you did not create a password file before running RMAN duplicate.
Finished restore at 14-APR-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2336
LOGFILE
GROUP 1 '/u0202/oradata/PROD/redo01.log' SIZE 50 M ,
GROUP 2 '/u0202/oradata/PROD/redo02.log' SIZE 50 M ,
GROUP 3 '/u0202/oradata/PROD/redo03.log' SIZE 50 M
DATAFILE
'/u0201/oradata/PROD/system01.dbf'
CHARACTER SET AL32UTF8
released channel: ch1
released channel: ch2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/14/2010 12:12:03
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-00600: internal error code, arguments: [kccsga_update_amx_1], [11], [2656], [2353], [], [], [], []
Solution: Go ahead and create the password file and rerun RMAN duplicate command again.You do not have to cleanout the old datafiles and start over in 10g. Just kick off the rman duplicate script again and it will restart from where it has left off.
Sunday, April 11, 2010
Sunday, April 04, 2010
Flashbacklogs Cleanup in Oracle
Flashback logs cleanup without downtime – Step by Step
Summary
Have you ever gotten into a situation where flash back logs have been building up for years in the Flash Recovery Area of your database and that you are stumped as to how to clean them up manually? You are dealing with the production database and that no downtime is allowed unless it is of utmost necessity. How do we cleanup the flashback logs from the Flash Recovery Area without any downtime to the production database? Before we go into the details, let us see what Flash Recovery Area and Flash Back Database are. (Trust me, many people are so confused with these two terms and many actually think they are the same)
Flash Recovery Area
Flash Recovery Area contains all the files you need to completely recover a database from a media failure. They are archivelog files, RMAN backups, control files, flash back logs. Oracle manages these files automatically when the database experience space pressure in Flash Recovery Area. In other words, when Flash recovery area gets to 85% filled, it will issue a warning, but continue to fill up FRA to 100%. As soon as FRA reaches 100%, oracle will start deleting these files from the oldest to make room for new files.
Flash Back Database
You can set your database to use Flash Back Database feature. This feature allows your entire database to be reverted back to a point in time in the past. When you have this feature set, flash back logs will be created. You can limit the number of flashback logs created by setting the retention parameter.
In our case we over estimated the Flash Recovery Area in our database and that flashback logs have been growing for several years. Oracle does not recommend deleting these files from using operating system commands, because that might corrupt your database. You need to let Oracle delete these files automatically. But these files are not going to be deleted by oracle, unless 100% space is filled up. I wanted to take manual action at this point and delete last 4 years worth of flash back files from FRA.
There are two ways of achieving this.
1. Disable flashback database feature, bounce the database and enable it back. But as you all know, this requires down time and hence not a preferred solution.
2. Shrink the FRA size to the used space, to trick oracle into thinking that FRA is 100% full, in which case oracle will start deleting the files automatically to make room.
Let us now look at step by step process to accomplish this task.
Following are the steps I executed in production to accomplish this using the 2nd option.
First, let us check the size of FLASH_RECOVERY_AREA
SQL> show parameter db_recovery
NAME TYPE VALUE
--------------------- ------------------------------------------------------------
db_recovery_file_dest string /u01/flash_recovery_area
db_recovery_file_dest_size big integer 190000M
So the size of the FLASH_RECOVERY_AREA is 190GB.
How do we know how much of that 190GB is used and how much is free? Issue the following query to get that information.
SQL> select space_used/(1024*1024*1024),space_limit/(1024*1024*1024) from v$recovery_file_dest;
SPACE_USED/(1024*1024*1024) SPACE_LIMIT/(1024*1024*1024)
--------------------------- ----------------------------
55.6249833 185.546875
Only 55GB of space is being used. Now we want to see, how much of space is being split among flashback logs, archivelogs and backups. Run the following query to get that information.
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- -----------------------------------------------------------------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 18.17 6.03 4691
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 11.81 11.73 1077
6 rows selected.
As per the above query output, 18.17% of FRA space is used for archivelog files, out of which 6.03% is reclaimable. 11.81% of the space is used for flashback logs, out of which 11.73% is reclaimable.
Let us now verify the retention target for flashback.
SQL> show parameter db_flashback
NAME TYPE VALUE
------------------------------------ ----------- --------------------------------------
db_flashback_retention_target integer 1440
Retention is set to 24 hours so only last 24 hours of flashback logs can be utilized anyway.
We can free up all the space utilized by flashback logs older than last 24 hours and reclaim space. Following steps illustrates how to accomplish this.
Make sure the following parameter is set.
SQL> show parameter log_archive_min_succeed_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_min_succeed_dest integer 1
Set log_archive_dest_1 to a different location where there is enough space. This is required to prevent the database from hanging for want of space while db_recovery_file_destination_size is reduced preventing archivelog file creation.
SQL> alter system set log_archive_dest_1=’LOCATION=/home/oracle/temp_archivelog’ scope=memory;
System altered.
DEFER the default DB_RECOVERY_FILE_DESTINATION so archivelogs won’t be written to the default destination.
SQL> alter system set log_archive_dest_state_10=defer scope=memory;
System altered.
Switch logfiles and make sure archivelog files are created in the new location /oracle/home/temp_archivelog
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Make the db_recovery_file_size to 55GB, same as the actual usage to make oracle delete older files.
SQL> alter system set db_recovery_file_dest_size=55G scope=memory;
System altered.
You will see following messages in the alert log
ALTER SYSTEM SET db_recovery_file_dest_size=’55G’ SCOPE=MEMORY;
Mon Jul 27 13:27:45 2009
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngo02h_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngookw_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngpd5j_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngq3l6_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngqw4j_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngrpfp_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngsjpb_.flb
Now let us check the space now by issuing the same query used before.
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ----------------------------------------------------------------------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 61.3 20.34 4692
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 38.7 38.44 1046
6 rows selected.
SQL> select space_used/(1024*1024*1024),space_limit/(1024*1024*1024) from v$recovery_file_dest;
SPACE_USED/(1024*1024*1024) SPACE_LIMIT/(1024*1024*1024)
--------------------------- ----------------------------
54.9990034 55
Note the new sizes and the number of files. It looks like oracle deleted some flashback logs but none of the archivelog files were deleted.
Alert log showed following warnings.
Mon Jul 27 13:31:42 2009
Errors in file /u01/app/oracle/product/admin/PRODDB/bdump/db_mmon_22830.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 87.63% used, and has 6642196992 remaining bytes available.
Oracle could not delete anymore files because archivelog files were not backed up or even cleaned out. For archivelog files to become eligible for automatic cleanout, they needs to be either backed up or deleted manually.
So I removed archive log files from the os manually and ran the following commands from RMAN.
RMAN> crosscheck archivelog all;
RMAN> delete expired;
Now check space
SQL> /
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ------------------------------------------------------------------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 3.83 0 1274
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 51.23 50.93 1007
6 rows selected.
Reclaimable space for ARCHIVELOG is now 0 and number of files matched RMAN retention period. In other words, all the archivelog files belonged to the retention set in RMAN.
Now check the new sizes.
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- --------------------------------------------------------------------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 97.95 0 1274
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 7.76 0 6
6 rows selected.
SQL> select space_used/(1024*1024*1024),space_limit/(1024*1024*1024) from v$recovery_file_dest;
SPACE_USED/(1024*1024*1024) SPACE_LIMIT/(1024*1024*1024)
--------------------------- --------------------------------------------------
1.6516757 1.66015625
Reclaimable space for FLASHBACKLOG is 0 and that there are only 6 files left. I also confirmed that those were from the last 24 hours.
Put the log_archive_destination back
SQL> alter system set log_archive_dest_1=’ ‘ scope=memory;
System altered.
SQL> alter system set log_archive_dest_10=’LOCATION=USE_DB_RECOVERY_FILE_DEST’ scope=memory;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4253
Next log sequence to archive 4254
Current log sequence 4254
Make sure to increase flash_recovery_area so there is enough space to write files.
SQL> alter system set db_recovery_file_dest_size=50G scope=memory;
System altered.
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/flash_recovery_area
db_recovery_file_dest_size big integer 50G
Now finally enable the log_archive_dest_state
SQL> alter system set log_archive_dest_state_10=enable scope=memory;
System altered.
Summary
Have you ever gotten into a situation where flash back logs have been building up for years in the Flash Recovery Area of your database and that you are stumped as to how to clean them up manually? You are dealing with the production database and that no downtime is allowed unless it is of utmost necessity. How do we cleanup the flashback logs from the Flash Recovery Area without any downtime to the production database? Before we go into the details, let us see what Flash Recovery Area and Flash Back Database are. (Trust me, many people are so confused with these two terms and many actually think they are the same)
Flash Recovery Area
Flash Recovery Area contains all the files you need to completely recover a database from a media failure. They are archivelog files, RMAN backups, control files, flash back logs. Oracle manages these files automatically when the database experience space pressure in Flash Recovery Area. In other words, when Flash recovery area gets to 85% filled, it will issue a warning, but continue to fill up FRA to 100%. As soon as FRA reaches 100%, oracle will start deleting these files from the oldest to make room for new files.
Flash Back Database
You can set your database to use Flash Back Database feature. This feature allows your entire database to be reverted back to a point in time in the past. When you have this feature set, flash back logs will be created. You can limit the number of flashback logs created by setting the retention parameter.
In our case we over estimated the Flash Recovery Area in our database and that flashback logs have been growing for several years. Oracle does not recommend deleting these files from using operating system commands, because that might corrupt your database. You need to let Oracle delete these files automatically. But these files are not going to be deleted by oracle, unless 100% space is filled up. I wanted to take manual action at this point and delete last 4 years worth of flash back files from FRA.
There are two ways of achieving this.
1. Disable flashback database feature, bounce the database and enable it back. But as you all know, this requires down time and hence not a preferred solution.
2. Shrink the FRA size to the used space, to trick oracle into thinking that FRA is 100% full, in which case oracle will start deleting the files automatically to make room.
Let us now look at step by step process to accomplish this task.
Following are the steps I executed in production to accomplish this using the 2nd option.
First, let us check the size of FLASH_RECOVERY_AREA
SQL> show parameter db_recovery
NAME TYPE VALUE
--------------------- ------------------------------------------------------------
db_recovery_file_dest string /u01/flash_recovery_area
db_recovery_file_dest_size big integer 190000M
So the size of the FLASH_RECOVERY_AREA is 190GB.
How do we know how much of that 190GB is used and how much is free? Issue the following query to get that information.
SQL> select space_used/(1024*1024*1024),space_limit/(1024*1024*1024) from v$recovery_file_dest;
SPACE_USED/(1024*1024*1024) SPACE_LIMIT/(1024*1024*1024)
--------------------------- ----------------------------
55.6249833 185.546875
Only 55GB of space is being used. Now we want to see, how much of space is being split among flashback logs, archivelogs and backups. Run the following query to get that information.
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- -----------------------------------------------------------------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 18.17 6.03 4691
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 11.81 11.73 1077
6 rows selected.
As per the above query output, 18.17% of FRA space is used for archivelog files, out of which 6.03% is reclaimable. 11.81% of the space is used for flashback logs, out of which 11.73% is reclaimable.
Let us now verify the retention target for flashback.
SQL> show parameter db_flashback
NAME TYPE VALUE
------------------------------------ ----------- --------------------------------------
db_flashback_retention_target integer 1440
Retention is set to 24 hours so only last 24 hours of flashback logs can be utilized anyway.
We can free up all the space utilized by flashback logs older than last 24 hours and reclaim space. Following steps illustrates how to accomplish this.
Make sure the following parameter is set.
SQL> show parameter log_archive_min_succeed_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_min_succeed_dest integer 1
Set log_archive_dest_1 to a different location where there is enough space. This is required to prevent the database from hanging for want of space while db_recovery_file_destination_size is reduced preventing archivelog file creation.
SQL> alter system set log_archive_dest_1=’LOCATION=/home/oracle/temp_archivelog’ scope=memory;
System altered.
DEFER the default DB_RECOVERY_FILE_DESTINATION so archivelogs won’t be written to the default destination.
SQL> alter system set log_archive_dest_state_10=defer scope=memory;
System altered.
Switch logfiles and make sure archivelog files are created in the new location /oracle/home/temp_archivelog
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Make the db_recovery_file_size to 55GB, same as the actual usage to make oracle delete older files.
SQL> alter system set db_recovery_file_dest_size=55G scope=memory;
System altered.
You will see following messages in the alert log
ALTER SYSTEM SET db_recovery_file_dest_size=’55G’ SCOPE=MEMORY;
Mon Jul 27 13:27:45 2009
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngo02h_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngookw_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngpd5j_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngq3l6_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngqw4j_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngrpfp_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngsjpb_.flb
Now let us check the space now by issuing the same query used before.
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ----------------------------------------------------------------------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 61.3 20.34 4692
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 38.7 38.44 1046
6 rows selected.
SQL> select space_used/(1024*1024*1024),space_limit/(1024*1024*1024) from v$recovery_file_dest;
SPACE_USED/(1024*1024*1024) SPACE_LIMIT/(1024*1024*1024)
--------------------------- ----------------------------
54.9990034 55
Note the new sizes and the number of files. It looks like oracle deleted some flashback logs but none of the archivelog files were deleted.
Alert log showed following warnings.
Mon Jul 27 13:31:42 2009
Errors in file /u01/app/oracle/product/admin/PRODDB/bdump/db_mmon_22830.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 87.63% used, and has 6642196992 remaining bytes available.
Oracle could not delete anymore files because archivelog files were not backed up or even cleaned out. For archivelog files to become eligible for automatic cleanout, they needs to be either backed up or deleted manually.
So I removed archive log files from the os manually and ran the following commands from RMAN.
RMAN> crosscheck archivelog all;
RMAN> delete expired;
Now check space
SQL> /
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ------------------------------------------------------------------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 3.83 0 1274
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 51.23 50.93 1007
6 rows selected.
Reclaimable space for ARCHIVELOG is now 0 and number of files matched RMAN retention period. In other words, all the archivelog files belonged to the retention set in RMAN.
Now check the new sizes.
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- --------------------------------------------------------------------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 97.95 0 1274
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 7.76 0 6
6 rows selected.
SQL> select space_used/(1024*1024*1024),space_limit/(1024*1024*1024) from v$recovery_file_dest;
SPACE_USED/(1024*1024*1024) SPACE_LIMIT/(1024*1024*1024)
--------------------------- --------------------------------------------------
1.6516757 1.66015625
Reclaimable space for FLASHBACKLOG is 0 and that there are only 6 files left. I also confirmed that those were from the last 24 hours.
Put the log_archive_destination back
SQL> alter system set log_archive_dest_1=’ ‘ scope=memory;
System altered.
SQL> alter system set log_archive_dest_10=’LOCATION=USE_DB_RECOVERY_FILE_DEST’ scope=memory;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4253
Next log sequence to archive 4254
Current log sequence 4254
Make sure to increase flash_recovery_area so there is enough space to write files.
SQL> alter system set db_recovery_file_dest_size=50G scope=memory;
System altered.
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/flash_recovery_area
db_recovery_file_dest_size big integer 50G
Now finally enable the log_archive_dest_state
SQL> alter system set log_archive_dest_state_10=enable scope=memory;
System altered.
Subscribe to:
Posts (Atom)