Thinking Out Loud

March 18, 2016

Migrate to 12c using Export/Import

Filed under: 12c,oracle,upgrade — mdinh @ 2:07 am

If you had success doing this, please share.

This took some troubleshooting and more than one pass.

Still not comfortable to guarantee target is identical to source.

Here are all the references, I have gathered if you want an adventure.

How to Perform a Full Database Export Import During Upgrade, Migrate, Copy or Move of a Database (Doc ID 286775.1)

Information On Installed Database Components and Schemas (Doc ID 472937.1)
Schemas CTXSYS MDSYS ORDSYS Are Not Exported (Doc ID 228482.1)

How to Install/Deinstall Oracle Workspace Manager (Doc ID 263428.1)
How to Determine if Workspace Manager is Being Used? (Doc ID 727765.1)

Data Pump: GRANTs On SYS Owned Objects Are Not Transferred With Data Pump And Are Missing In The Target Database (Doc ID 1911151.1)
After Full Import All Object Privileges Granted By SYS Are Lost (Doc ID 97902.1)

DataPump Export Fails With Error ORA-39095 (Doc ID 1071931.1)

++++++++++
How many components are installed?

*** dba_registry ***

ACTUAL SOURCE:
COMP_ID    COMP_NAME			       VERSION	       STATUS	    NAMESPACE	 SCHEMA
---------- ----------------------------------- --------------- ------------ ------------ ---------------
CATALOG    Oracle Database Catalog Views       10.2.0.4.0      VALID	    SERVER	 SYS
CATPROC    Oracle Database Packages and Types  10.2.0.4.0      VALID	    SERVER	 SYS
EM	   Oracle Enterprise Manager	       10.2.0.4.0      VALID	    SERVER	 SYSMAN
OWM	   Oracle Workspace Manager	       10.2.0.4.3      VALID	    SERVER	 WMSYS

ONE BAD SOURCE:
COMP_ID    COMP_NAME                           VERSION         STATUS       NAMESPACE    SCHEMA
---------- ----------------------------------- --------------- ------------ ------------ ---------------
APS        OLAP Analytic Workspace             11.2.0.4.0      VALID        SERVER       SYS
CATALOG    Oracle Database Catalog Views       11.2.0.4.0      VALID        SERVER       SYS
CATJAVA    Oracle Database Java Packages       11.2.0.4.0      VALID        SERVER       SYS
CATPROC    Oracle Database Packages and Types  11.2.0.4.0      VALID        SERVER       SYS
CONTEXT    Oracle Text                         11.2.0.4.0      VALID        SERVER       CTXSYS
EXF        Oracle Expression Filter            11.2.0.4.0      VALID        SERVER       EXFSYS
JAVAVM     JServer JAVA Virtual Machine        11.2.0.4.0      VALID        SERVER       SYS
ORDIM      Oracle Multimedia                   11.2.0.4.0      VALID        SERVER       ORDSYS
OWB        OWB                                 11.2.0.4.0      VALID        SERVER       OWBSYS
OWM        Oracle Workspace Manager            11.2.0.4.0      VALID        SERVER       WMSYS
RUL        Oracle Rules Manager                11.2.0.4.0      VALID        SERVER       EXFSYS
SDO        Spatial                             11.2.0.4.0      VALID        SERVER       MDSYS
XDB        Oracle XML Database                 11.2.0.4.0      VALID        SERVER       XDB
XML        Oracle XDK                          11.2.0.4.0      VALID        SERVER       SYS
XOQ        Oracle OLAP API                     11.2.0.4.0      VALID        SERVER       SYS

15 rows selected.

++++++++++
Export error using FLASHBACK_TIME

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Took a some time but finally found a work around other than using FLASHBACK_SCN which is hokie.

expdp_full.par

USERID="/ as sysdba"
directory=DATA_PUMP_DIR
METRICS=Y
PARALLEL=4
FULL=Y
DUMPFILE=full%U.dmp
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(systimestamp,'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS')"
EXCLUDE=statistics
exclude=schema:"IN ('WMSYS','SYSMAN')"
LOGFILE=exp_full.log

++++++++++
Create 12c non-PDB database.

whenever sqlerror exit sql.sqlcode
whenever oserror exit 1
set echo on
shu abort;
create spfile from pfile;
startup force nomount;
CREATE DATABASE
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE 
GROUP 1 size 100M,GROUP 2 size 100M,GROUP 3 size 100M
EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 257M AUTOEXTEND ON NEXT 256M MAXSIZE 30721M
SYSAUX DATAFILE SIZE 257M AUTOEXTEND ON NEXT 256M MAXSIZE 30721M
DEFAULT TABLESPACE USERS DATAFILE SIZE 257M AUTOEXTEND ON NEXT 257M MAXSIZE 30721M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 30720M
UNDO TABLESPACE UNDOTBS1 DATAFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 30720M
;
set echo off
@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catproc.sql;
@?/rdbms/admin/catblock.sql;
@?/rdbms/admin/catoctk.sql;
@?/rdbms/admin/catio.sql
@?/rdbms/admin/dbmsiotc.sql
@?/rdbms/admin/dbmspool.sql
@?/rdbms/admin/userlock.sql
@?/rdbms/admin/utlrp.sql
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
connect system/oracle
@?/sqlplus/admin/pupbld.sql
exit

++++++++++
Check for errors.
grep '^ORA-' create_db_omf.tiger.out |cut -d ":" -f1|sort -u

ORA-00942
ORA-01432
ORA-01434
ORA-01921
ORA-02289
ORA-04043
ORA-29807

oerr ora 1432

01432, 00000, "public synonym to be dropped does not exist"
// *Cause:
// *Action:

grep '^ORA-' create_db_omf.tiger.out |cut -d ":" -f2|grep -v "not exist"|sort -u

 role name 'EXP_FULL_DATABASE' conflicts with another user or role
 role name 'IMP_FULL_DATABASE' conflicts with another user or role

++++++++++
Don’t use FILESIZE – I know, there are presentation showing usage of FILESIZE.

Example:
FILESIZE=8g
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

++++++++++
DATAFILE SIZE 2097152000 is larger than MAXSIZE 1048576000.

ORA-39083: Object type TABLESPACE:"USERS" failed to create with error:
ORA-02494: invalid or missing maximum file size in MAXSIZE clause
Failing sql is:
CREATE TABLESPACE "USERS" DATAFILE SIZE 2097152000 AUTOEXTEND ON NEXT 104857600 MAXSIZE 1048576000

Related error:

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39083: Object type TABLE: failed to create with error:
ORA-01950: no privileges on tablespace 'USERS'

++++++++++
SYS triggers need to be extracted from Source and created at Target.
Remember this one – OJVM?

SQL> CREATE OR REPLACE TRIGGER "SYS"."DBMS_JAVA_DEV_TRG" before create
2 on database
3 begin
4 if (ora_dict_obj_type='JAVA')
5 then
6 raise_application_error(-20031,'Java Development Disabled');
7 end if;
8 end;
9 /
Trigger created.

SQL> ALTER TRIGGER "SYS"."DBMS_JAVA_DEV_TRG" DISABLE;

Trigger altered.
SYS@SQL>

++++++++++
Tablespaces were pre-created since datafile locations changed.  Alternatively, use REMAP_DATAFILE.

set echo off head off verify off feedb off pages 0 long 10000 longchunk 10000 trimspool on lines 2000 timing off term off
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',TRUE);
define _filename=none
COL name NEW_VALUE name
select name from v$database;
define _filename='tbs_&name..sql'
spool &_filename
select dbms_metadata.get_ddl('TABLESPACE',TABLESPACE_NAME) from dba_tablespace
;
spool off
set term on
pro
pro *** Spool Output: &_filename
pro
pro
exit

++++++++++
Import done in 2 passes, impdp_full_user.par and impdp_full.par

impdp_full_user.par

directory=DATA_PUMP_DIR
USERID="/ as sysdba"
METRICS=Y
PARALLEL=4
FULL=Y
DUMPFILE=full%U.dmp
LOGFILE=imp_user.log
INCLUDE=user,role,default_role,tablespace_quota,role_grant,profile,password_verify_function,tablespace

Want to detect as many issues as possible before actual data import.
ORA-39083: Object type TABLESPACE:"USERS" failed to create with error

ORA-39083: Object type TABLE: failed to create with error:
ORA-01950: no privileges on tablespace 'USERS'

++++++++++
grep -v ORA-31684 imp_user.log | grep -A 1 Processing

Processing object type DATABASE_EXPORT/TABLESPACE
     Completed 26 TABLESPACE objects in 1 seconds
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
     Completed 1 PASSWORD_VERIFY_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
     Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYS_USER/USER
     Completed 1 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/USER
     Completed 7796 USER objects in 20 seconds
Processing object type DATABASE_EXPORT/ROLE
     Completed 321 ROLE objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
     Completed 21442 ROLE_GRANT objects in 129 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
     Completed 7796 DEFAULT_ROLE objects in 10 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
     Completed 7790 TABLESPACE_QUOTA objects in 24 seconds

++++++++++
impdp_full.par

directory=DATA_PUMP_DIR
USERID="/ as sysdba"
METRICS=Y
PARALLEL=4
FULL=Y
DUMPFILE=full%U.dmp
LOGFILE=imp_full.log
EXCLUDE=user,role,default_role,tablespace_quota,role_grant,profile,password_verify_function,tablespace
STREAMS_CONFIGURATION=NO

++++++++++
grep -v ORA-31684 imp_full.log | grep -A 1 Processing

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
     Completed 6 PROC_SYSTEM_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
     Completed 145 SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/RESOURCE_COST
     Completed 1 RESOURCE_COST objects in 0 seconds
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
     Completed 1 TRUSTED_DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
     Completed 197 SEQUENCE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 205 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
     Completed 7 DIRECTORY objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 12 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/CONTEXT
     Completed 7 CONTEXT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
     Completed 1139 SYNONYM objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
     Completed 1014 SYNONYM objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
     Completed 180 TYPE objects in 3 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
--
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
     Completed 3 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
     Completed 1302 TABLE objects in 18 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Startup took 356 seconds
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 68094 OBJECT_GRANT objects in 91 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
     Completed 4 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
     Completed 238 CONSTRAINT objects in 86 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
     Completed 696 COMMENT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
     Completed 1 AUDIT_OBJ objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/RLS_POLICY
     Completed 68 RLS_POLICY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
     Completed 148 PACKAGE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 195 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
     Completed 42 FUNCTION objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 52 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
     Completed 16 PROCEDURE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
     Completed 42 ALTER_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
     Completed 16 ALTER_PROCEDURE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
     Completed 72 VIEW objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 155 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
     Completed 62 COMMENT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/RLS_POLICY
     Completed 4 RLS_POLICY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
     Completed 148 PACKAGE_BODY objects in 4 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
     Completed 16 TYPE_BODY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
ORA-39083: Object type INDEX: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
     Completed 68 TRIGGER objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
     Completed 3 TRIGGER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
     Completed 1 MATERIALIZED_VIEW objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/JOB
     Completed 5 JOB objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
     Completed 3 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
--
Processing object type DATABASE_EXPORT/AUDIT
     Completed 5 AUDIT objects in 0 seconds

August 27, 2013

12c Grid Infrastructure Standalone Silent Upgrade

Filed under: 12c,upgrade — mdinh @ 1:17 pm

This is what insomnia will do to you.

After Apply PSU 11.2.0.3.7 for Grid Infrastructure Standalone and DB with Oracle Restart, I was curious to see if I can upgrade just the Grid Infrastructure to 12c.

I came across something new that I have not seen before. Uh oh! Time to go stealing.

Successfully Setup Software.
As install user, execute the following script to complete the configuration.
        1. /u01/app/12.1.0/grid/cfgtoollogs/configToolAllCommands RESPONSE_FILE=<response_file>

        Note:
        1. This script must be run on the same host from where installer was run.
        2. This script needs a small password properties file for configuration assistants that require passwords (refer to install guide documentation).

There are not many posts with silent operation. Luckily for me, look at what I found: How to install Grid Infrastructure 12.1 in silent mode

Can you believe it? Upgrade completed successfully.

12c_grid_silent_upgrade log
grid12c_upgrade.rsp

Should I shorten the response file to make it cleaner or keep it that way for clarity?

July 3, 2013

Upgrade to 12c using DBUA Silent (shhh) Mode

Filed under: 12c,upgrade — mdinh @ 1:52 am

Single instance database upgrade from 11.2.0.3 to 12.1.0.1.0.

There’s plenty of install posts already out there; hence, I am not going to blog about it.

If you find a post on DBUA Silent mode, then please let me know.

Oracle Database 12c Release 1 (12.1) DBUA in Silent mode [ID 1516616.1]

First attempt failed with errors:

ORA-00823: Specified value of sga_target greater than sga_max_size
ORA-01078: failure in processing system parameters

ORA-00823 – Specified value of sga_target greater than sga_max_size Error During Upgrade Using DBUA [ID 1439671.1]

As an alternative solution SGA_MAX_SIZE can be removed as it is not mandatory while upgrade

Is Oracle lying to me? Let’s look at spfile before and after.

$ tail spfiledb01.ora.orig

*.processes=50
*.remote_logCC"-in_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_max_size=512M
*.sga_target=512M
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_tablespace='undotbs'
*.workarea_size_policy='AUTO'

$ tail spfiledb01.ora

*.pga_aggregate_target=128M
*.processes=50
*.remoCC"n&te_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_target=512M
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_tablespace='undotbs'
*.workarea_size_policy='AUTO'

[oracle@lax:db01]/home/oracle
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/dbua -silent \
> -sid db01 \
> -oracleHome /u01/app/oracle/product/11.2.0.3/db_1 \
> -diagnosticDest /u01/app/oracle \
> -autoextendFiles \
> -recompile_invalid_objects true \
> -degree_of_parallelism 2 \
> -upgradeTimezone \
> -emConfiguration NONE \
> -keepHiddenParams \
> -gatheringStatistics \
> -createGRP true \
> -upgrade_parallelism 2

Log files for the upgrade operation are located at: /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
Performing Database Backup
16% complete
Performing Pre Upgrade
20% complete
33% complete
Performing RDBMS Upgrade
34% complete
49% complete
50% complete
Performing Post Upgrade
51% complete
53% complete
66% complete
Upgrading Timezone
83% complete
Generating Summary
Database upgrade has been completed successfully, and the database is ready to use.
100% complete
Check the log file "/u01/app/oracle/cfgtoollogs/dbua/logs/silent.log_1372773594442" for upgrade details.

DBUA performed the following actions:

Pre-Upgrade Checks

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 unless there were over 5000. 
Use utluiobj.sql after the upgrade to identify any new invalid objects due to the upgrade.
It is required that the recycle bin is empty prior to upgrading your database. 
Run "PURGE DBA_RECYCLEBIN" command before continuing with your upgrade.

Initialization Parameter changes

The following changes will be made in the initialization parameters:
sga_target 536870912|624951296 (old|new)
processes  50|300

Obsolete Parameters to be removed

sec_case_sensitive_logon

Timezone Upgrade

The time zone version and TIMESTAMP WITH TIME ZONE data of the Database will be upgraded from version 14 to version 18.

Recovery options

Create a new guaranteed restore point before upgrade and flashback the database in case of any upgrade failure.

Upgrade Details

The following summary lists the steps performed during the upgrade process.
Log files for all the steps, as well as this summary, are available at “/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1”.

Database Backup     -Backup.log
Pre Upgrade         -PreUpgrade.log (PreUpgradeResults.html is a lot better)
RDBMS Upgrade       -Oracle_Server.log
Post Upgrade        -PostUpgrade.log
Timezone Upgrade    -UpgradeTimezone.log
Generate Summary    -generateSummary.log (does not exist)

Let’s review the logs.

$ORACLE_BASE=/u01/app/oracle

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/logs
$ ls -l

total 64
-rw-r----- 1 oracle oinstall  3592 Jul  2 10:48 silent.log_1372773594442
-rw-r----- 1 oracle oinstall 17721 Jul  2 07:02 sqls.log
-rw-r----- 1 oracle oinstall 38554 Jul  2 07:02 trace.log

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/logs
$ head -20 silent.log_1372773594442

Upgrading the database dbName="db01"
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 unless there were over 5000. Use utluiobj.sql after the upgrade to identify any new invalid objects due to the upgrade.
Your recycle bin contains objects. It is required that the recycle bin is empty prior to upgrading your database. Run "PURGE DBA_RECYCLEBIN" command before continuing with your upgrade.
Supported upgrade version check succeeded.
The oratab permission check succeeded.
Oracle Home Owner Check succeeded.
Database does not use ASM storage.
Space usage summary

Space usage reason:  Upgrade

Tablespace Name:  SYSTEM
Additional space required:  159 MB
Datafile:  /oracle/oradata/db02/system_1.dbf
Auto Extensible:  Yes

Tablespace Name:  SYSAUX
Additional space required:  244 MB
Datafile:  /oracle/oradata/db02/sysaux_1.dbf
Auto Extensible:  Yes

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/logs
$ tail -20 silent.log_1372773594442

UPGRADE_PROGRESS : 48%
UPGRADE_PROGRESS : 48%
UPGRADE_PROGRESS : 48%
UPGRADE_PROGRESS : 49%
UPGRADE_PROGRESS : 49%
UPGRADE_PROGRESS : 49%
UPGRADE_PROGRESS : 50%
Performing Post Upgrade
UPGRADE_PROGRESS : 51%
UPGRADE_PROGRESS : 53%
UPGRADE_PROGRESS : 66%
Upgrading Timezone
UPGRADE_PROGRESS : 83%
Generating Summary
UPGRADE_PROGRESS : 100%
Database upgrade has been completed successfully, and the database is ready to use.

The following document describes important behavioral changes from previous database releases:
/u01/app/oracle/product/12.1.0/dbhome_1/assistants/dbua/doc/DefaultBehaviorChanges_<NLS_LANG>.html

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ ls -alrt

total 24208
-rw-r----- 1 oracle oinstall        0 Jul  2 07:02 trace.log.lck
drwxr-x--- 3 oracle oinstall     4096 Jul  2 07:02 ..
-rw-r----- 1 oracle oinstall     2595 Jul  2 07:04 upgrade.xml1
-rw-r----- 1 oracle oinstall     2256 Jul  2 07:07 upgrade.xml
-rw-r----- 1 oracle oinstall     4504 Jul  2 07:07 PreUpgradeResults.html
-rw-r----- 1 oracle oinstall        0 Jul  2 07:07 Backup.log
-rw-r----- 1 oracle oinstall     1168 Jul  2 07:07 init.ora
-rwxr-xr-x 1 oracle oinstall      173 Jul  2 07:07 createSPFile_db01.sql
-rwxr-xr-x 1 oracle oinstall      127 Jul  2 07:07 grpOpen_db01.sql
-rwxr-xr-x 1 oracle oinstall     1257 Jul  2 07:07 db01_restore.sh
-rwxr-xr-x 1 oracle oinstall       42 Jul  2 07:07 shutdown_db01.sql
-rwxr-xr-x 1 oracle oinstall       35 Jul  2 07:07 startup_db01.sql
-rw-r----- 1 oracle oinstall      486 Jul  2 07:10 PreUpgrade.log
-rw-r----- 1 oracle oinstall  8407445 Jul  2 10:16 catupgrd1.log
-rw-r----- 1 oracle oinstall 15815542 Jul  2 10:16 catupgrd0.log
-rw-r----- 1 oracle oinstall   152750 Jul  2 10:20 Oracle_Server.log
-rw-r----- 1 oracle oinstall     9703 Jul  2 10:34 PostUpgrade.log
-rw-r----- 1 oracle oinstall      364 Jul  2 10:48 UpgradeTimezone.log
-rw-r----- 1 oracle oinstall     6323 Jul  2 10:48 UpgradeResults.html
-rw-r----- 1 oracle oinstall   212540 Jul  2 10:48 trace.log
-rw-r----- 1 oracle oinstall   101483 Jul  2 10:48 sqls.log
drwxr-x--- 2 oracle oinstall     4096 Jul  2 14:38 .

-createGRP true (NF –¬†create a guaranteed restore point when database is in archive log and flashback mode)

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat db01_restore.sh

#!/bin/sh

# -- Run this Script to Restore Oracle Database Instance db01
echo -- Bringing down the database from the new oracle home
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=db01; export ORACLE_SID
/u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/shutdown_db01.sql
echo You should Remove this entry from the /etc/oratab: db01:/u01/app/oracle/product/12.1.0/dbhome_1:Y
echo -- Bringing up the database from the source oracle home
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1; export ORACLE_HOME
ORACLE_SID=db01; export ORACLE_SID
unset LD_LIBRARY_PATH; unset LD_LIBRARY_PATH_64; unset SHLIB_PATH; unset LIB_PATH
echo You should Add this entry in the /etc/oratab: db01:/u01/app/oracle/product/11.2.0.3/db_1:Y
cd /u01/app/oracle/product/11.2.0.3/db_1
echo -- Removing /u01/app/oracle/cfgtoollogs/dbua/logs/Welcome_db01.txt file
rm -f /u01/app/oracle/cfgtoollogs/dbua/logs/Welcome_db01.txt ;
/u01/app/oracle/product/11.2.0.3/db_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/createSPFile_db01.sql
/u01/app/oracle/product/11.2.0.3/db_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/grpOpen_db01.sql

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/shutdown_db01.sql

connect / as sysdba
shutdown abort;
exit;
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/createSPFile_db01.sql
connect / as sysdba
CREATE SPFILE='/u01/app/oracle/product/11.2.0.3/db_1/dbs/spfiledb01.ora' from pfile='/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/init.ora';
exit;

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/grpOpen_db01.sql

connect / as sysdba
startup mount;
flashback database to restore point grp_1372774067744;
alter database open resetlogs;
exit;

Improved sysresv
$ sysresv

IPC Resources for ORACLE_SID "db01" :
Maximum shared memory segment size (shmmax): 4398046511104 bytes
Total system shared memory (shmall): 4398046511104 bytes
Total system shared memory count (shmmni): 4096
Total /dev/shm size: 1581465600 bytes, used: 278528 bytes
Shared Memory:
ID              KEY
24084484        0x450f83c4
24117253        0x00000000
Semaphores:
ID              KEY
1867778         0x0c97f794
1900547         0x0c97f795
1933316         0x0c97f796
Oracle Instance alive for sid "db01"

Don’t forget to change compatible
$ sqlplus “/ as sysdba”

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 2 14:54:29 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

LAX:(SYS@db01)> show parameter compat

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.3
noncdb_compatible                    boolean     FALSE
plsql_v2_compatibility               boolean     FALSE
LAX:(SYS@db01)> exit

November 7, 2010

Upgrade Pain01

Filed under: oracle,upgrade — mdinh @ 2:07 pm

Completed upgrade of OLTP database from 10.2.0.4 to 11.2.0.2

To my surprise, I found 199 dead locks and increasing fast in the alert log as shown:

> grep -ic ‘ORA-00060: Deadlock detected’ alert_db06.log
199

The trace file shows:

*** 2010-11-07 03:53:20.238
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TM-0003b69d-00000000 113 38 SX SSX 119 221 SX SSX
TM-0003b69d-00000000 119 221 SX SSX 113 38 SX SSX

session 38: DID 0001-0071-00000004 session 221: DID 0001-0077-00000004
session 221: DID 0001-0077-00000004 session 38: DID 0001-0071-00000004

Rows waited on:
Session 38: no row
Session 221: no row

—– Information for the OTHER waiting sessions —–
Session 221:
sid: 221 ser: 4 audsid: 795496700 user: 211/JOHN
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 119 O/S info: user: oracle, term: UNKNOWN, ospid: 25907
image: oracle@proddb06
client details:
O/S info: user: john, term: unknown, ospid: 1234
machine: terminator program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
DELETE FROM parent WHERE pk_id = :”SYS_B_0″

The following deadlock is not an ORACLE error. It is a deadlock due to the design of an application

Try telling this to the duhvelopers.

Before going further, I would like to thank Google and everyone who shares his/her knowledge and pain.

google: deadlock 11g and found Deadlock on Oracle 11g but Not on 10g

Root cause analysis:

  • Bad Design
  • Anecdotal Upgrade Testing
  • Upgrade
  • Not spending enough time on the internet
  • July 12, 2010

    Using DBUA in Silent Mode

    Filed under: 11g,oracle,upgrade — mdinh @ 5:38 pm

    I just completed upgrade to 11gR2 for a test and small reporting database.

    This time I used DBUA (recommended) versus the manual process.

    /u01/app/oracle/product/11gR2/dbhome_1/bin/dbua -silent \
    -sid dbtest \
    -oracleHome /u01/app/oracle/product/10.2.0/db_1 \
    -diagnosticDest /u01/app/oracle \
    -autoextendFiles \
    -recompile_invalid_objects true \
    -degree_of_parallelism 4 \
    -emConfiguration NONE

    The summary log files are located at $ORACLE_BASE/cfgtoollogs/dbua/logs (ORACLE_BASE=/u01/app/oracle)

    oracle@proddbtest:dbtest:/u01/app/oracle/cfgtoollogs/dbua/logs
    > ll
    total 144
    drwxr-x--- 4 oracle dba 96 Jun 18 10:11 ..
    -rw-r----- 1 oracle dba 19287 Jun 22 10:03 sqls.log
    -rw-r----- 1 oracle dba 28750 Jun 22 10:03 trace.log
    -rw-r----- 1 oracle dba 1833 Jun 22 10:38 silent.log
    drwxr-x--- 2 oracle dba 8192 Jun 22 10:38 .

    The detailed logs are located at $ORACLE_BASE/cfgtoollogs/dbua/<db_unique_name>/upgrade#

    Example: db_name=dbtest and db_unique_name=dc_dbtest (dc is the name for data center)

    oracle@proddbtest:dbtest:/u01/app/oracle/cfgtoollogs/dbua
    > ll
    total 16
    drwxr-x--- 5 oracle dba 96 Jun 18 10:10 ..
    drwxr-x--- 4 oracle dba 96 Jun 18 10:11 .
    drwxr-x--- 4 oracle dba 96 Jun 22 10:03 dc_dbtest
    drwxr-x--- 2 oracle dba 8192 Jun 22 10:38 logs

    I attempted 2 upgrades just so I can get the feel of things.

    oracle@proddbtest:dbtest:/u01/app/oracle/cfgtoollogs/dbua/dc_dbtest
    > ll
    total 32
    drwxr-x--- 4 oracle dba 96 Jun 18 10:11 ..
    drwxr-x--- 2 oracle dba 8192 Jun 18 10:13 upgrade1
    drwxr-x--- 4 oracle dba 96 Jun 22 10:03 .
    drwxr-x--- 2 oracle dba 8192 Jun 22 10:38 upgrade2

    oracle@proddbtest:dbtest:/u01/app/oracle/cfgtoollogs/dbua/dc_dbtest/upgrade2
    > ll
    total 41136
    drwxr-x--- 4 oracle dba 96 Jun 22 10:03 ..
    -rw-r----- 1 oracle dba 1732 Jun 22 10:03 upgrade.xml
    -rw-r----- 1 oracle dba 1167 Jun 22 10:03 Upgrade_Directive.log
    -rw-r----- 1 oracle dba 397 Jun 22 10:03 SpaceUsage.txt
    -rw-r----- 1 oracle dba 5210 Jun 22 10:03 PreUpgradeResults.html
    -rw-r----- 1 oracle dba 442 Jun 22 10:04 PreUpgrade.log
    -rw-r----- 1 oracle dba 20751782 Jun 22 10:36 Oracle_Server.log
    -rw-r----- 1 oracle dba 9317 Jun 22 10:38 PostUpgrade.log
    drwxr-x--- 2 oracle dba 8192 Jun 22 10:38 .
    -rw-r----- 1 oracle dba 6087 Jun 22 10:38 UpgradeResults.html
    -rw-r----- 1 oracle dba 155467 Jun 22 10:38 trace.log
    -rw-r----- 1 oracle dba 73544 Jun 22 10:38 sqls.log

    More information can be found in the upgrade guide (open new window)

    Create a free website or blog at WordPress.com.