Oracle 11g RMAN Incremental Backup

RMAN Incremental Backup
-----------------------------

DB should be backed up in ARCHIVELOG mode for Incremental Backup.

1.
ORACLE_HOME=/u01/app/oracle/product/11.0.1/db_1
export ORACLE_HOME

ORACLE_SID=orcl1
export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin

2.
$ sqlplus / as sysdba

SQL> Select log_mode
From v$database;

O/P -> Make sure the DB is in ARCHIVELOG mode

SQL> exit

3.
rman target=/

4.
RMAN> Backup incremental level 0 database plus archivelog delete input;

5.
RMAN> exit

6.
SQL to check the size of the Backup

$ sqlplus / as sysdba

SQL> Select a.set_count, a.start_time, a.completion_time, sum(b.blocks)
From v$backup_set a, v$backup_datafile b
Where a.set_count = b.set_countand to_char(a.start_time, 'mm/dd/yyyy hh24:mi:ss') =
(Select to_char(max(start_time), 'mm/dd/yyyy hh24:mi:ss')
From v$backup_set
Where incremental_level = 0)
group by a.set_count, a.start_time, a.completion_time;

---------

To do a incremental level 1 backup

7.
rman target=/
RMAN> Backup incremental level 1 database plus archivelog delete input;

8.
RMAN> exit

Note: To check the size of the incremental level 1 Backup use SQL in step 6 except use incremental_level = 1 in the inner Where clause.

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

- Suresh

11g Oracle RMAN offline Backup

RMAN offline Backup
----------------------

DB can be backed up in NOARCHIVELOG or ARCHIVELOG mode withoffline backups.

1.
ORACLE_HOME=/u01/app/oracle/product/11.0.1/db_1
export ORACLE_HOME

ORACLE_SID=orcl1
export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin

$ rman target=/

2.
RMAN> shutdown immediate
RMAN> startup mount

3.
RMAN> Backup database;

O/p -> ....Finished backup at ...
...Finished Control File and SPFILE Autobackup at ...

4.
RMAN> alter database open;

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

- Suresh

Oracle 11g Recovery from loss of current online Redo log file

Recovery from loss of current online Redo log file
--------------------------------------------------

1.
SQL> Select member
From v$logfile;

2.
To simulate loss of online Redo log file rename the fileslisted in step 1 above.

Result -> This may crash the DB -> This simulates loss of online Redo log files

If DB did not crash try
SQL> alter database checkpoint;
SQL> alter database clear logfile;
SQL> shutdown abort;

3.
sqlplus / as sysdba;
SQL> startup

O/P -> ORA-00313
followed by ORA-00312
followed by ORA-27037...

4.
Restore the DB files, control files from the latest backup

5.
sqlplus / as sysdba
SQL> startup mount;

6.
Determine the last log sequence archived

SQL> Select max(sequence#)
From v$archived_log
Where resetlogs_changed# = (Select max(resetlogs_change#)
From v$archived_log);

7.
SQL> recover database using backup controlfile until cancel;
SQL> recover database until cancel using backup controlfile;

O/P -> ORA-00279
followed by ORA-00289
...
followed by ORA-00280
followed by ORA-00278
Specify log: {=suggested filename Auto Cancel}

8.
Type
cancel

O/p -> Media recovery cancelled.

9.
SQL> alter database open resetlogs;

Recovery is done.

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

- Suresh

Oracle 11g Recovery from Backup Control File

Recovery from Backup Control File due to loss of all Control Files
------------------------------------------------------------------

1.
SQL> Select name
From v$controlfile;

2.
SQL> connect sys as sysdba
SQL> shutdown immediate;

3.
To simulate loss of control file rename the control files listed in step 1 above.

4.
SQL> startup

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

5.
Copy the control file from backup location to the location shown in step 1 above
(Where the control file should be)

$cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control01.ctl
$cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control02.ctl
$cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control03.ctl

or

SQL> host cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control01.ctl

SQL> host cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control02.ctl

SQL> host cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control03.ctl

6.
SQL> alter database mount;

7.
SQL> recover database using backup controlfile;

O/P -> may have ORA-00279
followed by ORA-00289
followed by ORA-00280...
Specify log: filename Auto Cancel

8.
Type
auto

O/P -> may have ORA-00308
followed by ORA-27037...

9.
Needed if step 8 did not complete with 'Media recovery complete'.

SQL> Select lf.group#, lf.member, l.sequence#
From v$logfile lf, v$log l
Where lf.group# = l.group#;

Note the member and Sequence#

10.
Start recovery again

SQL> recover database using backup controlfile;

O/P -> ORA-00279
followed by ORA-00289
followed by ORA-00280...
Specify log: filename Auto Cancel

11.
Enter the member value of sequence# for which error was given in step 10 above

e.g./u01/app/oracle/oradata/orcl1/onlinelog/redo01.log

O/p -> Media recovery complete.

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

- Suresh

11g Oracle Point-in-time recovery (Time based)

Point-in-time recovery (Time based)
----------------------------------------
1.
ORACLE_HOME=/u01/app/oracle/product/11.0.1/db_1
export ORACLE_HOME

ORACLE_SID=orcl1
export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin

$ sqlplus / as sysdba
or
SQL> connect / as sysdba;
or
SQL> connect sys as sysdba;

SQL> Shutdown immediate;

2.
Restore DB datafiles from backup location to the location where DB files are.

$ cd /u01/oracle/orabackup/orcl1/hot/
$ pwd
$ cp *.dbf /u01/app/oracle/oradata/orcl1/*

3. sqlplus / as sysdba

SQL> startup mount

SQL> alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss';

4.
SQL> recover database until time 'yyyy-mm-dd:hh:mm:ss';

e.g.
SQL> recover database until time '2009-09-26:19:18:12';
or
SQL> recover database until time '09/26/2009 19:18:12';
(based on nls_date_format)

Enter auto when prompted for archived redo log to apply

5.
alter database open resetlogs;

Note: For this to work you should have all of your datafile backup and archived redo log
upto the point-in-time specified for the recovery and the correct control file.

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

- Suresh

11g Summary of Oracle Hot Backup (Online Backup)

Summary of Backup Steps
----------------------------

1. Put the database in hot backup mode.
2. Copy all database datafiles to a backup location.
3. Take the database out of hot backup mode.
4. Force a log switch.
5. Backup the archived redo logs.

sqlplus / as sysdba

SQL> alter database begin backup;
SQL> host cp /u01/app/oracle/oradata/orcl1/*.dbf /u01/oracle/orabackup/orcl1/hot/*.*
SQL> alter database end backup;
SQL> alter system switch logfile;
optional -> SQL> alter system archive log all;
SQL > host cp /u01/oracle/arch/orcl1/* /u01/oracle/orabackup/orcl1/hot/*
SQL> alter database backup controlfile to trace;
SQL> alter database backup controlfile to '/u01/oracle/orabackup/orcl1/hot/control1.bak';

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

- Suresh

11g Oracle Hot Backup (Online Backup)

Manual Hot (online) Backup
-----------------------------

1.
mkdir for hot backup and make sure it exists with correct write permissions.
$ pwd -> /u01/oracle/orabackup/orcl1/hot

2.
sqlplus sys as sysdba

SQL> Select log_mode
From v$database;

o/p -> ARCHIVELOG

3.
SQL> Select tablespace_name, file_name
From DBA_DATA_FILES;

4.
SQL> Select group#, sequence#, status
From v$log;

o/p -> Status as CURRENT, ACTIVE, INACTIVE

5.
SQL> alter database begin backup;

6.
Copy all datafiles (Result from step 3 above)

e.g.$ cp /u01/app/oracle/oradata/orcl1/*.dbf /u01/oracle/orabackup/orcl1/hot/*.*
or
SQL> host cp /u01/app/oracle/oradata/orcl1/*.dbf /u01/oracle/orabackup/orcl1/hot/*.*

7.
SQL> Select group#, sequence#, status
From v$log;

o/p -> Status as CURRENT, ACTIVE, INACTIVE

The Sequence that was active in step 4 and upto the sequence that is active from step 7 are needed to restore the backup.

8.
SQL> alter database end backup;

9.
SQL> alter system switch logfile;

If needed run
SQL> alter system archive log all;

10.
Check the archive log dir to make sure log-file sequences that was active in step 4(through)
log-file sequences that was active in step 7 were created.

SQL > show parameterlog_archive_format

SQL > show parameterlog_archive_dest_1

SQL > host ls /u01/oracle/arch/orcl1
(In this e.g. /u01/oracle/arch/orcl1 is archive log dir)

11.
SQL > host cp /u01/oracle/arch/orcl1/arc*.* /u01/oracle/orabackup/orcl1/hot/*.*

12.
SQL > host ls /u01/oracle/orabackup/orcl1/hot/*.*
Ensure the files (data files and Redo log sequence) are copied

13.
SQL> alter database backup controlfile to '/u01/oracle/orabackup/orcl1/hot/control1.bak';

14.
optional -> If needed remove the archive-log files that were backed-up

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

- Suresh

11g Convert to Archive Mode

Archivelog Mode
------------------

1.
mkdir for archive and make sure it exists with correct write permissions.
$ pwd -> /u01/oracle/arch/orcl1

2.
sqlplus sys as sysdba

SQL> Select log_mode
From v$database;

o/p -> NOARCHIVELOG or ARCHIVELOG (If NOARCHIVELOG then proceed as follows;)

3.
SQL> alter system set log_archive_dest_1='location=/u01/oracle/arch/orcl1';

4.
SQL> shutdown immediate;

5.
SQL> startup mount;

SQL> Select open_mode
From v$database;

o/p -> MOUNTED

6.
SQL> alter database archivelog;

7.
SQL> alter database open;

SQL> Select log_mode
From v$database;

o/p -> ARCHIVELOG

8.
SQL> alter system switch logfile;

9.
SQL> host ls /u01/oracle/arch/orcl1

o/p -> The logswitch should have created an archive redo log

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

- Suresh

Oracle 11g Manual Cold Backup

Manual Cold Backup
---------------------

Note: For ASM based backup use RMAN

For Non-ASM

1.
$ cd /u01/oracle
$ mkdir orabackup
$ cd orabackup
$ mkdir orcl1
$ cd orcl1
$ mkdir cold
$ cd cold

$ ls
$ pwd -> /u01/oracle/orabackup/orcl1/cold

2.
sqlplus sys as sysdba
SQL>

3.
SQL> Select tablespace_name, file_name
From DBA_DATA_FILES;

4.
SQL> Select member
From v$logfile;

5.
SQL> Select name
From v$controlfile;

6.
SQL> shutdown immediate;

7.
SQL> exit

8.
Copy all the necessary files from based on o/p from step 3, 4 and 5

e.g.
$ cp /u01/app/oracle/oradata/orcl1/*.* /u01/oracle/orabackup/orcl1/cold

Note: TEMP file need not be copied.
Copy datafiles, logfiles and controlfiles.

9.
sqlplus sys as sysdba

SQL> startup


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

- Suresh