Oracle 18c Database Upgrade From 12.1.0.2 to 18.3.0.0.0 Using Manual Method

Oracle 18c Database Manual Upgrade From 12.1.0.2 to 18.3.0.0.0 :-

Description:- 

A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database release.

Oracle Database Releases That Support Direct Upgrade
Review the supported options for direct upgrades to the latest Oracle Database release.
You can perform a direct upgrade to the new release from the following releases:
• 11.2.0.3 and 11.2.0.4
• 12.1.0.1 and 12.1.0.2
• 12.2.0.1

Presteps for db upgrade :-

Take RMAN full backups before upgrade.

rman target / 

run {
allocate channel test_backup_disk1 type disk format '/u01/backup/%d_%U';
backup incremental level 0 tag 'ORCL_BEFORE_UPG'  database;
backup format  tag 'ORCL_CONTROL_FILE' current controlfile;
release channel test_backup_disk1;
}

Ensure backup is complete before upgrade.

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

Empty Recycle bin.

SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.

Run Gather statistics to finish upgrade soon.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

 Pre-upgrade checks :

Run the preupgrade tool .

The preupgrade.jar Pre-Upgrade Information Tool is supplied with Oracle Database 18c.
This tool has reside in new oracle home.
/u02/app/oracle/product/18.3.0/db_1/rdbms/admin/preupgrade.jar

[oracle@test db_1]$ mkdir /u01/preupgrade

[oracle@test db_1]$ export ORACLE_SID=orcl
[oracle@test db_1]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
[oracle@test db_1]$ /u01/app/oracle/product/12.1.0/db_1/jdk/bin/java -jar /u02/app/oracle/product/18.3.0/db_1/rdbms/admin/preupgrade.jar FILE DIR /u01/preupgrade/
===================
PREUPGRADE SUMMARY
===================
/u01/preupgrade/preupgrade.log
/u01/preupgrade/preupgrade_fixups.sql
/u01/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups
@/u01/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/postupgrade_fixups.sql

Preupgrade complete: 2019-02-06T04:42:43

After run the above tool we will get below sql files.

[oracle@test db_1]$ cd /u01/preupgrade
[oracle@test u01/preupgrade]$ ls -lrt

total 660
-rw-r--r-- 1 oracle oinstall  14846 Feb  8 04:42 dbms_registry_extended.sql
-rw-r--r-- 1 oracle oinstall 7963 Feb 6 04:42 preupgrade_driver.sql
-rw-r--r-- 1 oracle oinstall 422048 Feb 6 04:42 preupgrade_package.sql
-rw-r--r-- 1 oracle oinstall 14383 Feb 6 04:42 parameters.properties
-rw-r--r-- 1 oracle oinstall 83854 Feb 6 04:42 preupgrade_messages.properties
-rw-r--r-- 1 oracle oinstall 50172 Feb 6 04:42 components.properties
drwxr-xr-x 3 oracle oinstall 4096 Feb 6 04:42 upgrade
-rw-r--r-- 1 oracle oinstall 2 Feb 6 04:42 checksBuffer.tmp
-rw-r--r-- 1 oracle oinstall 9181 Feb 6 04:42 preupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 8875 Feb 6 04:42 postupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 8120 Feb 6 04:42 preupgrade.log

 Run the preupgrade_fixup.sql

[oracle@test ]$ export ORACLE_SID=orcl

[oracle@test ]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

[oracle@test ]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@test ]$ cd /u01/preupgrade

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 6 04:56:54 2019

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

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

SQL> @preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2019-02-06 21:36:38

For Source Database: ORCL
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 18.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. apex_manual_upgrade NO Manual fixup recommended.
2. mv_refresh NO Informational only.
Further action is optional.
3. tablespaces_info NO Informational only.
Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

Check Timezone version :

Check if target database’s time zone version is lower than the source database time zone version.

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
18

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;

PROPERTY_NAME                VALUE
--------------------------- ----------

DST_PRIMARY_TT_VERSION       18
DST_SECONDARY_TT_VERSION     0
DST_UPGRADE_STATE            NONE

Upgrade Database from 12.1.0.2 to 18c :-

Shutdown the 12c database:

[oracle@test ~]$ sqlplus / as sysdba 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 6 04:56:54 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Copy the parameter and password file from the 12c home to the new 18c home.

[oracle@test db_1]$ cp /u01/app/oracle/product/12.1.0/db_1/dbs/spfileorcl.ora /u02/app/oracle/product/18.3.0/db_1/dbs/
[oracle@test db_1]$ cp /u01/app/oracle/product/12.1.0/db_1/dbs/orapworcl /u02/app/oracle/product/18.3.0/db_1/dbs/

Set new ORACLE_HOME for 18c location and startup database in upgrade mode :

[oracle@test ]$ export ORACLE_SID=orcl
[oracle@test ]$ export ORACLE_HOME=/u02/app/oracle/product/18.3.0/db_1/
[oracle@test ]$ export PATH=/u02/app/oracle/product/18.3.0/db_1/bin
[oracle@test db_1]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Feb 6 22:02:24 2019
Version 18.3.0.0.0

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

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2516580184 bytes
Fixed Size 8660824 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

Run catcl.pl  script:

catctl.pl is introduce in Oracle Database 12c, Parallel Upgrade Utility replaces the SQL Upgrade Utility catupgrd.sql. With Parallel Upgrade Utility, we can run upgrade scripts and processes in parallel. Using this script will increase our server CPU capacity and reduce the upgrade time.

-n is used for parallelism (new feature on oracle 12c)

You can run the dbupgrade using either of the following commands.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -l /home/oracle/ -n 4 catupgrd.sql

$ORACLE_HOME/bin/dbupgrade

[oracle@test db_1]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -l /home/oracle/ -n 4 catupgrd.sql

Argument list for [/u02/app/oracle/product/18.3.0/db_1//rdbms/admin/catctl.pl]
Run in c = 0
Do not run in C = 0
Input Directory d = /u02/app/oracle/product/18.3.0/db_1//rdbms/admin
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = /home/oracle/
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 4
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0

catctl.pl VERSION: [18.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627]

/u02/app/oracle/product/18.3.0/db_1/rdbms/admin/orahome = [/u02/app/oracle/product/18.3.0/db_1/]
/u02/app/oracle/product/18.3.0/db_1//bin/orabasehome = [/u02/app/oracle/product/18.3.0/db_1/]
catctlGetOrabase = [/u02/app/oracle/product/18.3.0/db_1/]

Analyzing file /u02/app/oracle/product/18.3.0/db_1//rdbms/admin/catupgrd.sql

Log file directory = [/home/oracle/]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/catupgrd_catcon_37104.lst]

catcon::set_log_file_base_path: catcon: See [/home/oracle/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/home/oracle/catupgrd_*.lst] files for spool files, if any

Number of Cpus = 1
Database Name = orcl
DataBase Version = 12.1.0.2.0
Parallel SQL Process Count = 4
Components in [orcl]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [EM MGW ODM RAC WK]

------------------------------------------------------
Phases [0-108] Start Time:[2019_02_06 22:05:38]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcl] Files:1 Time: 45s
*************** Catalog Core SQL ***************
Serial Phase #:1 [orcl] Files:5 Time: 33s
Restart Phase #:2 [orcl] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [orcl] Files:19 Time: 19s
Restart Phase #:4 [orcl] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [orcl] Files:7 Time: 10s
***************** Catproc Start ****************
Serial Phase #:6 [orcl] Files:1 Time: 10s
***************** Catproc Types ****************
Serial Phase #:7 [orcl] Files:2 Time: 9s
Restart Phase #:8 [orcl] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [orcl] Files:66 Time: 31s
Restart Phase #:10 [orcl] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [orcl] Files:1 Time: 53s
Restart Phase #:12 [orcl] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [orcl] Files:94 Time: 14s
Restart Phase #:14 [orcl] Files:1 Time: 0s
Parallel Phase #:15 [orcl] Files:117 Time: 26s
Restart Phase #:16 [orcl] Files:1 Time: 0s
Serial Phase #:17 [orcl] Files:17 Time: 3s
Restart Phase #:18 [orcl] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [orcl] Files:32 Time: 24s
Restart Phase #:20 [orcl] Files:1 Time: 1s
Serial Phase #:21 [orcl] Files:3 Time: 6s
Restart Phase #:22 [orcl] Files:1 Time: 0s
Parallel Phase #:23 [orcl] Files:24 Time: 137s
Restart Phase #:24 [orcl] Files:1 Time: 0s
Parallel Phase #:25 [orcl] Files:12 Time: 79s
Restart Phase #:26 [orcl] Files:1 Time: 0s
Serial Phase #:27 [orcl] Files:1 Time: 0s
Serial Phase #:28 [orcl] Files:3 Time: 3s
Serial Phase #:29 [orcl] Files:1 Time: 0s
Restart Phase #:30 [orcl] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [orcl] Files:1 Time: 0s
Restart Phase #:32 [orcl] Files:1 Time: 1s
Serial Phase #:34 [orcl] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [orcl] Files:288 Time: 19s
Serial Phase #:36 [orcl] Files:1 Time: 0s
Restart Phase #:37 [orcl] Files:1 Time: 1s
Serial Phase #:38 [orcl] Files:2 Time: 3s
Restart Phase #:39 [orcl] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [orcl] Files:3 Time: 45s
Restart Phase #:41 [orcl] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [orcl] Files:13 Time: 91s
Restart Phase #:43 [orcl] Files:1 Time: 0s
Parallel Phase #:44 [orcl] Files:11 Time: 13s
Restart Phase #:45 [orcl] Files:1 Time: 0s
Parallel Phase #:46 [orcl] Files:3 Time: 1s
Restart Phase #:47 [orcl] Files:1 Time: 1s
************* Final Catproc scripts ************
Serial Phase #:48 [orcl] Files:1 Time: 3s
Restart Phase #:49 [orcl] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [orcl] Files:1 Time: 19s
************ Upgrade Component Start ***********
Serial Phase #:51 [orcl] Files:1 Time: 0s
Restart Phase #:52 [orcl] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [orcl] Files:2 Time: 439s
***************** Upgrading XDB ****************
Restart Phase #:54 [orcl] Files:1 Time: 0s
Serial Phase #:56 [orcl] Files:3 Time: 8s
Serial Phase #:57 [orcl] Files:3 Time: 4s
Parallel Phase #:58 [orcl] Files:9 Time: 3s
Parallel Phase #:59 [orcl] Files:25 Time: 4s
Serial Phase #:60 [orcl] Files:4 Time: 5s
Serial Phase #:61 [orcl] Files:1 Time: 0s
Serial Phase #:62 [orcl] Files:31 Time: 3s
Serial Phase #:63 [orcl] Files:1 Time: 0s
Parallel Phase #:64 [orcl] Files:6 Time: 3s
Serial Phase #:65 [orcl] Files:2 Time: 13s
Serial Phase #:66 [orcl] Files:3 Time: 31s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [orcl] Files:1 Time: 1s
Serial Phase #:69 [orcl] Files:1 Time: 0s
Parallel Phase #:70 [orcl] Files:2 Time: 44s
Serial Phase #:71 [orcl] Files:1 Time: 44s
Restart Phase #:72 [orcl] Files:1 Time: 0s
Parallel Phase #:73 [orcl] Files:2 Time: 8s
Serial Phase #:74 [orcl] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:75 [orcl] Files:1 Time: 0s
Serial Phase #:77 [orcl] Files:1 Time: 29s
Serial Phase #:78 [orcl] Files:1 Time: 1s
Restart Phase #:79 [orcl] Files:1 Time: 0s
Serial Phase #:80 [orcl] Files:1 Time: 15s
Restart Phase #:81 [orcl] Files:1 Time: 0s
Parallel Phase #:82 [orcl] Files:3 Time: 53s
Restart Phase #:83 [orcl] Files:1 Time: 0s
Serial Phase #:84 [orcl] Files:1 Time: 3s
Restart Phase #:85 [orcl] Files:1 Time: 0s
Serial Phase #:86 [orcl] Files:1 Time: 5s
Restart Phase #:87 [orcl] Files:1 Time: 1s
Parallel Phase #:88 [orcl] Files:4 Time: 60s
Restart Phase #:89 [orcl] Files:1 Time: 0s
Serial Phase #:90 [orcl] Files:1 Time: 0s
Restart Phase #:91 [orcl] Files:1 Time: 1s
Serial Phase #:92 [orcl] Files:2 Time: 5s
Restart Phase #:93 [orcl] Files:1 Time: 0s
Serial Phase #:94 [orcl] Files:1 Time: 1s
Restart Phase #:95 [orcl] Files:1 Time: 0s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:96 [orcl] Files:1 Time: 11s
Restart Phase #:97 [orcl] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:98 [orcl] Files:1 Time: 1s
************* Final Upgrade scripts ************
Serial Phase #:99 [orcl] Files:1 Time: 189s
******************* Migration ******************
Serial Phase #:100 [orcl] Files:1 Time: 38s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:101 [orcl] Files:1 Time: 0s
Serial Phase #:102 [orcl] Files:1 Time: 0s
Serial Phase #:103 [orcl] Files:1 Time: 68s
***************** Post Upgrade *****************
Serial Phase #:104 [orcl] Files:1 Time: 13s
**************** Summary report ****************
Serial Phase #:105 [orcl] Files:1 Time: 1s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:106 [orcl] Files:1 Time: 0s
Serial Phase #:107 [orcl] Files:1 Time: 0s
Serial Phase #:108 [orcl] Files:1 Time: 28s

------------------------------------------------------
Phases [0-108] End Time:[2019_02_06 22:36:13]
------------------------------------------------------

Grand Total Time: 1836s

LOG FILES: (/home/oracle//catupgrd*.log)

Upgrade Summary Report Located in:
/home/oracle//upg_summary.log

Grand Total Upgrade Time: [0d:0h:30m:36s]

Execute Post-Upgrade Status Tool, utlu122s.sql.

[oracle@test ]$ cd $ORACLE_HOME/rdbms/admin

SQL> @utlu122s.sql

Oracle Database Release 18 Post-Upgrade Status Tool 02-06-2019 23:16:2

Component Current Full Elapsed Time
Name Status Version HH:MM:SS

Oracle Server UPGRADED 18.3.0.0.0 00:11:38
JServer JAVA Virtual Machine VALID 18.3.0.0.0 00:04:25
Oracle XDK UPGRADED 18.3.0.0.0 00:00:59
Oracle Database Java Packages UPGRADED 18.3.0.0.0 00:00:10
OLAP Analytic Workspace UPGRADED 18.3.0.0.0 00:00:14
Oracle Label Security UPGRADED 18.3.0.0.0 00:00:06
Oracle Database Vault UPGRADED 18.3.0.0.0 00:00:16
Oracle Text UPGRADED 18.3.0.0.0 00:00:33
Oracle Workspace Manager UPGRADED 18.3.0.0.0 00:00:31
Oracle Real Application Clusters UPGRADED 18.3.0.0.0 00:00:00
Oracle XML Database UPGRADED 18.3.0.0.0 00:01:13
Oracle Multimedia UPGRADED 18.3.0.0.0 00:01:36
Spatial UPGRADED 18.3.0.0.0 00:02:52
Oracle OLAP API UPGRADED 18.3.0.0.0 00:00:10
Upgrade Datapatch 00:01:10
Final Actions 00:03:46
Post Upgrade 00:00:13
Post Upgrade Datapatch 00:00:15

Total Upgrade Time: 00:29:10

Database time zone version is 18. It is older than current release time
zone version 31. Time zone upgrade is needed using the DBMS_DST package.

Summary Report File = /home/oracle//upg_summary.log
SQL> @catuppst.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/catuppst.sql /main/60 2017/08/03 17:44:03 wesmith Exp $
SQL> Rem
SQL> Rem catuppst.sql
SQL> Rem
SQL> Rem Copyright (c) 2006, 2017, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem        catuppst.sql - CATalog UPgrade PoST-upgrade actions
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem        This post-upgrade script performs remaining upgrade actions that
SQL> Rem        do not require that the database be open in UPGRADE mode.
SQL> Rem        Automatically apply the latest PSU.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem        You must be connected AS SYSDBA to run this script.
SQL> Rem
SQL> Rem    BEGIN SQL_FILE_METADATA
SQL> Rem    SQL_SOURCE_FILE: rdbms/admin/catuppst.sql
SQL> Rem    SQL_SHIPPED_FILE: rdbms/admin/catuppst.sql
SQL> Rem    SQL_PHASE: UPGRADE
SQL> Rem    SQL_STARTUP_MODE: UPGRADE
SQL> Rem    SQL_IGNORABLE_ERRORS: NONE
SQL> Rem    SQL_CALLING_FILE: rdbms/admin/catupgrd.sql
SQL> Rem    END SQL_FILE_METADATA
SQL> Rem

Execute utlrp.sql script to compile  invalid objects.

SQL> @utlrp.sql

Session altered.

TIMESTAMP
-----------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2019-02-06 23:24:56

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP
-----------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2019-02-06 23:31:50

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
0

Set COMPATIBLE parameter :

SQL> show parameter compatible;

NAME              TYPE    VALUE
----------------- -------- ---------
compatible        string  12.1.0.2.0
noncdb_compatible boolean FALSE


SQL> ALTER SYSTEM SET COMPATIBLE = '18.0.0' SCOPE=SPFILE;

System altered.

Restart the database :

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2516580184 bytes
Fixed Size 8660824 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.
SQL> show parameter compatible;

NAME              TYPE     VALUE
----------------- -------- ---------
compatible        string   18.0.0
noncdb_compatible boolean  FALSE

Compile Fixed objects stats :

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Upgrade Time Zone version :

Startup database in Upgrade mode.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2516580184 bytes
Fixed Size 8660824 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

Start the upgrade window

SQL> SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;

DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/SQL> 2 3 4 5 6 7 8 9
l_tz_version=31
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

Shutdown the database and startup in normal mode.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.

Total System Global Area 2516580184 bytes
Fixed Size 8660824 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

Upgrade Timezone version :

SQL> SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/SQL> 2 3 4 5 6 7 8 9
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES2"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

Check time zone file version :

SQL> SELECT * FROM v$timezone_file;

FILENAME             VERSION    CON_ID
-------------------- ---------- -------
timezlrg_31.dat      31         0


SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        18

If they differ after an upgrade then updating registry$database can be done by

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

1 row updated.

SQL> commit;

Commit complete.

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
31

Reference: Steps to Upgrade Time Zone File and Timestamp with Time Zone Data

Post-upgrade checks:

Run “postupgrade_fixups.sql” to check post upgrade status.

SQL> @/u01/preupgrade/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2019-02-06 21:36:48

For Source Database: ORCL
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 18.0.0.0.0

Preup                          Preupgrade
Action                         Issue Is
Number Preupgrade Check Name    Remedied   Further DBA Action
------ ------------------------ ---------- ------------------
4. depend_usr_tables            YES        None.
5. old_time_zones_exist         YES        None.
6. post_dictionary              YES        None.
7. post_fixed_objects           NO         Informational only.Further action is optional.

The fixup scripts have been run and resovled what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

Session altered.

Check Database version and status.

SQL> select name,version,open_mode from v$database,v$instance;

NAME      VERSION           OPEN_MODE
--------- ----------------- --------------------
ORCL      18.0.0.0.0        READ WRITE

Reference : Oracle DB 18c – Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 18c (Doc ID 2418045.1)

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                      FB Group:https://www.facebook.com/groups/894402327369506/             FB Page: https://www.facebook.com/dbahariprasath/?               Twitter: https://twitter.com/hariprasathdba

 

 

Duplicate a Database Using RMAN in Oracle Database 12c Release 1

Cloning Oracle 12c Database Using Active database Duplication :-

To create a Physical Standby database using RMAN DUPLICATE FROM ACTIVE DATABASE feature which is now available in 11g Release 1 onwards. This enables us to create a physical standby database without having to take a backup of the primary database as a prerequisite step.

Steps for creating database using Active database Duplication :-

  1. Change the archive log mode :

If it is noarchivelog mode, switch  to archivelog mode.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT
SQL> alter database archivelog

Database altered.

SQL> alter database open

Database altered.

SQL> archive log list
Database log mode                  Archive Mode
Automatic archival                 Enabled
Archive destination                +DG01
Oldest online log sequence         299300
Next log sequence to archive       299305
Current log sequence               299305

2. Enable force logging mode:

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

3. Initialization Parameters :

Check the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to “prod” on the primary database.

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value.

SQL> show parameter db_name;

NAME     TYPE   VALUE
-------- -----  ------
db_name  string prod

SQL> show parameter db_unique;

NAME           TYPE   VALUE
-------------- -----  ------
db_unique_name string prod
SQL> alter system set log_archive_config='DG_CONFIG=(prod,clone)' SCOPE=both sid='*';

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/prod/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod' SCOPE=both sid='*';

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=clone LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=clone' SCOPE=both sid='*';
SQL> alter system set fal_server=prod SCOPE=both sid='*';

System altered.

SQL> alter system set fal_client=clone SCOPE=both sid='*';

System altered.

SQL> alter system set standby_file_management=auto SCOPE=both sid='*';

System altered.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive scope=spfile;

System altered.

4. Listener Configuration in Target database :

[oracle@devserver ]$ export ORACLE_SID=clone
[oracle@devserver ]$ export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
[oracle@devserver admin]$ cd $ORACLE_HOME/network/admin
[oracle@devserver admin]$ cat listener.ora

# listener.ora Network Configuration File: /oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

      (SID_NAME = clone )

    )

  )

LISTENER_CLONE =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devserver.localdomain.com)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

5. TNS Service Name Registration in Target database :

CLONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = devserver.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = CLONE)
)
)

6. Create respective directories in Target Server:

[oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/ctrl
[oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/data
[oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/logs
[oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/arch
[oracle@devserver admin]$ mkdir /oracle/app/oracle/admin/clone/adump

7. Start listener in Target side:

[oracle@proddr01 admin] $lsnrctl start LISTENER_CLONE

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JAN-2019 14:05:49

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

Starting listener to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=devserver.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_CLONE
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 03-DEC-2018 14:09:08
Uptime 55 days 23 hr. 56 min. 40 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/devserver/listener_clone/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devserver.localdomain.com)(PORT=1521)))
Services Summary...
Service "clone" has 1 instance(s).
Instance "clone", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

8. Copying password & parameter file to standby server:

  • After copying pfile,only keep the parameter entry in PFILE:

db_name

[oracle@devserver ]$ cd $ORACLE_HOME/dbs
[oracle@devserver dbs]$ scp initprod.ora orapwprod
oracle@devserver:/oracle/app/oracle/product/12.1.0/dbhome_1/dbs oracle@devserver's password: 
initprod.ora  100% 1536     1.5KB/s   00:00
orapwprod     100% 1536     1.5KB/s   00:00                                 
[oracle@proddr01 dbs]$ mv orapwprod orapwclone

[oracle@proddr01 dbs]$ cat initprod.ora
db_name='clone'

9. Check connectivity between primary and standby side :

[oracle@devserver ]$ tnsping prod  [In boths the nodes]

[oracle@devserver ]$ tnsping clone [In boths the nodes]

10. Clone Database Creation :-

Startup in nomount stage :

[oracle@proddr01 ]$ export ORACLE_SID=clone
[oracle@proddr01 ]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 29 01:12:25 2019

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes

11. Connect RMAN to create target  database,

[oracle@proddr01 ]$ rman target sys/****@prod auxiliary sys/****@clone
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jan 30 20:15:10 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: PROD (DBID=1459429229)
connected to auxiliary database: PROD (not mounted)

RMAN> run
{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'prod','proddr'
set db_file_name_convert='+DG01/prod/datafile','/oradata1/clone/data' 
set db_unique_name='clone'
set cluster_database='false'
set log_file_name_convert='+DG01/prod/onlinelog','/oradata1/clone/logs' 
set control_files='/oracle/app/oracle/oradata/clone/ctrl/control.ctl'
set fal_client='clone'
set fal_server='prod'
set audit_file_dest='/oracle/app/oracle/admin/proddr/adump'
set log_archive_config='dg_config=(clone,prod)'
set log_archive_dest_1='location=location=/oradata1/prod/arch'
set log_archive_dest_2='service=prod async valid_for=(online_logfiles,primary_role) db_unique_name=prod'
set sga_target='50GB'
set sga_max_size='50GB'
set undo_tablespace='UNDOTBS1'
nofilenamecheck;
}

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=42 device type=DISK
 
allocated channel: prmy2
channel prmy2: SID=36 device type=DISK
 
allocated channel: prmy3 
channel prmy3 : SID=45 device type=DISK

allocated channel: prmy4 
channel prmy4 : SID=45 device type=DISK
 
allocated channel: stby
channel stby: SID=20 device type=DISK
 
Starting Duplicate Db at 30-JAN-19
.
.
.
.
.
Finished Duplicate Db at 30-JAN-19
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN>

12. Disable Archivelog mode in Target database :

SQL> shut immediate

SQL> startup mount

SQL> alter database noarchivelog

Database altered.
SQL> alter database open

Database altered.

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/              FB Page: https://www.facebook.com/dbahariprasath/?               Twitter: https://twitter.com/hariprasathdba

Understanding The Oracle Sysaux Tablespace

 

The SYSAUX tablespace stores data for auxiliary applications such as the Log Miner, Work space Manager, Oracle Data Mining, Oracle Streams, and many other Oracle tools.

  • This tablespace is automatically created if you use the DataBase Creation Assistant(DBCA) software to build an Oracle database.
  • Like the SYSTEM tablespace, SYSAUX requires a higher level of security and it cannot be dropped or renamed.
  • Do not allow user objects to be stored in SYSAUX. This tablespace should only store system specific objects.
  • This is a permanent tablespace.
RECOVERY:

If you have backup of sysaux and following all archives then you can recover sysaux tablespace.

RMAN> restore tablespace sysaux;

RMAN> recover tablespace sysaux;

SQL> alter tablespace sysaux online;

If you say you lost sysaux file and you don’t have backup and all archive files, then you don’t have option of recreating sysaux tablespace

Solution for this scenario:

you may have to use CTAS or export backup of this database and rebuild complete database.

you may not be able to deal with physical backup.

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Understanding Oracle System Tablespace

From Oracle 10g, the smallest Oracle database is two tablespaces. This applies to Oracle 11g.

  • SYSTEM – stores the data dictionary.
  • SYSAUX – stores data for auxiliary applications (sysaux page link).

In reality, a typical production database has numerous tablespaces. These include SYSTEM and NON-SYSTEM tablespaces.

SYSTEM – a tablespace that is always used to store SYSTEM data that includes data about tables, indexes, sequences, and other objects – this metadata comprises the data dictionary.

  • Every Oracle database has to have a SYSTEM tablespace—it is the first tablespace created when a database is created.
  • Accessing it requires a higher level of privilege.
  • You cannot rename or drop a SYSTEM.
  • You cannot take a SYSTEM tablespace offline.
  • The SYSTEM tablespace could store user data, but this is not normally done—a good rule to follow is to never allow allow the storage of user segments in the SYSTEM
  • This tablespace always has a SYSTEM Undo segment.

It Contains,

  • Data Dictionary Information
  • system rollback segment
  • sys,system objects
  • should not contain user data

When creating a table it is customary to specify a “tablespace <tablespace name>” clause. If you didn’t mention the “tablespace” , Oracle will create the table in the user’s default tablespace. To change the default tablespace for a user you can issue this command:

alter user <username> default tablespace users;

This will keep new tables from getting into the system tablespace. To move a table from the system tablespace, you can use the alter table move tablespace <tablespace name> command.

You can also use the CTAS and rename commands but you must re-add the table constraints.

RECOVERY :

When you lose the SYSTEM tablespace the instance could crash or hang: anyway the instance will alert you as soon as possible.

It’s not rare the case when you cannot even shutdown the instance and proceed with a kill or a shutdown abort command.

If you have a good backup you can  restore it, but the database could not be open until the recovery process finishes.No problem for your committed transactions if your database is running in ARCHIVE LOG mode and they will be available as soon as the database open.

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Oracle 12c Background Process-LREG (Listener Registration)

LREG (Listener Registration Process) :-

LREG (Listener Registration) is an Oracle background process created when you start an Oracle 12c or higher database instance. This function was handled by the PMON process in earlier releases.It registers the instance with the listeners.

The Listener Registration process attempts register the information about the database instance and services if the listener is running. If the listener is not running it will keep trying periodically connect to it. It may take some time till LREG makes all listener registration after you start it, if you want to make an immediate registration you must keep using “ALTER SYSTEM REGISTER;” exactly as in the older versions.

[oracle@PROD ]$ ps -ef | grep lreg

oracle 18174 1 0 2018 ? 00:04:37 asm_lreg_+ASM1
oracle 1675 1 0 Jan27 ? 00:00:02 ora_lreg_PROD

To trace the service registration to Listener,

SQL> alter system set events='immediate trace name listener_registration level 3';

System altered.

SQL> alter system register;

System altered.

SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

VALUE
----------------------------------------------
/oracle/app/oracle/diag/rdbms/PROD/PROD1/trace

[oracle@PROD trace]$ cd /app/oracle/diag/rdbms/PROD/PROD1/trace
[oracle@PROD trace]$ ls -lrt *lreg*

-rw-r----- 1 oracle oinstall  175 Sep 19 23:44 PROD_lreg_9012.trm
-rw-r----- 1 oracle oinstall 4972 Sep 19 23:44 PROD_lreg_9012.trc

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Oracle 12c-2 Node Rac To Single Instance Standby Database Setup

Steps for creating Single instance standby database from RAC primary database :-

  1. Change the archive log mode :
$ sqlplus / as sysdba

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode                  Archive Mode
Automatic archival                 Enabled
Archive destination                +DG01
Oldest online log sequence         299300
Next log sequence to archive       299305
Current log sequence               299305

2. Enable force logging mode:

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

3.  Parameter Configuration setup:

SQL> alter system set log_archive_config='DG_CONFIG=(prod,proddr)' SCOPE=both sid='*';

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/prod/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod' SCOPE=both sid='*';

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=proddr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddr' SCOPE=both sid='*';
SQL> alter system set fal_server=prod SCOPE=both sid='*';

System altered.

SQL> alter system set fal_client=proddr SCOPE=both sid='*';

System altered.

SQL> alter system set standby_file_management=auto SCOPE=both sid='*';

System altered.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive scope=spfile;

System altered.

4. Standby Listener Configuration:

[oracle@proddr01 ]$ export ORACLE_SID=prod
[oracle@proddr01 ]$ export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
[oracle@proddr01 admin]$ cd $ORACLE_HOME/network/admin
[oracle@proddr01 admin]$ cat listener.ora

# listener.ora Network Configuration File: /oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

      (SID_NAME = prod )

    )

  )

LISTENER_PRODDR=

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = proddr01)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

5. TNS Connection string Configuration :

Standby and primary tnsnames.ora entry should be available in both nodes:

[oracle@proddr01 admin]$ cd $ORACLE_HOME/network/admin
[oracle@proddr01 admin]$ cat tnsnames.ora

PROD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = prod1)(PORT = 1521))

    )

    (CONNECT_DATA =

       (SERVER = DEDICATED)

        (SID = prod1)

    )

  )

PRODDR =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = proddr01)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = prod)

    )

  )

6. Create respective directories in Standby Server:

[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/ctrl
[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/data
[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/logs
[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/arch
[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/admin/proddr/adump

7. Start Standby listener :

[oracle@proddr01 admin] $lsnrctl start LISTENER_PRODDR

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JAN-2019 14:05:49

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

Starting listener to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=proddr01.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_PRODDR
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 03-DEC-2018 14:09:08
Uptime 55 days 23 hr. 56 min. 40 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/proddr01/listener_proddr/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=proddr01)(PORT=1521)))
Services Summary...
Service "proddr" has 1 instance(s).
Instance "proddr", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

8. Copying password & parameter file to standby server:

  • After copying pfile,only keep the parameter entry in PFILE:

db_name

[oracle@proddr01 ]$ cd $ORACLE_HOME/dbs
[oracle@prod1 dbs]$ scp initprod.ora orapwprod
oracle@proddr01:/oracle/app/oracle/product/12.1.0/dbhome_1/dbs oracle@proddr01's password: 
initprod.ora  100% 1536     1.5KB/s   00:00
orapwprod     100% 1536     1.5KB/s   00:00                                 
[oracle@proddr01 dbs]$ cat initprod.ora

db_name='prod'

9. Check connectivity between primary and standby side :

[oracle@proddr01 ]$ tnsping prod   [In boths the nodes]

[oracle@proddr01 ]$ tnsping proddr    [In boths the nodes]

10. Standby Database Creation :

Startup in nomount stage :

[oracle@proddr01 ]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 29 01:12:25 2019

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes

11. Connect RMAN to create standby database,

Set cluster_database is FALSE.

[oracle@proddr01 ]$ rman target sys/****@prod auxiliary sys/****@proddr
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jan 27 16:15:10 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: PROD (DBID=1459429229)
connected to auxiliary database: PROD (not mounted)

RMAN> run
{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'prod','proddr'
set db_file_name_convert='+DG01/prod/datafile','/oradata1/proddr/data' 
set db_unique_name='proddr'
set cluster_database='false'
set log_file_name_convert='+DG01/prod/onlinelog','/oradata1/proddr/logs' 
set control_files='/oracle/app/oracle/oradata/proddr/ctrl/control.ctl'
set fal_client='proddr'
set fal_server='prod'
set audit_file_dest='/oracle/app/oracle/admin/proddr/adump'
set log_archive_config='dg_config=(proddr,prod)'
set log_archive_dest_1='location=location=/oradata1/prod/arch'
set log_archive_dest_2='service=prod async valid_for=(online_logfiles,primary_role) db_unique_name=prod'
set sga_target='50GB'
set sga_max_size='50GB'
set undo_tablespace='UNDOTBS1'
nofilenamecheck;
}

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=42 device type=DISK
 
allocated channel: prmy2
channel prmy2: SID=36 device type=DISK
 
allocated channel: prmy3 
channel prmy3 : SID=45 device type=DISK

allocated channel: prmy4 
channel prmy4 : SID=45 device type=DISK
 
allocated channel: stby
channel stby: SID=20 device type=DISK
 
Starting Duplicate Db at 28-JAN-19
.
.
.
.
.
Finished Duplicate Db at 28-JAN-19
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN>

12. Enable Recovery Manager in standby side:

[oracle@proddr01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jan 28 10:36:39 2019

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

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

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

Database altered.

13. Check Standby SYNC Verification:

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Thread     Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- -----------
1          299314                 299314                0
2          149803                 149803                0

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Purging/Cleaning Sysaux Tablespace In Oracle

Restrictions on SYSAUX tablespace

1. Using SYSAUX DATAFILE clause in the CREATE DATABASE statement you can specify only datafile attributes in SYSAUX tablespace.
2. You can not alter attributes like (PERMANENT, READ WRITE,EXTENT MANAGEMENT LOCAL,SEGMENT SPACE MANAGEMENT AUTO) with an ALTER TABLESPACE statement
3. SYSAUX tablespace cannot be dropped or renamed.

Check whats occupying SYSAUX tablespace:

SYSAUX tablespace is more or less stable so it would be smart to check what is eating the space in there. Connected as a DBA user, run the script   ${ORACLE_HOME}/rdbms/admin/utlsyxsz to get the current usage of the SYSAUX tablespace and see how it will grow when you change certain parameters for which you are asked to enter values.

Run below query to know what all occupants are there in sysaux occupying all the space.

SQL> select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants;

Once you find what are all occupying the space then we can work on it. If you have tackled with huge number of AWR reports occupying then you need to check AWR retention period.

SQL>select retention from dba_hist_wr_control;

Through this query we can get for how many days AWR reports maintaining, If that which we did not require that many days we can reduce it.

For Example : If we have more than 7 days,we can reduce it to 7 day which is 7*24*60 = 10080 minutes.the retention period is modified to 7 days (10080 minutes) and the interval between each snapshot is 60 minutes through below query.

SQL>execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080);

In case while reducing the retention period if we face an  error then check the MOVING_WINDOW_SIZE value with below query

SQL>exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7);

SQL>SELECT moving_window_size FROM dba_hist_baseline WHERE baseline_type = 'MOVING_WINDOW';

Then update it to correct value and then execute the above AWR retention query.

SQL>execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080);

Once the retention period is set you can follow below steps to cleanup the old AWR reports to free up space. Run below query to find the oldest and newest AWR snapshots.

SQL>SELECT snap_id, begin_interval_time, end_interval_time
FROM SYS.WRM$_SNAPSHOT WHERE snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT) UNION SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT WHERE
snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT);

To cleanup all AWR reports between snap_id <start snap> to <end snap> execute below query.

SQL>BEGIN
dbms_workload_repository.drop_snapshot_range(low_snap_id => <start snap>, high_snap_id=><end snap>);
END;
/

Else you can run below commands to drop the old AWR reports and rebuild the repositories. This process is very fast too.

SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql

After clearing up all the AWR reports we were able to get space from SYSAUX tablespace.

High Storage Consumption for LOBs in SYSAUX Tablespace:

A number of LOB segments created for LOB columns of AWR tables in the SYSAUX tablespace consume significant storage.

This storage is not released even after AWR snapshots are dropped and row counts in the tables reduced.

SQL> select * from dba_lobs where table_name = 'WRH$_SQLTEXT';

SQL> select * from dba_segments where segment_name = '<seg-name>';

SQL> select count(*) from WRH$_SQLTEXT;

The LOB segments can be rebuilt using the same name, storage parameters and SYSAUX tablespace by executing a MOVE operation on them.
This builds a new LOB segment containing only the data currently in the LOB and exchanges it with the original LOB segment.

To implement the solution, Execute below steps:

1. Startup the database in RESTRICT mode to prevent interference from AWR operations during the rebuild.

2. From a SQL*Plus session connected as SYSDBA perform the move operation as follows:

SQL> alter table wrh$_sqltext move lob(sql_text) store as <name of LOB> tablespace sysaux;

the AWR table is WRH$_SQLTEXT, the LOB column is SQL_TEXT and <name of LOB> is the name of the LOB segment whose storage we want to reclaim

The new LOB segment will have a new data_object_id in dba_objects and a new (header_file,header_block) pair in dba_segments. It should also have a reduced number of bytes and blocks in dba_segments.

3. Check for and rebuild any unusable index on the table after the LOB move:

SQL> select index_name from dba_indexes where table_name='WRH$_SQLTEXT' and status='UNUSABLE';

INDEX_NAME
-----------
SAMPLE

SQL> alter index SAMPLE rebuild;

Index altered.

4. Finally, restart the database in normal mode.

Other possible solutions

1) ALTER TABLE … SHRINK SPACE CASCADE on the table (assumes that ASSM and ENABLE ROW MOVEMENT) are used

  NOTE: This operation will lock the table during the shrink

2) Use DBMS_REDEFINITION to redefine the table online

  NOTE: a) This operation could require up to double the size of the existing table … if the table does not really have space to release

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Oracle 12c Database Cloning Using Hot Backup

Steps for Database Cloning using Hot Backup :-

  • Backup the parameter file

If SOURCE database is using spfile create pfile or if database is using pfile, use OS command to copy the pfile to a backup location.

  • Note down the oldest log sequence number.
  • Place the database to backup mode
  • Copy all data files of ‘Source’ database to a clone location.
  • After copying all datafiles, release the database from backup mode.
  • Switch the current log file and note down the oldest log sequence number
  • Copy all archive log files generated during FIRST old log sequence no. to the LAST old log sequence no. during which the database was in backup mode.
  • Take the control file trace backup to the trace path
  • Edit the clone database parameter file and make necessary changes to the clone
  • Startup the cloned database in NOMOUNT mode, using target pfile.
  • Create the control file for the clone database using the trace control file.
  • Create the control file by running trace file from the trace path
  • Recover the database using backup controlfile option.
  • Open the database with resetlogs option

Hot Backup :-

Hot Backups can be performed when the database is up and in Archive log mode.

  • First, check the v$database view to see if your database is set in Archive log mode:
SQL> select log_mode from v$database;

LOG_MODE
----------
ARCHIVELOG
SQL> archive log list
Database log mode          No Archive Mode
Automatic archival         Enabled
Archive destination        /u01/app/oracle/product/12.1.0/db_1/dbs/arch
Oldest online log sequence 186
Current log sequence       187
  • Switch the current archivelogs,
SQL> alter system archive log current;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archivelog
Oldest online log sequence     187
Next log sequence to archive   188
Current log sequence           188

Preparing Hot Backup for Database clone :-

  • Create parameter file
SQL> show parameter spfile;

NAME   TYPE	VALUE
------ -------- ------------------------------
spfile string   +DATA/orcl11g/spfileorcl11g.ora

SQL> create pfile='/home/oracle/initHCLONE.ora' from spfile;

File created.
  • Check number of tablespaces associated with database.
SQL> Select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
---------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
UNDOTBS2

6 rows selected.
  • Ensure the tablespaces are ready for taking hot backup.
SQL> select * from v$backup;

FILE#      STATUS             CHANGE#    TIME      CON_ID
---------- ------------------ ---------- --------- ----------
1          NOT ACTIVE           0                  0
2          NOT ACTIVE           0                  0
3          NOT ACTIVE           0                  0
4          NOT ACTIVE           0                  0
5          NOT ACTIVE           0                  0
  • Put database in hot backup mode.
SQL> alter database begin backup;

Database altered.

SQL> select * from v$backup;

FILE#      STATUS             CHANGE#    TIME      CON_ID
---------- ------------------ ---------- --------- ----------
1          ACTIVE             4282518    25-JAN-19 0
2          ACTIVE             4282518    25-JAN-19 0
3          ACTIVE             4282518    25-JAN-19 0
4          ACTIVE             4282518    25-JAN-19 0
5          ACTIVE             4282518    25-JAN-19 0
  • Create appropriate directory structure in clone database.
asmcmd> mkdir  +DATA/HCLONE/CONTROLFILE
asmcmd> mkdir  +DATA/HCLONE/DATAFILE
asmcmd> mkdir  +DATA/HCLONE/ONLINELOG

[oracle@racpb1 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/HCLONE

[oracle@racpb1 ~]$ mkdir -p /u01/app/oracle/admin/HCLONE

[oracle@racpb1 ~]$ mkdir -p /u01/app/oracle/admin/HCLONE/adump
  • Copy the tablespaces from source to target location.
ASMCMD> cd ORCL11G/DATAFILE

ASMCMD> cp SYSTEM.256.995570219 +DATA/HCLONE/DATAFILE/system.dbf
copying +DATA/ORCL11G/DATAFILE/SYSTEM.256.995570219 -> +DATA/HCLONE/DATAFILE/system.dbf

ASMCMD> cp SYSAUX.257.995570219 +DATA/HCLONE/DATAFILE/sysaux.dbf
copying +DATA/ORCL11G/DATAFILE/SYSAUX.257.995570219 -> +DATA/HCLONE/DATAFILE/sysaux.dbf

ASMCMD> cp UNDOTBS1.258.995570219 +DATA/HCLONE/DATAFILE/undotbs1.dbf
copying +DATA/ORCL11G/DATAFILE/UNDOTBS1.258.995570219 -> +DATA/HCLONE/DATAFILE/undotbs1.dbf

ASMCMD> cp UNDOTBS2.264.995570411 +DATA/HCLONE/DATAFILE/undotbs2.dbf
copying +DATA/ORCL11G/DATAFILE/UNDOTBS2.264.995570411 -> +DATA/HCLONE/DATAFILE/undotbs2.dbf

ASMCMD> cp USERS.259.995570219 +DATA/HCLONE/DATAFILE/users.dbf
copying +DATA/ORCL11G/DATAFILE/USERS.259.995570219 -> +DATA/HCLONE/DATAFILE/users.dbf
  • After copying datafiles to backup location,release database from Hot backup mode.
SQL> alter database end backup;

Database altered.

SQL> select * from v$backup;

FILE#      STATUS     CHANGE# TIME      CON_ID
---------- ---------- ------- --------- ------
1          NOT ACTIVE 4282518 25-JAN-19 0
2          NOT ACTIVE 4282518 25-JAN-19 0
3          NOT ACTIVE 4282518 25-JAN-19 0
4          NOT ACTIVE 4282518 25-JAN-19 0
5          NOT ACTIVE 4282518 25-JAN-19 0
  • Switch the current log sequence and note down the oldest log sequence number.
SQL> alter system archive log current;

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 188
Next log sequence to archive 189
Current log sequence 189
  • Backup the controlfile as trace.
SQL> alter database backup controlfile to trace as '/home/oracle/control.sql';

Database altered.
  • Copy all archive log files generated during FIRST old log sequence no. to the LAST old log sequence no. during which the database was in backup mode.
[oracle@racpb1 archivelog]$ ls -lrt
total 6636

-rw-r----- 1 oracle oinstall 4622848 Jan 25 00:39 1_187_995570300.dbf
-rw-r----- 1 oracle oinstall 2160128 Jan 25 01:19 1_188_995570300.dbf

Restoration using hot backup :-

  • Edit the clone database parameter file and make necessary changes to the HCLONE database.
HCLONE.__db_cache_size=436207616
HCLONE.__java_pool_size=4194304
HCLONE.__large_pool_size=8388608
HCLONE.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
HCLONE.__pga_aggregate_target=373293056
HCLONE.__sga_target=700448768
HCLONE.__shared_io_pool_size=0
HCLONE.__shared_pool_size=243269632
HCLONE.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/HCLONE/adump'
*.audit_trail='DB'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/HCLONE/controlfile/current.260.995570299'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='localdomain.com'
*.db_name='HCLONE'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=HCLONEXDB)'
*.event=''
*.log_archive_dest_1='LOCATION=/u01/archivelog'
*.memory_target=1073741824
*.open_cursors=300
*.processes=300
*.remote_listener='racsn:1521'
*.remote_login_passwordfile='EXCLUSIVE'
HCLONE.undo_tablespace='UNDOTBS'
  • Startup database in NOMOUNT stage using target PFILE.
[oracle@racpb1 ~]$ export ORACLE_SID=HCLONE
[oracle@racpb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 25 01:40:01 2019

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

Connected to an idle instance.

SQL> startup pfile='/home/oracle/initHCLONE.ora' nomount;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 675283048 bytes
Database Buffers 390070272 bytes
Redo Buffers 5455872 bytes
  • Create the control file trace for the clone database using the trace control file and specify the appropriate paths for redolog and datafiles

Note: Change the “REUSE” parameter to “SET” and RESETLOGS option.

CREATE CONTROLFILE SET DATABASE "HCLONE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/HCLONE/onlinelog/group_1'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '+DATA/HCLONE/onlinelog/group_2'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '+DATA/HCLONE/onlinelog/group_3'  SIZE 50M BLOCKSIZE 512,
  GROUP 4 '+DATA/HCLONE/onlinelog/group_4'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/HCLONE/datafile/system.dbf',
  '+DATA/HCLONE/datafile/sysaux.dbf',
  '+DATA/HCLONE/datafile/undotbs1.dbf',
  '+DATA/HCLONE/datafile/users.dbf',
  '+DATA/HCLONE/datafile/undotbs2.dbf'
CHARACTER SET AL32UTF8
;

Control file created.
SQL> select status from v$instance;

STATUS
--------
MOUNTED
  • Recover the database using backup controlfile option.
SQL> recover database using backup controlfile until cancel;

ORA-00279: change 4283965 generated at 01/25/2019 01:19:16 needed for thread 1
ORA-00289: suggestion : /u01/archivelog/1_189_995570300.dbf
ORA-00280: change 4283965 for thread 1 is in sequence #189


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
  • Open database using resetlogs option :
SQL> alter database open resetlogs;

Database altered.
  • Check the database name and status :
SQL >select database_name, open_mode from v$database;

DATABASE_NAME OPEN_MODE
------------- ------------
HCLONE        READ WRITE
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Cloning Oracle 12c Database Using Rman Backup Based Duplication

PRODUCTION    : MACHINE DB31
CLONE                  : MACHINE DB32

MACHINE DB32 CLONE:
——————

1) Create Directory CLONE
————————-

[oracle@db32 ~]$ cd /u01

[oracle@db32 u01]$ ls

[oracle@db32 u01]$ mkdir clone

 

2) Create Pfile for Clone:
————————–

[oracle@db32 ~]$ cd $ORACLE_HOME/dbs

[oracle@db32 dbs]$ vi initclone.ora

*.db_name=CLONE
*.control_files='/u01/app/oracle/oradata/CLONE/control01.ctl','/u01/app/oracle/flash_recovery_area/clone/control02.ctl'
*.db_recovery_file_dest_size=4g
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/CLONE/'
*.log_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/CLONE/'

3) Create password file Clone:
——————————

[oracle@db32 dbs]$ orapwd file=orapwclone password=manager force=y

4) create directory which is mentioned in your pfile:
—————————————————–

/u01/app/oracle/flash_recovery_area/clone (create clone directory)
MACHINE DB32 CLONE:
——————
5) create listener and tns by NETCA Clone Side:
———————————————-
i) Listener Name: list_col
ii) Tns Name: toclone

6) register created listener by NETMGR
————————————–

MACHINE DB31 PRODUCTION:
————————
7) create listener and tns by NETCA
i) Listner Name: list
ii) Tns Name: toprod

8) register created listener by NETMGR

MACHINE DB31 PRODUCTION:
————————
9) copy paste listener and tns for each other : prod & clone

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

[oracle@db31 admin]$ vi listener.ora
[oracle@db31 admin]$ vi tnsnames.ora

MACHINE DB32 CLONE:
——————
10) copy paste listener and tns for each other : prod & clone

cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin

[oracle@db32 admin]$ vi listener.ora
[oracle@db32 admin]$ vi tnsnames.ora

11) Check Connectivity:

ping tns (tnspingtoclone)
(tnspingtoprod)

rman target sys/manager@toclone
rman target sys/manager@toprod
MACHINE DB31 PRODUCTION:
———————–
12) Check connectivity:
ping tns (tnspingtoclone)
(tnspingtoprod)

Rman>rman target sys/manager@toclone
Rman>rman target sys/manager@toprod

13) Enable archive log:
———————–

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15

14) Connect RMAN :
—————–

[oracle@db31 ~]$ rman target/

RMAN> show all;

RMAN> configure controlfile autobackup on;

15) take database backup through RMAN :
—————————————

RMAN> backup database include current controlfile plus archivelog;

Starting backup at 23-JAN-19
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=1 STAMP=952712839
channel ORA_DISK_1: starting piece 1 at 23-JAN-19
channel ORA_DISK_1: finished piece 1 at 23-JAN-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2019_01_21/o1_mf_annnn_TAG20170822T182720_dsrbkjxj_.bkp tag=TAG20170822T182720 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-JAN-19

Starting backup at 23-JAN-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prod/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prod/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prod/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-JAN-19
channel ORA_DISK_1: finished piece 1 at 23-JAN-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2019_01_21/o1_mf_nnndf_TAG20170822T182722_dsrbkzmj_.bkp tag=TAG20170822T182722 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:08:28
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 23-JAN-19
channel ORA_DISK_1: finished piece 1 at 23-JAN-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2019_01_21/o1_mf_ncnnf_TAG20170822T182722_dsrc1mfn_.bkp tag=TAG20170822T182722 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-JAN-19

Starting backup at 23-JAN-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=2 STAMP=952713357
channel ORA_DISK_1: starting piece 1 at 23-JAN-19
channel ORA_DISK_1: finished piece 1 at 23-JAN-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2019_01_21/o1_mf_annnn_TAG20170822T183557_dsrc1p1c_.bkp tag=TAG20170822T183557 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-JAN-19

Starting Control File and SPFILE Autobackup at 23-JAN-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD/autobackup/2019_01_21/o1_mf_s_952713359_dsrc1qyr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 23-JAN-19

16) Go flash_recover_area
————————–

[oracle@db31 ~]$ cd /u01/app/oracle/flash_recovery_area/

[oracle@db31 flash_recovery_area]$ scp -r PROD oracle@192.168.139.32:/u01/app/oracle/flash_recovery_area/
oracle@192.168.139.32's password:
o1_mf_annnn_TAG20170822T183557_dsrc1p1c_.bkp 100% 143KB 142.5KB/s 00:00
o1_mf_ncnnf_TAG20170822T182722_dsrc1mfn_.bkp 100% 9568KB 9.3MB/s 00:01
o1_mf_nnndf_TAG20170822T182722_dsrbkzmj_.bkp 100% 1060MB 2.6MB/s 06:42
o1_mf_annnn_TAG20170822T182720_dsrbkjxj_.bkp 100% 4222KB 4.1MB/s 00:01
o1_mf_1_5_dsrc1o50_.arc 100% 141KB 141.0KB/s 00:00
o1_mf_1_4_dsrbkhmp_.arc 100% 4220KB 602.9KB/s 00:07
o1_mf_s_952713359_dsrc1qyr_.bkp 100% 9600KB 4.7MB/s 00:02

MACHINE DB32 CLONE:
[oracle@db32 ~]$ export ORACLE_SID=clone

[oracle@db32 ~]$ sqlplus / as sysdba

SQL> startup nomount

RMAN> rman target sys/manager@toprod auxiliary sys/manager@toclone

connected to target database: PROD (DBID=364534697)
connected to auxiliary database: CLONE (not mounted)

RMAN> duplicate target database to 'CLONE' Nofilenamecheck;

Starting Duplicate Db at 23-JAN-19
using channel ORA_AUX_DISK_1

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''CLONE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''CLONE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''CLONE'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''CLONE'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     631914496 bytes

Fixed Size                     1338364 bytes
Variable Size                377488388 bytes
Database Buffers             247463936 bytes
Redo Buffers                   5623808 bytes

Starting restore at 23-JAN-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/CLONE/autobackup/2013_01_27/o1_mf_s_805812962_8j9onctk_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/CLONE/autobackup/2013_01_27/o1_mf_s_805812962_8j9onctk_.bkp tag=TAG20130127T125602
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/CLONE/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/CLONE/control02.ctl
Finished restore at 23-JAN-19

database mounted

contents of Memory Script:
{
   set until scn  763666;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/CLONE/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/CLONE/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/CLONE/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/CLONE/users01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 23-JAN-19
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/CLONE/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/CLONE/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/CLONE/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/CLONE/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/CLONE/backupset/2013_01_27/o1_mf_nnndf_TAG20130127T125432_8j9okjyl_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/CLONE/backupset/2013_01_27/o1_mf_nnndf_TAG20130127T125432_8j9okjyl_.bkp tag=TAG20130127T125432
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:22
Finished restore at 23-JAN-19

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=805814774 file name=/u01/app/oracle/oradata/CLONE/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=805814774 file name=/u01/app/oracle/oradata/CLONE/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=805814774 file name=/u01/app/oracle/oradata/CLONE/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=805814774 file name=/u01/app/oracle/oradata/CLONE/users01.dbf

contents of Memory Script:
{
   set until scn  763666;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 23-JAN-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/CLONE/archivelog/2013_01_27/o1_mf_1_4_8j9on8j1_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/CLONE/archivelog/2013_01_27/o1_mf_1_4_8j9on8j1_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-JAN-19

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''CLONE'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     631914496 bytes

Fixed Size                     1338364 bytes
Variable Size                377488388 bytes
Database Buffers             247463936 bytes
Redo Buffers                   5623808 bytes

sql statement: alter system set  db_name =  ''CLONE'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     631914496 bytes

Fixed Size                     1338364 bytes
Variable Size                377488388 bytes
Database Buffers             247463936 bytes
Redo Buffers                   5623808 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1  SIZE 50 M ,
  GROUP  2  SIZE 50 M ,
  GROUP  3  SIZE 50 M
 DATAFILE
  '/u01/app/oracle/oradata/CLONE/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/CLONE/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/CLONE/sysaux01.dbf",
 "/u01/app/oracle/oradata/CLONE/undotbs01.dbf",
 "/u01/app/oracle/oradata/CLONE/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/CLONE/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/CLONE/sysaux01.dbf RECID=1 STAMP=805814812
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/CLONE/undotbs01.dbf RECID=2 STAMP=805814812
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/CLONE/users01.dbf RECID=3 STAMP=805814812

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=805814812 file name=/u01/app/oracle/oradata/CLONE/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=805814812 file name=/u01/app/oracle/oradata/CLONE/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=805814812 file name=/u01/app/oracle/oradata/CLONE/users01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 23-JAN-19

RMAN performed the following steps automatically to duplicate the database :

  • Allocates automatic auxiliary channel
  • Creates a controlfile for the clone database
  • Performs an incomplete recovery of the clone database using incremental backups and archived redo log files up to the last backed up archived redo log file.
  • Shutdowns the database and opens it using RESETLOGS option.
  • Generates a new unique DBID for the clone database.

All file locations were the same as in the production database. However, in case we need to change clone database’s directory structure.

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Oracle 12c Background Process-MMAN (Memory Manager)

 

MMAN dynamically adjust the sizes of the SGA components like buffer cache, large pool, shared pool and java pool and serves as SGA memory broker.

It is a new process added to Oracle 10g as part of automatic shared memory management.

It used for internal database tasks such as Automatic Shared Memory Management (ASMM)

ASMM

Automatic Shared Memory Management, or ASMM is what you do when setting SGA_TARGET and not setting MEMORY_TARGET. Basically, you define the size of the SGA you want to allocate at startup and that will be available for the instance, most of it being buffer cache and shared pool.

SGA_TARGET and SGA_MAX_SIZE because on the most common platforms, all is allocated at instance startup. Then, in addition to this shared area used by all instance processes, each processes can allocate private memory, and you control this with PGA_AGGREGATE_TARGET.

The total size of SGA and PGA for all instances in a system must reside in physical memory for the simple reason that they are mostly used to avoid I/O (a large buffer cache avoids physical reads and optimizes physical writes, a large PGA avoids reads and writes to temp files).

MMAN background process manages the dynamic resizing of SGA memory components depending on load for database.

Responsible for managing instance memory based on the workloads.

Uses the the metrics collected to determine the ideal distribution of memory within oracle. It constantly monitors the database and adjusts the memory allocations according to workloads.

 

 

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba