Google it ....

Showing posts with label Upgrade. Show all posts
Showing posts with label Upgrade. Show all posts

Friday, October 24, 2014

Upgrade Oracle Database from 10g to 11g on another host

In this post we will discuss about restoring 10g database into new host with 11g oracle software installed.
Source database is 10.2.0.1, O/S is Windows server 2003 64 bit
On destination server installed oracle database software 11.2.0.1, O/S is Windows server 2012 64 bit

Note : direct upgrade between these versions is not possible, see Oracle Documentation http://docs.oracle.com/cd/E11882_01/server.112/e23633/preup.htm#UPGRD12358, hence we need to upgrade source database version from 10.2.0.1 to 10.2.0.2 or(higher)

after applying patchset 10.2.0.5, we can continue upgrading to 11g on another host.

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

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.
run {
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
backup tag test_full database format "E:\databasefiles_%d_%u_%s_%T";
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP archivelog all format "E:\archivelogs_%d_%u_%s_%T";
backup current controlfile format "E:\control01.cf";
RELEASE CHANNEL RMAN_BACK_CH01;
}

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 and change path of directories parameters as you have.

2) Open the database in NOMOUNT state.
rman target /

RMAN> startup nomount;

3) Restore controlfile from backup.
RMAN> restore controlfile from 'D:\backup\control01.cf';

4) Open the database in MOUNT state.
RMAN> alter database mount;

5) Make crosscheck backup and archive log, for delete expireds, Catalog RMAN backup files and archive log files.
RMAN> crosscheck backup;
RMAN> crosscheck archivelog all;
RMAN> delete expired backup;
RMAN> delete expired archivelog all; 
RMAN> catalog start with 'D:\backup';

6) restore and recover 10g database backup
RMAN> run
{
allocate channel c1 device type disk FORMAT 'D:\backup';
SET NEWNAME FOR DATAFILE 1 TO ‘D:\oradata\sd01\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO ‘D:\oradata\sd01\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 3 TO ‘D:\oradata\sd01\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 4 TO ‘D:\oradata\sd01\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO ‘D:\oradata\sd01\USERS02.DBF';
SET NEWNAME FOR DATAFILE 6 TO ‘D:\oradata\sd01\USERS03.DBF';
SET NEWNAME FOR DATAFILE 7 TO ‘D:\oradata\sd01\USERS04.DBF';
restore database;
switch datafile all;
recover database;
}

7) open database with upgrade mode
SQL> shutdown immediate;
SQL> startup upgrade;

8) Run the upgrade script
SQL> SPOOL upgradelog.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
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> SPOOL upgradelog.log
SQL> @%ORACLE_HOME%\rdbms\admin\utlmmig.sql
SQL> SPOOL off

10) open the database
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

11) Run the Post-Upgrade script
SQL> @%ORACLE_HOME%\rdbms\admin\utlu112s.sql

12) Compile invalid objects
SQL> @%ORACLE_HOME%\rdbms\admin\utlrp.sql

that's all, have a good upgrade.

Tuesday, October 15, 2013

ORA-39700: database must be opened with upgrade option

Hello,
After restore database another server and was trying to open database appear error
ORA-39700: database must be opened with upgrade option
because on older server database version was 11.2.0.1.0 and on new server database version is 11.2.0.3.0, therefore we need:
1) open with upgrade option
2) upgrade database - it means run script $ORACLE_HOME/rdbms/admin/catupgrd.sql
3) shutdown clearly and startup
4) run script $ORACLE_HOME/rdbms/admin/utlrp.sql - for recompile all invalid PL/SQL packages

sqlplus / as sysdba
SQL> startup upgrade;
SQL> @/u01/app/oracle/product/11.2.0/db_home1/rdbms/admin/catupgrd.sql;
SQL> shutdown immediate;
SQL> startup;
SQL> @/u01/app/oracle/product/11.2.0/db_home1/rdbms/admin/utlrp.sql


that's all