Knowing all related directories on the source database
Form the bottom, we would like to know the location of spfile, control_file, recovery area, trace dumps, and any other related destination for reproducing an friendly environment in the target server.
[oracle@db11202 ~]$ sqlplus / as sysdba
...
SQL> startup mount
ORACLE instance started.
...
Database mounted.
SQL> column name format a25;
SQL> column value format a50;
SQL> select name, value from v$parameter where value like '%/%';
NAME VALUE
------------------------- --------------------------------------------------
spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfile
DB11202.ora
control_files /u01/app/oracle/oradata/DB11202/control01.ctl, /u0
1/app/oracle/recovery_area/DB11202/control02.ctl
standby_archive_dest ?/dbs/arch
db_recovery_file_dest /u01/app/oracle/recovery_area
background_dump_dest /u01/app/oracle/diag/rdbms/db11202/DB11202/trace
user_dump_dest /u01/app/oracle/diag/rdbms/db11202/DB11202/trace
core_dump_dest /u01/app/oracle/diag/rdbms/db11202/DB11202/cdump
NAME VALUE
------------------------- --------------------------------------------------
audit_file_dest /u01/app/oracle/admin/DB11202/adump
dg_broker_config_file1 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1DB1
1202.dat
dg_broker_config_file2 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2DB1
1202.dat
diagnostic_dest /u01/app/oracle
11 rows selected.
Don't forget the location of data files.
SQL> column name format a50;
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/DB11202/system01.dbf
/u01/app/oracle/oradata/DB11202/sysaux01.dbf
/u01/app/oracle/oradata/DB11202/undotbs01.dbf
/u01/app/oracle/oradata/DB11202/users01.dbf
/u01/app/oracle/oradata/DB11202/example01.dbf
SQL> shutdown immediate;
Show and select useful service entries of tnsnames.ora for the target server.
[oracle@db11204 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
...
DB11202 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db11202)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11202)
)
)
...
Reproducing a friendly environment in the target server
First of all, we append some useful service entries to tnsnames.ora from the source database. If it's necessary, please modify the entries.
[oracle@db11204 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
...
DB11202 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db11204)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11202)
)
)
Make required directories.
[oracle@db11204 ~]$ mkdir -p /u01/app/oracle/oradata/DB11202/
[oracle@db11204 ~]$ mkdir -p /u01/app/oracle/recovery_area/DB11202/
[oracle@db11204 ~]$ mkdir -p /u01/app/oracle/diag/rdbms/db11202/DB11202/trace/
[oracle@db11204 ~]$ mkdir -p /u01/app/oracle/diag/rdbms/db11202/DB11202/cdump/
[oracle@db11204 ~]$ mkdir -p /u01/app/oracle/admin/DB11202/adump/
Copy all required files to the target server. The first file will be spfile or pfile.
[oracle@db11204 ~]$ scp -p 192.168.0.151:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDB11202.ora $ORACLE_HOME/dbs/
[email protected]'s password:
spfileDB11202.ora 100% 2560 2.5KB/s 00:00
The second and third files will be control files.
[oracle@db11204 ~]$ scp -p 192.168.0.151:/u01/app/oracle/oradata/DB11202/control01.ctl /u01/app/oracle/oradata/DB11202/
[email protected]'s password:
control01.ctl 100% 9520KB 9.3MB/s 00:00
[oracle@db11204 ~]$ scp -p 192.168.0.151:/u01/app/oracle/recovery_area/DB11202/control02.ctl /u01/app/oracle/recovery_area/DB11202/
[email protected]'s password:
control02.ctl 100% 9520KB 9.3MB/s 00:00
Next, all data files will be copied.
[oracle@db11204 ~]$ scp -p 192.168.0.151:/u01/app/oracle/oradata/DB11202/* /u01/app/oracle/oradata/DB11202/
[email protected]'s password:
control01.ctl 100% 9520KB 9.3MB/s 00:00
example01.dbf 100% 313MB 104.4MB/s 00:03
redo01.log 100% 50MB 50.0MB/s 00:00
redo02.log 100% 50MB 50.0MB/s 00:01
redo03.log 100% 50MB 50.0MB/s 00:00
sysaux01.dbf 100% 520MB 104.0MB/s 00:05
system01.dbf 100% 710MB 78.9MB/s 00:09
temp01.dbf 100% 29MB 29.0MB/s 00:00
undotbs01.dbf 100% 105MB 105.0MB/s 00:01
users01.dbf 100% 5128KB 5.0MB/s 00:00
All other files are not necessarily required, you may copy them later.
If you plan to copy these files to new locations that are different from the current directories, you have to change the file locations of the database after copying.
Duplicate the data files in case we get in troubles later.
[oracle@db11204 oradata]$ mkdir /u01/app/oracle/oradata/DB11202_bk
[oracle@db11204 oradata]$ cp -p /u01/app/oracle/oradata/DB11202/* /u01/app/oracle/oradata/DB11202_bk/
Add an entry to /etc/oratab.
[oracle@db11204 ~]$ vi /etc/oratab
...
DB11202:/u01/app/oracle/product/11.2.0/dbhome_1:N
If this database will be your primary instance, you have to change the environment variable $ORACLE_SID permanently.
[oracle@db11204 ~]$ vi .bash_profile
...
export ORACLE_SID=DB11202
[oracle@db11204 ~]$ . .bash_profile
[oracle@db11204 ~]$ echo $ORACLE_SID
DB11202
Otherwise, you have to change it at the session level.
[oracle@db11204 ~]$ export ORACLE_SID=DB11202
Upgrading the database
If you prefer to upgrade the database via GUI, you may use DBUA to upgrade the database. This post only demonstrate a CLI mode to operate.
Startup the database with upgrade.
[oracle@db11204 ~]$ sqlplus / as sysdba
...
Connected to an idle instance.
SQL> startup upgrade
...
Or you may startup mount and followed by alter database open upgrade.
Perform a pre-upgrade status reporting.
SQL> spool /home/oracle/utlu112i.log
SQL> @?/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 11-27-2016 19:03:43
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: DB11202
--> version: 11.2.0.2.0
--> compatible: 11.2.0.0.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V14
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 919 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 613 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 310 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
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]
**********************************************************************
-- 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 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
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
... APEX will only be upgraded if the version of APEX in
... the target Oracle home is higher than the current one.
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER SYS has 2 INVALID objects.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APEX_030200 has dependent objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
SELECT name,description from SYS.V$PARAMETER WHERE name
LIKE '_%' ESCAPE ''
Changes will need to be made in the init.ora or spfile.
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
SQL> spool off
Warnings are not required to be solved, you can ignore them and proceed. But there're might be errors, so be careful.
Start the upgrade procedure catupgrd.sql, the most important step in the whole job.
SQL> spool /home/oracle/catupgrd.log
SQL> @?/rdbms/admin/catupgrd.sql
...
When it was done, please check any errors during upgrade by viewing the log.
[oracle@db11204 ~]$ vi catupgrd.log
Since catupgrd.sql will shutdown the instance at the end of the procedure, so we only need to startup the instance normally.
[oracle@db11204 ~]$ sqlplus / as sysdba
...
SQL> startup
...
The following post-upgrade status report is not really necessary, because catupgrd.sql has done it near the end of its upgrade procedures. But we can still check the status here.
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 11-27-2016 19:12:13
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:05:50
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:02:43
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:17
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:00:08
OLAP Catalog
. VALID 11.2.0.4.0 00:00:20
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:11
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:01:07
Oracle XDK
. VALID 11.2.0.4.0 00:00:20
Oracle Text
. VALID 11.2.0.4.0 00:00:13
Oracle XML Database
. VALID 11.2.0.4.0 00:01:32
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:08
Oracle Multimedia
. VALID 11.2.0.4.0 00:01:28
Spatial
. VALID 11.2.0.4.0 00:01:10
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:04
Oracle Rules Manager
. VALID 11.2.0.4.0 00:00:03
Oracle Application Express
. VALID 3.2.1.00.12
Final Actions
. 00:00:00
Total Upgrade Time: 00:15:41
PL/SQL procedure successfully completed.
Please note that, both utlu112i.sql and utlu112s.sql will not change anything to the database, they only check and report the status of pre-upgrade and post-upgrade of the database.
Check any registry error during upgrade.
SQL> select * from registry$error;
no rows selected
Some upgrade action can be done after a normal startup.
SQL> @?/rdbms/admin/catuppst.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2016-11-27 19:18:54
PL/SQL procedure successfully completed.
This script will migrate the Baseline data on a pre-11g database
to the 11g database.
... ...
... Completed Moving the Baseline Data ...
... ...
... If there are no Move BL Data messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
... ...
... Completed the Dropping of the ...
... Renamed Baseline Tables ...
... ...
... If there are no Drop Table messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
PL/SQL procedure successfully completed.
0 rows created.
Commit complete.
Table created.
2 rows created.
1 row updated.
2 rows updated.
0 rows updated.
Table dropped.
Commit complete.
0 rows updated.
Commit complete.
0 rows updated.
Commit complete.
0 rows updated.
Commit complete.
0 rows created.
Commit complete.
0 rows created.
Commit complete.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2016-11-27 19:18:54
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_DB11202_GENERATE_2016Nov27_19_18_56.log
Apply script: /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catbundle_PSU_DB11202_APPLY.sql
Rollback script: /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catbundle_PSU_DB11202_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/u01/app/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.4',
9 0,
10 'PSU',
11 'Patchset 11.2.0.2.0');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_DB11202_APPLY_2016Nov27_19_18_56.log
Upgrading might make your object invalid, so next, we recompile invalid objects.
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-11-27 19:21:09
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2016-11-27 19:21:48
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
Check if there're any invalid objects.
SQL> column owner format a10;
SQL> column object_name format a25;
SQL> column object_type format a25;
SQL> select owner, object_name, object_type from dba_objects where status = 'INVALID' order by 1, 2, 3;
no rows selected
If there're any invalid objects that you do mind. You may try utlirp.sql first, then followed by utlrp.sql.
Last, you can check the version status from this query.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
We have finished our job.