Oracle Database Upgrade from 10.2.0.1 to 10.2.0.3

Long day off makes me tired of being at home so thought to do something for my friends........

People, who are new to Oracle world think that database upgrade is a big deal go walk with, yes - it is if we talk about a production and mission critical systems. But once you will go through this article and implement it at our own then you would realize - yeah, it is not that big deal ;)

In this section I will describe how we apply an oracle platform upgrade patch step by step to upgrade the database from 10.2.0.1. To 10.2.0.3

Pre-requisites:

1. First of all, stop all the running oracle components:

 [oracle@ora10srv bin]$ ./emctl stop dbconsole

TZ set to Asia/Calcutta

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0

Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.

http://ora10srv:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 10g Database Control ...

 ...  Stopped.

[oracle@ora10srv bin]$ lsnrctl stop listener

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

The command completed successfully

LSNRCTL>

 [oracle@ora10srv bin]$ ./isqlplusctl stop

iSQL*Plus 10.2.0.1.0

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Stopping iSQL*Plus ...

iSQL*Plus stopped.

[oracle@ora10srv bin]$ sqlplus /nolog;

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 28 21:45:47 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2. Second step is to take a backup of existing Oracle home for restore in case of failure in upgrade process to get our database operation back to its previous state for normal business operations.

 [oracle@ora10srv bin]$ echo $ORACLE_HOME

/u01/app/oracle/product/10.2.0/db_1

$tar –zcvf /u01/app//oracle/OraHomeBkp.tar.gz /u01/app/oracle/product/10.2.0/db_1

3. Take a database cold backup now.

Copy the following list of physical database file sets

a.       Datafile

b.      Control files

c.       Redo log files

d.      Pfile & password file can optionally be copies to backup location as well.

4. Extract the downloaded database patch to a /tmp or other location on the database server.

[oracle@ora10srv bin]$ Unzip p5337014_10203_LINUX.zip

It will unzip the archive to a folder named ‘Disk1’

Now navigate to the extracted folder “Disk1” and start the installer.

./runInstaller

Click on the Next Button.

  

Specify the Oracle Home Path and its Name – Click Next.

Click on Install.

Now login as root user on a separate terminal and run the root.sh script and then click on OK button.

 Click on Exit.

5. Start the Database in upgrade mode now.

6. Run the Database Upgrade Pre-Requisite Information Tool to get and implement the recommendation before actual upgrade script execution.

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

Oracle Database 10.2 Upgrade Information Utility    10-28-2014 22:31:10

.**********************************************************************

Database:

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

--> name:       ORA10DB

--> version:    10.2.0.1.0

--> compatible: 10.2.0.1.0

--> blocksize:  8192

.**********************************************************************

Tablespaces: [make adjustments in the current environment]

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

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 491 MB

.... AUTOEXTEND additional space required: 11 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 13 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 235 MB

.... AUTOEXTEND additional space required: 5 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 58 MB

.... AUTOEXTEND additional space required: 38 MB

--> EXAMPLE tablespace is adequate for the upgrade.

.... minimum required size: 69 MB

.

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

Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]

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

WARNING: --> "streams_pool_size" needs to be increased to at least 50331648

.**********************************************************************

Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]

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

-- No renamed parameters found. No changes are required.

.**********************************************************************

Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]

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

-- No obsolete parameters found. No changes are required

.**********************************************************************

Components: [The following database components will be upgraded or installed]

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

--> Oracle Catalog Views         [upgrade]  VALID

--> Oracle Packages and Types    [upgrade]  VALID

--> JServer JAVA Virtual Machine [upgrade]  VALID

--> Oracle XDK for Java          [upgrade]  VALID

--> Oracle Java Packages         [upgrade]  VALID

--> Oracle Text                  [upgrade]  VALID

--> Oracle XML Database          [upgrade]  VALID

--> Oracle Workspace Manager     [upgrade]  VALID

--> Oracle Data Mining           [upgrade]  VALID

--> OLAP Analytic Workspace      [upgrade]  VALID

--> OLAP Catalog                 [upgrade]  VALID

--> Oracle OLAP API              [upgrade]  VALID

--> Oracle interMedia            [upgrade]  VALID

--> Spatial                      [upgrade]  VALID

--> Expression Filter            [upgrade]  VALID

--> EM Repository                [upgrade]  VALID

--> Rule Manager                 [upgrade]  VALID

.

PL/SQL procedure successfully completed.

Note: - Above you can see that Database upgrade Pre-Requisite Tool has given the recommendation for Streams_pool_size parameter to set to the recommended size so now we will alter that parameter with the recommended size and will run the pre-requisite tool again until it has no more recommendations to implement.

SQL> alter system set streams_pool_size=50331648 scope=both;

System altered.

Run the Pre-requisite Tool again:

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

Oracle Database 10.2 Upgrade Information Utility    10-28-2014 22:37:14

.**********************************************************************

Database:

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

--> name:       ORA10DB

--> version:    10.2.0.1.0

--> compatible: 10.2.0.1.0

--> blocksize:  8192

.**********************************************************************

Tablespaces: [make adjustments in the current environment]

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

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 491 MB

.... AUTOEXTEND additional space required: 11 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 13 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 235 MB

.... AUTOEXTEND additional space required: 5 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 58 MB

.... AUTOEXTEND additional space required: 38 MB

--> EXAMPLE tablespace is adequate for the upgrade.

.... minimum required size: 69 MB

.**********************************************************************

Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]

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

-- No update parameter changes are required.

.**********************************************************************

Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]

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

-- No renamed parameters found. No changes are required.

.

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

Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]

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

-- No obsolete parameters found. No changes are required

.

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

Components: [The following database components will be upgraded or installed]

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

--> Oracle Catalog Views         [upgrade]  VALID

--> Oracle Packages and Types    [upgrade]  VALID

--> JServer JAVA Virtual Machine [upgrade]  VALID

--> Oracle XDK for Java          [upgrade]  VALID

--> Oracle Java Packages         [upgrade]  VALID

--> Oracle Text                  [upgrade]  VALID

--> Oracle XML Database          [upgrade]  VALID

--> Oracle Workspace Manager     [upgrade]  VALID

--> Oracle Data Mining           [upgrade]  VALID

--> OLAP Analytic Workspace      [upgrade]  VALID

--> OLAP Catalog                 [upgrade]  VALID

--> Oracle OLAP API              [upgrade]  VALID

--> Oracle interMedia            [upgrade]  VALID

--> Spatial                      [upgrade]  VALID

--> Expression Filter            [upgrade]  VALID

--> EM Repository                [upgrade]  VALID

--> Rule Manager                 [upgrade]  VALID

.PL/SQL procedure successfully completed.

SQL> spool off

7. Now you can see no more recommendation to implement so we would run the actual database upgrade script(catupgrd.sql) in next step.

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

After running the above script just go out for a walk and have some tea/fun as it will take more than half an hour for a starter database to complete ;-)

DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS.  Disconnect
DOC> and reconnect with AS SYSDBA.

DOC>######################################################################
DOC>######################################################################
DOC>#no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statements will cause an "ORA-01722: invalid number"
DOC> error if the SYSAUX tablespace does not exist or is not
DOC> ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and
DOC> SEGMENT SPACE MANAGEMENT AUTO.
DOC>
DOC> The SYSAUX tablespace is used in 10.1 to consolidate data from
DOC> a number of tablespaces that were separate in prior releases.
DOC> Consult the Oracle Database Upgrade Guide for sizing estimates.
DOC>
DOC> Create the SYSAUX tablespace, for example,
DOC>
DOC> create tablespace SYSAUX datafile 'sysaux01.dbf'
DOC>      size 70M reuse
DOC>      extent management local
DOC>      segment space management auto
DOC>      online;
DOC>
DOC> Then rerun the catupgrd.sql script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#no rows selected
no rows selected
no rows selected
no rows selected
no rows selected
Session altered.
Session altered.
Table created.
2 rows deleted.
1 row created.
Commit complete.
TIMESTAMP                                                                      ------------------------------------------------------------                   COMP_TIMESTAMP UPGRD__BGN 2014-10-28 22:38:32 2456959 81512                    

Table created.
Index created.
Table created.
Index created.
Table created.
Index created.
Index created.
Table created.
Index created.
Index created.
Table created.
Index created.
Table created.
Table altered.……………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP UPGRD_END  2014-10-28 23:11:29.Oracle Database 10.2 Upgrade Status Utility           10-28-2014 23:11:29.Component                                Status         Version  HH:MM:SSOracle Database Server                    VALID      10.2.0.3.0  00:15:26JServer JAVA Virtual Machine              VALID      10.2.0.3.0  00:04:53Oracle XDK                                VALID      10.2.0.3.0  00:00:33Oracle Database Java Packages             VALID      10.2.0.3.0  00:00:23Oracle Text                               VALID      10.2.0.3.0  00:00:32Oracle XML Database                       VALID      10.2.0.3.0  00:00:52Oracle Data Mining                        VALID      10.2.0.3.0  00:00:26OLAP Analytic Workspace                   VALID      10.2.0.3.0  00:00:36OLAP Catalog                              VALID      10.2.0.3.0  00:01:10Oracle OLAP API                           VALID      10.2.0.3.0  00:00:54Oracle interMedia                         VALID      10.2.0.3.0  00:03:35Spatial                                   VALID      10.2.0.3.0  00:01:04Oracle Expression Filter                  VALID      10.2.0.3.0  00:00:13Oracle Enterprise Manager                 VALID      10.2.0.3.0  00:02:07Oracle Rule Manager                       VALID      10.2.0.3.0  00:00:07.Total Upgrade Time: 00:32:56
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above PL/SQL lists the SERVER components in the upgraded
DOC>   database, along with their current version and status.
DOC>
DOC>   Please review the status and version columns and look for
DOC>   any errors in the spool log file.  If there are errors in the spool
DOC>   file, or any components are not VALID or not the current version,
DOC>   consult the Oracle Database Upgrade Guide for troubleshooting
DOC>   recommendations.
DOC>
DOC>   Next shutdown immediate, restart for normal operation, and then
DOC>   run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>


SQL> spool off

8. Wow…… database upgrade script finished with success as above we can see no any database component in INVALID state so let’s restart the database instance now normally.

SQL> shutdown immediate;
Database closed.

SQL> startup
ORACLE instance started.
Total System Global Area  436207616 bytes
Fixed Size                  1262236 bytes
Variable Size             222301540 bytes
Database Buffers          209715200 bytes
Redo Buffers                2928640 bytes
Database mounted.
Database opened.

9. The database upgrade may leave many database objects in INVALID state so in order to compile those INVALID objects we would have to run the oracle provided re-compile in-build tool making it in VALID state.

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

10. Finally, Let’s verify if our all database components are upgraded and are in VALID state.

SQL> set linesize 220
SQL> column COMP_NAME format a50;
SQL> select comp_name, version, status from sys.dba_registry;

COMP_NAME                                          VERSION                        STATUS
-------------------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views                      10.2.0.3.0                     VALID
Oracle Database Packages and Types                 10.2.0.3.0                     VALID
Oracle Workspace Manager                           10.2.0.3.0                     VALID
JServer JAVA Virtual Machine                       10.2.0.3.0                     VALID
Oracle XDK                                         10.2.0.3.0                     VALID
Oracle Database Java Packages                      10.2.0.3.0                     VALID
Oracle Expression Filter                           10.2.0.3.0                     VALID
Oracle Data Mining                                 10.2.0.3.0                     VALID
Oracle Text                                       10.2.0.3.0                     VALID
Oracle XML Database                                10.2.0.3.0                     VALID
Oracle Rule Manager                                10.2.0.3.0                     VALID
Oracle interMedia                                  10.2.0.3.0                     VALID
OLAP Analytic Workspace                            10.2.0.3.0                     VALID
Oracle OLAP API                                    10.2.0.3.0                     VALID
OLAP Catalog                                       10.2.0.3.0                     VALID
Spatial                                           10.2.0.3.0                     VALID
Oracle Enterprise Manager                          10.2.0.3.0                     VALID

17 rows selected.

[oracle@ora10srv bin]$ ./sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 29 00:56:22 2014Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn /as sysdba
Connected.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
- ProdPL/SQL Release 10.2.0.3.0 
- ProductionCORE 10.2.0.3.0 
- ProductionTNS for Linux: Version 10.2.0.3.0 
- ProductionNLSRTL Version 10.2.0.3.0 - Production

Yes..looks fine as all oracle database components got upgraded successfully to 10.2.0.3 with VALID state ……….Enjoy the DB upgrade now :-)

Oracle RAC Components and Background Process

Oracle RAC is composed of two or more database instances.
They are composed of Memory structures and background processes same as the single instance database.

Oracle RAC instances use two processes:
1- GES(Global Enqueue Service)
2- GCS(Global Cache Service) that enable cache fusion.

Oracle RAC instances are composed of following background processes:
ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor

Oracle Backup & Recovery Check List

User Managed Recovery Scenarios And Configuration

1. Complete Closed Database Recovery. System tablespace is missing

2. Complete Open Database Recovery. Non system tablespace is missing

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

4. Recovery of a Missing Datafile that has no backups.

5. Restore and Recovery of a Datafile to a different location.

6. Control File Recovery

7. Incomplete Recovery, Until Time/Sequence/Cancel


Rman Recovery Scenarios And Configuration

1. Complete Closed Database Recovery. System tablespace is missing

2. Complete Open Database Recovery. Non system tablespace is missing

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

4. Recovery of a Datafile that has no backups.

5. Restore and Recovery of a Datafile to a different location.

6. Control File Recovery

7. Incomplete Recovery, Until Time/Sequence/Cancel

User Managed Recovery Scenarios

User managed recovery scenarios do require that the database is in archive log mode, and that backups of all datafiles and control files are made with the tablespaces set to begin backup, if the database is open while the copy is made. At the end of the copy of each tablespace it is necessary to take it out of backup mode. Alternatively complete backups can be made with the database shutdown. Online redologs can be optionally backed up.

Files to be copied: 
select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

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 /user/backup/uman/system01.dbf /user/oradata/u01/dbtst/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 /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf

2. alter tablespace offline immediate;
3. recover tablespace ;
4. alter tablespace 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 /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf
3. alter database datafile3 offline; (tablespace cannot be used because the database is not open)
4. alter database open;
5. recover datafile 3;
6. alter tablespace 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 offline immediate;
2. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf';
3. recover tablespace ;
4. alter tablespace 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/dbtst/newdata01.dbf' as '/user/oradata/u02/dbtst/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 /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf
2. alter tablespace offline immediate;
3. alter tablespace rename datafile '/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf';
4. recover tablespace ;
5. alter tablespace 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 /user/backup/uman/control01.dbf /user/oradata/u01/dbtst/control01.dbf cp -p /user/backup/uman/control02.dbf /user/oradata/u01/dbtst/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 /user/backup/uman/u01/*.dbf /user/oradata/u01/dbtst/ cp -p /user/backup/uman/u02/*.dbf /user/oradata/u01/dbtst/ cp -p /user/backup/uman/u03/*.dbf /user/oradata/u01/dbtst/ cp -p /user/backup/uman/u04/*.dbf /user/oradata/u01/dbtst/ 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 rcuser identified by rcuser default tablespace repcat temporary tablespace temp;
4. grant connect, resource, recovery_catalog_owner to rcuser
5. exit

6. rman catalog rcuser/rcuser # connect to rman catalog as the rcuser
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 offline immediate';
3. restore datafile 3;
4. recover datafile 3;
5. sql 'alter tablespace 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 '' offline;
5. alter database open;
6. exit;

7. rman target /
8. restore datafile '';
9. recover datafile '';
10. sql 'alter tablespace 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 offline immediate;
3. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf;
4. exit

5. rman target /
6. recover tablespace ;
7. sql 'alter tablespace 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/dbtst/newdata01.dbf' as '/user/oradata/u02/dbtst/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 /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf

2. alter tablespace offline immediate;

3. alter tablespace rename datafile '/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf';

4. rman target /

5. recover tablespace ;

6. sql 'alter tablespace 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
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: '2004-12-28:01:01:10'.

go up ;-)

RMAN Recovery Methods

RMAN Recovery Methods:

When performing a restore operation, it is best to open two telnet sessions, one for SQL commands, and one for RMAN commands. For the rest of this document, RMAN commands will be prefaced with the RMAN> prompt, and SQL commands will be prefaced with the SQL> prompt. A $ indicates that the command is executed from the Unix prompt.

Restoring and Recovering All Datafiles

In this scenario, it is assumed that your control files are still accessible. You have a backup, done for example with backup database plus archivelog;

Your first step is to make sure that the target database is shut down: 

$ sqlplus “/ as SYSDBA”

SQL> shutdown abort;

ORACLE instance shut down.

Next, you need to start up your target database in mount mode. RMAN cannot restore datafiles unless the database is at least in mount mode, because RMAN needs to be able to access the control file to determine which backup sets are necessary to recover the database. If the control file isn't available, you have to recover it first. Issue the STARTUP MOUNT command shown in the following example to mount the database: 

SQL> startup mount;

Oracle instance started. 

Database mounted.

Since backup set files are created in an RMAN-specific format, you must use RMAN to restore the datafiles. To use RMAN, connect to the target database:

$ rman target / rcvcat rcvcat/rcvcat@oemprod

The remainder of this example shows how to restore all of the datafiles of the target database. When the restore command is executed, RMAN will automatically go to its last good backup set and restore the datafiles to the state they were in when that backup set was created. 

When restoring database files, RMAN reads the datafile header and makes the determination as to whether the file needs to be restored. The recovery is done by allocating a channel for I/O and then issuing the RMAN restore database command. 

With Oracle9i and above, you don't need to allocate a channel explicitly. Instead, you can use the default channel mode: 

RMAN> restore database;

RMAN> recover database;

SQL> alter database open;

For Oracle8i, the ALLOCATE, RESTORE, and RECOVER commands need to be enclosed by the run{} command: 

RMAN> run {

allocate channel d1 type disk;

restore database;

recover database;

}

alter database open;

Once the recovery has been completed, execute a complete RMAN backup to establish a new baseline.

Restoring Specific Tablespaces

In this scenario, it is assumed that your control files are still accessible. You have a backup, done for example with backup database plus archivelog;

Take the tablespace that needs recovery offline, restore the tablespace, recover the tablespace, and bring the tablespace online. If you cannot take the tablespace offline, then shutdown abort the database and restore in mount mode.

First try to take the tablespace offline;

$ sqlplus "/ as sysdba"

SQL> alter tablespace tab offline;

If this works, continue with the RMAN recovery:

$ rman target / rcvcat rcvcat/rcvcat@oemprod

RMAN> restore tablespace tab;

RMAN> recover tablespace tab;

SQL> alter tablespace tab online;

If taking the tablespace offline fails, follow these steps:

$ sqlplus “/ as SYSDBA”

SQL> shutdown abort;

SQL> startup mount;

$ rman target / rcvcat rcvcat/rcvcat@oemprod

RMAN> restore tablespace tab;

RMAN> recover tablespace tab;

SQL> alter database open;

Once the recovery has been completed, execute a complete RMAN backup to establish a new baseline.

Restoring Specific Datafiles

In this scenario, it is assumed that your control files are still accessible. You have a backup, done for example with backup database plus archivelog;

Take the datafile that needs recovery offline, restore the datafile, recover the datafile, and bring the datafile online. If you cannot take the datafile offline, then shutdown abort the database and restore in mount mode.

First try to take the datafile offline:

SQL> alter database datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf' offline;

If this works, continue with the RMAN recovery:

$ rman target / rcvcat rcvcat/rcvcat@oemprod

RMAN> restore datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf'

RMAN> recover datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf'

SQL> alter database datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf' online;

If taking the datafile offline fails, follow these steps:

$ sqlplus “/ as SYSDBA”

SQL> shutdown abort;

SQL> startup mount;

$ rman target / rcvcat rcvcat/rcvcat@oemprod

RMAN> restore datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf';

RMAN> recover datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf';

SQL> alter database open;

Once the recovery has been completed, execute a complete RMAN backup to establish a new baseline.

Restoring Control Files

In this scenario, it is assumed that your control files are backed up. You have a backup, done for example with backup database plus archivelog;

In an ideal world you'll never use RMAN to restore a control file. But if something catastrophic happens, and you lose all control files, here are the steps for getting them back: 

The following examples assume that you are using a catalog. First, here's the simplest Oracle9i syntax for restoring a control file: 

$ sqlplus “/ as SYSDBA”

SQL> shutdown abort;

SQL> startup nomount;

$ rman target / rcvcat rcvcat/rcvcat@oemprod

Set the database id (DBID) with the following command. This is a 10-digit number that RMAN uses to uniquely identify this database in the recovery log. The number can be obtained from any previous RMAN backup log file.

RMAN> set dbid = xxxxxxxxxx;

RMAN> restore controlfile;

SQL> alter database mount;

SQL> alter database open;

If this fails with ...

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

... then you must perform a recover database:

SQL> shutdown abort;

SQL> startup mount;

$ rman target / rcvcat rcvcat/rcvcat@oemprod

RMAN> recover database;

SQL> alter database open resetlogs;

RMAN> reset database;

Note, that all offline archivelogs are now useless, perform a full back as soon as possible.

Restoring Online Redologs

In this scenario, it is assumed that your control files are backed up. You have a backup, done for example with backup database plus archivelog;

$ sqlplus "/ as sysdba"

SQL> shutdown abort;

SQL> startup nomount;

$ rman target / rcvcat rcvcat/rcvcat@oemprod

Set the database id (DBID) with the following command. This is a 10-digit number that RMAN uses to uniquely identify this database in the recovery log. The number can be obtained from any previous RMAN backup log file.

RMAN> set dbid = xxxxxxxxxx;

RMAN> restore controlfile;

SQL> alter database mount;

RMAN> restore database;

RMAM> recover database;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 09/28/2004 11:03:23

RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 8448414

Since the online logs were lost, complete recovery is not possible. Open the database with resetlogs to continue.

RMAN> alter database open resetlogs;

-------------------------------------IMPORTANT-------------------------------------------

During this type of recovery, if you receive error messages like this:

RMAN> restore database;

Starting restore at 11-JUL-05

using channel ORA_SBT_TAPE_1

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/11/2005 14:25:22

RMAN-06026: some targets not found - aborting restore

RMAN-06023: no backup or copy of datafile 10 found to restore

RMAN-06023: no backup or copy of datafile 9 found to restore

RMAN-06023: no backup or copy of datafile 8 found to restore

RMAN-06023: no backup or copy of datafile 7 found to restore

RMAN-06023: no backup or copy of datafile 6 found to restore

RMAN-06023: no backup or copy of datafile 5 found to restore

RMAN-06023: no backup or copy of datafile 4 found to restore

RMAN-06023: no backup or copy of datafile 3 found to restore

RMAN-06023: no backup or copy of datafile 2 found to restore

RMAN-06023: no backup or copy of datafile 1 found to restore

…use the following directions to recover (recreate the controlfile):

• With the database mounted, execute ‘alter database backup controlfile to trace resetlogs;’

• Perform a shutdown abort on the database, but remain at the SQL> prompt.

• In another telnet session, go to the udump directory to retrieve the resulting trace file and copy it to another location to edit it.

• Edit the file and add the phrase “until cancel” to the recover database command at the end. The phrase should read “recover database until cancel using backup controlfile”. Remove the “alter database open” command after the recover command. Save the file with a .sql extension.

• Back at the SQL> prompt, execute the modified trace file. When prompted for an archived log, type in “cancel” and the reply should be “media recovery cancelled”.

• Issue “alter database open resetlogs”. The database should open after a few moments.

• Connect to the RMAN recovery catalog and issue the “reset database” command.

• Perform a full RMAN backup as soon as possible.

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

Time-Based, Change-Based, or SCN-based Incomplete Recovery

Incomplete recovery uses a backup to produce a non-current version of the database. In other words, you

do not apply all of the redo records generated after the most recent backup.

You usually perform incomplete recovery of the whole database in the following situations:

Media failure destroys some or all of the online redo logs. 

A user error causes data loss, for example, a user inadvertently drops a table. 

You cannot perform complete recovery because an archived redo log is missing. 

You lose your current control file and must use a backup control file to open the database. 

To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. The RESETLOGS operation creates a new incarnation of the database; in other words, a database with a new stream of log sequence numbers starting with log sequence 1.

NOTE – Start every RMAN incomplete recovery with the following commands:

$ sqlplus "/ as sysdba"

SQL> shutdown abort;

SQL> startup mount;

$ rman target / rcvcat rcvcat/rcvcat@oemprod

--For time-based recovery, use these commands:

RMAN> restore database until time "to_date('07/12/05 12:0:00','MM/DD/YY HH24:MI:SS')";

RMAN> recover database until time "to_date('07/12/05 12:0:00','MM/DD/YY HH24:MI:SS')";

media recovery complete.

SQL> alter database open resetlogs;

--For SCN-based recovery, user these commands:

RMAN> restore database until scn 1000;

RMAN> recover database until scn 1000;

media recovery complete.

SQL> alter database open resetlogs;

--For change-based recovery, user these commands:

RMAN> restore database until sequence 9923; --Archived log sequence number

RMAN> recover database until sequence 9923; --Archived log sequence number

media recovery complete.

SQL> alter database open resetlogs;

Once the recovery has been completed, execute the following steps:

• Delete prior backups with this command (from the RMAN prompt):

RMAN> delete force backup;

This command removes all prior backups from the RMAN catalog as they can no longer be used once the database has been restarted with the resletlogs option. After completing this command, create a new RMAN backup to establish a new baseline.

Recovering Archived Logs only 

In the event that you want to recover the database archived redo logs until a desired time, you can use the following commands:

$ rman target / rcvcat rcvcat/rcvcat@oemprod

RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';

or

RMAN> restore ARCHIVELOG FROM TIME "to_date('07/11/05 00:00:01','MM/DD/YY HH24:MI:SS')

UNTIL TIME 'SYSDATE';