Oracle in World: 4/27/08

Saturday, May 3, 2008

What Backup Optimization does in RMAN backup

•You can see whether your backup optimization is on or off by issuing following commands,

RMAN> SHOW BACKUP OPTIMIZATION;
RMAN configuration parameters are:
CONFIGURE BACKUP OPTIMIZATION OFF; # default

By default it is OFF. To make it ON issue,

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

Now the question is if backup optimization is on then how RMAN behaves while taking backup. In the following section I will try to make understand.

•In a straight-forward if you enable backup optimization, then the BACKUP command skips backing up files when the identical file has already been backed up to the specified device type. Now question is how RMAN check whether the file is identical or not?

-In case of datafile RMAN compares the DBID, checkpoint SCN, creation SCN, and RESETLOGS SCN and time as a datafile already in a backup. If they are identical then skip to take backup again.

-In case of archived redo log RMAN compares thread, sequence number, and RESETLOGS SCN and time.

-In case of backupset RMAN compares recid and stamp.

Besides these comparison RMAN further checks to determine whether to skip the file, because both the retention policy and the backup duplexing feature are factors in the algorithm that determines whether RMAN has sufficient backups on the specified device type.

•Backup optimization applies to only commands,
-BACKUP DATABASE
-BACKUP ARCHIVELOG with ALL or LIKE options
-BACKUP BACKUPSET ALL

•You can override optimization at any time by specifying the FORCE option on the BACKUP command.
Like,

RMAN>BACKUP DATABASE FORCE;
RMAN>BACKUP DEVICE TYPE sbt BACKUPSET ALL FORCE;

What will be the Archived Redo Log Destination in Oracle

•As a sysdba privilege issue "archive log list" to see your present archival destination settings.

SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

•Based on the destination you can see the physical destination of your archived redo log file in the v$parameter. You can use,

SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata2/flash_recovery_area
db_recovery_file_dest_size big integer 2G

Now a question is for which parameter settings my archived destination affects. In the following I will try to clear the idea.

•If you set LOG_ARCHIVE_DEST parameter then this parameter is used to locate the archived redo log destination.

•If you set DB_RECOVERY_FILE_DEST and and no LOG_ARCHIVE_DEST_n is specified, then LOG_ARCHIVE_DEST_10 is implicitly set to archival destination. However if you set LOG_ARCHIVE_DEST_10 explicitly empty then this behavior changed. Like,

SQL> select DEST_NAME,DESTINATION from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_10';

DEST_NAME DESTINATION
------------------------------ ------------------------------
LOG_ARCHIVE_DEST_10 USE_DB_RECOVERY_FILE_DEST

Though you can implicitly change DB_RECOVERY_FILE_DEST to another destination like LOG_ARCHIVE_DEST_9.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.

SQL> select DEST_NAME,DESTINATION from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_10';
DEST_NAME DESTINATION
------------------------------ ------------------------------
LOG_ARCHIVE_DEST_10

SQL> select DEST_NAME,DESTINATION from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_9';
DEST_NAME DESTINATION
------------------------------ ------------------------------
LOG_ARCHIVE_DEST_9 USE_DB_RECOVERY_FILE_DEST

•If you set any local destinations for LOG_ARCHIVE_DEST_n, then archived redo logs are stored only in the destinations you specify using those parameters. In this case, redo log files are not archived in the flash recovery area by default. Though if you want to archive redo log to flash recover area then use LOCATION=USE_DB_RECOVERY_FILE_DEST as in previous example.

•If you do not set any value for LOG_ARCHIVE_DEST, LOG_ARCHIVE_DEST_n, or DB_RECOVERY_FILE_DEST, then the redo logs are archived to a default location that is platform-specific. On Solaris, for example, the default is ?/dbs/arch.

SQL> alter system set log_archive_dest_9='';
System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination ?/dbs/arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

Related Documents

Startup fails with oracle error ORA-00119, ORA-00132

Recovering After the Loss of Online Redo Log Files

Redo Log File Status

Relocating and Renaming Redo Log Members

ORA-00354 ORA-00353 ORA-00312 Corrupt redo block header

ORA-00313 ,ORA-00312 open failed for members of log group

Mechanism followed by RMAN Restore & Recovery.

•If we simply use RESTORE command then RMAN directs a server session to restore the file to default location that is overwrite the existing file.

•If we use RESTORE command with SET NEWNAME option then RMAN restore the file with specified location.

Example: RUN {
SET NEWNAME FOR DATAFILE '/oradata/datafile/1.dbf' TO '/tmp/1.dbf';
RESTORE DATAFILE '/oradata/datafile/1.dbf';
}

In this case, RMAN restore /oradata/datafile/1.dbf to /tmp/1.dbf
•RMAN always restore data file as image copy.

•After restoring a datafile to a new location if you want to use that then use the SWITCH command in order to update control file. RMAN SWITCH command is equivalent to SQL statement ALTER DATABASE RENAME FILE command.

Example: SWITCH DATAFILE '/oradata/datafile/1.dbf' TO DATAFILECOPY '/tmp/1.dbf';

•RMAN always select the most recent backup while restoring. If two backups are in the same point then RMAN prefers to use image copies over backup sets because RMAN can restore more quickly from image copies than from backup sets.

•During RESTORE operation if RMAN fails the restore one copy then i automatically searches other usable copy. If there is no backup exist then RMAN try to re-create the datafile.

•RMAN uses restore optimization to avoid restoring datafiles from backup when possible. If a datafile is already present in the correct location and its header contains the expected information, then RMAN does not restore the datafile from backup. However you can override this behavior by FORCE option of RESTORE command.

•If RMAN has a choice between archived redo logs and incremental backups then RMAN always choice incremental backups during recovery.If overlapping levels of incremental backups are available then RMAN choose the longest covering one.

Related Documents

How to Discover find DBID

How to skip a tablespace for restore operation

How to Restore the Controlfile from Backup.

Restore and Recover database in Noarchivelog Mode

Restore and Recovery of Individual Tablespaces or Datafiles
How to Restore spfile from backup
How to restore an Spfile from autobackup older than 7 days
Restore Point and Flashback Database.

About RMAN channels

•An RMAN channel corresponds to one server session and it represents one stream of data to a device type.

•When you run a command that requires to use any server session then RMAN automatically allocates channel with the option specified in CONFIGURE command, if you don't explicitly allocate any channel.

•In the settings for which RMAN automatically allocate channel are.

CONFIGURE DEVICE TYPE ... PARALLELISM
CONFIGURE DEFAULT DEVICE TYPE
CONFIGURE CHANNEL

•You can override automatically allocated channel by explicitly specifying ALLOCATE CHANNEL within run block as follows,

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
BACKUP DATABASE;
}

•If you have any three above types of CONFIGURE settings then RMAN automatically allocates channel when you perform BACKUP, RESTORE, DELETE command or command within RUN block.

•The number of channels allocation is done by CONFIGURE DEVICE TYPE ... PARALLELISM is based on parallelism settings. If setttings is
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
then RMAN allocate 3 channels.

•Channel name is defined by ORA_devicetype_n, where

devicetype refers to the user's device type (such as DISK or sbt_tape) and
n refers to the channel number.

The thing need to know that RMAN output always displays sbt_tape whether the input is sbt or sbt_tape. If I have CONFIGURE DEVICE TYPE sbt PARALLELISM 2; then channel 1 will be named as ORA_SBT_TAPE_1 and channel 2 will be named as ORA_SBT_TAPE_2.

•Channel name prefix by ORA_ are reserved for RMAN. You cannot manually allocate a channel with a name that begins with ORA_.

Friday, May 2, 2008

Basic Steps of RMAN media Recovery

Step 1: Place the database in mount stage when you like to perform whole database recovery or open the database and offline the specified tablespace offline when performing tablespace recovery.

Step 2: Use SET UNTIL clause to perform incomplete recovery, that is recovery terminated up to the past time,SCN,restore point or log sequence number. Also you can use UNTIL clause with RESTORE and RECOVER command to perform incomplete recovery.

Step 3: Restore the files with RESTORE command.

Step 4: Recover the data files with RECOVER command.

Step5: Place the database in open stage if you did operation in MOUNT stage , or make the intended tablespace online if you placed tablespaces offline.

Related Documents

About Media Recovery in Oracle.
Block Media Recovery with RMAN ORA-01578: ORA-01110:
User Managed Hot Backup and Recovery of lost of data file

Thursday, May 1, 2008

Backup Retention Policy of RMAN

•Setting the RETENTION POLICY in RMAN keep track about the backup files and tells the report whether they are obsoleted and whether it is needed to backup the database or datafile.

•If you have flash recovery configured then the database automatically deletes unnecessary files from the flash recovery area based on its internal disk quota rules. The disk quota rules are distinct from the backup retention policy rules, but the database will never delete files in violation of the retention policy to satisfy the disk quota.

•If you don't have flash recovery configured then the database does not delete any file even they are obsolete.

•The configuration parameter RETENTION POLICY can be set by CONFIGURE RETENTION POLICY ....

•A backup becomes obsolete based on retention policy, that it is not needed for recovery.A backup becomes expired only when RMAN perform CROSSCHECK and can't find the file. (A most common is file is deleted by OS). Obsolete means "not needed," whereas expired means "not found."

•There is two mutually exclusive options for setting a retention policy; redundancy and recovery window.

To set Recovery Window of 3 days,
RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

To set Recovery Window of redundancy 2 copies,
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

Whenever retention policy is set to recovery window of 3 days then rman retains all information and backup data though which it can go to any point within 3 days from current date. The backup data which is not needed to go back any point within 3 days from current date are termed as obsolete. Suppose before 5 days ago I took an incremental level 0 backup and then I took incremental level 1 backup, now the backup before 5 days ago level 0 backup is not obsolete. Because this is base backup and needed if I want to back any point within 3 days. However, if I took a backup right 4 days ago then backup before 5 days ago become obsolete.

Whenever retention policy is set to redundancy of 2 copies the rman at least retains latest 2 copies of each datafile. If I took 3rd backup of datafile 3 then 1st backup of datafile 3 become obsolete.

•The default retention policy is REDUNDANCY = 1

•You can disable the retention policy by setting,

CONFIGURE RETENTION POLICY TO NONE;

If the retention policy is configured to NONE, then REPORT OBSOLETE and DELETE OBSOLETE do not consider any backups to be obsolete.

Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE

In this topic I will try to make you understand the differences between SELECT ANY DICTIONARY privilege, SELECT ANY TABLE privilege and SELECT_CATALOG_ROLE.

Before proceed it is nice if you remember that ,

•If you have O7_DICTIONARY_ACCESSIBILITY=TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.

•If you have O7_DICTIONARY_ACCESSIBILITY=FALSE then SELECT ANY TABLE privilege provides access only to non-SYS objects.

•If only SELECT_CATALOG_ROLE is enabled then it provides access to all SYS views only.

•If only SELECT ANY DICTIONARY privilege is enabled then it provides access to SYS schema objects only.

•If both SELECT ANY TABLE and SELECT any DICTIONARY privilege is enabled then it allow access to all SYS and non-SYS objects.

•SELECT ANY DICTIONARY privilege and SELECT_CATALOG_ROLE has no affect over O7_DICTIONARY_ACCESSIBILITY settings.

To make the scenario more clear I will demonstrate an example over
1)ARJU schmea table named A. And over two
2)SYS schema objects OBJ$ Table and
3)SYS schema DBA_USERS view.

SQL> select object_type , object_name from dba_objects where object_name in ('OBJ$' ,'DBA_USERS') and owner='SYS';

OBJECT_TYPE OBJECT_NAME
------------------- --------------------
VIEW DBA_USERS
TABLE OBJ$

Workaround Example:
----------------------

A)Secnario 1:(When O7_DICTIONARY_ACCESSIBILITY is set to FALSE)
------------------
SQL> create user t identified by t;
User created.

SQL> grant create session to t;
Grant succeeded.

Have only Create Session Privilege
-------------------------------------
SQL> conn t/t
Connected.

SQL> select * from user_tables;
no rows selected

SQL> select * from arju.a;
select * from arju.a
*
ERROR at line 1:
ORA-00942: table or view does not exist

Have only Select Any Table Privilege
-----------------------------------
SQL> conn arju/a
Connected.

SQL> grant select any table to t;
Grant succeeded.

SQL> conn t/t
Connected.

User T can select Arju schema's obejct but failed on SYS schema objects.

SQL> select count(*) from arju.a;

COUNT(*)
----------
1
SQL> select count(*) from dba_users;
select count(*) from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist

Have select_catalog_role only
---------------------------------

SQL> conn arju/a
Connected.

SQL> revoke select any table from t;
Revoke succeeded.

SQL> grant select_catalog_role to t;
Grant succeeded.

SQL> conn t/t
Connected.

User T can only select SYS schema Views.

SQL> select count(*) from dba_users;
COUNT(*)
----------
23

SQL> select * from arju.t;
select * from arju.t
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist

Have only Select Any Dictionary Privilege
-----------------------------------------------
SQL> conn arju/a
Connected.

SQL> revoke select_catalog_role from t;
Revoke succeeded.

SQL> grant select any dictionary to t;
Grant succeeded.

SQL> conn t/t
Connected.

User T can only select SYS schema objects.
SQL> select count(*) from dba_users;
COUNT(*)
----------
23

SQL> select count(*) from sys.obj$;
COUNT(*)
----------
51053

SQL> select * from arju.a;
select * from arju.a
*
ERROR at line 1:
ORA-00942: table or view does not exist

Have both SELECT ANY TABLE and SELECT ANY DICTIONARY Privilege
---------------------------------------------------------------------
Both system privileges together allow access to all SYS and non-SYS objects.

SQL> grant select any table , select any dictionary to t;
Grant succeeded.

SQL> conn t/t
Connected.

SQL> select count(*) from sys.obj$;
COUNT(*)
----------
51053

SQL> select count(*) from dba_users;
COUNT(*)
----------
23

SQL> select count(*) from arju.a;
COUNT(*)
----------
1

B)Scenario 2:(When O7_DICTIONARY_ACCESSIBILITY is set to TRUE)
----------------------------------------------------------------
Has only SELECT ANY TABLE privilege
-----------------------------------------
User T can now select all SYS and NO-SYS objects.

Related Documents:
---------------------

What is O7_DICTIONARY_ACCESSIBILITY

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Symtompts of the Problem:
---------------------------
Whenever you try to conenct to database by providing SYS user name and password it retuens error.
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Cause of The Problem:
--------------------
This is because of the parameter O7_DICTIONARY_ACCESSIBILITY settings to FALSE.

Access to dictionary objects is restricted to the users with the system privileges SYSDBA and SYSOPER. Connecting as SYSDBA gives a user unrestricted privileges to perform any operation on a database or the objects within a database. Data dictionary objects is under SYS schema and is protected by O7_DICTIONARY_ACCESSIBILITY to FALSE settings.

Workaround Example:
---------------------
1)Try to connect by user sys without sysdba privilege.

SQL> conn sys/a
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
Warning: You are no longer connected to ORACLE.

2)Connect as sysdba and change O7_DICTIONARY_ACCESSIBILITY
SQL> conn / as sysdba
Connected.

SQL> SHOW PARAMETER O7_DICTIONARY_ACCESSIBILITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE

SQL> ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;
System altered.

3)Since O7_DICTIONARY_ACCESSIBILITY is static parameter restart is necessary.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 109051944 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

4)Now connect as sys with only password.

SQL> conn sys/a
Connected.

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE

5)Though you are SYS user but you have not currently have SYSDBA privilege. So, you can't do SYSDBA privilege tasks.

SQL> shutdown immediate;
ORA-01031: insufficient privileges

SQL> show user
USER is "SYS"

Caution:
-----------
Oracle Strongly recommends not to use O7_DICTIONARY_ACCESSIBILITY to TRUE.

Related Documents:
-------------------
What is O7_DICTIONARY_ACCESSIBILITY

ORA-01994: GRANT failed: password file missing or disabled

Symptoms of The Error:
-------------------------
In unix system whenever you tried to grant sysdba privilege to a user it returns with error.

ORA-01994: GRANT failed: password file missing or disabled.

Cause of The Problem:
------------------------
The oracle software owner is not the owner of the passwordfile.

Solutions of The Problem:
----------------------------
1)Log on to Unix box as the same user who owns the file $ORACLE_HOME and create the password file as follows.

$orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password entries=4 force=y

2)Now grant sysdba privilege to the users that you need. Check V$PWFILE_USERS about the entry.

SQL>GRANT SYSDBA to SYSTEM;

3)Check the owner of $ORACLE_HOME/dbs/orapw$ORACLE_SID

This would typically,

SQL> !ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID
-rw-r----- 1 oracle oinstall 1536 Apr 23 16:31 /oracle/app/oracle/product/10.2.0/db_1/dbs/orapwdata1

4)If it is not to dba then change the ownership by

$chown oracle:dba $ORACLE_HOME/dbs/orapw$ORACLE_SID

Also change permission by,

$chmod 4640 $ORACLE_HOME/dbs/orapw$ORACLE_SID

Related Documents:
---------------------
What isRemote Login Passwordfile
Abour Database Authentication

Wednesday, April 30, 2008

A user can do work in his schema with only Create Session Privilege.

A user in his schema can alter any table or select table if he has only CREATE SESSION privilege.

Workaround Example:
--------------------
1.Create one user TEST_USER and I grant CREATE SESSION Privilege.

SQL> create user test_user identified by t;
User created.

SQL> grant create session to test_user;
Grant succeeded.

SQL> alter user test_user quota 5M on users;
User altered.

This quota is only needed to create table for the user test_user. I am creating table in the test_user schema as another user who have create any table privilege.

2.I create a Table TEST_TABLE for TEST_USER.

SQL> create table test_user.test_table ( col1 number);
Table created.

3.Connect to TEST_USER and do operation.

SQL> conn test_user/t
Connected.

SQL> insert into test_user.test_table values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_schema_stats('TEST_USER', dbms_stats.auto_sample_size);
PL/SQL procedure successfully completed.

SQL> alter table test_table add col2 number;
Table altered.

SQL> desc test_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 NUMBER

SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> exec dbms_stats.gather_schema_stats('TEST_USER');
PL/SQL procedure successfully completed.

SQL> insert into test_table values(2,4);
1 row created.

SQL> commit;
Commit complete.

SQL> select num_rows from user_tables where table_name='TEST_TABLE';
NUM_ROWS
----------
1

SQL> exec dbms_stats.gather_schema_stats('TEST_USER');
PL/SQL procedure successfully completed.

SQL> select num_rows from user_tables where table_name='TEST_TABLE';
NUM_ROWS
----------
2

SQL> drop table test_table;
table dropped.

So a user can do everything with the table that resides in his schema.

ORA-30041: Cannot grant quota on the tablespace

Changing a DBA user to a normal user in oracle
Create user in oracle
ORA-01536: space quota exceeded for tablespace

Use of Tag in Backup and Recovery

A tag is a symbolic name for a backup set or image copy. While creating backup you can assign a user-specified character string called a tag to backup sets and image copies and later using this tag name you can restore the backup set or image copy.

•The maximum length of a tag is 30 bytes.

•If you don't assign any tag then RMAN itself assigns tag to a backupset or image copy in the format of TAGYYYYMMDDTHHMMSS, where
YYYY is the year,
MM is the month,
DD is the day,
HH is the hour (in 24-hour format),
MM is theminutes, and
SS is the seconds.

•If multiple backup sets are created by one BACKUP command, then each backup piece is assigned the
same default tag.

•If controlfile autobackup is on then tag is not assigned only that backup piece.

•To use of tag you can simply use TAG tag_name. Like,

RMAN> backup spfile tag test_sp;
RMAN> backup datafile 4 tag test_backup;

•Tags do not need to be unique, so multiple backup sets or image copies can have the same tag. Now if I recall restore command with tag name then most recent intended assigned tag is invoked.

•Here is an example how we can use TAG in restore command.

RMAN> list backup by file;

List of Datafile Backups
========================

File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Compressed Tag
---- ------- - -- - ---------- --------- ------- ------- ---------- ---
4 61 B F A 953430 30-APR-08 1 1 NO TEST_BACKUP
.
.
.

RMAN> sql'alter database datafile 4 offline';

sql statement: alter database datafile 4 offline

RMAN> restore datafile 4 from tag TEST_BACKUP;

Starting restore at 30-APR-08
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /oradata2/data1/data1/users01.dbf
channel ORA_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DATA1/backupset/2008_04_30/o1_mf_nnndf_TEST_BACKUP_41km20xo_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DATA1/backupset/2008_04_30/
o1_mf_nnndf_TEST_BACKUP_41km20xo_.bkp tag=TEST_BACKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 30-APR-08

Why RMAN tool is used to take backup.

We know that backup of a database can be taken through OS command or by using RMAN. Most people want to use RMAN or feel more flexible to use RMAN. The features that make RMAN better than other backup methodology are as follows.

1)RMAN came with oracle and it is free to use.
2)RMAN becomes simpler day by day. The simply BACKUP DATABASE will backup the database.
3)When use RMAN you are 100% sure that database is backed up.
4)With RMAN you can validate a database before backup and also validate a database after taking backup.
5)RMAN maintains a central repository where backup information is kept. So we manually get rid to remember the backup information of database.
6)RMAN is the only tool that supports incremental backup.
7)RMAN improve performance in time while taking backup and restore database.
8)RMAN encrypt data and also use compression of backup.
9)Can take backup parallelly and also restore operation can be done by parallel.
10)Various reports and querying facility through RMAN.
11)No extra redo generated when backup is taken..compared to online backup without RMAN which
results in saving of space in hard disk.
12)RMAN is an intelligent tool which help us to decide various query like which files need to take backup, which backup is obsolete, which backup is expired etc.

RMAN Backup format specification

Whenever we take backup through RMAN then RMAN automatically generates a file name for the backup piece or image copy. However we can override the default file name generation of RMAN by using FORMAT parameter.

FORMAT parameter order of precedence is
1)With backup specification.

Example: Here is use both format with Backup command and after datafile file_number comamnd. But later one i used. In this example I had CONFIGURE .... COPIES .. 2 settings that's why 2 pieces are generated.

RMAN> backup format '/oradata2/%U' datafile 4 format '/oradata2/users_1%U';
Starting backup at 30-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=150 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oradata2/data1/data1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 30-APR-08
channel ORA_DISK_1: finished piece 1 at 30-APR-08 with 2 copies and tag TAG20080430T133252
piece handle=/oradata2/users_12bjf4bc5_1_1 comment=NONE
piece handle=/oradata2/users_12bjf4bc5_1_2 comment=NONE

2) After BACKUP command.

BACKUP FORMAT ...

3)With the ALLOCATE CHANNEL command.

RUN
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/oradata2/a%U'
...
}

4)The CONFIGURE CHANNEL command.

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/?/%U';

Some Syntax:
----------------

%U: It specifies a system-generated unique filename. If no format is used then by default %U is used. The meaning of %U is different for image copies and backup pieces.

For a backup piece, %U specifies a convenient shorthand for %u_%p_%c that guarantees uniqueness in generated backup filenames.

For an image copy of a datafile, %U means the following:

data-D-%d_id-%I_TS-%N_FNO-%f_%u

For an image copy of an archived redo velog, %U means the following:

arch-D_%d-id-%I_S-%e_T-%h_A-%a_%u

For an image copy of a control file, %U means the following:

cf-D_%d-id-%I_%u

%c: It specifies the copy number of the backup piece within a set of duplexed backup pieces. If you did not multiplex a backup(i.e by COPIES parameter), then this variable is 1 for backup sets and 0 for proxy copies.

%d: It specifies the name of the database.

%e Specifies the archived log sequence number.
%f Specifies the absolute file number.
%F Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name. This variable translates into c-IIIIIIIIII-YYYYMMDD-QQ
%h Specifies the archived redo log thread number.
%I Specifies the DBID.
%M Specifies the month in format MM.
%N Specifies the tablespace name.
%n Specifies the name of the database, padded on the right with x characters to a total length of eight characters.
%p Specifies the piece number within the backup set.
%s Specifies the backup set number.
%t Specifies the backup set time stamp
%T Specifies the year, month, and day in the format: YYYYMMDD.
%u Specifies an 8-character name constituted by compressed representations of the backup set or image copy number and the time the backup set or image copy was created.
%Y Specifies the year in this format: YYYY.

Example:
-----------
As today is 30 so whenever I use %D 30 is substituted.

RMAN> backup as copy datafile 4 format '/oradata2/%D%U';
Starting backup at 30-APR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oradata2/data1/data1/users01.dbf
output filename=/oradata2/30data_D-DATA1_I-2547250380_TS-USERS_FNO-4_2fjf4d1k tag=TAG20080430T140124 recid=11 stamp=653407284
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-APR-08

Tuesday, April 29, 2008

How to get back dropped Tablespace using Flashback Database.

It needs to remember that if you drop your tablespace with including datafiles option then it is not possible to get your tablespace back with flashback database. While dropping if you use DROP TABLESPACE INCLUDING CONTENTS option then it is possible to get back tablespace.

Procedures with and Example:
---------------------------------
1)Create a Tablespace and Table inside it.

SQL> create tablespace test_restore datafile '/oradata2/test_restore01.dbf' size 5m;
Tablespace created.

SQL> create table test tablespace test_restore as select level a1 from dual connect by level <99; Table created.

2)Note the SCN and Drop the Tablespace with including contents option.
---------------------------------------------------------------------------
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
938686

SQL> drop tablespace test_restore including contents;
Tablespace dropped.

3)Mount the database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 104857640 bytes
Database Buffers 54525952 bytes
Redo Buffers 6369280 bytes
Database mounted.

4)Perform FlashBack.

SQL> flashback database to scn 938686;
flashback database to scn 938686
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005'

5)The datafile Become Unnamed. So rename it with original data file location.

SQL> alter database rename file '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' to
2 '/oradata2/test_restore01.dbf';
Database altered.

6)Now perforem Flashback and Open the database with read only mode.

SQL> flashback database to scn 938686;
Flashback complete.

SQL> alter database open read only;
Database altered.

SQL> select count(*) from test;
COUNT(*)
----------
98

7)Now you can follow the step 6 choice b)in Peforming Flashback (export) and then recover database. And later create tablespace and import the contents of tablespace.

Related Document:
-------------------
Perform Flashback Database

Limitations of Flashback Database

Create Multiple Duplex backup by one Backup Command

In RMAN you can issue one backup command and there would be several identical copies of your each backup piece.You can produce up to four identical copies of each backup piece in the backup set on different backup destinations. Image copy is not supported.

There are three ways to multiplex of backup sets with RMAN BACKUP command.

A)Specify a default level of duplexing with CONFIGURE... BACKUP COPIES

Example:
----------
1)RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;

2)RMAN> BACKUP DATAFILE 3 format '/oradata2/%U','/oradata2/flash_recovery_area/%U';

Starting backup at 30-APR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=/oradata2/data1/data1/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 30-APR-08
channel ORA_DISK_1: finished piece 1 at 30-APR-08 with 2 copies and tag TAG20080430T103732
piece handle=/oradata2/1njf413c_1_1 comment=NONE
piece handle=/oradata2/flash_recovery_area/1njf413c_1_2 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 30-APR-08

3)LIST BACKUP;
.
.
.
Backup Set Copy #1 of backup set 42
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:34 30-APR-08 NO TAG20080430T103732

List of Backup Pieces for backup set 42 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
38 1 AVAILABLE /oradata2/1njf413c_1_1

Backup Set Copy #2 of backup set 42
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:34 30-APR-08 NO TAG20080430T103732

List of Backup Pieces for backup set 42 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
39 1 AVAILABLE /oradata2/flash_recovery_area/1njf413c_1_2
.
.
.

B)Use SET BACKUP COPIES in a RUN block
----------------------------------------
This will override the default CONFIGURE...BACKUP COPIES setting but will not override if you provide a COPIES option as part of the backup command.

Example:
-----------
RMAN> run{
2> set backup copies 2;
3> backup datafile 4 format '/oradata2/%U','/oradata2/flash_recovery_area/%U';
4> }

executing command: SET BACKUP COPIES

Starting backup at 30-APR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oradata2/data1/data1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 30-APR-08
channel ORA_DISK_1: finished piece 1 at 30-APR-08 with 2 copies and tag TAG20080430T110518
piece handle=/oradata2/1tjf42ne_1_1 comment=NONE
piece handle=/oradata2/flash_recovery_area/1tjf42ne_1_2 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30-APR-08

C)Provide a COPIES option to the BACKUP command
-------------------------------------------------------
You can specify up to 4 values for the FORMAT option.
Example:
-----------
RMAN> run{
2> backup copies 3 datafile 3 format '/oradata2/%U', '/export/home/oracle/%U',
3> '/oradata2/flash_recovery_area/%U';
4> }

ORA-01034: ,ORA-27101: shared memory realm does not exist

Error Description:
-----------------------
Whenever you try to connect to database it returns message,

ORA-01034 : ORACLE not available
ORA-27101 : shared memory realm does not exist

Cause of the Problem:
--------------------------
This problem happens whenever ORACLE_SID or ORACLE_HOME is not properly set. Or for normal users whenever oracle database is not started. For remote users there may be a problem in listener.

Solution of the Problem:
----------------------------
A)For Local Connections:
----------------------------
1)Verify the ORACLE_SID and ORACLE_HOME is set properly. You can check the variable in UNIX by,

SQL> !echo $ORACLE_SID
data1

SQL> !echo $ORACLE_HOME
/oracle/app/oracle/product/10.2.0/db_1

If it is set incorrectly then set on UNIX by

$ export ORACLE_SID=db_name_here (on ksh,sh)
$ setenv ORACLE_SID=db_name_here (on csh)

Remember that ORACLE_SID is case sensitive on UNIX.

2)Verify the database instance is running.

On UNIX you can verify by,
SQL>!ps -ef |grep smon

It will return a row as ora_smon_your_database_name

If it does not return such row then your database is not started. So, start it after setting proper sid.

$sqlplus / as sysdba
$startup

On windows system press CTRL+ALT+DEL and see the process and look for oracle.exe. If it is not present there then from right click my computer>select manage>services>and select oracle database service and start it.
B)For Remote Connections:
-------------------------------
1)Verify the ORACLE_HOME is set correctly for all listed Oracle databases. If the ORACLE_HOME points to the different oracle version software, but the database was created using a different version, then this error can occur.

2)Review the listener.ora file and it is properly set. Like if you set ORACLE_HOME path inside listener ended by slash(/) then the error will come.

Incorrect:
(ORACLE_HOME = /oracle/app/oracle/product/10.2.0/)
Correct:
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)

3)If listener.ora use SID then verify that you have set properly ORACLE_SID. Also be sure about listener services by issuing lsnrctl services.

4)Verify the database is running on server machine while you connect as normal user.

Other links
http://arjudba.blogspot.com/2008/07/database-startup-fails-with-error-ora.html
http://arjudba.blogspot.com/2008/05/startup-fails-with-oracle-error-ora.html
http://arjudba.blogspot.com/2008/05/database-startup-fails-with-errors-ora.html

About Issuing RMAN Commands

RMAN commands can be entered at RMAN prompt or from OS command line.

For example you can schedule your backup through OS job control utility. You can create a text file and will write your RMAN command in it and invoke like,

$rman TARGET / CMDFILE=commandfile.txt LOG outfile.txt

CMDFILE is same as @

That is, rman target / cmdfile=/export/home/oracle/1.txt
and rman target / @/export/home/oracle/1.txt equivalent.
Directing RMAN Output to a Log File
-------------------------------------
When you run RMAN in command line mode, it sends the output to the terminal. If you specify the LOG option, then RMAN writes the output to a specified log file instead.

If you do not specify this argument, then RMAN writes its message log file to standard output. The RMAN output is also stored in the V$RMAN_OUTPUT view (a memory-only view for jobs in progress) and in V$RMAN_STATUS (a control file view for completed jobs and jobs in progress).

Suppose if I use,
rman target / @/export/home/oracle/1.txt log=/export/home/oracle/2.txt
or,
rman target / cmdfile=/export/home/oracle/1.txt log=/export/home/oracle/2.txt
Then rman will not show output in command line rather it will write output to a file 2.txt.

Commands Valid Only in RUN Blocks
--------------------------------------------
The command ALLOCATE CHANNEL and SET NEWNAME FOR DATAFILE is only valid within RUN block.

Commands Not Valid in RUN Blocks
-------------------------------
CONNECT
CONFIGURE
CREATE CATALOG, DROP CATALOG, UPGRADE CATALOG
CREATE SCRIPT, DELETE SCRIPT, REPLACE SCRIPT
LIST
REPORT

Stored Scripts
----------------
A stored script is a block of RMAN job commands that is stored in the recovery catalog. The contents of a stored script are executed within a RUN block.

Related Documents

Set up a Database for RMAN Backup

Interact with RMAN Client.

The LIST, REPORT, and DELETE Commands in RMAN

Issues between RMAN Client and Database

Monday, April 28, 2008

How to restore the old data using flashback query

About Oracle Database Incarnations

Whenever you open the database with the command ALTER DATABASE OPEN RESETLOGS , a new incarnation is created. Performing an OPEN RESETLOGS do the followind,

-Archives the current online redo logs,

-Incarnation resets the log sequence number to 1, and then

-Gives the online redo logs a new time stamp and SCN.

-Increments the incarnation number, which is used to uniquely tag and identify a stream of redo.

Parent, Ancestor and Sibling Database Incarnations
------------------------------------------------------
•The incarnation from which the current incarnation branched following an OPEN RESETLOGS operation is called the parent incarnation of the current incarnation.

•The parent incarnation and all of its parent incarnations are the ancestor incarnations of the current incarnation.

•Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.

•To view the incarnation history of a database use the RMAN>LIST INCARNATION; commnad.

•By default, when used command like FLASHBACK DATABASE or RECOVER... UNTIL, an SCN is assumed to refer to the current incarnation path, rather than sibling incarnations.

•However, you can use the RESET DATABASE TO INCARNATION command to specify that SCNs are to be interpreted in the frame of reference of another incarnation.

•For example my current database INCARNATION is 5 and now I have used
FLASHBACK DATABASE TO SCN 6666;
then SCN 6666 will be search in current incarnation which is 5. However if I want to get back to SCN 6666 of INCARNATION 4 then I have to use,

RMAN> RESET DATABASE TO INCARNATION 4;
RMAN> RECOVER DATABASE TO SCN 6666;

•When a database goes through multiple incarnations, some backups can become orphaned. Orphaned backups are backups that are created during incarnations of the database that are not ancestors of the current incarnation.

•For example, database incarnation is 5 and SCN is 7000. A DBPITR is done at SCN 7000 to SCN 6666 and then RESETLOGS is performed. Now in current incarnation 6 the backup taken between 6666 and 7000 SCN became orphaned.

•Orphaned backups are usable by RMAN in cases where you wish to restore the database to a point in time not in the current incarnation path.

Sunday, April 27, 2008

How to Change the iSQL*Plus Port

1)Stop the iSQL*Plus.

bash-3.00$ isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle. All rights reserved.
iSQL*Plus instance on port 5560 is not running ...

2)Take a backup of the files,

$ORACLE_HOME/host_sid/sysman/config/emoms.properties
$ORACLE_HOME/oc4j/j2ee/isqlplus/config/http-web-site.xml

3)Modify the following parameters in the file $ORACLE_HOME/host_sid/sysman/config/emoms.properties

In my computer hostname is neptune and database is data1, so I edited,/oracle/app/oracle/product/10.2.0/db_1/neptune_data1/sysman/config/emoms.properties

oracle.sysman.db.isqlplusUrl=http\://host.domain\:5561/isqlplus/dynamic
oracle.sysman.db.isqlplusWebDBAUrl=http\://host.domain\:5561/isqlplus/

And,
/oracle/app/oracle/product/10.2.0/db_1/oc4j/j2ee/isqlplus/config/http-web-site.xml

4)Start the iSql*Plus server.

bash-3.00$ isqlplusctl start
And enter new post number to your browser like,
http://neptune:5561/isqlplus/

How to Change DB Control Http Port.

1)Stop the dbconsole.

bash-3.00$ emctl stop dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://neptune:5500/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
... Stopped.

2)Take a backup of files:

$ORACLE_HOME/host_sid/sysman/config/emoms.properties
$ORACLE_HOME/host_sid/sysman/config/emd.properties
$ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_host_sid/config/http-web-site.xml

3)Edit these three files. Suppose My hostname is neptune and sid is data1 and I like to change it to port 510. To do this following is the procedures. You can either edit files directly or you can use command line utility to change the port.

Editing directly inside files
In /oracle/app/oracle/product/10.2.0/db_1/neptune_data1/sysman/config/emoms.properties

oracle.sysman.emSDK.svlt.ConsoleServerPort=5510
oracle.sysman.emSDK.svlt.ConsoleServerHTTPSPort=5510

In /oracle/app/oracle/product/10.2.0/db_1/neptune_data1/sysman/config/emd.properties

REPOSITORY_URL=http://host.domain:5510/em/upload/
emdWalletSrcUrl=http://host.domain:5510/em/wallets/emd

In /oracle/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_neptune_data1/config/http-web-site.xml

<web-site port="5510" ...>

Command line Options to change DB Control Http Port
To change the Oracle Enterprise Management Agent HTTP port,
$ emca -reconfig ports -AGENT_PORT 1851

To change the Oracle Enterprise Manager Database Control ports,
$ emca -reconfig ports -DBCONTROL_HTTP_PORT 5510

To change the Oracle Enterprise Manager RMI_PORT port,
$ emca -reconfig ports -RMI_PORT 5520

To change the Oracle Enterprise Manager JMS_PORT port,
$ emca -reconfig ports -JMS_PORT 5521

You can change all these by single command like,
$ emca -reconfig ports -DBCONTROL_HTTP_PORT 5510 -AGENT_PORT 1851 -RMI_PORT 5520

Related Document
EMCA fails with ORA-06502 PL/SQL: numeric or value error: character string buffer too small
EM Daemon is not running
Authentication failed!null
How to Change DB Control Http Port
Login to Dbconsole, Authentication failed!null Returned
An Stack of Problems while creating Repository using emca
Running EMCA Fails To Accept the DBSNMP Password
How to Access Database using Database Control
How To Drop, Create And Recreate DB Control In 10g Database
Troubleshooting DbConsole Error - OC4J Configuration issue
Java.lang.Exception: IOException in sending Request

Limitation of Flashback Database.

The Flashback Database allows you to get back the entire database to a specific point-in-time.
It is best used as a replacement for incomplete recovery of a complete database. The main benefit of the Oracle Flashback Database over incomplete database recovery is that the Flashback Database is much quicker and more efficient. The Flashback Database is based on flashback logs.

It has some limitations such as,

•Flashback Database can only undo changes to a datafile made by an Oracle database. If any media failure occurs in database then flashback database can't be used. That means if a datafile is dropped then we can't use flashback feature.

•If you have shrink your any datafile then flashback database can't be used.

•If the database control file is restored from backup or re-created, then you cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.

•If you get back to a time by flashback database when nologging operation was running then it is likely happen block corruption on database.

Flashback Database To The Right of Open Resetlogs

In some cases, you may need to return the database to a point in time in the parent incarnation, later than the SCN of the OPEN RESETLOGS at which the current incarnation path branched from the old incarnation.

The process is described below.

1)Verify that the flashback logs contain enough information to flash back.

SQL> select oldest_flashback_scn from v$flashback_database_log;

2)Determine the target incarnation number for the flashback, that is, the incarnation key for the parent incarnation.

SQL> select prior_incarnation# from v$database_incarnation where status ='CURRENT';

3)Start the database in mount stage.

RMAN>SHUTDOWN IMMEDIATE;
RMAN>START THE MOUNT;

4)Issue List Incarnation to see the SCN.

RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DATA1 2547250380 PARENT 1 17-MAR-06
2 2 DATA1 2547250380 PARENT 526290 23-APR-08
3 3 DATA1 2547250380 ORPHAN 789429 27-APR-08
4 4 DATA1 2547250380 PARENT 789429 28-APR-08
5 5 DATA1 2547250380 ORPHAN 792942 28-APR-08
6 6 DATA1 2547250380 PARENT 792942 28-APR-08
7 7 DATA1 2547250380 CURRENT 793335 28-APR-08

5)Re-check the OLDEST_FLASHBACK_SCN again.

SQL> select oldest_flashback_scn from v$flashback_database_log;
OLDEST_FLASHBACK_SCN
--------------------
786801

6)Set the database incarnation to the parent incarnation:

RMAN> RESET DATABASE TO INCARNATION 6;

7)Run the FLASHBACK DATABASE command:

RMAN> flashback database to scn 792942;

8)Open the database read only and see the contents. Either OPEN it with RESETLOGS option or export/import data and RECOVER DATABASE.

Flashback Database to Undo an OPEN RESETLOGS

Suppose you have opened your database with OPEN RESETLOGS option after performing flashback feature. And after that you have made unwanted changes in your database.

So, now you want to get your database prior to RESETLOGS option. Then you have to do following.

1)Verify the flashback window:

Before performing the flashback, verify that the beginning of the flashback window is earlier than the time of the most recent OPEN RESETLOGS.

sql> select resetlogs_change# from v$database;
sql> select oldest_flashback_scn from v$flashback_database_log;

If V$DATABASE.RESETLOGS_CHANGE# is greater than V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN, then you can use Flashback Database to reverse the OPEN RESETLOGS.

2)If step 1 is ok then shutdown the database and mount it. Now check the step 1 again. If the target SCN is prior to the beginning of the flashback database window, an error is returned and the database is not modified.

3)Perform the Operation:

SQL> FLASHBACK DATABASE TO BEFORE RESETLOGS;

4)Open the database with read only and check your desired obejcts.

SQL> alter database open read only;

5)Follow step 6 of Step 6

Performing Flashback Database

Before going any production upgrade to database we can make a guaranteed restore point to database and if any wrong then we can get back to the restore point state. Guaranteed restore point always ensure that we can get back data to our restore point creation time.

Suppose before upgradation to database I have made an guaranteed restore point to database like,

SQL> create restore point before_upgrade guarantee flashback database;
Restore point created.

SQL> conn arju/a
Connected.

SQL> select count(*) from user_tables;
COUNT(*)
----------
4

SQL> create table after_restore_point (col1 number);
Table created.

SQL> desc after_restore_point;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER

Now I want to perform flashback database to get back database at the time of creating restore point. To do it follow the following steps,

1. Determine the desired SCN, restore point or point in time for the FLASHBACK DATABASE command.

SQL> select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;

NAME SCN GUA DATABASE_INCARNATION#
------------------------------ ---------- --- ---------------------
BEFORE_UPGRADE 787027 YES 2

2.Shutdown the database cleanly and mount it.

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;

3.Determine the current window for flashback database.

When flashback logging is enabled, the earliest SCN in the flashback database window can be determined by querying V$FLASHBACK_DATABASE_LOG.

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;

If you attempt FLASHBACK DATABASE and your target SCN is now outside the flashback window, then FLASHBACK DATABASE will fail with an ORA-38729 error.

So, you can't get back to state to an SCN before OLDEST_FLASHBACK_SCN.

4.Run your RMAN flashback command your restore point or to the desired SCN or to your desired time.

In this case I ran,

SQL> FLASHBACK DATABASE TO RESTORE POINT before_upgrade;
Flashback complete.

You can also use TO SCN Clause and TO TIMESTAMP Clause with FLASHBACK DATABASE.

5. You can verify that you have returned the database to the desired state, by opening the
database read-only and performing some queries to inspect the database contents.Like I perform some operations,

SQL> alter database open read only;
Database altered.

SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY

SQL> conn arju/a
Connected.
SQL> select count(*) from tabs;

COUNT(*)
----------
4

SQL> desc after_restore_point;
ERROR:
ORA-04043: object after_restore_point does not exist

6) If you are satisfied with the state of your database about flashback then you have two choices.

Choice a) Make the database available for updates by open resetlogs, Once you perform this OPEN RESETLOGS operation, all changes to the database after the target SCN for FLASHBACK DATABASE are abandoned.

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>alter database open RESETLOGS;

Choice b) Use the export/data pump export utility to export the objects whose state was corrupted. Then, recover the database to the present time:

RMAN> RECOVER DATABASE;

This step undoes the effect of the Flashback Database, by re-applying all changes in the redo logs to the database, returning it to the most recent SCN.

After re-opening the database read-write, you can import the exported objects using the import utility corresponding to the export utility used earlier.

Identifying Datafiles Requiring Media Recovery

1)Querying the V$DATAFILE_HEADER view.

COL FILE# FORMAT 99
COL STATUS FORMAT A7
COL ERROR FORMAT A15
COL TABLESPACE_NAME FORMAT A10
COL NAME FORMAT A30
SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

FILE# STATUS ERROR REC TABLESPACE NAME
----- ------- --------------- --- ---------- ------------------------------
6 OFFLINE YES TEST_RC /oradata2/3.dbf

The column RECOVER indicates whether it is needed media recovery or not.

If ERROR is not NULL, then the datafile header cannot be read and validated.

If the ERROR column is NULL and the RECOVER column is YES, then the file requires media recovery (and may also require a restore from backup).

2)Querying the V$RECOVER_FILE view.

SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME
FROM V$RECOVER_FILE;
FILE# ERROR ONLINE_ CHANGE# TIME
----- ---------- ------- ---------- ---------
15 OFFLINE 103059069 15-APR-08

3)To know datafile and Tablespace specifically,

COL DF# FORMAT 999
COL DF_NAME FORMAT A35
COL TBSP_NAME FORMAT A7
COL STATUS FORMAT A7
COL ERROR FORMAT A10
COL CHANGE# FORMAT 9999999999
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#;

DF# DF_NAME TBSP_NA STATUS ERROR CHANGE# TIME
---- ----------------------------------- ------- ------- ---------- ----------- ---------
15 /oradata1/streams/tbs/streams_tbs.d STREAMS RECOVER 103059069 15-APR-08
bf _TBS

Restore and Recovery of Individual Tablespaces or Datafiles

In this mode some of the datafiles are damaged or lost. Now you can take the corresponding tablespace offline and perform restore and recover of the specified tablespaces.

Procedures:
--------------
Here I have deleted users01.dbf datafile from OS. Now to restore and recover the datafile you can keep your database mount or in open mode do the following.

1)Make offline of the affected tablespace.

RMAN> sql 'alter tablespace users offline immediate';
sql statement: alter tablespace users offline immediate

2)Restore Tablespace.

RMAN> restore tablespace users;
Starting restore at 27-APR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /oradata2/data1/data1/users01.dbf
channel ORA_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DATA1/backupset/2008_04_27/o1_mf_nnndf_TAG20080427T130713_419dp1z4_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DATA1/backupset/2008_04_27/o1_mf_nnndf_TAG20080427T130713_419dp1z4_.bkp tag=TAG20080427T130713
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 27-APR-08

3)Recover Tablespace.

RMAN> recover tablespace users;
Starting recover at 27-APR-08
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-APR-08

4)Make the tablespace online.

RMAN> sql'alter tablespace users online';
sql statement: alter tablespace users online

Related Documents

How to Restore spfile from backup
How to restore an Spfile from autobackup older than 7 days
Mechanism followed by RMAN Restore & Recovery.
Restore Point and Flashback Database.

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle

Saturday, May 3, 2008

Friday, May 2, 2008

Thursday, May 1, 2008

Wednesday, April 30, 2008

Tuesday, April 29, 2008

Monday, April 28, 2008

Sunday, April 27, 2008

Tag Cloud

Blog Archive

Which Oracle database version you use?