Oracle9i Lab 1 , but is applicable for higher RDBMS versions aswell
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 ...];
show_operand: RETENTION POLICY |
EXCLUDE |
BACKUP COPIES |
CHANNEL |
DEFAULT DEVICE TYPE |
DEVICE TYPE |
SNAPSHOT CONTROLFILE |
...
ALL
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 database.
#
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
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
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 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
---- ----- -----------------------------------------------------
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?
[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
[otcsol1]/proj/SME9i/backup>
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 Ckp Time #Pieces #Copies Tag
---- ------- - -- - ---------- --------- ------- ------- ---
1 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
5 B F A 97763 20-JUL-01 1 1
2 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
4 B F A 97763 20-JUL-01 1 1
3 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
4 B F A 97763 20-JUL-01 1 1
4 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
5 B F A 97763 20-JUL-01 1 1
5 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
5 B F A 97763 20-JUL-01 1 1
6 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
4 B F A 97763 20-JUL-01 1 1
7 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
5 B F A 97763 20-JUL-01 1 1
8 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
4 B F A 97763 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;
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
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.
top of page
Search
Recent Posts
See AllOracle Error: ORA-1157 An ORA-01157 is issued whenever Oracle attempts to access a file but cannot find or lock the file. Error...
All Oracle support Analysts, DBAs and Consultants who have a role to play in recovering an Oracle database Loss due to Disk Failure...
BACKUP SCENARIOS a) Consistent backups A consistent backup means that all data files and control files are consistent to a point in...
bottom of page
Comments