loss of all controlfiles using user managed backup and recovery

SETUP : All scenarios are in archivelog mode.

              Database version is  10.2.0.1.0

1. Loss of all controlfiles when all redos are available.

SQL> startup

SQL> alter database begin backup;

SQL> select * from v$backup;

$cp /u01/app/oradata/orcl1/*.* /u01/app/backup/orcl1/.

 FILE# STATUS CHANGE# TIME

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

1 ACTIVE  482215 05-DEC-11

2 ACTIVE  482215 05-DEC-11

3 ACTIVE  482215 05-DEC-11

4 ACTIVE  482215 05-DEC-11

5 ACTIVE  482215 05-DEC-11

SQL> alter database end backup;

SQL>  select * from v$backup;

     FILE# STATUS CHANGE# TIME

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

1 NOT ACTIVE  482215 05-DEC-11

2 NOT ACTIVE  482215 05-DEC-11

3 NOT ACTIVE  482215 05-DEC-11

4 NOT ACTIVE  482215 05-DEC-11

5 NOT ACTIVE  482215 05-DEC-11

SQL> select NAME,SEQUENCE#,RESETLOGS_CHANGE#,FIRST_CHANGE#,NEXT_CHANGE# from v$archived_log;

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_7frrnnyw_.arc   1 482026      482026   482412

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_7frrnp2f_.arc   2 482026      482412   482414

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_3_7frrnvjn_.arc   3 482026      482414   482416

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS    FIRST_CHANGE# FIRST_TIM

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

1    1       4   52428800     1 NO  CURRENT   482416 05-DEC-11

2    1       2   52428800     1 YES INACTIVE   482412 05-DEC-11

3    1       3   52428800     1 YES INACTIVE   482414 05-DEC-11

Now, delete all the control files.

SQL> shutdown abort

SQL> startup 

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size    1218292 bytes

Variable Size   71305484 bytes

Database Buffers   88080384 bytes

Redo Buffers    7168000 bytes

ORA-00205: error in identifying control file, check alert log for more info

-> Restore controlfile from backup to all the locations mentioned in initialization parameter file.

$cp /u01/app/backup/contro1.ctl /u01/app/oradata/orcl1/control01.ctl

$cp /u01/app/backup/contro1.ctl /u01/app/oradata/orcl1/control02.ctl

$cp /u01/app/backup/contro1.ctl /u01/app/oradata/orcl1/control03.ctl

SQL> alter database mount;

Database altered.

SQL> recover database using backup controlfile [until cancel];  

ORA-00279: change 482216 generated at 12/05/2011 11:42:39 needed for thread 1

ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_%u_.arc

ORA-00280: change 482216 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

-> It requires archived log related to seq1. Suggestion is pointing to current location, and press enter to accept the suggestion

ORA-00279: change 482412 generated at 12/05/2011 11:50:04 needed for thread 1

ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_%u_.arc

ORA-00280: change 482412 for thread 1 is in sequence #2

ORA-00278: log file '/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_7frrnnyw_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

-> It requires archived log related to seq2. Suggestion is pointing to current location, and press enter to accept the suggestion

ORA-00279: change 482414 generated at 12/05/2011 11:50:05 needed for thread 1

ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_3_%u_.arc

ORA-00280: change 482414 for thread 1 is in sequence #3

ORA-00278: log file '/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_7frrnp2f_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

-> It requires archived log related to seq3. Suggestion is pointing to current location, and press enter to accept the suggestion

ORA-00279: change 482416 generated at 12/05/2011 11:50:11 needed for thread 1

ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_4_%u_.arc

ORA-00280: change 482416 for thread 1 is in sequence #4

ORA-00278: log file '/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_3_7frrnvjn_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

  

-> It requires archived log related to seq4. Suggestion is pointing to an archived location, but required redo is available in redo01.log(it is clear from the output of redo log) so,specify the file name and location and press enter.

/u01/app/oradata/orcl1/redo01.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

-> And, you will get all the data back.

**********************************************************************************

2. Loss of all controlfiles and one of the redo is missing

SQL> startup

SQL> alter database begin backup;

$cp /u01/app/oradata/orcl1/*.* /u01/app/backup/orcl1/

SQL> alter database end backup;

*****Perform some changes*************

SQL> Alter system switch logfile;

/

/

/

SQL> select name,sequence# from v$archived_log; 

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_7fs1hyz7_.arc   1

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_7fs1hzyk_.arc   2

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_3_7fs1j3o5_.arc   3

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_4_7fs1j40o_.arc   4

SQL>  select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS    FIRST_CHANGE# FIRST_TIM

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

1    1       5   52428800     1 NO  CURRENT   485153 05-DEC-11

2    1       3   52428800     1 YES ACTIVE   485148 05-DEC-11

3    1       4   52428800     1 YES ACTIVE   485151 05-DEC-11

->Remove archived log sequence#2

$ rm /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_7fs1hzyk_.arc 

-> remove all controlfiles

$ rm /u01/app/oradata/orcl1/control*.ctl

SQL> shutdown abort

SQL> startup 

Error-> error in identifying controlfile

Database is in nomount state.

-> copy all the controlfile and datafiles from backup location to target location

$ cp /u01/app/backup/orcl1/*.* /u01/app/oradata/orcl1/*.*

SQL> alter database mount

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 484998 generated at 12/05/2011 14:15:41 needed for thread 1

ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_%u_.arc

ORA-00280: change 484998 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 485146 generated at 12/05/2011 14:21:10 needed for thread 1

ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_2_%u_.arc

ORA-00280: change 485146 for thread 1 is in sequence #2

ORA-00278: log file '/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_05/o1_mf_1_1_7fs1hyz7_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

--> Since redo sequenc#2 does not exists.Issue cancel. 

CANCEL

Media recovery cancelled.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

**********************************************************************************

3. Recovery from loss of all controlfiles  by creating controlfile.

-> Same procedure will work whether all the archivelogs is available or not.

SQL> SQL> startup

SQL> alter database begin backup;

$cp /u01/app/oradata/orcl1/*.* /u01/app/backup/orcl1/

SQL> alter database end backup;

SQL> alter database backup controlfile to trace as '/u01/app/control.bak' noresetlogs;

*****Perform some changes*************

SQL> Alter system switch logfile;

/

/

/

SQL> select name,sequence# from v$archived_log; 

1

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_1_7fv6mo17_.arc

 SEQUENCE#

----------

NAME

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

2

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_2_7fv6zzz6_.arc

3

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_3_7fv700xt_.arc

4

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_4_7fv70466_.arc

 SEQUENCE#

----------

NAME

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

5

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_5_7fv70532_.arc

6

/u01/app/flash_recovery_area/ORCL1/archivelog/2011_12_06/o1_mf_1_6_7fv70b3f_.arc

SQL> select * from v$log;

delete archive log related to seq 2 and delete all the controlfiles

SQL> shutdown abort

SQL> startup 

error in identifying contorlfile.

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL1" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oradata/orcl1/redo01.log'  SIZE 50M,

  GROUP 2 '/u01/app/oradata/orcl1/redo02.log'  SIZE 50M,

  GROUP 3 '/u01/app/oradata/orcl1/redo03.log'  SIZE 50M

DATAFILE

  '/u01/app/oradata/orcl1/system01.dbf',

  '/u01/app/oradata/orcl1/undotbs01.dbf',

  '/u01/app/oradata/orcl1/sysaux01.dbf',

  '/u01/app/oradata/orcl1/users01.dbf',

  '/u01/app/oradata/orcl1/example01.dbf'

CHARACTER SET WE8ISO8859P1

;

Controlfuile  created

SQL> recover database;

SQL> alter database open;

**********************************************************************************

create database manually with minimum configuration

This blog discusses how to create oracle database manually using CREATE DATABASE command.

Following are some of the ways alongwith minimum requirement that must be satisfied in order to create database successfully.

These steps have been tested in oracle 10g Enterprise Edition 10.2.0.1.0. on Linux platform.

1. First of all, set ORACLE_SID and ORACLE_HOME to apprpriate values.

Suppose you want to create a new database named TEST. Then , issue

$ORACLE_SID=TEST

$ORACLE_HOME=/oracle/product/10.2.0/db_1

2. Create initialization parameter file $ORACLE_HOME/dbs/initTEST.ora

/*****initTEST.ora******/

control_files=(/oracle/product/10.2.0/db_1/oradata/TEST/control01.ctl,/oracle/product/10.2.0/db_1/oradata/TEST/control02.crl)

undo_management=AUTO

undo_tablespace=UNDOTBS

db_name=TEST

db_block_size=8192

sga_max_size=268000000

sga_target=268000000

user_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/udump'

background_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/bdump'

core_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/cdump

/********************/

Specified location must exist. Otherwise, error returns.

3. Now, connect to sqlplus:

$sqlplus /nolog

SQL> conn / as sysdba

4. Start the database in nomount state.

SQL> startup nomount

5.Issue following create database statement:

SQL> create database TEST

logfile group 1('/oracle/product/10.2.0/db_1/oradata/TEST/redo01.log') size 10M,

group 2('/oracle/product/10.2.0/db_1/oradata/TEST/redo02.log') size 10M,

group 3('/oracle/product/10.2.0/db_1/oradata/TEST/redo03.log') size 10M

character set WE8ISO8859P1

national character set AL16UTF16

datafile '/oracle/product/10.2.0/db_1/oradata/TEST/system01.dbf' size 100M autoextend on next 10M maxsize unlimited extent management local

sysaux datafile '/oracle/product/10.2.0/db_1/oradata/TEST/sysaux01.dbf' size 10M autoextend on next 10M maxsize unlimited

undo tablespace undotbs datafile '/oracle/product/10.2.0/db_1/oradata/TEST/undotbs01.dbf' size 10M

default temporary tablespace temp tempfile '/oracle/product/10.2.0/db_1/oradata/TEST/temp01.dbf' size 10M;

Above procedure would create datafiles in the specified location. It create four tablespaces: system,sysaux,undotbs and temp.

6. run $ORACLE_HOME/rdbms/admin/catalog.sql script.

7. run $ORACLE_HOME/rdbms/admin/catproc.sql script.

Creating database using OMF feature

You can also create database using OMF feature. Change initTEST.ora file as follows:

undo_management=AUTO

sga_max_size=268000000

sga_target=268000000

undo_tablespace=UNDOTBS1

db_name=TEST

db_block_size=8192

db_create_file_dest='/oracle/product/10.2.0/db_1/oradata'

db_create_online_log_dest_1='/oracle/product/10.2.0/db_1/oradata'

user_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/udump'

background_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/bdump'

core_dump_dest='/oracle/product/10.2.0/db_1/admin/TEST/cdump'

Here, two OMF related parameters have been set,that is, db_create_file_dest and db_create_online_log_dest.

And issue create database statement as:

SQL> create database TEST

character set WE8ISO8859P1

national character set AL16UTF16

undo tablespace undotbs1

default temporary tablespace temp;

No need to specify the name of any of the datafiles and redolog files here. It will create all the datafiles according to the specified OMF related parameters' values.

All the other steps are same except step no.2 and 5.

If creating database using OMF features, controlfile parameter needs to be set in the initialization parameter file after successful creation of the database as Oracle would not identify correct controlfile during next startup of the database. Oracle will locate the controlfile in $ORACLE_HOME/dbs but can not find one. That's why always set controfile parameter that points to the correct location before shutting down the instance.

You can issue even much simple create database statement when using OMF feature. Issue just:

SQL> create database TEST;

But all other steps are same.

restoring controlfile in archivelog mode using RMAN

If controlfile has been dropped and we are running in archivelog mode, what options do we have to restore controlfile.

1. RMAN> startup nomount
2. RMAN> restore controlfile from autobackup;
3. RMAN> alter database mount;
4. RMAN> recover database;
5. RMAN> alter database open resetlogs;

Procedure is same if any archivelog is missing

complete recovery of datafile using RMAN

You can perform following experiment to perform complete recovery of datafile in archivelog mode.
1. Assume database is up and running.
2. Delete datafile 4.
3. Valid backup must exist.
4. All the redo logs after the last valid backup exist.

To restore it,
1. RMAN> SQL'alter database datafile 4 offline';
2. RMAN> restore datafile 4;
3. RMAN> recover datafile 4;
4. RMAN>SQL'alter database datafile 4 online';

If an active redo log file is lost, then incomplete recovery of a datafile is not possible . Instead we have to perform Tablespace point in time recovery.

Resolving Exception in thread "main" error

When trying to execute dbca from my unix(ubuntu 9) prompt, I get following error:

Exception in thread "main"
and prompt returns.
Solution: If working on the local machine, set DISPLAY variable as
$ DISPLAY=:0.0
$ export DISPLAY

$ dbca

and everything works fine.

Incremental backup and restore in oracle 10g

Here I am presenting the simple steps to demonstrate how to take incremental level backup,perform restore and recovery using RMAN.One can learn Rman incremental level backup easily by going through this post. I used oracle 10g express edition.

Difference between differential and cumulative incremental backup will be clear by going through this simple demo.

Simple scenarios has been taken.

Demonstrating DIFFERENTIAL INCREMENTAL BACKUP

1. Database is in NOARCHIVELOG mode.
2. Not using recovery catalog.
3. RMAN configuration setting that I used is following. I am posting the output of
RMAN> SHOW ALL;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 400 M FORMAT '/usr/lib/oracle/xe/backup/df_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/snapcf_XE.f'; # default

4. Since database is in NOARCHIVELOG mode, it is necessary to shut down the database cleanly using immediate,normal or transactional options.

RMAN> shutdown

5. To take incremental 0 level backup,mount the database. This step is required even in non-incremental backups.

RMAN> startup mount

6. Issue
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'FULL_INC';
This takes the full backup of the database and also includes controlfile as well as spfile.

7. Issue
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 1 YES UNUSED
0

2 1 1 52428800 1 NO CURRENT
1077758 08-MAR-11


SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
1084366


8. RMAN> ALTER DATABASE OPEN;

9. Now, perform some DML operations. For example,

SQL> CREATE TABLE T1(C1 NUMBER);
SQL> INSERT INTO T1 VALUES(10);
SQL> /
SQL> /
SQL> COMMIT;

10. Again, shutdown the database to take incremental level backup.
RMAN>SHUTDOWN
RMAN>STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1';
It backups only the changes made since the last incremental level n or lower backup.

11. Open the database again to perform some insertions.
RMAN> ALTER DATABASE OPEN;

12. SQL> INSERT INTO T1 VALUES(1);
SQL> /
SQL> /
SQL> /
SQL> COMMIT;

13. Shutdown the database again to take incremental level 2 backup.
RMAN> SHUTDOWN
RMAN> STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 2 DATABASE TAG 'INC_1';

14. Open database again to made some insertions.
RMAN> ALTER DATABASE OPEN;
SQL> INSERT INTO T1 VALUES(9);
SQL> /
SQL> /
SQL> COMMI;

15. select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 1 YES UNUSED
0

2 1 1 52428800 1 NO CURRENT
1077758 08-MAR-11


SQL> select checkpoint_change# from v$database;


CHECKPOINT_CHANGE#
------------------
1084663

Here all the changes since the last backup has been stored in the redo logs.

16. Now, delete controlfile.
$ mv oradata/XE/controlfile cf.bak

17. RMAN> SHUTDOWN ABORT;

18. RMAN> STARTUP
Database will not open. Database will only go upto the NOMOUNT state since controlfile has been lost.

19. RMAN> Now following steps are needed to recover the database.

20. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
If controlfile autobackup is located in default location, then we can skip this step.

21. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP

22. RMAN> ALTER DATABASE MOUNT;

23. RMAN> RESTORE DATABASE;
It will restore the database using the 0 level backup.

24. RMAN> RECOVER DATABASE
Watch the output carefully. You can recognise various backups that are being applied. Look for the tags that you have given to backupsets. It will applies all the incrementals one by one. First it will apply level 1 incremental, and then level 2. Then it will search for appropriate log sequence and applies the same if found. If log switching has not been taken place after the last incremental backup, then we get all the data without any data loss. And database will restore upto the current point in time. In our case no log switching has taken place and all the data since the last backup exist in the redologs. NOREDO option is needed when log switching has taken place. NOREDO option is not needed if log switch has not taken place.

25.RMAN> ALTER DATABASE OPEN RESETLOGS;

26. Now, view the table t1 and you will find the table restored upto the latest point in time if all the redos has been applied.

27. Always take full backup of the database after opening the database in resetlogs mode.

Demonstrating CUMULATIVE INCREMENTAL BACKUP

Here we will utilize same scenario as above,i.e. no recovery catalog mode,no archivelog mode.
We will start here by taking incremental level 0 backup.

1. RMAN> SHUTDOWN
2. RMAN> STARTUP MOUNT
3. RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'FULL_INC';
4. RMAN> ALTER DATABASE OPEN;

5. SQL> SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;
6. Make some insertion.
SQL> INSERT INTO T1 VALUES(2);
SQL> /
SQL> /
SQL> COMMIT;

7. Again shutdown the database to take incremental level 1 database which will copies only the changed blocks since the last incremental level 0 backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1';

8.ALTER DATABASE OPEN;

9. SQL> INSERT INTO T1 VALUES(3);
SQL> /
SQL> /
SQL> COMMIT;

10. Again shutdown the database to take cumulative incremental level 1 backup, this time. This backups all the changes made after the last n-1 or lower backup,here it will backup all the changes since the incremental level 0 backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'CUM_1';

11. RMAN> ALTER DATABASE OPEN;

12. Perform some DML again.
SQL> INSERT INTO T1 VALUES(9);
SQL>/
SQL> /
SQL> COMMIT;

13. Now, we will shutdown the database to take incremental level 1 backup this time.
RMAN> SHUTDOWN
RMAN> STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1_1';

14. RMAN> ALTER DATABASE OPEN;

15. SQL> INSERT INTO T1 VALUES(0);
SQL> /
SQL> COMMIT;

16. Delete controlfile.
$ mv oradata/XE/controlfile cf.bak

17. RMAN> SHUTDOWN ABORT

18. RMAN> STARTUP
Database will not open.It will go upto only NOMOUNT state.

19. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/XE/backup/cf_%F';

20. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

21. RMAN> ALTER DATABASE MOUNT;

22. RMAN> RESTORE DATABASE;,
It will restore the database from the last incremental level 0 database.

23. RMAN> RECOVER DATABASE;
Here NOREDO option is not necessary as it is assumed that redo logs has not been switched and complete recovery is possible. Check the same by looking at the contents of v$log and comparing with the checkpoint_change# that have noted in step 5. If all the changes since that value exist in the redo logs then NOREDO option is not needed. Else it is required.

Here first of all, cumulative incremental level 1 backup would restore as there is no need for the backup having tag 'INC_1' as cumulative incremental level 1 backup take backup of all the changes since the last level 0 backup. Thus our first incremental level 1 backup is not applied. Then it applies backup having tag 'INC_1_1' which we take after cumulative incremental level backup. After that it will apply all the changes recorded in the online redologs.
Thus , only two incremental level backups needed to apply here. But in the first scenario all the incremental backups had been applied. Thus we can say that using cumulative incremental backup in your incremental strategy provides faster recovery as number of incremental backups to be applied is less.

24. RMAN> ALTER DATBASE OPEN RESETLOGS;

25. Take whole database backup after opening database in resetlogs mode. It is a good practice to perform th same.

26. Now check your table. It must have all the changes that we made, keeping all the scenario same.

Configuring non-default listener to listen to non-default port in Oracle xe 10g

Following are the steps that I use to configure non-default listener to listen to non-default port,1522.

1. Edit initXE.ora file which resides in $ORACLE_HOME/dbs
Add local_listener=listener1 line to this file. Listener1 will be the name of the non-default listener.

2. Edit listener.ora file to configure listener1. listener.ora file resides in $ORACLE_HOME/network/admin.

My listener.ora file looks like:

LISTENER1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vishesh-netbook)(PORT = 1522)))
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
(GLOBAL_DBNAME = XE)
(SID_NAME = XE)))

3. Edit tnsnames.ora file to resolve non-default listener name,listener1.

My tnsnames.ora file looks like:

# tnsnames.ora Network Configuration File:
LISTENER1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vishesh-netbook)(PORT = 1522)))
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vishesh-netbook)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)

4. Now connect to the database.
5. After connecting, use
SQL> CREATE SPFILE FROM PFILE; #can be run after or before instance startup
SQL> SHUTDOWN

At OS prompt, use
#lsnrctl start listener1
Then use
# sqlplus /nolog

Recover from loss of controlfile in NOARCHIVELOG mode using RMAN

Here I am taking simple scenario to recover the controlfile. Following describes a scenario when my database is in NOARCHIVELOG mode. Database in oracle 10.2.0.1.0

SCENARIO: WHEN USED WITH DEFAULT RMAN CONFIGURATION SETTINGS.

Here only controlfile is lost, all the datafile and redo log files remain intact.
Following are the steps that can be taken:

1.RMAN> STARTUP MOUNT
2.RMAN> BACKUP DATABASE
3. RMAN> ALTER DATABASE OPEN;
4. delete controlfile.
5. RMAN> SHUTDOWN ABORT;
6. RMAN> STARTUP;
Database will not start. It starts in nomount state.
7. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
8. RMAN> ALTER DATABASE MOUNT;
9. RMAN> RESTORE DATABASE;
10.RMAN> RECOVER DATABASE ;
11.RMAN> ALTER DATABASE OPEN RESETLOGS;

And database will be recovered. Here SET DBID command is not needed because backup is located in flash recovery area. Controlfile autobackup default format is used.

SCENARIO: WHEN USED WITH non-DEFAULT RMAN CONFIGURATION SETTINGS.

I changed some of the default RMAN configuration settings. Now backup is not using flash recovery area. Also, controlfile autobackup is not using flash recovery area.

RMAN> SHOW ALL;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 400 M FORMAT '/usr/lib/oracle/xe/backup/df_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/snapcf_XE.f'; # default


Now follow these steps:

1.RMAN> STARTUP MOUNT
2.RMAN> BACKUP DATABASE
3. RMAN> ALTER DATABASE OPEN;
4. delete controlfile.
5. RMAN> SHUTDOWN ABORT;
6. RMAN> STARTUP;
Database will not start. It starts in nomount state.
7. RMAN> SET DBID=
8. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
9. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
10. RMAN> ALTER DATABASE MOUNT;
11. RMAN> RESTORE DATABASE;
12.RMAN> RECOVER DATABASE ;
13.RMAN> ALTER DATABASE OPEN RESETLOGS;

Note: If all the redo logs since the last database exist, then you can skip RESTORE DATABASE statement in both the above cases.

RMAN restore and recovery in NOARCHIVELOG mode

Here I am writing about simple scenarios related to backup and recovery so that RMAN can be learned easily.
Lets start with a simple scenario for performing backup,restore and recovery using RMAN when our database is running in NOARCHIVELOG mode.
Oracle doc says that recovery cannot be performed in NOARCHIVELOG mode. But following scenarios explains how can we perform complete as well as incomplete recovery in NOARCHIVELOG mode. Different scenarios has been given to show how to recover database to a point in time as well as upto the recent time when the disaster occur. Each scenario has been tested in Oracle 10g xe hoping that each will also work with oracle 10g enterprise edition. Each scenario has been tested in NOARCHIVELOG mode.

Complete recovery can be performed in NOARCHIVEOG mode if all the changes since the last backup exists in the redo logs(including the redo logs that exists during the backup.)

Incomplete recovery can be performed if all the changes since the last backup up to a point in time exists in the redo log.

We can perform incomplete recovery until scn,until time and until sequence using RMAN.

SCENARIO I:Complete recovery: a datafile is lost

1. I am using Oracle 10g express edition.
2. Database is running in NOARCHIVELOG mode.
3. Cold backup has been performed after clean shutdown of the database.
4. One of the datafiles ,say users.dbf, has been lost.
5. All the redologs including one with the same log seq# as during the backup is available.
6. Thus here we have to restore only the effected datafiles,not the whole database.
Use the following sequence of steps:
1. RMAN> shutdown
2. RMAN> startup mount
3. RMAN> restore datafile 4; or restore tablespace users;
4. RMAN> recover datafile 4; or recover tablespace users;
5. RMAN> alter database open;

Instead of step 3 and 4, we can also use
RMAN> restore database; and
RMAN> recover database, respectively.

But we do not need to restore and recover the whole database in this scenario. Important point is that No data loss is guaranteed in NOARCHIVELOG mode if all the redo logs exist since the last backup. Thus complete recovery has been performed in NOARCHIVELOG mode.

SCENARIO II : Incomplete recovery
Keeping everything same as scenario I, some redo log seq no# since the last backup is not available say, redo log has been switched. Users.dbf has been lost. Here, to restore the lost datafile following steps should be taken:

1. RMAN>startup force nomount;
If database is already shutdown, then don't use force option.
2. RMAN>restore controlfile from autobackup; or restore controlfile from 'autobackup_location';
3. RMAN>alter database mount;
4. RMAN>restore database;
5. RMAN>recover database noredo;
6. RMAN> alter database open resetlogs;

Here restore of whole database is must.

You can also follow following link:


SCENARIO III: Incomplete recovery - until scn-when a table is dropped

SQL> CREATE TABLE T1(C1 NUMBER);
SQL> INSERT INTO T1 VALUES(10);
SQL>/
SQL>/
SQL> COMMIT;

SQL> select current_scn from v$database;
It generated 810390;
SQL> drop table t1;

Check v$log. If all the scns since the last backup upto the point you want to recover to (in our case it is, 810390, because table is dropped after that time) exists , then we can recover table t1 even in noarchivelog mode.
Use following operations:


RMAN> run
{
shutdown;
startup mount;
restore database;
recover database until scn 810390;
alter database open resetlogs;
}

Now you will find recovered table t1.

SCENARIO IV- incomplete recovery -until time: when a table is dropped.

1. Yesterday night, you have taken cold,consistent whole database backup.
2. In the next morning, some user error caused employees table t1 to drop.
3. If you know the approximate time of dropping the table, you can recover the table even to the point of recent changes made in the table. There is no need to panic even if your database is running in NOARCHIVElog mode.
4. Check v$log. If all the changes since the last backup is present in the redo log groups, we can recover the dropped table. Use the following procedure. Suppose the table is dropped at 10:30 in the morning.

RMAN> RUN {
2> SET UNTIL TIME = '2011-02-22:10:28:00';
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> ALTER DATABASE OPEN RESETLOGS;
}

For until time to function you must set $NLS_LANG and $NLS_DATE_FORMAT appropriately. In my case $NLS_LANG has been set to american and NLS_DATE_FORMAT has been set to 'YYYY-MM-DD:HH24:MI:SS';

SCENARIO V-INCOMPLETE RECOVERY : SQLPLUS UNTIL CANCEL:DATAFILES AND ACTIVE REDO LOG FILE IS LOST

Suppose two datafiles as well as active redo log group is lost. Here to recover database, use following procedure:

RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
SQL> RECOVER DATABASE UNTIL CANCEL;

ORA-00279: change 828771 generated at 02/23/2011 11:38:39 needed for thread 1
ORA-00289: suggestion :
/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/archivelog/2011_02_23/o1_mf
_1_1_%u_.arc
ORA-00280: change 828771 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

RMAN> ALTER DATABASE OPEN RESETLOGS;

Since one of the redolog is lost ,we do not have all the redo logs available after the last backup. That is why we have to use sql command UNTIL CANCEL. Type cancel when prompted. We will get changes only upto the last backup that is restored.

SCENARIO VI-INCOMPLETE RECOVERY : UNTIL SCN:DATAFILES AND ACTIVE REDO LOG FILE IS LOST

Here we will suppose that along with one datafile, active redo log file has also been lost. Yes, of course, it is the same scenario as scenario VI. But read out, difference will be clear.

1. SQL> truncate table t1;

Table truncated.

SQL> inert into t1 values(0);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

C1
----------
0
0

SQL> alter system switch logfile;

System altered.
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 1 52428800 1 NO ACTIVE
1183659 14-MAR-11

2 1 2 52428800 1 NO CURRENT
1184383 14-MAR-11

Group# 2 is current. We will delete log file that belongs to group# 2. Changes since the last backup are stored in log seq#1 which belongs to group# 1. Only the changes that are stored in log seq#2 will be lost. We will get back our changes that we made in table t1. Note the low scn (1184383,here)that is listed in Log seq#2. We will need it

Get the name and location of the redo log group# 2 by listing the contents of v$logfile.
SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
2 ONLINE
/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_2_6qwfdq7v_
.log
YES

1 ONLINE
/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_1_6qwm5xz0_

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
.log
YES


SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

oracle@geet:~$ mv /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/onlinelog/o1_mf_2_6qwfdq7v_.log m.log
oracle@geet:~$ mv oradata/XE/users.dbf uu.bak

RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database UNTIL SCN 1184383;
RMAN> recover databse UNTIL SCN 1184383;
RMAN> alter database open resetlogs;

SQL> select * from t1;

C1
----------
0
0
See the output above, we got the modification of our table back.

When is listener.ora needed

Today I got stucked with ora:12514 : TNS: listener does not know cuurrently of service requested in connect descriptor.

When I tried to connect with sqlplus using
sql> conn sys/sys@xe as sysdba

I got ora:12514 error

Initially, I thought that error occured due to non registration of xe database service with the listener. And I tried to set the parameters of static service registration in listener.ora file.
But later I got the knowledge that error occured because my database server has not yet been started. Without starting the database I was trying to connect to the database which is a wrong, even impossible action.
After much struggle, I got the idea of the actual problem.

A listener.ora file is NOT needed to start a default listener. I renamed my file to something else, so that I have NO file named "listener.ora". Then I restarted the listener, and it came up just fine with all default values .

If the listener uses default values, there is nothing special needed to get the instance to self-register with the listener. If the listener is already running when the instance starts, it should register on startup. It will also re-register every few minutes there after, so if the listener comes up after the db instance, it may take a couple of minutes for the registration to occur and the listener come to know of the instance.