Exercise - Using the control file autobackup to recover from the lost of all control files - Oracle 10g

Environment: Oracle 10gR2

Summary of the testing steps

1. First, configure RMAN to perform a control file autobackup:
2. Next, perform a backup with the control file autobackup enabled:
3. Next, simulate the missing control files by deleting all the control files.
4. Next, start the database in NOMOUNT mode, which is required because there is no control file to mount.
5. Next, connect to RMAN and the target database. Specify the DBID to identify the database you are connecting to
6. Next, restore the control file from backup:
7. Next, mount the database and begin to recover the database:
8. Finally, open the database with RESETLOGS option for normal operations:

Detailed steps:

1. First, configure RMAN to perform a control file autobackup:

RMAN> connect target
connected to target database: TOY10G (DBID=3330944552)
RMAN> configure controlfile autobackup on;
using target database control file instead of recovery catalog

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN>
RMAN> show all;
RMAN>

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
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 CHANNEL DEVICE TYPE DISK FORMAT '/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_%t_%s_%p.dbf';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/snapcf_TOY10G.f';


RMAN>

2. Next, perform a backup with the control file autobackup enabled:


RMAN> run
2> {
3> backup database;
4> backup (archivelog all);
5> }

Starting backup at 06-JUL-2007 14:19:13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=35 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/system01.dbf

input datafile fno=00003 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/sysaux01.dbf

input datafile fno=00004 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/users01.dbf

input datafile fno=00005 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/indx01.dbf

input datafile fno=00002 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 06-JUL-2007 14:19:15

channel ORA_DISK_1: finished piece 1 at 06-JUL-2007 14:19:22

piece handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_627229154_17_1.dbf tag=TAG20070706T141914 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 06-JUL-2007 14:19:22
Starting backup at 06-JUL-2007 14:19:25
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=478 recid=1 stamp=627229166
channel ORA_DISK_1: starting piece 1 at 06-JUL-2007 14:19:27
channel ORA_DISK_1: finished piece 1 at 06-JUL-2007 14:19:28
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_627229166_18_1.dbf tag=TAG20070706T141926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 06-JUL-2007 14:19:28
Starting Control File Autobackup at 06-JUL-2007 14:19:28
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp comment=NONE
Finished Control File Autobackup at 06-JUL-2007 14:19:30

RMAN>

3. Next, simulate the missing control files by deleting all the control files. (The database will need to be shut down to perform this simulated failure.)

sys@TOY10G> select name from v$controlfile;


NAME
----------------------------------------------------------------------------------------------------
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf


after shutdown, do:

rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf


4. Next, start the database in NOMOUNT mode, which is required because there is no control file to mount:


idle> conn / as sysdba

Connected to an idle instance.

idle> startup nomount pfile='/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/pfile/initTOY10G.ora';

ORACLE instance started.


5. Next, connect to RMAN and the target database. Specify the DBID to identify the database you are connecting to, because the control file contains this information and failure causes the control file to be unavailable.

RMAN> connect target

connected to target database: TOY10G (not mounted)

RMAN> set dbid 3330944552

executing command: SET DBID

RMAN>

6. Next, restore the control file from backup:

RMAN> restore controlfile from autobackup;
Starting restore at 06-JUL-2007 14:35:11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=47 devtype=DISK


recovery area destination: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G

database name (or database unique name) used for search: TOY10G

channel ORA_DISK_1: autobackup found in the recovery area

channel ORA_DISK_1: autobackup found: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf

output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf

output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf

Finished restore at 06-JUL-2007 14:35:18



RMAN>


7. Next, mount the database and begin to recover the database:
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;


Starting recover at 06-JUL-2007 14:44:20
Starting implicit crosscheck backup at 06-JUL-2007 14:44:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 06-JUL-2007 14:44:22
Starting implicit crosscheck copy at 06-JUL-2007 14:44:22
using channel ORA_DISK_1
Crosschecked 5 objects
Finished implicit crosscheck copy at 06-JUL-2007 14:44:22


searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
File Name: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 479 is already on disk as file /ora01/orabkup/GENQ/.temp_toy10g/ora01/oraredo/TOY10G/redo02.log
archive log filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraredo/TOY10G/redo02.log thread=1 sequence=479
media recovery complete, elapsed time: 00:00:03
Finished recover at 06-JUL-2007 14:44:28

RMAN>

8. Finally, open the database with RESETLOGS option for normal operations:

RMAN> alter database open resetlogs;
database opened
RMAN>

Exercise - Switch the database archiving mode from NOARCHIVELOG to ARCHIVELOG

Exercise - Switch the database archiving mode from NOARCHIVELOG to
ARCHIVELOG
--------------------------------------------------------------------
Environment: Oracle 10g

1. Shut down the database instance.

sys@TOY10G> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TOY10G>

An open database must first be closed and any associated instances shut
down
before you can switch the database archiving mode. You cannot change the
mode from
ARCHIVELOG to NOARCHIVELOG if any datafiles need media recovery.

2. Back up the database.

Before making any major change to a database, always back up the
database to protect
against any problems. This will be your final backup of the database in
NOARCHIVELOG
mode and can be used if something goes wrong during the change to
ARCHIVELOG mode.

3. Edit the initialization parameter file to include the initialization
parameters
that specify the destinations for the archive log files.

log_archive_dest_1 =
"LOCATION=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G"
log_archive_dest_state_1 = enable
LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc

4. Start a new instance and mount, but do not open, the database.

idle> conn / as sysdba
Connected to an idle instance.
idle> startup mount
pfile='/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/pfile/initTO
Y10G.ora';
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size 2029392 bytes
Variable Size 92276912 bytes
Database Buffers 167772160 bytes
Redo Buffers 6356992 bytes
Database mounted.

To enable or disable archiving, the database must be mounted but not
open.

4. Change the database archiving mode. Then open the database for normal
operations.

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

idle> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraarch/TOY10G
Oldest online log sequence 476
Next log sequence to archive 478
Current log sequence 478

5. Shut down the database.

SHUTDOWN IMMEDIATE

6. Back up the database.

Changing the database archiving mode updates the control file. After
changing the database archiving mode, you must back up all of your
database files and control file. Any previous backup is no longer usable
because it was taken in NOARCHIVELOG mode.

Link - Oracle Replication FAQ

<<faqrepl.url>>

The message is ready to be sent with the following file or link
attachments:

Shortcut to: http://orafaq.com/faqrepl.htm#DEFERR

Note: To protect against computer viruses, e-mail programs may prevent
sending or receiving certain types of file attachments. Check your
e-mail security settings to determine how attachments are handled.

RMAN: How to delete backup of archivelog in catalog after deleting by OS command

========================================================================
==
How to delete backup of archivelog in catalog after deleting by OS
command
========================================================================
==
Environment: Oracle 8i

1. Resync catalog if necessary

2. Query RC_BACKUP_SET
2.1 Log into catalog db as catalog user:
sqlplus rman/xxx@catp92a

2.2 Generate RMAN command to crosscheck backupset of archivelog for next

step. (dbid represents GENB database)
------------------------------------------------------------------------
--
select 'change backupset ' || bs_key || ' crosscheck;' || ' # ' ||
completion_time
from rc_backup_set
where db_id=3427423174
and completion_time > sysdate - 7
and completion_time < sysdate - 1
and backup_type='L'
/

Sample Output:

change backupset 19565786 crosscheck; # 28-JUN-2007 19:56:06
change backupset 19565787 crosscheck; # 28-JUN-2007 19:56:06
change backupset 19676247 crosscheck; # 03-JUL-2007 21:02:26
change backupset 19676248 crosscheck; # 03-JUL-2007 21:04:26
change backupset 19676249 crosscheck; # 03-JUL-2007 21:06:04
change backupset 19676250 crosscheck; # 03-JUL-2007 21:07:43
change backupset 19676251 crosscheck; # 03-JUL-2007 21:09:02
change backupset 19676252 crosscheck; # 03-JUL-2007 21:10:18
change backupset 19676253 crosscheck; # 03-JUL-2007 21:11:32
change backupset 19676254 crosscheck; # 03-JUL-2007 21:12:44
change backupset 19676259 crosscheck; # 04-JUL-2007 21:00:52

3. Cross check the backupset of archivelog
rman target / catalog rman/xxx@catp92a
allocate channel for maintenance type disk;
execute the command obtained in step 2

4. Delete expired backup of archivelog;
rman target / catalog rman/xxx@catp92a

Example syntax to delete expired backup of archivelog
-----------------------------------------------------
delete expired backup of archivelog from time '28-JUN-2007' until
time '04-JUL-2007';

Note: If it just hang there for more than 10 min, ctrl-c then take
following action:

Action:
~~~~~~
4.1 Connect without catalog:
rman target / nocatalog
then issue:
RMAN> allocate channel for maintenance type disk;
RMAN> delete expired backup of archivelog from time '28-JUN-2007'
until
time '04-JUL-2007';
4.2 Connect to target and catalog databases issue:
RMAN> resync catalog;

Experience errors in a test database refreshed from production

MYDBP is a production database (8i) with replication and in archivelog mode. MYDB3S is a test databe, intend to be standalone and in noarchivelog mode. I created database MYDB3S from RMAN backup of MYDBP using RMAN duplicate command. However, I have made two mistakes:


1. Dropped replication package, resulting in some triggers invalid

In some triggers, there is a condition check:

IF DBMS_REPUTIL.FROM_REMOTE
THEN
RETURN;
END IF;

While I executed the $ORACLE_HOME/rdbms/admin/catrepr.sql to remove replication catalog views, packages, etc, I dropped the DBMS_REPUTIL package among other things.

Solution: re-install replciation catalog views and package by issue:
$ORACLE_HOME/rdbms/admin/catrep.sql

2. Failed to turn off archivelog mode
I only set archive_log_start=false in the init.ora. I should have issued
'alter system noarchivelog' in the mount mode too to really turn off the archivelog mode