Restore and Upgrade Oracle Database from 11gR2 to 12c from Exadata X4-2 to X7-2

Upgrade from Exadata X4-2 11g (11.2.0.4) to Exadata X7-2 12CR1 (12.1.0.2.0) : ASM to ASM

Oracle Database 11.2.0.3and 11.2.0.4 to Oracle 12.2.0.1 Direct upgrade is supported by Oracle. If you have Database lower than those version than you have to upgrade to 11.2.0.4 first and you can upgrade to 12cR2

In our Scenario We will Migrate and Upgrade from One Exadata machine (X4-2) to another Exadata machine (X7-2) from 11.2.0.4 to 12.1.0.2.0

Source Exadata Setup

———————-

RAC 2-Nodes

Hardware : X4-2

Image version: 12.1.2.3.5.170418

DB NAME : mob

DATABASE HOME VERSION: 11.2.0.4

GRID HOME VERSION : 12.1.0.2.0

ASM Diskgroups : +DATAC1, +RECOC1

Target Exadata Setup

—————–

RAC 2-Nodes

Hardware : X7-2

Image version: 18.1.4.0.0.180125.3

DB NAME: SWX

DATABASE HOME VERSION : 12.1.0.2.0

GRID HOME VERSION : 12.2.0.1.0

ASM Diskgroups : +DATAC1, +RECOC1

 

Presumptions

————

* Oracle Home (12.1.0.2.0) is installed on the Target Server with latest patches

* Both Exadata Servers are reachable from the Network to Copy the backup files

1. Take RMAN Backup of Source Database on X4-2 hardware

rman target / nocatalog

run

{

allocate channel swx1 DEVICE TYPE DISK ;

allocate channel swx2 DEVICE TYPE DISK ;

allocate channel swx3 DEVICE TYPE DISK ;

allocate channel swx4 DEVICE TYPE DISK ;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/exabackups/rmanbkp/swx/autobackup_control_file_%F’;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

BACKUP DATABASE FORMAT ‘/exabackups/rmanbkp/swx/SWX_FULL_%s_%D_%M_%Y’ tag ‘SWX_DAILY_FULL_DB_BKP’;

BACKUP ARCHIVELOG ALL FORMAT ‘/exabackups/rmanbkp/swx/ARCH_%d_ALL_%s_%t’ tag ‘SWX_ARCH_LOG_BKP’;

}

SQL> create pfile=’/exabackups/rmanbkp/swx/swx_source.ora’ from spfile ;

2. Copy Backup from Source Server to Target Server

cd /exabackups/rmanbkp/swx

scp -r * oracle@qiibtstdbadm01.qiibonline.com:/u03/swx_today

3. Edit the Source pfile : swx_source.ora and change below parameters

Old pfile

———

swx1.__db_cache_size=9395240960

swx2.__db_cache_size=9395240960

swx1.__java_pool_size=33554432

swx2.__java_pool_size=33554432

swx1.__large_pool_size=67108864

swx2.__large_pool_size=67108864

swx2.__oracle_base=’/u02/app/oracle’#ORACLE_BASE set from environment

swx1.__oracle_base=’/u02/app/oracle’#ORACLE_BASE set from environment

swx1.__pga_aggregate_target=6006243328

swx2.__pga_aggregate_target=6006243328

swx1.__sga_target=11173625856

swx2.__sga_target=11173625856

swx1.__shared_io_pool_size=0

swx2.__shared_io_pool_size=0

swx1.__shared_pool_size=1577058304

swx2.__shared_pool_size=1577058304

swx1.__streams_pool_size=0

swx2.__streams_pool_size=0

*._client_enable_auto_unregister=TRUE

*.archive_lag_target=0

*.audit_file_dest=’/u02/app/oracle/admin/swx/adump’ ####### Change to Target Directory and Create teh Directory on OS ###########

*.audit_trail=’db’

*.cluster_database=TRUE ######## Set to FALSE ############

*.compatible=’11.2.0.4.0′ ######## Change to 12.1.0.2.0 ##########

*.control_files=’+datac1/swx/controlfile/current.346.853755085′,’+RECOC1/swx/controlfile/current.4208.853579009′ ########## Hash this #########

*.cpu_count=2

*.db_block_size=8192

*.db_create_file_dest=’+DATAC1′

*.db_create_online_log_dest_1=’+RECOC1′

*.db_domain=”

*.db_name=’swx’

*.db_recovery_file_dest=’+RECOC1′

*.db_recovery_file_dest_size=858993459200

*.diagnostic_dest=’/u02/app/oracle’ ########## Change to new target Oracle Base directory ############

*.dispatchers='(PROTOCOL=TCP) (SERVICE=swxXDB)’

swx2.instance_number=2

swx1.instance_number=1

*.java_jit_enabled=TRUE

*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SWX’

*.log_archive_format=’%t_%s_%r.arc’

swx1.log_archive_format=’%t_%s_%r.arc’

swx2.log_archive_format=’%t_%s_%r.arc’

*.log_archive_max_processes=8

*.log_archive_min_succeed_dest=1

swx1.log_archive_trace=0

swx2.log_archive_trace=0

*.sga_max_size=17179869184

*.sga_target=17179869184

*.open_cursors=300

*.processes=1500

*.remote_listener=’exaprod-scan:1521′ ########## Change to SCAN listener of Target DB #########

*.remote_login_passwordfile=’exclusive’

*.resource_manager_cpu_allocation=48

*.resource_manager_plan=’DEFAULT_PLAN’

*.sessions=1655

*.standby_file_management=’AUTO’

swx2.thread=2

swx1.thread=1

swx1.undo_tablespace=’UNDOTBS1′

swx2.undo_tablespace=’UNDOTBS2′

*.instance_number=2

Save the parameters and save pfile as swx_target.ora

New pfile : swx_target.ora

———

swx1.__db_cache_size=9395240960

swx2.__db_cache_size=9395240960

swx1.__java_pool_size=33554432

swx2.__java_pool_size=33554432

swx1.__large_pool_size=67108864

swx2.__large_pool_size=67108864

swx2.__oracle_base=’/u02/app/oracle’#ORACLE_BASE set from environment

swx1.__oracle_base=’/u02/app/oracle’#ORACLE_BASE set from environment

swx1.__pga_aggregate_target=6006243328

swx2.__pga_aggregate_target=6006243328

swx1.__sga_target=11173625856

swx2.__sga_target=11173625856

swx1.__shared_io_pool_size=0

swx2.__shared_io_pool_size=0

swx1.__shared_pool_size=1577058304

swx2.__shared_pool_size=1577058304

swx1.__streams_pool_size=0

swx2.__streams_pool_size=0

*._client_enable_auto_unregister=TRUE

*.archive_lag_target=0

*.audit_file_dest=’/u03/app/oracle/admin/swx/adump’ ####### Change to Target Directory and Create teh Directory on OS ###########

*.audit_trail=’db’

*.cluster_database=FALSE ######## Set to FALSE ############

*.compatible=’12.1.0.2.0′ ######## Change to 12.1.0.2.0 ##########

#*.control_files=’+datac1/swx/controlfile/current.346.853755085′,’+RECOC1/swx/controlfile/current.4208.853579009′ ########## Hash this #########

*.cpu_count=2

*.db_block_size=8192

*.db_create_file_dest=’+DATAC1′

*.db_create_online_log_dest_1=’+RECOC1′

*.db_domain=”

*.db_name=’swx’

*.db_recovery_file_dest=’+RECOC1′

*.db_recovery_file_dest_size=858993459200

*.diagnostic_dest=’/u03/app/oracle’ ########## Change to new target Oracle Base directory ############

*.dispatchers='(PROTOCOL=TCP) (SERVICE=swxXDB)’

swx2.instance_number=2

swx1.instance_number=1

*.java_jit_enabled=TRUE

*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SWX’

*.log_archive_format=’%t_%s_%r.arc’

swx1.log_archive_format=’%t_%s_%r.arc’

swx2.log_archive_format=’%t_%s_%r.arc’

*.log_archive_max_processes=8

*.log_archive_min_succeed_dest=1

swx1.log_archive_trace=0

swx2.log_archive_trace=0

*.sga_max_size=17179869184

*.sga_target=17179869184

*.open_cursors=300

*.processes=1500

*.remote_listener=’qiibtst-scan:1521′ ########## Change to SCAN listener of Target DB #########

*.remote_login_passwordfile=’exclusive’

*.resource_manager_cpu_allocation=48

*.resource_manager_plan=’DEFAULT_PLAN’

*.sessions=1655

*.standby_file_management=’AUTO’

swx2.thread=2

swx1.thread=1

swx1.undo_tablespace=’UNDOTBS1′

swx2.undo_tablespace=’UNDOTBS2′

*.instance_number=2

4. Start the Instance on Target Database using swx_target.ora pfile

Make sure you export ORACLE_HOME and ORACLE_SID environment variables properly before beginning

SQL> startup nomount pfile=’/u03/swx_today/swx_source.ora’;

rman target /

RMAN> restore controlfile from ‘/u03/swx_today/control_file_SWX_853579011_20180215_4985_1’;

Starting restore at 18-MAR-18

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1140 instance=swx device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

output file name=+RECOC1/SWX/CONTROLFILE/current.654.971100501

Finished restore at 18-MAR-18

RMAN> alter database mount;

RMAN> catalog start with ‘/u03/swx_today’;

Starting implicit crosscheck backup at 18-MAR-18

allocated channel: ORA_DISK_1

allocated channel: ORA_DISK_2

allocated channel: ORA_DISK_3

allocated channel: ORA_DISK_4

Crosschecked 158 objects

Finished implicit crosscheck backup at 18-MAR-18

Starting implicit crosscheck copy at 18-MAR-18

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

Crosschecked 4 objects

Finished implicit crosscheck copy at 18-MAR-18

searching for all files in the recovery area

cataloging files…

no files cataloged

searching for all files that match the pattern /u03/swx_today

List of Files Unknown to the Database

=====================================

File Name: /u03/swx_today/ARCH_SWX_ALL_4979_968178229

File Name: /u03/swx_today/control_file_SWX_853579011_20180215_4985_1

File Name: /u03/swx_today/SWX_FULL_4975_15_02_2018

File Name: /u03/swx_today/ARCH_SWX_ALL_4981_968178230

File Name: /u03/swx_today/ARCH_SWX_ALL_4978_968178229

File Name: /u03/swx_today/ARCH_SWX_ALL_4982_968178255

File Name: /u03/swx_today/autobackup_control_file_c-133836929-20180215-02

File Name: /u03/swx_today/autobackup_control_file_c-133836929-20180215-00

File Name: /u03/swx_today/SWX_FULL_4974_15_02_2018

File Name: /u03/swx_today/autobackup_control_file_c-133836929-20180215-01

File Name: /u03/swx_today/swx_source.ora

File Name: /u03/swx_today/SWX_FULL_4976_15_02_2018

File Name: /u03/swx_today/ARCH_SWX_ALL_4980_968178230

File Name: /u03/swx_today/ARCH_SWX_ALL_4983_968178258

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files…

cataloging done

List of Cataloged Files

=======================

File Name: /u03/swx_today/ARCH_SWX_ALL_4979_968178229

File Name: /u03/swx_today/control_file_SWX_853579011_20180215_4985_1

File Name: /u03/swx_today/SWX_FULL_4975_15_02_2018

File Name: /u03/swx_today/ARCH_SWX_ALL_4981_968178230

File Name: /u03/swx_today/ARCH_SWX_ALL_4978_968178229

File Name: /u03/swx_today/ARCH_SWX_ALL_4982_968178255

File Name: /u03/swx_today/autobackup_control_file_c-133836929-20180215-02

File Name: /u03/swx_today/autobackup_control_file_c-133836929-20180215-00

File Name: /u03/swx_today/SWX_FULL_4974_15_02_2018

File Name: /u03/swx_today/autobackup_control_file_c-133836929-20180215-01

File Name: /u03/swx_today/SWX_FULL_4976_15_02_2018

File Name: /u03/swx_today/ARCH_SWX_ALL_4980_968178230

File Name: /u03/swx_today/ARCH_SWX_ALL_4983_968178258

List of Files Which Were Not Cataloged

=======================================

File Name: /u03/swx_today/swx_source.ora

RMAN-07517: Reason: The file header is corrupted

RMAN> restore database;

Starting restore at 18-MAR-18

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=26 instance=swx device type=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 7.1.0.0

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00003 to +DATAC1/swx/datafile/undotbs1.281.853578943

channel ORA_DISK_1: restoring datafile 00004 to +RECOC1/swx/datafile/audit_tbs.2213.871200459

channel ORA_DISK_1: reading from backup piece /u03/swx_today/SWX_FULL_4975_15_02_2018

channel ORA_DISK_2: starting datafile backup set restore

channel ORA_DISK_2: specifying datafile(s) to restore from backup set

channel ORA_DISK_2: restoring datafile 00001 to +DATAC1/swx/datafile/system.285.853578943

channel ORA_DISK_2: restoring datafile 00002 to +DATAC1/swx/datafile/sysaux.278.853578943

channel ORA_DISK_2: reading from backup piece /u03/swx_today/SWX_FULL_4976_15_02_2018

channel ORA_DISK_3: starting datafile backup set restore

channel ORA_DISK_3: specifying datafile(s) to restore from backup set

channel ORA_DISK_3: restoring datafile 00005 to +DATAC1/swx/datafile/undotbs2.351.853579027

channel ORA_DISK_3: restoring datafile 00006 to +DATAC1/swx/datafile/swx.345.853583217

channel ORA_DISK_3: reading from backup piece /u03/swx_today/SWX_FULL_4974_15_02_2018

channel ORA_DISK_1: piece handle=/u03/swx_today/SWX_FULL_4975_15_02_2018 tag=SWX_DAILY_FULL_DB_BKP

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

channel ORA_DISK_2: piece handle=/u03/swx_today/SWX_FULL_4976_15_02_2018 tag=SWX_DAILY_FULL_DB_BKP

channel ORA_DISK_2: restored backup piece 1

channel ORA_DISK_2: restore complete, elapsed time: 00:00:15

channel ORA_DISK_3: piece handle=/u03/swx_today/SWX_FULL_4974_15_02_2018 tag=SWX_DAILY_FULL_DB_BKP

channel ORA_DISK_3: restored backup piece 1

channel ORA_DISK_3: restore complete, elapsed time: 00:04:45

Finished restore at 18-MAR-18

RMAN> recover database;

Starting recover at 18-MAR-18

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_SBT_TAPE_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=2 sequence=48891

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=43454

channel ORA_DISK_1: restoring archived log

archived log thread=2 sequence=48892

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=43455

channel ORA_DISK_1: reading from backup piece /u03/swx_today/ARCH_SWX_ALL_4983_968178258

channel ORA_DISK_1: piece handle=/u03/swx_today/ARCH_SWX_ALL_4983_968178258 tag=SWX_ARCH_LOG_BKP

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_1_seq_43454.645.971100923 thread=1 sequence=43454

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_2_seq_48891.642.971100923 thread=2 sequence=48891

channel default: deleting archived log(s)

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_2_seq_48891.642.971100923 RECID=238985 STAMP=971100923

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_2_seq_48892.639.971100923 thread=2 sequence=48892

channel default: deleting archived log(s)

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_1_seq_43454.645.971100923 RECID=238987 STAMP=971100923

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_1_seq_43455.648.971100923 thread=1 sequence=43455

channel default: deleting archived log(s)

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_1_seq_43455.648.971100923 RECID=238988 STAMP=971100923

unable to find archived log

archived log thread=1 sequence=43456

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 03/18/2018 14:15:25

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 43456 and starting SCN of 21582106266

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00601: fatal error in recovery manager

RMAN-03004: fatal error during execution of command

ORA-03114: not connected to ORACLE

RMAN-03002: failure of sql statement command at 03/18/2018 14:15:42

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 2

ORA-00904: “I”.”UNUSABLEBEGINNING#”: invalid identifier

Process ID: 136136

Session ID: 1137 Serial number: 14577

sqlplus “/as sysdba”

Change control file information with new location after restore in pfile and start database again +RECOC1/SWX/CONTROLFILE/current.654.971100501

Also change CLUSTER_DATABASE to false in pfile

If you get any error like below error then copy the file to the dbs folder as init.ora

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file ‘/u03/app/oracle/product/12.1.0.2/dbhome_1/dbs/initswx.ora’

$ cp -p /u03/swx_today/swx_source.ora /u03/app/oracle/product/12.1.0.2/dbhome_1/dbs/initswx.ora

sqlplus “/as sysdba”

SQL> startup upgrade;

$ cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql

Argument list for [catctl.pl]

SQL Process Count n = 4

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 = /u03/app/oracle

Analyzing file catupgrd.sql

Log files in /u03/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin

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

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

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

Number of Cpus = 2

SQL Process Count = 4

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

Phases [0-73] Start Time:[2018_03_18 14:39:13]

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

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

Serial Phase #: 1 Files: 5 Time: 22s

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

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

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

Serial Phase #: 5 Files: 5 Time: 10s

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

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

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

Parallel Phase #: 9 Files: 62 Time: 12s

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

Serial Phase #:11 Files: 1 Time: 8s

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

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

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

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

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

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

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

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

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

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

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

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

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

Parallel Phase #:25 Files: 11 Time: 17s

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: 12s

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Serial Phase #:65 Files: 1 Time: 11s

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

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

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

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

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

Phases [0-73] End Time:[2018_03_18 14:58:16]

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

Grand Total Time: 1145s

*** WARNING: ERRORS FOUND DURING UPGRADE ***

Due to errors found during the upgrade process, the post

upgrade actions in catuppst.sql have not been automatically run.

*** THEREFORE THE DATABASE UPGRADE IS NOT YET COMPLETE ***

1. Evaluate the errors found in the upgrade logs

and determine the proper action.

2. Execute the post upgrade script as described in Chapter 3

of the Database Upgrade Guide.

REASON:

catuppst.sql unable to run in Database: swx Id: 0

ERRORS FOUND: during upgrade CATCTL ERROR COUNT=5

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

Identifier XDB 18-03-18 02:47:25 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [ORA-01917: user or role ‘ANONYMOUS’ does not exist ORA-06512: at line 7

ORA-06512: at line 15

]

STATEMENT = [declare

already_revoked exception;

pragma exception_init(already_revoked,-01927);

procedure revoke_inherit_privileges(user in varchar2) as

begin

execute immediate ‘revoke inherit privileges on user ‘||

dbms_assert.enquote_name(user)||’ from public’;

exception

when already_revoked then null;

end;

begin

revoke_inherit_privileges(‘xdb’);

revoke_inherit_privileges(‘anonymous’);

end;]

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

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

Identifier XDB 18-03-18 02:47:25 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [ORA-06512: at line 7 ORA-06512: at line 15

]

STATEMENT = [as above]

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

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

Identifier XDB 18-03-18 02:47:25 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [ORA-06512: at line 15]

STATEMENT = [as above]

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

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

Identifier ORDIM 18-03-18 02:50:44 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [ORA-20000: Oracle XML Database component not valid. Oracle XML Database must be installed and valid prior to Oracle Multimedia install, upgrade, downgrade, or patch.

ORA-06512: at line 3

]

STATEMENT = [begin

IF dbms_registry.is_valid(‘XDB’,dbms_registry.release_version) != 1 THEN

raise_application_error(-20000,

‘Oracle XML Database component not valid. ‘||

‘Oracle XML Database must be installed and valid prior to Oracle Multimedia install, upgrade, downgrade, or patch.’);

END IF;

end;]

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

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

Identifier ORDIM 18-03-18 02:50:44 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [installed and valid prior to Oracle Multimedia install, upgrade, downgrade, or patch.

ORA-06512: at line 3

]

STATEMENT = [as above]

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

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

Identifier ORDIM 18-03-18 02:50:44 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [patch. ORA-06512: at line 3

]

STATEMENT = [as above]

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

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

Identifier ORDIM 18-03-18 02:50:44 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [ORA-06512: at line 3]

STATEMENT = [as above]

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

LOG FILES: (catupgrd*.log)

Upgrade Summary Report Located in:

/u03/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/swx/upgrade/upg_summary.log

Grand Total Upgrade Time: [0d:0h:19m:5s]

sqlplus “/as sysdba”

SQL> @utlrp.sql

SQL> select * from dba_registry;

Verify all components are valid

OWB OWB 11.2.0.4.0 VALID 18-MAR-2018 15:02:31 SERVER SYS OWBSYS DBMS_OWB.VALIDATE

EM Oracle Enterprise Manager 11.2.0.4.0 VALID 24-AUG-2013 11:58:27 SERVER SYS SYSMAN

AMD OLAP Catalog 11.2.0.4.0 OPTION OFF 18-MAR-2018 14:46:55 SERVER SYS OLAPSYS CWM2_OLAP_INSTALLER.VALIDATE_CWM2_INSTALL

SDO Spatial 12.1.0.2.0 VALID 18-MAR-2018 15:02:30 SERVER SYS MDSYS VALIDATE_SDO MDDATA,MDSYS,SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN_USR

ORDIM Oracle Multimedia 12.1.0.2.0 VALID 18-MAR-2018 15:02:29 SERVER SYS ORDSYS VALIDATE_ORDIM ORDDATA,ORDPLUGINS,SI_INFORMTN_SCHEMA

XDB Oracle XML Database 12.1.0.2.0 VALID 18-MAR-2018 15:02:28 SERVER SYS XDB DBMS_REGXDB.VALIDATEXDB XS$NULL

CONTEXT Oracle Text 12.1.0.2.0 VALID 18-MAR-2018 15:02:27 SERVER SYS CTXSYS VALIDATE_CONTEXT

OWM Oracle Workspace Manager 12.1.0.2.0 VALID 18-MAR-2018 15:02:25 SERVER SYS WMSYS VALIDATE_OWM

CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID 18-MAR-2018 15:02:23 SERVER SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG

CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID 18-MAR-2018 15:02:23 SERVER SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC APPQOSSYS,DBSNMP,DIP,GSMADMIN_INTERNAL,ORACLE_OCM,OUTLN,SYSTEM

JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 VALID 18-MAR-2018 15:02:26 SERVER SYS SYS INITJVMAUX.VALIDATE_JAVAVM

COMP_ID COMP_NAME VERSION STATUS MODIFIED NAMESPACE CONTROL SCHEMA PROCEDURE STARTUP PARENT_ID OTHER_SCHEMAS

—————————— ————————————————————————————————————————————————————————————————————————————————————— —————————— ———– ——————– —————————— ——————————————————————————————————————————– ——————————————————————————————————————————– ————————————————————- ——– —————————— —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-

XML Oracle XDK 12.1.0.2.0 VALID 18-MAR-2018 15:02:27 SERVER SYS SYS XMLVALIDATE

CATJAVA Oracle Database Java Packages 12.1.0.2.0 VALID 18-MAR-2018 15:02:27 SERVER SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATJAVA

APS OLAP Analytic Workspace 12.1.0.2.0 VALID 18-MAR-2018 15:02:29 SERVER SYS SYS APS_VALIDATE

XOQ Oracle OLAP API 12.1.0.2.0 VALID 18-MAR-2018 15:02:30 SERVER SYS SYS XOQ_VALIDATE

RAC Oracle Real Application Clusters 12.1.0.2.0 VALID 18-MAR-2018 15:02:31 SERVER SYS SYS DBMS_CLUSTDB.VALIDATE

16 rows selected.

SQL>

ORA-00600: internal error code, arguments: [audins:Invalid_param_type_for_binding

Error in Alert Log:

Errors in file /u03/app/oracle/diag/rdbms/primeprd/primeprd1/trace/primeprd1_ora_303370.trc (incident=1070393):

ORA-00600: internal error code, arguments: [audins:Invalid_param_type_for_binding], [], [], [], [], [], [], [], [], [], [], []

 

Error in Broker :

DGMGRL> add database primedr as connect identifier is primedr maintained as physical;
Database “primedr” added

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration

Configuration – dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd – Primary database
primedr – Physical standby database
Error: ORA-16664: unable to receive the result from a database

DGM-17016: failed to retrieve status for database “primedr”
ORA-16664: unable to receive the result from a database

Broker logfile error:

11/22/2017 16:42:26
ENABLE CONFIGURATION
11/22/2017 16:42:55
Site primedr returned ORA-16664.

 

Description:

We had this error when we were adding a Standby database to the Dataguard Broker. After enabling the configuration always it used to give error ORA-16664: unable to receive the result from a database. After literally months of digging around and opening multiple SR’s we discovered it was related to AUDIT logs which are written in AUD$. It was clear after searching the trace logfiles that an application context package has some issues with the Audit in Oracle 12c.

Error Showing Up in TraceFile

DDE: Problem Key ‘ORA 600 [audins:Invalid_param_type_for_binding]’ was flood controlled (0x2) (incident: 1070379)
ORA-00600: internal error code, arguments: [audins:Invalid_param_type_for_binding], [], [], [], [], [], [], [], [], [], [], []
*********START PLSQL RUNTIME DUMP************
***Got internal error Exception caught in pl/sql run-time while running PLSQL***
***Got ORA-2002 while running PLSQL***
PACKAGE BODY TCTDBS.SV_DB_CONTEXT:
library unit=677604e58 line=225 opcode=86 static link=7fb08c2b4888 scope=1
FP=0x7fb08c2b4d10 PC=0x9e7cdec82 Page=0 AP=0x7fb08c2b4888 ST=0x7fb08c2b5158
DL0=0x7fb0872edd50 GF=0x7fb0872edf60 DL1=0x7fb0872ede48 DPF=0x7fb0872edf48
HS=0x9e7cd8ae8 AR=0x7fb0872e9fc8 DS=0x9e7ce0148
PB_PC=(nil) SV_PC=(nil)

Solution:

The solution is simply to turn off the auditing for the objects which is giving error. Since we are already running an auditing solution;  native auditing of Oracle was not required in this scenario.

— Dynamic SQL to generate and execute the no audit statements —

DECLARE
l_sql_stmt varchar2(1000);
BEGIN
FOR t IN (SELECT owner, table_name
FROM all_tables
WHERE owner = ‘TCTDBS’
and
iot_type IS NULL
or
iot_type != ‘IOT_OVERFLOW’
)
LOOP
l_sql_stmt := ‘NOAUDIT ALL ON ‘ || t.owner || ‘.’ || t.table_name;
EXECUTE IMMEDIATE l_sql_stmt;
END LOOP;
END;

Once this was done we added the Standby Database to the Dataguard Broker and Enabled the configuration without any issues.

 

 

Rebuild All Indexes on a Schema in Oracle

spool index_rebuild.sql

select ‘alter index ‘||owner||’.’||index_name ||’ rebuild online nologging;’
from dba_indexes
where owner=upper(‘SAMPLE’);
spool off

Move Table to Another Tablespace Online Oracle Database 12c

The new PL/SQL procedure REDEF_TABLE in Oracle 12c  lets you redefine a table online in a single step. It can do the below changes

  • Move table,partition or index to another tablespace
  • Compression type change on a table including for table,partition and index

More information on DBMS_REDFINITION can be accessed on Oracle Support : Master Note:Overview of Online Redefinition of Tables (DBMS_REDEFINITION) (Doc ID 1357825.1)

We will now go ahead and move the table HR.EMP to another tablespace called TBS01 online.

EXEC
BEGIN
DBMS_REDEFINITION.REDEF_TABLE(
uname => ‘HR’,
tname => ‘EMP’,
table_part_tablespace => ‘TBS01’
);
END;
/

uname –> Schema Name in which Table resides
tname –> Table Name
table_part_tablespace –> New Tablespace to which table will be moved
index_tablespace => If you need to move the indexes to another table it can be done using an additional arguement

Restore Table from RMAN Backup Oracle 12c

 

One of the amazing features of Oracle Database 12c is that now you can do a logical restore from a physical backup. Before 12c it was not possible to restore a table from a RMAN backup. This feature is amazing speciall when you have a large database and needs to restore a small table. One of the pre-requisites is you must have a Full Backup of the database and also archivelogs after the fully backup must be available. We can proceed to do the restore with below RMAN command

$ rman target /

RECOVER TABLE HR.EMP
UNTIL TIME ‘SYSDATE-0.22’
AUXILIARY DESTINATION ‘/backups/rmanbkp’
REMAP TABLE ‘HR’.’EMP’:’EMP_RECVR’

HR.EMP is the table to be recovered from the RMAN backup

SYSDATE-0.22 is the time till where the table will be rolled back; this is to specify the point in time for recovery: UNTIL TIME, UNTIL SCN, or UNTIL SEQUENCE can be used

Auxiliary Destination is destination where a instance is created for the recovery process

REMAP TABLE clause is used to specify to restore the table with another name

Using this method even individual table paritions can be restored.

EXPDP/IMPDP – ORA-04063 (view “SYS.KU$_RADM_FPTM_VIEW” Has Errors) on Full Export / Import

This is caused by a mismatch between the character set in which the table radm_fptm$ is created and in which the type ku$_radm_fptm$_t is created. This leads to the ORA-932 error when trying to recompile the view SYS.KU$_RADM_FPTM_VIEW, you have specified UTF8 for the national characaterset instead of the default AL16UTF16 national characaterset:

To Check Character set

col parameter for a35
col value for a40
select * from nls_database_parameters where parameter like ‘%SET’ order by 1;

 

Workaround is  to recompile the Recompile the View ku$_radm_fptm_t

alter type ku$_radm_fptm_t compile reuse settings;

Type altered.

SQL> alter view SYS.KU$_RADM_FPTM_VIEW compile;

View altered.

set lines 140
col status for a9
col object_type for a20;
col owner.object for a50
select con_id, status, object_id, object_type,
owner||’.’||object_name “OWNER.OBJECT”
3 from cdb_objects where object_name like ‘%KU$_RADM_FPTM_VIEW%’ order by 4,1;

 

CON_ID STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
———- ——— ———- ——————– ————————————————–
0 VALID 14188 VIEW SYS.KU$_RADM_FPTM_VIEW

 

 

 

Oracle 12c Transparent Data Encryption (TDE) on RAC Database with Physical Standby Database : Multiple Databases in Single Oracle Home

Oracle 12c TDE on RAC Database with Physical Standby RAC. The Oracle Database Home has Multiple Instances, due to which we have to use parameter $ORACLE_UNQNAME for ENCRYPTION_WALLET_LOCATION in sqlnet.ora file

In Oracle 12c ALTER SYSTEM and orapki has been deprecated and the correct method is to use ADMINISTER KEY MANAGEMENT commands. Many of the statements from the ALTER SYSTEM SQL statement correspond to the ADMINISTER KEY MANAGEMENT statement.

1. Create folder for wallets, since this is RAC cluster database ,it is better to have a shared filesystem. In our case it is ACFS
mkdir -p /ACFS/wallets/primeprd
mkdir -p /ACFS/wallets/onlnprd
chown -R oracle:oinstall /ACFS/wallets/primeprd
chown -R oracle:oinstall /ACFS/wallets/onlnprd
2. Create sqlnet.ora file with encryption wallet location, do on both nodes with oracle user
ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE) (METHOD_DATA =
(DIRECTORY=/ACFS/wallets/$ORACLE_UNQNAME/)))
3. Set DB Unique Name for the databases and verfiy the encryption wallet location, do on both nodes with oracle user

Also it is better to set the ORACLE_UNQNAME in the user profile

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d primeprd -T “ORACLE_UNQNAME=primeprd”
select * from v$encryption_wallet;

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d onlnprd -T “ORACLE_UNQNAME=onlnprd”
select * from v$encryption_wallet;

4. At Dr side, with Oracle user in DB home directory
mkdir -p /ACFS/wallets/primedr
mkdir -p /ACFS/wallets/onlndr
chown -R oracle:oinstall /ACFS/wallets/onlndr
chown -R oracle:oinstall /ACFS/wallets/primedr

Create sqlnet.ora file in DR side with encryption wallet location, do on both nodes with oracle user

ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE) (METHOD_DATA =
(DIRECTORY=/ACFS/wallets/$ORACLE_UNQNAME/)))

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d onlndr -T “ORACLE_UNQNAME=onlndr”

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d primedr -T “ORACLE_UNQNAME=primedr”

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management create keystore ‘/ACFS/wallet/primeprd/’ identified by “Abc1234$#”;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
CLOSED UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management set keystore open identified by Abc1234$#;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED
0
SQL> administer key management create key identified by Abc1234$# with backup;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED
0
SQL> select key_id from v$encryption_keys;

KEY_ID
——————————————————————————
AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> administer key management use key ‘AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’ identified by Abc1234$# with backup;

keystore altered.

SQL> select key_id,activation_time from v$encryption_keys;

KEY_ID
——————————————————————————
ACTIVATION_TIME
—————————————————————————
AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
23-NOV-16 09.53.46.485930 AM +00:00
SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN PASSWORD SINGLE NO
0

primeprd1::12c–>srvctl stop database -d primeprd
primeprd1::12c–>srvctl start database -d primeprd
primeprd1::12c–>sqld

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
CLOSED UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management set keystore open identified by Abc1234$#;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN PASSWORD SINGLE NO
0
— Create Auto Login for the Keystore —

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE ‘/ACFS/wallet/primeprd/’ identified by Abc1234$#;

keystore altered.

primeprd1::12c–>srvctl stop database -d primeprd
primeprd1::12c–>srvctl start database -d primeprd
primeprd1::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 23 13:13:30 2016

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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN AUTOLOGIN SINGLE NO
0
Copy the Encryption key Files to Standby Site as “Oracle” user

primeprd2::12c–>cd /ACFS/wallet/primeprd/
primeprd2::12c–>scp -r * oracle@exadrdbadm01:/ACFS/wallets/primedr

The authenticity of host ‘exadrdbadm01 (10.5.238.11)’ can’t be established.
RSA key fingerprint is c8:c0:69:80:1c:c8:ea:e3:6c:46:6b:c1:05:86:aa:c9.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘exadrdbadm01,10.5.238.11’ (RSA) to the list of known hosts.
oracle@exadrdbadm01’s password:
cwallet.sso 100% 3893 3.8KB/s 00:00
ewallet_2016112309525312.p12 100% 2408 2.4KB/s 00:00
ewallet_2016112309534645.p12 100% 3656 3.6KB/s 00:00
ewallet.p12 100% 3848 3.8KB/s 00:00

— At DR Side on Both Nodes–
SQL> select * from v$encryption_wallet;
WRL_TYPE
——————–
WRL_PARAMETER
—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
—————————— ——————– ——— ——— ———-
FILE
/u03/app/oracle/admin/primedr/wallet
NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED
/home/oracle::12c–>cd /u03/app/oracle/product/12.1.0.2/teststandby/network/admin/
/u03/app/oracle/product/12.1.0.2/teststandby/network/admin::12c–>vi sqlnet.ora
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /ACFS/wallets/primedr/$ORACLE_UNQNAME/)))

/u03/app/oracle/product/12.1.0.2/teststandby/network/admin::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 23 13:27:22 2016

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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallets/primedr/
OPEN AUTOLOGIN SINGLE NO
0
SQL> exit

 

5. Create Encrypted Tablespace
CREATE TABLESPACE encrypt_ts
DATAFILE ‘+DATAC1’ SIZE 250M
ENCRYPTION USING ‘AES256’
DEFAULT STORAGE (ENCRYPT);

SYSAUX tablespace growing rapidly in Oracle, 12c Unified Audit Trail AUDSYS

The Unified Auditing in Oracle 12c can Grow the SYSAUX table to a large size. In our case it grew to about 100GB and We had to take action to make sure it did not grow any larger. First we query to identify what is residing inside the SYSAUX tablespace. In our case it was the AUDSYS schema which was taking the most space. All arrows pointed to Unified Auditing’s way

— Query to Check the SYSAUX Tablespace Occupants —

COLUMN “Item” FORMAT A25
COLUMN “Space Used (GB)” FORMAT 999.99
COLUMN “Schema” FORMAT A25
COLUMN “Move Procedure” FORMAT A40

SELECT occupant_name “Item”,
space_usage_kbytes/1048576 “Space Used (GB)”,
schema_name “Schema”,
move_procedure “Move Procedure”
FROM v$sysaux_occupants
ORDER BY 2
/
— Query to Check the SYSAUX Tablespace Largest Segments–

col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||’~’||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = ‘SYSAUX’ ORDER BY BLOCKS desc) where rownum < 11;
— Clean Up the Unified Audit Trail —

1. First set the Date, starting from where you need to have the Unified Auditing Records

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
last_archive_time => TO_TIMESTAMP(’15-SEP-2016 00:00:10.0′,’DD-MON-RRRR HH24:MI:SS.FF’),
rac_instance_number => 2);
END;
/
2. Since Unified Auditing caches the audit trail in memory to implement a ‘lazy write’ feature that helps performance, some of the records eligible for deletion may still linger in the cache, to also first flush this cache

BEGIN
DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
END;
/

3. Execute the Purge Procedure

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE);
END;
/

Useful Links:

https://docs.oracle.com/database/121/DBSEG/audit_admin.htm#DBSEG474

How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)

 

Monitor Tablespace Script Oracle 12c 11g 10g

The Below Script will generate output if percentage of tablespace space left free is less tan 20%. You can easily put in a shell script and schedule it on crontab to send alerts on a daily basis.

 

select df.tablespace_name tspace,
round(sum(fs.bytes)/(df.bytes) * 100) “%_free”,
round(sum(fs.bytes)/(1024*1024)) free_ts_size,
df.bytes/(1024*1024) tot_ts_size
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes
having round(sum(fs.bytes)/(df.bytes) * 100) < 20;