Database Administrator: rman

Showing posts with label rman. Show all posts

Monday, November 5, 2012

11g Migration using RMAN

The steps for 10g database:

1- Run the utlu112i.sql Pre-upgrade script. You can find this script under @$ORACLE_HOME/rdbms/admin/. It must be copied from the 11g database software.

SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql

This script adds a column named tz_version to table named registry$database. Pre-upgrade script updates this column with the value of the following query.

SQL> select version from v$timezone_file;

So it performs following operation.

SQL> ALTER TABLE registry$database ADD (tz_version NUMBER);
SQL> UPDATE registry$database set tz_version =4;

SQL> ALTER PACKAGE “SYS”.”DBMS_REGISTRY” COMPILE BODY;
SQL> ALTER VIEW “SYS”.”DBA_REGISTRY_DATABASE” COMPILE;

2- Connect to 10g database and take RMAN full backup.

#rman target /
RMAN> backup as backupset database;

3- Copy 10g database backup files and archive files to 11g database server.

The steps for 11g database:

1- Create temporary pfile in $ORACLE_HOME/dbs

*.audit_file_dest=’/oracle/admin/TALIPDB/adump’
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/talipdb/controlfile/current.257.787742981′,’+DATA/talipdb/controlfile/current.258.787742983′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_create_online_log_dest_1=’+RECO’
*.db_domain=”
*.db_name=’TALIPDB’
*.diagnostic_dest=’/oracle’
*.job_queue_processes=0
*.open_cursors=300
*.pga_aggregate_target=1G
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=2G
*.undo_tablespace=’UNDOTBS1′

2- Open the database in NOMOUNT state.

# rman target /

RMAN> startup nomount;

3- Restore controlfile from backup.

RMAN> restore controlfile from ‘/oracle/ora11g/talipdb/backup/c-784951186-20120620-02′;

4- Open the database in MOUNT state.

RMAN> alter database mount;

5- Catalog RMAN backup files and archive log files.

RMAN> catalog start with ‘/oracle/ora11g/talipdb/backup’;
RMAN> catalog start with ‘/oracle/ora11g/talipdb/archive’;

6- Restore 10g database backup to +DATA diskgroup and perform incomplete recovery.

RMAN> run
{
allocate channel c1 device type disk;
SET NEWNAME FOR DATAFILE 1 TO ‘+DATA’;
SET NEWNAME FOR DATAFILE 2 TO ‘+DATA’;
SET NEWNAME FOR DATAFILE 3 TO ‘+DATA’;
SET NEWNAME FOR DATAFILE 4 TO ‘+DATA’;
restore database until sequence 4;
switch datafile all;
recover database until sequence 4;
}

7- Open the database with RESETLOGS UPGRADE.

# sqlplus / as sysdba
SQL> alter database open resetlogs upgrade;

8- Run the upgrade script.

SQL> SPOOL upgrade.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
SQL> SPOOL off

9- If our 10g and 11g database os platforms are different then you must run utlmmig.sql script.

————–Changing 32 bit to 64 bit————–
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> SPOOL migrate.log
SQL> @$ORACLE_HOME/rdbms/admin/utlmmig.sql
SQL> SPOOL off
——————————————-

10- Now, you can open the database.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

11- Run the Post-Upgrade script to check problems.

SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql

12- Compile invalid objects.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

13- Drop the temporary file and create new one in +DATA diskgroup.

SQL> alter tablespace temp drop tempfile ‘/data_TALIPDB/temp01.dbf’;
SQL> alter tablespace temp add tempfile ‘+DATA’ size 1024M;

Friday, September 21, 2012

RMAN Block Media Recovery

From Oracle 9i onwards you can use RMAN to recover only blocks while database is up and running.
This could possibly save hours and hours of recovery time as a full database restore is not necessary.
Error reported by user pointing to block corruption.

POPULATE_MACSDATA - ORA-01578: ORACLE data block corrupted (file # 48, block # 142713)
ORA-01110: data file 48: '/hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf'
ORA-02063: preceding 2 lines from MODSL_MACSL_LINK
File name : /hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf

Check first if the there is only one(few) blocks corrupted or most of the blocks are corrupted.

macsl:/opt/oracle/admin/macsl/bdump>
Issue command below at UNIX prompt.

dbv file=/hqlinux08db06/ORACLE/macsl/MACSDAT_2006_06.dbf BLOCKSIZE=8192 LOGFILE=test.log

DBV-00200: Block, dba 201469305, already marked corrupted

SQL> Select * from v$database_block_corruption;

You will get block number corrupt.
Ex: block 142713.

After that LOGIN TO RMAN.

rman target / catalog rman10/rman10@rman10p
RMAN> BLOCKRECOVER DATAFILE 48 BLOCK 142713;

V$database_block_corruption is the view to check the list of corrupted blocks.

If you have multiple block list as corrupt, You can use single command to recover them.

RMAN> BLOCKRECOVER corruption list;

Thursday, September 20, 2012

Table level Recovery using Flashback Table

---------------------------------------------------Recover Dropped Table from Recyclebin using Flashback Table-------------------------

Oracle Flashback Table enables you to restore a table to its state as of a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. In many

cases, Oracle Flashback Table eliminates the need for you to perform more complicated point-in-time recovery operations.

Oracle Flashback Table:

Restores all data in a specified table to a previous point in time described by a timestamp or SCN.

Performs the restore operation online.

Automatically maintains all of the table attributes, such as indexes, triggers, and constraints that are necessary for an application to function with the flashed-back table.

Maintains any remote state in a distributed environment. For example, all of the table modifications required by replication if a replicated table is flashed back.

Maintains data integrity as specified by constraints. Tables are flashed back provided none of the table constraints are violated. This includes any referential integrity constraints specified between a table included in the FLASHBACK

TABLE statement and another table that is not included in the FLASHBACK TABLE statement.

Even after a flashback operation, the data in the original table is not lost. You can later revert to the original state.

FLASHBACK TABLE <table_name> TO BEFORE DROP;

Some other variations of the flashback database command include.

FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;

--------------------------------------------------Recover deleted Table Data from Recyclebin using Flashback Table---------------------

On Oracle Database 11g (10gR2...), we can rewind one or more tables back to their contents at a previous time without affecting other database objects.

Before we use Flashback Table, We must enable row movement on the table. because rowids will change after the flashback.

Example: Flashback the table back to previous time using SCN

select count(*) from LDBO.test;
COUNT(*)
----------
68781

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1584494

SQL> delete from LDBO.test where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
18781

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1587106

Enable row movement:

SQL> alter table LDBO.test enable row movement;

Table altered.

SQL> FLASHBACK TABLE LDBO.test to scn 1584494;

Flashback complete.

SQL> select count(*) from LDBO.test;

QL> alter table LDBO.test disable row movement;

Table altered.

We can rewind the table back to previous time using timestamp:

SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2009/08/30 17:01:09

SQL> delete from LDBO.test ;

68781 rows deleted.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
2009/08/30 17:03:18

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
0

SQL> alter table LDBO.test enable row movement;

Table altered.

SQL> flashback table LDBO.test to timestamp TO_TIMESTAMP('2009/08/30 17:01:09','YYYY/MM/DD HH24:MI:SS');

Flashback complete.

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
68781

--------------------------------------------------------------------------------------------Flashback Table recover table to different table----------------------

FLASHBACK TABLE test TO BEFORE DROP RENAME TO test2;

flashback table LDBO.test to timestamp TO_TIMESTAMP('2009/08/30 17:01:09','YYYY/MM/DD HH24:MI:SS') RENAME TO test2;

Friday, September 14, 2012

RMAN Recovery Scenarios

RMAN Backup and Recovery Scenarios

Complete Closed Database Recovery. System tablespace is missing

If the system tablespace is missing or corrupted the database cannot be started up so a complete closed database recovery must be performed.
Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted system datafile to its original location, ie:
cp -p /usr/backup/RMAN/system01.dbf /usr/oradata/u01/IASDB/system01.dbf
2. startup mount;
3. recover datafile 1;
4. alter database open;

Complete Open Database Recovery. Non system tablespace is missing

If a non system tablespace is missing or corrupted while the database is open, recovery can be performed while the database remain open.
Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted datafile to its original location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u01/IASDB/user01.dbf
2. alter tablespace <tablespace_name> offline immediate;
3. recover tablespace <tablespace_name>;
4. alter tablespace <tablespace_name> online;

Complete Open Database Recovery (when the database is initially closed).Non system tablespace is missing

If a non system tablespace is missing or corrupted and the database crashed,recovery can be performed after the database is open.
Pre requisites: A closed or open database backup and archived logs.
1.   startup; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)
2.   Use OS commands to restore the missing or corrupted datafile to its original location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u01/IASDB/user01.dbf
3.   alter database datafile 6 offline; (tablespace cannot be used because the database is not open)
4.   alter database open;
5.   recover datafile 6;
6.   alter tablespace <tablespace_name> online;

Recovery of a Missing Datafile that has no backups (database is open).

If a non system datafile that was not backed up since the last backup is missing,recovery can be performed if all archived logs since the creation of the missing datafile exist.
Pre requisites: All relevant archived logs.
1.   alter tablespace <tablespace_name> offline immediate;
2.   alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf’;
3.   recover tablespace <tablespace_name>;
4.   alter tablespace <tablespace_name> online;
If the create datafile command needs to be executed to place the datafile on a location different than the original use:
alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf’ as ‘/user/oradata/u02/IASDB/newdata01.dbf’

Restore and Recovery of a Datafile to a different location.

If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed.
Pre requisites: All relevant archived logs.
1.    Use OS commands to restore the missing or corrupted datafile to the new location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u02/IASDB/user01.dbf
2.    alter tablespace <tablespace_name> offline immediate;
3.    alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/IASDB/user01.dbf’ to ‘/user/oradata/u02/IASDB/user01.dbf’;
4.    recover tablespace <tablespace_name>;
5.    alter tablespace <tablespace_name> online;

Control File Recovery

Always multiplex your controlfiles. Controlfiles are missing, database crash.
Pre requisites: A backup of your controlfile and all relevant archived logs.
1.    startup; (you get ora-205, missing controlfile, instance start but database is not mounted)
2.    Use OS commands to restore the missing controlfile to its original location:
cp -p /usr/backup/RMAN/control01.dbf /usr/oradata/u01/IASDB/control01.dbf
cp -p /usr/backup/RMAN/control02.dbf /usr/oradata/u01/IASDB/control02.dbf
3.    alter database mount;
4.    recover automatic database using backup controlfile;
5.    alter database open resetlogs;
6.    make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Incomplete Recovery, Until Time/Sequence/Cancel

Incomplete recovery may be necessaire when an archived log is missing, so recovery can only be made until the previous sequence, or when an important object was dropped, and recovery needs to be made until before the object was dropped.
Pre requisites: A closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.
1. If the database is open, shutdown abort
2. Use OS commands to restore all datafiles to its original locations:
cp -p /usr/backup/RMAN/u01/*.dbf /usr/oradata/u01/IASDB/
cp -p /usr/backup/RMAN/u02/*.dbf /usr/oradata/u01/IASDB/
cp -p /usr/backup/RMAN/u03/*.dbf /usr/oradata/u01/IASDB/
cp -p /usr/backup/RMAN/u04/*.dbf /usr/oradata/u01/IASDB/
etc…
3. startup mount;
4. recover automatic database until time ’2004-03-31:14:40:45′;
5. alter database open resetlogs;
6. make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.Alternatively you   may use instead of until time, until sequence or until cancel:
recover automatic database until sequence 120 thread 1; OR
recover database until cancel;

Rman Recovery Scenarios

Rman recovery scenarios require that the database is in archive log mode, and that backups of datafiles, control files and archived redolog files are made using Rman. Incremental Rman backups may be used also.
Rman can be used with the repository installed on the archivelog, or with a recovery catalog that may be installed in the same or other database.
Configuration and operation recommendations:
Set the parameter controlfile autobackup to ON to have with each backup a
controlfile backup also:
configure controlfile autobackup on;
set the parameter retention policy to the recovery window you want to have,
ie redundancy 2 will keep the last two backups available, after executing delete obsolete commands:
configure retention policy to redundancy 2;
Execute your full backups with the option ‘plus archivelogs’ to include your archivelogs with every backup:
backup database plus archivelog;
Perform daily maintenance routines to maintain on your backup directory the number of backups you need only:
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete backup;
To work with Rman and a database based catalog follow these steps:
1. sqlplus /
2. create tablespace repcat;
3. create user rmuser identified by rmuser default tablespace repcat temporary tablespace temp;
4. grant connect, resource, recovery_catalog_owner to rmuser
5. exit
6. rman catalog rmuser/rmuser          # connect to rman catalog as the rmuser
7. create catalog                     # create the catalog
8. connect target /                   #

Complete Closed Database Recovery. System tablespace is missing

In this case complete recovery is performed, only the system tablespace is missing,so the database can be opened without reseting the redologs.
1. rman target /
2. startup mount;
3. restore database;
4. recover database;
5. alter database open;

Complete Open Database Recovery. Non system tablespace is missing,database is up

1.   rman target /
2.   sql ‘alter tablespace <tablespace_name> offline immediate’;
3.   restore datafile 3;
4.   recover datafile 3;
5.   sql ‘alter tablespace <tablespace_name> online’;

Complete Open Database Recovery (when the database is initially closed).Non system tablespace is missing

A user datafile is reported missing when tryin to startup the database. The datafile can be turned offline and the database started up. Restore and recovery are performed using Rman. After recovery is performed the datafile can be turned online again.
1.    sqlplus /nolog
2.    connect / as sysdba
3.    startup mount
4.    alter database datafile ‘<datafile_name>’ offline;
5.    alter database open;
6.    exit;
7.    rman target /
8.    restore datafile ‘<datafile_name>’;
9.    recover datafile ‘<datafile_name>’;
10.   sql ‘alter tablespace <tablespace_name> online’;

Recovery of a Datafile that has no backups (database is up).

If a non system datafile that was not backed up since the last backup is missing,recovery can be performed if all archived logs since the creation of the missing datafile exist. Since the database is up you can check the tablespace name and put it offline. The option offline immediate is used to avoid that the update of the datafile header.
Pre requisites: All relevant archived logs.
1.    sqlplus ‘/ as sysdba’
2.    alter tablespace <tablespace_name> offline immediate;
3.    alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf;
4.    exit
5.    rman target /
6.    recover tablespace <tablespace_name>;
7.    sql ‘alter tablespace <tablespace_name> online’;
If the create datafile command needs to be executed to place the datafile on a location different than the original use:
alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf’ as ‘/user/oradata/u02/IASDB/newdata01.dbf’

Restore and Recovery of a Datafile to a different location. Database is up.

If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed.
Pre requisites: All relevant archived logs, complete cold or hot backup.
1.    Use OS commands to restore the missing or corrupted datafile to the new location, ie:
cp -p /usr/backup/RMAN/user01.dbf /usr/oradata/u02/IASDB/user01.dbf
2.    alter tablespace <tablespace_name> offline immediate;
3.    alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/IASDB/user01.dbf’ to ‘/user/oradata/u02/IASDB/user01.dbf’;
4.    rman target /
5.    recover tablespace <tablespace_name>;
6.    sql ‘alter tablespace <tablespace_name> online’;

Control File Recovery

Always multiplex your controlfiles. If you loose only one controlfile you can replace it with the one you have in place, and startup the Database. If both controlfiles are missing, the database will crash.
Pre requisites: A backup of your controlfile and all relevant archived logs. When using Rman alway set configuration parameter autobackup of controlfile to ON. You will need the dbid to restore the controlfile, get it from the name of the backed up controlfile.It is the number following the ‘c-’ at the start of the name.
1.   rman target /
2.   set dbid <dbid#>
3.   startup nomount;
4.   restore controlfile from autobackup;
5.   alter database mount;
6.   recover database;
7.   alter database open resetlogs;
8.   make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Incomplete Recovery, Until Time/Sequence/Cancel

Incomplete recovery may be necessaire when the database crash and needs to be recovered, and in the recovery process you find that an archived log is missing. In this case recovery can only be made until the sequence before the one that is missing.
Another scenario for incomplete recovery occurs when an important object was dropped or incorrect data was committed on it.
In this case recovery needs to be performed until before the object was dropped.
Pre requisites: A full closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.
1.   If the database is open, shutdown it to perform full restore.
2.   rman target \
3.   startup mount;
4.   restore database;
5.   recover database until sequence 8 thread 1; # you must pass the thread, if a single instance will always be 1.
6. alter database open resetlogs;
7. make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.Alternatively you may use instead of until sequence, until time, ie: ’2012-01-04:01:01:10′.

RMAN scenarios

http://itcareershift.com/blog1/2010/11/25/real-life-oracle-dba-scenarios-using-rman-backups-and-troubleshooting-new-oracle-dba-career/
http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmadvre.htm
http://www.reachdba.com/showthread.php?453-RMAN-All-major-Restoration-and-Recovery-Scenarios

Note: When database is in open state then we can not delete datafiles from OS level (File is being used by another User)

--------------------------------------------------------------DataFile removed physcially from OS level and no Backup of that datafile------------------------------------

1) rman backup taken 01-01-12
2) add datafile and add some tables and data on 02-01-12
3) remove datafile from OS level on 02-01-12 no backup till now
4) alter tablespace USR offline;
ORA-01116: error in opening database file 11
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
5) alter tablespace drtbs offline immediate;
select TABLESPACE_NAME,STATUS from dba_tablespaces;
6) select file_id, file_name from dba_data_files where Tablespace_name='USR';
7) RMAN TARGET
8) rman > list of backup datafile 11;
9) list backup of datafile 12; ---- no exist because no backup
10) RMAN> restore tablespace USR;
11) RMAN> recover tablespace USR;
select file_id, file_name from dba_data_files where Tablespace_name='USR';

--------------------------------------------------------------------------Loss of Controlfile-----------------------------

1) select name from v$controlfile;
2) remove controlfile physcially from OS level
3) alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;

Since we are not using a RMAN catalog we need to set the DBID

RMAN> set dbid=2415549446;

executing command: SET DBID

Restore the controlfile

RMAN> run {
restore controlfile from autobackup;
}

error RMAN-06172 because when check show all; controlfile autoback is not present...also not able to configure again becuase of nomount state

set controlfile autobackup format for device type disk to '/orabkp\%F';
restore controlfile from autobackup;

RMAN> alter database mount;
RMAN> recover database;
SQL> alter database open resetlogs;

-------------------------------------recover and open the database if the archive log required for recovery is miss

SQL> recover database until cancel using backup controlfile;
CANCEL
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent

1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) recover database until cancel using backup controlfile;
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”

------------------------------------------Redo log removed from OS level-----------------------------------

select member from v$Logfile;

rm redo*.log

If one or all of the online redo logfiles are delete then the database hangs and in the alert log file
ARC1: Failed to archive thread 1 sequence 10 (0)

SQL> shutdown immediate;
SQL> startup mount;

SELECT GROUP#,SEQUENCE#,STATUS,FIRST_CHANGE# from v$log;

GROUP# SEQUENCE# STATUS FIRST_CHANGE#
--------- ---------- ---------------- -------------
1 10 CURRENT 8.6056E+10
3 9 INACTIVE 8.6056E+10
2 8 INACTIVE 8.6056E+10

RMAN TARGET /
RMAN> run {
2> set until sequence 10;
3> restore database;
4> recover database;
5> alter database open resetlogs;
6> }
EXIT

The recovery process creates the online redo logfiles at the operating system level also.
Since we have done an incomplete recover with open resetlogs, we should take a fresh complete backup of the database.

NOTE: Please make sure you remove all the old archived logfiles from the archived area.

------------------------------------------------------Drop tablespace by mistake (drop tablespace test including contents and datafiles;)------Point inTime

DBA realized the mistake;

He will refer alert log for the exact timing when tablespace was dropped.

-------Alert log--------------

Sun Feb 4 10:59:43 2012
drop tablespace test including contents and datafiles
Sun Feb 4 10:59:47 2012
Completed: drop tablespace test including contents and datafiles

SQL> shutdown abort
rman target /

RMAN> RUN
{
STARTUP NOMOUNT
SET UNTIL TIME "TO_DATE ('04-02-07 10:58:00', 'DD-MM-YY HH24:MI:SS')";
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

select name from v$tablespace;

-----------------------------------------------------------Complete loss of all database files including SPFILE using RMAN

ls -l
rm *.dbf
ls -l *.dbf
ls: *.dbf: No such file or directory
mv spfileopsdba.ora spfileopsdba.org
ls -lt spfile* *SPFILE REMOVED

Database Details
------------------
Database Name=OPSDBA
Machine Name=ITLINUXDEVBLADE07
DBID=1499754868 (select dbid from v$database)
---------------------

Step 1: RECOVERY OF SPFILE

Create spfile.rcv as:
set dbid= 1499754868
run {
startup nomount force ;
};

rman target / catalog rman10/rman10@rman10p cmdfile=spfile.rcv

Now restore the spfile

set dbid=1499754868
run {
allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
restore spfile ;
release channel ch1 ;
}

Step 2: RESTORE OF CONTROLFILES

Same Steps as spfile with the restore command changed. So the new script is

set dbid=1499754868
run {
allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
restore controlfile ;
release channel ch1 ;
}

Step 3: RESTORE OF DATABASE
SQL> conn sys as sysdba
SQL> alter database mount;

Now get the log sequence number of the database from the catalog database:

select sequence# from rc_backup_redolog where db_name=’OPSDBA’;

RMAN> run {
2> allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
3> restore database ;
4> recover database until logseq=6; -- GOT FROM THE ABOVE QUERY
5> release channel ch1 ;
6> }
7>

Step 4: alter database open resetlogs;

--------------------------- SYSTEM / SYSAUX / UNDO tablespace is missing

In this case complete recovery is performed, only the system tablespace is missing, so the database can be opened without resetlogs option.

$ rman target /
RMAN> startup mount;
RMAN> restore tablespace SYSTEM;
RMAN> recover tablespace SYSTEM;
RMAN> alter database open;

$ rman target /
RMAN> startup mount;
RMAN> restore tablespace SYSAUX;
RMAN> recover tablespace SYSAUX;
RMAN> alter database open;

------------------------------Non system tablespace is missing, database is up

$ rman target /
RMAN> sql ‘alter tablespace <tbs> offline immediate’ ;
RMAN> restore tablespace <tbs> ;
RMAN> recover tablespace <tbs> ;
RMAN> sql ‘alter tablespace <tbs> online’ ;

To restore/recover only datafile(s)
$ rman target /

RMAN>. sql 'alter database datafile <file#> offline';
RMAN> restore datafile <file#>;
RMAN> recover datafile <file#>;
RMAN> sql 'alter database datafile <file#> online' ;

-------------------------------Non system tablespace is missing,database is closed
sqlplus “/ as sysdba “
startup mount
alter database datafile <file#> offline;
alter database open;
exit;

$rman target /
RMAN> restore datafile <file#>;
RMAN> recover datafile <file#>;
RMAN> sql 'alter tablespace <tablespace_name> online';

-----------------------restore a tablespace to a new location
rman target / catalog rman/rman@rcat
run {
allocate channel ch1 type disk;
sql 'ALTER TABLESPACE USERS OFFLINE IMMEDIATE';
set newname for datafile '/disk1/oracle/users_1.dbf' to '/disk2/oracle/users_1.dbf';
restore tablespace users;
# make the control file recognize the restored file as current
switch datafile all;
}

RMAN> recover tablespace USERS;
RMAN> sql 'alter tablespace USERS online';

-------------------Recovery of a Datafile that has no backups (database is up)
If a non system datafile that was not backed up since the last backup, is missing, recovery can be performed if all archived logs since the creation of the missing datafile exist.

Pre requisites: All relevant archived logs.

$ rman target /
RMAN> sql ‘alter database datafile <file#> offline’;
RMAN> restore datafile <file#> ;
-- no need to create a blank file, restore command takes care of that.
RMAN> recover datafile <file#>;
RMAN> sql 'alter database datafile <file#> online';

-------------------------------Recovering After the Loss of All Members of an Online Redo Log Group

If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.
If the damaged log group is inactive, then it is not needed for crash recovery; if it is active, then it is needed for crash recovery.
SQL> startup mount

Case-1 If the group is INACTIVE
Then it is not needed for crash recovery
Clear the archived or unarchived group. (For archive status, check in v$log)

1.1 Clearing Inactive, Archived Redo

alter database clear logfile group 1 ;
alter database open ;

1.2 Clearing Inactive, Not-Yet-Archived Redo

alter database clear unarchived logfile group 1 ;
OR
(If there is an offline datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared, and there is no copy of it. )

alter database clear unarchived logfile group 1 unrecoverable datafile;

Take a complete backup of database.

And now open database:
alter database open ;

Case-2 If the group is ACTIVE

Restore backup and perform an incomplete recovery.
And open database using resetlogs
alter database open resetlogs;

Case-3 If the group is CURRENT

Restore backup and perform an incomplete recovery.
And open database using resetlogs
alter database open resetlogs;

--------------------------
1) OPEN REDO01.LOG (INACTIVE) AND EDIT AND CORRUPT IT

SQL> ALTER SYSTEM SWITCH LOGFILE; -------HANG
alter database clear logfile group 1 ;

ORA-00350: log 1 of instance rkangel (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: 'G:\RKANGEL\REDO01.LOG'

SQL> SHUT IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
SQL> ALTER DATABASE OPEN;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;

-------------2) OPEN REDO02.LOG (ACTIVE) AND EDIT AND CORRUPT IT
SQL> ALTER SYSTEM SWITCH LOGFILE; -------HANG
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL> STARTUP
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size 2267184 bytes
Variable Size 4563404752 bytes
Database Buffers 1828716544 bytes
Redo Buffers 19292160 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'G:\RKANGEL\REDO02.LOG'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 52429318)

ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
alter database clear logfile group 2 unrecoverable datafile;
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance rkangel (thread 1)
ORA-00312: online log 2 thread 1: 'G:\RKANGEL\REDO02.LOG'

-----note: For ACTIVE or CURRENT logfile
Restore backup and perform an incomplete recovery.
And open database using resetlogs
alter database open resetlogs;

1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) recover database until cancel using backup controlfile;
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”

--------------------------------------Recovering a NOARCHIVELOG Database
Restore of a database running in NOARCHIVELOG mode is similar to restore of a database in ARCHIVELOG mode. The main differences are:

Only consistent backups can be used in restoring a database in NOARCHIVELOG mode.

Media recovery is not possible because no archived redo logs exist.

When recovering a NOARCHIVELOG database, specify the NOREDO option on the RECOVER command to indicate that RMAN should not attempt to apply archived redo logs.

----------------------------------------------------------------Remove Temporary datafile from OS level---------------------------------

shut immediate
Remove Temporary datafile from OS level
startup
auotmatically create temp

shut immediate
startup mount
Remove Temporary datafile from OS level
alter database open;
auotmatically create temp

----------------------------------------------------------------Remove UNDO datafile from OS level----------------------------------------

shut immediate
startup mount
Remove UNDO datafile from OS level
alter database open;

ERROR at line 1:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'G:\RKANGEL\UNDOTBS01.ORA'

RMAN> restore tablespace UNDOTBS1;
RMAN> recover tablespace UNDOTBS1;
RMAN> alter database open;

----------------------------------------How to recover dropped / deleted table using RMAN backup----------------------

restore backup on to the UAT machine
export the table from UAT
import into LIVE

1) incomplete recovery ( just before to the drop table) can recover your table from RMAN backup.
or
2) You can recover /duplcate the database to another database with RMAN backup
Export the concerned table from the restored/duplicated database to the database you want.

3) use flashback (in oracle 10g) for indivisual table recovery purpose.
Oracle 10g makes life easier with the ability to recover a dropped table similar to recovering a file from a Windows Recycle Bin.
And in case if this on 10g,
Eg: You dropped the DEPT table, which belongs to the USER_DATA tablespace
You can use the following command to recover a dropped table:

FLASHBACK TABLE <table_name> TO BEFORE DROP;

4) RMAN TSPITR is most useful for recovering the following:
An erroneous DROP TABLE or TRUNCATE TABLE statement
A table that has become logically corrupted
An incorrect batch job or other DML statement that has affected only a subset of the database
A logical schema to a point different from the rest of the physical database when multiple schemas exist in separate tablespaces of one physical database

--------------------------------------------------------Point in time recovery using RMAN (until a log sequence number) ----------------delete some data from table by mistake

Someone delete all data from a table at 1:47 PM (delete from myobjects) SELECT * FROM DBA_AUDIT_TRAIL;

windows dir d:\archive
20/09/2012 01:44 PM 1,024 ARC0000000010_0794497245.0001
20/09/2012 01:47 PM 41,461,248 ARC0000000011_0794497245.0001

archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/ORACLE/opsdba/arch
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
--Note the current log sequence number (22)

We need to determine the log sequence we need to recover until
select sequence#,first_change#, to_char(first_time,'HH24:MI:SS') from v$log order by 3

SEQUENCE# FIRST_CHANGE# TO_CHAR(
--------- ------------- --------
21 8.6056E+10 13:48:16
22 8.6056E+10 13:48:22
23 8.6056E+10 13:48:29

Log sequence 21 was first written to at 1:48 PM so we should recover to a log sequence before this – i.e sequence# 10 (from window dir )

SQL> shutdown immediate;
SQL> startup mount;
RMAN> run {
set until sequence=10; >>> add one to the sequence number we have to recover until
restore database;
recover database;
}
alter database open resetlogs;

ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: 'G:\RKANGEL\SYSTEM01.ORA'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/20/2012 14:15:38
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: 'G:\RKANGEL\SYSTEM01.ORA'

SQL> ALTER DATABASE DATAFILE 1 ONLINE;
SQL> RECOVER DATABASE;

ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

select count(*) from myobjects;

---------------------------------------------------Recover Dropped Table from Recyclebin using Flashback Table-------------------------

Oracle Flashback Table enables you to restore a table to its state as of a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. In many cases, Oracle Flashback Table eliminates the need for you to perform more complicated point-in-time recovery operations.

Oracle Flashback Table:

Restores all data in a specified table to a previous point in time described by a timestamp or SCN.

Performs the restore operation online.

Automatically maintains all of the table attributes, such as indexes, triggers, and constraints that are necessary for an application to function with the flashed-back table.

Maintains any remote state in a distributed environment. For example, all of the table modifications required by replication if a replicated table is flashed back.

Maintains data integrity as specified by constraints. Tables are flashed back provided none of the table constraints are violated. This includes any referential integrity constraints specified between a table included in the FLASHBACK TABLE statement and another table that is not included in the FLASHBACK TABLE statement.

Even after a flashback operation, the data in the original table is not lost. You can later revert to the original state.

FLASHBACK TABLE <table_name> TO BEFORE DROP;

Some other variations of the flashback database command include.

FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;

--------------------------------------------------Recover deleted Table Data from Recyclebin using Flashback Table---------------------

On Oracle Database 11g (10gR2...), we can rewind one or more tables back to their contents at a previous time without affecting other database objects.

Before we use Flashback Table, We must enable row movement on the table. because rowids will change after the flashback.

Example: Flashback the table back to previous time using SCN

select count(*) from LDBO.test;
COUNT(*)
----------
68781

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1584494

SQL> delete from LDBO.test where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
18781

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
1587106

Enable row movement:

SQL> alter table LDBO.test enable row movement;

Table altered.

SQL> FLASHBACK TABLE LDBO.test to scn 1584494;

Flashback complete.

SQL> select count(*) from LDBO.test;

QL> alter table LDBO.test disable row movement;

Table altered.

We can rewind the table back to previous time using timestamp:

SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2009/08/30 17:01:09

SQL> delete from LDBO.test ;

68781 rows deleted.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
2009/08/30 17:03:18

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
0

SQL> alter table LDBO.test enable row movement;

Table altered.

SQL> flashback table LDBO.test to timestamp TO_TIMESTAMP('2009/08/30 17:01:09','YYYY/MM/DD HH24:MI:SS');

Flashback complete.

SQL> select count(*) from LDBO.test;

COUNT(*)
----------
68781

--------------------------------------------------------------------------------------------Flashback Table recover table to different table----------------------

FLASHBACK TABLE test TO BEFORE DROP RENAME TO test2;

flashback table LDBO.test to timestamp TO_TIMESTAMP('2009/08/30 17:01:09','YYYY/MM/DD HH24:MI:SS') RENAME TO test2;

Monday, April 23, 2012

Job Scheduler for RMAN backup

1)
RMAN TARGET SYS/.....@KSH1213SRV
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'F:\RMANBACKUP\%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:\RMANBACKUP\%U';

or

-----------------------------Advance setting with RMAN maintenace command----------------------------------------------------

# BACKUP.rcv
# Configure RMAN settings
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%n_cf_%T_%s_%F.bck';
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DEFAULT DEVICE TYPE TO disk;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '%n_df_%T_%s.bck';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '%n_df_%T_%s.bck';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '%n_df_%T_%s.bck';
CONFIGURE CHANNEL 4 DEVICE TYPE DISK FORMAT '%n_df_%T_%s.bck';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '%n_sn_%T_%s.bck';

# Perform backup of database and archivelogs, deleting backed up archivelogs
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

# Maintenance commands for crosschecks and deleting expired backups
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
# Cleaning up to save space.
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
exit

------------------------------------------------------------------------------------------------------------------------------------------------
2)
create user rman identified by .........
Temporary tablespace temporary
Default tablespace usr
Quota unlimited on usr;

Grant recovery_catalog_owner,connect, resource to rman;
Grant Create type to rman;

3)

RMAN TARGET SYS/.....@KSH1213SRV CATALOG RMAN/....@KSH1213SRV
CREATE CATALOG;
REGISTER DATABASE;

CREATE SCRIPT RMANBACKUP
{
BACKUP DATABASE;
}

4)
BEGIN
dbms_scheduler.create_job(
job_name => 'RMAN_BACKUP',
job_type => 'EXECUTABLE',
job_action => 'rman target sys/.......@KSH1213SRV CATALOG RMAN/..........@KSH1213SRV SCRIPT RMANBACKUP',
start_date => '01-APR-12 07:00.00.00 PM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
enabled => TRUE,
comments => 'BACKUP RMAN');
END;
/

EXEC dbms_scheduler.RUN_JOB('RMAN_BACKUP');

Sunday, March 13, 2011

Sunday, March 6, 2011

Incremental Backup

RMAN Incremental Backup

Level 0 (same as full backup but used for incremental backup level1)
backup incremental level 0 as compressed backupset database FORMAT 'D:\archive1011\%U';

A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

backup incremental level 1 as compressed backupset database FORMAT 'D:\archive1011\%U';
backup incremental level 1 as compressed backupset database FORMAT 'D:\archive1011\%U';
backup incremental level 1 as compressed backupset database FORMAT 'D:\archive1011\%U';
backup incremental level 1 as compressed backupset database FORMAT 'D:\archive1011\%U';

Cumulative incremental backups reduce the work needed for a restore by ensuring that you only need one incremental backup from any particular level. Cumulative backups require more space and time than differential backups, however, because they duplicate the work done by previous backups at the same level.

cumulative incremental backup (backup after level 0)
backup cumulative incremental level 1 as compressed backupset database FORMAT 'D:\archive1011\%U';

Sunday, February 20, 2011

RMAN :Restore Different Server, Database folder on different Directory, Backup Piece on different location

Scenario
Restore on Different Server, Database folders are on different Directory, Backup Piece on different location
Previous Server Prod, E:\snsd1011\, E:\archive1011\
New Server UAT D:\snsd1011\, D:\archive1011\

-----install oracle server10.2.0.3 without create startup db

------
mkdir D:\oracle\product\10.2.0\admin\sns1011\adump
mkdir D:\oracle\product\10.2.0\admin\sns1011\bdump
mkdir D:\oracle\product\10.2.0\admin\sns1011\cdump
mkdir D:\oracle\product\10.2.0\admin\sns1011\dpdump
mkdir D:\oracle\product\10.2.0\admin\sns1011\pfile
mkdir D:\oracle\product\10.2.0\admin\sns1011\udump
mkdir D:\archive1011\sns1011\arch
copy initsns1011.ora, tnsnames.ora,listener.ora to destination location and change paramater accordingly.

------
D:\>
oradim -new -sid sns1011 -SRVC OracleServicesns1011 -intpwd oracle -MAXUSERS 5 -STARTMODE auto -PFILE D:\oracle\product\10.2.0\db_1\database\initsns1011.ORA

----
lsnrctl stop
lsnrctl start
lsnrctl services
tnsping sns1011
----
sqlplusw sys/oracle@sns1011srv as sysdba

SQL>startup nomount pfile='D:\oracle\product\10.2.0\db_1\database\initsns6.ora';

-----
cmd
c:>
SET ORACLE_SID=sns6
RMAN TARGET SYS/linux@SNS1011SRV
shutdown immediate;
startup nomount;

RMAN>RESTORE CONTROLFILE FROM 'D:\archive1011\SNS1011\C-3554091374-20100603-00';

RMAN > SET DBID=3554091374

alter database MOUNT;

---------
RMAN>
list backup;
CROSSCHECK backup of database;
delete backup of database;
delete expired backup;
list backup;
delete backupset 146;
CROSSCHECK backup of controlfile;
delete backup of controlfile;
CROSSCHECK archivelog all;
delete force obsolete;
delete expired archivelog all;

---------

RMAN>CATALOG START WITH 'D:\archive1011\sns1011';

or

catalog backuppiece
'D:\archive1011\sns1011\RMANBACKUP_DB_SNS1011_S_156_P_1_T_721740034',
'D:\archive1011\sns1011\RMANBACKUP_DB_SNS1011_S_156_P_2_T_721740034',
'D:\archive1011\sns1011\RMANBACKUP_DB_SNS1011_S_156_P_3_T_721740034',
'D:\archive1011\sns1011\RMANBACKUP_DB_SNS1011_S_156_P_4_T_721740034',
'D:\archive1011\sns1011\RMANBACKUP_DB_SNS1011_S_156_P_5_T_721740034',
'D:\archive1011\sns1011\RMANBACKUP_DB_SNS1011_S_156_P_6_T_721740034',
'D:\archive1011\sns1011\RMANBACKUP_DB_SNS1011_S_156_P_7_T_721740034'
;

---------

RUN{
set newname for datafile 1 TO 'D:\SNSD1011\SYSTEM01.ORA';
set newname for datafile 2 TO 'D:\SNSD1011\UNDOTBS01.ORA';
set newname for datafile 3 TO 'D:\SNSD1011\SYSAUX01.ORA';
set newname for datafile 4 TO 'D:\SNSD1011\INDX01.ORA';
set newname for datafile 4 TO 'D:\SNSD1011\USERS01.ORA';
set newname for tempfile 5 TO 'D:\SNSD1011\TEMP01.ORA';
}

---------
SQL>
alter database rename file 'e:\snsd1011\system01.ora' to 'd:\snsd1011\system01.ora';
alter database rename file 'e:\snsd1011\users01.ora' to 'd:\snsd1011\users01.ora';
alter database rename file 'e:\snsd1011\UNDOTBS01.ora' to 'd:\snsd1011\UNDOTBS01.ora';
alter database rename file 'e:\snsd1011\SYSAUX01.ora' to 'd:\snsd1011\SYSAUX01.ora';
alter database rename file 'e:\snsd1011\INDX01.ora' to 'd:\snsd1011\INDEX01.ora';
alter database rename file 'e:\snsd1011\TEMP01.ora' to 'd:\snsd1011\TEMP01.ora';

alter database rename file 'e:\snsd1011\redo01.ora' to 'd:\snsd1011\redo01.ora';
alter database rename file 'e:\snsd1011\redo02.ora' to 'd:\snsd1011\redo02.ora';
alter database rename file 'e:\snsd1011\redo03.ora' to 'd:\snsd1011\redo03.ora';

------------
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

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

Tuesday, June 8, 2010

Restore Database when source database(E:) and destination database location(D:) is different

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\archive1011\sns1011\%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\archive1011\sns1011\rmanbackup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 900 M; --if more than one backuppiece

RMAN> list backup of database;
RMAN> CROSSCHECK backup of database;
RMAN> delete expired backup;

RMAN> catalog backuppiece 'D:\archive1011\sns1011\RMANBACKUP_DB_SNS1011_S_149_P_1_T_720717287','D:\archive1011\sns1011\RMANBACKUP_DB_SNS1011_S_149_P_2_T_720717287';-----if more than one backpupiece

SQL> alter database rename file 'E:\SNSD1011\REDO01.ORA' TO 'D:\SNSD1011\REDO01.ORA' ;
SQL> alter database rename file 'E:\SNSD1011\REDO02.ORA' TO 'D:\SNSD1011\REDO02.ORA' ;
SQL> alter database rename file 'E:\SNSD1011\REDO03.ORA' TO 'D:\SNSD1011\REDO03.ORA' ;
SQL> alter database rename file 'E:\SNSD1011\REDO04.ORA' TO 'D:\SNSD1011\REDO04.ORA' ;
SQL> alter database rename file 'E:\SNSD1011\REDO05.ORA' TO 'D:\SNSD1011\REDO05.ORA' ;

run { set until sequence
set newname for datafile 1 to 'D:\SNSD1011\SYSTEM01.ORA' ;
set newname for datafile 1 to 'D:\SNSD1011\UNDOTBS01.ORA' ;
set newname for datafile 1 to 'D:\SNSD1011\SYSAUX01.ORA' ;
set newname for datafile 1 to 'D:\SNSD1011\INDX01.ORA' ;
set newname for datafile 1 to 'D:\SNSD1011\USERS01.ORA' ;

restore database;
switch datafile all;
recover database;
alter database open resetlogs; }

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

Oracle RMAN Duplicate Database / Clone Database

Configure The Network
We need to configure the network so that there is connectivity to the target
(source) database and to the database we want to create (the clone)
Listener file:
# listener.ora Network Configuration File:
c:\oracle\product\10.2.0\client_1/network/admin/listener.
ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = snsdup)
(ORACLE_HOME = c:\oracle\product\10.2.0\client_1)
(SID_NAME = snsdup)
)
(SID_DESC =
(GLOBAL_DBNAME = sns6)
(ORACLE_HOME = c:\oracle\product\10.2.0\client_1)
(SID_NAME = sns6)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = snsdup))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = sns6))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1521))
)
)

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

Tnsnames file:

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = snsdup)
)
)
sns6 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sns6)
)
)

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

Create A Password File For The New Database

[oracle@10.100.0.32 dbs]$ orapwd file=orapwdsnsdup password=oracle

-----------------
Create An Init.Ora For The New Database
Create the pfile using create pfile from spfile from the source database, then
edit it changing all occurrences of old database name to new name

*.audit_file_dest='/oracle/app/admin/snsdup/adump'
*.audit_trail='db'
*.compatible='10.0.2.3.0.0'
#*.control_files='’
*.db_block_size=8192
*.db_create_file_dest='/oracle/app/oradata'
*.db_domain=''
*.db_name='snsdup'
*.db_recovery_file_dest='/oracle/app/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=snsdupXDB)'
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
-----------------

Create The Admin Directory For The New Database
mkdir –p ../snsdup/adump

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

Shutdown And Startup Mount The Source Database
This is required if the source database is in no archivelog mode.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

------------------------
Startup Nomount The New Database

SQL> startup nomount
ORACLE instance started.

--------------
Connect To The Target (Source) And Auxiliary (New Clone) Databases Using Rman

[oracle@10.100.0.32 dbs]$ Rman target sys/oracle@sns6 auxiliary sys/oracle@snsdup

--------------
Execute The Duplicate Command

RMAN> DUPLICATE TARGET DATABASE TO snsdup FROM ACTIVE DATABASE;

--------

Remove The Old Pfile
The duplicate database created an spfile that has the new controlfile names in it, the old pfile, without controlfiles can be removed

[oracle@10.100.0.32 dbs]$ rm initsnsdup.ora

--------

Check The New Database
ORACLE_SID=snsdup
ORACLE_HOME=c:\oracle\product\10.2.0\client_1
[oracle@10.100.0.32 dbs]$ sqlplus / as sysdba

SQL> archive log list

SQL> select name from v$datafile;

SQL> show sga

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

Database Administrator

Monday, November 5, 2012

11g Migration using RMAN

Friday, September 21, 2012

RMAN Block Media Recovery

Thursday, September 20, 2012

Table level Recovery using Flashback Table

Friday, September 14, 2012

RMAN Recovery Scenarios

RMAN Backup and Recovery Scenarios

RMAN Backup and Recovery Scenarios

RMAN scenarios

Monday, April 23, 2012

Job Scheduler for RMAN backup

Sunday, March 13, 2011

RMAN Configuration Channel Allocation

Sunday, March 6, 2011

Incremental Backup

Sunday, February 20, 2011

RMAN :Restore Different Server, Database folder on different Directory, Backup Piece on different location

Tuesday, June 8, 2010

Restore Database when source database(E:) and destination database location(D:) is different

Oracle RMAN Duplicate Database / Clone Database

Followers

Labels