Oracle in World: April 2008

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.

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

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

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.

Saturday, April 26, 2008

Restore and Recovery of Whole Database. (All datafile damaged but controlfile ok)

Scenario:
-------------
1)You have a current control file and SPFILE but all datafiles are damaged or lost. Now you must restore and recover the whole database.

Before proceed you have to ensure that,

■ The current control file is intact.

■ You have the complete set of archived logs and incremental backups needed for media recovery of your available datafile backups.

■ For any datafiles for which you have no backup, you must have a complete set of online and archived redo logs going back to the creation of that datafile. (With a complete set of redo logs, RMAN can re-create a datafile for which there is no backup, by creating an empty datafile and then re-applying all changes since the file was created as part of the recovery process.)

In this example, I have taken backup and then I dropped the datafiles using OS command. But I have controlfile. Now I am restore and recover the full database.

Procedures:
-----------------
1)I have taken full database rman backup and then I delete some datafiles using OS command.

2)Now whenever I issue command I will show error file can't be open. Like,

SQL> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/oradata2/data1/data1/system01.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3

3)Do a shutdown abort and conenct to rman.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> !rman target /

4)From rman mount the database as controlfile is intact so no problem.

RMAN> startup mount;

5)Issue list backup to see available backup and you need to restore database and then recover database.

RMAN> list backup;
RMAN> restore database;
RMAN> recover database;

6)If it successful then open the database.

RMAN>alter database open;

Requirements for Using Guaranteed Restore Points

1)The COMPATIBLE initialization parameter must be set to 10.2 or greater. You can issue

SQL>SHOW PARAMETER COMPATIBLE;

to see the current compatible settings.

2)The database must be running in ARCHIVELOG mode. Issue SQL>ARCHIVE LOG LIST; to see the mode.

3)A flash recovery area must be configured. To configure it look at Configure Flash Recovery Area

4)If flashback database is not enabled, then the database must be mounted, not open,when creating the first guaranteed restore point (or if all previously created guaranteed restore points have been dropped).

ORA-00439: feature not enabled: Flashback Database

Error Description:
-------------------
While trying to enable flashback feature the error comes.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

Error Explanation:
--------------------

Some feature is is not enabled in oracle database standard edition or Oracle database Standard Edition One. The most common feature is Real Application Clusters, Online index maintenance, Online table organization, Online table redefinition, Block-level media recovery,Parallel backup and recovery,Point-in-time tablespace recovery ,RMAN Backup Encryption, Unused Block Compression, Oracle Flashback Table, Oracle Flashback Database, Oracle Flashback Transaction Query, Restore Points.

So flashback database is disabled in standard edition or in standard one edition. To be able to flashback on you have to use enterprise edition.

You can check from,
Version:
---------
1)SQL> SELECT * FROM V$VERSION;

To know the options enabled in your database,

2)SQL> SELECT * FROM V$OPTION;

Restore Point and Flashback Database.

Flashback database enables us to get back our database to a prior time.

And with restore point we can make a point before any database upgrade and if there is any problem we can get back to that point.

Restore point can be two types.

1)Normal Restore point:
---------------------------

Creating a normal restore point assigns the restore point name to a specific point in time or SCN, as a kind of bookmark or alias you can use with commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.

In normal restore point Flashback logs are deleted in response to space pressure.

To create a restore point simply use,

CREATE RESTORE POINT before_upgrade;

2)Guaranteed Restore Point:
-------------------------------

In guaranteed restore point Flashback logs are not deleted in response to space pressure, if they are required to satisfy the guarantee.

Guaranteed restore point can be created whether your flashback feature on or off.

If a guaranteed restore point is created when Flashback Database is disabled, then, the first time a datafile block is modified after the time of the guaranteed restore point, an image of the block before the modification is stored in the flashback logs but subsequent modifications to the same block do not cause the block contents to be logged again. In this case you cannot use FLASHBACK DATABASE to reach points in time between the guaranteed restore points and the current time.

If a guaranteed restore point is created when Flashback Database is enabled, then the database perform normal flashback operations and create logs. The flash recovery area always retains the flashback logs required to allow FLASHBACK DATABASE to any time as far back as the earliest
currently defined guaranteed restore point. In this case you can use FLASHBACK DATABASE to reach points in time between the guaranteed restore points and the current time.

To create a guaranteed restore point use,

CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

You can see your restore points from V$RESTORE_POINT.

To drop a restore point use

SQL> DROP RESTORE POINT before_app_upgrade;

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
Mechanism followed by RMAN Restore & Recovery.

How to Move the Change Tracking File

A)Losing Contents of the file
-------------------------------------
If you don't bother about the contents of the change tracking file then it is simple to move the change tracking file. If fact if you don't want to shut down your database and yet want to move the change tracking file you will loss the contents of the file.

In this situation just do the following, Disable and Enable change tracking with new file location.

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'new_location';

B)You do want to loss the contents of the file.
-------------------------------------------------------

1) Find the current name of the change tracking file.

SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;
FILENAME
--------------------------------------------------------------------------------
/oradata2/change_tracking.f

2)Shutdown the Database.

SQL> shutdown immediate;

3)Using OS move the file to new location.

SQL> !mv /oradata2/change_tracking.f /oradata1/moved.f

4)Mount the database and reflect new location in database.

SQL>STARTUP MOUNT;
SQL> alter database rename file '/oradata2/change_tracking.f' to '/oradata1/moved.f';
Database altered.

5)Open the database and Check the location.

SQL> ALTER DATABASE OPEN;
Database altered.
SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;
FILENAME
--------------------------------------------------------------------------------
/oradata1/moved.f

Improve Incremental Backup Performance -Change Tracking.

RMAN's change tracking feature for incremental backups improves incremental backup performance.
With change tracking feature RMAN creates a file named change tracking file and then any blocks changes entry is recorded in the file.

After enabling change tracking, the first level 0 incremental backup still has to scan the entire datafile, as the change tracking file does not yet reflect the status of the blocks. Subsequent incremental backup that use this level 0 as parent will. use the change tracking file to identify changed blocks.

Enable and disable Change Tracking
-------------------------------------------------
Block change tracking can enabled by
SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
The file will be created in the DB_CRETE_FILE_DEST if it is set. However manually can be set by

SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/oradata2/change_tracking.f';

To disable change tracking, use this SQL statement:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Query V$BLOCK_CHANGE_TRACKING to know the status and filename of block change tracking.

About RMAN Incremental Backup

RMAN incremental backups back up only datafile blocks that have changed since a
specified previous backup.

Each data block in a datafile contains a system change number (SCN), which is the
SCN at which the most recent change was made to the block. During an incremental
backup, RMAN reads the SCN of each data block in the input file and compares it to
the checkpoint SCN of the parent incremental backup. If the SCN in the input data
block is greater than or equal to the checkpoint SCN of the parent, then RMAN copies
the block.

If you use block change tracking feature then RMAN can refer change tracking file the changed block in the datafile in stead of scanning the whole datafile. I will show in other topic how I can enable block change tracking.

Incremental Backup can be either level 0 or level 1 backup.

Level 0 Incremental Backup:
------------------------------------
A level 0 incremental backup is the base backup for subsequent incremental backups.

It copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. The only difference between a level 0 incremental backup and a full backup is that a full backup
is not included in an incremental strategy. That is after taking full backup , you can’t perform incremental backup over it.

Level 0 backup of database is taken by

RMAN>BACKUP INCREMENTAL LEVEL 0 DATABASE;

Level 1 Incremental Backup:
------------------------------------------------------
Level 1 incremental backup can be two types,

1) Differential Level 1 Backup: It backs up all blocks changed after the most recent
incremental backup at level 1.

If no level 1 found then backs up all blocks after most recent incremental backup at level 0.

If no level 1 and level 0 is found then the behavior is based on COMPATIBILITY settings.

If compatibility is >=10.0.0, RMAN copies all blocks changed since the file was created, and stores the results as a level 1 backup.

If compatibility <10.0.0, RMAN generates a level 0 backup.

This is the default level 1 incremental backup. Whenever you use
RMAN>BACKUP INCREMENTAL LEVEL 1 DATABASE;
differential backup is performed on database.

2) Cumulative Level 1 Backup: It backs up all blocks changed after the most recent incremental backup at level 0. Level 1 cumulative backup can be taken by,

RMAN>BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Thursday, April 24, 2008

How to Enable Flashback Database

To enable flashback database the following operations is needed.

1)Configure the Database in archivelog mode.
To change archiving read, Change Archival Mode

2)Configure Flash Recovery Area.
To configure flash recovery area,
Set up Flash Recovery Area

3)Clean Shutdown and mount the database.

Alter Database Flashback ON;

Before running command you can check whether flashback was actually On or not.

select flashback_on from v$database;

Steps:
------
SQL> alter database flashback ON;
Database altered.

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

4)Open the database and optionally you can set DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes. By default it is 1 day(1440 minutes).

To make it 3 days
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 4320

However you can disable Flashback Database for a tablespace.Then you must take its datafiles offline before running FLASHBACK DATABASE.

Like,

SQL> select file_name,file_id from dba_data_files where tablespace_name='TEST';
FILE_NAME FILE_ID
------------------------------ ----------
/oradata2/1.dbf 5

SQL> alter database datafile 5 offline;
Database altered.

SQL> ALTER TABLESPACE test flashback off;
Tablespace altered.

SQL> recover datafile 5;
Media recovery complete.

SQL> alter database datafile 5 online;
Database altered.

To disable flashback feature simply issue,
SQL>ALTER DATABASE FLASHBACK OFF;
Database altered.

The LIST, REPORT, and DELETE Commands in RMAN

List Command:
The LIST command uses RMAN repository information and provide lists of backups, archived logs, and database incarnations.

Output of LIST command can be filtered BY BACKUP and BY FILE option.

1)RMAN> list backup; #List all your backup sets.
2)RMAN>LIST BACKUPSET; #Lists only backup sets and proxy copies.
3)RMAN>LIST COPY; #Lists of Image copies and Archive Logs.
4)RMAN>LIST EXPIRED BACKUP; #Backups did not found after crosscheck. That is backup is manually moved or deleted from OS.
5)RMAN>LIST BACKUP BY FILE; #List backup by Datafile, controlfile, spfile.
6)RMAN>LIST BACKUP SUMMARY; #Lists backup sets, proxy copies, and disk copies.
7)LIST BACKUP OF DATABASE; LIST BACKUP LIKE '/tmp/%'; list backup of datafile 1; LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '17-MAR-2008' AND '22-MAR-2008'; are also available.
8) LIST INCARNATION; LIST INCARNATION OF DATABASE; to see the incarnations of your database.

Report Command:
RMAN REPORT command analyzes the available backups and return results about while files need backup which files are obsolete etc.

Remember the result of REPORT command is based on repository data. So, if backups have been deleted from disk or tape outside of RMAN, reports generated by RMAN do not automatically reflect these changes.

So before report it is good to run CROSSCHECK of all backup in order to update repository.

1)REPORT NEED BACKUP; # Determine which database files need backup under a specific retention policy.

2)REPORT UNRECOVERABLE; #Report which database files require backup because they have been affected by some NOLOGGING operation.

3)REPORT SCHEMA; #Lists and displays information about the database files.

4)REPORT OBSOLETE; #REPORT OBSOLETE displays the backups that are obsolete according to the current retention policy.

5)REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE SKIP TABLESPACE data01;

If you use Recovery Catalog then past data can be shown.
Like, REPORT SCHEMA AT TIME 'SYSDATE-10';

Delete Command:
Delete commands is used to delete any backup or backupsets.
1)Delete all your backup sets RMAN> delete backupset all;
2)Delete all image copies. RMAN> delete copy all;
3)Delete obsolete Backups. RMAN>DELETE OBSOLETE;

Set up a Database for RMAN Backup

Interact with RMAN Client.

Issues between RMAN Client and Database

About Issuing RMAN Commands

Various RMAN Backup Commands.

1)Full Database Backup:
------------------------------
RMAN>BACKUP DATABASE;

2)Individual Tablespaces Backup:
------------------------------------
To Backup tablespace data01 and data02 only to tape,
BACKUP DEVICE TYPE sbt TABLESPACE data01,data02;

3)Individual Datafiles and Datafile Copies with RMAN
--------------------------------------------------------------
To Backup datafile 1 through 4 to tape and to backup datafile system01.dbf located in /oradata from disk to tape use the following,
BACKUP DEVICE TYPE sbt DATAFILE 1,2,3,4 DATAFILECOPY '/oradata/system01.dbf';

4)Backing Up Control Files
---------------------------------
•If CONFIGURE CONTROLFILE AUTOBACKUP is ON (by default it is OFF), then RMAN automatically backs up the control file and server parameter file after every backup and after database structural changes.

•Manually controlfile can be backed up by BACKUP CURRENT CONTROLFILE;

•Also manually it is backed up when we use BACKUP TABLESPACE users INCLUDE CURRENT CONTROLFILE;

•Manually, when we back up datafile 1, RMAN automatically includes the control file and SPFILE in backups of datafile 1. But if the control file block size is not the same as the block size for datafile 1, then the control file cannot be written into the same backup set as the datafile. RMAN writes the control file into a backup set by itself if the block size is different.

•When controlfile is backed up manually (the above three), the only RMAN repository data for backups within the current RMAN session is in the control file backup, and also a manually backed-up control file cannot be automatically restored.

5)Backing Up SP Files:
------------------------------
•If CONFIGURE CONTROLFILE AUTOBACKUP is ON (by default it is OFF), then RMAN automatically backs up the control file and server parameter file after every backup and after database structural changes.

•Explicitly SPfile can be backed up by BACKUP SPFILE;

•If database is not started with SPfile then SPfile can't be backed up.

6)Backing Up Archived Redo Logs
------------------------------------------
BACKUP ARCHIVE ALL DELETE INPUT;
-This command will backup just one archivelog and delete just one log archive destination.

BACKUP ARCHIVE ALL DELETE ALL INPUT;
-This command will backup just one archivelog and delete all log archive destination.

BACKUP DATABASE PLUS ARCHIVELOG;
-This command backs up the database and all archived logs.
-If CONFIGURE BACKUP OPTIMIZATION ON is set then RMAN skips backups of archived logs that have already been backed up to the specified device.

Wednesday, April 23, 2008

Set Up a Flash Recovery Area for RMAN

Flash recovery area simplifies the ongoing administration of your database by automatically naming recovery-related files, retaining them as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed to restore your database and space is needed for some other backup and recovery-related purpose.

To see up flash recovery follow below steps.

1)Set up DB_RECOVERY_FILE_DEST_SIZE:
SQL> alter system set db_recovery_file_dest_size=2G;

2)Decide the area from OS where you will place Flash recovery area.
SQL>host mkdir /oradata1/flash_recovery_area

3)Set up DB_RECOVERY_FILE_DEST:
SQL> alter system set db_recovery_file_dest='/oradata1/flash_recovery_area';

The V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE views can help to find out the current location, disk quota, space in use, space reclaimable by deleting files,total number of files, the percentage of the total disk quota used by different types of files, and how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.

In order to disable flash recovery area issue,
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';

System altered.
Related Documents
Set up Flash Recovery Area for RMAN
Database Startup fails with error ORA-16038,ORA-19809, ORA-00312