RMAN Recipe Note 1

There are two types of restore points—normal and guaranteed

                 create restore point rp1;

 flashback database to rp1;

                 create guaranteed restore point rp1;

----------------------------------------------------

The data dictionary view V$RECOVERY_FILE_DEST shows the sum of various types of files in the flash recovery area in terms of percentages of the total space. It has only one row. Here is an example of how you can use the view:
                 SQL>select * from v$recovery_file_dest;

                 SQL> select * from v$flash_recovery_area_usage;

----------------------------------------------------

select
file_type,
space_used*percent_space_used/100/1024/1024 used,
space_reclaimable*percent_space_reclaimable/100/1024/1024 reclaimable,
frau.number_of_files
from v$recovery_file_dest rfd, v$flash_recovery_area_usage frau;
FILE_TYPE USED RECLAIMABLE NUMBER_OF_FILES

CONTROLFILE .00 .00 0
ONLINELOG .00 .00 0
ARCHIVELOG 664.86 547.20 34

 

--------------------------------------------------------------------

 

SQL> alter system set db_recovery_file_dest_size = 2G;

 

---------------------------------------------------------------------

 

RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies

RMAN> delete obsolete;

RMAN> delete noprompt obsolete;

 

-----------------------------------------------------------------------------

 

The default location for archived redo
logs is $ORACLE_HOME/dbs. Of course, you can always configure a specific location by executing
the command alter system set log_archive_dest_1.

 

-------------------------------------------------------------------------------

 

Here are the steps to follow to send archived redo logs to the flash recovery area:
1. Configure the flash recovery area with adequate space (recipe 3-1).
2. If the flash recovery area is already defined, then make sure you have enough space to
hold at least one archived log (recipe 3-4).
3. Log on to the database as a user with the sysdba privilege (such as sys), and issue the
following command:
alter system set log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST';
This command instructs the database to use the flash recovery area as the destination
for archived redo logs.
4. Make sure the archived redo log destination 1 is enabled. By default it’s enabled, but
someone may have disabled it. Issue the following SQL:
SQL>show parameter log_archive_dest_state_1
NAME TYPE VALUE
------------------------------------ ----------- -------------
log_archive_dest_state_1 string ENABLE
The presence of ENABLE confirms that the destination is enabled.

 

5. If the destination is not enabled, enable it now by issuing this:
alter system set log_archive_dest_state_1 = enable;
6. Check the correct setting by issuing an archive log list command at the SQL
prompt:
SQL>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 47
Next log sequence to archive 49
Current log sequence 49
Note the line Archive destination USE_DB_RECOVERY_FILE_DEST, which confirms that
the archived redo log destination is set to the flash recovery area.
7. Check the operation by issuing a log switch that forces the generation of an archived
redo log:
alter system switch logfile;

 

SQL>select name from v$archived_log
2 order by completion_time;
NAME
------------------------------------------------------------------------------

Using the single command backup recovery area (recipe 3-15), you can back up everything,
including archived redo logs, to tape at once.

 

----------------------------------------------------------------------------

 

SQL> select db_unique_name
2 from v$database;
DB_UNIQUE_NAME
------------------------------
PRODB2

 

you must use different unique names.
Unfortunately, you can’t change this dynamically. You have to put the following parameter in
the initialization file and restart the database:
db_unique_name = <Unique Name of the Database>

-------------------------------------------------------------------------------

 

After the database is created, confirm the creation of online redo log files by selecting
the member names from the data dictionary view V$LOGFILE:
SQL> select member
2 from v$logfile
3 where group# = 1

 

alter database add logfile;

-----------------------------------------------------------------------------------

 

RMAN> list backupset;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
157 Full 7.14M DISK 00:00:01 14-OCT-06
BP Key: 151 Status: AVAILABLE Compressed: NO Tag:➥
TAG20061014T233415
Piece Name: /home/oracle/flasharea/PRODB2/autobackup/2006_10_14/➥
o1_mf_n_6038
48055_2m3c1r1s_.bkp
Control File Included: Ckp SCN: 1909037 Ckp time: 14-OCT-06
2. Now, to delete a backup set, let’s say number 157, issue a delete backupset command:

RMAN> delete backupset 157;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK

-----------------------------------------------------------------------------------------------------------------------

 

1. First, find out the archived redo logs to delete. List all archived redo logs like so:
RMAN> list archivelog all;
Here is the output:
using target database control file instead of recovery catalog
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
70 1 46 A 09-OCT-06 /tmp/1_46_599877236.dbf
102 1 78 A 09-NOV-06 /home/oracle/flasharea/➥
PROD2/archivelog/2006_11_10/o1_mf_1_78_2obh633f_.arc
103 1 79 A 10-NOV-06 /home/oracle/flasharea/PROD2/➥
archivelog/2006_11_12/
o1_mf_1_79_2ofdltnv_.arc
2. To delete the archived log sequences 78 and 79, you can use the following commands:
RMAN> delete archivelog from logseq=78 until logseq=79;
The output comes back as follows:
allocated channel: ORA_DISK_1

 

---------------------------------------------------------------------------------------------------------------------------------------

RMAN> report schema
2> ;

Report of database schema for database with db_unique_name SCPRD

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    500      SYSTEM               ***     /opt/oradata/SCPRD/SCPRD_system01.dbf
2    200      SYSAUX               ***     /opt/oradata/SCPRD/SCPRD_sysaux01.dbf
3    1395     UNDO                 ***     /opt/oradata/SCPRD/SCPRD_undo1.dbf
4    30       USERS                ***     /opt/oradata/SCPRD/SCPRD_users01.dbf
5    30       TOOLS                ***     /opt/oradata/SCPRD/SCPRD_tools01.dbf
6    500      SCE_COMMON           ***     /opt/oradata/SCPRD/common/sce_common_01.dbf
7    200      SCE_COMPONENT        ***     /opt/oradata/SCPRD/component/sce_component_01.dbf
8    500      WMWHSE1_DATA         ***     /opt/oradata/SCPRD/wmwhse1/wmwhse1_01.dbf
9    500      WMWHSE2_DATA         ***     /opt/oradata/SCPRD/wmwhse2/wmwhse2_01.dbf
10   500      WMWHSE3_DATA         ***     /opt/oradata/SCPRD/wmwhse3/wmwhse3_01.dbf
11   500      WMWHSE4_DATA         ***     /opt/oradata/SCPRD/wmwhse4/wmwhse4_01.dbf
12   500      WMWHSE5_DATA         ***     /opt/oradata/SCPRD/wmwhse5/wmwhse5_01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    200      TEMP                 200         /opt/oradata/SCPRD/SCPRD_temp01.dbf
2    100      SCE_COMMON_TMP       32767       /opt/oradata/SCPRD/common/sce_common_tmp01.dbf
3    100      SCE_COMPONENT_TMP    32767       /opt/oradata/SCPRD/component/sce_component_tmp01.dbf
4    200      WMWHSE1_TEMPDATA     32767       /opt/oradata/SCPRD/wmwhse1/wmwhse1_tmp01.dbf
5    200      WMWHSE2_TEMPDATA     32767       /opt/oradata/SCPRD/wmwhse2/wmwhse2_tmp01.dbf
6    200      WMWHSE3_TEMPDATA     32767       /opt/oradata/SCPRD/wmwhse3/wmwhse3_tmp01.dbf

------------------------------------------------------------------------------------------------------------------------------------------------

RMAN> list copy of datafile 5;

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
133 5 A 11-NOV-06 3374753 11-NOV-06 /home/oracle/➥
flasharea/PROD2/datafile/o1_mf_example_2obvkx07_.dbf
As you can see from the output, there is an image copy of the damaged file in the flash
recovery area (o1_mf_example_2obvkx07_.dbf).
3. Take the damaged datafile offline, if not offline already:
RMAN> sql 'alter database datafile 5 offline';
sql statement: alter database datafile 5 offline

----------------------------------------------------------------------

 

4. Now, instruct the database to make the copy of the file in the flash recovery area, the
production datafile:
RMAN> switch datafile 5 to copy;
datafile 5 switched to datafile copy "/home/oracle/flasharea/PROD2/datafile/➥
o1_mf_example_2obvkx07_.dbf"
5. Recover the copy to make it consistent with the current state of the database:
RMAN> recover datafile 5;
Starting recover at 12-NOV-06
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:06

----------------------------------------------------------------------------