Step by Step Configuring A Oracle 13c Enterprise Manager Cloud Control

Description:

In this article, let us perform the OEM 13.2 Installation on Linux 7 64-bit.

Download the following software’s by clicking the below link

Main steps:-

  • 12c Database Installation (Software-Only)
  • Repository Database Creation Using Template
  • Install Cloud Control 13c software

12c Database Installation (Software-Only):

For this installation you will need 12.1.0.2 for the repository database, so use the installation instructions from one of the following articles,

Kindly refer Oracle Database 12c(12.1.0.2) Installation to install 12c binaries.

Login as oracle user and add the following entry in .bash_profile

vi /home/oracle/.bash_profile

emrepdb()
{
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
export ORACLE_HOME
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_SID=emrepdb
export ORACLE_SID
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:.
export LD_LIBRARY_PATH
LIBPATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:/usr/lib:/lib
export LIBPATH
TNS_ADMIN=${ORACLE_HOME}/network/admin
export TNS_ADMIN
PATH=$ORACLE_HOME/bin:$PATH:.
export PATH
}

Repository Database Creation Using Template:

To create the repository database,check all the prerequisites has to be done

Database version 12.1.0.2 Enterprise Edition.

Character set AL32UTF8.

Step 1:- Unzip the repository template under the ORACLE_HOME.

[oracle@oem13c ~]$ cd /u01/app/oracle/product/12.1.0.2/db_1/assistants/dbca/templates/

[oracle@oem13c templates]$ unzip /mnt/hgfs/OracleSoftwares/OEM\ 13c/12.1.0.2.0_Database_Template_for_EM13_2_0_0_0_Linux_x64.zip

Database Creation :

Step 2:- Start the Database Configuration Assistant (DBCA) and create a new database using the template.

$ dbca

Step 3:- Select the “Create Database” option and click the “Next” button.

Step 4:- Select the “Advanced Mode” option and click the “Next” button.

Step 5:- Select appropriate template for your EM installation Small (my choice), Medium or Large then click “Next” button.

Step 6:- Enter the Global Database Name and SID, then click the “Next” button.

Step 7:- Both options “Configure Enterprise Manager (EM) Database Express” and “Register with Enterprise Manager (EM) Cloud Control” must be unchecked. Click “Next” button.

Step 8:- Enter the SYS and system password.Click the “Next” button.

Step 9:- Enter the listener details and click the the “Next” button.

Step 10:- Specify the location where to install new database. Then click “Next” button.

Step 11:- If we you need to change the memory settings and character sets.But I am accepting the memory defaults and character as “AL32UTF8” option.

Step 12:- Click the “Next” button to create the database.

Step 13:- Revise the database configuration before it will be created. Click “Finish” button to start installation.

Step 14:- Database creation is complete, click the “Close” button.

Install Cloud Control 13c software :

$ mkdir -p /u01/app/oracle/middleware
$ mkdir -p /u01/app/oracle/agent

Start installation

./em13200_linux64.bin

Step 15:- If you don’t want to get email after installation just uncheck “I wish to receive security updates via My Oracle Support” and click “Next” button.

Step 16:- Prerequisite checks are executed. I got two warnings for two cases but I ignored them

  • “memory”- EM installation requires at least 10GB.
  • “ip_local_port_range” – my range is wider 9000 – 65500  so I ignored it

Step 17:- Select “Simple” installation method then click “Next” button.

Step 18:- Enter middleware, agent location and click “Next” button.

Step 19:- Enter administrator password for EM and database credentials. Click “Next” button.

Step 20:- Uncheck “Configure a Shared Location for Oracle BI Publisher” then click “Next” button and Oracle software library is checked.

Step 21:- Review the summary screen and Click Install button

Step 22:- After installation,run root.sh script. Once done click “OK” button.

Step 23:- Note down the URL address of Final summary screen  for Cloud Control 13C

Accept license and agreement

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
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

Reinstating a Failed Oracle Rac Primary Database using Dataguard Broker

In the previous article, we have performed the Oracle RAC to RAC physical standby database failover steps in 11gR2. Now, let us continue with the reinstate process of the failed primary RAC database.

Below is the environment details:-Note:-

As per the current status, the primary site ORCL_PRODRAC1 is failed and physical standby site ORCL_PRODRAC2 already converted as primary by failover process. So, now ORCL_PRODRAC2 is primary and ORCL_PRODRAC1 is failed primary which should be reinstated.

Steps involved in reinstate of failed primary RAC database:-

A) Check Database Configuration
B) Performing Reinstate
C) Verify the Reinstate of new physical standby database

A) Check Database Configuration:-

Configuration check in Cluster:-

Failed Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

DG Broker configuration:-

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC2
DGMGRL> show configuration verbose;

Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC2 - Primary database
ORCL_PRODRAC1 - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

B) Performing Reinstate:-

During the reinstate using dataguard broker, the failed primary site will be flashbacked and converted as physical standby database, then the media recovery process will be started automatically started. These can be view in the alert log of the database which is to be reinstated.

DGMGRL> reinstate database 'ORCL_PRODRAC1';

Reinstating database "ORCL_PRODRAC1", please wait...
Operation requires shutdown of instance "ORCL1" on database "ORCL_PRODRAC1"
Shutting down instance "ORCL1"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ORCL1" on database "ORCL_PRODRAC1"
Starting instance "ORCL1"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ORCL_PRODRAC1" ...
Operation requires shutdown of instance "ORCL1" on database "ORCL_PRODRAC1"
Shutting down instance "ORCL1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ORCL1" on database "ORCL_PRODRAC1"
Starting instance "ORCL1"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ORCL_PRODRAC1" ...
Reinstatement of database "ORCL_PRODRAC1" succeeded

Attached the output of alert log of instance ORCL1 of database  ORCL_PRODRAC1.

C) Verify the Reinstate of new physical standby database:-

Configuration check in Cluster:-

Failed Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Verify in DG Broker configuration:-

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC1
DGMGRL> show configuration verbose;

Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC2 - Primary database
ORCL_PRODRAC1 - Physical standby database

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

From the above we can notice that the failed primary is now converted as physical standby database. If needed we can switchback it to primary. Refer here for the switchover process.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
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

All In One Datapump Parameters Up To Oracle 18c (A-Z)

Description:-

In this article we are covering all the datapump parameters upto 18c database

Oracle 10g Release 1

ATTACH https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-attach-parameter/

CONTENT https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-content-parameter-2/

DIRECTORY https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-directory-parameter/ 
    
DUMPFILE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-dumpfile-parameter/          
        
ESTIMATE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-estimate-parameter/

ESTIMATE_ONLY  http://www.oracledbwr.com/18c-datapump/oracle-18c-datap…e_only-parameter/


EXCLUDE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-exclude-parameter/


FLASHBACK_SCN https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-flashback_scn-parameter/  
 
FLASHBACK_TIME https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-flashback_time-parameter/  
 
FULL https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-database-level/

HELP https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-help-parameter/

INCLUDE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-include-parameter/ 
JOB_NAME https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-job_name-parameter/

NETWORK_LINK https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-network-link/
NOLOGFILE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-nologfile-parameter/

PARALLEL https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-parallel-parameter/

PARFILE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-parfile-parameter/

QUERY https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-query-parameter/

REMAP_DATAFILE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-remap_datafile-parameter/

REMAP_SCHEMA https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-remap_schema-parameter/ 

REMAP_TABLESPACE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-remap_tablespace-parameter/         
            
REUSE_DUMPFILES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-reuse_dumpfiles-parameter/

SCHEMAS https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-schema-level/

SKIP_UNUSABLE_INDEXES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-skip_unusable_indexes-parameter/

SQLFILE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-sqlfile-parameter/

STATUS https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-status-parameter/

 
TABLE_EXISTS_ACTION  https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-table_exists_action-parameter/


TABLES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-table-level/

TABLESPACES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-tablespace-level/

TRANSPORT_DATAFILES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-transport_datafiles-parameter/

TRANSPORT_TABLESPACES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-transport_tablespaces-parameter/

VERSION https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-version-parameter/

CONTINUE_CLIENT https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-continue_client-parameter/

EXIT_CLIENT https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-exit_client-parameter/

HELP https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-help-parameter/

KILL_JOB https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-kill_job-parameter/

PARALLEL https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-parallel-parameter/

START_JOB https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-start_job-parameter/

STATUS https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-status-parameter/

STOP_JOB https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-stop_job-parameter/

ADD_FILE https://www.oracledbwr.com/18c-database/oracle-18c-datapump-add_file-parameter/

REMAP_SCHEMA https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-remap_schema-parameter/

Oracle 10g Release 2

ENCRYPTION_PASSWORD https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-encryption_password-parameter/


FILESIZE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-filesize-parameter/

SAMPLE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-sample-parameter/

COMPRESSION https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-compression-parameter/

Oracle 11g Release 1


PARTITION_OPTIONS https://www.oracledbwr.com/18c-datapump/oracle-18c-datap…ptions-parameter/


REMAP_DATA http://www.oracledbwr.com/18c-datapump/oracle-18c-datap…p_data-parameter/


REMAP_TABLE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-remap_table-parameter/

ENCRYPTION https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-encryption-parameter-2/

ENCRYPTION_ALGORITHM https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-encryption_algorithm-parameter/

ENCRYPTION_MODE https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-encryption_mode-parameter/

Oracle 11g Release 2

Source_edition

https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-source_edition-parameter/

Target_edition

https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-target_edition-parameter/
Oracle 12c Release 1

VIEWS_AS_TABLES https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-views_as_tables-parameter/

METRICS https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-metrics-parameter/

MASTER_ONLY http://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-master_only-parameter/


KEEP_MASTER https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-keep_master-parameter/

ENCRYPTION_PWD_PROMPT https://www.oracledbwr.com/uncategorized/oracle-18c-datapump-encryption_pwd_prompt-parameter/


DISABLE_ARCHIVE_LOGGING https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-disable_archive_logging-parameter/
LOGTIME https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-logfile-parameter/


Oracle 12c Release 2

REMAP_DIRECTORY https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-remap_directory-parameter/

TRANSPORT_DATAFILES https://www.oracledbwr.com/18c-datapump/oracle-18c-datap…afiles-parameter/

LOGTIME https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-logtime-parameter/

Oracle 18c Release 1

DATABASE LINK https://www.oracledbwr.com/18c-datapump/oracle-18c-datapump-database-link/

 

Oracle Rac Database Failover using Dataguard Broker

In the previous article, we have configured the dataguard broker and performed the switchover from Oracle 11gR2 two node RAC primary to two node physical standby. In this article, let us perform the failover process. The environment details are provided below.

Steps involved in Failover using dataguard broker:-

A) Check Database Configuration before Failover
B) Performing Failover
C) Check database configuration after Failover

A) Check Database Configuration before Failover:-

Configuration check in Cluster:-

Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Standby:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

B) Performing Failover:-

Here, connect to the standby site and perform the failover process.

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC2
DGMGRL> failover to 'ORCL_PRODRAC2';
Performing failover NOW, please wait...
Operation requires a connection to instance "ORCL1" on database "ORCL_PRODRAC2"
Connecting to instance "ORCL1"...
Connected.
Failover succeeded, new primary is "ORCL_PRODRAC2"

C) Check database configuration after Failover:-

Configuration check in Cluster:-

Failed Primary(ORCL_PRODRAC1):

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Failedover Primary (ORCL_PRODRAC2):

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

From the above configuration we can see that the Database role for both failed primary site(ORCL_PRODRAC1) and new primary site(ORCL_PRODRAC2) is ‘Primary’ since the failed primary site crashed due to some unplanned outage and that site should be reinstated using dataguard broker.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
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 Rac Database Switchover Using Dataguard Broker

Description:-

In the previous article, we have configured dataguard broker for Oracle RAC database with physical standby in 11gR2. Here, we will continue with the switchover process. Below is the environment.Steps Involved in Switchover using dataguard broker:-

A) Check Database Configuration before Switchover
B) Performing Switchover
C) Check database configuration after Switchover

A) Check Database Configuration before Switchover:-

Configuration check in Cluster:-

Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Standby:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Primary Node 1:-

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:09:45

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-AUG-2018 02:18:33
Uptime 0 days 22 hr. 51 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac101/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.211)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.214)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1_DGB" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1_DGMGRL" has 1 instance(s).
Instance "ORCL1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Primary Node 2:-

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:11:02

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-AUG-2018 02:18:45
Uptime 0 days 22 hr. 52 min. 17 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac102/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.212)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.215)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1_DGB" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1_DGMGRL" has 1 instance(s).
Instance "ORCL2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Standby Node 1:-

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:11:29

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-AUG-2018 02:18:46
Uptime 0 days 22 hr. 52 min. 43 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac201/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.221)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.224)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2_DGB" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2_DGMGRL" has 1 instance(s).
Instance "ORCL1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Standby Node 2:-

lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:12:10

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-AUG-2018 02:18:55
Uptime 0 days 22 hr. 53 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac202/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.222)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.225)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2_DGB" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2_DGMGRL" has 1 instance(s).
Instance "ORCL2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Configuration check in database level (Primary):-

$ sqlplus / as sysdba
SQL> select name, db_unique_name, database_role, switchover_status, open_mode from v$database;

NAME   DB_UNIQUE_NAME  DATABASE_ROLE  SWITCHOVER_STATUS  OPEN_MODE
------ --------------  -------------  -----------------  ---------
ORCL   ORCL_PRODRAC1   PRIMARY        TO STANDBY         READ WRITE

SQL> @stby_gap

DG Broker configuration:-

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC1
DGMGRL> show configuration verbose;


Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC1 - Primary database
ORCL_PRODRAC2 - Physical standby database

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Primary database setting:-

DGMGRL> show database verbose 'ORCL_PRODRAC1';

Database - ORCL_PRODRAC1

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORCL1
ORCL2

Properties:
DGConnectIdentifier = 'orcl_prodrac1'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value

Database Status:
SUCCESS


DGMGRL> show instance verbose 'ORCL1' on database 'ORCL_PRODRAC1';

Instance 'ORCL1' of database 'ORCL_PRODRAC1'

  Host Name: prodrac101.oracledbwr.com
  PFILE:
  Properties:
    SidName                         = 'ORCL1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.214)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC1_DGMGRL)(INSTANCE_NAME=ORCL1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '+DBWR_FRA'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'ORCL_%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

DGMGRL> show instance verbose 'ORCL2' on database 'ORCL_PRODRAC1';

Instance 'ORCL2' of database 'ORCL_PRODRAC1'

Host Name: prodrac102.oracledbwr.com
PFILE:
Properties:
SidName = 'ORCL2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.215)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC1_DGMGRL)(INSTANCE_NAME=ORCL2)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+DBWR_FRA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ORCL_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Instance Status:
SUCCESS

Standby database setting:-

DGMGRL> show database verbose 'ORCL_PRODRAC2';

Database - ORCL_PRODRAC2

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 187.00 KByte/s
Real Time Query: ON
Instance(s):
ORCL1
ORCL2

Properties:
DGConnectIdentifier = 'orcl_prodrac2'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value

Database Status:
SUCCESS

DGMGRL> show instance verbose 'ORCL1' on database 'ORCL_PRODRAC2';

Instance 'ORCL1' of database 'ORCL_PRODRAC2'

Host Name: prodrac201.oracledbwr.com
PFILE:
Properties:
SidName = 'ORCL1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.224)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC2_DGMGRL)(INSTANCE_NAME=ORCL1)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+DBWR_FRA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ORCL_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Instance Status:
SUCCESS

DGMGRL> show instance verbose 'ORCL2' on database 'ORCL_PRODRAC2';

Instance 'ORCL2' of database 'ORCL_PRODRAC2'

Host Name: prodrac202.oracledbwr.com
PFILE:
Properties:
SidName = 'ORCL2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.225)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC2_DGMGRL)(INSTANCE_NAME=ORCL2)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+DBWR_FRA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ORCL_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Instance Status:
SUCCESS

B) Performing Switchover:-

It is ready for the switchover. Note that we do not need to shutdown any instances

DGMGRL> switchover to 'ORCL_PRODRAC2';
Performing switchover NOW, please wait...
Operation requires a connection to instance "ORCL1" on database "ORCL_PRODRAC2"
Connecting to instance "ORCL1"...
Connected.
New primary database "ORCL_PRODRAC2" is opening...
Operation requires startup of instance "ORCL1" on database "ORCL_PRODRAC1"
Starting instance "ORCL1"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "ORCL_PRODRAC2"

C) Check database configuration after Switchover:-

Old Primary:-

$ srvctl status database -d ORCL_PRODRAC1 -v
Instance ORCL1 is running on node prodrac101. Instance status: Open.
Instance ORCL2 is running on node prodrac102. Instance status: Mounted (Closed).

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:-

$ srvctl status database -d ORCL_PRODRAC2 -v
Instance ORCL1 is running on node prodrac201. Instance status: Open.
Instance ORCL2 is running on node prodrac202. Instance status: Open.

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

In the above configuration we can see that database role for both the databases has been changed automatically in cluster, whereas in the manual switchover this has to be done manually. Also, we can notice that the “Start options” is open and read only in old primary and new primary respectively. It is recommended to change this in the cluster manually.

Old Primary:-

$ srvctl modify database -d ORCL_PRODRAC1 -s 'READ ONLY'
$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:-

$ srvctl modify database -d ORCL_PRODRAC2 -s 'OPEN'
$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Let us verify in the database for archive gap.

$ sqlplus / as sysdba

SQL> @stby_gap

We can also verify the log shipping from the new primary to old primary by switch log in new primary and tail the alert log in old primary.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
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 18c Datapump-TARGET_EDITION Parameter

  • TARGET_EDITION parameter used in import datapump process.
  •  You should remove the objects from the target edition before importing the dump.Importing the dumpfile / views to ORA$BASE edition.
  • Drop the target edition present under HARI user and import TARGET_ EDITION.
SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
----------------------------------------------
ORA$BASE
SQL> conn hari/oracle;
Connected.

SQL> ALTER SESSION SET EDITION =ORA$BASE;

Session altered.

SQL> drop view ed_emp_view_ORABASE;

View dropped.

Import  the dumpfile / views to ORA$BASE edition.

[oracle@18c empdata]$ impdp dumpfile=edition.dmp directory=TEST_DIR target_edition=ORA\$BASE

Import: Release 18.0.0.0.0 - Production on Sat Aug 25 02:38:44 2018
Version 18.3.0.0.0

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

Username: system
Password:

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=edition.dmp directory=TEST_DIR target_edition=ORA$BASE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat Aug 25 02:38:51 2018 elapsed 0 00:00:03

After the import you can verify the import after setting corresponding edition.

SQL> conn hari/oracle;
Connected.
SQL> ALTER SESSION SET EDITION =ORA$BASE;

Session altered.

SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
---------------------------------------------
ORA$BASE

SQL> desc ed_emp_view_ORABASE;

Name  Null?  Type
---- ----- ------
ID         NUMBER
SAL        NUMBER

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
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 18c Datapump-SOURCE_EDITION Parameter

  • SOURCE_EDITION parameter used in export datapump process.
  • Oracle provides the provision for different versions of same object under different editions. Suppose if you wanted to backup and restore these versions into across different editions using these options.
  • During the application development you can create multiple versions of objects for different releases. So in case if you wanted to migrate code from one version to different version within database or across database you can use this option.
  • The objects are limited to some set of objects which is not having the storage.

EDITIONABLE AND NONEDITIONABLE SCHEMA OBJECT TYPES :

These schema objects types are editionable:

  • SYNONYM
  • VIEW
  • All PL/SQL object types:
    • FUNCTION
    • LIBRARY
    • PACKAGE and PACKAGE BODY
    • PROCEDURE
    • TRIGGER
    • TYPE and TYPE BODY
  • All other schema object types are noneditionable. Table is an example of an noneditionable type.
  • A schema object of an editionable type is editioned if its owner is editions-enabled; otherwise, it is potentially editioned.
  • A schema object of a noneditionable type is always noneditioned, even if its owner is editions-enabled. A table is an example of an noneditioned object.

Grant access to HARI user to create edition

SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to HARI;

Grant succeeded.

Enabling edition for a user HARI

SQL> select USERNAME,EDITIONS_ENABLED from dba_users where USERNAME='HARI';

USERNAME      E
-----------  ---
HARI          N


SQL> alter user HARI enable editions;

User altered.

SQL> grant create any view to HARI;

Grant succeeded.

SQL> select USERNAME,EDITIONS_ENABLED from dba_users where USERNAME='HARI';

USERNAME         E
-----------     ---
HARI             Y

Check the current & new edition for HARI

SQL> conn hari/oracle;
Connected.


SQL> create edition NEW_EDITION;

Edition created.


SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
-----------------------------------------------
ORA$BASE

SQL> create editioning view ED_EMP_VIEW_ORABASE as select ID,SAL from emp_data;

View created.

SQL> desc ed_emp_view_ORABASE;
Name   Null?     Type
----- ----   ------
ID                     NUMBER
SAL                  NUMBER


SQL> alter session set edition=NEW_EDITION;

Session altered.

SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
-----------------------------------------------
NEW_EDITION

Take the export view

[oracle@18c empdata]$ expdp dumpfile=edition.dmp schemas=HARI include=view directory=TEST_DIR source_edition=NEW_EDITION

Export: Release 18.0.0.0.0 - Production on Sat Aug 25 02:20:35 2018
Version 18.3.0.0.0

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

Username: hari
Password:

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "HARI"."SYS_EXPORT_SCHEMA_05": hari/******** dumpfile=edition.dmp schemas=HARI include=view directory=TEST_DIR source_edition=NEW_EDITION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Master table "HARI"."SYS_EXPORT_SCHEMA_05" successfully loaded/unloaded
******************************************************************************
Dump file set for HARI.SYS_EXPORT_SCHEMA_05 is:
/u01/empdata/edition.dmp
Job "HARI"."SYS_EXPORT_SCHEMA_05" successfully completed at Sat Aug 25 02:20:52 2018 elapsed 0 00:00:12

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
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 18c Datapump-REMAP_DATA Parameter

REMAP_DATA :-

This is a common requirement for DBA to export data from production for various purposes. In a restricted or production environment it’s a must to mask a particular data while exporting from production.

Syntax :- REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function

schema — the schema containing the table to be remapped. By default, this is the schema of the user doing the export.
tablename — the table whose column will be remapped.
column_name — the column whose data is to be remapped. The maximum number of columns that can be remapped for a single table is 10.
schema — the schema containing the PL/SQL package you have created that contains the remapping function. As a default, this is the schema of the user doing the export.
pkg — the name of the PL/SQL package you have created that contains the remapping function.
function — the name of the function within the PL/SQL that will be called to remap the column table in each row of the specified table.

Create a package in source database

create or replace package body datapump_remap_emp
as
function idcard(id varchar2) return varchar2 is
begin
return translate(id,
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*()_+-=\/ ',
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
end;
end;
/ 4 5
Package created.

SQL> SQL> 2 3 4 5 6 7 8 9 10

Package body created.

REMAP_DATA :

[oracle@18c ~]$ expdp system/oracle directory=DATA_PUMP_DIR dumpfile=emp_prod18c.dmp logfile=emp_prod18c.log REMAP_DATA=hari.emp.id:sys.datapump_remap_emp.idcard

Export: Release 18.0.0.0.0 - Production on Tue Aug 21 02:09:18 2018
Version 18.3.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** directory=DATA_PUMP_DIR dumpfile=emp_prod18c.dmp logfile=emp_prod18c.log REMAP_DATA=hari.emp.id:sys.datapump_remap_emp.idcard
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SYSTEM"."SYS_EXPORT_SCHEMA_01" 263.0 KB 1456 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
/u01/app/oracle/admin/prod18c/dpdump/emp_prod18c.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at Tue Aug 21 02:09:58 2018 elapsed 0 00:00:40

In impdp process,emp table column ID  data is encrypted in target database.Import with REMAP_DATA parameter option,the column ID data  of emp table is imported with decrypt original data.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
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 18c Datapump-PARTITION_OPTIONS Parameter

The PARTITION_OPTIONS parameter determines how partitions will be handled during export and import operations. The syntax is shown below.

Syntax:

PARTITION_OPTIONS={none | departition | merge}

NONE: The partitions are created exactly as they were on the system the export was taken from.
DEPARTITION: Each partition and sub-partition is created as a separate table, named using a combination of the table and (sub-)partition name.
MERGE: This option will import all partition data into single table.
The NONE and MERGE options are not possible if the export was done using the TRANSPORTABLE parameter with a partition or subpartition filter. If there are any grants on objects being departitioned, an error message is generated and the objects are not loaded.

Partition Table creation:

SQL> create table employee (emp_id number,sal INT NOT NULL) PARTITION BY RANGE (sal)( PARTITION employee_e1 VALUES LESS THAN (001),PARTITION employee_e2 VALUES LESS THAN (002));

Table created.
SQL> SELECT partition_name FROM user_tab_partitions WHERE table_name = 'EMPLOYEE';

PARTITION_NAME
----------------
EMPLOYEE_E1
EMPLOYEE_E2
[oracle@18c empdata]$ expdp hari/oracle dumpfile=employee.dmp directory=test_dir tables=employee

Export: Release 18.0.0.0.0 - Production on Fri Aug 24 23:32:41 2018
Version 18.3.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "HARI"."SYS_EXPORT_TABLE_01": hari/******** dumpfile=employee.dmp directory=test_dir tables=employee
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HARI"."EMPLOYEE":"EMPLOYEE_E1" 0 KB 0 rows
. . exported "HARI"."EMPLOYEE":"EMPLOYEE_E2" 0 KB 0 rows
Master table "HARI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
**********************************************************
Dump file set for HARI.SYS_EXPORT_TABLE_01 is:
/u01/empdata/employee.dmp
Job "HARI"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 24 23:33:00 2018 elapsed 0 00:00:18

DEPARTITION :-

Drop EMPLOYEE table, 

SQL> conn hari/oracle;
Connected.
SQL> drop table employee purge;

Table dropped.

 

[oracle@18c empdata]$ impdp hari/oracle dumpfile=employee.dmp directory=test_dir partition_options=DEPARTITION

Import: Release 18.0.0.0.0 - Production on Fri Aug 24 23:48:09 2018
Version 18.3.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "HARI"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HARI"."SYS_IMPORT_FULL_01": hari/******** dumpfile=employee.dmp directory=test_dir partition_options=DEPARTITION
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HARI"."EMPLOYEE_EMPLOYEE_E1" 0 KB 0 rows
. . imported "HARI"."EMPLOYEE_EMPLOYEE_E2" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "HARI"."SYS_IMPORT_FULL_01" successfully completed at Fri Aug 24 23:48:12 2018 elapsed 0 00:00:03
SQL> select * from tab where tname like 'EMP%';

TNAME                TABTYPE       CLUSTERID
-------------------- ------------- ----------
EMPLOYEE               TABLE
EMPLOYEE_EMPLOYEE_E1   TABLE
EMPLOYEE_EMPLOYEE_E2   TABLE

SQL> select partition_name from user_tab_partitions where table_name='EMPLOYEE';

no rows selected

MERGE :-

[oracle@18c empdata]$ impdp hari/oracle dumpfile=employee.dmp directory=test_dir partition_options=MERGE

Import: Release 18.0.0.0.0 - Production on Sat Aug 25 00:36:31 2018
Version 18.3.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "HARI"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HARI"."SYS_IMPORT_FULL_01": hari/******** dumpfile=employee.dmp directory=test_dir partition_options=MERGE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HARI"."EMPLOYEE":"EMPLOYEE_E1" 0 KB 0 rows
. . imported "HARI"."EMPLOYEE":"EMPLOYEE_E2" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "HARI"."SYS_IMPORT_FULL_01" successfully completed at Sat Aug 25 00:36:35 2018 elapsed 0 00:00:02
SQL> select * from tab where tname like 'EMP%';

TNAME                TABTYPE       CLUSTERID
-------------------- ------------- ----------
EMPLOYEE               TABLE
EMPLOYEE_EMPLOYEE_E1   TABLE
EMPLOYEE_EMPLOYEE_E2   TABLE

SQL> select partition_name from user_tab_partitions where table_name='EMPLOYEE';

no rows selected

SQL> select table_name , partitioned from dba_tables where table_name='EMPLOYEE';

TABLE_NAME           PAR
-------------------- ---
EMPLOYEE             NO

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
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