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-00245 control file backup failed target is likely on a local file system

Solution:

This happens when you havea RAC setup and you are backing up the Snapshot of the controlfile to a local disk instead of a shared disk.

In our setup we setup an ACFS file system on both nodes of RAC and changed RMAN parameter as below

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/ACFS/snapcf_mw1.f’;

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/ACFS/snapcf_mw1.f’;
new RMAN configuration parameters are successfully stored

RMAN database restore until time

I had a block corruption of my primary database, though we had a standby build we wanted to recover the Primary instead of failover to the Standby.
This recovery is possible when you have say a full backup made of the database till 9th May and all archivelogs from 9th May till 12th MAY are present.

Put the database in mount mode and restore it till the time before the corruption happened.

run {
set until time to_date(’12-May-2012 00:00:00′, ‘DD-MON-YYYY HH24:MI:SS’);
restore database;
recover database;
}

 

A Ballad For a DBA

Dedicated to Gwen Shapira

It’s a tale of woe and malice, a tale of a DBA
That the users learned to fear and to get out of his way
He would drop production tables when complaints had grown to loud
He would smile and feign surprise and then suggested “use the cloud”

He would backup every hour but would not deign to restore
“If it’s gone, just be more careful, it’s just me the backup’s for”
He would kill off all the queries that had gone on way too long
When the users would complain he would just say that they are wrong

When the database expanded
And the system was too slow
He just shrugged, “go talk to network –
Go tell them your tales of woe”

When the backups all are running and the database is gone
He won’t care about the users, just let an evil yawn
When the disks will come down crashing, if it’s way too late at night
He’ll just sets his cell to “silent”, waits until the morning light

When the database expanded
And the network was too slow
He just shrugged, “go talk to network –
Go tell them your tales of woe”

When the database expanded
I/O rates became too high
He just shrugged, “go talk to storage –
Or just go crawl off and die”

And the coders told him – “Buddy,
All your indices are wrong”
So he told them, “It’s your queries,
and your fault they take too long”

I've lost an archived/online REDO LOG file, can I get my DB back?

The following INIT.ORA/SPFILE parameter can be used if your current redologs are corrupted or blown away. It may also be handy if you do database recovery and one of the archived log files are missing and cannot be restored.

NOTE: Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it.

_allow_resetlogs_corruption = true

This should allow you to open the database. However, after using this parameter your database will be inconsistent (some committed transactions may be lost or partially applied).

Steps:

  • Do a “SHUTDOWN NORMAL” of the database
  • Set the above parameter
  • Do a “STARTUP MOUNT” and “ALTER DATABASE OPEN RESETLOGS;”
  • If the database asks for recovery, use an UNTIL CANCEL type recovery and apply all available archive and on-line redo logs, then issue CANCEL and reissue the “ALTER DATABASE OPEN RESETLOGS;” command.
  • Wait a couple of minutes for Oracle to sort itself out
  • Do a “SHUTDOWN NORMAL”
  • Remove the above parameter!
  • Do a database “STARTUP” and check your ALERT.LOG file for errors.

Extract the data and rebuild the entire database.

ORA-38701: Flashback database log seq thread

The database does not startup, and gives errors like:
ORA-38737: Expected sequence number 2483 doesn’t match 2304
ORA-38701: Flashback database log 143 seq 2483 thread 1:

These errors can be seen in the Alert.log file. These errors occur and prevent the database from starting up because, at some point prior, we were not able to write to the flashback area.

Review the alert.log file prior to these errors on startup. You may see other information indicating an issue writing to the flashback area. For example, you may see something like:

ORA-38701: Flashback database log 36 seq 2305 thread 1…..
ORA-27041: unable to open file
OSD-04002: unable to open file

To get the database open,  try:

1. Mount the database:
SQL> startup mount;

2. Turn off flashback.
SQL> alter database flashback off;

3. Open the database:
SQL> alter database open;

4. Shutdown
SQL> Shutdown immediate;

5. Mount the database:
SQL> Startup mount;

6. Turn flashback back on.
SQL> alter database flashback on;

7. Open the database:
SQL> alter database open;

Note : 1 If your are on Oracle  10.2.0.4  your instance will not hang but the alert log will display information like below.

************************************************************
Thu May 20 08:32:43 2010
Errors in file /u1/oracle/OraSWX/admin/SWX/bdump/swx_rvwr_17519.trc:
ORA-38701: Flashback database log 1 seq 1 thread 1: “/u5/flashback/SWX/flashback/o1_mf_5z66yxwt_.flb”
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
*************************************
RVWR encountered an error when writing flashback database logs.
See error stack in alert log.  To avoid crashing the instance,
this instance has turned off flashback database.
*************************************

The database will disable flashback automatically to prevent the instance from hanging.

Backup Controlfile to Trace

How to backup the Oracle Control File?

There are two approaches: you either generate a binary image of the Control File, or you generate a text file script which will re-generate a Control File when run as a SQL script.

To create the binary image, issue the command ALTER DATABASE BACKUP CONTROLFILE TO ‘C:\SOMEWHERE\CONTROL01.BKP’; (obviously pick a destination and file name that are suitable for your needs).

To create the text script version, issue the command ALTER DATABASE BACKUP CONTROLFILE TO TRACE. That causes a file to be written to wherever your init.ora parameter USER_DUMP_DEST is pointing. The file name will be something like ‘ora_<some numbers>.trc’. You’ll have to track it down using the O/S date and timestamp (or you can take advantage of the fact that the “some numbers” bit is your Server Process ID Number -which you can determine from v$session and v$process).
The binary image is ready to work from the word go (with one hugely important proviso, of which more below).

The trace file is, however, a bit of a mess, and needs to be knocked into shape if it is to be of any use in a recovery situation. Firstly, it contains a whole heap of junk at the top (referencing your O/S, your Oracle version, dates and times and process or thread numbers). That’s got to go -which means deleting all of it, until the first line reads STARTUP NOMOUNT. Immediately before that line, you need a connect string, since you can’t ever issue the ‘startup’ command until you’ve connected as a Privileged User. Insert something appropriate, therefore (such as CONNECT / AS SYSDBA if using O/S authentication, or CONNECT SYS/ORACLE AS SYSDBA if using Password File Authentication). You may possibly also need to qualify the ‘STARTUP NOMOUNT’ line itself so that it references an appropriate init.ora (for example, STARTUP NOMOUNT PFILE=/SOMEWHERE/NON-DEFAULT/INIT.ORA). Other than that, the file is fine, and useable.

Under no circumstances should you ever need a backup of your Control File, of either type. You’d only use one if ALL copies of the Control File had become corrupt or had been destroyed (and the whole purpose of multiplexing your Control Files is to minimise those possibilities). But if that fateful day ever arrives, then you simply need to fire up Server Manager (or SQL Plus if using 8i or above), and issuing the command to run the trace file script (i.e., typing @name_of_script.ext). There’s no need to connect first -remember, that was the first line we added to the script earlier. If for some reason the Instance is already running, the script will fail -it needs a completely shut down system before it can do its stuff.

The trouble starts when you attempt to restore the binary version of the Control File backup. Because it was an exact, binary copy of a Control File, its SCN number will not agree with the SCN in the headers of all the data files -basically, the Master Clock is out of whack. You therefore have to issue the command “RECOVER DATABASE USING BACKUP CONTROLFILE;” to tell the system not to pay too much attention to the SCN of the Control File. Unfortunately, after you issue that command (and following any recovery that it might cause to take place), you must open the database with the command “ALTER DATABASE OPEN RESETLOGS;”.

That’s unfortunate, because the ‘resetlogs’ command basically forces the entire database to re-synchronise at time zero, which is fine for getting the database open, but is not exactly useful if you ever need to restore from your prior backups (taken when the database was at a time of, say, 10894329), or if you ever expect to be able to apply redo from priot archive logs (which were also taken when the database was at time 10894329 and earlier). Basically, a ‘resetlogs’ renders your database entirely vulnerable: there are no effective backups, and no effective archives. You are therefore supposed to immediately shut the newly-recovered database down, and perform a whole, closed backup of the entire database (which is not exactly a cheap option).

You might wonder why the use of the trace file does not cause this HUGE problem.  The answer is simple: it cheats. Contained within the trace file are locations of every data file in the system. When the file is run, it uses those locations to read the headers of all the data files, whereupon it picks the highest SCN it comes across as the one to write into the header of the Control File it is about to create. That means the re-constructed Control File is already in synchronisation with the data files, and no forced synchronisation to time Zero is therefore required.

So, what’s the best way of backing up the Control File? Answer: multiplex your Control Files so that a recovery is never needed in the first place. But if, despite that, you need some insurance, the Trace File is definitely the better way to go.  It doesn’t render your entire database exposed to failure, it doesn’t effectively trash all prior backups and archives, it works quickly, and well.

There’s only one proviso to this whole discussion: whatever backup method you choose, you need to keep it up-to-date.  Since the Control File contains pointers to the physical location of all data files and redo logs, any backup of that file needs to make sure that those pointers are accurate. Making a physical change to your database (for example, adding a new data file or tablespace, dropping a tablespace, moving or renaming a data file) will instantly render all prior Control File backups out-of-date.  Slightly unnervingly, changing a tablespace from read-write to read-only (or vice versa) also counts as a physical change to your database (because the Control File must always accurately identify any read-only data files).  After any of those operations, therefore, you need to take a fresh backup of the Control File.

It is always conceivable that you could edit a Trace File backup before using it to take account of any physical changes, but the syntax is not easy, and I don’t rate your chances of pulling it off. As for editing the binary copy -forget it!  Net result: keep taking the backups on a regular basis.  I usually recommend a chron job (for our NT friends, that’s an AT job!) which issues the ‘backup to trace’ command every night. It means you need a bit of house-keeping to avoid complete mayhem (and a million trace files) in the user_dump_dest, but it will guarantee a file which, at worst, can be used with the mere addition of a line or two to reference any data files created between the time the trace file was created and the time all Control Files went awol.

RMAN-06059: expected archived log not found. ORA-19625:

Whenever you are trying to backup Archivelogs from RMAN you might get the following error.

Starting backup at 25-FEB-10
current log archived
archived log /u2/archivelogs/1_12241_642439782.dbf not found or out of sync with catalog
trying alternate file for archivelog thread 1, sequence 12241
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 02/25/2010 10:05:30
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /backup_restor_only/1_12241_642439782.dbf
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

SOLUTION

If the archivelogs are no longer physically available, then you can issue the RMAN command “crosscheck copy of archivelog all”.  This command checks for the physical existence of the archivelog file and will change the V$ARCHIVED_LOG.STATUS of the affected archivelog from “A” for AVAILABLE to “X” for EXPIRED.   Once the archivelog file has a status of X, RMAN will no longer attempt to backup this archivelog file.

RMAN> crosscheck copy of archivelog all;

IMPORTANT NOTE: If there is no RMAN backup of the missing archivelog file, it is essential to perform a full-database backup. Otherwise, you may not be able to recover the database beyond missing log sequence number.

Backup and Recovery Best Practices RMAN Oracle 10g

1. Turn on block checking
REASON: The aim is to detect, very early the presence of corrupt blocks in the database. This has a slight performance overhead, but Checksums allow Oracle to detect early corruption caused by underlying disk, storage system, or I/O system problems.

SQL> alter system set db_block_checking = true scope=both;

2. Turn on block tracking when using RMAN backups (if running 10g or above)
REASON: This will allow RMAN to backup only those blocks that have changed since the last full backup, which will reduce the time taken to back up, as less blocks will be backed up.

SQL> alter database enable block change tracking using file ‘/u01/oradata/ora1/change_tracking.f’;

3. Duplex log groups and members and have more than one archive log dest
REASON: If an archivelog is corrupted or lost, by having multiple copies in multiple locations, the other logs will still be available and could be used.

If an online log is deleted or becomes corrupt, you will have another member that can be used to recover if required.

SQL> alter system set log_archive_dest_2=’location=/new/location/archive2′ scope=both;
SQL> alter database add logfile member ‘/new/location/redo21.log’ to group 1;

4. When backing up the database use the ‘check logical’ parameter
REASON: This will cause RMAN to check for logical corruption within a block as well as the normal
head/tail checksumming. This is the best way to ensure that you will get a good backup.

RMAN> backup check logical database plus archivelog delete input;

5. Test your backup
REASON: This will do everything except actually restore the database. This is the best method to determine if your backup is good and usable before being in a situation where it is
critical and issues exist.

RMAN> restore validate database;

6. Have each datafile in a single backup piece
REASON: When doing a partial restore RMAN must read through the entire piece to get the
datafile/archivelog requested. The smaller the backup piece the quicker the restore can
complete. This is especially relevent with tape backups of large databases or where the
restore is only on individual / few files.

RMAN> backup database filesperset 1 plus archivelog delete input;

7. Maintain your RMAN catalog/controlfile
REASON: Choose your retention policy carefully. Make sure that it compliments your tape subsystem
retention policy, requirements for backup recovery strategy. If not using a catalog, ensure that your controlfile record keep time instance parameter matches your retention policy.

SQL> alter system set control_file_record_keep_time=21 scope=both;
This will keep 21 days of backup records.

Run regular catalog maintenance.
REASON: Delete obsolete will remove backups that are outside your retention policy. If obsolete backups are not deleted, the catalog will continue to grow until performance becomes an issue.

RMAN> delete obsolete;

REASON: crosschecking will check that the catalog/controlfile matches the physical backups. If a backup is missing, it will set the piece to ‘EXPIRED’ so when a restore is started, that it will not be eligible, and an earlier backup will be used. To remove the expired backups from the catalog/controlfile use the delete expired command.

RMAN> crosscheck backup;
RMAN> delete expired backup;

8. Prepare for loss of controlfiles set autobackup on
REASON: This will ensure that you always have an up to date controlfile available that has been taken at the end of the current backup not during.

RMAN> configure controlfile autobackup on;

keep your backup logs
REASON: The backup log contains parameters for your tape access, locations on controlfile backups that can be utilised if complete loss occurs.

9. Test your recovery
REASON: During a recovery situation this will let you know how the recovery will go without actually doing it, and can avoid having to restore source datafiles again.

SQL> recover database test;

10. Do not specify ‘delete all input’ when backing up archivelogs
REASON: Delete all input’ will backup from one destination then delete both copies of the archivelog where as ‘delete input’ will backup from one location and then delete what has been backed up. The next backup will back up those from location 2 as well as new logs from location 1, then delete all that are backed up. This means that you will have the archivelogs since the last backup available on disk in location 2 (as well as backed up once) and two copies backup up prior to the previous backup