RMAN: Archivelogs lost

Problem: I have lost some of the archivelog files without taking backup. If I run the rman to backup available archive logs, it throws error that the archivelog_seq# is not available.

Solution: run the following command.

RMAN> change archivelog all validate;

Now you run the backup archivelog command. RMAN will backup the available archivelogs successfully.

Thanks

Block Change Tracking

RMAN Incremental Backups backup only the blocks that were changed since the lastest base incremental backups. But RMAN had to scan the whole database to find the changed blocks. Hence the incremental backups read the whole database and writes only the changed blocks. Thus the incremental backups saves space but the reduction in the time is fairly neglegible.


Block Change Tracking (BCT) is a new feature in Oracle 10g. BCT enables RMAN to read only the blocks that were changed since the lastest base incremental backups. Hence by enabling BCT, RMAN reads only the changed blocks and writes only the changed blocks.


Without BCT, RMAN has to read every block in the database and compare the SCN in the block with the SCN in the base backup. If the block's SCN is greater than the SCN in the base backup then the block is a candidate for the new incremental backup. Usually only few blocks are changed between backups and the RMAN has to do unncessary work of reading the whole database.


BCT Stores the information about the blocks being changed inthe BlockChange Tracking File. The background process that does this logging is Change Tracking Writer (CWTR).


BlockChange Tracking File


BCT File is one per database and in the case RAC, it is shared among all the instances. BCT File is created in the location defined by the parameter DB_CREATE_FILE_DEST as OMF file.


To enable BCT


SQL> Alter Database Enable Block Change Tracking;


To disable BCT


SQL> Alter Database Disable Block Change Tracking;


To specify the BCT file location


SQL> Alter Database enable Block Change Tracking using File '/Backup/BCT/bct.ora';


A useful query,

SQL> Select Completion_time, datafile_blocks, blocks_read, blocks, used_change_tracking
From v$backup_datafile
where to_char(completion_time, 'dd/mon/yy') = to_char(sysdate, 'dd/mon/yy');

Where,
datafile_blocks is the total number of blocks in the datafile.
blocks_read is the total number of blocks read by RMAN
blocks is the total number of blocks backed up by the RMAN.
used_change_tracking if yes BCT is used, if no BCT is not used.

Thanks

Restore Vs Recovery

Restore

Restore means using the backup files to replace the original files after a media failure.

Recovery

Recovery means bringing the database up to date using the restored files, archive logs and online redo logs.

Thanks

FRA Parameters

The following parameters are involved in setting up a FRA.

1. DB_CREATE_FILE_DEST - location for all OMF data files.
2. DB_CREATE_ONLINE_LOG_DEST_n - location for control files and online redo log files. If this parameter is not set then oracle creates all three types of files in the DB_CREATE_FILE_DEST location.
3. DB_RECOVERY_FILE_DEST_SIZE - Size of FRA.
4. DB_RECOVERY_FILE_DEST - Location of FRA.
5. LOG_ARCHIVE-DEST_N - Location of Archive log files.

For eg.,

DB_CREATE_FILE_DEST = /oradata/dbfiles/
LOG_ARCHIVE-DEST_1 = 'LOCATION=/export/archive/arc_dest1'
LOG_ARCHIVE-DEST_2 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
DB_RECOVERY_FILE_DEST_SIZE =350GB
DB_RECOVERY_FILE_DEST='LOCATION=/fra/rec_area'

one copy of current control file and online redo log files are stored in FRA also.

Control Files, redolog files and FRA

DB_CREATE_ONLINE_DEST_n : Setting this init parameter enable Oracle to create OMF control files and online redolog files in the location specified by the parameter.

DB_RECOVERY_FILE_DEST : Setting this init parameter enables Oracle to create OMF control files and online redolog files in the FRA.

Specifying both the above parameters enable Oracle to create OMF based control and redolog files on both the locations.

Omitting the above parameters will enable Oracle to create non-OMF based control and redolog files in the system specific default location.

Thanks

Setup Flash Recovery Area

To setup the FRA, you need to set two init paramters in the following order.

SQL> Alter system set DB_RECOVERY_FILE_DEST_SIZE=375809638400 scope=spfile;

SQL> Alter system set DB_RECOVERY_FILE_DEST=+ASM_FLASH scope=spfile;

When you configure FRA, LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST init parameters become obsolete. You must use the parameters LOG_ARCHIVE_DEST_n.

Log_Archive_dest_10 is by default set to USE_DB_RECOVERY_FILE_DEST. Archived log files are stored in the FRA by default. If you have configured other locations a copy of archived files will also be placed in other locations as well.

USE_DB_RECOVERY_FILE_DEST refers to the FRA.

You can also use OEM and DBCA to configure FRA.

Disable FRA

Set DB_RECOVERY_FILE_DEST=''

Convert ArchiveLog into NoArchivelog mode in RAC

The post has been moved to a different location.

Click Here to access the post.

Archive to No Archive Mode

Steps to convert the database from Archivelog Mode to No Archive log mode.

SQL> Alter system set log_archive_start=false scope=spfile;
Database Altered

SQL> Shutdown Immediate
Database Closed
Database Dismounted
Instance Shutdown

SQL> Startup Mount;
Oracle Instance started
.....
Database Mounted

SQL> Alter Database NoArchivelog;
Database Altered

SQL> Alter database Open;
Database Altered.

SQL> Archivelog list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3108
Next log sequence to archive 3109
Current log sequence 3109

Your database is in NoArchivelog Mode.

Configure Archive Log in RAC

The post has been moved to another location. Click Here to access the post.

Configure Archive Log Mode

Oracle databases are created in NOARCHIVELOG mode. This means that you can only take consistent cold backups.

In order to perform online backups your database must be running in ARCHIVELOG mode. Archive log files can also be transmitted and applied to a standby database inDR (Disaster Recovery) site.

To find out the mode database is running. Login as sys user and issue the following statement.

SQL> archive log list
Database log mode : Archive Mode
Automatic archival : Enabled
Archive destination : /cgildb/oradata/archive
Oldest online log sequence : 3413
Next log sequence to archive : 3415
Current log sequence : 3415

You can also issue the following statement.

SQL> Select log_mode from v$database;
LOG_MODE
---------------
Archivelog

Convert NoArchive to ArchiveLog mode

Step 1: Set the following Initialization Parameter

1. Log_archive_dest_1 = 'LOCATION=/export/prodarch' MANDATORY REOPEN
2. Log_archive_dest_2 = 'SERVICE=stbyprod' OPTIONAL REOPEN=60
3. Log_archive_format = 'prod_%t_%r_%s.arc'
4. Log_archive_max_processes= 2
5. Log_archive_min_succeed_dest = 1
6. Log_archive_start= True (deprecated from oracle 10g).

Step 2:

SQL> Shutdown Immediate
Database Closed
Database Dismounted
Instance Shutdown
SQL> Startup Mount
Oracle Instance started
.....
Database Mounted
SQL> Alter Database Archivelog;
Database Altered
SQL> Alter Database Open;
Database Altered.

Thats it..Your database is in Archivelog mode now..

About Related Initialization Parameter

1. Log_archive_dest_n : n = 1 to 10. The destinationcan be on a local machine or a remote machine (Standby). The syntax is,
log_archive_dest_n = 'null' Service = service_name (from tnsnames) Location = directory [Mandatory Optional] [Reopen [= integer]]
where,
Mandatory - archiving must succeed at this destination.
Reopen - (time in secs) retries to archive when it did not succeed the first time. (default 300)

2. Log_archive_format: Format of the archived log filenames. Use any text in combination with any of the substitution variable to uniquely name the file. The substitution variables are,
%s - Log Sequence Number
%t - Thread Number
%r - Resetlogs ID (Ensure uniqueness when you reset the log sequence number)
%d - Database ID.

3. Log_archive_dest_state_n: (Enable Defer alternate reset)
Enable - available (default)
Defer - temporarily unavailable
Alternate - Initially deferred. Available when there is a failure in the primary destination.

4. Log_archive_max_processes : Maximum number of active archive processes. default 2. Range 1 - 10.

5. Log_archive_min_succeed_dest: Minimum number of destinations that arcn must write before overwriting the filled in online redo log file. Default - 1.

6. Log_archive_duplex_dest: When you use this parameter you can archive to a maximum of two destination. The first destination is given by log_archive_dest parameter and the second destination by this parameter.

7. Log_archive_start: Set this parameter to true to enable the archiver starts when you restart the instance. This parameter is not dynamic, so you must restart the instance to take effect.

SQL> alter system set log_archive_start=true scope=spfile;
SQL> Shutdown Immediate;
To enable automatic archiving with out shutting down the instance,
SQL> Alter system archive log start;
--Change the log_archive_start to true during next startup.

In 10g this parameter has been deprecated. By enabling the the archive log mode, the archiver will start automatically.