Database Cloning from Production to Development DB
STEP 1: sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PRODDB" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/PRODDB/log1a.dbf',
'/u03/oradata/PRODDB/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/PRODDB/log2a.dbf',
'/u04/oradata/PRODDB/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/PRODDB/system01.dbf',
'/u01/oradata/PRODDB/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
STEP 2: Shutdown the PRODDB database
STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.
rcp /u01/oradata/PRODDB/* newhost:/u01/oradata/DEVDB
rcp /u01/oradata/PRODDB/* newhost:/u01/oradata/DEVDB
rcp /u03/oradata/PRODDB/* newhost:/u03/oradata/DEVDB
rcp /u04/oradata/PRODDB/* newhost:/u04/oradata/DEVDB
STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:
Old:
CREATE CONTROLFILE REUSE DATABASE "PRODDB" NORESETLOGS
New:
CREATE CONTROLFILE SET DATABASE "DEVDB" RESETLOGS
STEP 5: Remove the “recover database” and “alter database open” syntax
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
STEP 6: Re-names of the data files names that have changed.
Save as db_create_controlfile.sql.
Old:
DATAFILE
'/u01/oradata/PRODDB/system01.dbf',
'/u01/oradata/PRODDB/mydatabase.dbf'
New:
DATAFILE
'/u01/oradata/DEVDB/system01.dbf',
'/u01/oradata/DEVDB/mydatabase.dbf'
STEP 7: Create the bdump, udump and cdump directories
cd $DBA/admin
mkdir DEVDB
cd DEVDB
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile
STEP 8: Copy-over the old init.ora file
rcp $DBA/admin/PRODDB/pfile/*.ora newhost:/u01/oracle/admin/DEVDB/pfile
STEP 9: Start the DEVDB database
@db_create_controlfile.sql
STEP 10: Place the DEVDB database in archivelog mode
Saturday, May 21, 2011
Friday, May 20, 2011
Upgrading Oracle Database Server From 10g Release2 To 11g Release2
Steps to Upgrade Oracle DB from 10g R2 to 11g R2
I have been done this upgrade process on one of our Development Sun Server.
1. First Upgrade Solaris from 5.10 to 6.10 By applying Solaris Patch Set update (PSU)
a. Download all respective Solaris Patches from Sun Service support
b. Take a OS full Backup ( or detach a virtual partition)
c. Apply all downloaded patches on prior to dependency
d. Restart the Server and test the packages Updated successfully
e. Attach a virtual partition if it detached
f. Test all the applications running on this server
2. Prerequisite to upgrade Oracle from 10.2.0.1.0 to 11.2.0.1
Apply an Oracle patch Set Update (PSU) p4547817
a. Download the Oracle patch set for 10.2.0.1 from OTN
b. Take a DB full backup
c. Apply the downloaded patch set
d. Restart oracle instance and check patches applied successfully
e. Test all the applications running from this DB
Upgradation of Oracle 10G R2 to 11G R2
a. Download Oracle 11G Release2 from OTN
b. Take a DB full Backup
c. Setup a new ORACLE_HOME directory for 11G on same DB server
d. Upgrade Automatic Storage Management (ASM) instance if any
e. Install oracle 11G using exe file runInstaller and Upgrade Database using
Database Upgrade Assistant (DBUA) from downloaded software (for detail go
through the bellow document with screenshots)
f. Restart oracle instance and check the installation status
g. Test all the application running from this DB
NOTE: DO NOT SHUTDOWN DATABASE BEFORE RUNNING DBUA.
STEP 1: Upgrade Oracle 10g using Oracle 11gR2 Software:
Execute runInstaller to install “SOFTWARE ONLY” option, Make sure that you select “software only” option as shown below.
STEP 2 : Upgrade Database using DBUA
A: Run Pre-Upgrade Information tool
Install the software under “/u01/11g/oracle/product/11.2.0/dbhome_1″ location. Once the software is installed, then go to location $ORACLE_HOME/rdbms/admin and copy utlu112i.sql script to /tmp directory. Now login to 10g database “/ as sysdba” and startup the 10g database, then:
SQL > spool /tmp/upgrade.spl
SQL > @/tmp/utlu112i.sql
SQL > spool off
Following is the output of this script from my database:
Oracle Database 11.2 Pre-Upgrade Information Tool 09-04-2009 01:54:32
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL10G
--> version: 10.2.0.3.0
--> compatible: 10.2.0.3.0
--> blocksize: 8192
--> platform: Linux IA (32-bit)
--> timezone file: V3
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 724 MB
.... AUTOEXTEND additional space required: 244 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 464 MB
.... AUTOEXTEND additional space required: 439 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 447 MB
.... AUTOEXTEND additional space required: 207 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 336 MB
WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
WARNING: --> "pga_aggregate_target" needs to be increased to at least 24 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
--> core_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
.
**********************************************************************
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 Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.3.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... XDB
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the Upgrade Guide for instructions to configure Network ACLs.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING:--> recycle bin in use.
.... Your recycle bin turned on.
.... It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command: PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
PL/SQL procedure successfully completed.
SQL> spool off
Adjust the warnings shown by Pre-Upgrade Information tool.
B: Run DBUA
Execute the DBUA from 11gR2 software home as
$ cd $ORACLE_HOME/bin
$./dbua
Click “Next”
Select the database that you want to upgrade
Click “Next”
Here DBUA will show warnings that were not solved after running Pre-Upgrade Information tool
Click “Yes”
Turn OFF archiving while upgrading
Click “Next”
Check whether you want to move the datafiles while upgrade, though the “move datafile” check-box was not highlighted when I upgraded
Click “Next”.
Specify “FRA” and “Diagnostic Destination”
Click “Next”.
Check configuration for EM
Click “Next”.
Check “Summary” page
Click “Finish”
Upgrade Process is started
Check the Results
Congratulations!!!!!!!!!! Upgrade is Successful !!!!!!!!!!!!!!!
Now you are ready to use Most Powerful Database!!
Regards,
Anand kc
I have been done this upgrade process on one of our Development Sun Server.
1. First Upgrade Solaris from 5.10 to 6.10 By applying Solaris Patch Set update (PSU)
a. Download all respective Solaris Patches from Sun Service support
b. Take a OS full Backup ( or detach a virtual partition)
c. Apply all downloaded patches on prior to dependency
d. Restart the Server and test the packages Updated successfully
e. Attach a virtual partition if it detached
f. Test all the applications running on this server
2. Prerequisite to upgrade Oracle from 10.2.0.1.0 to 11.2.0.1
Apply an Oracle patch Set Update (PSU) p4547817
a. Download the Oracle patch set for 10.2.0.1 from OTN
b. Take a DB full backup
c. Apply the downloaded patch set
d. Restart oracle instance and check patches applied successfully
e. Test all the applications running from this DB
Upgradation of Oracle 10G R2 to 11G R2
a. Download Oracle 11G Release2 from OTN
b. Take a DB full Backup
c. Setup a new ORACLE_HOME directory for 11G on same DB server
d. Upgrade Automatic Storage Management (ASM) instance if any
e. Install oracle 11G using exe file runInstaller and Upgrade Database using
Database Upgrade Assistant (DBUA) from downloaded software (for detail go
through the bellow document with screenshots)
f. Restart oracle instance and check the installation status
g. Test all the application running from this DB
NOTE: DO NOT SHUTDOWN DATABASE BEFORE RUNNING DBUA.
STEP 1: Upgrade Oracle 10g using Oracle 11gR2 Software:
Execute runInstaller to install “SOFTWARE ONLY” option, Make sure that you select “software only” option as shown below.
STEP 2 : Upgrade Database using DBUA
A: Run Pre-Upgrade Information tool
Install the software under “/u01/11g/oracle/product/11.2.0/dbhome_1″ location. Once the software is installed, then go to location $ORACLE_HOME/rdbms/admin and copy utlu112i.sql script to /tmp directory. Now login to 10g database “/ as sysdba” and startup the 10g database, then:
SQL > spool /tmp/upgrade.spl
SQL > @/tmp/utlu112i.sql
SQL > spool off
Following is the output of this script from my database:
Oracle Database 11.2 Pre-Upgrade Information Tool 09-04-2009 01:54:32
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL10G
--> version: 10.2.0.3.0
--> compatible: 10.2.0.3.0
--> blocksize: 8192
--> platform: Linux IA (32-bit)
--> timezone file: V3
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 724 MB
.... AUTOEXTEND additional space required: 244 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 464 MB
.... AUTOEXTEND additional space required: 439 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 447 MB
.... AUTOEXTEND additional space required: 207 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 336 MB
WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
WARNING: --> "pga_aggregate_target" needs to be increased to at least 24 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
--> core_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
.
**********************************************************************
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 Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.3.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... XDB
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the Upgrade Guide for instructions to configure Network ACLs.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING:--> recycle bin in use.
.... Your recycle bin turned on.
.... It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command: PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
PL/SQL procedure successfully completed.
SQL> spool off
Adjust the warnings shown by Pre-Upgrade Information tool.
B: Run DBUA
Execute the DBUA from 11gR2 software home as
$ cd $ORACLE_HOME/bin
$./dbua
Click “Next”
Select the database that you want to upgrade
Click “Next”
Here DBUA will show warnings that were not solved after running Pre-Upgrade Information tool
Click “Yes”
Turn OFF archiving while upgrading
Click “Next”
Check whether you want to move the datafiles while upgrade, though the “move datafile” check-box was not highlighted when I upgraded
Click “Next”.
Specify “FRA” and “Diagnostic Destination”
Click “Next”.
Check configuration for EM
Click “Next”.
Check “Summary” page
Click “Finish”
Upgrade Process is started
Check the Results
Congratulations!!!!!!!!!! Upgrade is Successful !!!!!!!!!!!!!!!
Now you are ready to use Most Powerful Database!!
Regards,
Anand kc
Subscribe to:
Posts (Atom)