Manual Upgrade (PART 4)

 

8. After log examination, execute utlu121s script.

[oracle@upgrade admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 2 12:47:22 2015

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 616562688 bytes

Fixed Size 2927384 bytes

Variable Size 507512040 bytes

Database Buffers 100663296 bytes

Redo Buffers 5459968 bytes

Database mounted.

Database opened.

SQL> @utlu121s.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

CATCTL REPORT = /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/imc12c/upgrade/upg_summary.log

PL/SQL procedure successfully completed.

Oracle Database 12.1 Post-Upgrade Status Tool 11-02-2015 12:48:03

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

Oracle Server UPGRADED 12.1.0.2.0 00:16:11

JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:00:00

Oracle Real Application Clusters OPTION OFF 12.1.0.2.0 00:00:01

Oracle Workspace Manager VALID 12.1.0.2.0 00:00:00

OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:00

Oracle OLAP API VALID 12.1.0.2.0 00:00:00

Oracle Label Security VALID 12.1.0.2.0 00:00:00

Oracle XDK VALID 12.1.0.2.0 00:00:00

Oracle Text VALID 12.1.0.2.0 00:00:00

Oracle XML Database VALID 12.1.0.2.0 00:00:00

Oracle Database Java Packages VALID 12.1.0.2.0 00:00:00

Oracle Multimedia VALID 12.1.0.2.0 00:00:00

Spatial VALID 12.1.0.2.0 00:00:00

Oracle Application Express VALID 4.2.5.00.08 00:00:00

Oracle Database Vault VALID 12.1.0.2.0 00:00:00

Final Actions 00:00:41

Post Upgrade 00:07:13

Total Upgrade Time: 00:24:11

PL/SQL procedure successfully completed.

SQL>

SQL> —

SQL> — Update Summary Table with con_name and endtime.

SQL> —

SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName,

2 con_name = SYS_CONTEXT(‘USERENV’,’CON_NAME’),

3 endtime = SYSDATE

4 WHERE con_id = -1;

1 row updated.

SQL> commit;

Commit complete.

9. Finally , execute post upgrade script.

 

SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

SQL> REM Post Upgrade Script Generated on: 2015-11-02 08:09:30

SQL> REM Generated by Version: 12.1.0.2 Build: 006

SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 750;

PL/SQL procedure successfully completed.

10. Please , check the condition with sqlplus.

 

[oracle@upgrade admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 2 13:01:56 2015

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT name, open_mode FROM v$database;

NAME OPEN_MODE

——— ——————–

ORCL READ WRITE

Summary

This documants guiding to upgrade database .All of upgrade methods are helping you during upgrade time. In addition to I would like to thank g.g. for your helping .

References for All Upgrade Posts

1.Oracle Upgrade 12c Website

https://oracle-base.com/articles/12c/upgrading-to-12c

2.Oracle Upgrade 12c Doc

https://docs.oracle.com/database/121/UPGRD/toc.htm

3.Oracle Upgrade 12c Doc

https://docs.oracle.com/database/121/UPGRD/title.htm

4.Oracle Upgrade 12c Doc

Click to access upgrading-oracle-database-wp-12c-1896123.pdf

5.Oracle Upgrade 12c Website

http://ioracle-dba.blogspot.com.tr/2015/08/upgrade-oracle-11204-database-to-oracle.html

 

Manual Upgrade (PART 3)

7. After the script execution, please take a look to upgrade log .
[oracle@upgrade db_1]$ vi /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/imc12c/upgrade/upg_summary.log
JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:00:00
Oracle Real Application Clusters OPTION OFF 12.1.0.2.0 00:00:01
Oracle Workspace Manager VALID 12.1.0.2.0 00:00:00
OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:00
Oracle OLAP API VALID 12.1.0.2.0 00:00:00
Oracle Label Security VALID 12.1.0.2.0 00:00:00
Oracle XDK VALID 12.1.0.2.0 00:00:00
Oracle Text VALID 12.1.0.2.0 00:00:00
Oracle XML Database VALID 12.1.0.2.0 00:00:00
Oracle Database Java Packages VALID 12.1.0.2.0 00:00:00
Oracle Multimedia VALID 12.1.0.2.0 00:00:00
Spatial VALID 12.1.0.2.0 00:00:00
Oracle Application Express VALID 4.2.5.00.08 00:00:00
Oracle Database Vault VALID 12.1.0.2.0 00:00:00
Final Actions 00:00:41
Post Upgrade 00:07:13
Total Upgrade Time: 00:24:11
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
Grand Total Upgrade Time: [0d:0h:25m:18s]

Manual Upgrade (PART 2)

3. Apply these recommended settings on database.

SQL>ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;

SQL>SET ECHO ON;

SQL>SERVEROUTPUT ON;

SQL>@ /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/emremove.sql

SQL>@ ?/olap/admin/catnomad.sql

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

4. Move spfile and password file from Oracle Database 11g’s folder to Oracle Database 12c’s folder .

 

[oracle@upgrade dbs]$ cp spfileorcl.ora /u01/app/oracle/product/12.1.0.2/db_1/dbs/

[oracle@upgrade dbs]$ cp orapworcl /u01/app/oracle/product/12.1.0.2/db_1/dbs/

5. Almost done with configuration, set new environment variables.

 

[oracle@upgrade db_1]$ export ORACLE_SID=orcl

[oracle@upgrade db_1]$ ORAENV_ASK=NO

[oracle@upgrade db_1]$ . oraenv

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@upgrade db_1]$ ORAENV_ASK=YES

[oracle@upgrade db_1]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 2 08:29:05 2015

Connected to an idle instance.

SQL> STARTUP UPGRADE;

ORACLE instance started.

Total System Global Area 613797888 bytes

Fixed Size 2255712 bytes

Variable Size 457180320 bytes

Database Buffers 150994944 bytes

Redo Buffers 3366912 bytes

Database mounted.

Database opened.

SQL>exit;

6. In Oracle Database 12c folder, execute script in admin directory.

 

[oracle@upgrade ~]$ cd $ORACLE_HOME/rdbms/admin

[oracle@upgrade admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

Argument list for [catctl.pl]

SQL Process Count n = 0

SQL PDB Process Count N = 0

Input Directory d = 0

Phase Logging Table t = 0

Log Dir l = 0

Script s = 0

Serial Run S = 0

Upgrade Mode active M = 0

Start Phase p = 0

End Phase P = 0

Log Id i = 0

Run in c = 0

Do not run in C = 0

Echo OFF e = 1

No Post Upgrade x = 0

Reverse Order r = 0

Open Mode Normal o = 0

Debug catcon.pm z = 0

Debug catctl.pl Z = 0

Display Phases y = 0

Child Process I = 0

catctl.pl version: 12.1.0.2.0

Oracle Base = /u01/app/oracle

Analyzing file catupgrd.sql

Log files in /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin

catcon: ALL catcon-related output will be written to catupgrd_catcon_13411.lst

catcon: See catupgrd*.log files for output generated by scripts

catcon: See catupgrd_*.lst files for spool files, if any

Number of Cpus = 1

SQL Process Count = 0

New SQL Process Count = 4

——————————————————

Phases [0-73]

Serial Phase #: 0 Files: 1

Time: 21s

Serial Phase #: 1 Files: 5

Time: 61s

Restart Phase #: 2 Files: 1 Time: 0s

Parallel Phase #: 3 Files: 18 Time: 29s

Restart Phase #: 4 Files: 1 Time: 0s

Serial Phase #: 5 Files: 5

Time: 22s

Serial Phase #: 6 Files: 1

Time: 12s

Serial Phase #: 7 Files: 4 Time: 17s

Restart Phase #: 8 Files: 1 Time: 0s

Parallel Phase #: 9 Files: 62

Time: 110s

Restart Phase #:10 Files: 1 Time: 0s

Serial Phase #:11 Files: 1

Time: 19s

Restart Phase #:12 Files: 1 Time: 0s

Parallel Phase #:13 Files: 91 Time: 12s

Restart Phase #:14 Files: 1 Time: 1s

Parallel Phase #:15 Files: 111 Time: 32s

Restart Phase #:16 Files: 1 Time: 0s

Serial Phase #:17 Files: 3 Time: 2s

Restart Phase #:18 Files: 1 Time: 0s

Parallel Phase #:19 Files: 32 Time: 43s

Restart Phase #:20 Files: 1 Time: 0s

Serial Phase #:21 Files: 3 Time: 10s

Restart Phase #:22 Files: 1 Time: 1s

Parallel Phase #:23 Files: 23 Time: 155s

Restart Phase #:24 Files: 1 Time: 0s

Parallel Phase #:25 Files: 11

Time: 96s

Restart Phase #:26 Files: 1 Time: 0s

Serial Phase #:27 Files: 1 Time: 0s

Restart Phase #:28 Files: 1 Time: 0s

Serial Phase #:30 Files: 1 Time: 0s

Serial Phase #:31 Files: 257 Time: 40s

Serial Phase #:32 Files: 1 Time: 0s

Restart Phase #:33 Files: 1 Time: 0s

Serial Phase #:34 Files: 1 Time: 4s

Restart Phase #:35 Files: 1 Time: 0s

Restart Phase #:36 Files: 1 Time: 0s

Serial Phase #:37 Files: 4 Time: 76s

Restart Phase #:38 Files: 1 Time: 0s

Parallel Phase #:39 Files: 13 Time: 185s

Restart Phase #:40 Files: 1 Time: 0s

Parallel Phase #:41 Files: 10 Time: 12s

Restart Phase #:42 Files: 1 Time: 0s

Serial Phase #:43 Files: 1 Time: 9s

Restart Phase #:44 Files: 1 Time: 0s

Serial Phase #:45 Files: 1 Time: 3s

Serial Phase #:46 Files: 1 Time: 0s

Restart Phase #:47 Files: 1 Time: 0s

Serial Phase #:48 Files: 1 Time: 0s

Restart Phase #:49 Files: 1 Time: 0s

Serial Phase #:50 Files: 1 Time: 1s

Restart Phase #:51 Files: 1 Time: 0s

Serial Phase #:52 Files: 1 Time: 0s

Restart Phase #:53 Files: 1 Time: 0s

Serial Phase #:54 Files: 1 Time: 0s

Restart Phase #:55 Files: 1 Time: 0s

Serial Phase #:56 Files: 1 Time: 2s

Restart Phase #:57 Files: 1 Time: 0s

Serial Phase #:58 Files: 1 Time: 1s

Restart Phase #:59 Files: 1 Time: 0s

Serial Phase #:60 Files: 1 Time: 0s

Restart Phase #:61 Files: 1 Time: 0s

Serial Phase #:62 Files: 1 Time: 0s

Restart Phase #:63 Files: 1 Time: 1s

Serial Phase #:64 Files: 1 Time: 1s

Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0.2/db_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > catupgrd_datapatch_upgrade.log 2> catupgrd_datapatch_upgrade.err

returned from sqlpatch

Time: 41s

Serial Phase #:66 Files: 1 Time: 1s

Serial Phase #:68 Files: 1 Time: 0s

Serial Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0.2/db_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > catupgrd_datapatch_normal.log 2> catupgrd_datapatch_normal.err

returned from sqlpatch

Time: 45s

Serial Phase #:70 Files: 1 Time: 435s

Serial Phase #:71 Files: 1 Time: 1s

Serial Phase #:72 Files: 1 Time: 0s

Serial Phase #:73 Files: 1 Time: 16s

Grand Total Time: 1518s

LOG FILES: (catupgrd*.log)

Upgrade Summary Report Located in:

/u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/imc12c/upgrade/upg_summary.log

Grand Total Upgrade Time: [0d:0h:25m:18s]

Manual Upgrade (PART 1)

 

Although Database Upgrade Assistant (DBUA) is the popular way for upgrade, Manual Upgrade, upgrade with scripts, could be seen as an alternative way of doing this. This process needs necessary scripts to upgrade the database. Such as;

 

1. Execute preupgrd script for checking system prerequisites before upgrade .

 

SQL> @preupgrd

Loading Pre-Upgrade Package…

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

Executing Pre-Upgrade Checks in ORCL…

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

====>> ERRORS FOUND for ORCL <<====

The following are *** ERROR LEVEL CONDITIONS *** that must be addressed

prior to attempting your upgrade.

Failure to do so will result in a failed upgrade.

You MUST resolve the above errors prior to upgrade

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

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

====>> PRE-UPGRADE RESULTS for ORCL <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:

/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:

/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:

/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql

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

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

Pre-Upgrade Checks in ORCL Completed.

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

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

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

2. Execute preupgrade_fixups script .

 

SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql

Pre-Upgrade Fixup Script Generated on 2015-11-02 07:53:33 Version: 12.1.0.2 Bui ld: 006

Beginning Pre-Upgrade Fixups…

Executing in container ORCL

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

Check Tag: DEFAULT_PROCESS_COUNT

Check Summary: Verify min process count is not too low

Fix Summary: Review and increase if needed, your PROCESSES value.

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

Fixup Returned Information:

WARNING: –> Process Count may be too low

Database has a maximum process count of 150 which is lower than the

default value of 300 for this release.

You should update your processes value prior to the upgrade

to a value of at least 300.

For example:

ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE

or update your init.ora file.

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

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

Check Tag: EM_PRESENT

Check Summary: Check if Enterprise Manager is present

Fix Summary: Execute emremove.sql prior to upgrade.

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

Fixup Returned Information:

WARNING: –> Enterprise Manager Database Control repository found in the databas e

In Oracle Database 12c, Database Control is removed during

the upgrade. To save time during the Upgrade, this action

can be done prior to upgrading using the following steps after

copying rdbms/admin/emremove.sql from the new Oracle home

– Stop EM Database Control:

$> emctl stop dbconsole

– Connect to the Database using the SYS account AS SYSDBA:

SET ECHO ON;

SET SERVEROUTPUT ON;

@emremove.sql

Without the set echo and serveroutput commands you will not

be able to follow the progress of the script.

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

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

Check Tag: AMD_EXISTS

Check Summary: Check to see if AMD is present in the database

Fix Summary: Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.

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

Fixup Returned Information:

INFORMATION: –> OLAP Catalog(AMD) exists in database

Starting with Oracle Database 12c, OLAP Catalog component is desupported.

If you are not using the OLAP Catalog component and want

to remove it, then execute the

ORACLE_HOME/olap/admin/catnoamd.sql script before or

after the upgrade.

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

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

Check Tag: APEX_UPGRADE_MSG

Check Summary: Check that APEX will need to be upgraded.

Fix Summary: Oracle Application Express can be manually upgraded prior to data base upgrade.

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

Fixup Returned Information:

INFORMATION: –> Oracle Application Express (APEX) can be

manually upgraded prior to database upgrade

APEX is currently at version 3.2.1.00.12 and will need to be

upgraded to APEX version 4.2.5 in the new release.

Note 1: To reduce database upgrade time, APEX can be manually

upgraded outside of and prior to database upgrade.

Note 2: See MOS Note 1088970.1 for information on APEX

installation upgrades.

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

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

[Pre-Upgrade Recommendations]

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

********* Dictionary Statistics *********

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

Please gather dictionary statistics 24 hours prior to

upgrading the database.

To gather dictionary statistics execute the following command

while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^

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

************* Fixup Summary ************

4 fixup routines generated INFORMATIONAL messages that should be reviewed.

**************** Pre-Upgrade Fixup Script Complete *********************

Transportable Tablespace (PART 4)

7. Consequently, query is executed for checking all of upgrading process. [oracle@upgrade bin]$ sqlplus test/test SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 2 12:16:44 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select count(*) from t1; COUNT(*) ———- 86955

Transportable Tablespace (PART 3)

 

4. All of necessary datafiles are moved to Oracle Database 12c’s folder.

[oracle@upgrade orcl]$ cp /u01/app/oracle/oradata/orcl/data01.dbf /u01/app/oracle/oradata/IMC12C/

[oracle@upgrade orcl]$ cp /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/IMC12C/

5. Specific tablespaces are altered to READ WRITE mode on database and also 11g database is closed.

 

[oracle@upgrade orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 2 09:38:11 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER TABLESPACE data_ts READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE example READ WRITE;

Tablespace altered.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

6. Finally, data is imported to Oracle 12c database. Before import operation, environment variables should be set on Linux.

 

[oracle@upgrade bin]$ export ORACLE_SID=imc12c

[oracle@upgrade bin]$ ORAENV_ASK=NO

[oracle@upgrade bin]$ . oraenv

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@upgrade bin]$ ORAENV_ASK=YES

[oracle@upgrade bin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 3 03:43:02 2015

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE OR REPLACE DIRECTORY TEMP_DIR AS ‘/tmp/’;

[oracle@upgrade bin]$ impdp system full=Y directory=TEMP_DIR dumpfile=orcl.dmp logfile=impdporcl.log transport_datafiles= ‘/u01/app/oracle/oradata/IMC12C/data01.dbf’, ‘/u01/app/oracle/oradata/IMC12C/example01.dbf’

Import: Release 12.1.0.2.0 – Production on Mon Nov 2 11:46:26 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded

Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** full=Y directory=TEMP_DIR dumpfile=orcl.dmp logfile=impdporcl.log transport_datafiles=/u01/app/oracle/oradata/IMC12C/data01.dbf, /u01/app/oracle/oradata/IMC12C/example01.dbf

Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK

Processing object type DATABASE_EXPORT/TABLESPACE

ORA-31684: Object type TABLESPACE:”UNDOTBS1″ already exists

Job “SYSTEM”.”SYS_IMPORT_FULL_01″ completed with 587 error(s) at Mon Nov 2 12:10:40 2015 elapsed 0 00:24:09

Transportable Tablespace (PART 2)

The system takes to export from database.

[oracle@upgrade bin]$

[oracle@upgrade bin]$ expdp system full=Y transportable=always version=12 directory=TEMP_DIR \

> dumpfile=orcl.dmp logfile=expdporcl.log exclude=TABLESPACE:\”= \’USERS\’\” Export: Release 11.2.0.4.0 – Production on Mon Nov 2 09:20:31 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** full=Y transportable=always version=12 directory=TEMP_DIR dumpfile=orcl.dmp logfile=expdporcl.log exclude=TABLESPACE:”= ‘USERS'” Estimate in progress using BLOCKS method… Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 58.25 MB Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PROFILE

Transportable Tablespace (PART 1)

Before Upgrade steps , dbca is started …

[oracle@upgrade bin]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1/ [oracle@upgrade bin]$ PATH=$ORACLE_HOME/bin:$PATH [oracle@upgrade bin]$dbca

1. First of all, environment variables must set.

[oracle@upgrade bin]$ export ORACLE_SID=orcl

[oracle@upgrade bin]$ ORAENV_ASK=NO

[oracle@upgrade bin]$ . oraenv

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@upgrade bin]$ ORAENV_ASK=YES

2. After environment settings, connect database over command-line via sqlplus . As an initial step at database, directory object is created and respectively create user and tablespace and grant privileges to user. Finally, change status of tablespaces to read-only mode.

[oracle@upgrade bin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 2 09:18:53 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE OR REPLACE DIRECTORY TEMP_DIR AS ‘/tmp/’; Directory created.
SQL> CREATE TABLESPACE data_ts DATAFILE ‘/u01/app/oracle/oradata/orcl/data01.dbf’ SIZE 1M AUTOEXTEND ON NEXT 1M; 2 3 Tablespace created.

SQL> CREATE user test IDENTIFIED BY test DEFAULT TABLESPACE data_ts TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON data_ts; 2 3 4 User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO test; Grant succeeded.

SQL> CREATE TABLE test.t1 AS SELECT * FROM dba_objects; 2 Table created.

SQL> ALTER TABLESPACE data_ts READ ONLY; Tablespace altered.

SQL> ALTER TABLESPACE example READ ONLY; Tablespace altered.

SQL> SQL> EXIT

DBUA (PART 4)

 

DBUA (PART 3)