Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files (Doc ID 734862.1)

In this Document

Goal

Solution

References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 29-Jul-2014***

GOAL

Step by step guide on how to recreate standby control file when datafiles are on ASM and using Oracle Managed Files  (OMF)

In case you may want or need more about your current topic - please also access the Data Guard Community of Customers and Oracle Specialists directly via:
https://communities.oracle.com/portal/server.pt/community/high_availability_data_guard/302

Please Note: If your Standby database role at the time of creating the new controlfile is "SNAPSHOT STANDBY" do NOT follow this process as you will destroy your standby databases ability to revert from a snapshot standby back to a physical standby.

SOLUTION

Below are the steps to accomplish the task : 

Step 1: Disable data guard broker if it exist
Step 2: Create the Standby control file on primary database
Step 3: Copy the controlfile backup to the standby system
Step 4: Shutdown, restore, rename
Step 5: Enable configuration if disabled in step 1.

Example

Step 1 : DGMGRL> disable configuration;

Step 2 : Create the Standby control file on primary database. 

$ export ORACLE_SID=DEL
$rman target /
RMAN> backup current controlfile for standby format 'stdbyctl.bkp';
RMAN> EXIT;

stdbyctl.bkp file will be created in "$ORACLE_HOME/dbs" (Unix) or "$ORACLE_HOME/database" (Windows).

Step 3 : Copy the controlfile backup to the standby system 
Using ftp/scp move stdbyctl.bkp to standby system

Step 4 : Shutdown, restore, rename.

A. Shutdown all instances of the standby.


On linux
$ export ORACLE_SID=MUM
On windows
set ORACLE_SID=MUM

$sqlplus / as sysdba
SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

B. Depending on the location of the logfiles on the standby server remove all online and standby redo logs from the standby directories Using an Operating System utility or ASMCMD and make sure that you have the LOG_FILE_NAME_CONVERT parameter defined to translate any directory paths.

C. Startup one instance of Standby database in nomount stage:

$sqlplus / as sysdba
SQL> startup nomount
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 75498636 bytes
Database Buffers 125829120 bytes
Redo Buffers 7139328 bytes

D. Connect to RMAN with nocatalog option and Restore the standby control file:

$rman nocatalog target /
RMAN> restore standby controlfile from '\tmp\stdbyctl.bkp';

Starting restore at 29-AUG-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:17
output filename=+DATA1/del/controlfile/current.257.661096899
Finished restore at 29-AUG-08

E. Mount standby database

RMAN> alter database mount;

database mounted

F. Catalog the datafiles of standby database

Below command will give you a list of files and ask if they should all be catalog. Review the list and say YES if all the datafiles are properly listed
In below command while cataloging the files, the string specified should refer to the diskgroup/filesystem destination of the standby data files.

RMAN> catalog start with '+DATA1/MUM/DATAFILE/';

Starting implicit crosscheck backup at 29-AUG-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 29-AUG-08

Starting implicit crosscheck copy at 29-AUG-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 29-AUG-08

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +fra/MUM/BACKUPSET/2008_07_28/nnndf0_TAG20080728T113319_0.296.661260801
File Name: +fra/MUM/BACKUPSET/2008_07_28/ncsnf0_TAG20080728T113319_0.297.661260847
File Name: +fra/MUM/CONTROLFILE/backup.272.661096103

searching for all files that match the pattern +DATA1/MUM/DATAFILE/

List of Files Unknown to the Database
=====================================
File Name: +data1/MUM/DATAFILE/SYSTEM.258.661097855
File Name: +data1/MUM/DATAFILE/SYSAUX.259.661097855
File Name: +data1/MUM/DATAFILE/UNDOTBS1.260.661097855
File Name: +data1/MUM/DATAFILE/USERS.261.661097855

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

List of Cataloged Files
=======================
File Name: +data1/MUM/DATAFILE/SYSTEM.258.661097855
File Name: +data1/MUM/DATAFILE/SYSAUX.259.661097855
File Name: +data1/MUM/DATAFILE/UNDOTBS1.260.661097855
File Name: +data1/MUM/DATAFILE/USERS.261.661097855

NOTE:
a) This will only work if you are using OMF. If you are using ASM without OMF you have to catalog all non-OMF Datafiles as Datafile Copies manually using

RMAN> catalog datafilecopy '<File-Specification>';

b) If you have Datafiles on different Diskgroups you have to catalog from all Diskgroups, of course.


G. Commit the changes to the controlfile

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA1/mum/datafile/system.258.661097855"
datafile 2 switched to datafile copy "+DATA1/mum/datafile/undotbs1.260.661097855"
datafile 3 switched to datafile copy "+DATA1/mum/datafile/sysaux.259.661097855"
datafile 4 switched to datafile copy "+DATA1/mum/datafile/users.261.661097855"

RMAN> EXIT;

H. Re-enable flashback on the standby database.
$sqlplus / as sysdba
SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

I. Query v$log and clear all online redo log groups

SQL> select group# from v$log;

GROUP#
----------
1
2
3

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

J. Query v$standby_log and clear all standby redo logs
SQL> select group# from v$standby_log;

GROUP#
----------
4
5
6

SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database clear logfile group 5;

Database altered.

SQL> alter database clear logfile group 6;

Database altered.

Recreate the standby redo logs on standby database if standby redo logs are not present on the primary.

SQL> select group# from v$standby_log;

no row selected

SQL> alter database add standby logfile group 4 size 50m;

Database altered.

SQL> alter database add standby logfile group 5 size 50m;

Database altered.

SQL> alter database add standby logfile group 6 size 50m;

Database altered.

K. Start Managed recovery process on standby

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Step 5 : DGMGRL>enable configuration;

SQL> exit

If there exist online redo logs and standby redo logs, please see below note

Handling ORL and SRL (Resize) on Primary and Physical Standby in Data Guard Environment (Doc ID 1532566.1)

Analyzing Sensor Data with Spark Streaming

By the end of this activity, you will be able to:

1. Read streaming data into Spark
2. Create and apply computations over a sliding window of data

Step 1. Open Jupyter Python Notebook for Spark Streaming. Open a web browser by clicking on the web browser icon at the top of the toolbar:

Navigate to localhost:8889/tree/Downloads/big-data-3/spark-streaming:

Open the Spark Streaming Notebook by clicking on Spark-Streaming.ipynb:

Step 2. Look at sensor format and measurement types. The first cell in the notebook gives an example of the streaming measurements coming from the weather station:

Each line contains a timestamp and a set of measurements. Each measurement has an abbreviation, and for this exercise, we are interested in the average wind direction, which is Dm. The next cell lists the abbreviations used for each type of measurement:

The third cell defines a function that parses each line and returns the average wind direction (Dm). Run this cell:

Step 3. Import and create streaming context. Next, we will import and create a new instance of Spark's StreamingContext:

Similar to the SparkContext, the StreamingContext provides an interface to Spark's streaming capabilities. The argument sc is the SparkContext, and 1 specifies a batch interval of one second.

Step 4. Create DStream of weather data. Let's open a connection to the streaming weather data:

Instead of 12028, you may find that port 12020 works instead. This create a new variable lines to be a Spark DStream that streams the lines of output from the weather station.

Step 5. Read measurement. Next, let's read the average wind speed from each line and store it in a new DStream vals:

This line uses flatMap() to iterate over the lines DStream, and calls the parse() function we defined above to get the average wind speed.

Step 6. Create sliding window of data. We can create a sliding window over the measurements by calling the window() method:

This create a new DStream called window that combines the ten seconds worth of data and moves by five seconds.

Step 7. Define and call analysis function. We would like to find the minimum and maximum values in our window. Let's define a function that prints these values for an RDD:

This function first prints the entire contents of the RDD by calling the collect() method. This is done to demonstrate the sliding window and would not be practical if the RDD was containing a large amount of data. Next, we check if the size of the RDD is greater than zero before printing the maximum and minimum values.
Next, we call the stats() function for each RDD in our sliding window:

This line calls the stats() function defined above for each RDD in the DStream window.

Step 8. Start the stream processing. We call start() on the StreamingContext to begin the processing:

The sliding window contains ten seconds worth of data and slides every five seconds. In the beginning, the number of values in the windows are increasing as the data accumulates, and after Window 3, the size stays (approximately) the same. Since the window slides half as often as the size of the window, the second half of a window becomes the first half of the next window. For example, the second half of Window 5 is 310, 321, 323, 325, 326, which becomes the first half of Window 6.
When we are done, call stop() on the StreamingContext:

Instructions for Configuring VirtualBox for Spark Streaming

Spark Streaming requires more than one executor. This Reading describes how to configure VirtualBox so that the Cloudera VM has more than one virtual processor.

Step 1. Stop the Cloudera VM. Before we can change the settings for the Cloudera VM, the VM needs to be powered off. If the VM is running, click on System in the top toolbar, and then click on Shutdown:

Next, click on Shut down:

Step 2. Change number of processors. Once the Cloudera VM is powered off, select the Cloudera VM in the list of virtual machines in the VirtualBox Manager:

Next, click on Settings:

Next, click on System:

Next, click on Processor:

The default number of processors is one. Change this to two or more:

Finally, click on OK and start the Cloudera VM.

Exploring Spark SQL and Spark DataFrames

By the end of this activity, you will be able to:

1. Access Postgres database tables with SparkSQL
2. Filter rows and columns of a Spark DataFrame
3. Group and perform aggregate functions on columns in a Spark DataFrame
4. Join two SparkDataframes on a single column

Step 1. Open Jupyter Python Notebook for SparkSQL. First Open the Terminal and enter the command "pyspark" to setup the server. Next, open a web browser by clicking on the web browser icon at the top of the toolbar:

Navigate to localhost:8889/tree/Downloads/big-data-3/spark-sql:

Open the SparkSQL Notebook by clicking on SparkSQL.ipynb:

Step 2. Connect to Postgres Table. This notebook already contains three lines of code so you do not have to enter them. Run these three lines. The first line imports the SQLContext module, which is needed access SQL databases in Spark:

The second line creates a new SQLContext from the SparkContext sc:

The third line creates a new Spark DataFrame in the variable df for the Postgres table gameclicks:

The format("jdbc") says that the source of the DataFrame will be using a Java database connection, the url option is the URL connection string to access the Postgres database, and the dbtable option specifies the gameclicks table.

Step 3. View Spark DataFrame schema and count rows. We can call the printSchema() method to view the schema of the DataFrame:

The description lists the name and data type of each column.
We can also call the count() method to count the number of rows in the DataFrame:

Step 4. View contents of DataFrame. We can call the show() method to view the contents of the DataFrame. The argument specifies how many rows to display:

Step 5. Filter columns in DataFrame. We can filter for one or more columns by calling the select() method:

Step 6. Filter rows based on criteria. We can also filter for rows that match a specific criteria using filter():

The arguments to filter() are a Column, in this case specified as df["teamlevel"], and the condition, which is greater than 1. The remainder of the commander selects only the userid and teamlevel columns and shows the first five rows.

Step 7. Group by a column and count. The groupBy() method groups the values of column(s). The ishit column only has values 0 and 1. We can calculate how many times each occurs by grouping the ishit column and counting the result:

Step 8. Calculate average and sum. Aggregate operations can be performed on columns of DataFrames. First, let's import the Python libraries for the aggregate operations. Next, we can calculate the average and total values by calling the mean() and sum() methods, respectively:

Step 9. Join two DataFrames. We can merge or join two Dataframes on a single column. First, let's create a DataFrame for the adclicks table in the Postgres database by copying the third cell in this notebook and changing gameclicks to adclicks and storing the result in a new variable df2:

Let's view the columns in df2 by calling printSchema():

We can see that the adclicks df2 DataFrame also has a column called userid. Next, we will combine the gameclicks and adclicks DataFrames by calling the join() method and saving the resulting DataFrame in a variable called merge:

We are calling the join() method on the gameclicks DataFrame; the first argument is the DrataFrame to join with, i.e., the adclicks DataFrame, and the second argument is the column name in both DataFrames to join on.
Let's view the schema of merge:

We can see that the merged DataFrame has all the columns of both gameclicks and adclicks.
Finally, let's look at the contents of merge:

Complete Checklist for Upgrading to Oracle Database 12c Release 2 (12.2) using DBUA (Doc ID 2189854.1)

In this Document

Purpose

Scope

Details

Database Upgrade Assistant (DBUA)

Upgrade Path / Compatibility Matrix for 12.2 Oracle Database.

Requirements and Recommendation for source database.

Requirements and Recommendations for Target database

Recommendation for Oracle Spatial Component:

Prerequisites for Preparing Oracle Home on Windows

Pre-Upgrade

Check for Invalid Objects / Components:

Gathering Optimizer Statistics to Decrease Oracle Database Downtime

Verifying Materialized View Refreshes are Complete Before Upgrade

Check of TIMESTAMP WITH TIMEZONE Datatype

Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading

Purging Recycle Bin before upgrade

Starting with Oracle Database 12c release 2 (12.2), you can upgrade the database without disabling Oracle Database Vault.

Requirements for Upgrading Databases That Use Oracle Label Security and Oracle Database Vault (For Oracle Database releases earlier than 12.1)

Audit Table Preupgrade

Running olspreupgrade.sql on Oracle Database Release 11.2

Save OEM DB Control Configuration (For Oracle Database releases earlier than 12.1)

Single-instance databases:

Oracle Real Application Clusters (Oracle RAC) databases:

Removing DB Control with emremove.sql (For Oracle Database releases earlier than 12.1)

Desupport of OLAP Catalog (OLAP AMD) (For Oracle Database releases earlier than 12.1)

Drop JSON-Enabled Context search Indexes (Only for the upgrade of 12.1 DB to 12.2)

Copying Transparent Encryption Oracle Wallets

Check the accounts use Case-Insensitive password version

About Password Case Sensitivity

Requirement for Upgrading Databases that Use Oracle Warehouse Builder (OWB)

Invoke DBUA

DBUA (Step 1 of 9)

DBUA (step 2of 9)

DBUA (Step 3 of 9)

DBUA (Step 4 of 9)

DBUA (Step 5 of 9)

DBUA (Step 6 of 9)

DBUA (step 7 of 9)

DBUA (Step 8 of 9)

DBUA Step 9 of 9)

Post-Upgrade

Known Issues

References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.2.0.1 [Release 11.2 to 12.2]
Information in this document applies to any platform.

PURPOSE

 The purpose of this article is to perform the upgrade of the pre-12c database using DBUA to 12.2.0.1.

SCOPE

DBA, Support

DETAILS

Database Upgrade Assistant (DBUA)

• Database Upgrade Assistant (DBUA) interactively steps you through the upgrade process. configures the database for the new Oracle Database 12c release 2. It is the recommended method for performing a major release upgrade or patchset release upgrade.
• DBUA automates the upgrade process by performing all of the tasks. DBUA makes appropriate recommendations for configuration options and then you can act on these recommendations.
• DBUA provides support for Oracle Real Application Clusters (Oracle RAC) databases. In an Oracle RAC environment, DBUA upgrade all the database and configuration files on all nodes in the cluster.
• DBUA, graphical user interface must be invoked within the new Oracle home where the Oracle Database 12c R2 software has been installed.
  For windows, Only an Administrator or Installed owner should invoke DBUA for Windows systems.
• DBUA starts the Pre-Upgrade Tool, which automatically fixes some configuration settings to the values required for the upgrade. For example, the Pre-Upgrade Tool can change initialization parameters to values required for the upgrade. The Pre-Upgrade Tool also provides you with a list of items that you need to fix manually before you can continue with the upgrade.
• It also gives certain recommendations on certain areas belonging to the database. The recommendations can then be acted on making the upgrade process user friendly and easy.
• Once, you address / fix the pre-upgrade recommendation / warnings /errors and continue with the upgrade, DBUYA shows the progress of the upgrade for each component of source database.
• As with previous releases of DBUA, 12c DBUA restricts the carry over of hidden parameters since Oracle recommends not to have hidden parameters other than those suggested via support during the upgrade.
  To view existing hidden parameters execute the following command while connected AS SYSDBA:
  SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';
• DBUA performs some of the checks before actually starting the database upgrade. Some of the checks can be done manually to reduce downtime for the upgrade.
• DBUA provides below options:
  - Upgrade timezone. The default timezone vetrsion in 12.2.0.1 is 26.- Gather dictionary statistics before upgrade.
  - Make user tablespaces read only.
  - Take RMAN backup before upgrade.
  - Restore database backup to rollback upgrade
  - Option to execute Custom scripts before and after upgrade
  - show the location of DBUA logs and Alert log files.
  - Option to upgrade existing listener to 12c home or create a new listener in 12.2 target home.
• Starting with Oracle Database 12c release 2 (12.2), you can upgrade the database without disabling Oracle Database Vault. However, if you disabled Oracle Database Vault, then you must enable it manually after an upgrade.

Upgrade Path / Compatibility Matrix for 12.2 Oracle Database.

DBUA can upgrade only supported versions of direct upgrade.

Direct Upgrade to 12.2:

Source Database	Target Database
11.2.0.3 and higher	12.2.0.x
12.1.0.x (12.1.0.1 - 12.1.0.2)	12.2.0.x

Indirect Upgrade to 12.2:

Source Database	Upgrade Path	Target Database
7.3.3 (lower)	7.3.4 --> 9.2.0.8 --> 11.2.0.3 or higher	12.2.0.x
8.0.5 ( or lower )	8.0.6 --> 9.2.0.8 --> 11.2.0.3 or higher	12.2.0.x
8.1.7 ( or lower )	8.1.7 --> 9.2.0.8 --> 11.2.0.3 or higher	12.2.0.x
9.0.1.3 ( or lower )	9.0.1.3 --> 9.2.0.8 --> 11.2.0.3 or higher	12.2.0.x
9.2.0.7 ( or lower )	9.2.0.7 --> 11.2.0.3 or higher	12.2.0.x
10.2.0.4 ( or lower )	10.2.0.4 --> 11.2.0.3 or higher	12.2.0.x
11.1.0.6	11.1.0.6 --> 11.2.0.3 or higher	12.2.0.x
11.2.0.1	11.2.0.1 --> 11.2.0.3 or higher	12.2.0.x

Requirements and Recommendation for source database.

• Ensure that all database components/objects provided by Oracle are VALID in the source database prior to starting the upgrade.
• Before you start an upgrade or downgrade process, Oracle strongly recommends that you update both your earlier release and your new release (12.2) Oracle Database to the latest Oracle bundle patch or patch set update (BP or PSU).
• Ensure that you do not have duplicate objects in the SYS and SYSTEM schema. For 1 and 2 refer to:
  Doc Id 556610.1 - Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)dbupgdiag.sql script is a set of sql statements intended to provide a user friendly output to diagnose the status of the database either before (or) after upgrade. The script will create a output file called db_upg_diag_<sid>_<timestamp>.log
• Make sure to have a valid backup of source database prior to upgrade.
• Disable any custom triggers that would get executed before / after DDL statements. Re-enable after the upgrade.
• Check the database server upgrade/downgrade compatibility matrix before upgrading the database.
• Set Archive Log ON during upgrade. Oracle recommends that you set Archive Log ON in order for DBUA to create and update the log file for the upgrade process.
• For Oracle RAC, if you upgrade a cluster database using DBUA, then you must leave the CLUSTER_DATABASE initialization parameter set to TRUE.
• Ensure to run the pre-upgrade utility prior to upgrading the database.
  Examine and follow the recommendation given in the preupgrade log file.
• Materialized views in source database should be stopped before upgrade
  Doc ID 1406586.1 - How to Handle Materialized Views When You Upgrade or Clone a Database
• Disable scheduled database custom jobs / cron jobs.

Requirements and Recommendations for Target database  

• Verify whether your operating system / platform is certified for 12.2 release.
• Download and Install Oracle 12c Release 2 (12.2.0.1) in a new Oracle_Home and make sure there are no binary relinking errors.
• Download and Install the latest available Patch Set Update (PSU) or Bundle Patch (BP) (If available) from My Oracle Support (MOS).
• Make sure to set the ORACLE_HOME, PATH, LD_LIBRARY_PATH, LIBPATH etc. to 12.2 target home.

Recommendation for Oracle Spatial Component:

Execute the below query on source database prior to 12.2 upgrade:
col owner format a15
col table_name format a30
col column_name format a30
SELECT owner,table_name,column_name
FROM dba_tab_columns
WHERE data_type = 'SDO_GEOMETRY'
AND owner != 'MDSYS' ORDER BY 1,2,3;
If the above query return row(s) then below patch need to be applied on target 12.2 Oracle Home before performing the database upgrade.
Link : https://updates.oracle.com/Orion/Download/process_form/p25293022_122010_Generic.zip?file_id=95607293&aru=21422588
FileName : p25293022_122010_Generic.zip
If the patch apply will be skipped on target 12.2 home then utlrp.sql script during post upgrade phase may take more time to complete and could impact the outage.

Prerequisites for Preparing Oracle Home on Windows

Your system must meet these requirements before you can upgrade Oracle Database on Microsoft Windows platforms. For security reasons, different Microsoft Windows user accounts configured as Oracle
home users for different Oracle homes are not allowed to share the same Oracle Base.

• Database upgrade is supported when the same Windows user account is used as the Oracle home user in both the source and destination Oracle homes.
• Database upgrade is supported when the Oracle home from which the database is being upgraded uses the Windows Built-in Account. Releases earlier than Oracle Database 12c (release 11.2 and earlier) only supported the built-in account option for the Oracle home user on Windows.
• The Oracle home user may not have access to files outside its own Oracle Base and Oracle home. If that is the case, then if you choose a different Oracle Base during upgrade, it is possible that Oracle Database services cannot access files in the older Oracle Base. Using DBUA for database upgrade ensures that the Oracle home user has access to files outside of its own Oracle Base and its own Oracle home.

Pre-Upgrade

$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]

FILE|TERMINAL - Use this option to direct script output to a file. Use TERMINAL to direct output to the terminal. If it is not specified then defaultr is FILE.
TEXT - Use this option to specify log should be in Text format. Use TEXT to specify text output. Use XML to specify XML output. If you do not specify an output type, then the default is text.
DIR - Logs will be created under <output_dir>. Directs the output to a specific directory. If you do not specify an output directory with the DIR option, then the output is directed to one of the default locations: If you define ORACLE_BASE environment variable then the generated scripts and log files will be created under $ORACLE_BASE/cfgtoollogs/<dbname>/preupgrade/ location else it will create under $ORACLE_HOME/cfgtoollogs/db_name/preupgrade/.

For example,
source Oracle Home : /u01/app/oracle/product/12.1.0.1/dbgome_1
target Oracle Home : /u01/app/oracle/product/12.2.0.1/dbhome_1
$ export ORACLE_SID=orcl
$ export ORACLE_BASE=/u01/app/oracle
$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/dbhome_1
$ $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/preupgrade.jar FILE TEXT
Preupgrade generated files:
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
$

Examine the preupgrade.log file and follow the recommendation.

The latest preupgrade utility for 12.2 can be  found from :
How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)

Check for Invalid Objects / Components:

set pagesize500
set linesize 100
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type;
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

 If you find invalid objects and/or database components then try to VALIDATE the invalid objects and/or database components by executing the following steps:

Run $ORACLE_HOME/rdbms/admin/utlrp.sql to validate the invalid objects in the database. You can execute the utlrp.sql scripts multiple times to validate the invalid objects.

$ sqlplus "/ as sysdba"
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Gathering Optimizer Statistics to Decrease Oracle Database Downtime

Oracle strongly recommends that you use this procedure to gather statistics before performing Oracle Database upgrades.Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS   procedure to gather these statistics. For example, enter the following SQL statement:     

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Verifying Materialized View Refreshes are Complete Before Upgrade

  Use this procedure to query the system to determine if there are any materialized view refreshes still in progress. Before upgrading Oracle Database, you must wait until all materialized views have
completed refreshing.

SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

How to Handle Materialized Views When You Upgrade or Clone a Database (Doc ID 1406586.1)

Check of TIMESTAMP WITH TIMEZONE Datatype

The time zone files that are supplied with Oracle Database 12c release 2 (12.2) is version 26.

Case 1 Timezone version of source database is lower or equal 26.

If the source database is using a timezone file lower than version 26 then there is no DST patch to apply in source oracle home or target 12cR2 home.

Case 2 Timezone version of source database is higher than 26.

If the source database uses a Timezone version higher than 26 then BEFORE the upgrade you MUST patch the target 12cR2 $ORACLE_HOME with a timezone data file of the SAME version as the one used in the source release database.

Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading

Execute below query to check for the status of the backup:
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
Ensure that no files require media recovery:
SQL> SELECT * FROM v$recover_file;

Purging Recycle Bin before upgrade

 SQL> PURGE DBA_RECYCLEBIN;

we can purge the recycle bin via DBUA.

Starting with Oracle Database 12c release 2 (12.2), you can upgrade the database without disabling Oracle Database Vault.

Starting with Oracle Database 12c release 2 (12.2), you can upgrade the database without disabling Oracle Database Vault. However, if you disabled Oracle Database Vault, then you must enable it manually after an upgrade.
Starting with Oracle Database 12c release 2 (12.2), if you have Oracle Database Vault enabled, then you can upgrade the Oracle Database without first disabling Oracle Database Vault. After the upgrade, Oracle Database Vault is enabled by default with the enforcement settings that you had in place before the upgrade. If you manually disable Oracle Database Vault before the upgrade, then you must enable Oracle Database Vault manually after the upgrade.

Enable Oracle Database Vault in the upgraded database by using the procedure dvsys.dbms_macadm.enable_dv(). Run this procedure with a user account that is granted either DV_OWNER or DV_ADMIN. After you run the procedure, restart the database instance so that the procedure takes effect.

Requirements for Upgrading Databases That Use Oracle Label Security and Oracle Database Vault (For Oracle Database releases earlier than 12.1)

You must complete these tasks before starting an upgrade with a database using Oracle Label Security or Oracle Database Vault.

Audit Table Preupgrade

If you are upgrading from a database earlier than Oracle Database release 12.1 that uses Oracle Label Security (OLS) and Oracle Database Vault, then you must first run the OLS preprocess script, olspreupgrade.sql, to process the aud$ table contents. The OLS upgrade moves the aud$ table from the SYSTEM schema to the SYS schema. The olspreupgrade.sql script is a preprocessing script required for this move.

Running olspreupgrade.sql on Oracle Database Release 11.2

If Oracle Label Security is installed in the earlier release that you are upgrading, then you must run the OLS preprocess olspreupgrade.sql script.

If Oracle Database Vault is not installed with your release 11.2 database, then you can skip steps 2, 3, 6, and 7 in this section.

To run the OLS preprocess script on a release 11.2 database before upgrading:

1. Copy the ORACLE_HOME/rdbms/admin/olspreupgrade.sql script from the newly installed Oracle home to the Oracle home of the database that you want to upgrade.
2. Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade.
3. Run the following statement:
SQL> GRANT DV_PATCH_ADMIN to SYS;
4. At the system prompt, connect SYS as SYSDBA:
CONNECT SYS AS SYSDBA
5. Run the OLS preprocess script:
ORACLE_HOME/rdbms/admin/olspreupgrade.sql
You may continue to run your applications on the database while the OLS preprocess script is running.
6. After the olspreupgrade.sql completes its run successfully, start SQL*Plus and connect to the database as DVOWNER.
7. Run the following SQL statement:
SQL> REVOKE DV_PATCH_ADMIN from SYS;

Save OEM DB Control Configuration (For Oracle Database releases earlier than 12.1)

If you plan to downgrade database after upgrading to 12.2, then before starting the upgrade save the DB Control configuration and data using emdwgrd utility, so that you can restore the files after downgrading.

Steps to Save data:

     1. Install the software for the new Oracle Database 12c release.
     2. Set ORACLE_HOME to your old Oracle home.
     3. Set ORACLE_SID to the SID of the database being upgraded.
     4. Set PATH, LD_LIBRARY_PATH, and SHLIB_PATH to point to the upgraded Oracle Database Oracle home.
     5. Change directory to the new Oracle Database release Oracle home.
     6. Run emdwgrd

Single-instance databases:

emdwgrd[sh|bat] -save -sid old_SID -path save_directory

Oracle Real Application Clusters (Oracle RAC) databases:

Remote copy must be enabled across all cluster member nodes. Use EM_REMCP environment variable, to indicate which remote copy is configured, for example: export EM_REMCP /usr/bin/scp

emdwgrd -save -cluster -sid old_SID -path save_directory

7. Enter the SYS password for the database that you want to upgrade.

Removing DB Control with emremove.sql (For Oracle Database releases earlier than 12.1)

Warning :
The below step should not be executed during the upgrade of Enterprise Manager Cloud Control Repository database. Doing so your EM Cloud Control Repository DB will become unusable.

  

Starting with Oracle Database 12c release 1 (12.1), DB Control is removed as part of the upgrade process.
Stop DB Console:
emctl stop dbconsole
Execute emremove.sql script. The script willbe located in target 12.2 OH/rdbms/admin/.
SQL>SET ECHO ON
SQL>SET SERVEROUTPUT ON
SQL>@emremove.sql >> Script located in new 12c ORACLE_HOME/rdbms/admin

After emremove.sql completes, you must manually remove ORACLE_HOME/HOSTNAME_SID and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID directories from your file system.

Desupport of OLAP Catalog (OLAP AMD) (For Oracle Database releases earlier than 12.1)

Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is desupported and will be removed from the database during the database upgrade. This step can be manually performed before the upgrade to reduce downtime. It can be removed by running the SQL script $ORACLE_HOME/olap/admin/catnoamd.sql script. Do not run the script in UPGRADE mode.

Drop JSON-Enabled Context search Indexes (Only for the upgrade of 12.1 DB to 12.2)

If you are upgrading from Oracle Database 12c release 1 (12.1) to 12c release 2 (12.2), then Oracle recommends that you drop the JSON-enabled context index before upgrading.
Oracle recommends that you drop your existing JSON-enabled index.

If you created a JSON search index using Oracle Database 12c Release 1 (12.1.0.2) then Oracle recommends that you drop that index and create a new search index for use with later releases, using CREATE SEARCH INDEX statement. Please refer "Oracle Database JSON Developer’s Guide" for more details.

Copying Transparent Encryption Oracle Wallets

If you use Oracle wallet with Transparent Data Encryption (TDE), and you use Database Upgrade Assistant (DBUA) to upgrade the database, then copy thesqlnet.ora and wallet file to the new 12.2 Oracle home.

You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.
1. Log in as an authorized user.
2. Manually copy the sqlnet.ora file, and the wallet file, ewallet.p12, to the new release Oracle home.
3. Open the Oracle wallet in mount.

For example:
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN;

Check the accounts use Case-Insensitive password version

Log in to SQL*Plus as an administrative user, and enter the following SQL query
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

If there are any 10g versions, suggest you to refer Oracle documentation to fix 10g versions, failing to do so, user accounts with LOCKED after upgrade is completed.

About Password Case Sensitivity

Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE.

Requirement for Upgrading Databases that Use Oracle Warehouse Builder (OWB)

OWB releases earlier than release 11.2.0.3 do not work with Oracle Database 12c. OWB is not installed as part of the software for Oracle Database 12c, and OWB components that may exist in earlier releases are not upgraded as part of the Oracle Database upgrade process. However, you can use OWB release 11.2.0.3 with Oracle Database 12c.

Invoke DBUA

run DBUA, once all Prerequisite checks are successful. please make sure that environment variables are pointing to target 12.2 home.

unset the ORACLE_PATH environment variable, if it is already set. 

DBUA (Step 1 of 9)

we need to choose the database SID for upgrade to 12.2.0.1. In this case, it is orcl database.

DBUA (step 2of 9)

 In below screen, DBUA will perform various pre-checks. It will execute the preupgrade script (preupgrade.jar) and return warnings / errors.

DBUA (Step 3 of 9)

once the pre-upgrade warnings has been adressed, next screen will show various options like Enable Parallel Upgrade, Recompile invalid objects in post upgrade phase, upgrading timezone, gather statistics before the upgrade, setting the user tablespaces to read only during upgrade. also, you can provide any SQL scripts before or after the upgrade.

DBUA (Step 4 of 9)

After selecting option, The next screen will show various Recovery options where it will create Guaranteed Restore Point, or Take an RMAN backup or the backup is already taken outside DBUA then "I have my own backup and restore stratege " option can be chosen.

DBUA (Step 5 of 9)

in this screen, we can create a new listener or upgrade the existing listener to 12.2.0.1.

DBUA (Step 6 of 9)

This screen is for configure EM express or register the upgraded database with EM Cloud control.

DBUA (step 7 of 9)

This is the summary screen before the actual upgrade starts. Click on Finish to proceed with the upgrade.

DBUA (Step 8 of 9)

The DBUA will start the upgrade process of orcl database. It will perform pre-upgrade, database upgrade and post upgrade steps.

DBUA will perform the upgrade of database components like Oracle server, XML etc.

DBUA Step 9 of 9)

The final screen will show the result of upgrade.

It will show the log file location and the time taken by various steps of upgrade.

Post-Upgrade

Execute dbupgdiag.sql script to verify status of objects and components. If there are invalid objects then run utlrp.sql to recompile the invalid objects as follows:

sql> connect / as sysdba
sql> @?/rdbms/admin/utlrp.sql

Known Issues

Below are the known issues:

Doc ID 2279575.1 - Upgrade to 12.2.0.1 Fails With Error "ORA-00001: Unique Constraint (SYS.I_DAM_LAST_ARCH_TS$) Violated"