RMAN : Consistent Backup, Restore and Recovery using RMAN

Subject: RMAN : Consistent Backup, Restore and Recovery using RMAN

Doc ID: 162855.1

Oracle9i Lab 1

Consistent Backup, Restore and Recovery using RMAN

In Oracle9i, Recovery Manager still works the same as in 8 and 8i with

enhancements for ease of use and manageability. Using the updateable

persistent configuration options, backing up the database is easier then

ever. To begin lets look where information is stored in the database about

RMAN backups. For testing we’ll use the target database controlfile without

a recovery catalog. By setting up the autocontrolfile backup feature we can

use the controlfile backups for a recovery catalog.

1. V$ tables – What does the controlfile know?

What are the views and synonyms related to backup and rman?

Let’s take a look before backups are done to see what information

they hold for us.

SQL> set pagesize 60

SQL> column object_name format a30

SQL> select object_name from dba_objects

2 where object_name like ‘%BACKUP%’

3 and object_type = ‘SYNONYM’;

select object_name from dba_objects

where object_name like ‘%RMAN%’

and object_type = ‘SYNONYM’;

OBJECT_NAME

——————————–

V$BACKUP_CORRUPTION

V$BACKUP_DATAFILE

V$BACKUP_DEVICE

V$BACKUP_PIECE

V$BACKUP_REDOLOG

V$BACKUP_SET

V$BACKUP_SYNC_IO

V$RMAN_CONFIGURATION

V$BACKUP

V$BACKUP_ASYNC_IO

Other controlfile views to reference are:

v$controlfile_record_section, v$copy_corruption, v$database, v$datafile,

v$datafile_copy, v$offline_range, v$proxy_archivelog, v$proxy_datafile,

v$session_longops, v$database_block_corruption

2. RMAN Configuration

Check the default configuration in RMAN and then check the V$ view to see what

is stored. Then update the configuration in RMAN and take a look at the view

again. This is accomplished in RMAN using the “show all” command.

% sqlplus “/ as sysdba”

set pagesize 60

spool rman_new_conf.lst

column name format a30

column VALUE format a31

set echo on;

set serveroutput on;

select * from v$rman_configuration;

spool off;

CONF# NAME VALUE

———- —————————— ——————————-

1 RETENTION POLICY TO REDUNDANCY 3

2 BACKUP OPTIMIZATION OFF

3 DEFAULT DEVICE TYPE TO DISK

4 CONTROLFILE AUTOBACKUP ON

5 CONTROLFILE AUTOBACKUP FORMAT DISK TO ‘/proj/SME9i/backup/%F’

FOR DEVICE TYPE

6 DEVICE TYPE DISK PARALLELISM 2

7 DATAFILE BACKUP COPIES FOR DEV DISK TO 1

ICE TYPE

8 ARCHIVELOG BACKUP COPIES FOR D DISK TO 1

EVICE TYPE

9 MAXSETSIZE TO UNLIMITED

9 rows selected.

Since we are not using a recovery catalog let’s look at the controlfile since

this is our catalog without a recovery catalog. In sqlplus run:

select TYPE, RECORDS_TOTAL, RECORDS_USED

from v$controlfile_record_section

where type like ‘%BACKUP%’;

TYPE RECORDS_TOTAL RECORDS_USED

—————— ————- ————

BACKUP SET 101 0

BACKUP PIECE 204 0

BACKUP DATAFILE 210 0

BACKUP REDOLOG 53 0

BACKUP CORRUPTION 185 0

The records used column is still zero since we have not taken any backups.

The records are inserted into these views after successful creation of a

backupset. When RMAN reads the files to backup, if any corruption is

encountered then the corruption views are populated with file#, block# and

contiguous blocks after the initial corrupt block.

3. Create a persistent configuration for reuse

Create a persistent backup configuration. Once these parameters are configured

RMAN will continue to reuse the configured options for subsequent backups unless

you override the option within your script or to clear or disable it.

In 9i RMAN you use the “show” command to see the currently configured options.

SHOW show_operand [,show_operand …];

RMAN> show all;

using target database controlfile instead of recovery catalog

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 OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # 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 SNAPSHOT CONTROLFILE NAME TO ‘/beta/app/oracle/product/9.0.1/dbs/snapc

f_V901.f’; # default

You can script the configuration you want to use within a run block to change

more then one parameter or just change a single parameter at a time from the

RMAN prompt not using the run block.

run {

# Use the configure commands to create your backup policy.

# When complete these will be the new persistent

# configuration parameters for RMAN in the controlfile

# of the target datbase.

#

CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

CONFIGURE BACKUP OPTIMIZATION OFF;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO

‘/proj/SME9i/backup/%F’;

CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE MAXSETSIZE TO UNLIMITED;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/proj/SME9i/backup/snapf_prod9.f’;

}

RMAN> show all;

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

CONFIGURE BACKUP OPTIMIZATION OFF;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO

‘/proj/SME9i/backup/%F’;

CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE MAXSETSIZE TO UNLIMITED;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/proj/SME9i/backup/snapf_prod9.f’;

4. Create a consistent backup script

Create an RMAN script to generate closed database backups without a recovery

catalog. We set the autocontrolfile backup feature during configuration so

the backup controlfile will become our catalog to avoid the overhead of

maintaining a second database for recovery.

% vi backup_lvl0_cold.rcv

## Consistent backup

#

connect target;

run {

shutdown immedate;

startup mount pfile=/proj/SME9i/prod9/pfile/initprod9.ora;

backup

incremental level 0

database format ‘/proj/SME9i/backup/%d_closed_%U’

tag=prod9_closed_LVL0;

shutdown;

startup pfile=/proj/SME9i/prod9/pfile/initprod9.ora;

}

exit

5. Create a backup using backup_lvl0_cold.rcv.

Make a backup of the target database using the new script. Rerun the loop

script in sqlplus to generate changes and run another backup of the database.

Do this until you have 4 backups of the database.

How many backup records were created for the backup we did?

Let’s look at the controlfile and verify what’s there.

select TYPE, RECORDS_TOTAL, RECORDS_USED

from v$controlfile_record_section

where type like ‘%BACKUP%’;

TYPE RECORDS_TOTAL RECORDS_USED

—————— ————- ————

BACKUP SET 101 3

BACKUP PIECE 204 3

BACKUP DATAFILE 210 9

BACKUP REDOLOG 53 0

BACKUP CORRUPTION 185 0

We have 2 backupsets from the parallelism 2 in the channel persistent

configuration set in step #3. The files to channel algorithm will split

the files across the allocated channels, since there are 2 channels there

will be 2 backupsets that consist of the datafile backups. The 3rd is the

controlfile backup created with “AUTOCONTROLFILE BACKUP” feature.

Use the “by summary” option to list your backups in RMAN.

This shows a short summary of your backupsets.

1 – channel datafile backupset

2 – channel datafile backupset

3 – autocontrolfile backup

RMAN> list backup summary;

List of Backups

===============

Key TY LV S Device Type Completion Time #Pieces #Copies Tag

——- — — – ———– ————— ——- ——- —

1 B F A DISK 20-JUL-01 1 1

2 B F A DISK 20-JUL-01 1 1

3 B F A DISK 20-JUL-01 1 1

The “list backup” command will also show the files associated with the

backupset key. It uses verbose by default.

6. Sample procedure for change generation.

This is a test procedure to generate 5 1M archived logs or just to

generate changes to the database to create blocks for backup. Useful

for incremental backup testing.

— loop.sql

— set echo off

drop table team1;

create table team1

(col1 number, col2 date);

drop sequence team1_seq;

create sequence team1_seq start with 1;

truncate table team1;

begin

for i in 1..100 loop

for i in 1..200 loop

insert into team1

values(team1_seq.nextval, sysdate);

end loop;

commit;

end loop;

end;

/

— end

7. Create a test schema.

Create a schema to generate changes to the database so there will changed

blocks for backup. Then connect as that user and run “loop.sql”. This can

be used to make changes for incremental backups.

SQL> create user team1 identified by team1

2 default tablespace users

3 temporary tablespace temp;

User created.

SQL> grant connect, resource to team1;

Grant succeeded.

SQL> connect team1/team1

Error accessing PRODUCT_USER_PROFILE

Warning: Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

Connected.

SQL> @loop.sql

drop table team1

*

ERROR at line 1:

ORA-00942: table or view does not exist

Table created.

drop sequence team1_seq

*

ERROR at line 1:

ORA-02289: sequence does not exist

Sequence created.

Table truncated.

PL/SQL procedure successfully completed.

SQL> exit

8. Create a new closed database backup of the database.

% rman trace b_prod9_2_closed.log

RMAN> run {

shutdown immediate;

startup mount pfile=/proj/SME9i/prod9/pfile/initprod9.ora;

backup

incremental level 0

database format ‘/proj/SME9i/backup/%d_closed_%U’;

shutdown;

startup pfile=/proj/SME9i/prod9/pfile/initprod9.ora;

}

9. Verify the new backup information in sqlplus and compare with RMAN.

sqlplus “/ as sysdba”

select TYPE, RECORDS_TOTAL, RECORDS_USED

from v$controlfile_record_section

where type like ‘%BACKUP%’;

TYPE RECORDS_TOTAL RECORDS_USED

—————— ————- ————

BACKUP SET 101 6

BACKUP PIECE 204 6

BACKUP DATAFILE 210 18

BACKUP REDOLOG 53 0

BACKUP CORRUPTION 185 0

RMAN> list backup summary;

using target database controlfile instead of recovery catalog

4 B F A DISK 20-JUL-01 1 1

5 B F A DISK 20-JUL-01 1 1

6 B F A DISK 20-JUL-01 1 1

RMAN> list backup by file;

List of Datafile Backups

========================

File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Tag

—- ——- – — – ———- ——— ——- ——- —

1 5 B F A 97763 20-JUL-01 1 1

2 B F A 96266 20-JUL-01 1 1

2 4 B F A 97763 20-JUL-01 1 1

1 B F A 96266 20-JUL-01 1 1

3 4 B F A 97763 20-JUL-01 1 1

1 B F A 96266 20-JUL-01 1 1

4 5 B F A 97763 20-JUL-01 1 1

2 B F A 96266 20-JUL-01 1 1

5 5 B F A 97763 20-JUL-01 1 1

2 B F A 96266 20-JUL-01 1 1

6 4 B F A 97763 20-JUL-01 1 1

1 B F A 96266 20-JUL-01 1 1

7 5 B F A 97763 20-JUL-01 1 1

2 B F A 96266 20-JUL-01 1 1

8 4 B F A 97763 20-JUL-01 1 1

1 B F A 96266 20-JUL-01 1 1

List of Controlfile Backups

===========================

CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Tag

———- ——— ——- – ——- ——- —

97763 20-JUL-01 6 A 1 1

96266 20-JUL-01 3 A 1 1

RMAN> exit

The above shows the controlfile backups are in BS_KEY 3 and 6 after 2 backups.

List by file shows details for which file belongs to which backupset.

10. Accounting for all files in the target.

SQL> column name format a50

SQL> select file#, name from v$datafile;

FILE# NAME

———- ————————————————–

1 /proj/SME9i/prod9/data/system01.dbf

2 /proj/SME9i/prod9/data/undotbs01.dbf

3 /proj/SME9i/prod9/data/cwmlite01.dbf

4 /proj/SME9i/prod9/data/drsys01.dbf

5 /proj/SME9i/prod9/data/example01.dbf

6 /proj/SME9i/prod9/data/indx01.dbf

7 /proj/SME9i/prod9/data/tools01.dbf

8 /proj/SME9i/prod9/data/users01.dbf

8 rows selected.

What and where is file #9?

/proj/SME9i/prod9/data/control01.ctl

The controlfile autotbackup from the controlfile persistent configuration

does a backup of the controlfile when you issue a backup command.

BACKUP DATABASE

BACKUP TABLESPACE

BACKUP DATAFILE

BACKUP ARCHIVELOG

RMAN> report need backup;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 3

Report of files with less than 3 redundant backups

File #bkps Name

—- —– —————————————————–

1 2 /proj/SME9i/prod9/data/system01.dbf

2 2 /proj/SME9i/prod9/data/undotbs01.dbf

3 2 /proj/SME9i/prod9/data/cwmlite01.dbf

4 2 /proj/SME9i/prod9/data/drsys01.dbf

5 2 /proj/SME9i/prod9/data/example01.dbf

6 2 /proj/SME9i/prod9/data/indx01.dbf

7 2 /proj/SME9i/prod9/data/tools01.dbf

8 2 /proj/SME9i/prod9/data/users01.dbf

In sqlplus run the team1 procedure again to generate more changes.

RMAN> @b_prod9_closed.rcv

Here is what I have in my backup directory.

Take a look at what’s in yours.

[otcsol1]/proj/SME9i/backup> ls -g

ls -FC -g

total 1491252

-rw-r—– 1 udba 1511936 Jul 20 10:09 c-2094960375-20010720-00

-rw-r—– 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01

-rw-r—– 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02

-rw-r—– 1 udba 129495552 Jul 20 10:09 PROD9_closed_01cvcpvf_1_1

-rw-r—– 1 udba 122544640 Jul 20 10:09 PROD9_closed_02cvcpvf_1_1

-rw-r—– 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1

-rw-r—– 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1

-rw-r—– 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1

-rw-r—– 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1

-rw-r—– 1 udba 1503232 Jul 20 12:07 snapf_prod9.f

Since the retention policy is redundancy 3, let’s see what needs backup or

if we’ve satisfied the policy.

RMAN> report need backup;

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 3

no obsolete backups found

RMAN>

Nothing to obsolete yet. Only 3 backups.

Run team# loop.sql to generate more changes.

Run sqlplus and verify the control file v$ backup information.

select TYPE, RECORDS_TOTAL, RECORDS_USED

from v$controlfile_record_section

where type like ‘%BACKUP%’;

TYPE RECORDS_TOTAL RECORDS_USED

—————— ————- ————

BACKUP SET 101 12

BACKUP PIECE 204 12

BACKUP DATAFILE 210 36

BACKUP REDOLOG 53 0

BACKUP CORRUPTION 185 0

[otcsol1]/proj/SME9i/backup> ls -g

ls -FC -g

total 1987620

-rw-r—– 1 udba 1511936 Jul 20 10:09 c-2094960375-20010720-00

-rw-r—– 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01

-rw-r—– 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02

-rw-r—– 1 udba 1511936 Jul 20 12:22 c-2094960375-20010720-03

-rw-r—– 1 udba 129495552 Jul 20 10:09 PROD9_closed_01cvcpvf_1_1

-rw-r—– 1 udba 122544640 Jul 20 10:09 PROD9_closed_02cvcpvf_1_1

-rw-r—– 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1

-rw-r—– 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1

-rw-r—– 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1

-rw-r—– 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1

-rw-r—– 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1

-rw-r—– 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1

-rw-r—– 1 udba 1503232 Jul 20 12:22 snapf_prod9.f

SQL> set pagesize 60

SQL> select RECID, STAMP, COMPLETION_TIME, INCREMENTAL_LEVEL from v$backup_set;

RECID STAMP COMPLETION_TIME INCREMENTAL_LEVEL

———- ———- ————— —————–

1 435578964 20-JUL-01 0

2 435578982 20-JUL-01 0

3 435578987 20-JUL-01

4 435583853 20-JUL-01 0

5 435583874 20-JUL-01 0

6 435583877 20-JUL-01

7 435586019 20-JUL-01 0

8 435586027 20-JUL-01 0

9 435586036 20-JUL-01

10 435586934 20-JUL-01 0

11 435586942 20-JUL-01 0

12 435586945 20-JUL-01

12 rows selected.

SQL> set pagesize 60

SQL> column handle format a32

SQL> column tag format a18

SQL> select RECID,SET_STAMP, TAG, STATUS, HANDLE from v$backup_piece

order by SET_STAMP;

RECID SET_STAMP TAG S HANDLE

———- ———- —————— – ——————————–

1 435578863 A /proj/SME9i/backup/PROD9_closed_

01cvcpvf_1_1 <<= DB Backupset 2 435578863 A /proj/SME9i/backup/PROD9_closed_
02cvcpvf_1_1 <<= DB Backupset 3 435578986 A /proj/SME9i/backup/c-2094960375-
20010720-00 <<= Controlfile backup 4 435583785 A /proj/SME9i/backup/PROD9_closed_
04cvcup9_1_1 <<= DB Backupset 5 435583785 A /proj/SME9i/backup/PROD9_closed_
05cvcup9_1_1 <<= DB Backupset 6 435583876 A /proj/SME9i/backup/c-2094960375-
20010720-01 <<= Controlfile backup 7 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
07cvd0ua_1_1 <<= DB Backupset 8 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
08cvd0ua_1_1 <<= DB Backupset 9 435586035 A /proj/SME9i/backup/c-2094960375-
20010720-02 <<= Controlfile backup 10 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
0acvd1ps_1_1 <<= DB Backupset 11 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
0bcvd1ps_1_1 <<= DB Backupset 12 435586944 A /proj/SME9i/backup/c-2094960375-
20010720-03 <<= Controlfile backup
12 rows selected.

What do we have in the backup directory?

What backups obsolete now?

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 3

Report of obsolete backups and copies

Type Key Completion Time Filename/Handle

——————– —— —————— ——————–

Backup Set 1 20-JUL-01

Backup Piece 1 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0

1cvcpvf_1_1

Backup Set 2 20-JUL-01

Backup Piece 2 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0

2cvcpvf_1_1

Backup Set 3 20-JUL-01

Backup Piece 3 20-JUL-01 /proj/SME9i/backup/c-2094960375-2

0010720-00

We now have an obsolete controlfile and 2 bakup sets. These are the 1st backups

we took. Backupsets 1, 2, and 3.

To remove the backupsets that exceed the retention policy set run “delete obsolete”

command and you will be prompted for Y/N to remove the physical backupsets and pieces.

RMAN> delete obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 3

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=9 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=10 devtype=DISK

Deleting the following obsolete backups and copies:

Type Key Completion Time Filename/Handle

——————– —— —————— ——————–

Backup Set 1 20-JUL-01

Backup Piece 1 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0

1cvcpvf_1_1

Backup Set 2 20-JUL-01

Backup Piece 2 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0

2cvcpvf_1_1

Backup Set 3 20-JUL-01

Backup Piece 3 20-JUL-01 /proj/SME9i/backup/c-2094960375-2

0010720-00

Do you really want to delete the above objects (enter YES or NO)? y

deleted backup piece

backup piece handle=/proj/SME9i/backup/PROD9_closed_01cvcpvf_1_1 recid=1 stamp=4

35578863

deleted backup piece

backup piece handle=/proj/SME9i/backup/PROD9_closed_02cvcpvf_1_1 recid=2 stamp=4

35578863

deleted backup piece

backup piece handle=/proj/SME9i/backup/c-2094960375-20010720-00 recid=3 stamp=43

5578987

The backups are removed from the backup directory and the controlfile.

[otcsol1]/proj/SME9i/backup> ls -g

ls -FC -g

total 1492068

-rw-r—– 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01

-rw-r—– 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02

-rw-r—– 1 udba 1511936 Jul 20 12:22 c-2094960375-20010720-03

-rw-r—– 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1

-rw-r—– 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1

-rw-r—– 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1

-rw-r—– 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1

-rw-r—– 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1

-rw-r—– 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1

-rw-r—– 1 udba 1503232 Jul 20 12:22 snapf_prod9.f

There were 13 backup objects before delete obsolete and there are now 10.

The 3 backup sets are removed.

Looking at it in RMAN now:

RMAN> list backup by file;

List of Datafile Backups

========================

File Key TY LV S Ckp SCN
—- ——- –
1 11 B F A 100734
8 B F A 99238
5 B F A 97763
2 10 B F A 100734
7 B F A 99238
4 B F A 97763
3 10 B F A 100734
7 B F A 99238
4 B F A 97763
4 11 B F A 100734
8 B F A 99238
5 B F A 97763
5 11 B F A 100734
8 B F A 99238
5 B F A 97763
6 10 B F A 100734
7 B F A 99238
4 B F A 97763
7 11 B F A 100734
8 B F A 99238
5 B F A 97763
8 10 B F A 100734
7 B F A 99238
4 B F A 97763 Ckp Time #Pieces #Copies Tag
— – ———- ——— ——- ——- —
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1 PROD9_CLOSED_LVL0
20-JUL-01 1 1

List of Controlfile Backups

===========================

CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Tag

———- ——— ——- – ——- ——- —

100734 20-JUL-01 12 A 1 1

99238 20-JUL-01 9 A 1 1

97763 20-JUL-01 6 A 1 1

The records are no longer available (RECID 1, 2, and 3) in RMAN.

set pagesize 60

column handle format a32

column tag format a18

select RECID,SET_STAMP, TAG, STATUS, HANDLE from v$backup_piece

order by SET_STAMP;

RECID SET_STAMP TAG S HANDLE

———- ———- —————— – ——————————–

1 435578863 D /proj/SME9i/backup/PROD9_closed_

01cvcpvf_1_1

2 435578863 D /proj/SME9i/backup/PROD9_closed_

02cvcpvf_1_1

3 435578986 D /proj/SME9i/backup/c-2094960375-

20010720-00

4 435583785 A /proj/SME9i/backup/PROD9_closed_

04cvcup9_1_1

5 435583785 A /proj/SME9i/backup/PROD9_closed_

05cvcup9_1_1

6 435583876 A /proj/SME9i/backup/c-2094960375-

20010720-01

7 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_

07cvd0ua_1_1

8 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_

08cvd0ua_1_1

9 435586035 A /proj/SME9i/backup/c-2094960375-

20010720-02

10 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_

0acvd1ps_1_1

11 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_

0bcvd1ps_1_1

12 435586944 A /proj/SME9i/backup/c-2094960375-

20010720-03

12 rows selected.

select TYPE, RECORDS_TOTAL, RECORDS_USED

from v$controlfile_record_section

where type like ‘%BACKUP%’;

TYPE RECORDS_TOTAL RECORDS_USED

—————— ————- ————

BACKUP SET 101 12

BACKUP PIECE 204 12

BACKUP DATAFILE 210 36

BACKUP REDOLOG 53 0

BACKUP CORRUPTION 185 0

Because we are maintaining the backups using a set backup policy the records_used

will increase/decrease as you manage your backups.

2. Restore of a database w/o a catalog and noachivelog mode.

Restoring using the backups you’ve taken. Now the fun begins. First we’ll remove

the database to simulate our own disaster to recover from.

[otcsol1]/proj/SME9i/prod9/rmanlab> cd ../data

[otcsol1]/proj/SME9i/prod9/data> ls -ltr

total 1320832

-rw-r—– 1 usupport udba 41947136 Jul 12 14:49 temp01.dbf

-rw-r—– 1 usupport udba 1049088 Jul 20 13:59 redo03.log

-rw-r—– 1 usupport udba 1049088 Jul 20 13:59 redo02.log

-rw-r—– 1 usupport udba 26218496 Jul 20 14:00 users01.dbf

-rw-r—– 1 usupport udba 209719296 Jul 20 14:00 undotbs01.dbf

-rw-r—– 1 usupport udba 10489856 Jul 20 14:00 tools01.dbf

-rw-r—– 1 usupport udba 340791296 Jul 20 14:00 system01.dbf

-rw-r—– 1 usupport udba 1049088 Jul 20 14:00 redo01.log

-rw-r—– 1 usupport udba 26218496 Jul 20 14:00 indx01.dbf

-rw-r—– 1 usupport udba 10489856 Jul 20 14:00 example01.dbf

-rw-r—– 1 usupport udba 20975616 Jul 20 14:00 drsys01.dbf

-rw-r—– 1 usupport udba 20975616 Jul 20 14:00 cwmlite01.dbf

-rw-r—– 1 usupport udba 1503232 Jul 20 14:00 control03.ctl

-rw-r—– 1 usupport udba 1503232 Jul 20 14:00 control02.ctl

-rw-r—– 1 usupport udba 1503232 Jul 20 14:00 control01.ctl

[otcsol1]/proj/SME9i/prod9/data> rm *

rm: remove control01.ctl (yes/no)? y

rm: remove control02.ctl (yes/no)? y

rm: remove control03.ctl (yes/no)? y

rm: remove cwmlite01.dbf (yes/no)? y

rm: remove drsys01.dbf (yes/no)? y

rm: remove example01.dbf (yes/no)? y

rm: remove indx01.dbf (yes/no)? y

rm: remove redo01.log (yes/no)? y

rm: remove redo02.log (yes/no)? y

rm: remove redo03.log (yes/no)? y

rm: remove system01.dbf (yes/no)? y

rm: remove temp01.dbf (yes/no)? y

rm: remove tools01.dbf (yes/no)? y

rm: remove undotbs01.dbf (yes/no)? y

rm: remove users01.dbf (yes/no)? y

[otcsol1]/proj/SME9i/prod9/data> y

[otcsol1]/proj/SME9i/prod9/data> ls -la

total 4

drwxr-xr-x 2 usupport udba 512 Jul 20 14:20 ./

drwxr-xr-x 11 usupport udba 512 Jul 19 13:43 ../

OK it’s all gone now and you have to bring it back with RMAN…

Note: Make sure you set the NLS_LANG on the target for the recovery session at the

Unix prompt if the database is not using the US7ASCII characterset. Ex.

whatever your database characterset is you are restoring.

setenv NLS_LANG AMERICAN_AMERICA.WE8ISO8859P1

or

setenv NLS_LANG AMERICAN_AMERICA.UTF8

To recover the database using an autobackup of the control file without a recovery

catalog:

1. Use SQL*Plus to start, but not mount, the database. For example, run:

SQL> STARTUP NOMOUNT

2. Start RMAN but do not connect to the target database:

% rman

RMAN>

3. Set the database identifier for the target database with SET DBID. RMAN

displays the DBID whenever you connect to the target. You can also get it

by running LIST or by querying the catalog (refer to “Restoring When

Multiple Databases Share the same Name: Example”). For example, run:

SET DBID 2094960375;

Note: You can use log files to determine the DBID also. Everytime RMAN connects

to the database the DBID is displayed if the database is open or mounted.

4. Connect to the target database. For example, run:

CONNECT TARGET

5. Restore the backup control file, then perform recovery. Do the following:

a. Optionally, specify the most recent backup time stamp that

RMAN can use when searching for a control file autobackup to restore.

b. If a non-default format was used to create the control file, then

specify a non-default format for the restore of the control file.

c. If the channel that created the control file autobackup was device

type sbt, then you must allocate one or more sbt channels. Because no

repository is available, you cannot use automatic channels. If the

autobackup was created on a disk channel, however, then you do not

need to manually allocate a channel.

d. Restore the autobackup of the control file, optionally setting the

maximum number of days backward that RMAN can search (up to 366) and

the initial sequence number that it should use in its search for the

first day.

e. Mount the database. Note that because the repository is now

available, any automatic channels that you configured are also

available.

f. If the online logs are inaccessible, then restore and recover the

database as described in “Performing Incomplete Restore and

Recovery”. You must terminate recovery by setting the UNTIL clause to

a time, log sequence, or SCN before the online redo logs. If the

online logs are usable, then restore and recover the database as

described in “Performing Complete Restore and Recovery”.

In this example, the online redo logs have been lost. This example limits

the restore of the control file autobackup, then performs recovery of the

database to log sequence 13243, which is the most recent archived log:

# manually allocate one or more channels

RUN

{

SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO

‘/proj/SME9i/backup/%F’;

ALLOCATE CHANNEL d1 DEVICE TYPE disk;

RESTORE CONTROLFILE FROM AUTOBACKUP

MAXSEQ 5 # start at sequence 5 and count down

MAXDAYS 5; # start at UNTIL TIME and search back 5 days

MOUNT DATABASE;

}

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

allocated channel: d1

channel d1: sid=9 devtype=DISK

Starting restore at 20-JUL-01

channel d1, looking for controlfile autobackup on day: 20010720

channel d1, controlfile autobackup found: /proj/SME9i/backup/c-2094960375-200107

20-03

channel d1, controlfile autobackup restore complete

replicating controlfile

input filename=/proj/SME9i/prod9/data/control01.ctl

output filename=/proj/SME9i/prod9/data/control02.ctl

output filename=/proj/SME9i/prod9/data/control03.ctl

Finished restore at 20-JUL-01

database mounted

released channel: d1

r_prod9_db.log:

RMAN> run {

2> restore database;

3> recover database noredo;

4> alter database open resetlogs;

5> }

Backup the database after resetlogs.

A new 3rd autocontrolfile backup is created. We used the old -03 to recover with

so backup -03 never really existed since the current controlfile now would have

been backup -03 of the controlfile.

Starting Control File Autobackup at 20-JUL-01

piece handle=/proj/SME9i/backup/c-2094960375-20010720-03 comment=NONE

Finished Control File Autobackup at 20-JUL-01

[otcsol1]/proj/SME9i/backup> ls -g

ls -FC -g

total 1985460

-rw-r—– 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01

-rw-r—– 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02

-rw-r—– 1 udba 1511936 Jul 20 16:18 c-2094960375-20010720-03

-rw-r—– 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1

-rw-r—– 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1

-rw-r—– 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1

-rw-r—– 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1

-rw-r—– 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1

-rw-r—– 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1

-rw-r—– 1 udba 129909248 Jul 20 16:18 PROD9_closed_0dcvdfjd_1_1

-rw-r—– 1 udba 122544640 Jul 20 16:18 PROD9_closed_0ecvdfjd_1_1

-rw-r—– 1 udba 1503232 Jul 20 12:22 snapf_prod9.f

[otcsol1]/proj/SME9i/backup> ls -g

ls -FC -g

total 992724

-rw-r—– 1 udba 1511936 Jul 20 16:18 c-2094960375-20010720-03

-rw-r—– 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1

-rw-r—– 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1

-rw-r—– 1 udba 129909248 Jul 20 16:18 PROD9_closed_0dcvdfjd_1_1

-rw-r—– 1 udba 122544640 Jul 20 16:18 PROD9_closed_0ecvdfjd_1_1

-rw-r—– 1 udba 1503232 Jul 20 12:22 snapf_prod9.f

RMAN> report need backup;

select TYPE, RECORDS_TOTAL, RECORDS_USED

from v$controlfile_record_section

where type like ‘%BACKUP%’;

TYPE RECORDS_TOTAL RECORDS_USED

—————— ————- ————

BACKUP SET 101 14

BACKUP PIECE 204 14

BACKUP DATAFILE 210 44

BACKUP REDOLOG 53 0

BACKUP CORRUPTION 185 0

Summary:

In this lesson you should have learned how to configure RMAN to use the target

database controlfile as the recovery catalog using autocontrolfile backups. You

also learned how to created a persistent backup configuration for reuse. Then

you created level 0 and level 1 consistent backup using RMAN. After simulating

your own disaster you successfully restored and recovered the database using

the backups taken with RMAN.

Related Posts

Leave a Reply Cancel reply