Database Experts

GOLD Image Creation IN OEM

2025-05-05T19:36:00.000+05:30

🛠️ GOLD Image Creation

Creating a standardized GOLD image for Oracle Grid Infrastructure and Database binaries is essential for maintaining consistency, simplifying patching, and ensuring compliance across production environments. This guide walks you through the process of setting up and generating a GOLD image using Oracle Enterprise Manager (OEM) on the GCP production server (OEM Server).

✅ Prerequisites

🖥️ Environment Setup

Login Server: OEM Server
Source Database Server: Test DB Server
Oracle Version: 19.22
Target Requirement: Database must be patched with version 19.22 before proceeding.

🔐 Credential Creation

In the OEM Console, create the following credentials:

Normal Credential
- Name: V2V_ORACLE_VM_LOGIN
- User: oracle
Privilege Credential
- Name: GLOBAL_SUDO_TO_ROOT_VIA_ORACLE
- User: oracle with sudo privileges to root

📸 Screenshot Placeholder:
🖼️ OEM Console → Setup → Security → Named Credentials
👉 Show creation screen of both credentials

📁 Step 1: Prepare Directories on Source DB Server

On the source server geetldbpl01, execute:

bash
CopyEdit
mkdir -p /ora01/app/dispatch
mkdir -p /ora01/app/stage

📸 Screenshot Placeholder:
🖼️ Terminal session showing directory creation

🧾 Step 2: Create Parameter and Input Files

🔧 Create a Working Directory

bash
CopyEdit
mkdir FPP
cd FPP

✍️ Create Parameter Files

bash
CopyEdit
vi grid1922_image.txt
vi DB1922_image.txt
chmod 755 *.txt

📸 Screenshot Placeholder:
🖼️ Terminal window showing ls -lrt of FPP directory after file creation

✍️ Create Input Deployment Files

bash
CopyEdit
vi input_deploy_grid1922.txt
vi input_deploy_db1922.txt
chmod 755 *.txt

📸 Screenshot Placeholder:
🖼️ Final file listing with permissions shown via ls -lrt

📄 Step 3: Parameter File Contents

`grid1922_image.txt`

ini
CopyEdit
IMAGE_NAME=GRID922
IMAGE_DESCRIPTION=GRID PSU
REF_TARGET_NAME=OraHome1_4_Test DB Server_8731
IMAGE_SWLIB_LOC=FPPGIMAGE/GRID_1922
STORAGE_TYPE_FOR_SWLIB=OmsShared
STORAGE_NAME_FOR_SWLIB=default_loc
REF_GI_CREDENTIALS=V2V_ORACLE_VM_LOGIN:SYSMAN
REF_HOST_ROOT_CREDENTIALS=GLOBAL_SUDO_TO_ROOT_VIA_ORACLE:SYSMAN
WORKING_DIRECTORY=/ora01/app/stage
VERSION_NAME=19.22

`DB1922_image.txt`

ini
CopyEdit
IMAGE_NAME=DB1922
IMAGE_DESCRIPTION=DB PSU
REF_TARGET_NAME=OraHome2_5_Test DB Server_6713
IMAGE_SWLIB_LOC=FPPGIMAGE/DB_922
STORAGE_TYPE_FOR_SWLIB=OmsShared
STORAGE_NAME_FOR_SWLIB=default_loc
REF_HOST_CREDENTIALS=V2V_ORACLE_VM_LOGIN:SYSMAN
REF_HOST_ROOT_CREDENTIALS=GLOBAL_SUDO_TO_ROOT_VIA_ORACLE:SYSMAN
WORKING_DIRECTORY=/ora01/app/stage
VERSION_NAME=19.22

📸 Screenshot Placeholder:
🖼️ Terminal output showing contents using cat grid1922_image.txt and cat DB1922_image.txt

🖥️ Step 4: OEM Console – Create FPP Image Directory

Login to OEM Console.
Navigate to Enterprise → Provisioning and Patching → Software Library.
Create a new directory:
- Path: FPPGIMAGE
- Structure: Subdirectories for GRID_1922 and DB_922

📸 Screenshot Placeholder:
🖼️ OEM Console view showing creation of directories in Software Library

🚀 Step 5: Initiate GOLD Image Creation

Use the OEM’s image creation workflow.
Select the parameter files grid1922_image.txt and DB1922_image.txt.
Choose the respective credentials.
Confirm all validations and begin the creation process.

📸 Screenshot Placeholder:
🖼️ OEM screen during image creation wizard showing parameter file inputs

🧩 Summary Checklist

Task	Description	Status
Patch source DB to 19.22	Ensure DB is updated	✅
Create required directories	On source server	✅
Create credentials in OEM	`V2V_ORACLE_VM_LOGIN`, `GLOBAL_SUDO_TO_ROOT_VIA_ORACLE`	✅
Build parameter/input files	Use provided formats	✅
Setup Software Library	Create `FPPGIMAGE` in OEM	✅
Launch Image Creation	From OEM Console	✅

Oracle Fleet Maintenance Patching – Detailed Implementation Plan

2025-05-05T19:29:00.000+05:30

Oracle Fleet Maintenance Patching – Detailed Implementation Plan

Overview

This document provides a comprehensive procedure for Oracle Fleet Maintenance patching to 19.24.0.0.0 across RAC clusters. It covers precheck, implementation, post-check, rollback, and testing plans.

1. Pre-Change Checks

Includes all the pre-implementation validations and checks to ensure the environment is ready for patching. This phase helps identify any potential conflicts or configuration mismatches.

2. Implementation Plan

Step-by-step instructions to perform the actual patch deployment using Oracle Enterprise Manager (OEM). This section also includes command references for all tasks and validations.

3. Post Implementation Verification

Validations to ensure the patch has been applied correctly and all Oracle services are functioning from the new Oracle homes.

4. Backout (Rollback) Plan

This section defines the rollback procedure if the patch fails or needs to be reverted due to any unforeseen issues.

5. Test Plan

After patching, this phase involves validation commands to confirm the correct patch level and functionality of ASM and database services.

Detailed Procedural Steps

Refer to the following step-by-step commands and instructions copied from the implementation draft.

Note: All outputs indicated as <COPY O/P HERE> must be filled post-execution by the administrator.

[Insert complete raw text here. For delivery, this content should be copied and formatted directly into this section as necessary.]

Expanded Procedural Content

Precheck Sample Commands

. oraenv
+ASM*
sudo -u oracle $ORACLE_HOME/OPatch/opatch lspatches > DB_patch_lspatches.log

sudo -u oracle $ORACLE_HOME/OPatch/opatch lsinventory > DB_patch_lsinv.log

Imperva Agent Check

ps -ef | grep -i imperva
<IMPERVA_HOME>/bin/rainit status
<IMPERVA_HOME>/bin/rainit stop

OGG Process Check

ps -ef | grep dirprm

OEM EMCLI Image Subscription

sudo -u oracle ./emcli db_software_maintenance -getTargetSubscriptions -target_name="<cluster_name>" -target_type="cluster"

Grid and DB Image Deployment

sudo -u oracle ./emcli db_software_maintenance -performOperation -name="deployGIsoftware" -purpose=DEPLOY_GI_SOFTWARE -target_type="cluster" -target_list="<cluster_name>" -normal_credential="V2V_ORACLE_VM_LOGIN:SYSMAN" -privilege_credential="GLOBAL_SUDO_TO_ROOT_VIA_ORACLE:SYSMAN" -input_file="data:/oracle_software/FPP/input_deploy_RAC_grid1924.txt"

sudo -u oracle ./emcli db_software_maintenance -performOperation -name="deployRACDBsoftware" -purpose=DEPLOY_RAC_SOFTWARE -target_type="rac_database" -target_list="<DB_NAME1>" -normal_credential="V2V_ORACLE_VM_LOGIN:SYSMAN" -privilege_credential="GLOBAL_SUDO_TO_ROOT_VIA_ORACLE:SYSMAN" -input_file="data:/oracle_software/FPP/input_deploy_RAC_db1924.txt"

Post-Check Commands

. oraenv
+ASM*
sudo -u oracle $ORACLE_HOME/OPatch/opatch lspatches

. oraenv
<DBNAME>
sudo -u oracle sqlplus / as sysdba

SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
action, status, description, patch_id
FROM sys.dba_registry_sqlpatch
ORDER BY action_time;

Database Growth report

2024-12-17T14:53:00.003+05:30

Use this query to find future database growth projection.

--Output

Database Create Database Used Used Free Free Previous Previous Current Current Previous Previous Current Current

Name Time Size Space in % Space in % Week Week in Month Month in % Month Month in % Year Year in % Year % Year in %

---------- ---------- ---------- ------- ------- ------- ------- ------- ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

set longchunksize 500;
set pagesize 1000;
set lines 2000;
set linesize 500;
Col database_name format a10 heading 'Database|Name'
Col create_time format a10 heading 'Create|Time'
Col database_size format a10 heading 'Database|Size'
Col used_space format a7 heading 'Used|Space'
Col used_in format a7 heading 'Used|in %'
Col free_space format a7 heading 'Free|Space'
Col free_in format a7 heading 'Free|in %'
Col growth_week format a7 heading 'Week'
Col growth_week_in format a7 heading 'Week in %'
Col growth_current_month format a10 heading 'Current|Month'
Col growth_current_month_in format a10 heading 'Current|Month in %'
Col growth_previous_month format a10 heading 'Previous|Month'
Col growth_previous_month_in format a10 heading 'Previous|Month in %'
COl growth_previous_year format a10 heading 'Previous|Year'
Col growth_previous_year_in format a10 heading 'Previous|Year in %'
Col growth_current_year format a10 heading 'Current|Year %'
Col growth_current_year_in format a10 heading 'Current|Year in %'
SELECT *
FROM   (SELECT (SELECT name
                FROM   v$database) Database_Name,
               (SELECT Min(creation_time)
                FROM   v$datafile) Create_Time,
               Round(( SUM(USED.bytes) / 1024 / 1024 / 1024 ), 2)
               || ' GB'            Database_Size,
               Round(( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - Round(
                     FREE.p / 1024 / 1024 / 1024), 2)
               || ' GB'            Used_Space,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) -
                       ( FREE.p / 1024 / 1024 / 1024 ) ) /
                           Round(SUM(USED.bytes) / 1024 / 1024 / 1024, 2) * 100,
               2)
               || '% GB'           Used_in,
               Round(( FREE.p / 1024 / 1024 / 1024 ), 2)
               || ' GB'            Free_Space,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - ( (
                             SUM(USED.bytes) / 1024 / 1024 / 1024 ) - Round(
                             FREE.p / 1024 / 1024 / 1024)
                                                                          ) ) /
                     Round(
                           SUM(USED.bytes) / 1024 / 1024 / 1024, 2) * 100, 2)
               || '% GB'           Free_in,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) -
                       ( FREE.p / 1024 / 1024 / 1024 ) ) /
                           (SELECT SYSDATE - Min(creation_time)
                           FROM   v$datafile) * 7, 2)
               || ' GB'            Growth_WEEK,
               Round(( ( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - (
                         FREE.p / 1024 / 1024 / 1024 ) )
                             /
                               (SELECT SYSDATE - Min(creation_time)
                               FROM   v$datafile) / Round((
                       SUM(USED.bytes) / 1024 / 1024 / 1024 ), 2
                                                    ) * 100 ) * 7, 3)
               || '% GB'           Growth_WEEK_in,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) -
                       ( FREE.p / 1024 / 1024 / 1024 ) ) /
                           (SELECT SYSDATE - Min(creation_time)
                           FROM   v$datafile) * (SELECT
                     To_char(Add_months(SYSDATE, -1), 'dd')
                                                 FROM   dual), 2)
               || ' GB'            Growth_Previous_Month,
               Round(( ( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - (
                         FREE.p / 1024 / 1024 / 1024 ) )
                             /
                               (SELECT SYSDATE - Min(creation_time)
                               FROM   v$datafile) / Round((
                       SUM(USED.bytes) / 1024 / 1024 / 1024 ), 2
                                                    ) * 100 ) * (SELECT
                     To_char(
                     Add_months(SYSDATE, -1)
                     ,
                     'dd')
                                                                 FROM   dual), 3
               )
               || '% GB'           Growth_Previous_Month_in,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) -
                       ( FREE.p / 1024 / 1024 / 1024 ) ) /
                           (SELECT SYSDATE - Min(creation_time)
                           FROM   v$datafile) * (SELECT To_char(SYSDATE, 'dd')
                                                 FROM   dual), 2)
               || ' GB'            Growth_Current_Month,
               Round(( ( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - (
                         FREE.p / 1024 / 1024 / 1024 ) )
                             /
                               (SELECT SYSDATE - Min(creation_time)
                               FROM   v$datafile) / Round((
                       SUM(USED.bytes) / 1024 / 1024 / 1024 ), 2
                                                    ) * 100 ) * (SELECT
                     To_char(SYSDATE, 'dd')
                                                                 FROM   dual), 3
               )
               || '% GB'           Growth_Current_Month_in,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) -
                       ( FREE.p / 1024 / 1024 / 1024 ) ) /
                           (SELECT SYSDATE - Min(creation_time)
                           FROM   v$datafile) * (SELECT Trunc(SYSDATE, 'YYYY') -
                                                        Add_months(Trunc(SYSDATE
                                                                   ,
                                                                   'YYYY'), -12)
                                                 FROM   dual), 2)
               || ' GB'            Growth_Previous_Year,
               Round(( ( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - (
                         FREE.p / 1024 / 1024 / 1024 ) )
                             /
                               (SELECT SYSDATE - Min(creation_time)
                               FROM   v$datafile) / Round((
                       SUM(USED.bytes) / 1024 / 1024 / 1024 ), 2
                                                    ) * 100 ) * (SELECT
                     Trunc(SYSDATE, 'YYYY') - Add_months(Trunc(SYSDATE, 'YYYY'),
                                              -12)
                                                                 FROM   dual), 3
               )
               || '% GB'           Growth_Previous_Year_in,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) -
                       ( FREE.p / 1024 / 1024 / 1024 ) ) /
                           (SELECT SYSDATE - Min(creation_time)
                           FROM   v$datafile) * (SELECT Add_months(Trunc(SYSDATE
                                                                   ,
                                                                   'YYYY'), 12)
                                                        - Trunc(SYSDATE, 'YYYY')
                                                 FROM   dual), 2)
               || ' GB'            Growth_Current_Year,
               Round(( ( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - (
                         FREE.p / 1024 / 1024 / 1024 ) )
                             /
                               (SELECT SYSDATE - Min(creation_time)
                               FROM   v$datafile) / Round((
                       SUM(USED.bytes) / 1024 / 1024 / 1024 ), 2
                                                    ) * 100 ) * (SELECT
                     Add_months(Trunc(SYSDATE, 'YYYY'), 12) -
                     Trunc(SYSDATE, 'YYYY')
                        FROM   dual), 3)
               || '% GB'           Growth_Current_Year_in
        FROM   (SELECT bytes
                FROM   v$datafile
                UNION ALL
                SELECT bytes
                FROM   v$tempfile
                UNION ALL
                SELECT bytes
                FROM   v$log) used,
               (SELECT SUM(bytes) AS P
                FROM   dba_free_space) free
        GROUP  BY free.p);

------Without spaces----

set longchunksize 500;

set pagesize 1000;

set lines 2000;

set linesize 500;

Col Database_Name format a10 HEADING 'Database|Name'

Col Create_Time format a10 HEADING 'Create|Time'

Col Database_Size format a10 HEADING 'Database|Size'

Col Used_Space format a7 HEADING 'Used|Space'

Col Used_in format a7 HEADING 'Used|in %'

Col Free_Space format a7 HEADING 'Free|Space'

Col Free_in format a7 HEADING 'Free|in %'

Col Growth_WEEK format a7 HEADING 'Week'

Col Growth_WEEK_in format a7 HEADING 'Week in %'

Col Growth_Current_Month format a10 HEADING 'Current|Month'

Col Growth_Current_Month_in format a10 HEADING 'Current|Month in %'

Col Growth_Previous_Month format a10 HEADING 'Previous|Month'

Col Growth_Previous_Month_in format a10 HEADING 'Previous|Month in %'

COl Growth_Previous_Year format a10 HEADING 'Previous|Year'

Col Growth_Previous_Year_in format a10 HEADING 'Previous|Year in %'

Col Growth_Current_Year format a10 HEADING 'Current|Year %'

Col Growth_Current_Year_in format a10 HEADING 'Current|Year in %'

select * from (select

(select name from v$database) Database_Name,(select min(creation_time) from v$datafile) Create_Time,

ROUND((SUM(USED.BYTES) / 1024 / 1024/1024 ),2) || ' GB' Database_Size,

ROUND((SUM(USED.BYTES) / 1024 / 1024/1024 ) - ROUND(FREE.P / 1024 / 1024/1024 ),2) || ' GB' Used_Space,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024/1024 ,2)*100,2) || '% GB' Used_in,

ROUND((FREE.P / 1024 / 1024/1024 ),2) || ' GB' Free_Space,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - ((SUM(USED.BYTES) / 1024 / 1024/1024 ) - ROUND(FREE.P / 1024 / 1024/1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024/1024,2 )*100,2) || '% GB' Free_in,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' GB' Growth_WEEK,

ROUND((((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024/1024 ),2)*100)*7,3) || '% GB' Growth_WEEK_in,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)*(select to_char(add_months(sysdate,-1),'dd') from dual),2) || ' GB' Growth_Previous_Month,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)*(select to_char(sysdate,'dd') from dual),2) || ' GB' Growth_Current_Month,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)*(select trunc(sysdate,'YYYY')-add_months(trunc(sysdate,'YYYY'),-12) from dual),2) || ' GB' Growth_Previous_Year,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)*(select add_months(trunc(sysdate,'YYYY'),12)-trunc(sysdate,'YYYY') from dual),2) || ' GB' Growth_Current_Year,

from (

SELECT BYTES FROM V$DATAFILE UNION ALL

SELECT BYTES FROM V$TEMPFILE UNION ALL

SELECT BYTES FROM V$LOG) used,(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) free group by free.p);

gDBClone Powerful Database Clone/Snapshot Management Tool (Doc ID 2099214.1)

2023-10-04T12:06:00.000+05:30

Didn't find what you are looking for?

Attachments

gDBClone-3.0.4.Solaris.sparc.pkg - 20200722 - $Revision: 3.0.4.2 $(13.12 MB)
gDBClone.el6.x86_64.rpm - 20230531 - $Revision: 3.0.5.3 $(9.72 MB)
gDBClone.el7.x86_64.rpm - 20230531 - $Revision: 3.0.5.3 $(11.16 MB)
gDBClone.el8.x86_64.rpm - 20230531 - $Revision: 3.0.5.3 $(11.57 MB)
gDBClone-Solaris.i386.pkg - 20230531 - $Revision: 3.0.5.3 $(14.6 MB)
gDBClone306.el7.x86_64.rpm - 20231003 - $Revision: 3.0.6.3 $(12.93 MB)
gDBClone306.el8.x86_64.rpm - 20231003 - $Revision: 3.0.6.3 $(13.34 MB)

Products

In this Document

Abstract

History

- gDBClone Version 3.0.6:

- gDBClone Version 3.0.5:

What's new

Details

Executive Overview

Database Provisioning Lifecycle and Challenges

Managing Test & Dev Environments Does Not Have to be Complex

Purpose of Database Duplication

Test and Dev environment

Database Clone vs Database Snapshot creation time

Supported Configurations and Features

gDBClone Clone

gDBClone Snap

gDBClone nzdru (near Zero Downtime Rolling Upgrade) (beta)

gDBClone Convert

gDBClone ListDBs & DelDB

gDBClone ListHomes

gDBClone ListSnaps & DelSnap

gDBClone SYSPwF

gDBClone Command Syntax

gDBClone Installation

gDBClone deinstallation

Managing gDBClone Privileges and Security with SUDO

Allowing Root User Access Using SUDO

SUDO Example 1: Allow a User to Perform Any gDBClone Operation

SUDO Example 2: Allow a User to Perform Only Selected gDBClone Operations

SUDO Example 3: Allow a User to Perform Any gDBClone Operation without password request

Limitations & Considerations

gDBClone to target ACFS Consideration

Recovery Manager (RMAN) considerations

Source database using Transparent Data Encryption (TDE)

Database clone/snap overwriting SGA parameters

gDBClone usage example

1. Clone a Remote/Local (physical/standby or backup) database to ACFS(Gold/Image)

2. Clone a Remote/Local database to ASM and make it a RAC database

3. Clone a Multitenant database to ACFS

4. Clone a remote database to Exascale

5. Snapshot a gold/master database as RAC OneNode

6. Convert a database (SI or RAC OneNode) to RAC

7. Convert a non-CDB database to PDB of a given CDB

8. Delete database

9. List databases

10. Create an encrypted SYS password file

Case Studies

1. Managing a test & dev environment combined with Oracle Data Guard

2. Creating database clone using RMAN backupsets

3. Creating database clone from Oracle Cloud Database Backup Service (OCI)

4. Creating database clone from RMAN full backup and upgrade to 19c

5. Clone 11g Database from ASM to ACFS keeping the source running

6. Create a snapshot database as RAC from a running database (GOLD clone)

7. Create a clone RAC database from a “remote” standby database

8. Create a snapshot RAC database from a “local” standby database

9. Clone a database from RMAN full backup to ACFS as standby Database

10. Clone a database from OCDBS (Oracle Cloud Database Backup Service/Storage) as RAC standby Database

11. Database upgrade using Transient Logical Standby (TLS) (manual mode)

12. Database upgrade with limted downtime using nzdru feature (TLS automatic) (beta)

13. Clone a database encrypted with Transparent Data Encryption (TDE)

14. Using gDBClone on ODA HA,S,M,L (Enterprise Edition)

15. Migrate a database from OCIC to OCI using gDBClone

16. Test & Dev Management environment example

gDBClone “-pfile” option

Debug Information

References

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Linux x86-64
Oracle Solaris on x86-64 (64-bit)
Oracle Solaris on SPARC (64-bit)

Abstract

gDBClone is a tool to simplify management of database test and development environments leveraging Oracle ACFS snapshots and/or Oracle Exascale sparse clone

History

Author: Ruggero Citton - RAC Pack, Cloud Innovation and Solution Engineering Team
Create Date Set - 2013
Update Date October - 2023

- gDBClone Version 3.0.6:

OS	Version/Rrevision	sha256sum
Linux_el8	20231003 - $Revision: 3.0.6.3 $	7e8efb03e3d12b5db567c06f08accd418437eb81357a177615045d96acd7379a
Linux_el7	20231003 - $Revision: 3.0.6.3 $	55daa2e1affbc1bd3a3cfca85603a1df29ddbb8d41e97c6cb1cdbfbae5b205df

- For a snapshot from a standby DB on Oracle Exascale, fix for bug:34301681 is required
- For a snapshot on Exascale with 23c, fix for bug:35856329 is required

- gDBClone Version 3.0.5:

OS	Version/Revision	sha256sum
Linux el8	Version: 20230531 - $Revision: 3.0.5.3 $	870a3daefeb57e8e9a2fb79009288e6fd7e25c6f351c52450898203e457db26a
Linux el7	Version: 20230531 - $Revision: 3.0.5.3 $	3bbe72ca7b1dd6971ed5b10c36bd31425fee7ef94981946101a72263f13f5919
Linux el6	Version: 20230531 - $Revision: 3.0.5.3 $	ff63c12d221d81dbe551289be180d2af31193012237e2066969ae55623f22c9a
Solaris i386	Version: 20230531 - $Revision: 3.0.5.3 $	4f7085b84afaf03da9bf24823019b8343162b6b125e532fb1a0aeb606549c3ef
Solaris sparc	Version: 20200722 - $Revision: 3.0.4.3 $	9ad3786838b72356df7250cc25d1f72e72a00beabfbe6da5167902d2e46efbb6

What's new

What's new in 3.0.6.3 release:
- minor update
What's new in 3.0.6.2 release:
- Oracle 23c support
What's new in 3.0.6.1 release:
- Oracle Exascale support + others improvement
What's new in 3.0.5.3 release:
- Improvements for no-CDB to PDB conversion
What's new in 3.0.5.1 release:
- "gDBClone clone" is now supporting "source DB connect string" (with paramater "-sconstring")

Details

### -------------------------------------------------------------------
### Disclaimer:
###
### EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
### PROVIDED ON AN \"AS IS\" AND \"AS AVAILABLE\" BASIS. ORACLE EXPRESSLY DISCLAIMS
### ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
### LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
### PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
### THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
### RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
### EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
### OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
### NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
### RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.
###
### ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
### TIME WITHOUT NOTICE.
###
### Limitation of Liability:
###
### IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
### SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
### DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
### CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.
### -------------------------------------------------------------------

Executive Overview

As database-driven applications grow rapidly, maximizing agility, reducing management overhead and cost savings are top priority for IT organizations today. Customers must have solutions to contain data redundancy that is sprawling out of control. On the average, more than 10 full copies of a production databases are created for test, development and reporting purposes.
In addition to redundant data, customers struggle with ever increasing management overhead required for managing database life cycles. Frequent challenges are provisioning of databases and deployment in test and development environments frequently, efficiently, quickly and cost effectively.
Oracle addresses management of test and development environments with advanced products and technologies to realize:

» Simplicity
» Cost savings
» Reduction of management overhead
» Agility

Database Provisioning Lifecycle and Challenges

Managing database test and development (test & dev) environments can be challenging and costly in time and resources. Production databases often require 8-10 or more copies for varies types of test and development purposes. Each copy of a database consumes significant storage space. Database copies are typically recycled (created, deleted or refreshed) often. Conventional ways of manually managing test & dev environments can be complex, costly and time consuming. Test & dev life cycle can be defined at a high level as follows:

An initial copy of a production database is created on a test & dev cluster as a master copy. Data scrubbing may be required either on the production server or on the test cluster as well. Database or data scrubbing could mean data filtering, redaction or any other technique the user chooses to use in order to provide only the data set that is needed or authorizes for test and development purposes. A database home should be identified or provisioned in preparation for deploying a test database. Multiple copies of databases may be provisioned off the master copy on the test cluster. Database administrators manage the environments and clean up when a database copy is no longer needed.

Managing Test & Dev Environments Does Not Have to be Complex

gDBClone is a tool that was developed to provide a simple and efficient method for cloning a database for test and dev environments. gDBClone leverages on ASM Cluster File System (ACFS) snapshot functionality to create space efficient copies of databases and manage a test and dev database life cycle.
The key enabling technology was introduced in Oracle Database 12.1 that allows creation of Oracle Database files directly on the ACFS file system (RDBMS 11.2.0.4 and up) and therefore benefiting from ACFS point in time snapshot functionality for sparse database provisioning efficiently.

Starting with gDBClone 3.0.6, clone and snap operation are supported on Exascale too.

gDBClone performs following key functions:

Clone: Creates a clone database (as Primary or as Standby) from a production database copying the DB to the target test and dev cluster
Snap: Creates sparse ACFS snapshots and/or sparse Exascale clone of the DB to be used for test and development
Convert: Converts a given database to RAC (Real Application Cluster) OneNode, RAC or from non-CDB (non-container database) to a PDB (pluggable database) of a given CDB
NZDRU: Near to Zero Donwtime Rolling Upgrade, It will perform a rolling upgrade leveraging on Trans Logical Standby (beta)
ListDBs: Lists the cloned databases and its snapshots
DelDB: Deletes cloned databases and/or its snapshots
ListHomes: Lists the available oracle home
listSnaps: List the available ACFS snapshots
delSnap: Deletes ACFS snapshot
SYSPwF: Creates an encrypted password file

Purpose of Database Duplication

A duplicate database is useful for a variety of purposes, most of which involve testing & upgrade. You can perform the following tasks in a duplicate database:

» Test backup and recovery procedures
» Test an upgrade to a new release of Oracle Database
» Test the effect of applications on database performance
» Create a standby database (Dataguard) and as option with DG Broker setup
» Leverage on Transient Logical Standby (TLS) to perform an upgrade
» Generate reports

For example, you can duplicate the production database on host1 to host2, and then use the duplicate database on host2 to practice restoring and recovering this database while the production database on host1 operates as usual.

Test and Dev environment

The following diagram illustrates a typical test and dev environment that can be created and managed with the gDBClone command.

In this example, a copy of a production database is cloned into the test & dev cluster with a single gDBClone command (“gDBClone clone”). The source database may be on an Exadata Database Machine or any other legacy server and any type of file system including Oracle ASM, Oracle Exascale.

gDBClone is utilized to provision sparse space efficient copies of databases (“gDBClone snap”). These copies may be deployed for test and dev purposes. Only small incremental storage is required by the snapshots after the initial creation of the master copy as illustrated by the storage capacity illustration on the right of figure 2 above.

An Oracle ACFS snapshot is an online, read only or read write, point in time copy of an Oracle ACFS file system. The snapshot copy is space efficient and uses Copy-on-Write (ACFS COW) functionality. Before an Oracle ACFS file extent is modified or deleted, its current value is preserved in the snapshot to maintain the point in time view of the file system. Oracle ACFS supports 1023 snapshots per file system

Database Clone vs Database Snapshot creation time

The database clone creation time depends on the database size and on the network throughput. In case of database snapshot, the cloning operation is a very fast operation as it’s independent on the database size and/or network speed. In the following example, we compare a clone/snap of 3Gb vs 60Gb database size:

Supported Configurations and Features

gDBClone Clone

Creates a clone database (as Primary or as Physical Standby) from a production database duplicating (physical copy) the DB to the Test & Dev cluster using “RMAN Duplicate from Active Database” (by default gDBClone is allocating 3 RMAN channels, you may overwrite it using “-channels <RMAN channels number>” command option). The source database may be on an Exadata Database Machine or any other legacy server and any type of file system including Oracle ASM. gDBClone needs to connect the remote database normally through the SCAN (Single Client Access Network) listener.

It’s also possible clone a production database from a given RMAN full backup location (the full backup can be on Filesystem/NFS, on Oracle Database Backup Cloud Service or on a tape) and, if needed, perform the upgrade within one command. The source database can be SI (Single Instance), RAC OneNode or RAC, primary or standby. The target clone database can be SI (Single Instance), RAC OneNode or RAC (by default it will be SI). The 12c Multitenant container databases are supported.
The gDBClone “clone” option can be used to instantiate a Dataguard environment and the standby can be and “Active Standby” or a “Real Time Apply”.

The “gDBClone clone” is done without special impact on the source production database. The “gDBClone clone” could duplicate a source database full backup having less IO impact over the production source database. In this case it’s also possible to limit the clone (“duplicate until”) to a given SCN (‘-scn’) or time (‘-time’).

On cloning a remote or local database 3 different ACFS mount points are possible:

-dataacfs <acfs mount point> --> Database datafiles target ACFS storage
-redoacfs <acfs mount point> --> Database redologs target ACFS storage (default dataacfs)
-recoacfs <acfs mount point> --> Database recovery target ACFS storage (default dataacfs)

gDBClone can be used to clone a database to ASM, in such case, 3 different disk groups are possible:

-datadg <ASM diskgroup> --> Database datafiles target ASM disk group
-redodg <ASM diskgroup> --> Database redologs target ASM disk group (default +DATA)
-recodg <ASM diskgroup> --> Database recovery target ASM disk group (default +DATA)

Note: cloning a database to ASM you cannot leverage later on the database gDBClone snapshot feature

Starting with gDBClone 3.0.6, it's possible clone a database to Exascale vault. On Exascale it's possible get database snapshot (sparse clone):

-dataxdg <Exascale vault> --> Database datafiles target Exascale vault
-redoxdg <Exascale vault> --> Database redologs target Exascale vault (default @DATA)
-recoxdg <Exascale vault> --> Database recovery target Exascale vault (default @DATA)

gDBClone Snap

Creates sparse snapshots of the DB to be used for test and development. The source database must be stored on local Oracle ACFS filesystem or on Exascale vault

The source database can be SI (Single Instance), RAC OneNode or RAC, primary or standby. The target snapshot database can be SI (Single Instance), RAC OneNode or RAC (by default it will be SI), primary or standby. The gDBClone is introducing the “Hot Database Snapshot as Standby” capability. Without impact over the source database and "without" storage duplication, leveraging on ACFS snapshot copy-on-write (ACFS COW) feature, gDBClone is making a snapshot of a running database and if "-standby" option is used the result will be a standby database.

Note: the possibility to get a physical standby from a running database without downtime is the key to make database upgrade leveraging on TLS (Transient Logical Standby)

gDBClone supports snapshot of a running standby database without production impact leveraging on the “Snapshot Standby” database feature.

gDBClone is also supporting “Multi ACFS database file locations” and “database snapshot for different ORACLE_HOMEs

gDBClone nzdru (near Zero Downtime Rolling Upgrade) (beta)

"Near Zero Downtime Rolling Upgrade" is a feature able to make a database upgrade with very limited downtime leveraging on TLS (Trans Logical Standby). This feature still in beta version

- nzdru has resumable capability. Using "-prompt" command option, you have the possibility to stop the tool at macrostep and in case re-execute the tool

» MacroStep3 - Temporary database physical standby to transient logical standby
» MacroStep4 - Temporary database logical standby upgrade
» MacroStep5 - Switchover Temporary database logical standby to primary and Primary database to logical standby
» MacroStep7 - Source database logical standby to physical standby
» MacroStep9 - Switchover Source database logical standby to primary and Temporary database to physical standby

- In order to leverage on nzdru you must have a Database Service in place, (here an example of AC application continuity service for database PROD and TEMP, where PROD is the target database to migrate and TEMP is the temporary standby created as "clone" or as "snap" used by gDBClone nzdru to perform the upgrade)

/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl add service -d PROD -s NZDRU_AC
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl modify service -d PROD -s NZDRU_AC -e SESSION -P BASIC
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl modify service -d PROD -s NZDRU_AC -w 5
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl modify service -d PROD -s NZDRU_AC -z 60
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl modify service -d PROD -s NZDRU_AC -j SHORT
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl start service -d PROD -s NZDRU_AC

/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl add service -d TEMP -s NZDRU_AC
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl modify service -d TEMP -s NZDRU_AC -e SESSION -P BASIC
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl modify service -d TEMP -s NZDRU_AC -w 5
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl modify service -d TEMP -s NZDRU_AC -z 60
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl modify service -d TEMP -s NZDRU_AC -j SHORT

and the connect string the application may use could be as example:

(DESCRIPTION=
(RETRY_COUNT=6)(FAILOVER=ON)
(ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=TCP)(HOST=exa-scan)(PORT=1521)))
    (CONNECT_DATA=
    (SERVICE_NAME=NZDRU_AC)
    (SERVER = DEDICATED)
   )
)

Note: as part of the nzdru process a restore point "NZDRU_0000_0001" and a backup control file (ORACLE_HOME/dbs/NZDRU_0000_<temp standby>_f.f) will be created. You can use them to restore temp standby back to its original state as
a physical standby, in case the rolling upgrade operation needs to be aborted prior to the first switchover done in MacroStep6.

- "Near Zero Downtime Rolling Upgrade" uses autoupgrade tool by default. You can specify "-dbua" to leverage on DBUA instead

Note: Autoupgrade will write related logs under "/opt/gDBClone/out/log". You could check the upgrade status issuing:

cd /opt/gDBClone/out/log/cfgtoollogs/upgrade/auto
python -m SimpleHTTPServer 8000

and from a broser checkout the URL: "http://<host>:8000/state.html"

gDBClone Convert

Beside “clone” and “snap” features, gDBClone can be used to convert a database to a RAC or RAC OneNode database and it can be used to convert a no-container database(non-CDB) to a Pluggable database (PDB) of a given CDB.

gDBClone ListDBs & DelDB

gDBClone also provides single command to verify your database environments, providing parent/child relation in case of snap-of-snap database, and delete databases that are no longer in use.

gDBClone ListHomes

With “listhomes” command option you can check which available ORACLE_HOMEs are available. The oracle home name will be used later to “attach” the clone/snap database (“-tdbhome”).

gDBClone ListSnaps & DelSnap

You could use gDBClone to list and remove ACFS snapshot

gDBClone SYSPwF

Using “gDBClone syspwf” an encrypted password file will be created. Such password is the SYS source remote database password. Doing a clone/snap with “-syspwf <sys password file>” option, gDBClone will use the encrypted password, otherwise it will request at command line. If a file with the name: “SYSpasswd_file” is present under gDBClone home (“/opt/gDBClone”), at clone/snap time, gDBClone will check for the password from that file, in such case you can avoid the “-syspwf” option and no password request are done at command line.

gDBClone Command Syntax

Usage:
gDBClone clone -sdbname <source DB name>
               -sdbscan <source DB Host SCAN name> | -sconstring '<source DB connect string>'
               |-sbckloc '<backup location path>' [-time <DD-MON-YYYY_HH24:MI:SS>] [-upgrade [-parallel <number of process>]]
               |-sopcbck -opclib '<opc_lib_path>' -opcpfile '<opc_pfile_path'
                 [-scn <scn>] [-dbid <DB id>] [-rmanpwf <rman password file>] [-upgrade [-parallel <number of process>] ]
               |-catuser <catalog user> [-catpwf <rman catalog password file>] -cstring <connect string>
                 [-scn <scn>] [-dbid <DB ID>] [-sbt1 <sbt params>] [-sbt2 <sbt params>]
               -tdbname <Target Database Name> [-tdomain <Target Database Domain Name>] -tdbhome <Target Database Home Name>
               -dataacfs <acfs mount point> [-redoacfs <acfs mount point>] [-recoacfs <acfs mount point>]
              |-datadg <asm data diskgroup> [-redodg <asm redo diskgroup> ] [-recodg <asm reco diskgroup>]}
              |-dataxdg <exascale data diskgroup> [-redoxdg <exascale redo diskgroup> ] [-recoxdg <exascale reco diskgroup>]}
               [-sga_max_size <size Mb> ] [-sga_target <size Mb>] | [-pfile <file path>]
               [-channels <RMAN channels number> ] [-ssize <size Mb>] [-cbset]
               [-sdbport <Source DB SCAN Listener Port> ] [-tdbport <Target DB SCAN Listener Port>]
               [-racmod <db type> ]
               [-standby [-pmode maxperf|maxavail|maxprot] [-activedg] [-rtapply] [-dgbroker [-dgbpath1 <dgb config path>][-dgbpath2 <dgb config path>]] ]
               [-opc ] [-noping]
               [-resume ]
               [-syspwf <sys password file>]

gDBClone snap -sdbname <source DB name> -tdbname <Target Database Name>
             [-tdbhome <Target Database Home Name>]
             [-sga_max_size <size Mb>] [-sga_target <size Mb>] | [-pfile]
             [-standby [-pmode maxperf|maxavail|maxprot] [-activedg][-rtapply][-dgbroker]]
             [-sdbport <SCAN Listener Port>]
             [-racmod <db type>]
             [-syspwf <sys password file>]
             [-walletpwf <wallet password file>]

gDBClone nzdru -sdbname <Source DB name>
               -tdbname <Target Database Name>
               -udbhome <Upgraded Database Home Name>
               -service <Service Name>
              [-prompt]
              [-dbua]
              [-syspwf <sys password file>] [-tsyspwf <sys password file>]

gDBClone convert -sdbname <source noCDB name>
-racmod <1|2> | -tdbname <target CDB name> [-check] {[-copy] [-path <path>]}
[-syspwf <sys password file>] [-tsyspwf <sys password file>]

gDBClone listhomes [-verbose]

gDBClone listdbs [-tree] | [-verbose]
gDBClone deldb -tdbname <database name> [-tsyspwf <sys password file>][-force]

gDBClone listsnaps -dataacfs <acfs_mount_point> [-tree]
gDBClone delsnap -snapname <snapshot name> -dataacfs <acfs_mount_point>

gDBClone syspwf -syspwf <SYS encrypted password file path>

gDBClone OPTIONS

gDBClone OPTIONS
-sdbname      Source Database Name
-sdbscan      Source DB Host SCAN Name
-sconstring   Source DB connect string
-sdbport      Source SCAN Listener Port (default 1521)
-sbckloc      Source RMAN Full Backup Location
-tdbname      Target Database Name
-tdomain      Target Database Domain Name
-tdbhome    Target Database Home Name
-tdbport      Target SCAN Listener Port (default 1521)
-dataacfs     Database datafiles target ACFS storage
-redoacfs     Database redologs target ACFS storage (default dataacfs)
-recoacfs Database recovery target ACFS storage (default dataacfs)
-datadg       Database datafiles target ASM diskgroup
-redodg Database redologs target ASM diskgroup (default +DATA)
-recodg       Database recovery target ASM diskgroup (default +DATA)
-dataxdg      Database datafiles target Exascale vault
-redoxdg      Database redologs target Exascale vault (default @DATA)
-recoxdg      Database recovery target Exascale vault (default @DATA)
-standby      The clone/snap will be a physical standby database
-pmode        Standby option: maxperf/maxavail/maxprot (default maxperf)
-activedg     Enable Active Dataguard
-rtapply      Enable real time apply
-dgbroker     Enable DG Broker
-dgbpath1     DG Broker remote configuration file path
-dgbpath2 DG Broker remote configuration file path
-racmod       0/1/2 == SINGLE/RACONE/RAC (default 0)
-upgrade      Clone for upgrade
-dbua         NZDRU upgrade using dbua (Default: autoupgrade)
-parallel     Number of upgrade process (Default 4)
-sga_max_size SGA Max Size (Mb)
-sga_target   SGA Target (Mb)
-pfile        Parameters file
-channels     RMAN allocate channels (default 3)
-ssize        RMAN section size
-cbset        RMAN compressed backupset
-opc          Required option on RACDBaaS environment
-noping       Avoid ping check
-resume       Clone resume
-opclib       Location of the SBT library
-opcpfile     Location of the SBT configuration file
-scn          SCN number or Latest SCN from backup to clear datafile fuzziness
-time         Time in the format 'DD-MON-YYYY_HH24:MI:SS'
-catuser      RMAN catalog user
-catpwf       RMAN catalog user encrypted password file
-cstring      RMAN catalog connect string
-sbt1 RMAN SBT Tape Parameters
-sbt2         RMAN SBT Tape Parameters
-dbid         Database ID
-syspwf       SYS encrypted password file
-tsyspwf      SYS encrypted password file
-rmanpwf    RMAN encrypted password file
-walletpwf    Wallet encrypted password file
-check    Will perform a CDB to PDB conversion pre-check
-copy         Will copy the source noCDB datafiles to CDB location (default: nocopy)
-path         Path where to copy the dbfiles (default CDB system dbf path)
-tree         With listdb will show the Parent/Snapshot tree
-verbose      Display OH & version on listdb
-force        With deldb will unregister the db
-rmandbg      On cloning RMAN debug log file location
-version      Show program's version number and exit

gDBClone Installation

gDBClone can be installed using the RPM (RedHat Package Manager) command as following:

# rpm -i gDBClone-3.0.6-X.<arch>.rpm

(*) X=version number
<arch>=OS architecture (el6, el7, solaris i386, solaris sparc)

or updating an installed version, issuing:

# rpm -Uvh gDBClone-3.0.6-X.<arch>.rpm

Following files are created under ‘/opt/gDBClone’:

# tree /opt/gDBClone
/opt/gDBClone/
├── gDBClone.bin
└── utils
└──autoupgrade.jar

1 directory, 2 files

gDBClone deinstallation

gDBClone can be removed using the RPM (RedHat Package Manager) command as following:

# rpm -e gDBClone-3.0.6-X.<arch>

(*) X=version number
<arch>=OS architecture (el6, el7, solaris i386, solaris sparc)

Managing gDBClone Privileges and Security with SUDO

gDBClone command-line utility requires root system privileges for most actions. You may want to use SUDO as part of your system auditing and security policy.
For most tasks, you need to log in as root to use the gDBClone command-line interface. If you are not logged in as root, then you cannot carry out most actions such clone, snap.

Allowing Root User Access Using SUDO

In environments where system administration is handled by a different group than database administration, or where security is a significant concern, you may want to limit access to the root user account and password. SUDO enables system administrators to grant certain users (or groups of users) the ability to run commands as root, while logging all commands and arguments as part of your security and compliance protocol.
A SUDO security policy is configured by using the file /etc/sudoers. Within the sudoers file, you can configure groups of users and sets of commands to simplify and audit server administration with SUDO commands.

Caution: Configuring SUDO to allow a user to perform any operation is equivalent to giving that user root privileges. Consider carefully if this is appropriate for your security needs.

SUDO Example 1: Allow a User to Perform Any gDBClone Operation

This example shows how to configure SUDO to enable a user to perform any gDBClone operation. You
do this by adding lines to the commands section in the /etc/sudoers file:

Cmnd_Alias GDBCLONE_CMD=/opt/gDBClone/gDBClone *
rcitton ALL = GDBCLONE_CMD

In this example, the user name is rcitton . The file parameter setting ALL=GDBCLONE_CMDS grants the
user rcitton permission to run all gDBClone commands that are defined by the command alias
GDBCLONE_CMDS .

SUDO Example 2: Allow a User to Perform Only Selected gDBClone Operations

To configure SUDO to allow an user to perform only selected gDBClone operations, add lines to the
commands section in the /etc/sudoers file as follows:

Cmnd_Alias GDBCLONE_CMD=/opt/gDBClone/gDBClone clone
rcitton ALL = GDBCLONE_CMD

SUDO Example 3: Allow a User to Perform Any gDBClone Operation without password request

To configure SUDO to allow an user to perform gDBClone operations without password request, add
lines to the commands section in the /etc/sudoers file as follows:

Cmnd_Alias GDBCLONE_CMD=/opt/gDBClone/gDBClone *
rcitton ALL=(root) NOPASSWD:GDBCLONE_CMD

Limitations & Considerations

gDBClone works on a Grid Infrastructure environment and on Oracle Restart (SIHA). Source database must be in archivelog mode when cloning/snapshotting as it’s executed a hot clone/snapshot. Multitenant database (CDB) snapshot is not currently supported if contains PDBs created as “snapshot copy”.
“gDBClone snap” needs EE (Enterprise Edition) Databases as the RMAN snapshot time recovery feature is needed and Grid Infrastructure version 12.1 or above. GI version 11g is not supported due to missing ACFS snapshot-of-snapshot feature capability.

gDBClone to target ACFS Consideration

"gDBClone clone" to target ACFS will duplicate the source database to an ACFS snapshot and not to ACFS "root". Later gDBClone snap is expecting the source database running from an ACFS snapshot.
The idea is to have a single ACFS filesystem to store many different databases.
Every single databse will be stored on a single ACFS snapshot and the "root" ACFS will be keep empty to preserve space and avoid duplicates when we are going to take a snapshot.

Recovery Manager (RMAN) considerations

Starting with Oracle Database 12c Release 1 (12.1), you can use multisection backup sets to transfer the source files required to perform active database duplication. Use the ‘-ssize’ clause in the “gDBClone clone” command to create multisection backup sets that can be used for active database duplication.
You can compress the backup sets created to transfer files from the source database to the destination database. Using compressed backup sets minimizes the size of the backup sets and thus provides better performance for the database duplication. Compressing backup sets used by “gDBClone clone” is supported starting with Oracle Database 12c Release 1 (12.1).

Source database using Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE), which functions at the column level, and tablespace encryption. If you are cloning a database with encrypted tablespaces, you must manually copy the keystore to the duplicate database. If the keystore is not an auto login (SSO) keystore, then you must convert it to an auto login keystore at the duplicate database. (See also case study)

Database clone/snap overwriting SGA parameters

gDBClone is supporting the possibility to clone/snap a source database “overwriting” some SGA parameters. You can leverage on this feature using the “-pfile <parameters file>” command option. The supported parameters are the following:

aq_tm_processes
archive_lag_target
audit_file_dest
bitmap_merge_area_size
create_bitmap_area_size
db_block_checking
db_block_checksum
db_cache_size
db_file_multiblock_read_count
db_files
db_lost_write_protect
deferred_segment_creation
diagnostic_dest
fast_start_parallel_rollback
filesystemio_options
hash_area_size
java_pool_size
job_queue_processes
large_pool_size
log_archive_format
log_archive_max_processes
log_archive_trace
open_cursors
optimizer_adaptive_plans
optimizer_adaptive_statistics
optimizer_adaptive_features
parallel_execution_message_size
parallel_force_local
parallel_max_servers
parallel_threads_per_cpu
pga_aggregate_target
processes
recovery_parallelism
remote_login_passwordfile
sec_case_sensitive_logon
session_cached_cursors
sessions
sga_max_size
sga_target
shared_pool_reserved_size
shared_pool_size
shared_servers
sort_area_retained_size
sort_area_size
standby_file_management
streams_pool_size
undo_management
undo_retention
tde_configuration
wallet_root

It’s possible “overwrite” just only sga_max_size & sga_target, using “-sga_max_size <size Mb>” & “-sga_target <size Mb>” gDBClone command option.

gDBClone usage example

1. Clone a Remote/Local (physical/standby or backup) database to ACFS(Gold/Image)

gDBclone clone command options:

$ sudo /opt/gDBClone/gDBClone clone -sdbname ORCL \
                                    -sdbscan exadata316-scan \
                                    -tdbname GOLD \
                                    -tdbhome OraDb12201_home1 \
                                    -dataacfs /u02/app/oracle/oradata/datastore
                                   [-redoacfs <acfs mount point>][-recoacfs <acfs mount point>]

You could use “-redoacfs” and/or “-recoacfs” to store redologs/archivelogs in different ACFS filesystems.

Note: If you need to decrease/increase the SGA footprint, example if your target local system cannot accommodate the source SGA, you can leverage on “-sga_max_size” and “-sga_target” gDBClone clone parameters (both expressed in Mb), or using the more comprehensive “-pfile” option.

2. Clone a Remote/Local database to ASM and make it a RAC database

gDBclone clone command options:

# gDBClone.bin clone -sdbname ORCL \
                     -sdbscan exadata316-scan \
                     -tdbname GOLD \
                     -tdbhome OraDb12201_home1 \
                     -datadg +MYDATA
                    [-redodg <dgname>][-recodg <dgname>]

Local default ASM diskgroup:+DATA
Local ASM diskgroup override: -datadg <dgname>, -redodg <dgname>, -recodg <dgname>

3. Clone a Multitenant database to ACFS

gDBclone clone command options:

# gDBClone.bin clone -sdbname ORCL \
                     -sdbscan exadata316-scan \
                     -tdbname GOLD \
                     -tdbhome OraDb12201_home1 \
                     -dataacfs /u02/app/oracle/oradata/datastore
                   [ -standby [-pmode maxperf|maxavail|maxprot] [-activedg] [-rtapply] ]
                   [ -racmod <db type> ]

Note: no extra options are needed, automatic CDB recognition

4. Clone a remote database to Exascale

gDBclone clone command options:

# gDBClone.bin clone \
                     -sdbname ORCL.sub11110943180.exascale.oraclevcn.com \
                     -sdbscan racpack-scan.racpack.sub11110943180.exascale.exascale \
                     -tdbname CLONE \
                     -tdomain sub11110943180.exascale.oraclevcn.com \
                     -tdbhome DbHome_1 \
                     -dataxdg @0nhGLu2f \
                     -syspwf syspasswd \
                     -racmod 2

5. Snapshot a gold/master database as RAC OneNode

# gDBClone.bin snap -sdbname GOLD \
-tdbname SNAP \
-racmod 1

In this case as the “ -tdbhome ” option is not provided, the ORACLE_HOME will be the same source database’s ORACLE_HOME.

Note: If you need to decrease/increase the SGA footprint, example if your target local system cannot accommodate the source SGA, you can leverage on “-sga_max_size” and “-sga_target” gDBClone snap parameters (both expressed in Mb), or using the more comprehensive “-pfile” option.

6. Convert a database (SI or RAC OneNode) to RAC

# gDBClone.bin convert -sdbname SNAP \
-racmod 2

Note: “ -racmod ” 0/1/2 = SINGLE/RACONE/RAC (default 0). Convert RAC OneNode, RAC to single instance is not supported.

7. Convert a non-CDB database to PDB of a given CDB

# gDBClone.bin convert -sdbname <source noCDB name>
-tdbname <target CDB name>
[-check] {[-copy] [-path <path>]}

-check Will perform a CDB to PDB conversion pre-check
-copy Will copy the source noCDB datafiles to CDB location (default: nocopy)
-path Path where to copy the dbfiles (default CDB system dbf path)

Note: before the conversion you may want execute “ gDBClone convert -check ” to verify the conversion result in “dry mode”. Using “-check” a report with warnings and potential conversion errors is generated for your review.

8. Delete database

# gDBClone.bin deldb -tdbname SNAP -force

9. List databases

Having the following scenario, “ gDBClone listdb ” will list relations and database type

Using "-tree" option

Using "-verbose" option

10. Create an encrypted SYS password file

# gDBClone syspwf -syspwf /opt/gDBClone/SYSpasswd_file

Case Studies

The gDBClone script provides flexible options and configurations to best fulfill the customer’s requirements.

1. Managing a test & dev environment combined with Oracle Data Guard

Many customers deploy Oracle Data Guard as their disaster recovery solution. When you create the standby database on an ACFS file system, you will have simple options to create and manage a test & dev environment on the standby cluster. This makes better utilization of the standby resources while enabling a test and dev environment. The following diagram illustrates a test and dev environment that can easily be managed using the gDBClone commands:

Using the ‘gDBClone snap’ function, you can either create multiple snapshots and provision for different purposes, or create a snapshot to preserve the point in time copy and create snaps of snaps to deploy identical copies of databases for test & dev as you can see from the diagram above. The advantage of this approach is that the master copy of the database (standby) is continuously refreshed by Data Guard allowing disaster recovery as well as refreshed data for testing.

Such scenario can be setup using gDBClone within few commands:

1) On target list the available Oracle Homes:

gDBClone.bin listhomes

Oracle Home Name Home Location
---------------- ------------
OraDb11204_home1 /u01/app/oracle/product/11.2.0.4/dbhome_1
OraDb12102_home1 /u01/app/oracle/product/12.1.0.2/dbhome_1
OraDb12201_home1 /u01/app/oracle/product/12.2.0.1/dbhome_1
OraDb19700_home1 /u01/app/oracle/product/19.7.0.0/dbhome_1

2) Create an encrypted SYS (source database) password file (optional):

$ sudo /opt/gDBClone/gDBClone.bin syspwf -syspwf /opt/gDBClone/SYS.passwd_file

Please enter the SYS User password : ## Enter the SYS source database password
Please re-enter the SYS user password : ##re-enter the SYS source DB password file

SYS password file created as /opt/gDBClone/SYS.passwd_file

Note: if you skip the syspwf file creation, the SYS source database password will be requested at command line

3) Clone the source database (ORCL) on target as Standby database (CLONE):

$ sudo /opt/gDBClone/gDBClone.bin clone -sdbname DB197H1 \
                                      -sdbscan exadata316-scan \
                                        -tdbname CLONE \
                            -tdbhome OraDb19700_home1 \
                            -dataacfs /u02/app/oracle/oradata/datastore \
                                        -redoacfs /u03/app/oracle \
                                        -recoacfs /u03/app/oracle \
                            -syspwf /opt/gDBClone/SYS.passwd_file \
                            -standby

output example:

INFO: 2020-07-17 17:56:06: Please check the logfile '/opt/gDBClone/out/log/gDBClone_21040.log' for more details

MacroStep1 - Getting information and validating setup...
INFO: 2020-07-17 17:56:06: Validating environment
INFO: 2020-07-17 17:56:06: Checking superuser usage
INFO: 2020-07-17 17:56:06: Checking if target database name 'CLONE' is a valid name
INFO: 2020-07-17 17:56:06: Checking if target database home 'OraDb19700_home1' exists
INFO: 2020-07-17 17:56:06: Checking if Oracle Restart
INFO: 2020-07-17 17:56:06: Checking ping to host 'exadata316-scan'
INFO: 2020-07-17 17:56:06: Getting ORACLE_BASE path from orabase
INFO: 2020-07-17 17:56:06: Checking if target database 'CLONE' exists
INFO: 2020-07-17 17:56:06: Checking 'CLONE' snapshot existence on '/u02/app/oracle/oradata/datastore'
INFO: 2020-07-17 17:56:06: Checking registered instance 'CLONE'
INFO: 2020-07-17 17:56:26: Checking listener on 'exadata316-scan:1521'
INFO: 2020-07-17 17:56:26: Checking ACFS command options
INFO: 2020-07-17 17:56:26: Checking if '/u02/app/oracle/oradata/datastore' is an ACFS file system
INFO: 2020-07-17 17:56:26: Checking if '/u03/app/oracle' is an ACFS file system
INFO: 2020-07-17 17:56:26: Checking if '/u03/app/oracle' is an ACFS file system
INFO: 2020-07-17 17:56:30: Checking source and target database version
INFO: 2020-07-17 17:56:33: Checking source database size
INFO: 2020-07-17 17:56:36: Checking source database role
INFO: 2020-07-17 17:56:40: Checking source log mode
INFO: 2020-07-17 17:56:43: Checking Force Logging mode
INFO: 2020-07-17 17:56:47: Checking Flashback mode
WARNING: 2020-07-17 17:56:50: Source database 'DB197H1' is not in FLASHBACK mode
INFO: 2020-07-17 17:56:50: Checking LOG_ARCHIVE_DEST settings
SUCCESS: 2020-07-17 17:56:54: Environment validation complete

MacroStep2 - Setting up clone environment...
INFO: 2020-07-17 17:56:54: Creating local pfile
INFO: 2020-07-17 17:56:54: Creating local password file
INFO: 2020-07-17 17:56:57: Creating local Audit folder
INFO: 2020-07-17 17:56:57: Checking local auxiliary listener
INFO: 2020-07-17 17:56:58: Creating local auxiliary listener
INFO: 2020-07-17 17:56:58: Starting auxiliary listener
INFO: 2020-07-17 17:56:58: Sleeping 60 secs, please wait
INFO: 2020-07-17 17:57:58: Setting up ACFS storage
INFO: 2020-07-17 17:57:58: Creating dynamic scripts
INFO: 2020-07-17 17:58:01: Cloning to target ACFS from host 'rcvm-scan' as standby database
INFO: 2020-07-17 17:58:07: Creating RMAN script for spfile target to ACFS
INFO: 2020-07-17 17:58:08: Instantiating standby database
INFO: 2020-07-17 17:58:08: ...getting standby logs on source database DB197H1
INFO: 2020-07-17 17:58:11: ...getting MAX redologs group on source database DB197H1
INFO: 2020-07-17 17:58:15: ...getting THREAD# and redolog size on source database DB197H1
INFO: 2020-07-17 17:58:19: ...creating standby logs on source database DB197H1
SUCCESS: 2020-07-17 17:58:33: Environment setup complete

MacroStep3 - Cloning database 'DB197H1'...
INFO: 2020-07-17 17:58:33: Getting cluster_interconnects values on 'DB197H1'
INFO: 2020-07-17 17:58:36: Database clone from active database in progress, please wait
INFO: 2020-07-17 17:58:36: (this can take a while depending on database size and/or network speed)
INFO: 2020-07-17 18:00:06: Cloned database restore until SCN:2173758 done successfully
INFO: 2020-07-17 18:00:06: Moving spfile
INFO: 2020-07-17 18:00:34: Register 'CLONE' database as cluster resource
INFO: 2020-07-17 18:00:37: Updating spfile
INFO: 2020-07-17 18:00:42: Updating local pfile
INFO: 2020-07-17 18:00:46: Modifying DB instance
INFO: 2020-07-17 18:00:48: Setup ACFS dependency
SUCCESS: 2020-07-17 18:00:54: Clone database 'CLONE' created successfully

MacroStep5 - Standby setup...
INFO: 2020-07-17 18:01:10: Enabling standby file management for database 'CLONE'
INFO: 2020-07-17 18:01:13: Starting redo apply
INFO: 2020-07-17 18:01:22: Configuring primary database 'DB197H1'
SUCCESS: 2020-07-17 18:01:52: Successfully created clone "CLONE" database as standby

4) Check the target database (CLONE) creation:

gDBClone.bin listdbs

Database Name Database Type Database Role    Master/Snapshot Location/Parent
------------- ------------- -------------    --------------- ---------------
CLONE         SINGLE        PHYSICAL_STANDBY Master          /u02/app/oracle/oradata/datastore/.ACFS/snaps/

5) Create a snapshot database SDB1 from the source standby CLONE database

gDBClone.bin snap -sdbname CLONE -tdbname S1DB

Note: in order to get a snapshot database from a source standby database, the Flashback is mandatory, if needed, on source CLONE standby database execute:

RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE FLASHBACK ON;
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Note: without “ -tdbhome <Target Database Home Name> ” parameter, the database home will be the same source database oracle home, in this example “ OraDb19700_home1”

output example:

INFO: 2020-07-17 18:07:59: Please check the logfile '/opt/gDBClone/out/log/gDBClone_23972.log' for more details

MacroStep1 - Getting information and validating setup...

Please enter the 'SYS' User password for the database CLONE:
Please re-enter the 'SYS' user password for the database CLONE:
INFO: 2020-07-17 18:08:04: Validating environment...
INFO: 2020-07-17 18:08:04: Superuser usage check
INFO: 2020-07-17 18:08:04: Clusterware running check
INFO: 2020-07-17 18:08:04: Oracle Restart check
INFO: 2020-07-17 18:08:04: Minimum crs activeversion check
INFO: 2020-07-17 18:08:04: Database 'CLONE' existence check
INFO: 2020-07-17 18:08:04: Database 'CLONE' running check
INFO: 2020-07-17 18:08:06: Getting ORACLE_BASE path from orabase
INFO: 2020-07-17 18:08:06: Checking if target database name S1DB is a valid name
INFO: 2020-07-17 18:08:06: Checking database 'CLONE' connectivity
INFO: 2020-07-17 18:08:21: Checking whether the database 'CLONE' is in ACFS snapshot
INFO: 2020-07-17 18:08:21: Checking source database 'CLONE' and target dbhome version
INFO: 2020-07-17 18:08:28: Checking if target database 'S1DB' exists
INFO: 2020-07-17 18:08:28: Checking registered instance 'S1DB'
INFO: 2020-07-17 18:08:48: Checking if S1DB exists as snapshot in '/u02/app/oracle/oradata/datastore'
INFO: 2020-07-17 18:08:48: Checking if source database CLONE is snapable
INFO: 2020-07-17 18:08:55: ...Checking whether the database 'CLONE' is entirely on ACFS
INFO: 2020-07-17 18:09:02: ...Checking whether the database 'CLONE' is a primary/physical standby database.
INFO: 2020-07-17 18:09:05: ...Checking whether the Physical Standby database 'CLONE' is in MOUNT mode
INFO: 2020-07-17 18:09:12: ...Checking flashback on database 'CLONE'
INFO: 2020-07-17 18:09:19: ...Checking whether the database 'CLONE' is a multitenant
INFO: 2020-07-17 18:09:34: ...Checking whether the database 'CLONE' is running in archivelog mode
INFO: 2020-07-17 18:09:41: ...Checking if all the datafiles are available
SUCCESS: 2020-07-17 18:09:48: Environment validation complete

MacroStep2 - Getting database snapshot...
INFO: 2020-07-17 18:09:48: Cloning source database 'CLONE' using ACFS snapshot
INFO: 2020-07-17 18:10:27: Entering into SNAP database creation phase 1
INFO: 2020-07-17 18:10:49: Converting physical standby 'CLONE' to snapshot standby
INFO: 2020-07-17 18:11:26: ...getting the snapshot of Database 'CLONE' at this time
INFO: 2020-07-17 18:11:26: ...successfully took the snapshot 'S1DB' of database 'CLONE' on '/u02/app/oracle/oradata/datastore'
INFO: 2020-07-17 18:13:02: ...Converting snapshot standby 'CLONE' to physical standby
INFO: 2020-07-17 18:14:00: ...Starting redolog apply on standby database 'CLONE'
INFO: 2020-07-17 18:14:13: ...Setting up storage for SNAP Database 'S1DB'
INFO: 2020-07-17 18:14:18: Entering into SNAP database creation phase 2
INFO: 2020-07-17 18:14:18: ...Creating controlfile for database 'S1DB'
INFO: 2020-07-17 18:14:41: ...Opening the database 'S1DB' with resetlogs
INFO: 2020-07-17 18:14:51: ...Setting the temporary tablespace for database 'S1DB'
INFO: 2020-07-17 18:16:08: ...Changing the Database ID
INFO: 2020-07-17 18:17:42: ...Creating spfile for 'S1DB'
INFO: 2020-07-17 18:17:51: ...Creating password file for S1DB
INFO: 2020-07-17 18:17:51: Entering into SNAP database creation phase 3
INFO: 2020-07-17 18:18:16: ...Successfully started the database
INFO: 2020-07-17 18:18:20: ...Setting RMAN SNAPSHOT control file
INFO: 2020-07-17 18:19:40: Setup ACFS dependency
SUCCESS: 2020-07-17 18:19:48: Snapshot database 'S1DB' created successfully
INFO: 2020-07-17 18:19:48: Starting database 'S1DB'
SUCCESS: 2020-07-17 18:19:50: Successfully started clone database 'S1DB'

6) Check the target database (S1DB) creation:

gDBClone.bin listdbs

Database Name Database Type Database     Role Master/Snapshot Location/Parent
------------- ------------- -------------    ---------------      ---------------
CLONE         SINGLE        PHYSICAL_STANDBY Master               /u02/app/oracle/oradata/datastore/.ACFS/snaps/
S1DB          SINGLE      PRIMARY          Snapshot             CLONE

Note: you could verify the parent relationship also doing

gDBClone.bin listdbs -tree

Parent Child
------ -----
CLONE
S1DB

----------------------------------------------------------------
Note: a branch tree will be displayed only if the parent exists!
----------------------------------------------------------------

7) Create a snapshot database S2DB as RAC(Real Application Cluster) from the source standby CLONE database:

gDBClone.bin snap -sdbname CLONE \
-tdbname S2DB \
-racmod 2

output:

INFO: 2020-07-17 18:26:13: Please check the logfile '/opt/gDBClone/out/log/gDBClone_11429.log' for more details

MacroStep1 - Getting information and validating setup...

Please enter the 'SYS' User password for the database CLONE:
Please re-enter the 'SYS' user password for the database CLONE:
INFO: 2020-07-17 18:26:17: Validating environment...
INFO: 2020-07-17 18:26:17: Superuser usage check
INFO: 2020-07-17 18:26:17: Clusterware running check
INFO: 2020-07-17 18:26:17: Oracle Restart check
INFO: 2020-07-17 18:26:17: Minimum crs activeversion check
INFO: 2020-07-17 18:26:17: Database 'CLONE' existence check
INFO: 2020-07-17 18:26:18: Database 'CLONE' running check
INFO: 2020-07-17 18:26:20: Getting ORACLE_BASE path from orabase
INFO: 2020-07-17 18:26:20: Checking if target database name S2DB is a valid name
INFO: 2020-07-17 18:26:20: Checking database 'CLONE' connectivity
INFO: 2020-07-17 18:26:35: Checking whether the database 'CLONE' is in ACFS snapshot
INFO: 2020-07-17 18:26:35: Checking source database 'CLONE' and target dbhome version
INFO: 2020-07-17 18:26:42: Checking if target database 'S2DB' exists
INFO: 2020-07-17 18:26:43: Checking registered instance 'S2DB'
INFO: 2020-07-17 18:27:05: Checking if S2DB exists as snapshot in '/u02/app/oracle/oradata/datastore'
INFO: 2020-07-17 18:27:05: Checking if source database CLONE is snapable
INFO: 2020-07-17 18:27:12: ...Checking whether the database 'CLONE' is entirely on ACFS
INFO: 2020-07-17 18:27:20: ...Checking whether the database 'CLONE' is a primary/physical standby database.
INFO: 2020-07-17 18:27:22: ...Checking whether the Physical Standby database 'CLONE' is in MOUNT mode
INFO: 2020-07-17 18:27:30: ...Checking flashback on database 'CLONE'
INFO: 2020-07-17 18:27:37: ...Checking whether the database 'CLONE' is a multitenant
INFO: 2020-07-17 18:27:51: ...Checking whether the database 'CLONE' is running in archivelog mode
INFO: 2020-07-17 18:27:59: ...Checking if all the datafiles are available
SUCCESS: 2020-07-17 18:28:06: Environment validation complete

MacroStep2 - Getting database snapshot...
INFO: 2020-07-17 18:28:06: Cloning source database 'CLONE' using ACFS snapshot
INFO: 2020-07-17 18:28:43: Entering into SNAP database creation phase 1
INFO: 2020-07-17 18:29:05: Converting physical standby 'CLONE' to snapshot standby
INFO: 2020-07-17 18:29:44: ...getting the snapshot of Database 'CLONE' at this time
INFO: 2020-07-17 18:29:44: ...successfully took the snapshot 'S2DB' of database 'CLONE' on '/u02/app/oracle/oradata/datastore'
INFO: 2020-07-17 18:31:20: ...Converting snapshot standby 'CLONE' to physical standby
INFO: 2020-07-17 18:32:18: ...Starting redolog apply on standby database 'CLONE'
INFO: 2020-07-17 18:32:31: ...Setting up storage for SNAP Database 'S2DB'
INFO: 2020-07-17 18:32:37: Entering into SNAP database creation phase 2
INFO: 2020-07-17 18:32:37: ...Creating controlfile for database 'S2DB'
INFO: 2020-07-17 18:32:58: ...Opening the database 'S2DB' with resetlogs
INFO: 2020-07-17 18:33:06: ...Setting the temporary tablespace for database 'S2DB'
INFO: 2020-07-17 18:34:21: ...Changing the Database ID
INFO: 2020-07-17 18:35:52: ...Creating spfile for 'S2DB'
INFO: 2020-07-17 18:36:02: ...Creating password file for S2DB
INFO: 2020-07-17 18:36:02: Entering into SNAP database creation phase 3
INFO: 2020-07-17 18:36:27: ...Successfully started the database
INFO: 2020-07-17 18:36:31: ...Setting RMAN SNAPSHOT control file
INFO: 2020-07-17 18:37:50: Setup ACFS dependency
SUCCESS: 2020-07-17 18:37:58: Snapshot database 'S2DB' created successfully

MacroStep3 - Converting clone database 'S2DB' to cluster mode...
INFO: 2020-07-17 18:38:03: Checking 'Real Application Clusters' option
INFO: 2020-07-17 18:38:10: Getting DG or mount point dependency
INFO: 2020-07-17 18:38:13: Database conversion started...
INFO: 2020-07-17 18:38:13: Making required redolog thread
INFO: 2020-07-17 18:38:17: The number of the nodes is less or equal the thread#, no more redolog thread are required
INFO: 2020-07-17 18:38:17: Making required undo tablespaces
INFO: 2020-07-17 18:38:25: Making required cluster database spfile changes
INFO: 2020-07-17 18:38:55: Making required instances spfile changes
INFO: 2020-07-17 18:40:01: Register 'S2DB' database as RAC database
INFO: 2020-07-17 18:40:03: Registering additional instance
INFO: 2020-07-17 18:40:04: Copying password file to the cluster nodes...
INFO: 2020-07-17 18:40:04: ...copying '/u01/app/oracle/product/19.7.0.0/dbhome_1/dbs/orapwS2DB1' to 'rcvm'
INFO: 2020-07-17 18:40:05: Copying pfile to the cluster nodes...
INFO: 2020-07-17 18:40:05: Setup ACFS dependency back...
SUCCESS: 2020-07-17 18:40:08: Database 'S2DB' converted to RAC successfully

INFO: 2020-07-17 18:40:08: Starting database 'S2DB'
SUCCESS: 2020-07-17 18:40:29: Successfully started clone database 'S2DB'

8) Check the target database (S2DB) creation:

Database Name Database Type Database Role      Master/Snapshot Location/Parent
------------- ------------- -------------      --------------- ---------------
CLONE         SINGLE        PHYSICAL_STANDBY   Master           /u02/app/oracle/oradata/datastore/.ACFS/snaps/
S1DB          SINGLE        PRIMARY            Snapshot         CLONE
S2DB    RAC           PRIMARY            Snapshot CLONE

2. Creating database clone using RMAN backupsets

Databases may be cloned using RMAN backupsets from the production server to minimize the overhead. The backupsets may be exported using the NFS network protocol as the source for the gDBClone command.

The backup sets may be mounted via NFS on the test cluster. In this case, the NFS mount must be exported using the “insecure” export option on the source server for Oracle Database 12c tools to access the NFS mount properly.

The following is an example of RMAN source database full backup command:

RMAN> RUN
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/mnt/backup/ORCL/%U';
BACKUP DATABASE PLUS ARCHIVELOG;
BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/mnt/backup/ORCL/control_%U';
BACKUP SPFILE FORMAT '/mnt/backup/ORCL/spfile_%U';
}

The gDBClone command (example) is as following:

gDBClone.bin clone -sdbname DB197H1 \
                   -sbckloc /mnt/backup/ORCL/ \
                   -tdbname GOLD \
                   -tdbhome OraDb19700_home1 \
                   -dataacfs /u02/app/oracle/oradata/datastore \
                   -redoacfs /u03/app/oracle \
                   -recoacfs /u03/app/oracle \
                   -syspwf SYS.passwd

output:

INFO: 2020-07-18 10:19:17: Please check the logfile '/opt/gDBClone/out/log/gDBClone_13551.log' for more details

MacroStep1 - Getting information and validating setup...
INFO: 2020-07-18 10:19:17: Validating environment
INFO: 2020-07-18 10:19:17: Checking superuser usage
INFO: 2020-07-18 10:19:17: Checking if target database name 'GOLD' is a valid name
INFO: 2020-07-18 10:19:17: Checking if target database home 'OraDb19700_home1' exists
INFO: 2020-07-18 10:19:17: Checking if Oracle Restart
INFO: 2020-07-18 10:19:17: Checking source backup location /u01/BACKUP/DB197H1...
INFO: 2020-07-18 10:19:17: Getting ORACLE_BASE path from orabase
INFO: 2020-07-18 10:19:17: Checking if target database 'GOLD' exists
INFO: 2020-07-18 10:19:17: Checking 'GOLD' snapshot existence on '/u02/app/oracle/oradata/datastore'
INFO: 2020-07-18 10:19:17: Checking registered instance 'GOLD'
INFO: 2020-07-18 10:19:45: Checking listener on 'rcvm-scan:1521'
INFO: 2020-07-18 10:19:45: Checking ACFS command options
INFO: 2020-07-18 10:19:45: Checking if '/u02/app/oracle/oradata/datastore' is an ACFS file system
INFO: 2020-07-18 10:19:45: Checking if '/u03/app/oracle' is an ACFS file system
INFO: 2020-07-18 10:19:45: Checking if '/u03/app/oracle' is an ACFS file system
SUCCESS: 2020-07-18 10:19:50: Environment validation complete

MacroStep2 - Setting up clone environment...
INFO: 2020-07-18 10:19:50: Creating local pfile
INFO: 2020-07-18 10:19:50: Creating local password file
INFO: 2020-07-18 10:19:50: Creating local Audit folder
INFO: 2020-07-18 10:19:50: Checking local auxiliary listener
INFO: 2020-07-18 10:19:50: Creating local auxiliary listener
INFO: 2020-07-18 10:19:50: Starting auxiliary listener
INFO: 2020-07-18 10:19:50: Sleeping 60 secs, please wait
INFO: 2020-07-18 10:20:50: Setting up ACFS storage
INFO: 2020-07-18 10:20:50: Creating dynamic scripts
INFO: 2020-07-18 10:20:53: Cloning to target ACFS from backup location '/u01/BACKUP/DB197H1'
INFO: 2020-07-18 10:20:53: Creating RMAN script for spfile target to ACFS
INFO: 2020-07-18 10:20:53: Instantiating clone database
SUCCESS: 2020-07-18 10:20:53: Environment setup complete

MacroStep3 - Cloning database 'DB197H1'...
INFO: 2020-07-18 10:20:53: Database clone from backup in progress, please wait
INFO: 2020-07-18 10:20:53: (this can take a while depending on database size and/or network speed)
INFO: 2020-07-18 10:23:41: Cloned database restore until SCN:2401020 done successfully
INFO: 2020-07-18 10:23:41: Moving spfile
INFO: 2020-07-18 10:24:44: Register 'GOLD' database as cluster resource
INFO: 2020-07-18 10:24:48: Updating spfile
INFO: 2020-07-18 10:24:53: Updating local pfile
INFO: 2020-07-18 10:24:53: Checking database name
INFO: 2020-07-18 10:24:53: Modifying DB instance
INFO: 2020-07-18 10:24:55: Setup ACFS dependency
SUCCESS: 2020-07-18 10:25:01: Clone database 'GOLD' created successfully

INFO: 2020-07-18 10:25:01: Starting database 'GOLD'
SUCCESS: 2020-07-18 10:25:19: Successfully created clone database 'GOLD'

Note: if LOG_ARCHIVE_DEST_2 is set on source database backup, the clone may fail with error

" ORA-16019: cannot use LOG_ARCHIVE_DEST_2 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST "

Unset LOG_ARCHIVE_DEST_2 on source database, get a full database backup and retry the clone with “-sbckloc”

Check for the new cloned database “GOLD”:

Database Name Database Type Database Role Master/Snapshot Location/Parent
------------- ------------- ------------- --------------- ---------------
GOLD SINGLE PRIMARY Master /u02/app/oracle/oradata/datastore/.ACFS/snaps/

3. Creating database clone from Oracle Cloud Database Backup Service (OCI)

Databases can be cloned from the Oracle Cloud Database Backup Service. Oracle Database Cloud Backup Module is required.

gDBClone command (example) is as following:

gDBClone.bin clone -sdbname DB197H1 \
                   -sopcbck -opclib /opt/bck2cloud/oci/libopc \
                   -opcpfile /opt/bck2cloud/oci/bck2cloud.conf \
                   -dbid 3206634329 \
                   -tdbname DB197H1 \
                   -tdbhome OraDb19700_home1 \
                   -datadg +DATA \
                   -redodg +RECO \
                   -recodg +RECO \
                   -channels 6 \
                   -rmanpwf RMAN.password \
                   -syspwf SYS.passwd_file

"-opclib" is specifiing where is located the "Oracle Database Cloud Backup Module" library
"-opcpfile" is specifiing where is located the "Oracle Database Cloud Backup Module" configuration file

Then contenf for "Oracle Database Cloud Backup Module" configuration file is something like

OPC_HOST=https://objectstorage.<region>.oraclecloud.com/n/<tennant name>
OPC_WALLET='LOCATION=file:<wallet location> CREDENTIAL_ALIAS=alias_oci'
OPC_CONTAINER=<bucket name>
OPC_COMPARTMENT_ID=<compartment id>
OPC_AUTH_SCHEME=BMC

gDBClone command output:

INFO: 2020-07-18 12:46:16: Please check the logfile '/opt/gDBClone/out/log/gDBClone_32629.log' for more details

MacroStep1 - Getting information and validating setup...
INFO: 2020-07-18 12:46:17: Validating environment
INFO: 2020-07-18 12:46:17: Checking superuser usage
INFO: 2020-07-18 12:46:17: Checking if target database name 'DB197H1' is a valid name
INFO: 2020-07-18 12:46:17: Checking if target database home 'OraDb19700_home1' exists
INFO: 2020-07-18 12:46:17: Checking if Oracle Restart
INFO: 2020-07-18 12:46:17: Getting ORACLE_BASE path from orabase
INFO: 2020-07-18 12:46:17: Checking if target database 'DB197H1' exists
INFO: 2020-07-18 12:46:18: Checking registered instance 'DB197H1'
INFO: 2020-07-18 12:46:45: Checking listener on 'rcvm-scan:1521'
INFO: 2020-07-18 12:46:45: Checking ASM command options
INFO: 2020-07-18 12:46:50: Checking if '+DATA' is a valid ASM diskgroup
INFO: 2020-07-18 12:46:51: Checking '+DATA' RDBMS compatible
INFO: 2020-07-18 12:46:52: Checking if '+RECO' is a valid ASM diskgroup
INFO: 2020-07-18 12:46:53: Checking '+RECO' RDBMS compatible
INFO: 2020-07-18 12:46:54: Checking if '+RECO' is a valid ASM diskgroup
INFO: 2020-07-18 12:46:56: Checking '+RECO' RDBMS compatible
SUCCESS: 2020-07-18 12:47:00: Environment validation complete

MacroStep2 - Setting up clone environment...
INFO: 2020-07-18 12:47:00: Creating local password file
INFO: 2020-07-18 12:47:00: Creating local Audit folder
INFO: 2020-07-18 12:47:01: Setting up ASM storage
INFO: 2020-07-18 12:47:01: Getting GI ORACLE_BASE from orabase
INFO: 2020-07-18 12:47:11: Instantiating clone database
SUCCESS: 2020-07-18 12:47:11: Environment setup complete

MacroStep3 - Cloning database 'DB197H1' from Cloud Storage Backup...
INFO: 2020-07-18 12:47:11: Restoring the spfile as pfile
INFO: 2020-07-18 12:51:05: Updating the pfile '/u01/app/oracle/product/19.7.0.0/dbhome_1/dbs/initDB197H1.ora'
INFO: 2020-07-18 12:51:07: Creating spfile from pfile
INFO: 2020-07-18 12:51:07: Creating spfile to ASM
INFO: 2020-07-18 12:51:50: Register 'DB197H1' database as cluster resource
INFO: 2020-07-18 12:51:52: Restoring the controlfile
INFO: 2020-07-18 12:52:39: Restoring the database
INFO: 2020-07-18 12:54:08: Getting latest SCN
INFO: 2020-07-18 12:54:42: Recovering the database until SCN '2414210'
INFO: 2020-07-18 12:55:03: Modifying DB instance
INFO: 2020-07-18 12:55:06: Setup ASM DG dependency
SUCCESS: 2020-07-18 12:55:11: Clone database 'DB197H1' created successfully

INFO: 2020-07-18 12:55:11: Starting database 'DB197H1'
SUCCESS: 2020-07-18 12:55:13: Successfully created clone database 'DB197H1'

4. Creating database clone from RMAN full backup and upgrade to 19c

In the following scenario we are cloning an 11g database from an RMAN full backup, upgrading to 19c and making it a RAC database.

Steps to clone an 11g database to 19c:

Execute Preupgrade script (preupgrd.sql on 12c, preupgrade.jar on 19c) at 11g database. Such script will validate 11g database for upgrade and provide recommendations. This script is available under '$ORACLE_HOME/rdbms/admin' directory.
Take the RMAN full backup
Do the gDBClone “clone for upgrade”

The gDBClone command (example) is as following:

gDBClone.bin clone -sdbname PROD \
                   -sbckloc /u01/BACKUP/PROD/ \
                   -tdbname PRODUPG \
                   -tdbhome OraDb19700_home1 \
                   -dataacfs /u02/app/oracle/oradata/datastore \
                   -redoacfs /u03/app/oracle \
                   -recoacfs /u03/app/oracle \
                   -upgrade -parallel 8 \
                   -racmod 2 \
                   -syspwf /opt/gDBClone/SYS.passwd

output:

INFO: 2020-07-18 14:24:58: Please check the logfile '/opt/gDBClone/out/log/gDBClone_30868.log' for more details

MacroStep1 - Getting information and validating setup...
INFO: 2020-07-18 14:24:59: Validating environment
INFO: 2020-07-18 14:24:59: Checking superuser usage
INFO: 2020-07-18 14:24:59: Checking if target database name 'PRODUPG' is a valid name
INFO: 2020-07-18 14:24:59: Checking if target database home 'OraDb19700_home1' exists
INFO: 2020-07-18 14:24:59: Checking if Oracle Restart
INFO: 2020-07-18 14:24:59: Checking source backup location /u01/BACKUP/PROD/...
INFO: 2020-07-18 14:24:59: Getting ORACLE_BASE path from orabase
INFO: 2020-07-18 14:24:59: Checking if target database 'PRODUPG' exists
INFO: 2020-07-18 14:24:59: Checking 'PRODUPG' snapshot existence on '/u02/app/oracle/oradata/datastore'
INFO: 2020-07-18 14:24:59: Checking registered instance 'PRODUPG'
INFO: 2020-07-18 14:25:30: Checking listener on 'rcvm-scan:1521'
INFO: 2020-07-18 14:25:30: Checking ACFS command options
INFO: 2020-07-18 14:25:30: Checking if '/u02/app/oracle/oradata/datastore' is an ACFS file system
INFO: 2020-07-18 14:25:30: Checking if '/u03/app/oracle' is an ACFS file system
INFO: 2020-07-18 14:25:30: Checking if '/u03/app/oracle' is an ACFS file system
SUCCESS: 2020-07-18 14:25:39: Environment validation complete

MacroStep2 - Setting up clone environment...
INFO: 2020-07-18 14:25:39: Creating local pfile
INFO: 2020-07-18 14:25:39: Creating local password file
INFO: 2020-07-18 14:25:39: Creating local Audit folder
INFO: 2020-07-18 14:25:39: Checking local auxiliary listener
INFO: 2020-07-18 14:25:39: Creating local auxiliary listener
INFO: 2020-07-18 14:25:39: Starting auxiliary listener
INFO: 2020-07-18 14:25:39: Sleeping 60 secs, please wait
INFO: 2020-07-18 14:26:39: Setting up ACFS storage
INFO: 2020-07-18 14:26:39: Creating dynamic scripts
INFO: 2020-07-18 14:26:42: Cloning to target ACFS from backup location '/u01/BACKUP/PROD/'
INFO: 2020-07-18 14:26:42: Creating RMAN script for spfile target to ACFS
INFO: 2020-07-18 14:26:42: Instantiating clone database
SUCCESS: 2020-07-18 14:26:42: Environment setup complete

MacroStep3 - Cloning database 'PROD'...
INFO: 2020-07-18 14:26:42: Database clone from backup in progress, please wait
INFO: 2020-07-18 14:26:42: (this can take a while depending on database size and/or network speed)
INFO: 2020-07-18 14:29:24: Cloned database restore until SCN:2618982 done successfully
INFO: 2020-07-18 14:29:24: Moving spfile
INFO: 2020-07-18 14:29:52: Register 'PRODUPG' database as cluster resource
INFO: 2020-07-18 14:29:55: Updating spfile
INFO: 2020-07-18 14:30:00: Updating local pfile
INFO: 2020-07-18 14:30:00: Performing required actions for upgrade
INFO: 2020-07-18 14:30:00: ...Starting database 'PRODUPG' for upgrade
INFO: 2020-07-18 14:30:15: ...Opening database 'PRODUPG' for upgrade
INFO: 2020-07-18 14:30:31: ...Performing catalog upgrade, this can take a while, see '/opt/gDBClone/out/log'
INFO: 2020-07-18 15:06:51: ...Starting database 'PRODUPG'
INFO: 2020-07-18 15:07:10: ...Performing post upgrade scripts, this can take a while, see '/opt/gDBClone/out/log'
------------------------------------------------------------------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 UPGRADED
CATPROC Oracle Database Packages and Types 19.0.0.0.0 UPGRADED
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 UPGRADED
XML Oracle XDK 19.0.0.0.0 UPGRADED
CATJAVA Oracle Database Java Packages 19.0.0.0.0 UPGRADED
APS OLAP Analytic Workspace 19.0.0.0.0 UPGRADED
RAC Oracle Real Application Clusters 19.0.0.0.0 UPGRADED
OWM Oracle Workspace Manager 19.0.0.0.0 UPGRADED
CONTEXT Oracle Text 19.0.0.0.0 UPGRADED
XDB Oracle XML Database 19.0.0.0.0 UPGRADED
ORDIM Oracle Multimedia 19.0.0.0.0 UPGRADED
SDO Spatial 19.0.0.0.0 UPGRADED
XOQ Oracle OLAP API 19.0.0.0.0 UPGRADED
AMD OLAP Catalog 11.2.0.4.0 OPTION OFF
APEX Oracle Application Express 3.2.1.00.1 VALID
------------------------------------------------------------------
SUCCESS: 2020-07-18 15:07:42: Clone database upgraded successfully
INFO: 2020-07-18 15:07:42: Checking database name
INFO: 2020-07-18 15:07:42: Modifying DB instance
INFO: 2020-07-18 15:07:44: Setup ACFS dependency
SUCCESS: 2020-07-18 15:07:50: Clone database 'PRODUPG' created successfully

MacroStep4 - Converting clone database 'PRODUPG' to cluster mode...
INFO: 2020-07-18 15:07:55: Checking 'Real Application Clusters' option
INFO: 2020-07-18 15:08:03: Getting DG or mount point dependency
INFO: 2020-07-18 15:08:05: Database conversion started...
INFO: 2020-07-18 15:08:06: Making required redolog thread
INFO: 2020-07-18 15:08:08: The number of the nodes is less or equal the thread#, no more redolog thread are required
INFO: 2020-07-18 15:08:08: Making required undo tablespaces
INFO: 2020-07-18 15:08:16: Making required cluster database spfile changes
INFO: 2020-07-18 15:08:47: Making required instances spfile changes
INFO: 2020-07-18 15:09:54: Register 'PRODUPG' database as RAC database
INFO: 2020-07-18 15:09:56: Registering additional instance
INFO: 2020-07-18 15:09:57: Copying password file to the cluster nodes...
INFO: 2020-07-18 15:09:57: ...copying '/u01/app/oracle/product/19.7.0.0/dbhome_1/dbs/orapwPRODUPG1' to 'rcvm'
INFO: 2020-07-18 15:09:58: Copying pfile to the cluster nodes...
INFO: 2020-07-18 15:09:58: ...copying '/u01/app/oracle/product/19.7.0.0/dbhome_1/dbs/initPRODUPG1.ora' to 'rcvm'
INFO: 2020-07-18 15:09:58: Setup ACFS dependency back...
SUCCESS: 2020-07-18 15:10:01: Database 'PRODUPG' converted to RAC successfully

INFO: 2020-07-18 15:10:01: Starting database 'PRODUPG'
SUCCESS: 2020-07-18 15:10:21: Successfully created clone database 'PRODUPG'

5. Clone 11g Database from ASM to ACFS keeping the source running

Prior to Oracle 12c, moving datafiles is always an offline task. Using gDBClone you can “move” (clone) a database from ASM to ACFS keeping it running. If you need to preserve the transactions during the cloning operation you may consider the Oracle GoldenGate usage.

gDBClone clone -sdbname ORCL \
               -sdbscan exadata316-scan \
               -tdbname CLONE \
               -tdbhome OraDb11204_home1 \
               -dataacfs /u02/app/oracle/oradata/datastore
             [ -redoacfs <acfs mount point> ]
             [ -recoacfs <acfs mount point> ]
             [ -standby [-pmode maxperf|maxavail|maxprot][-activedg] [-rtapply] ]
             [ -racmod <db type> ]

6. Create a snapshot database as RAC from a running database (GOLD clone)

Once you got a clone gold image (from a backupset or from a running physical/standby database) of your production database you can now, using the ‘gDBCone snap’ function, create multiple snapshots for different purposes. The source clone can be SI, RAC OneNode or RAC and the target snapshot database can be SI, RAC OneNode or RAC (“ -racmod ”). You could convert the snapshot database later also, using the “convert” option.

1) Create a snapshot database SDB from the source standby CLONE database:

gDBClone.bin snap -sdbname GOLD \
-tdbname SDB \
-racmod 1

output:

MacroStep1 - Getting information and validating setup...
INFO: 2017-09-25 01:02:55: Validating environment...
INFO: 2017-09-25 01:02:55: Superuser usage check
INFO: 2017-09-25 01:02:55: Clusterware running check
INFO: 2017-09-25 01:02:55: Minimun crs activeversion check
INFO: 2017-09-25 01:02:55: Database 'GOLD' existence check
INFO: 2017-09-25 01:02:55: Database 'GOLD' running check
INFO: 2017-09-25 01:02:57: Getting ORACLE_BASE path from orabase
INFO: 2017-09-25 01:02:57: Checking if target database name SDB is a valid name
INFO: 2017-09-25 01:02:57: Checking database 'GOLD' connectivity
INFO: 2017-09-25 01:03:07: Checking whether the database 'GOLD' is in ACFS snapshot
INFO: 2017-09-25 01:03:07: Checking source database 'GOLD' and target dbhome version
INFO: 2017-09-25 01:03:11: Checking if target database 'SDB' exists
INFO: 2017-09-25 01:03:11: Checking registered instance 'SDB'
INFO: 2017-09-25 01:03:22: Checking if SDB exists as snapshot in '/u02/app/oracle/oradata/datastore'
INFO: 2017-09-25 01:03:22: Checking if source database GOLD is snapable
INFO: 2017-09-25 01:03:27: ...Checking whether the database 'GOLD' is entirely on ACFS
INFO: 2017-09-25 01:03:32: ...Checking whether the database 'GOLD' is a primary/physical standby database.
INFO: 2017-09-25 01:03:34: ...Checking whether the database 'GOLD' is in READ WRITE mode
INFO: 2017-09-25 01:03:38: ...Checking whether the database 'GOLD' is a CDB
INFO: 2017-09-25 01:03:43: ...Checking whether the database 'GOLD' is running as backup mode
INFO: 2017-09-25 01:03:48: ...Checking whether the database 'GOLD' is running in archivelog mode
INFO: 2017-09-25 01:03:52: ...Checking if all datafiles are available
INFO: 2017-09-25 01:03:57: ...Checking if there are OFFLINE datafiles
SUCCESS: 2017-09-25 01:04:02: Environment validation complete

MacroStep2 - Getting database snapshot...
INFO: 2017-09-25 01:04:02: Cloning source database 'GOLD' using ACFS snapshot
INFO: 2017-09-25 01:04:21: Entering into SNAP database creation phase 1
INFO: 2017-09-25 01:04:21: ...Getting required information to get consistent database snapshot
WARNING: 2017-09-25 01:04:26: Setting the database 'GOLD' in backup mode
INFO: 2017-09-25 01:04:41: ...Getting the snapshot of Database 'GOLD' at this time
INFO: 2017-09-25 01:04:41: ...Successfully took the snapshot 'SDB' of database 'GOLD' on
'/u02/app/oracle/oradata/datastore'
WARNING: 2017-09-25 01:04:51: Ending the database 'GOLD' backup mode
INFO: 2017-09-25 01:04:56: ...Setting up storage for SNAP Database 'SDB'
INFO: 2017-09-25 01:05:20: Entering into SNAP database creation phase 2
INFO: 2017-09-25 01:05:20: ...Creating controlfile for database 'SDB'
INFO: 2017-09-25 01:06:20: ...Recovering the database 'SDB' until 'SCN:15915958'
INFO: 2017-09-25 01:06:21: ...Opening the database with resetlogs
INFO: 2017-09-25 01:06:35: ...Setting the temporary tablespace for database 'SDB'
INFO: 2017-09-25 01:06:58: ...Changing the Database ID
INFO: 2017-09-25 01:07:41: ...Creating spfile for 'SDB'
INFO: 2017-09-25 01:07:42: ...Creating password file for SDB
INFO: 2017-09-25 01:07:42: Entering into SNAP database creation phase 3
INFO: 2017-09-25 01:08:12: ...Successfully started the database
INFO: 2017-09-25 01:08:12: ...Setting RMAN SNAPSHOT control file
INFO: 2017-09-25 01:08:19: ...Disabling the external references in the database 'SDB' inherited from 'GOLD'
------------------------------------------------------------------------------
Run on the database 'SDB' the SQL script:
'/u01/app/oracle/product/11.2.0.4/dbhome_1/enable_external_refs_SDB_67e7.sql'
to enable these external references.
Also need to restart the database after running the SQL script.
------------------------------------------------------------------------------
INFO: 2017-09-25 01:09:12: ...Enabling supplimentary logging for database 'SDB'
INFO: 2017-09-25 01:09:13: Enabling flashback for database 'SDB'
INFO: 2017-09-25 01:09:28: Setup ACFS dependency
SUCCESS: 2017-09-25 01:09:36: Successfully created database 'SDB' from 'GOLD'
MacroStep3 - Converting clone database 'SDB' to cluster mode...
INFO: 2017-09-25 01:09:40: Getting DG or mount point dependency
INFO: 2017-09-25 01:09:43: Database conversion started...
INFO: 2017-09-25 01:09:43: Making required redolog thread
INFO: 2017-09-25 01:09:43: The number of the nodes is less or equal the thread#, no more redolog thread are required
INFO: 2017-09-25 01:09:43: Making required undo tablespaces
INFO: 2017-09-25 01:09:48: Making required cluster database spfile changes
INFO: 2017-09-25 01:10:02: Creating audit file dest on node 'cac458'
INFO: 2017-09-25 01:10:02: Creating audit file dest on node 'cac459'
INFO: 2017-09-25 01:10:07: Making required instances spfile changes
INFO: 2017-09-25 01:10:54: Register 'SDB' database as RAC database
INFO: 2017-09-25 01:10:57: Registering additional instance
INFO: 2017-09-25 01:11:00: Copying password file to the cluster nodes...
INFO: 2017-09-25 01:11:00: ...copying '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwSDB1' to 'cac458'
INFO: 2017-09-25 01:11:00: ...copying '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwSDB2' to 'cac459'
INFO: 2017-09-25 01:11:00: Copying pfile to the cluster nodes...
INFO: 2017-09-25 01:11:00: ...copying '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initSDB1.ora' to 'cac458'
INFO: 2017-09-25 01:11:00: ...copying '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initSDB2.ora' to 'cac459'
INFO: 2017-09-25 01:11:00: Setup ACFS dependency back...
SUCCESS: 2017-09-25 01:11:03: Database 'SDB' converted to RAC successfully
INFO: 2017-09-25 01:11:03: Starting database 'SDB'
SUCCESS: 2017-09-25 01:12:08: Successfully created clone database 'SDB' as ACFS snapshot

2) Check the new database created:

Database Name Database Type Database Role Master/Snapshot Location/Parent
------------- ------------- ------------- --------------- ---------------
GOLD SINGLE PRIMARY Master /u02/app/oracle/oradata/datastore/.ACFS/snaps/
SDB RACOneNode PRIMARY Snapshot GOLD

7. Create a clone RAC database from a “remote” standby database

Having a “remote” running standby database, using gDBClone is possible to get a clone of it. The source “remote” standby can be SI, RAC OneNode or RAC and the clone database can be SI, RAC OneNode or RAC (“-racmod”) running from ASM or ACFS. You could convert the snapshot database also later using the “convert” option.

1) Having gDBClone on remote host, check the source DB role

Database Name Database Type Database Role     Master/Snapshot Location/Parent
------------- ------------- -------------     --------------- ---------------
STDBY         RAC           PHYSICAL_STANDBY n/a             ASM

2) Run gDBClone from target host

gDBClone.bin clone -sdbname STDBY \
               -sdbscan remotehost-scan \
                 -tdbname CLONE \
                 -tdbhome OraDb19700_home1 \
                 -dataacfs /u02/app/oracle/oradata/datastore
               [ -redoacfs <acfs mount point> ]
                 [ -recoacfs <acfs mount point> ]
                 [ -standby [-pmode maxperf|maxavail|maxprot][-activedg] [-rtapply] ]
               [ -racmod <db type> ]

8. Create a snapshot RAC database from a “local” standby database

Having a running standby database, using gDBClone is possible to get a snapshot of it. The source standby can be SI, RAC OneNode or RAC and the snapshot database can be SI, RAC OneNode or RAC (“ -racmod ”). You could convert the snapshot database also later using the “convert” option.

1) Check the source Standby database

Database Name Database Type Database Role     Master/Snapshot Location/Parent
------------- ------------- -------------     --------------- ---------------
STDBY         SINGLE        PHYSICAL_STANDBY  Master          /u02/app/oracle/oradata/datastore/.ACFS/snaps/

2) Create a snapshot database SNAP from the source standby STDBY database

MacroStep1 - Getting information and validating setup...
INFO: 2017-09-25 01:53:12: Validating environment...
INFO: 2017-09-25 01:53:12: Superuser usage check
INFO: 2017-09-25 01:53:12: Clusterware running check
INFO: 2017-09-25 01:53:12: Minimun crs activeversion check
INFO: 2017-09-25 01:53:12: Database 'STDBY' existence check
INFO: 2017-09-25 01:53:12: Database 'STDBY' running check
INFO: 2017-09-25 01:53:14: Getting ORACLE_BASE path from orabase
INFO: 2017-09-25 01:53:14: Checking if target database name SNAP is a valid name
INFO: 2017-09-25 01:53:14: Checking database 'STDBY' connectivity
INFO: 2017-09-25 01:53:23: Checking whether the database 'STDBY' is in ACFS snapshot
INFO: 2017-09-25 01:53:23: Checking source database 'STDBY' and target dbhome version
INFO: 2017-09-25 01:53:28: Checking if target database 'SNAP' exists
INFO: 2017-09-25 01:53:28: Checking registered instance 'SNAP'
INFO: 2017-09-25 01:53:43: Checking if SNAP exists as snapshot in '/u02/app/oracle/oradata/datastore'
INFO: 2017-09-25 01:53:43: Checking if source database STDBY is snapable
INFO: 2017-09-25 01:53:47: ...Checking whether the database 'STDBY' is entirely on ACFS
INFO: 2017-09-25 01:53:52: ...Checking whether the database 'STDBY' is a primary/physical standby database.
INFO: 2017-09-25 01:53:54: ...Checking whether the Physical Standby database 'STDBY' is in MOUNT mode
INFO: 2017-09-25 01:53:59: ...Checking flashback on database 'STDBY'
INFO: 2017-09-25 01:54:03: ...Checking whether the database 'STDBY' is a CDB
INFO: 2017-09-25 01:54:08: ...Checking whether the database 'STDBY' is running in archivelog mode
INFO: 2017-09-25 01:54:13: ...Checking if all datafiles are available
SUCCESS: 2017-09-25 01:54:18: Environment validation complete

MacroStep2 - Getting database snapshot...
INFO: 2017-09-25 01:54:18: Cloning source database 'STDBY' using ACFS snapshot
INFO: 2017-09-25 01:54:37: Entering into SNAP database creation phase 1
INFO: 2017-09-25 01:54:50: Converting physical standby 'STDBY' to snapshot standby
INFO: 2017-09-25 01:55:14: ...Getting the snapshot of Database 'STDBY' at this time
INFO: 2017-09-25 01:55:14: ...Successfully took the snapshot 'SNAP' of database 'STDBY' on '/u02/app/oracle/oradata/datastore'
INFO: 2017-09-25 01:56:25: ...Converting physical standby 'STDBY' to snapshot standby
INFO: 2017-09-25 01:57:11: ...Starting redolog apply on standby database 'STDBY'
INFO: 2017-09-25 01:57:25: ...Setting up storage for SNAP Database 'SNAP'
INFO: 2017-09-25 01:57:29: Entering into SNAP database creation phase 2
INFO: 2017-09-25 01:57:29: ...Creating controlfile for database 'SNAP'
INFO: 2017-09-25 01:58:26: ...Opening the database 'SNAP' with resetlogs
INFO: 2017-09-25 01:58:38: ...Setting the temporary tablespace for database 'SNAP'
INFO: 2017-09-25 01:59:04: ...Changing the Database ID
INFO: 2017-09-25 01:59:46: ...Creating spfile for 'SNAP'
INFO: 2017-09-25 01:59:48: ...Creating password file for SNAP
INFO: 2017-09-25 01:59:48: Entering into SNAP database creation phase 3
INFO: 2017-09-25 02:00:20: ...Successfully started the database
INFO: 2017-09-25 02:00:20: ...Setting RMAN SNAPSHOT control file
INFO: 2017-09-25 02:00:58: ...Enabling supplimentary logging for database 'SNAP'
INFO: 2017-09-25 02:00:59: Enabling flashback for database 'SNAP'
INFO: 2017-09-25 02:01:15: Setup ACFS dependency
SUCCESS: 2017-09-25 02:01:22: Successfully created database 'SNAP' from 'STDBY'

MacroStep3 - Converting clone database 'SNAP' to cluster mode...
INFO: 2017-09-25 02:01:26: Getting DG or mount point dependency
INFO: 2017-09-25 02:01:28: Database conversion started...
INFO: 2017-09-25 02:01:28: Making required redolog thread
INFO: 2017-09-25 02:01:28: The number of the nodes is less or equal the thread#, no more redolog thread are required
INFO: 2017-09-25 02:01:28: Making required undo tablespaces
INFO: 2017-09-25 02:01:33: Making required cluster database spfile changes
INFO: 2017-09-25 02:01:48: Creating audit file dest on node 'cac458'
INFO: 2017-09-25 02:01:48: Creating audit file dest on node 'cac459'
INFO: 2017-09-25 02:01:52: Making required instances spfile changes
INFO: 2017-09-25 02:02:40: Register 'SNAP' database as RAC database
INFO: 2017-09-25 02:02:42: Registering additional instance
INFO: 2017-09-25 02:02:44: Copying password file to the cluster nodes...
INFO: 2017-09-25 02:02:44: ...copying '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwSNAP1' to 'cac458'
INFO: 2017-09-25 02:02:44: ...copying '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwSNAP2' to 'cac459'
INFO: 2017-09-25 02:02:45: Copying pfile to the cluster nodes...
INFO: 2017-09-25 02:02:45: ...copying '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initSNAP1.ora' to 'cac458'
INFO: 2017-09-25 02:02:45: ...copying '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initSNAP2.ora' to 'cac459'
INFO: 2017-09-25 02:02:45: Setup ACFS dependency back...
SUCCESS: 2017-09-25 02:02:47: Database 'SNAP' converted to RAC successfully
INFO: 2017-09-25 02:02:47: Starting database 'SNAP'

SUCCESS: 2017-09-25 02:03:58: Successfully created clone database 'SNAP'

3) Check the new database created

Database Name Database Type Database Role     Master/Snapshot Location/Parent
------------- ------------- -------------     --------------- ---------------
STDBY         SINGLE        PHYSICAL_STANDBY Master          /u02/app/oracle/oradata/datastore/.ACFS/snaps/
STDBY         RAC           PRIMARY           Snapshot        STDBY

gDBClone.bin listdbs -tree

Parent Child
------ -----
CLONE
S1DB

----------------------------------------------------------------
Note: a branch tree will be displayed only if the parent exists!
----------------------------------------------------------------

9. Clone a database from RMAN full backup to ACFS as standby Database

Create a clone database NOWK from RMAN backupset and make it as standby of source database ORCL

gDBClone.bin clone -sdbname ORCL \
                   -sbckloc /mnt/ORCL/bck \
                   -sdbscan exa316c1n1-scan \
                   -tdbname NOWK \
                   -tdbhome OraDb12102_home1 \
                   -dataacfs /u02/app/oracle/oradata/datastore
                   -standby

Output:

MacroStep1 - Getting information and validating setup...
INFO: 2017-10-02 08:22:42: Validating environment
INFO: 2017-10-02 08:22:42: Checking superuser usage
INFO: 2017-10-02 08:22:42: Checking ping to host 'lac4-scan'
INFO: 2017-10-02 08:22:42: Checking if target database name 'NOWK' is a valid name
INFO: 2017-10-02 08:22:42: Checking if target database home 'OraDb11204_home1' exists
INFO: 2017-10-02 08:22:42: Getting ORACLE_BASE path from orabase
INFO: 2017-10-02 08:22:42: Checking if target database 'NOWK' exists
INFO: 2017-10-02 08:22:42: Checking 'NOWK' snapshot existence on '/u02/app/oracle/oradata/datastore'
INFO: 2017-10-02 08:22:42: Checking registered instance 'NOWK'
INFO: 2017-10-02 08:22:47: Checking listener on 'lac4-scan:1521'
INFO: 2017-10-02 08:22:47: Checking ACFS command options
INFO: 2017-10-02 08:22:47: Checking if '/u02/app/oracle/oradata/datastore' is an ACFS file system
INFO: 2017-10-02 08:22:47: Checking FLASHBACK mode
WARNING: 2017-10-02 08:22:47: Source database 'ORCL' is not in FLASHBACK mode
INFO: 2017-10-02 08:22:47: Checking LOG_ARCHIVE_DEST settings
SUCCESS: 2017-10-02 08:22:47: Environment validation complete

MacroStep2 - Setting up clone environment...
INFO: 2017-10-02 08:22:47: Creating local pfile
INFO: 2017-10-02 08:22:47: Creating local password file
INFO: 2017-10-02 08:22:47: Creating local Audit folder
INFO: 2017-10-02 08:22:47: Creating local auxiliary listener
INFO: 2017-10-02 08:22:47: Starting auxiliary listener
INFO: 2017-10-02 08:22:47: Sleeping 60 secs, please wait
INFO: 2017-10-02 08:23:47: Setting up ACFS storage
INFO: 2017-10-02 08:23:47: Creating dynamic scripts
INFO: 2017-10-02 08:23:50: Cloning to target ACFS from backup location ' /mnt/ORCL/bck ' as standby database
INFO: 2017-10-02 08:23:50: Creating RMAN script for spfile target to ACFS
INFO: 2017-10-02 08:23:50: Instantiating standby database
INFO: 2017-10-02 08:23:50: Enabling force logging
INFO: 2017-10-02 08:23:50: Getting standby logs on source database ORCL
INFO: 2017-10-02 08:23:50: Getting MAX redologs group on source database ORCL
INFO: 2017-10-02 08:23:50: Getting THREAD# and redolog size on source database ORCL
INFO: 2017-10-02 08:23:50: Creating standby logs on source database ORCL
SUCCESS: 2017-10-02 08:24:02: Environment setup complete

MacroStep3 - Cloning database 'ORCL'...
INFO: 2017-10-02 08:24:02: Getting cluster_interconnects values on 'ORCL'
INFO: 2017-10-02 08:24:02: Database clone in progress, please wait
INFO: 2017-10-02 08:24:02:
(this can take a while depending on database size and/or network speed)
INFO: 2017-10-02 08:26:12: Cloned database restore until SCN:215218 done successfully
INFO: 2017-10-02 08:26:12: Moving spfile
INFO: 2017-10-02 08:26:26: Register 'NOWK' database as cluster resource
INFO: 2017-10-02 08:26:27: Updating spfile
INFO: 2017-10-02 08:26:30: Updating local pfile
INFO: 2017-10-02 08:26:30: Modifying DB instance
INFO: 2017-10-02 08:26:31: Setup ACFS dependency
SUCCESS: 2017-10-02 08:26:35: Clone database 'NOWK' created successfully

MacroStep5 - Standby setup...
INFO: 2017-10-02 08:26:57: Enabling standby file management for database 'NOWK'
INFO: 2017-10-02 08:26:58: Starting redo apply
INFO: 2017-10-02 08:27:05: Configuring primary database 'ORCL'

SUCCESS: 2017-10-02 08:27:11: Successfully created clone "NOWK" database as standby

Check the standby creation

Database Name Database Type Database Role     Master/Snapshot Location/Parent
------------- ------------- -------------     --------------- ---------------
NOWK    SINGLE        PHYSICAL_STANDBY   Master         /u02/app/oracle/oradata/datastore/.ACFS/snaps/

Note1: for a clone as standby from backup-based duplication you must have the "controlfile for standby" included in the backup:

example:

RMAN> RUN
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/mnt/backup/ORCL/%U';
BACKUP DATABASE;
BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/mnt/backup/ORCL/control_%U';
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/mnt/backup/ORCL/standby_control_%U';
BACKUP SPFILE FORMAT '/mnt/backup/ORCL/spfile_%U';
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP ARCHIVELOG ALL;
}

Note2: With backup-based duplication you must copy the password file used on the primary to the standby, for Oracle Data Guard to ship logs.

10. Clone a database from OCDBS (Oracle Cloud Database Backup Service/Storage) as RAC standby Database

Create a clone database PROD from OCDBS (Oracle Cloud Database Backup Service/Storage) and make it as standby of source database:

gDBClone.bin clone -sdbname ORCL
                   -sdbscan oda404-scan
                   -sopcbck -opclib '/opt/bck2cloud/OPC/libopc'
                            -opcpfile '/opt/bck2cloud/OPC/bck2cloud.conf'
                   -tdbname ORCL
                   -tdbhome OraDb11204_home1
                   -dataacfs /u02/app/oracle/oradata/datastore
                   -channels 6
                   -racmod 2
                   -standby
                   -rmanpwf RMAN.password -syspwf SYS.passwd_file

With backup-based duplication you must copy the password file used on the primary to the standby, for Oracle Data Guard to ship logs.

11. Database upgrade using Transient Logical Standby (TLS) (manual mode)

Due to “Hot Database Snapshot as Standby” capability, without impact over the source production database and "without" storage duplication, leveraging on ACFS snapshot “redirect-on-write” feature, gDBClone is making a snapshot of a running database as standby. Having a physical standby from a running
production database without downtime is the key to make a database upgrade using the Transient Logical Standby (TLS).

The steps could be as following:

1. gDBClone snap as standby --> no downtime, minimal storage duplication (depend on source database production activity)
2. physru (TLS tool)
3. DBUA
4. physru (TLS tool)

Note: physru is a script to minimize downtime and simplify a database rolling upgrade using a physical standby database. A 'transient logical' herein refers to the physical standby database that has been temporarily converted to a transient logical standby database for the purpose of executing the upgrade. Also see the MAA Best Practice Oracle Database Rolling Upgrades, for additional information describing this process and Oracle11g Data Guard: Database Rolling Upgrade Shell Script (Doc ID NOTE:949322.1)

12. Database upgrade with limted downtime using nzdru feature (TLS automatic) (beta)

This approach will minimize the downtime required for an upgrade leveraging on transient logical standby rolling upgrade process. “gDBClone nzdru” greatly reduces the complexity of executing a rolling database upgrade by automating the upgrade steps. Although the upgrade begins with a physical standby database, the transient logical standby process uses SQL Apply to take redo generated by a database running a lower Oracle release, and apply the redo to a standby database running a higher Oracle release. When the upgrade process is complete, both the primary database and its physical standby database are operating at the new Oracle Database release.

The Steps

1. using gDBClone (clone/snap) build a physical standby (as snap or as clone, in case of clone the standby can be on ACFS or ASM)

example:

gDBClone.bin snap -sdbname PROD -tdbname TEMP -standby

2. Create the database service if not created yet (here an example of AC application continuity service)

3. Apply the nzdru feature

gDBClone.bin nzdru -sdbname PROD -tdbname TEMP \
-udbhome OraDb19000_home1
-service NZDRU_AC

By default gDBClone nzdru will upgrade the database using "autoupgrade". If you need to perform the database upgrade using "dbua" you need to use "-dbua" option

Complete execution example output:

gDBClone.bin listdbs -verbose
Database Name Database Type Database Role    Oracle Home Name Oracle Home Location    Home Version
------------- ------------- -------------    ---------------- ----------------------------------------- -----
TEMP    SINGLE    PHYSICAL_STANDBY OraDb11204_home1 /u01/app/oracle/product/11.2.0.4/dbhome_1 11.2.0.4.161018
PROD          SINGLE        PRIMARY          OraDb11204_home1 /u01/app/oracle/product/11.2.0.4/dbhome_1 11.2.0.4.161018

gDBClone.bin nzdru -sdbname PROD -tdbname TEMP -udbhome OraDb19700_home1 -service NZDRU_AC

INFO: 2017-06-01 06:17:08: Please check the logfile '/opt/gDBClone/out/log/gDBClone_93408.log' for more details
MacroStep1 - Stage 0 Getting information and validating setup...
INFO: 2017-06-01 06:17:09: [0-1] Database 'PROD' existence check
INFO: 2017-06-01 06:17:09: [0-1] Database 'TEMP' existence check
INFO: 2017-06-01 06:17:09: [0-1] DB home 'OraDb19700_home1' existence check
INFO: 2017-06-01 06:17:09: [0-1] Getting rdbms software version
INFO: 2017-06-01 06:17:14: ...Database 'PROD' is at version 11.2.0.4.0
INFO: 2017-06-01 06:17:19: ...Database 'TEMP' is at version 11.2.0.4.0
INFO: 2017-06-01 06:17:19: [0-1] Getting database 'PROD' unique name
INFO: 2017-06-01 06:17:20: [0-1] Getting database 'TEMP' unique name
INFO: 2017-06-01 06:17:21: [0-1] Getting database 'PROD' instance name
INFO: 2017-06-01 06:17:21: [0-1] Getting database 'TEMP' instance name
INFO: 2017-06-01 06:17:22: [0-1] Verifing database 'PROD' Flashback
INFO: 2017-06-01 06:17:27: [0-1] Verifing database 'TEMP' Flashback
INFO: 2017-06-01 06:17:32: [0-1] Verifing available database 'PROD' flashback restore points
INFO: 2017-06-01 06:17:32: [0-1] Verifing available database 'TEMP' flashback restore points
INFO: 2017-06-01 06:17:32: [0-1] Verifing if DG Broker is disabled on 'PROD'
INFO: 2017-06-01 06:17:33: [0-1] Verifing if DG Broker is disabled on 'TEMP'
INFO: 2017-06-01 06:17:33: [0-1] Verifing database 'PROD' temp files
INFO: 2017-06-01 06:17:34: [0-1] Verifing database 'TEMP' temp files
INFO: 2017-06-01 06:17:34: [0-1] Verifing database 'PROD' job_queue_processes
INFO: 2017-06-01 06:17:39: [0-1] Verifing database 'TEMP' job_queue_processes
INFO: 2017-06-01 06:17:44: [0-1] Looking up prior execution history
INFO: 2017-06-01 06:17:45: [0-1] Starting new execution of script
SUCCESS: 2017-06-01 06:17:45: Environment validation complete
MacroStep2 - Stage 1 Backup user environment in case rolling upgrade is aborted...
INFO: 2017-06-01 06:17:45: [1-1] Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:17:52: [1-1] Create initial restore point 'NZDRU_0000_0001' on database 'TEMP'
INFO: 2017-06-01 06:17:52: [1-1] Create backup control file '/u01/app/oracle/product/11.2.0.4/dbhome_1/NZDRU_0000_TEMP_f.f' on database 'TEMP'
INFO: 2017-06-01 06:17:54: [1-1] Create initial restore point 'NZDRU_0000_0001' on database 'PROD'
INFO: 2017-06-01 06:17:55: [1-1] Create backup control file '/u01/app/oracle/product/11.2.0.4/dbhome_1/NZDRU_0000_PROD_f.f' on database 'PROD'
-----------------------------------------------------------------------
NOTE: Restore point NZDRU_0000_0001
and backup control file /u01/app/oracle/product/11.2.0.4/dbhome_1/NZDRU_0000_TEMP_f.f
can be used to restore TEMP back to its original state as a physical standby,
in case the rolling upgrade operation needs to be aborted
prior to the first switchover done in MacroStep6.
-----------------------------------------------------------------------
INFO: 2017-06-01 06:17:57: [1-1] Creating checkpoint NZDRU_0101 on database 'TEMP'
INFO: 2017-06-01 06:17:57: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:17:57: ...Create restore point 'NZDRU_0101' on database 'TEMP'
SUCCESS: 2017-06-01 06:17:58: Backup user environment complete

MacroStep3 - Stage 2 Create transient logical standby from existing physical standby...
INFO: 2017-06-01 06:17:58: [2-1] Checking if RAC is disabled for database 'TEMP'
INFO: 2017-06-01 06:17:58: [2-1] Phase 1 - Prerequisites
INFO: 2017-06-01 06:17:58: ...checking database 'PROD' if PRIMARY
INFO: 2017-06-01 06:17:58: ...checking database 'TEMP' if PHYSICAL_STANDBY
INFO: 2017-06-01 06:17:59: ...checking database 'TEMP' if mounted
INFO: 2017-06-01 06:18:04: ...checking database 'TEMP' protection mode
INFO: 2017-06-01 06:18:04: ...exec dbms_stats.set_global_prefs on 'PROD'
INFO: 2017-06-01 06:18:05: ...checking database 'PROD' transient logical standby datatype support
INFO: 2017-06-01 06:18:06: [2-1] Creating checkpoint NZDRU_0201 on database 'TEMP'
INFO: 2017-06-01 06:18:06: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:18:06: ...Create restore point 'NZDRU_0201' on database 'TEMP'
INFO: 2017-06-01 06:18:06: [2-2] Phase 2 - Logical build and recover to logical standby
INFO: 2017-06-01 06:18:06: ...checking database 'TEMP' if PHYSICAL STANDBY
INFO: 2017-06-01 06:18:07: ...Stop media recovery for database 'TEMP'
INFO: 2017-06-01 06:18:07: ...Start media recovery for database 'TEMP'
INFO: 2017-06-01 06:18:07: ...Starting redo apply for standby database 'TEMP'
INFO: 2017-06-01 06:18:18: ...Switch logs on the primary database 'PROD'
INFO: 2017-06-01 06:18:18: ...Wait for the 'apply lag' to fall below 30 secs
INFO: 2017-06-01 06:18:19: ...Switch logs on primary database 'PROD'
INFO: 2017-06-01 06:18:29: ...Apply lag measured at 10 seconds
INFO: 2017-06-01 06:18:29: ...Stop media recovery for database 'TEMP'
INFO: 2017-06-01 06:18:36: ...Logical standby instantiation for database 'PROD'
INFO: 2017-06-01 06:18:46: ...Converting physical standby 'TEMP' into transient logical standby
INFO: 2017-06-01 06:18:51: [2-2] Creating checkpoint NZDRU_0202 on database 'TEMP'
INFO: 2017-06-01 06:18:51: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:18:51: ...Create restore point 'NZDRU_0202' on database 'TEMP'
INFO: 2017-06-01 06:18:51: [2-3] Phase 3 - Open transient logical standby database
INFO: 2017-06-01 06:18:51: ...Checking 'TEMP' open mode
INFO: 2017-06-01 06:18:52: ...Open the transient logical database 'TEMP'
INFO: 2017-06-01 06:18:54: [2-3] Creating checkpoint NZDRU_0203 on database 'TEMP'
INFO: 2017-06-01 06:18:54: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:18:54: ...Create restore point 'NZDRU_0203' on database 'TEMP'
INFO: 2017-06-01 06:18:55: [2-4] Phase 4 - Configure and startup logical standby
INFO: 2017-06-01 06:18:55: ...Checking 'TEMP' open mode
INFO: 2017-06-01 06:18:55: ...Stop logical standby 'TEMP'
INFO: 2017-06-01 06:18:55: ...Set rolling upgrade parameters for 'TEMP'
INFO: 2017-06-01 06:18:56: ...Start logical standby 'TEMP'
INFO: 2017-06-01 06:18:58: ...Wait until dictionary load has finished
INFO: 2017-06-01 06:19:20: ...dictionary load 75 percent complete
INFO: 2017-06-01 06:19:31: ...Dictionary load is complete
INFO: 2017-06-01 06:19:31: ...Switch logs on the primary database 'PROD'
INFO: 2017-06-01 06:19:34: ...Wait until apply lag is within 5 minutes
INFO: 2017-06-01 06:19:34: ...Wait for the 'apply lag' to fall below 30 secs
INFO: 2017-06-01 06:19:35: ...Switch logs on primary database 'PROD'
INFO: 2017-06-01 06:19:38: ...Apply lag measured at 4 seconds
INFO: 2017-06-01 06:19:38: ...Database 'TEMP' is now ready to be upgraded
INFO: 2017-06-01 06:19:38: [2-4] Creating checkpoint NZDRU_0204 on database 'TEMP'
INFO: 2017-06-01 06:19:38: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:19:38: ...Create restore point 'NZDRU_0204' on database 'TEMP'
SUCCESS: 2017-06-01 06:19:38: Transient logical standby from existing physical standby creation complete

MacroStep4 - Logical standby upgrade...
INFO: 2017-06-01 06:19:38: [3-1] Verifing if source db 'TEMP' is upgradable
INFO: 2017-06-01 06:19:49: Getting database 'TEMP' spfile
INFO: 2017-06-01 06:19:52: Upgrading the database 'TEMP'. It will take several minutes. Please wait...
INFO: 2017-06-01 06:19:54: Gathering dictionary stats
INFO: 2017-06-01 06:20:35: Running dbua to upgrade 'TEMP'
INFO: 2017-06-01 06:46:13: Setting spfile
'/u02/app/oracle/oradata/datastore/.ACFS/snaps/TEMP/TEMP/spfileTEMP.ora ' to 'TEMP'
SUCCESS: 2017-06-01 06:46:15: Logical standby upgrade complete

MacroStep5 - Stage 3 Validate upgraded transient logical standby...
INFO: 2017-06-01 06:46:15: Checking 'TEMP' open mode
INFO: 2017-06-01 06:46:20: [3-1]Database 'TEMP' is at target version
INFO: 2017-06-01 06:46:20: [3-1] Creating checkpoint NZDRU_0301 on database 'TEMP'
INFO: 2017-06-01 06:46:20: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:46:21: ...Create restore point 'NZDRU_0301' on database 'TEMP'
SUCCESS: 2017-06-01 06:46:21: Validate upgraded transient logical standby complete 3-1

MacroStep6 - Stage 4 Transient logical standby switch role...
INFO: 2017-06-01 06:46:21: ...checking database 'PROD' if PRIMARY
INFO: 2017-06-01 06:46:21: ...checking database 'TEMP' if LOGICAL STANDBY
INFO: 2017-06-01 06:46:22: ...checking 'PROD' open mode
INFO: 2017-06-01 06:46:22: ...checking 'TEMP' open mode
INFO: 2017-06-01 06:46:22: ...waiting for 'PROD' to catch up (this could take a while)
INFO: 2017-06-01 06:46:23: ...starting logical standby on database TEMP
INFO: 2017-06-01 06:46:24: ...wait for media recovery to catch up to a recent scn
INFO: 2017-06-01 06:46:24: ...Wait for the 'apply lag' to fall below 30 secs
INFO: 2017-06-01 06:46:24: ...Switch logs on primary database 'PROD'
INFO: 2017-06-01 06:47:35: ...Apply lag measured at 71 seconds
INFO: 2017-06-01 06:47:35: ...Switch logs on primary database 'PROD'
INFO: 2017-06-01 06:47:37: ...Apply lag measured at 2 seconds
INFO: 2017-06-01 06:47:37: [4-1] Creating checkpoint NZDRU_0401 on database 'TEMP'
INFO: 2017-06-01 06:47:37: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:47:37: ...Create restore point 'NZDRU_0401' on database 'TEMP'
INFO: 2017-06-01 06:47:37: ***************************************************
INFO: 2017-06-01 06:47:37: ...Switch 'PROD' to logical standby role
INFO: 2017-06-01 06:47:37: ***************************************************
INFO: 2017-06-01 06:47:37: Stopping database 'PROD' service NZDRU_AC
INFO: 2017-06-01 06:47:51: [4-2] Creating checkpoint NZDRU_0402 on database 'TEMP'
INFO: 2017-06-01 06:47:51: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:47:52: ...Create restore point 'NZDRU_0402' on database 'TEMP'
INFO: 2017-06-01 06:47:52: ...confirm TEMP has witnessed the role change
INFO: 2017-06-01 06:47:53: [4-3] Creating checkpoint NZDRU_0403 on database 'TEMP'
INFO: 2017-06-01 06:47:53: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:47:53: ...Create restore point 'NZDRU_0403' on database 'TEMP'
INFO: 2017-06-01 06:47:54: ***************************************************
INFO: 2017-06-01 06:47:54: ...Switch 'TEMP' to primary role
INFO: 2017-06-01 06:47:54: ***************************************************
INFO: 2017-06-01 06:48:07: Starting database 'TEMP' service NZDRU_AC
INFO: 2017-06-01 06:48:09: [4-4] Creating checkpoint NZDRU_0404 on database 'TEMP'
INFO: 2017-06-01 06:48:09: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:48:09: ...Create restore point 'NZDRU_0404' on database 'TEMP'
SUCCESS: 2017-06-01 06:48:10: Transient logical standby switch role complete

MacroStep7 - Stage 5 Flashback former primary to pre-upgrade restore point and convert to physical...
INFO: 2017-06-01 06:48:10: ...checking if RAC is disabled for database 'PROD'
INFO: 2017-06-01 06:48:10: [5-1] Phase 1 - Checking primary and standby role
INFO: 2017-06-01 06:48:10: ...checking database 'PROD' if LOGICAL STANDBY
INFO: 2017-06-01 06:48:11: ...checking database 'TEMP' if PRIMARY
INFO: 2017-06-01 06:48:11: ...stopping database 'PROD'
INFO: 2017-06-01 06:48:28: ...starting up database 'PROD'
INFO: 2017-06-01 06:48:50: [5-1] Creating checkpoint NZDRU_0501 on database 'TEMP'
INFO: 2017-06-01 06:48:50: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:48:50: ...Create restore point 'NZDRU_0501' on database 'TEMP'
INFO: 2017-06-01 06:48:50: [5-2] Phase 2 - Flashing back former primary to pre-upgrade restore point
INFO: 2017-06-01 06:48:50: ...flashing back former primary 'PROD' to pre-upgrade restore point
INFO: 2017-06-01 06:49:01: [5-2] Creating checkpoint NZDRU_0502 on database 'TEMP'
INFO: 2017-06-01 06:49:01: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:49:02: ...Create restore point 'NZDRU_0502' on database 'TEMP'
INFO: 2017-06-01 06:49:02: [5-3] Phase 3 - Converting primary to physical standby database
INFO: 2017-06-01 06:49:02: ...Converting primary 'PROD' to physical standby database
INFO: 2017-06-01 06:49:03: [5-3] Creating checkpoint NZDRU_0503 on database 'TEMP'
INFO: 2017-06-01 06:49:03: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:49:03: ...Create restore point 'NZDRU_0503' on database 'TEMP'
INFO: 2017-06-01 06:49:37: ...Create restore point 'NZDRU_0504' on database 'TEMP'

INFO: 2017-06-01 06:49:37: [5-4] Creating checkpoint NZDRU_0504 on database 'TEMP'
INFO: 2017-06-01 06:49:37: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:49:37: ...Create restore point 'NZDRU_0504' on database 'TEMP'
SUCCESS: 2017-06-01 06:49:38: Flashback former primary to pre-upgrade restore point and convert to physical complete

MacroStep8 - Stage 6 Former primary recovers...
INFO: 2017-06-01 06:49:38: [6-1] Run media recovery through upgrade redo
INFO: 2017-06-01 06:49:38: ...checking database 'PROD' role
INFO: 2017-06-01 06:49:38: ...checking database 'PROD' if mounted
INFO: 2017-06-01 06:49:44: ...checking database 'TEMP' role
INFO: 2017-06-01 06:49:44: ...checking database 'TEMP' if open as READ WRITE
INFO: 2017-06-01 06:49:50: [6-1] Upgrade redo region identified as scn range 1110493, 1566013
INFO: 2017-06-01 06:49:50: [6-1] Switch logs on the primary database 'TEMP'
INFO: 2017-06-01 06:49:52: [6-1] Copy /u01/app/oracle/product/19.7.0.0/dbhome_1/dbs/orapwTEMP /u01/app/oracle/product/19.7.0.0/dbhome_1/dbs/orapwPROD
INFO: 2017-06-01 06:49:52: [6-1] Add a TNS entry for 'PROD' on 'OraDb19700_home1'
INFO: 2017-06-01 06:49:55: [6-1] Set _transient_logical_clear_hold_mrp_bit=true on 'PROD'
INFO: 2017-06-01 06:49:55: [6-1] Start Media recovery, it will take a while...
INFO: 2017-06-01 06:50:10: [6-1] Switch logs on the primary database 'TEMP'
INFO: 2017-06-01 06:50:17: [6-1] Waiting for media recovery to initialize v$recovery_progress
INFO: 2017-06-01 06:50:36: ...monitoring media recovery's progress
INFO: 2017-06-01 06:50:36: [6-2] Creating checkpoint NZDRU_0602 on database 'TEMP'
INFO: 2017-06-01 06:50:36: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:50:36: ...Create restore point 'NZDRU_0602' on database 'TEMP'
INFO: 2017-06-01 06:50:37: recovery of upgrade redo at 4. percent - estimated complete at Jun 06:53:47
INFO: 2017-06-01 06:50:52: recovery of upgrade redo at 34 percent - estimated complete at Jun 06:51:47
INFO: 2017-06-01 06:51:08: recovery of upgrade redo at 56 percent - estimated complete at Jun 06:51:42
INFO: 2017-06-01 06:51:23: recovery of upgrade redo at 70 percent - estimated complete at Jun 06:51:49
INFO: 2017-06-01 06:51:38: recovery of upgrade redo at 82 percent - estimated complete at Jun 06:51:54
INFO: 2017-06-01 06:51:54: recovery of upgrade redo at 94 percent - estimated complete at Jun 06:51:59
INFO: 2017-06-01 06:52:09: [6-3] Creating checkpoint NZDRU_0603 on database 'TEMP'
INFO: 2017-06-01 06:52:09: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:52:09: ...Create restore point 'NZDRU_0603' on database 'TEMP'
INFO: 2017-06-01 06:52:10: ...media recovery has finished recovering through upgrad
SUCCESS: 2017-06-01 06:52:10: Former primary recovers complete

MacroStep9 - Stage 7 Switch back...
INFO: 2017-06-01 06:52:10: [7-1] Switch back to the original roles prior to the rolling upgrade
INFO: 2017-06-01 06:52:10: [7-1] Creating checkpoint NZDRU_0701 on database 'TEMP'
INFO: 2017-06-01 06:52:10: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:52:10: ...Create restore point 'NZDRU_0701' on database 'TEMP'
INFO: 2017-06-01 06:52:11: [7-1] Phase 1 - Performing the switchover...
INFO: 2017-06-01 06:52:11: [7-2] Phase 2 - Preparing media recovery for switchover...
INFO: 2017-06-01 06:52:11: ...checking database 'TEMP' if PRIMARY
INFO: 2017-06-01 06:52:11: ...checking database 'PROD' if PHYSICAL_STANDBY
INFO: 2017-06-01 06:52:11: ...checking 'TEMP' open mode
INFO: 2017-06-01 06:52:12: ...checking database 'PROD' if mounted
INFO: 2017-06-01 06:52:12: ...switch logs on the primary database 'TEMP'
INFO: 2017-06-01 06:52:14: ...start media recovery
INFO: 2017-06-01 06:52:14: ...Wait until media recovery to catch up to a recent scn
INFO: 2017-06-01 06:52:14: ...Wait for the 'apply lag' to fall below 30 secs
INFO: 2017-06-01 06:52:15: ...Switch logs on primary database 'TEMP'
INFO: 2017-06-01 06:52:46: ...Apply lag measured at 31 seconds
INFO: 2017-06-01 06:52:47: ...Switch logs on primary database 'TEMP'
INFO: 2017-06-01 06:52:49: ...Apply lag measured at 3 seconds

INFO: 2017-06-01 06:53:13: [7-3] Creating checkpoint NZDRU_0703 on database 'TEMP'
INFO: 2017-06-01 06:53:13: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:53:14: ...Create restore point 'NZDRU_0703' on database 'TEMP'
INFO: 2017-06-01 06:53:14: [7-4] Phase 4 - confirm PROD has witnessed the role change
INFO: 2017-06-01 06:53:15: [7-4] Creating checkpoint NZDRU_0704 on database 'TEMP'
INFO: 2017-06-01 06:53:15: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:53:15: ...Create restore point 'NZDRU_0704' on database 'TEMP'
INFO: 2017-06-01 06:53:15: ***************************************************
INFO: 2017-06-01 06:53:15: [7-5] Phase 5 - Switch 'PROD' to the primary role
INFO: 2017-06-01 06:53:15: ***************************************************
INFO: 2017-06-01 06:53:18: ...Open the transient logical database 'PROD'
INFO: 2017-06-01 06:53:22: [7-5] Creating checkpoint NZDRU_0705 on database 'TEMP'
INFO: 2017-06-01 06:53:22: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:53:23: ...Create restore point 'NZDRU_0705' on database 'TEMP'
INFO: 2017-06-01 06:53:23: [7-6] Phase 6 - Start media recovery
INFO: 2017-06-01 06:53:36: [7-6] Creating checkpoint NZDRU_0706 on database 'TEMP'
INFO: 2017-06-01 06:53:36: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:53:45: ...Create restore point 'NZDRU_0706' on database 'TEMP'
INFO: 2017-06-01 06:53:45: [7-7] Creating checkpoint NZDRU_0707 on database 'TEMP'
INFO: 2017-06-01 06:53:45: ...Stop media recovery on database 'TEMP'
INFO: 2017-06-01 06:53:46: ...Create restore point 'NZDRU_0707' on database 'TEMP'
SUCCESS: 2017-06-01 06:53:46: Switch back complete

MacroStep10 - Stage 8 Statistics...
INFO: 2017-06-01 06:53:46: ...statistics
#-----------------------------------------------------------------------------#
# Displays rolling upgrade statistics upon successful rolling upgrade #
#-----------------------------------------------------------------------------#
script start time: 01-Jun-17 06:17:52
script finish time: 01-Jun-17 06:53:46
total script execution time: +00 00:35:54
wait time for user upgrade: +00 00:26:43
active script execution time: +00 00:09:11
transient logical creation start time: 01-Jun-17 06:18:06
transient logical creation finish time: 01-Jun-17 06:18:51
primary to logical switchover start time: 01-Jun-17 06:47:37
logical to primary switchover finish time: 01-Jun-17 06:48:10
primary services offline for: +00 00:00:33
total time former primary in physical role: +00 00:02:34
time to reach upgrade redo:
time to recover upgrade redo: +00 00:01:33
primary to physical switchover start time: 01-Jun-17 06:52:11
physical to primary switchover finish time: 01-Jun-17 06:53:23
primary services offline for: +00 00:01:12
#-----------------------------------------------------------------------------#
INFO: 2017-06-01 06:53:55: Cleanup
SUCCESS: 2017-06-01 06:53:56: The physical rolling upgrade is complete

gDBClone.bin listdbs -verbose
Database Name Database Type Database Role     Oracle Home Name Oracle Home Location                      Home Version
------------- ------------- -------------     ---------------- ----------------------------------------- -----
TEMP          SINGLE    PHYSICAL_STANDBY  OraDb19700_home1 /u01/app/oracle/product/19.7.0.0/dbhome_1 19.7.0.0.0
PROD          SINGLE        PRIMARY           OraDb19700_home1 /u01/app/oracle/product/19.7.0.0/dbhome_1 19.7.0.0.0

13. Clone a database encrypted with Transparent Data Encryption (TDE)

In this scenario, we want use gDBClone to clone/snap database encrypted with the Transparent Data Encryption (TDE). Before to use gDBClone, you must manually copy the keystore to the duplicate database. If the keystore is not an auto login (SSO) keystore, then you must convert it to an auto login keystore at the duplicate database.

In this example we are considering ORCL as source encrypted database, ENCORCL the cloned database and SNAPENC the snapshot encrypted database

1) Copy the wallet file (ewallet.p12) from source database server to target clone database server. You can check the wallet file location on source database from sqlnet.ora file of the source database ORACLE_HOME

$ mkdir -p /u01/app/oracle/admin/ENCORCL/tde_wallet
$ scp oracle@prod-serv:/u01/app/oracle/admin/ORCL/tde_wallet/ewallet.p12 /u01/app/oracle/admin/ENCORCL/tde_wallet/

2) Modify sqlnet.ora file in target clone database ORACLE_HOME to reflect the location of the wallet file:

ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY=/u01/app/oracle/admin/ENCORCL/tde_wallet)
    )
)

In an Oracle Grid Infrastructure environment, add the TNS_ADMIN and ORACLE_UNQNAME initialization parameters to both the listener.ora file and the static listener for Data Guard Broker. The listener must be stopped and restarted after these changes are made.

The following is an example of setting the TNS_ADMIN and ORACLE_UNQNAME parameters:

(SID_DESC=(GLOBAL_DBNAME=ENCORCL.example.com) (ORACLE_HOME=/u01/app/oracle/product/19.7.0.0/dbhome_1)
(SID_NAME=ENCORCL) (ENVS="TNS_ADMIN=/u01/app/oracle/admin/ENCORCL/tde_wallet")
(ENVS="ORACLE_UNQNAME=ENCORCL"))

3) Invoke orapki utility on the target clone database server to make the wallet auto-login:

$ orapki wallet create -wallet /u01/app/oracle/admin/ENCORCL/tde_wallet \
-pwd "Welcome_1" \
-auto_login

4) You can now use gDBClone to clone the source encrypted database

gDBClone.bin clone -sdbname ORCL \
                   -sdbscan exadata316-scan \
                   -tdbname ENCORCL \
                   -tdbhome OraDb19700_home1 \
                   -dataacfs /u02/app/oracle/oradata/datastore

If you need also a database snapshot:

gDBClone.bin snap -sdbname ENCORCL -tdbname SNAPENC

The required wallet will be created under the expected location ” /u01/app/oracle/admin/SNAPENC/tde_wallet ”

Output:

[Insert code here. Use 'Paste from Word' to retain layout.]

You can avoid the “WALLET” password request if “/opt/gDBClone/WALLETpasswd_file” is present. You can create such file with the wallet password issuing:

gDBClone.bin syspwf -syspwf WALLET.passwd_file

Note: if you are going to use $ORACLE_UNQNAME into sqlnet.ora, example:

ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE)
    (METHOD_DATA =
    (DIRECTORY=/u01/app/oracle/admin/ENCORCL/$ORACLE_UNQNAME)
    )
)

You should also setup such env variable for the database using “srvctl” command:

$ srvctl setenv database -db db_unique_name -env "ORACLE_UNQNAME=val"
$ srvctl stop database -db db_unique_name
$ srvctl start database -db db_unique_name

14. Using gDBClone on ODA HA,S,M,L (Enterprise Edition)

Note: On ODA before to get database snapshots with gDBClone you must get a source database “clone” on ACFS as by default, on ODA the databases are stored on ACFS “root” filesystem and not on an ACFS filesystem snapshot.

1) Check the source database on source host (or on local ODA)

gDBClone.bin listdbs -verbose
Database Name Database Type Database Role Oracle Home Name Oracle Home Location Home Version Master/Snapshot Location/Parent
------------- ------------- ------------- ---------------- ----------------------------------------- ------------ ---------------- ----------------
DB122H1 RAC PRIMARY OraDb12201_home1 /u01/app/oracle/product/12.2.0.1/dbhome_1 12.2.0.1.0 n/a ASM

2) Make a new ODA “ACFS DB storage” for the clone database issuing:

# odacli create-dbstorage --dbname CLONE --databaseUniqueName CLONE --dbstorage ACFS
{
"jobId" : "d317a3de-99a1-4ba4-842f-bbd4ec4763f6",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "July 20, 2020 10:03:34 AM CEST",
"resourceList" : [ ],
"description" : "Database storage service creation with db name: CLONE",
"updatedTime" : "July 20, 2020 10:03:34 AM CEST"
}

3) Get a clone database from the source DB running gDBClone from target ODA:

./gDBClone.bin clone \
-sdbname DB122H1 \
-sdbscan rcvm-scan \
-tdbname CLONE \
-tdbhome OraDB12201_home1 \
-dataacfs /u02/app/oracle/oradata/CLONE \
-recoacfs /u03/app/oracle/fast_recovery_area \
-redoacfs /u04/app/oracle/redo/CLONE

output:

MacroStep1 - Getting information and validating setup...

Please enter the 'SYS' User password for the database DB122H1:
Please re-enter the 'SYS' user password for the database DB122H1:
INFO: 2020-07-20 11:15:08: Validating environment
INFO: 2020-07-20 11:15:08: Checking superuser usage
INFO: 2020-07-20 11:15:08: Checking if target database name 'CLONE' is a valid name
INFO: 2020-07-20 11:15:08: Checking if target database home 'OraDB12201_home1' exists
INFO: 2020-07-20 11:15:08: Checking if Oracle Restart
INFO: 2020-07-20 11:15:08: Checking ping to host 'rcvm-scan'
INFO: 2020-07-20 11:15:08: Getting ORACLE_BASE path from orabase
INFO: 2020-07-20 11:15:08: Checking if target database 'CLONE' exists
INFO: 2020-07-20 11:15:08: Checking 'CLONE' snapshot existence on '/u02/app/oracle/oradata/CLONE'
INFO: 2020-07-20 11:15:08: Checking registered instance 'CLONE'
INFO: 2020-07-20 11:15:16: Checking listener on 'scaoda811-scan:1521'
INFO: 2020-07-20 11:15:16: Checking ACFS command options
INFO: 2020-07-20 11:15:16: Checking if '/u02/app/oracle/oradata/CLONE' is an ACFS file system
INFO: 2020-07-20 11:15:16: Checking if '/u04/app/oracle/redo/CLONE' is an ACFS file system
INFO: 2020-07-20 11:15:16: Checking if '/u03/app/oracle/fast_recovery_area' is an ACFS file system
INFO: 2020-07-20 11:15:19: Checking source and target database version
INFO: 2020-07-20 11:15:19: Checking source database size
INFO: 2020-07-20 11:15:20: Checking source database role
INFO: 2020-07-20 11:15:20: Checking source log mode
INFO: 2020-07-20 11:15:21: Checking Flash Cache setting
SUCCESS: 2020-07-20 11:15:21: Environment validation complete

MacroStep2 - Setting up clone environment...
INFO: 2020-07-20 11:15:21: Creating local pfile
INFO: 2020-07-20 11:15:22: Creating local password file
INFO: 2020-07-20 11:15:22: Creating local Audit folder
INFO: 2020-07-20 11:15:22: Checking local auxiliary listener
INFO: 2020-07-20 11:15:22: Creating local auxiliary listener
INFO: 2020-07-20 11:15:22: Starting auxiliary listener
INFO: 2020-07-20 11:15:22: Sleeping 60 secs, please wait
INFO: 2020-07-20 11:16:22: Setting up ACFS storage
INFO: 2020-07-20 11:16:22: Creating dynamic scripts
INFO: 2020-07-20 11:16:23: Cloning to target ACFS from host 'rcvm-scan'
INFO: 2020-07-20 11:16:23: Creating RMAN script for spfile target to ACFS
INFO: 2020-07-20 11:16:23: Instantiating clone database
SUCCESS: 2020-07-20 11:16:23: Environment setup complete

MacroStep3 - Cloning database 'DB122H1'...
INFO: 2020-07-20 11:16:23: Getting cluster_interconnects values on 'DB122H1'
INFO: 2020-07-20 11:16:24: Database clone from active database in progress, please wait
INFO: 2020-07-20 11:16:24: (this can take a while depending on database size and/or network speed)
INFO: 2020-07-20 11:19:33: Cloned database restore until SCN:1487459 done successfully
INFO: 2020-07-20 11:19:33: Moving spfile
INFO: 2020-07-20 11:20:30: Register 'CLONE' database as cluster resource
INFO: 2020-07-20 11:20:31: Updating spfile
INFO: 2020-07-20 11:20:33: Updating local pfile
INFO: 2020-07-20 11:20:33: Checking database name
INFO: 2020-07-20 11:20:34: Modifying DB instance
INFO: 2020-07-20 11:20:35: Setup ACFS dependency
SUCCESS: 2020-07-20 11:20:37: Clone database 'CLONE' created successfully

INFO: 2020-07-20 11:20:37: Starting database 'CLONE'
SUCCESS: 2020-07-20 11:20:52: Successfully created clone database 'CLONE'

3) Check the new 'clone' database

gDBClone.bin listdbs
Database Name Database Type Database Role Master/Snapshot Location/Parent
------------- ------------- ------------- --------------- ---------------
CLONE SINGLE PRIMARY Master /u02/app/oracle/oradata/CLONE/.ACFS/snaps/

Note as the database is created under the ACFS snapshot and nto on dbstorage root filesystem:

# tree /u02/app/oracle/oradata/CLONE
/u02/app/oracle/oradata/CLONE
└── lost+found

1 directory, 0 files

# tree /u02/app/oracle/oradata/CLONE/.ACFS/snaps/
/u02/app/oracle/oradata/CLONE/.ACFS/snaps/
└── CLONE
    └── CLONE
        ├── datafile
        │   ├── o1_mf_sysaux_hkbrbt0y_.dbf
        │   ├── o1_mf_system_hkbrbsg9_.dbf
        │   ├── o1_mf_temp_hkbrf3wb_.tmp
        │   ├── o1_mf_undotbs1_hkbrbtjx_.dbf
        │   └── o1_mf_users_hkbrbyop_.dbf
        └── spfileCLONE.ora

3 directories, 6 files

You could register the new clone database to the dcs-agent so it can be managed by the dcs-agent stack also. In order to do so you must de-register the clone db into the cluster (done by gDBClone) as “ odacli register-database” will fail otherwise, the steps are as following:

1) stop the database (as oracle user):

srvctl stop database -d CLONE

2) de-register the database (as oracle user) from the cluster:

$ srvctl remove database -d CLONE

3) startup the database using SQL*Plus (as oracle user):

$ export ORACLE_SID=CLONE
$ sqlplus / as sysdba
SQL> startup

4) Register the databse on DCS (as root user)

odacli register-database --dbclass OLTP --dbshape odb1 --dbtype SI --servicename CLONE

-->

{
"jobId" : "a8298f61-2821-4aff-8da1-b5614e34d786",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "July 20, 2020 11:31:44 AM CEST",
"resourceList" : [ ],
"description" : "Database service registration with db service name: CLONE",
"updatedTime" : "July 20, 2020 11:31:44 AM CEST"
}

check the new registered database (from DCS propsective)

# odacli list-databases

ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
dd4dcb65-bf89-4783-979f-7b3cec2a7c66 CLONE Si 12.2.0.1.200714 false Oltp Odb1 Acfs Configured 3c405be6-a2eb-4909-93cb-e9c8a4203420

check the new registered database (from gDBClone propsective)

gDBClone.bin listdbs

Database Name Database Type Database Role Master/Snapshot Location/Parent
------------- ------------- ------------- --------------- ---------------
CLONE SINGLE PRIMARY Master /u02/app/oracle/oradata

Note: on registering the database on ODA DCS, db_create_file_dest will be updated to the target ACFS root filesystem (in this example: '/u02/app/oracle/oradata/CLONE'). You should set it to the right ACFS snapshot path (example) instead, issuing:

alter system set db_create_file_dest='/u02/app/oracle/oradata/CLONE/.ACFS/snaps/CLONE' scope=both;

Note: on registering the database on ODA DCS, db_recovery_file_dest will be updated to '/u03/app/oracle/fast_recovery_area', gDBClone is expecting db_recovery_file_dest set to <path>/<DB name>, example: '/u03/app/oracle/fast_recovery_area/CLONE'. You should set db_recovery_file_dest to the right expected value issuing (example):

ALTER SYSTEM SET db_recovery_file_dest='/u03/app/oracle/fast_recovery_area/CLONE' SCOPE=SPFILE;

You can now leverage on the gDBClone snapshot feature:

gDBClone.bin snap -sdbname CLONE -tdbname SNAP

Note: DCS-Agent does not support databases created using the “gDBClone snap” feature.

15. Migrate a database from OCIC to OCI using gDBClone

In this scenario, we want use gDBClone to migrate a database running on Oracle Cloud Infrastructure Classic (OCIC) to Oracle Cloud Infrastructure (OCI).

Requirements

Source database on OCIC must have Access Rule “ora_p2_dblistener” enabled
Source database time zone must be available on target DB home (DST Patches), check the timezone in use by the database using the following query:

col PROPERTY_NAME format a30
col VALUE format a5
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    28
DST_SECONDARY_TT_VERSION           0
DST_UPGRADE_STATE               NONE

In this example, time zone ver.28 must be present on OCI $ORACLE_HOME/oracore/zoneinfo:

[root@OCI ~]# ls -l /u01/app/oracle/product/12.1.0.2/dbhome_1/oracore/zoneinfo/*28*
-rw-r--r-- 1 oracle oinstall 53922 May 17 03:55
/u01/app/oracle/product/12.1.0.2/dbhome_1/oracore/zoneinfo/readme_28.txt
-rw-r--r-- 1 oracle oinstall 782585 May 17 03:55
/u01/app/oracle/product/12.1.0.2/dbhome_1/oracore/zoneinfo/timezlrg_28.dat
-rw-r--r-- 1 oracle oinstall 341401 May 17 03:55
/u01/app/oracle/product/12.1.0.2/dbhome_1/oracore/zoneinfo/timezone_28.dat

Steps

1. Make on OCI targert BM a new “DB storage” for the target database issuing:

[root@OCI ~] # dbcli create-dbstorage --dbname RC12COCI --dbstorage ACFS
{
"jobId" : "2275567f-d8ae-46a0-bd76-0745d3c6a5f4",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "October
04, 2017 11:17:03 AM UTC",
"resourceList" : [ ],
"description" : "Database storage service creation with db name: RC12COCI",
"updatedTime" : "October 04, 2017 11:17:03 AM UTC"
}

2. Setup the wallet file on OCI targert BM

- Copy the wallet file (ewallet.p12) from the OCIC database server to target OCI database server. You can check the wallet file location on source database from sqlnet.ora file of the source database
ORACLE_HOME

$ mkdir -p /opt/oracle/dcs/commonstore/wallets/tde/RC12COCI
$ scp oracle@ocic:/u01/app/oracle/admin/RC12COCI/tde_wallet/ewallet.p12
oracle@oci:/opt/oracle/dcs/commonstore/wallets/tde/RC12COCI/
$ chmod 600 /opt/oracle/dcs/commonstore/wallets/tde/RC12COCI/ewallet.p12

- Modify sqlnet.ora file on target BMC database ORACLE_HOME to reflect the location of the wallet file:

ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY=/opt/oracle/dcs/commonstore/wallets/tde/RC12COCI)
    )
)

- Invoke orapki utility on the target clone database server to make the wallet auto-login (the password is an example):

$ orapki wallet create -wallet /opt/oracle/dcs/commonstore/wallets/tde/RC12COCI \
-pwd "Welcome_1" \
-auto_login

3. Execugte gDBClone

- Create the password file for gDBClone:

[root@OCI ~]# gDBClone.bin syspwf -syspwf /opt/gDBClone/SYS.passwd
Please enter the SYS User password :
Please re-enter the SYS user password :
SYS password file created as /opt/gDBClone/SYS.passwd

- run gDBClone as following (you could check the sdbname with “lsnrctl service” command):

[root@OCI ~]# nohup /opt/gDBClone/gDBClone.bin clone \
                                               -sdbname RC12C.gboracle88888.oraclecloud.internal \
                                               -sdbscan 140.85.10.81 \
                                               -tdbname RC12COCI \
                                               -tdbhome OraDB12102_home1 \
                                               -dataacfs /u02/app/oracle/oradata/RC12COCI \
                                               -redoacfs /u03/app/oracle/redo \
                                               -recoacfs /u03/app/oracle/fast_recovery_area \
                                               -oci \
                                               -syspwf /opt/gDBClone/SYS.passwd &

-->

MacroStep1 - Getting information and validating setup...
INFO: 2017-05-23 08:59:42: Validating environment
INFO: 2017-05-23 08:59:42: Checking superuser usage
INFO: 2017-05-23 08:59:42: Checking if target database name 'RC12CBMC' is a valid name
INFO: 2017-05-23 08:59:42: Checking if target database home 'OraDB12102_home1' exists
WARNING: 2017-05-23 08:59:42: ORACLE_BASE is not set
INFO: 2017-05-23 08:59:42: Got Oracle Base from orabase
INFO: 2017-05-23 08:59:42: Checking if target database 'RC12CBMC' exists
INFO: 2017-05-23 08:59:43: Checking 'RC12CBMC' snapshot existence on '/u02/app/oracle/oradata/RC12CBMC'
INFO: 2017-05-23 08:59:43: Checking registered instance 'RC12CBMC'
INFO: 2017-05-23 08:59:43: Checking listener on 'rc12c:1521'
INFO: 2017-05-23 08:59:43: Checking source and target database version
INFO: 2017-05-23 08:59:49: Checking source log mode
INFO: 2017-05-23 08:59:52: Checking Flash Cache setting
INFO: 2017-05-23 08:59:55: Checking ACFS command options
INFO: 2017-05-23 08:59:55: Checking if '/u02/app/oracle/oradata/RC12CBMC' is an ACFS file system
INFO: 2017-05-23 08:59:55: Checking if '/u03/app/oracle/redo' is an ACFS file system
INFO: 2017-05-23 08:59:55: Checking if '/u03/app/oracle/fast_recovery_area' is an ACFS file system
SUCCESS: 2017-05-23 08:59:55: Environment validation complete
MacroStep2 - Setting up clone environment...
INFO: 2017-05-23 08:59:55: Creating local pfile
INFO: 2017-05-23 08:59:58: Creating local password file
INFO: 2017-05-23 08:59:58: Creating local Audit folder
INFO: 2017-05-23 08:59:58: Creating local auxiliary listener
INFO: 2017-05-23 08:59:58: Starting auxiliary listener
INFO: 2017-05-23 08:59:58: Sleeping 60 secs, please wait
INFO: 2017-05-23 09:00:58: Setting up ACFS storage
INFO: 2017-05-23 09:00:58: Creating dynamic scripts
INFO: 2017-05-23 09:00:59: Cloning to target ACFS from host '140.85.10.81'
INFO: 2017-05-23 09:00:59: Creating RMAN script for spfile target to ACFS
INFO: 2017-05-23 09:00:59: Instantiating clone database
SUCCESS: 2017-05-23 09:00:59: Environment setup complete
MacroStep3 - Cloning database 'RC12COPC.gboracle88888.oraclecloud.internal'...
INFO: 2017-05-23 09:00:59: please wait (this can take a while depending on database size and/or network speed)
INFO: 2017-05-23 09:12:18: Moving spfile
INFO: 2017-05-23 09:12:51: Updating local dbs pfile/spfile
INFO: 2017-05-23 09:12:51: Register 'RC12CBMC' database as cluster resource
INFO: 2017-05-23 09:12:55: Checking database name
INFO: 2017-05-23 09:12:55: Modifying DB instance
INFO: 2017-05-23 09:12:56: Setup ACFS dependency
INFO: 2017-05-23 09:12:58: Database 'RC12CBMC' dependency to
'/u02/app/oracle/oradata/RC12CBMC,/u03/app/oracle/redo,/u03/app/oracle/fast_recovery_area' done successfully
INFO: 2017-05-23 09:12:58: Starting database 'RC12CBMC'
SUCCESS: 2017-05-23 09:13:10: Successfully created clone database 'RC12CBMC'
INFO: 2017-05-23 09:13:10: Cleaning up the setup

Check the database creation:

gDBClone.bin listdbs

Database Name Database Type Database Role Master/Snapshot Location/Parent
------------- ------------- ------------- --------------- ---------------
RC12CBMC SINGLE PRIMARY Master /u02/app/oracle/oradata/RC12COCI

You could register the new clone database to the dcs-agent so it can be managed by the dcs-agent stack also. In order to do so the “COMPATIBLE” parameter must be in the form of 4 numbers (x.y.z.w) example: “12.1.0.2” (12.1.0.2.0 is not valid) and the database password must have at least two
uppercase, two lowercase, two special chars and two numbers (example: “WElcome__12”). You must de- register the clone db into the cluster (done by gDBClone) as “ dbcli register-database” will failotherwise, the steps are as following:

1. set the compatible parameter

SQL> alter system set compatible='12.1.0.2' scope=spfile;

2. Set the SYS password to support “dbcli registration”

SQL> alter user sys identified by "WElcome__12";

3. stop the database

$ srvctl stop database -d RC12CBMC

4. de-register the database

$ srvctl remove database -d RC12CBMC

5. startup the database using SQL*Plus

$ export ORACLE_SID=RC12CBMC
$ sqlplus / as sysdba
SQL> startup

6. Run the ‘ odacli register-database’ command

[root@BMC ~]# dbcli register-database \
--dbclass OLTP \
--dbshape odb2 \
--servicename RC12CBMC.gboracle60892.oraclecloud.internal \
-p
Password for SYS:

{
"jobId" : "36a28b76-43d7-4fe8-a8ff-11f96bc08e26",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "May 23, 2017 09:32:52 AM UTC",
"resourceList" : [ ],
"description" : "Database service registration with db service name: RC12CBMC.gboracle60000.oraclecloud.internal",
"updatedTime" : "May 23, 2017 09:32:52 AM UTC"
}

Check the database creation:

# dbcli list-databases

ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
19e4b52a-4ef0-4aaa-8d56-cae6dd0b069d RC12CBMC Si 12.1.0.2 false Oltp Odb2 Acfs Configured 6a705be6-a2ab-4909-93cb-e9d8a4203420

16. Test & Dev Management environment example

Test System Configuration

A two-node test & dev cluster: TestCluster1
Database on test and dev cluster
- TestCluster1 server running
  - 12.1 RACM database ‘RAC’
Database on production server
- ProdRAC1/2 server running ‘SALES’ RAC database
- ProdRAC3/4 server running ‘INV’ RACOne database
ACFS file systems: /acfs and /cloudfs

It is assumed that the user is running a 12.1 production database ‘SALES’ on ProdRAC1/2 cluster and wants to create a clone of that database on a test & dev cluster for testing and certification purposes. Therefore, the user needs to have two snapshots of the database for this purpose

The following are the steps necessary to provision two snapshot databases for test and dev purposes:

1) List the current cloned databases on the test cluster.

2) Create a clone of RAC database ‘SALES’ on the test cluster and name it ‘SALESM’. Create the clone on /acfs file system

[root@TestCluster1 ~]# gDBClone.bin clone -sdbname SALES -sdbhost ProdRAC1 -tdbname SALESM -dataacfs /acfs -racmod 2

3) List the current cloned databases on the test cluster.

4) Create a read-write snapshot of SALESM clone database called SALEST1 and configure it as a single instance database. Also, create a read-write snapshot of SALEM clone database called SALEST2 and configure it as a RAC database.

[root@TestCluster1 ~]# gDBClone.bin snap -sdbname SALESM -tdbname SALEST1
[root@TestCluster1 ~]# gDBClone.bin snap -sdbname SALESM -tdbname SALEST2 -racmod 2

5) List the current cloned databases on the test cluster.

gdbclone.bin listdbs
Database Name Database Type Database Role Master/Snapshot Location/Parent
------------- ------------- ------------- --------------- ---------------
RACM    RAC           PRIMARY       Master    /cloudfs
SALESM        RAC           PRIMARY       Master    /acfs
SALEST1       SINNGLE       PRIMARY       Snapshot        SALESM
SALEST2       RAC           PRIMARY       Snapshot        SALES

Conlusion

Managing test and dev environments does not have to be complex. The Oracle Cloud File System coupled with the gDBClone script provides powerful, flexible and simple tools that ease management of test and dev servers and reduce management complexity. You can finally contain the sprawling cost of storage by using the ACFS point-in-time snapshot technology; and therefore, realize significant storage savings. Many sparse snapshot clones can be created for parallel test and development purpose and only require a fraction of the storage. The Oracle Cloud File System is bundled with Oracle Grid Infrastructure and installs automatically on every cluster. Refreshing and recycling test databases have never been easier. The gDBClone script allows you to create clones and snapshots, list and delete them in one simple command. This is the type of agility businesses need to adapt to changing requirements in their IT organization.

Appendix - A

Clone Location

Using the following options:

-dataacfs Database datafiles target ACFS storage
-redoacfs Database redologs target ACFS storage (default dataacfs)
-recoacfs Database recovery target ACFS storage (default dataacfs)

automatically gDBClone will create:

an ACFS snapshot under "-dataacfs <acfs mount point>" called as tdbname and the database datafiles will be stored in such place
a folder called tdbname under "-redoacfs <acfs mount point>" and "-recoacfs <acfs mount point>" if such options are provided

gDBClone clone -sdbname O12C -sdbscan slcac458-scan -tdbname CO12C -tdbhome OraDb12102_home1
-dataacfs /u02/app/oracle/oradata/datastore
-redoacfs /u01/app/oracle/oradata/datastore
-recoacfs /u01/app/oracle/fast_recovery_area/datastore

-->
# tree /u02/app/oracle/oradata/datastore/.ACFS/snaps/CO12C
/u02/app/oracle/oradata/datastore/.ACFS/snaps/CO12C
└── CO12C
    ├── datafile
    │ ├── o1_mf_sysaux_d0wd0jj3_.dbf
    │ ├── o1_mf_system_d0wd0j6g_.dbf
    │   ├── o1_mf_temp_d0wd2qt1_.tmp
    │   ├── o1_mf_undotbs1_d0wd0jmb_.dbf
    │   ├── o1_mf_undotbs2_d0wd0qt2_.dbf
    │   └── o1_mf_users_d0wd0r1r_.dbf
    └── spfileCO12C.ora
.
# tree /u01/app/oracle/oradata/datastore/CO12C/
/u01/app/oracle/oradata/datastore/CO12C/
├── CO12C
    │ ├── onlinelog
    │ ├── o1_mf_1_d0wd2h1n_.log
    │ ├── o1_mf_2_d0wd2m7t_.log
    │ ├── o1_mf_3_d0wd26qo_.log
    │ └── o1_mf_4_d0wd2boz_.log
    └── control01.ctl
.
# tree /u01/app/oracle/fast_recovery_area/datastore/CO12C/
/u01/app/oracle/fast_recovery_area/datastore/CO12C/
└── CO12C
    ├── archivelog
    │   └── 2016_10_24
    │       ├── o1_mf_1_4_d0wd13wv_.arc
    │       ├── o1_mf_1_5_d0wd148z_.arc
    │       ├── o1_mf_1_6_d0wd14jr_.arc
    │       ├── o1_mf_2_6_d0wd14sl_.arc
    │       └── o1_mf_2_7_d0wd153n_.arc
    └── backupset
        └── 2016_10_24
            └── o1_mf_nnsnf_TAG20161024T090133_d0wd2xow_.bkp

Snap Location

When gDBClone snap is in use the database location is made based on the following assumptions:

Database datafiles will be stored following "db_create_file_dest" source database location.

Example having:

db_create_file_dest='/u02/app/oracle/oradata/datastore/.ACFS/snaps/<sourceDBname>'

the snapshot database will store the dbf under

/u02/app/oracle/oradata/datastore/.ACFS/snaps/<snapDBname>/<uppercasesnapDBname>/datafile

Database Redologs will follow "db_create_online_log_dest_1"
Example having:

db_create_online_log_dest_1='/u01/app/oracle/oradata/datastore/<sourceDBname>'

the snapshot database will store the dbf under

/u01/app/oracle/oradata/datastore/<snapDBname>

Database recovery area will follow "db_recovery_file_dest"
Example having:

db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/datastore/<sourceDBname>'

the snapshot database will store the dbf under

/u01/app/oracle/fast_recovery_area/datastore/<snapDBname>

Clone resume

In case of RMAN error/failure during the clone process, gDBClone wont cleanup the environment leaving what done until the failure. Restarting the same gDBClone clone command it will print errors as the previous db structure is (could be) present. In such case using "-resume" command option (it ll ask for confirmation), gDBClone will skips the errors and it will restarts the "rman duplicate".
Such feature is especially useful in case a huge database clone is failing. Restarting the process RMAN will starts from where it was failing.

Standby option

The standby option (usable doing clone/snap) is as following:

-standby [-pmode maxperf|maxavail|maxprot] [-activedg] [-rtapply][-dgbroker [-dgbpath1 <dgb config path>][-dgbpath2 <dgb config path>]]

Where
-standby The clone/snap will be a physical standby database
-pmode Standby option: maxperf/maxavail/maxprot (default maxperf)
-activedg Enable Active Dataguard
-rtapply Enable real time apply
-dgbroker Enable DG Broker
-dgbpath1 DG Broker remote configuration file path
-dgbpath2 DG Broker remote configuration file path

-pmode

If "-pmode" is used and ne maxperf LOG_ARCHIVE_DEST_2--> AFFIRM/ASYNC
If "-pmode" is not in use LOG_ARCHIVE_DEST_2--> NOAFFIRM/ASYNC

-activedg

Using "-activedg" the clone/snap database will be register as "-r physical_standby", "-s "READ ONLY"
Without "-activedg" the clone/snap database will be register as "-r physical_standby", "-s "mount"

-rtapply

If "-rtapply" is in use

"ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT USING CURRENT LOGFILE"

If "-rtapply" is not in use

"ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION"

DG broker Setup

During the “clone/snap” as standby and as option, it’s possible configure Oracle Data Guard Broker.

In case of database “clone” if the DG Broker is not configured yet you must provide the DG Broker configuration file path (-dgbpath1/-dgbpath2). If you don’t provide ‘-dgbpath2’, the same ‘-dgbpath1’ will be used.

In case of Database “snap”,

dgbpath1 will be set to “<dataacfs>/.ACFS/snaps/<db_name>/dgbc1<db_name>.dat
dgbpath2 will be set to “<recoacfs>/<db_name>/dgbc2<db_name>.dat

gDBClone “-pfile” option

As discussed early on this technical paper, if you need to decrease/increase SGA footprint, you can leverage on “-pfile” option. Here and example of the usage.

Example: gDBClone_pfile12c.conf

aq_tm_processes = 0
archive_lag_target = 900
bitmap_merge_area_size = 0
compatible = 12.1.0.2
create_bitmap_area_size = 0
db_block_checking = 'FULL'
db_block_checksum = 'FULL'
db_file_multiblock_read_count = 16
db_files = 500
db_lost_write_protect = 'FULL'
fast_start_parallel_rollback = 'HIGH'
hash_area_size = 0
job_queue_processes = 10
log_archive_format = '%t_%s_%r.arc'
log_archive_max_processes = 10
log_archive_trace = 0
open_cursors = 1000
parallel_execution_message_size = 8192
parallel_max_servers = 80
pga_aggregate_target = 1G
processes = 150
recovery_parallelism = 8
remote_login_passwordfile = 'EXCLUSIVE'
sec_case_sensitive_logon = 'FALSE'
session_cached_cursors = 100
sessions = 250
sga_max_size = 1G
sga_target = 1G
shared_pool_reserved_size = 22649241
shared_pool_size = 704643072
shared_servers = 0
sort_area_retained_size = 0
sort_area_size = 65536
undo_management = 'AUTO'
gDBClone_pfile12c.conf
undo_retention = 43200

Getting a database clone:

gDBClone.bin clone -sdbname ORCL \
                   -sdbscan exadata316-scan \
                   -tdbname CLONE \
                   -tdbhome OraDb12102_home1 \
                   -dataacfs /u02/app/oracle/oradata/datastore \
                   -pfile /opt/gDBClone/gDBClone_pfile12c.conf

Getting a database snapshot:

gDBClone.bin snap -sdbname STDBY -tdbname SNAP \
-pfile /opt/gDBClone/gDBClone_pfile12c.conf

Debug Information

If for any reason gDBClone is failing, you may have more detailed information from the log created under

“/opt/gDBClone/out/log/gDBClone_<pid>.log”

If on cloning a source database you need RMAN debug log, you can use “-rmandbg” command option;
the trace file will be crated as “/tmp/rman_debug.log”

References

NOTE:2363679.1 - Bck2Cloud - “1-Button” Cloud Backup/Restore Automation Utility
https://www.oracle.com/database/technologies/oracle-cloud-backup-downloads.html
http://www.oracle.com/technetwork/indexes/samplecode/gdbclone-download-2295388.html
NOTE:949322.1 - Oracle11g Data Guard: Database Rolling Upgrade Shell Script
NOTE:884522.1 - How to Download and Run Oracle's Database Pre-Upgrade Utility
NOTE:2485457.1 - AutoUpgrade Tool

Was this document helpful?

Yes

Document Details


	Type:
	Status:
	Last Major Update:
	Last Update:


	TECHNICAL BRIEF
	PUBLISHED
	03-Oct-2023
	03-Oct-2023

Related Products

Oracle Database - Enterprise Edition

Oracle Database Cloud Service

Oracle Database Backup Service

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine)

Oracle Cloud Infrastructure - Database Service

Information Centers

Information Center: Transportable Tablespaces (TTS) for Oracle Database [1461278.2]

19c Database Upgrade - Self Guided Assistance with Best Practices [1919.2]

Index of Oracle Database Information Centers [1568043.2]

インフォメーション・センター: データベースおよび Enterprise Manager 日本語ドキュメント [1946305.2]

Information Center: Data Warehousing [1487754.2]

Document References

Bck2Cloud - “1-Button” Cloud Backup/Restore Automation Utility [2363679.1]

Oracle11g Data Guard: Database Rolling Upgrade Shell Script [949322.1]

How to Download and Run Oracle's Database Pre-Upgrade Utility [884522.1]

AutoUpgrade Tool [2485457.1]

Incremental Database recovery -RMAN

2023-08-09T00:21:00.002+05:30

SOURCE (SourceDB - HOST - DBHOST01)

TARGET (TargetDB - HOST - DBHOST02)

STEP 1

ON SOURCE

Take a full backup of source database

Rman target /

run

{

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/home/oracle/opt/dpsapps/rmanagent/lib/libddobk.so,ENV=(STORAGE_UNIT=t1-OracleDB01,BACKUP_HOST=DBHOST01,RMAN_AGENT_HOME=/home/oracle/opt/dpsapps/rmanagent/)' FORMAT './%d/daily/%T_%d_Full_Daily_%s_%U';

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO './%d/daily/%T_%d_Control_%F';

BACKUP KEEP UNTIL TIME 'SYSDATE+8' RESTORE POINT 'MANUAL_FULL_03142023' FORCE NOEXCLUDE AS BACKUPSET TAG 'MANUAL_FULL_03142023' DATABASE;

BACKUP AS BACKUPSET TAG '%TAG' ARCHIVELOG ALL NOT BACKED UP 2 TIMES FORMAT './%d/daily/%T_%d_arch_%s_%U';

CROSSCHECK BACKUP;

DELETE NOPROMPT EXPIRED BACKUPSET;

DELETE NOPROMPT OBSOLETE DEVICE TYPE 'SBT_TAPE';

DELETE NOPROMPT BACKUP OF ARCHIVELOG UNTIL TIME 'SYSDATE-8';

}

Take backup of pfile & copied to /u03/app/oracle/utils/temp/*.ora on target server

create pfile='/tmp/initSource.ora' from spfile;

STEP 2

On Target

Create the target database using BUI before proceeding with below steps.

Remove below files and directories

rm -rf /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_38/dbs/*

rm -rf /u02/app/oracle/oradata/SourceDB/dbs/spfileSourceDB.ora

rm -rf /u04/app/oracle/redo/SourceDB/SourceDB/controlfile/cntrlSourceDB.ctl

rm -rf /u02/app/oracle/oradata/SourceDB/SourceDB/datafile/*

rm -rf /u03/app/oracle/fast_recovery_area/SourceDB/*

rm -rf/u04/app/oracle/redo/SourceDB/SourceDB/onlinelog/*

ls -lrt /u02/app/oracle/oradata/SourceDB/dbs/spfileSourceDB.ora

ls -lrt /u04/app/oracle/redo/SourceDB/SourceDB/controlfile/cntrlSourceDB.ctl

ls -lrt /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_4/dbs/

ls -lrt /u02/app/oracle/oradata/SourceDB/SourceDB/datafile/

ls -lrt /u03/app/oracle/fast_recovery_area/SourceDB/

ls -lrt /u04/app/oracle/redo/SourceDB/SourceDB/onlinelog/

mkdir -p /u03/app/oracle/fast_recovery_area/SourceDB/onlinelog/

mkdir -p /u03/app/oracle/fast_recovery_area/SourceDB/controlfile/

Identify the latest controlfile autobackup and use that controlfile autobackup to restore the latest full backup.

STEP 3

ON TARGET

shutdown abort the TargetDB database

. oraenv

TargetDB

Sqlplus / as sysdba

Shutdown abort

STEP 4

ON TARGET

. oraenv

TargetDB

Make sure the dbid and parameter file is correct.

rman target /

run

{

set dbid=***;

startup nomount pfile='/u03/app/oracle/utils/temp/initTargetDB.ora';

allocate channel sbt type 'SBT_TAPE' parms 'BLKSIZE=1048576,SBT_LIBRARY=/home/oracle/opt/dpsapps/rmanagent/lib/libddobk.so,ENV=(STORAGE_UNIT=t1-OracleDB01,BACKUP_HOST=DBHOST01,RMAN_AGENT_HOME=/home/oracle/opt/dpsapps/rmanagent/)';

SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO './%d/daily/%T_%d_Control_%F';

restore spfile from autobackup;

shutdown immediate;

startup nomount;

alter system set listener_networks='' scope=both;

restore controlfile from autobackup;

alter database mount;

restore database;

recover database;

}

Ignore errors

archived log thread=1 sequence=15991

released channel: sbt

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

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

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

RMAN-03002: failure of recover command at 06/22/2023 13:47:22

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

STEP 5

ON SOURCE

Take manual archivelog backup

rman target /

run

{

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/home/oracle/opt/dpsapps/rmanagent/lib/libddobk.so,ENV=(STORAGE_UNIT=t1-OracleDB01,BACKUP_HOST=DBHOST02,RMAN_AGENT_HOME=/home/oracle/opt/dpsapps/rmanagent/)' FORMAT './%d/daily/%T_%d_Full_Daily_%s_%U';

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO './%d/daily/%T_%d_Control_%F';

BACKUP DEVICE TYPE 'SBT_TAPE' TAG '%TAG' ARCHIVELOG ALL NOT BACKED UP 2 TIMES FORMAT './%d/daily/%T_%d_arch_%s_%U';

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO 'SBT_TAPE';

CROSSCHECK ARCHIVELOG ALL;

DELETE NOPROMPT OBSOLETE;

DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-3' BACKED UP 2 TIMES TO DEVICE TYPE 'SBT_TAPE';

DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

}

STEP 6

On target

indentify the archivelog backup piece and catalog and perform the 2nd recovery

run

{

allocate channel sbt type 'SBT_TAPE' parms 'BLKSIZE=1048576,SBT_LIBRARY=/home/oracle/opt/dpsapps/rmanagent/lib/libddobk.so,ENV=(STORAGE_UNIT=t1-OracleDB01,BACKUP_HOST=ivndatadom01.ad.skynet,RMAN_AGENT_HOME=/home/oracle/opt/dpsapps/rmanagent/)';

CATALOG BACKUPPIECE './SourceDB/daily/20230804_SourceDB_arch_22474_ua22vc9b_22474_1_1';

recover database;

}

STEP 7

DURING CUTOVER.

ON source database

For source database, Stop all rman backup jobs and put the database in blackout mode .

Make sure no application sessions are connected to the source database.

select username, status, count(*) from v$session where username is not null and username not in ('SYS','SYSTEM','DBSNMP','SYSRAC') GROUP BY USERNAME, STATUS;

create user test identified by test default tablespace users;

grant create session,create table,select_catalog_role to test;

alter user test quota unlimited on users;

connect test/test

create table test as select * from dba_users;

insert into test select * from dba_users;

commit;

conn / as sysdba

alter system switch logfile;

alter system checkpoint;

shutdown immediate;

startup mount;

take final archivelog backup when database is in mount state.

rman target /

run

{

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'BLKSIZE=1048576,SBT_LIBRARY=/home/oracle/opt/dpsapps/rmanagent/lib/libddobk.so,ENV=(STORAGE_UNIT=t1-OracleDB01,BACKUP_HOST=DBHOST02,RMAN_AGENT_HOME=/home/oracle/opt/dpsapps/rmanagent/)' FORMAT './%d/daily/%T_%d_Full_Daily_%s_%U';

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO './%d/daily/%T_%d_Control_%F';

BACKUP DEVICE TYPE 'SBT_TAPE' TAG '%TAG' ARCHIVELOG ALL NOT BACKED UP 2 TIMES FORMAT './%d/daily/%T_%d_arch_%s_%U';

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO 'SBT_TAPE';

CROSSCHECK ARCHIVELOG ALL;

DELETE NOPROMPT OBSOLETE;

DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-3' BACKED UP 2 TIMES TO DEVICE TYPE 'SBT_TAPE';

DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

}

STEP 8

Indentify the remaining backup pieces to catalog and perform final recover.

rman target /

run

{

allocate channel sbt type 'SBT_TAPE' parms 'BLKSIZE=1048576,SBT_LIBRARY=/home/oracle/opt/dpsapps/rmanagent/lib/libddobk.so,ENV=(STORAGE_UNIT=t1-OracleDB01,BACKUP_HOST=wobdatadom01.ad.skynet,RMAN_AGENT_HOME=/home/oracle/opt/dpsapps/rmanagent/)';

CATALOG BACKUPPIECE './TargetDB/daily/20230314_TargetDB_arch_7846_l61n18qt_7846_1_1';

recover database;

}

STEP 9

Open the database with resetlogs

RMAN> alter database open resetlogs;

++++++++++++++incase if we receive below error+++++++++++++++

using target database control file instead of recovery catalog

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

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

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

RMAN-03002: failure of sql statement command at 03/09/2023 07:12:11

ORA-00392: log 2 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 2 thread 1: '/u04/app/oracle/redo/IVLABDEV/IVLABDEV/onlinelog/log_g2m1.log'

ORA-00312: online log 2 thread 1: '/u03/app/oracle/fast_recovery_area/IVLABDEV/onlinelog/log_g2m2.log'

Solution;

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS

---------- ---------- ----------

1 1 CLEARING

4 1 CLEARING

3 1 CLEARING

2 1 CLEARING_C

URRENT

SQL> alter database clear unarchived logfile group 2;

Database altered.

RMAN>alter database open resetlogs;

Statement processed

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS

---------- ---------- ----------------

1 1 CURRENT

2 1 UNUSED

3 1 UNUSED

4 1 UNUSED

COL DF# FORMAT 999

COL DF_NAME FORMAT A35

COL TBSP_NAME FORMAT A7

COL STATUS FORMAT A7

COL ERROR FORMAT A10

COL CHANGE# FORMAT 99999999

SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,

d.STATUS, r.ERROR, r.CHANGE#, r.TIME

FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t

WHERE t.TS# = d.TS#

AND d.FILE# = r.FILE#

;

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS

---------- ---------- ----------------

1 1 CURRENT

2 1 UNUSED

3 1 UNUSED

4 1 UNUSED

SQL> set lines 1000;

col MEMBER for a80;

select * from v$logfile;

SQL> SQL>

GROUP# STATUS TYPE MEMBER IS_ CON_ID

---------- ------- ------- -------------------------------------------------------------------------------- --- ----------

3 ONLINE /u04/app/oracle/redo/TargetDB/TargetDB/onlinelog/log_g3m1.log NO 0

2 ONLINE /u04/app/oracle/redo/TargetDB/TargetDB/onlinelog/log_g2m1.log NO 0

1 ONLINE /u04/app/oracle/redo/TargetDB/TargetDB/onlinelog/log_g1m1.log NO 0

2 ONLINE /u03/app/oracle/fast_recovery_area/TargetDB/onlinelog/log_g2m2.log NO 0

3 ONLINE /u03/app/oracle/fast_recovery_area/TargetDB/onlinelog/log_g3m2.log NO 0

1 ONLINE /u03/app/oracle/fast_recovery_area/TargetDB/onlinelog/log_g1m2.log NO 0

4 ONLINE /u04/app/oracle/redo/TargetDB/TargetDB/onlinelog/log_g4m1.log NO 0

4 ONLINE /u03/app/oracle/fast_recovery_area/TargetDB/onlinelog/log_g4m2.log NO 0

8 rows selected.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 2

Current log sequence 2

SQL> alter system switch logfile;

SQL> alter system checkpoint;

System altered.

SQL>

SQL> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

/u02/app/oracle/oradata/TargetDB/TargetDB/datafile/o1_mf_temp_l0gjoopm_.tmp

SQL> !ls -lrt /u02/app/oracle/oradata/TargetDB/TargetDB/datafile/o1_mf_temp_l0gjoopm_.tmp

-rw-r----- 1 oracle asmadmin 29368320 Mar 7 08:57 /u02/app/oracle/oradata/TargetDB/TargetDB/datafile/o1_mf_temp_l0gjoopm_.tmp

SQL> show user

USER is "TEST"

SQL> select count(*) from test;

COUNT(*)

----------

SQL>

SQL>select file#, status, fuzzy, error, checkpoint_change#,

to_char(checkpoint_time,'dd-mon-rrrr hh24:mi:ss') as checkpoint_time

from v$datafile_header;

[oracle@wobodaprd01n0 ~]$ srvctl stop database -d WBPLL019

[oracle@wobodaprd01n0 ~]$ srvctl start database -d WBPLL019

SQL> select dbid, name from v$database;

DBID NAME

---------- ---------

2883099068 TargetDB

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1 1 TargetDB 2883099068 PARENT 1 10-NOV-21

2 2 TargetDB 2883099068 PARENT 2499432 04-APR-22

3 3 TargetDB 2883099068 CURRENT 42147169733 14-MAR-23

STEP 10 (POST MIGRATION STEPS)

purgeLogs: Archive & Cleanup traces, logs in one command (Doc ID 2081655.1)

2023-08-09T00:13:00.001+05:30

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=221156612954064&id=2081655.1&_adf.ctrl-state=wze2k6dq0_53

Accessibility Preferences

purgeLogs: Archive & Cleanup traces, logs in one command (Doc ID 2081655.1)

In this Document

Manual cleanup, what is needed to do?

purgeLogs - Onebutton automation script

How to Install purgelogs

PurgeLogs Usage Examples

purgeLogs - GI/RDBMS cleanup

purgeLogs - OSWatcher/ExaWatcher archive & OAK/DCS cleanup (on ODA - Oracle Database Appliance)

purgeLogs - Files from "extra" given folders

purgeLogs - GI/RDBMS, TFA, OSWatcher & OAK cleanup

purgeLogs - Cleanup listener logs

Setting up purgeLogs as crontab job

PurgeLogs Considerations

PurgeLogs inside TFA

References

APPLIES TO:

Exadata Database Machine V2
Gen 2 Exadata Cloud at Customer
Oracle Database - Enterprise Edition
Real Application Clusters
Oracle Database Appliance - Version All Versions and later
Linux x86-64

ABSTRACT

This document explains how to use "purgeLogs" - a tool used to archive on OCI bucket, cleanup, traces and logs created by different Exadata/ODA (Oracle Database Appliance) components such Grid Infrastructure, RDBMS, TFA (Trace File Analyzer Collector), OSWatcher, OAK (Oracle Appliance Kit) or DCS.

HISTORY

Author: Ruggero Citton - RACPack Team
Create Date jan-2015
Update Date May-2023
Latest purgeLogs Version: 20230503 - $Revision: 2.0.1.6 $

What's new

What's new in 2.0.1-6 release:
- audit files under 'ORACLE_BASE/admin/*/adump/*' are now removed/archived
What's new in 2.0.1-5 release:
- skipping '/opt/oracle/dcs/log/jobfiles/.gi_rhp_done_tasks_<version>' file
What's new in 2.0.1-4 release:
- on Exadata, orcl cleanup/archive is cleaning/archiving up "/u02/app/oracle/cfgtoollogs/sqlpatch"
What's new in 2.0.1-3 release:
- "orc"l cleanup/archive is cleaning/archiving up "$OH/rdbms/log"
- "oda" cleanup/archive is cleaning/archiving up "/u01/app/odaorabase/oracle/cfgtoollogs/sqlpatch"
- GI cleanup is managing not grid user files under "diag/crs/<hostname>/crs/cdump"

DETAILS

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

On-premises systems such Exadata, ODA (Oracle Database Appliance) and on-cloud DBCS, ExaCS, ExaC@C, several components such Grid Infrastructure, RDBMS, TFA (Trace File Analyzer Collector), OSWatcher, OAK (Oracle Appliance Kit) or DCS are running producing several traces and logs.

Since a patch update may fail due to filesystem full or some other unexpected behavior, using purgeLog tool can be helpful to cleanup old files. If you need to keep traces & logs for a while on inexpensive storage, purgelogs is offering the possibility to automatically archive them on OCI Storage Bucket

Manual cleanup, what is needed to do?

In order to cleanup all different trace/log files, several different manual actions are needed:

From GI prospective:
- For every single “diag homes”, manually clean several different folders: Alert,Incident,Trace,Cdump,HM
- TFA repository cleanup
- GPNPClient logs
- ACFS logs (from 18c and above)

From RDBMS prospective:
- For every single “diag homes”, manually clean several different folders: Alert,Incident,Trace,Cdump,HM
- TFA repository cleanup
From OSWatcher perspective, remove manually files into

/opt/oracle/oak/oswbb/archive/
|-- oswiostat
|-- oswmeminfo
|-- oswmpstat
|-- oswnetstat
|-- oswprvtnet
|-- oswps
|-- oswslabinfo
|-- oswtop
`-- oswvmstat

In case of ExaWatcher: "/opt/oracle.ExaWatcher/archive"

From OAK perspective, remove manually files into "/opt/oracle/oak/log"

/opt/oracle/oak/log/
|-- fishwrap
|-- test
| |-- client
| |-- dcli
| |-- oak
| |-- odabaseagent
| `-- tools
`-- node
|-- adapters
|-- client
|-- dcli
|-- oak
|-- oakvmclient
|-- odabaseagent
|-- tools
`-- utils

purgeLogs - Onebutton automation script

purgeLogs script provides the ability to archive & cleanup automatically in one single command, all trace/log/core/... files above based on file ‘age’ and its able to manage files from “extra folders” too. The default retention period is 30 days for all components. purgeLog needs to be executed on all nodes.

purgeLogs - Verbs

Archive operation
- archive
Cleanup operation
- cleanup
Make encrypted password file:
- encpass
Get help info
- help

purgeLogs - Usage

Archive function

Usage:
purgelogs.bin archive --bucketname <target bucket name>
                     [--nocli
                        --ociregion <OCI region>
                        --ocinamespace <OCI namespace>
                        --ociidname <OCI user name>
                        --ociatoken <Auth Tokens password file>
                       [--proxyhost <host> --proxyport <port> [--proxyid <user ID> --proxypass <password file>]]
                     ]
                     [--parallel <max parallel upload process>]
                     [--ocicli <oci-cli path>]
                     [--ociprofile <oci-cli profile name>]
                     [--cksum]
                     [--days <days> [--aud] [--lsnr]] |
                     [--orcl <days> [--aud] [--lsnr]] |
                     [--osw <days>] |
                     [--oda <days>] |
                     [--extra '<folder>':<days> | [, '<folder>':<days>]]
                     [--rootlabel <root label>]
                     [--onefile [--filename]]
                     [--dryrun]
purgeLogs archive OPTIONS
--bucketname   <target bucket name>           OCI Bucket name
--nocli                                       OCI restAPI usage
--ociregion    <OCI region>                   Without oci-cli, you need to specify OCI region name
--ocinamespace <OCI namespace>                Without oci-cli, you need to specify OCI namespace
--ociidname    <OCI user name>                Without oci-cli, you need to specify OCI User name
--ociatoken    <Auth Tokens password file>    Without oci-cli, you need to specify OCI User Authentication token password file
--parallel     <max parallel upload process>  Maximum parallel upload process (Default: #CPUs)
--ocicli       <oci-cli path>                 OCI client path (Default: from PATH)
--ociprofile   <oci-cli profile name>         OCI client profile name (Default: DEFAUL
--cksum                                       Verify the object checksum with the local file
--rootlabel    <root label>                   Root folder on OCI Bucket (Default: /yyyymmdd/hostname/)
--onefile                                     It will make a single tar.gz file
--filename     <filename>                     Archive tar.gz filename (Default: yyyymmdd_hostname.tar.gz)
--days         <days>                         Archive orcl,tfa,osw,oda components logs & traces older then # days
--orcl         <days>                         Archive only GI/RDBMS logs & traces (Default 30 days)
--osw          <days>                         Archive only OSW archives older then # days (Default 30 days)
--oda          <days>                         Archive only ODA logs and trace older then # days (Default 30 days)
--extra       '<folder>':<days>               Archive only files in user specified folders (Default 30 days)
--aud                                         Archive Audit logs based on '-orcl <days>' option
--lsnr                                        Archive of listeners log
--dryrun                                      It will show the Archive commands w/o execute them
--help                                        Display this help and exit

Parameters "--ociatoken" and/or "--proxypass" are expecting a file generated with 'encpass' function described below. The content is the encrypted "Auth Tokens" and/or the "proxy user password"
In case of restAPI usage (--nocli) more parameters are required such:
- --ociregion
- --ocinamespace
- --ociidname
- --ociatoken
By default purgelogs archive will upload the files one by one (in parallel) and preserving the original path adding a root folder '/date/hostname/', example

You could override such behavior using '--onefile' option for which purgelogs will upload a single tar.gz file created with following name convention: <date>_<hostname>.tar.gz, you could override such filename with '--filename' option

Cleanup function

Usage:
purgelogs.bin cleanup [--days <days> [--aud] [--lsnr]] |
                      [--orcl <days> [--aud] [--lsnr]] |
                      [--tfa <days>] |
                      [--osw <days>] |
                      [--oda <days>] |
                      [--extra '<folder>':<days> | [, '<folder>':<days>]]
                      [--automigrate]
                      [--dryrun]
purgeLogs cleanup OPTIONS
--days   <days>           Purge orcl,tfa,osw,oda components logs & traces older then # days
--orcl   <days>           Purge only GI/RDBMS logs & traces (Default 30 days)
--tfa    <days>           Purge only TFA repository older then # days (Default 30 days)
--osw    <days>           Purge only OSW archives older then # days (Default 30 days)
--oda    <days>           Purge only ODA logs and trace older then # days (Default 30 days)
--extra '<folder>':<days> Purge only files in user specified folders (Default 30 days)
--aud                     Purge Audit logs based on '-orcl <days>' option
--lsnr                    It will force the cleanup of listeners log independently by the age
--automigrate             It will run the adrci schema migrate commands in case of DIA-49803
--dryrun                  It will show the purge commands w/o execute them
--help                    Display this help and exit

Encapass function

Usage:
purgelogs.bin encpass --file <password file>
purgeLogs encpass OPTIONS
--filename <filename> File name to store encrypted password (for '--ociatoken' and/or '--proxypass')

How to Install purgelogs

purgelogs can be installed using the RPM (RedHat Package Manager) command as following:

# rpm -i purgelogs-2.0.1-X.el7.x86_64.rpm
(*) X=version number

or updating an installed version, issuing:

# rpm -Uvh purgelogs-2.0.1-X.el7.x86_64.rpm
(*) X=version number

Following files are created under ‘/opt/purgelogs‘:

/opt/purgelogs/
└── purgelogs.bin
0 directories, 1 file

Note: installing purgelogs a softlink link is created under '/usr/local/sbin' (/usr/local/sbin/purgelogs.bin --> /opt/purgelogs/purgelogs.bin)

PurgeLogs Usage Examples

purgeLogs - GI/RDBMS cleanup

Cleanup Alert,Incident,Trace,Cdump,HM, GPNPClient and ACFS log (from 18c and above) for the Grid Infrastructure and RDBMS homes removing all file older then 15 days

# /opt/purgelogs/purgelogs.bin cleanup --orcl 15
2015-11-24 01:58:06: I adrci GI purge started
2015-11-24 01:58:06: I adrci GI purging diagnostic destination diag/asm/+asm/+ASM1
2015-11-24 01:58:06: I ... purging ALERT older than 15 days
2015-11-24 01:58:06: I ... purging INCIDENT older than 15 days
2015-11-24 01:58:06: I ... purging TRACE older than 15 days
2015-11-24 02:04:10: I ... purging CDUMP older than 15 days
2015-11-24 02:04:10: I ... purging HM older than 15 days
2015-11-24 02:04:10: I adrci GI purging diagnostic destination diag/crs/oda101/crs
2015-11-24 02:04:10: I ... purging ALERT older than 15 days
2015-11-24 02:04:10: I ... purging INCIDENT older than 15 days
2015-11-24 02:04:10: I ... purging TRACE older than 15 days
2015-11-24 02:04:23: I ... purging CDUMP older than 15 days
2015-11-24 02:04:23: I ... purging HM older than 15 days
2015-11-24 02:04:23: I adrci GI purging diagnostic destination diag/tnslsnr/oda101/listener_scan2
2015-11-24 02:04:23: I ... purging ALERT older than 15 days
2015-11-24 02:04:24: I ... purging INCIDENT older than 15 days
2015-11-24 02:04:24: I ... purging TRACE older than 15 days
2015-11-24 02:04:24: I ... purging CDUMP older than 15 days
2015-11-24 02:04:24: I ... purging HM older than 15 days
2015-11-24 02:04:24: I adrci GI purging diagnostic destination diag/tnslsnr/oda101/listener
2015-11-24 02:04:24: I ... purging ALERT older than 15 days
2015-11-24 02:04:24: I ... purging INCIDENT older than 15 days
2015-11-24 02:04:25: I ... purging TRACE older than 15 days
2015-11-24 02:04:25: I ... purging CDUMP older than 15 days
2015-11-24 02:04:25: I ... purging HM older than 15 days
2015-11-24 02:04:25: I adrci GI purging diagnostic destination diag/tnslsnr/oda101/asmnet1lsnr_asm
2015-11-24 02:04:25: I ... purging ALERT older than 15 days
2015-11-24 02:04:25: I ... purging INCIDENT older than 15 days
2015-11-24 02:04:26: I ... purging TRACE older than 15 days
2015-11-24 02:04:26: I ... purging CDUMP older than 15 days
2015-11-24 02:04:27: I ... purging HM older than 15 days
(...)
2015-11-24 02:13:56: I adrci RDBMS purging diagnostic destination diag/rdbms/s2test/S2TEST
2015-11-24 02:13:56: I ... purging ALERT older than 15 days
2015-11-24 02:13:56: I ... purging INCIDENT older than 15 days
2015-11-24 02:13:57: I ... purging TRACE older than 15 days
2015-11-24 02:13:57: I ... purging CDUMP older than 15 days
2015-11-24 02:13:57: I ... purging HM older than 15 days
2015-11-24 02:13:57: I Purging completed succesfully!

You can include the Grid Infrastructure & RDBMS Audit files using "-aud" option, example:

# /opt/purgelogs/purgelogs.bin cleanup --orcl 15 --aud

purgeLogs - OSWatcher/ExaWatcher archive & OAK/DCS cleanup (on ODA - Oracle Database Appliance)

Cleanup OSWatcher/ExaWatcher archive files and OAK logs, removing all file older then 35 days

# /opt/purgelogs/purgelogs.bin cleanup --osw 35 --oda 35
2015-11-24 02:05:10: I Purging OSW archives older then 35 days
2015-11-24 02:05:10: I Purging completed successfully!

purgeLogs - Files from "extra" given folders

Cleanup files/traces/cores/... from some given "extra" folders such '/tmp', '/var/log', '/opt/app' with different retention period

# /opt/purgelogs/purgelogs.bin cleanup --extra '/tmp':10,'/var/log':20,'/opt/app':30
2015-11-24 02:06:13: I Purging files older then 10 days on user specified folder '/tmp'
2015-11-24 02:06:13: I Purging files older then 20 days on user specified folder '/var/log'
2015-11-24 02:06:13: I Purging files older then 30 days on user specified folder '/opt/app'
2015-11-24 02:06:13: I Purging completed succesfully!

Use this option with caution, a wrong path might result in data loss.

purgeLogs - GI/RDBMS, TFA, OSWatcher & OAK cleanup

Cleanup Alert,Incident,Trace,Cdump,HM for the Grid Infastructure and RDBMS homes + TFA archives + OSWatcher archive files + OAK logs, removing all file older then 30 days (default)

# /opt/purgelogs/purgelogs.bin cleanup
2015-11-24 03:35:35: I adrci GI purge started
(...)
2015-11-24 03:35:40: I adrci RDBMS purge started
(...)
2015-11-24 03:35:44: I Purging TFA archives older then 30 days
2015-11-24 03:35:48: I Purging OSW archives older then 30 days
2015-11-24 03:35:48: I Purging ODA logs older then 30 days
2015-11-24 03:35:49: I Purging completed succesfully! 

purgeLogs - Cleanup listener logs

As described by "Adrci Purge Does Not Clear the Text-formatted Alert.Log located in the Trace Folder (Doc ID 751082.1)", adrci is not cleaning up listener logs. In order to force the cleanup of the following files:

/u01/app/grid/diag/tnslsnr/<hostname>/asmnet1lsnr_asm/trace/mgmtlsnr.log
/u01/app/grid/diag/tnslsnr/<hostname>/mgmtlsnr/trace/asmnet1lsnr_asm.log
/u01/app/grid/diag/tnslsnr/<hostname>/listener/trace/listener.log
/u01/app/grid/diag/tnslsnr/<hostname>/listener_scan1/trace/listener_scan1.log
/u01/app/grid/diag/tnslsnr/<hostname>/listener_scan2/trace/listener_scan2.log

you should use the '-lsnr' option, example:

/opt/purgelogs/purgelogs.bin cleanup --orcl 50 --lsnr

Note: with '-lsnr' option, purgelogs will force the cleanup of listeners log independently by the age

Setting up purgeLogs as crontab job

As example, we are setting up purgelogs to be executed every day at 2.00am purging GI/Rdbms traces older then 20 days and keeping TFA, OSWatcher and ODA logs not older then 10 days

00 02 * * * /opt/purgelogs/purgelogs.bin cleanup --orcl 20 --tfa 10 --osw 10 --oda 10

Note: As many ODA patches could be staged and applied, the correct method to clean up the repository "/opt/oracle/oak/pkgrepos", outside the purdeLogs scope, is described by the Note:1681062.1

PurgeLogs Considerations

For systems without Grid Infrastructure installed (non-RAC), the '/etc/orab' file must be in use.
purgeLogs need to be executed by root user (as purgelogs will cleanup GI(grid user) and RDBMS(oracle user) staff)
purgeLogs can be used with crontab
"extra" command line option could have potentially destroying behavior as it will delete all files on related folders, use this option with caution, a wrong path might result in data loss.
purgeLogs archive function supports: oci-cli and/or restAPI
purgeLogs archive uses Automatic Diagnostic Repository Command Interpreter (ADRCI) utility and for each "ADR Homes" will collect traces & logs using "show tracefiles" command. purgeLogs cleanup for each "ADR Homes" will purge each traces/logs type such 'ALERT, INCIDENT, TRACE, CDUMP, STAGE, SWEEP, HM, UTSCDMP'
purgelogs archive is considering the 'change date' (the file creation date on almost all filesystems is not available). Then for files such the alert.log that is always modified (the age can no be more then 'days'...), the archive most likely will skip it. A possible workaround could be to configure the logrotate (see 'man logrotate'), example having '/u02/app/oracle/diag/rdbms/mydb/mydb1/trace/alert_mydb.log' you could make a file 'alert_mydb' under /etc/logrotate.d/ such

/u02/app/oracle/diag/rdbms/mydb/mydb1/trace/alert_mydb.log {
su oracle oinstall
daily
notifempty
missingok
sharedscripts
copytruncate
rotate 10
size 10M
compress
}

in such way we are going to create a copy of the alert and later purgelogs archive could archive it as the age condition could be satisfied

PurgeLogs inside TFA

Limited PurgeLogs has been included into TFA (Oracle Trace File Analyzer) as 'managelogs' tool feature.

You can turn the "auto purge on" by setting

tfactl set manageLogsAutoPurge=ON

The usage of managelogs:

tfactl [run] managelogs [ -purge [[-older <n><m|h|d>] | [-gi] | [-database <all|d1,d2..>] | [-dryrun] ]] [ -show [usage|variation] [ [-older <n><d>] | [-gi] | [-database <all|d1,d2..>] ] ] [-node <all|local|node1,node2..>]Options:
-purge Purge logs
-older Timeperiod for log purge
-gi Purge Grid Infrastructure logs(all ADR homes under GIBASE/diag and crsdata(cvu dirs))
-database Purge Database logs (Default all else provide list)
-dryrun Estimate logs which will be cleared by purge command
-show Print usage/variation details
-older Timeperiod for change in log volume
-gi Space utilization under GIBASE
-database Space utilization for Database logs (Default all else provide list)
-older <n><m|h|d> Files from past 'n' [d]ays or 'n' [h]ours or 'n' [m]inutes
e.g:
/u01/app/18.1.0.0/grid/tfa/tls/tfa_home/bin/tfactl managelogs -purge -older 30d -dryrun
/u01/app/18.1.0.0/grid/tfa/tls/tfa_home/bin/tfactl managelogs -purge -older 30d
/u01/app/18.1.0.0/grid/tfa/tls/tfa_home/bin/tfactl managelogs -show usage
/u01/app/18.1.0.0/grid/tfa/tls/tfa_home/bin/tfactl run managelogs -purge -older 30d -dryrun
/u01/app/18.1.0.0/grid/tfa/tls/tfa_home/bin/tfactl run managelogs -purge -older 30d
/u01/app/18.1.0.0/grid/tfa/tls/tfa_home/bin/tfactl run managelogs -show usage

You may find more details on Autonomous Health Framework User’s Guide (see tfactl managelogs)

REFERENCES

Was this document helpful?

Yes

Document Details


	Type:
	Status:
	Last Major Update:
	Last Update:
	Language:


	TECHNICAL BRIEF
	PUBLISHED
	10-Feb-2023
	10-Jul-2023

Related Products

Exadata Database Machine V2

Gen 2 Exadata Cloud at Customer

Oracle Database - Enterprise Edition

Real Application Clusters

Oracle Database Appliance

Information Centers

Document References

Attachments

Keywords

DIAGNOSTIC;EXADATA;GRID;HEALTH;INFRASTRUCTURE;ODA;ORACLE DATABASE APPLIANCE;OSWATCHER;PURGE;TRACE;UPDATE

Translations

EnglishSource

Japanese日本語

Legal Notices and Terms of Use

Privacy Statement

0 1 * * 5 /u03/app/oracle/utils/purgeLogs -orcl 90 > /u03/app/oracle/purgelogs/orcl/`date +\%Y\%m\%d\%H\%M\%S`-orcl.log 2>&1

15 1 * * 5 /u03/app/oracle/utils/purgeLogs -orcl 365 -aud > /u03/app/oracle/purgelogs/aud/`date +\%Y\%m\%d\%H\%M\%S`-aud.log 2>&1

#15 1 7 */2 * /u03/app/oracle/utils/purgeLogs -lsnr > /u03/app/oracle/purgelogs/lsnr/`date +\%Y\%m\%d\%H\%M\%S`-lsnr.log 2>&1

45 1 * * 5 /u03/app/oracle/utils/purgeLogs -tfa 90 > /u03/app/oracle/purgelogs/tfa/`date +\%Y\%m\%d\%H\%M\%S`-tfa.log 2>&1

0 2 * * 5 /u03/app/oracle/utils/purgeLogs -osw 90 > /u03/app/oracle/purgelogs/osw/`date +\%Y\%m\%d\%H\%M\%S`-osw.log 2>&1

15 2 * * 5 /u03/app/oracle/utils/purgeLogs -oda 90 > /u03/app/oracle/purgelogs/oda/`date +\%Y\%m\%d\%H\%M\%S`-oda.log 2>&1

Exadata Interview Questions Based on ChatGPT answers

2023-04-19T23:33:00.004+05:30

What is Exadata and how does it differ from a traditional database environment?

Exadata is a high-performance engineered system developed by Oracle Corporation that combines hardware and software to provide a comprehensive database solution for large-scale, data-intensive workloads. It is designed to deliver extreme performance and scalability for Oracle Database workloads, making it suitable for data warehousing, online transaction processing (OLTP), and mixed workloads.

The key differences between Exadata and a traditional database environment are:

Integrated Hardware and Software: Exadata is an engineered system that includes both hardware and software components that are optimized to work together. It combines Oracle Database software with specialized hardware components such as database servers, storage servers, and networking components, all of which are designed to work seamlessly and efficiently to deliver high performance.
Smart Storage: Exadata includes a feature called Smart Storage, which offloads some of the database processing to the storage servers. This offloading capability enables Exadata to perform data filtering, compression, and processing at the storage layer, reducing the amount of data that needs to be transferred to the database servers and improving overall performance.
High Performance Networking: Exadata uses high-performance networking technologies such as Remote Direct Memory Access (RDMA) and InfiniBand to provide low-latency and high-bandwidth communication between the database servers and storage servers. This enables Exadata to deliver extremely fast data transfer rates, which can significantly accelerate database performance.
Scale-Out Architecture: Exadata uses a scale-out architecture, where multiple database servers and storage servers are interconnected to form a high-performance, highly available cluster. This allows Exadata to scale horizontally by adding more servers to the cluster as needed, providing elasticity and flexibility in handling increasing data workloads.
Advanced Database Optimization: Exadata includes several advanced database optimization features such as Oracle Database Smart Scan, Smart Flash Cache, and Hybrid Columnar Compression, which are specifically designed to accelerate data retrieval and processing operations. These optimizations can significantly improve the performance of data-intensive workloads such as data warehousing and analytics.
Comprehensive Management: Exadata provides comprehensive management tools such as Oracle Enterprise Manager, which enable efficient monitoring, provisioning, and administration of the Exadata system. These management tools provide deep visibility into the performance and health of the system, allowing database administrators to optimize the system for maximum performance.

What are the key features of Exadata?

Smart Storage: Exadata includes Smart Storage, which offloads database processing to the storage servers. Smart Storage capabilities include Smart Scan, which offloads data filtering and processing to the storage layer, reducing data transfer overhead and improving query performance. Smart Flash Cache uses intelligent caching algorithms to accelerate database queries by caching frequently accessed data in flash storage, reducing I/O latency and improving overall performance.
High Performance Networking: Exadata uses high-performance networking technologies such as Remote Direct Memory Access (RDMA) and InfiniBand, which provide low-latency, high-bandwidth communication between the database servers and storage servers. This enables Exadata to achieve fast data transfer rates and reduce network overhead, resulting in improved performance for data-intensive workloads.
Scale-Out Architecture: Exadata uses a scale-out architecture, where multiple database servers and storage servers are interconnected to form a high-performance, highly available cluster. This allows Exadata to scale horizontally by adding more servers to the cluster as needed, providing elasticity and flexibility in handling increasing data workloads.
Advanced Database Optimization: Exadata includes advanced database optimization features such as Oracle Database Smart Scan, Smart Flash Cache, and Hybrid Columnar Compression. These optimizations are designed to accelerate data retrieval and processing operations, improving the performance of data-intensive workloads such as data warehousing and analytics.
Integrated Hardware and Software: Exadata is an engineered system that includes both hardware and software components that are optimized to work together. It combines Oracle Database software with specialized hardware components such as database servers, storage servers, and networking components, all of which are designed to work seamlessly and efficiently to deliver high performance.
Comprehensive Management: Exadata provides comprehensive management tools such as Oracle Enterprise Manager, which enable efficient monitoring, provisioning, and administration of the Exadata system. These management tools provide deep visibility into the performance and health of the system, allowing database administrators to optimize the system for maximum performance.
High Availability and Fault Tolerance: Exadata is designed to provide high availability and fault tolerance for critical database workloads. It includes features such as Oracle Real Application Clusters (RAC) for database clustering, automatic failover, and redundancy at multiple levels, including hardware, networking, and software components, ensuring high uptime and data reliability.
Security: Exadata includes a range of security features such as Oracle Advanced Security, Oracle Database Vault, and Oracle Audit Vault and Database Firewall, which provide advanced security capabilities to protect sensitive data and ensure data privacy and compliance.

What is the architecture of Exadata?

Database Servers: Exadata includes multiple database servers that are interconnected in a cluster using high-performance networking technologies such as Remote Direct Memory Access (RDMA) and InfiniBand. These database servers run the Oracle Database software and are responsible for processing database queries, transactions, and other database operations.
Storage Servers: Exadata includes multiple storage servers that are interconnected in a cluster using the same high-performance networking technologies as the database servers. These storage servers provide storage capacity for the database servers and are optimized for high-speed data retrieval and processing. They also include Smart Storage capabilities such as Smart Scan and Smart Flash Cache, which offload data processing to the storage layer and accelerate data retrieval.
InfiniBand Network: Exadata uses InfiniBand, a high-speed networking technology, to interconnect the database servers and storage servers in a low-latency, high-bandwidth network fabric. This enables fast data transfer rates and reduces network overhead, resulting in improved performance.
Switches: Exadata includes InfiniBand switches that provide connectivity between the database servers, storage servers, and other components of the Exadata system. These switches are responsible for routing data and commands between the various components of the system.
Software: Exadata includes Oracle Database software, which is the same software used in traditional Oracle Database environments. However, Exadata also includes additional software optimizations such as Oracle Database Smart Scan, Smart Flash Cache, and other performance-enhancing features that are specifically designed to take advantage of the Exadata hardware architecture.
Management Tools: Exadata provides comprehensive management tools such as Oracle Enterprise Manager, which enable efficient monitoring, provisioning, and administration of the Exadata system. These tools provide deep visibility into the performance and health of the system, allowing database administrators to optimize the system for maximum performance.
Power Distribution Units (PDUs): Exadata includes PDUs that provide power distribution and management for the various components of the system, including the database servers, storage servers, and switches.
Rack: Exadata is typically housed in a rack that contains the database servers, storage servers, switches, PDUs, and other components. The rack is designed for efficient cooling, power distribution, and organization of the Exadata system.

How does Exadata handle storage and I/O performance?

Smart Storage: Exadata storage servers include Smart Storage capabilities such as Smart Scan and Smart Flash Cache. Smart Scan offloads data processing to the storage layer, where data is filtered, compressed, and processed before it is sent to the database servers. This reduces the amount of data that needs to be transferred over the network, minimizing I/O and improving performance. Smart Flash Cache uses flash-based storage to cache frequently accessed data, reducing I/O latency and accelerating data retrieval.
Exadata Hybrid Columnar Compression (EHCC): Exadata supports EHCC, which is a specialized compression technique optimized for data warehousing workloads. EHCC compresses data at the columnar level, reducing the amount of data that needs to be stored and transferred, thereby improving storage and I/O performance.
High-speed Networking: Exadata uses InfiniBand networking technology to interconnect the database servers and storage servers. InfiniBand provides low-latency, high-bandwidth communication, enabling fast data transfer rates and reducing network overhead, which improves storage and I/O performance.
Smart I/O Resource Management: Exadata includes intelligent I/O resource management features that optimize I/O operations for maximum performance. For example, Exadata uses I/O Resource Manager (IORM) to prioritize I/O requests based on workload priorities, ensuring that critical workloads get the necessary I/O resources to perform optimally.
Storage Offloading: Exadata offloads certain database operations, such as filtering, aggregation, and join processing, to the storage layer using Smart Scan. This reduces the amount of data that needs to be processed in the database servers, minimizing CPU usage and improving storage and I/O performance.
Flash-based Storage: Exadata leverages high-performance flash-based storage for both caching (Smart Flash Cache) and persistent storage (Exadata Flash Cache and Exadata Smart Flash Logs). Flash-based storage provides low-latency access to data, reducing I/O latency and improving storage and I/O performance.
Parallelism: Exadata supports parallel processing at multiple levels, including parallel query execution, parallel DML (Data Manipulation Language) operations, and parallel backup and recovery. This allows Exadata to efficiently utilize the available processing power and storage resources, maximizing performance.

What is the role of the Exadata Storage Server Software (ESS)?

Smart Scan: ESS includes the Smart Scan feature, which offloads data processing to the storage layer. When a database query is executed, ESS processes the data at the storage layer, filtering, compressing, and processing the data before sending it to the database servers. This reduces the amount of data that needs to be transferred over the network and minimizes the amount of data that needs to be processed in the database servers, resulting in improved query performance.
Smart Flash Cache: ESS includes the Smart Flash Cache feature, which uses flash-based storage to cache frequently accessed data. This helps to accelerate data retrieval by reducing I/O latency and improving overall storage performance.
Exadata Hybrid Columnar Compression (EHCC): ESS supports EHCC, which is a specialized compression technique optimized for data warehousing workloads. EHCC compresses data at the columnar level, reducing the amount of data that needs to be stored and transferred, thereby improving storage and I/O performance.
I/O Resource Management: ESS includes I/O Resource Manager (IORM), which is a feature that manages I/O resources to ensure that critical workloads get the necessary I/O resources to perform optimally. IORM prioritizes I/O requests based on workload priorities, helping to optimize I/O performance for different database workloads.
Offloading Database Operations: ESS offloads certain database operations, such as filtering, aggregation, and join processing, to the storage layer using Smart Scan. This reduces the amount of data that needs to be processed in the database servers, minimizing CPU usage and improving overall performance.
Storage Management: ESS provides management and monitoring capabilities for the storage servers in the Exadata system. It includes features such as automatic storage management (ASM) for managing disk groups and volumes, proactive disk monitoring for detecting and resolving disk failures, and other storage-related management functions.
Monitoring and Diagnostics: ESS includes monitoring and diagnostic capabilities that provide visibility into the health and performance of the storage servers. This helps database administrators to identify and resolve any storage-related issues and optimize the storage performance for the database workloads.

How does Exadata handle compression?

Hybrid Columnar Compression (HCC): Exadata supports HCC, which is a compression technique that is optimized for data warehouse workloads. HCC organizes data in columns rather than rows, and then compresses each column separately, allowing for much higher compression ratios compared to traditional row-based compression. HCC is especially effective for compressing large data sets with repetitive data, such as data warehouses, and can significantly reduce storage footprint and I/O requirements.
Advanced Compression: Exadata also supports Oracle's Advanced Compression option, which provides additional compression techniques such as OLTP Compression and SecureFile Compression. OLTP Compression is designed for OLTP workloads and provides compression ratios optimized for transactional processing, while SecureFile Compression is designed for large object (LOB) data and provides compression options for LOB columns in database tables.
Smart Scan Compression: Exadata's Smart Scan feature allows the database to offload query processing to the storage cells, which can perform on-the-fly decompression of compressed data as part of the query execution process. This means that queries can directly access compressed data without the need to decompress it first, reducing I/O and improving query performance.
Automatic Compression: Exadata includes automatic compression features that automatically identify and compress low cardinality columns, which are columns with limited distinct values, during data loading, without requiring any changes to the application code. This can help reduce storage footprint and improve query performance for data with repetitive patterns.
Backup Compression: Exadata also supports compression for database backups, allowing for efficient storage and transfer of backup data to reduce backup storage requirements and backup times.

What is Smart Scan technology and how does it work?

Columnar Offloading: When a query is executed on Exadata, the storage cells scan the data stored in the storage cells at the columnar level, rather than the traditional row-based level. This is known as columnar offloading. Columnar offloading allows the storage cells to read only the columns needed for the query, rather than reading entire rows, which can result in significant reduction in I/O and network overhead.
Predicate Filtering: The storage cells perform predicate filtering during the columnar offloading process. Predicate filtering involves applying filters or conditions from the query directly on the storage cells, allowing them to discard unnecessary data early in the query processing pipeline. This can significantly reduce the amount of data that needs to be processed and returned to the database server, improving query performance.
Compression Offloading: If the data in the storage cells is compressed using Oracle's Hybrid Columnar Compression (HCC) or other compression techniques, the storage cells can perform on-the-fly decompression during the columnar offloading process. This means that queries can directly access compressed data without the need to decompress it first, reducing I/O and improving query performance.
Smart Join Filtering: Smart Scan technology can also optimize join operations. The storage cells can perform join filtering by leveraging the metadata stored in Exadata's Smart Flash Cache, which contains frequently used data, to accelerate join operations. This can reduce the amount of data that needs to be transferred across the network and processed in the database server, improving query performance.

What are some common performance tuning techniques

for Exadata?

Smart Scan Offloading: Leveraging Exadata's Smart Scan technology, which offloads query processing to the storage cells, can significantly improve performance. By performing columnar offloading, predicate filtering, compression offloading, and smart join filtering, Smart Scan reduces I/O, network overhead, and data movement, leading to faster query execution.
Optimized Storage Configuration: Proper configuration of Exadata's storage cells is crucial for optimal performance. This includes setting up appropriate disk groups, ASM (Automatic Storage Management) striping, and redundancy levels based on workload requirements. Proper storage configuration ensures optimal I/O distribution, reduces contention, and maximizes throughput.
Partitioning and Indexing: Effective use of partitioning and indexing can improve query performance on Exadata. Partitioning involves dividing large tables or indexes into smaller, more manageable pieces called partitions, which can be stored on different storage cells for parallel processing. Indexing involves creating appropriate indexes on columns that are frequently used in queries to speed up data retrieval.
SQL Optimization: Optimizing SQL queries is a critical performance tuning technique. This includes rewriting inefficient queries, using appropriate hints or optimizer features, and minimizing unnecessary joins, sorts, and filters. Proper indexing, leveraging features like query rewrite and materialized views, and using SQL performance analysis tools like Oracle's SQL Tuning Advisor can also help improve query performance.
In-Memory Columnar Store: Exadata provides an in-memory columnar store feature, which allows for storing frequently accessed data in memory in a columnar format, optimized for analytical workloads. Leveraging this feature can significantly speed up queries that involve large data sets and aggregations.
Flash Cache: Exadata's Smart Flash Cache, which is a high-performance flash-based cache, can be used to accelerate database operations. Properly configuring and using Flash Cache can improve I/O performance, reduce latency, and speed up query execution.
Monitoring and Diagnostics: Monitoring and diagnosing performance issues using Exadata-specific tools like Exadata Database Machine Monitoring (DBM) and ExaWatcher, as well as standard Oracle Database performance monitoring and diagnostic tools, can help identify and resolve performance bottlenecks. This includes monitoring system resource utilization, analyzing performance metrics, and identifying and resolving contention issues.
Database and OS Configuration: Proper configuration of the database and operating system on Exadata is important for optimal performance. This includes setting appropriate database initialization parameters, optimizing memory allocation, configuring CPU and I/O resource management, and tuning operating system settings like TCP/IP stack parameters and file system caching.
Patching and Upgrades: Keeping Exadata software up-to-date with the latest patches and upgrades can improve performance by fixing known issues, improving stability, and optimizing features. Regular patching and upgrades should be part of the performance tuning strategy.

How do you migrate a database to Exadata?

2022-05-27T15:23:00.003+05:30

Schedule cron for Cluster Health Check on Oracle 19c.

#!/bin/bash

export BASE_DIR=$ORACLE_HOME/cv

export dt=`printf '%(%m%d%Y)T\n' -1`

cluvfy comp healthcheck -html -save -savedir $BASE_DIR

export report=`ls cvucheckreport_$dt*.html`

ARG_EMAIL_TO="user1@user1.com, user@user2.com"

ARG_EMAIL_FROM="yourmail@domain.com"

ARG_EMAIL_SUBJECT="DBName - Cluster HealthCheck Report"

(

echo "To: ${ARG_EMAIL_TO}"

echo "From: ${ARG_EMAIL_FROM}"

echo "Subject: ${ARG_EMAIL_SUBJECT}"

echo "Mime-Version: 1.0"

echo "Content-Type: text/html; charset='utf-8'"

echo

cat $report

) | sendmail -t

Agent Installation on Windows Server. SQL Server (Failover Cluster) target addition in OEM 12c

2018-05-17T01:26:00.002+05:30

Agent Installation on Windows Server. SQL Server (Failover Cluster) target addition in OEM 12c

This document is prepared for monitoring windows server where sql server installed.Mostly used OEM for monitoring databases. In this document we will cover agent installation with target addition into OEM 12c. We will also cover prerequisite like enabling port for communication from agent to oms server

Agent Installation on windows server

As a part of installation, we require to fulfil prerequisite like connectivity between OEM server and Window server.
For Port opening and communication between two servers
Ports required to open : 4903 , 7799 , 3872
Following steps require doing for manually installation of Agent in Windows server.
- Verifying if the agent exists on OMS.

To begin, open your Cloud Control and go to "Setup -> Extensibility -> Self Update". Open the "Agent Software" folder and check if the Agent Software of Windows x64 is with status "Applied", as in the image below:

If the agent is not applied, you need to download it, deploy and then activate it.

Download the Agent installer

With your agent activated, we will now download it from your OMS server. Access your OMS Server and download the package so we can later install it manually on Windows.

First, log in with emcli:

[oracle@~]$ emcli login -username=sysman

Enter password:

[oracle@~]$

Let's check if the agent is really available (we have already done this check in step 1):

[oracle@~]$ emcli get_supported_platforms

Getting list of platforms...

Check the logs at /opt/app/oracle/em12cR2/Middleware/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/agent.log

About to access self-update code path to retrieve the platforms list..

Getting Platforms list ...

-----------------------------------------------

Version = 12.1.0.2.0

Platform = Linux x86-64

-----------------------------------------------

Version = 12.1.0.1.0

Platform = Linux x86-64

-----------------------------------------------

Version = 12.1.0.1.0

Platform = Oracle Solaris on SPARC (64-bit)

-----------------------------------------------

Version = 12.1.0.1.0

Platform = Oracle Solaris on x86-64 (64-bit)

-----------------------------------------------

Version = 12.1.0.1.0

Platform = Microsoft Windows x64 (64-bit)

-----------------------------------------------

Version = 12.1.0.1.0

Platform = HP-UX Itanium

-----------------------------------------------

Platforms list displayed successfully.

[oracle@-0--~]$

Great, let's download the installer to the local folder. This folder must be empty. Version 12.1.0.3 onwards will generate two files, one for the agent and one for the plugin:

[oracle@---~]$ emcli get_agentimage -destination=/home/oracle -platform="Microsoft Windows x64 (64-bit)" -version="12.1.0.3.0"
Platform:Microsoft Windows x64 (64-bit)
Destination:/home/oracle
=== Partition Detail ===
Space free : 13 GB
Space required : 1 GB
Check the logs at /opt/app/oracle/em12cR2/Middleware/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/get_agentimage_2013-11-08_16-04-37-PM.log
Setting property ORACLE_HOME to:/opt/app/oracle/em12cR2/Middleware/oms
calling pulloneoffs with arguments:/opt/app/oracle/em12cR2/Middleware/oms/opt/software/72/C1D0CDE7DD23F299E040DD0AEF3F63BD12.1.0.1.0windows_x64
Check this logs for more information: /opt/app/oracle/em12cR2/Middleware/oms/sysman/prov/agentpush/logs
[oracle@~]$ ls -la
total 472424
drwx------ 10 oracle oinstall 4096 Nov 8 16:04 .
drwxr-xr-x 3 root root 4096 May 8 2012 ..
-rw-r----- 1 oracle oinstall 243272366 Nov 8 16:04 12.1.0.3.0_AgentCore_233.zip
[oracle@~]$

Finally, upload the generated "12.1.0.3.0_AgentCore_233.zip" (or any similar version) from your server to the client machine running Windows. In my case, I downloaded it to a temporary server via SFTP and then sent to the client using Remote Desktop.

Change the host file on your server

Edit the file "C:\Windows\System32\drivers\etc\hosts" and add (or uncomment) lines, so that it stays similar to the following:

#your host

127.0.0.1 localhost.local localhost

#EMC 12C

IP Address FQDN FQDN

Create an agent registration password

Open your OEM Cloud Control, and access the "Setup -> Security -> Registration Passwords" menu:

Generate agent.rsp file

Create a new "agent.rsp" file and set only some basic parameters for the installation:
Example:

OMS_HOST=***.com

EM_UPLOAD_PORT=4903

AGENT_REGISTRATION_PASSWORD=****

b_startAgent=true

ORACLE_HOSTNAME=**Hostname**

Unpack and install the agent

Unzip the downloaded zip file "12.1.0.1.0_AgentCore_233.zip" to some directory. Create a directory where the agent will be installed, in my case, I chosed "c:\emagent" (avoid spaces).

PS: In version 12.1.0.3, you must leave the plugin file "12.1.0.3.0_PluginsOneoffs_233.zip" zipped in the same location where the file above was unzipped, next to the file "agentDeploy.bat". Check how it must stay:

If this is not done during installation, you may receive the error:

"The Management Agent configuration failed. The plug-in configuration for the oracle.sysman.oh monitoring plug-in may have failed, or this plug-in may not be present in the Management Agent software. Ensure that the Management Agent software has the oracle.sysman.oh monitoring plug-in, if not then retry the operation. If the agent software has the oracle.sysman.oh monitoring plug-in, view the plug-in deployment log E:\app\Administrator\agent12c\agent_inst\install\logs to check if the plug-in configuration for the oracle.sysman.oh monitoring plug-in failed."

Now open a command prompt and run from the folder where you unzipped the file:

Try to open command prompt with “Run as administrator”.

- agentDeploy.bat AGENT_BASE_DIR=C:\emagent RESPONSE_FILE=C:\Users\Administrator\Desktop\agent\agent.rsp

Microsoft Windows [Version 6.1.7600]

C:\Users\Administrator>cd Desktop\agent\12.1.0.1.0_AgentCore_233

C:\Users\Administrator\Desktop\agent\12.1.0.1.0_AgentCore_233>agentDeploy.bat AGENT_BASE_DIR=C:\emagent RESPONSE_FILE=C:\Users\Administrator\Desktop\agent\agent.rsp

C:\Users\Administrator\Desktop\agent\12.1.0.1.0_AgentCore_233

Present working directory:C:\Users\Administrator\Desktop\agent\12.1.0.1.0_AgentCore_233

Archive location:C:\Users\Administrator\Desktop\agent\12.1.0.1.0_AgentCore_233

directory

AGENT_BASE_DIR

C:\emagent

Agent base directory:C:\emagent

C:\emagent

RESPONSE_FILE

C:\Users\Administrator\Desktop\agent\agent.rsp

Agent base directory:C:\emagent

OMS Host:

Agent image loc : "C:\Users\Administrator\Desktop\agent\12.1.0.1.0_AgentCore_233"

C:\emagent configonlyfalse

Reading the properties file: "C:\Users\Administrator\Desktop\agent\12.1.0.1.0_AgentCore_233"\agentimage.properties

1 file(s) copied.

This is the version 12.1.0.1.0

This is the type core

This is the aru id 233

"Validating the OMS_HOST & EM_UPLOAD_PORT"

"C:\emagent\core\12.1.0.1.0"

"Executing command : C:\emagent\core\12.1.0.1.0\jdk\bin\java -classpath C:\emagent\core\12.1.0.1.0\jlib\agentInstaller.jar:C:\emagent\core\12.1.0.1.0\oui\jlib\OraInstaller.jar oracle.sysman.agent.installer.AgentInstaller C:\emagent\core\12.1.0.1.0 "C:\Users\Administrator\Desktop\agent\12.1.0.1.0_AgentCore_233" C:\emagent -prereq"

Validando o host e a porta de oms com o url: http://:4901/empbs/genwallet

Validando o host e a porta de oms com o url: https://---:4901/empbs/genwallet

Return status:3

"C:\Users\Administrator\Desktop\agent\12.1.0.1.0_AgentCore_233"\12.1.0.1.0_PluginsOneoffs_233.zip

"Executing command : C:\emagent\core\12.1.0.1.0\jdk\bin\java -classpath C:\emagent\core\12.1.0.1.0\jlib\OraInstaller.jar:C:\emagent\core\12.1.0.1.0\sysman\jlib\emInstaller.jar:C:\emagent\core\12.1.0.1.0\jlib\xmlparserv2.jar:C:\emagent\core\12.1.0.1.0\jlib\srvm.jar:C:\emagent\core\12.1.0.1.0\jlib\emCfg.jar:C:\emagent\core\12.1.0.1.0\sysman\jlib\agentInstaller.jar oracle.sysman.agent.installer.AgentInstaller C:\emagent\core\12.1.0.1.0"

Executando prÚ-requisitos de instalaþÒo do agente...

Executando comando: C:\emagent\core\12.1.0.1.0\oui\bin\setup.exe -ignoreSysPrereqs -prereqchecker -silent -ignoreSysPrereqs -waitForCompletion -prereqlogloc C:\emagent\core\12.1.0.1.0\cfgtoollogs\agentDeploy -entryPoint oracle.sysman.top.agent_Complete -detailedExitCodes PREREQ_CONFIG_LOCATION=C:\emagent\core\12.1.0.1.0\prereqs -J-DORACLE_HOSTNAME=10.192.12.77

Prereq Logs Location:C:\emagent\core\12.1.0.1.0\cfgtoollogs\agentDeploy\prereq<timestamp>.log

PrÚ-requisitos de instalaþÒo do agente concluÝdos com sucesso

Clonando o home do agente...

Executando comando: C:\emagent\core\12.1.0.1.0\oui\bin\setup.exe -ignoreSysPrereqs -clone -forceClone -silent -waitForCompletion -nowait ORACLE_HOME=C:\emagent\core\12.1.0.1.0 -responseFile C:\Users\Administrator\Desktop\agent\agent.rsp AGENT_BASE_DIR=C:/agent12c AGENT_BASE_DIR=C:/agent12c RESPONSE_FILE=C:\Users\Administrator\Desktop\agent\agent.rsp -noconfig ORACLE_HOME_NAME=agent12c2 -force

Clone Action Logs Location:C:\Program Files\Oracle\Inventory\logs\cloneActions<timestamp>.log

Clonagem do home do agente concluÝda com sucesso

Anexando home do sbin...

Executando comando: C:\emagent\core\12.1.0.1.0\oui\bin\setup.exe -ignoreSysPrereqs -attachHome -waitForCompletion -nowait ORACLE_HOME=C:\emagent\sbin ORACLE_HOME_NAME=sbin12c2 -force

Attach Home Logs Location:C:\emagent\core\12.1.0.1.0\cfgtoollogs\agentDeploy\AttachHome<timestamp>.log

AnexaþÒo do home do sbin concluÝda com sucesso.

Updating home dependencies...

Executando comando: C:\emagent\core\12.1.0.1.0\oui\bin\setup.exe -ignoreSysPrereqs -updateHomeDeps -waitForCompletion HOME_DEPENDENCY_LIST="C:\emagent\sbin:C:\emagent\core\12.1.0.1.0" -invPtrLoc C:\emagent\core\12.1.0.1.0\oraInst.loc -force

Update Home Dependencies Location:C:\emagent\core\12.1.0.1.0\cfgtoollogs\agentDeploy\UpdateHomeDeps<timestamp>.log

AtualizaþÒo da dependÛncia do home concluÝda com sucesso.

Executando a configuraþÒo do agente...

Executando comando: C:\emagent\core\12.1.0.1.0\oui\bin\runConfig.bat ORACLE_HOME=C:\emagent\core\12.1.0.1.0 RESPONSE_FILE=C:\emagent\core\12.1.0.1.0\agent.rsp ACTION=configure MODE=perform COMPONENT_XML={oracle.sysman.top.agent.11_1_0_1_0.xml} RERUN=true

Configuration Log Location:C:\emagent\core\12.1.0.1.0\cfgtoollogs\cfgfw\CfmLogger<timestamp>.log

Agent Configuration completed successfully

Agent deployment log location:

C:\emagent\core\12.1.0.1.0\cfgtoollogs\agentDeploy\agentDeploy_<timestamp>.log

Agent deployment completed successfully.

Great, your agent was installed and successfully deployed.

NOTE: In version 12.1.0.3, an screen with Windows error may appear as below. If it appears, click "Close the Program" and ignore the message:

Check logs if error occurred at time of installation.

C:\emagent\core\12.1.0.1.0\cfgtoollogs\cfgfw\CfmLogger<timestamp>.log

SQL server target addition into oem 12c

• Discovering Targets
From the Setup menu, select Add Target and then Add Targets Manually

In the Add Targets Manually page, select Add Targets Declaratively by Specifying Target Monitoring Properties, select Target Type as Microsoft SQL Server, select a Monitoring Agent and click Add Manually.
Before adding target verify agent status by emctl status agent on windows server.
Fill the target property.

Target Name: Unique target name across all Cloud Control targets, such as MSSQL_Hostname. This is the display name in Cloud Control. It represents this SQL Server target across all user interfaces within Cloud Control.
For E.g. MSSQL_ ---.-.com
Monitoring Database Host Credentials
- These properties keep blank as doesn’t for this configuration.
- Monitoring Database Credentials
  - Database Username (Required for SQL Authentication): Valid user for the database in sysadmin fixed server role.
  - Username is ****
  - Database Password (Required for SQL Authentication): Corresponding password for the database user.
  - Password for ***_USER i
  - Confirm Database Password (Required for SQL Authentication): Confirm the password entered for the database user.
  - Database Role (Optional): Role assigned to the database user.
  - Confirm Database Role: Confirm database role entered for database user.
  - Properties
    - JDBC Driver: (Optional) Microsoft SQL Server 2005 JDBC driver class name.

For example,

com.microsoft.sqlserver.jdbc.SQLServerDriver

JDBC URL: URL for JDBC. It is recommended that the host name provided in the JDBC URL should be a fully qualified domain name (FQDN). The default port number for Microsoft SQL Server is 1433. You can specify either IP Address or host name. If you are monitoring a Microsoft SQL Server Cluster, then specify the IP address or host name of the virtual SQL server of the cluster (Note: this is not the same as the IP address or host name of the Windows cluster). For example:

jdbc:sqlserver:// ****:1433

You do not need to include the port number if your instance is using the default of 1433.

TROUBLESHOOTING

There may be possibility of agent not properly configured. It requires complete de-installation with certain steps.
Use this document for complete de-installation
https://docs.oracle.com/cd/E29505_01/install.1111/e24089/deinstall_agent.htm
Don’t forget to do after deinstall steps with regedit entry removal.
At time of installation, if any error comes, it will be identified by logs.
C:\emagent\core\12.1.0.1.0\cfgtoollogs\cfgfw\CfmLogger<timestamp>.log
Due to wrong agent password agent unable to secure agent.
Follow steps to manually secure agent. For Agent secure related logs available at sysman/config/log/secure.log

Execute the command: cmd /c set NEED_EXIT_CODE=1&&C:\emagent\agent_inst\bin\emctl.bat secure agent
Start the agent by executing the command: C:\emagent\agent_inst\bin\emctl start agent
Add the targets by executing the command: C:\emagent\agent_inst\bin\emctl config agent addinternaltargets

References

https://docs.oracle.com/cd/E24628_01/install.121/e35211/discovery.htm#SQLPG152

https://docs.oracle.com/cd/E29505_01/install.1111/e24089/deinstall_agent.htm

Oracle 12c : How To Purge The UNIFIED AUDIT TRAIL

2017-12-02T18:54:00.002+05:30

Oracle 12c : How To Purge The UNIFIED AUDIT TRAIL

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.

GOAL

How to purge the UNIFIED_AUDIT_TRAIL table ?

SOLUTION

The unified audit trail can be purged using the DBMS_AUDIT_MGMT package:

qlplus / as sysdba

SQL> select count(*) from unified_audit_trail;

COUNT(*)
----------
2619

SQL>

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

PL/SQL procedure successfully completed.

SQL> select count(*) from unified_audit_trail;

COUNT(*)
----------
1

You can also use the last_arch_timestamp, if you don't want to purge all the audit record and kept the most recent record:

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,last_archive_time => TO_TIMESTAMP('10-SEP-0714:10:10.0','DD-MON-RRHH24:MI:SS.FF'));
END;
/

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

Since Unified Auditing caches the audit trail in memory to implement a 'lazy write' feature that helps performance, some of the records eligible for deletion may still linger in the cache, to also first flush this cache add a call to: DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; before the call to
dbms_audit_mgmt.clean_audit_trail, this will cause more consistent / expected results.

BEGIN
DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
END;
/

enable the Pure Unified Auditing

In order to enable to Pure Unified Auditing you will need to perform the following steps:

1. Shutdown your Oracle Databases and listeners that are associated to the Oracle Home

2. Relink the Oracle executable to support the Unified Auditing

3. Start your Oracle instances and listener

In order to relink the Oracle executable you will need to execute the "make" command from the $ORACLE_HOME/rdbms/lib directory:

make -f ins_rdbms.mk uniaud_on ioracle

Now, after the relink has been completed, let's query V$OPTION again to verify that the Unified Auditing feature is enabled:

SQL> select value
from V$OPTION
where parameter = 'Unified Auditing';

VALUE
-------------------------------------------
TRUE

References

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=28339281456777&id=1582627.1&_afrWindowMode=0&_adf.ctrl-state=14jj63o4eb_4

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

https://www.toadworld.com/platforms/oracle/b/weblog/archive/2015/11/23/review-of-oracle-12c-unified-auditing

Oracle 11g:OEM grid control 11gR1 installation and configuration on Windows Server (64 Bit)

2015-04-21T15:55:00.001+05:30

Oracle 11g:OEM grid control 11gR1 installation and configuration on Windows Server (64 Bit)

This post is mainly written for Installation of Oracle 11gR1(11.1.0.1.0) OEM grid control on windows server.

We will install it on separate grid server which OS is windows server 2012. 64 bit.

Perquisites..

Oracle Database for repository of OEM grid control.

All servers hosts names added to hosts files on server.

Installation of Weblogic server.

We will go steps by steps to complete grid control configuration.

1.Installation of JDK.

Read on this link..
http://oracle-mssql-dba.blogspot.in/2015/04/oracle-11g-grid-controlweblogic-1032.html

2.Installation of Weblogic Server for middleware.

Read on this link..
http://oracle-mssql-dba.blogspot.in/2015/04/oracle-11g-grid-controlweblogic-1032.html

3.Make necessary changes to repository database.

sqlplus / AS SYSDBA

ALTER SYSTEM SET log_buffer=10485760 SCOPE=SPFILE;
ALTER SYSTEM SET processes=500 SCOPE=SPFILE;
ALTER SYSTEM SET session_cached_cursors=200 SCOPE=SPFILE;

UNDO tablespace is at least 200M in size.

SQL> alter database datafile 'C:\ORACLE\ORCL\UNDOTBS01.DBF' resize 200m;

Database altered.

bounce DB.

deconfigure Enterprise Manager Database Control.

emca -deconfig dbcontrol db -repos drop -SYS_PWD syspassword-SYSMAN_PWD sysmanpassword

C:\Users\bhavesh>emca -deconfig dbcontrol db -repos drop -SYS_PWD oracle123 -SYS

MAN_PWD sysman

STARTED EMCA at Apr 21, 2015 4:05:51 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Enter the following information:

Database SID: orcl

Listener port number: 1521

Do you wish to continue? [yes(Y)/no(N)]: y

Apr 21, 2015 4:06:37 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at C:\Oracle\cfgtoollogs\emca\orcl\emca_201

5_04_21_16_05_51.log.

Apr 21, 2015 4:06:37 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration

WARNING: EM is not configured for this database. No EM-specific actions can be p

erformed.

Apr 21, 2015 4:06:38 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForD

BControl

WARNING: Error initializing SQL connection. SQL operations cannot be performed

Apr 21, 2015 4:06:38 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Dropping the EM repository (this may take a while) ...

Apr 21, 2015 4:11:41 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully dropped

Enterprise Manager configuration completed successfully

FINISHED EMCA at Apr 21, 2015 4:11:42 PM

3.Grid Installation..

Run setup.exe as Administrator. & follow steps.

Supply ermail address for security updates.

Skip software updates..

Install new Enterprise manager system..

Check perquisites & ignore if it is not important..

Supply middleware location..

Supply password.Keep password into in txt file.

Repository database connection details.

Supply SYSMAN password..

Supply Registration password..

Check default ports for grid configuration..

Check if any of port already used then changes according to range...

Check summary before go for installation..

Oracle 11g grid control:Weblogic 10.3.2 Installation on windows server 64 Bit

2015-04-20T08:26:00.004+05:30

Oracle 11g grid control:Weblogic 10.3.2 Installation on windows server 64 Bit

Today We will learn to installaton of Weblogic installation for middleware in grid control configuration.

This installation requires JDK with suitable version to support as it will give error while configuration so be careful while choosing JDK to install.

For us, We will use Java^TM Platform, Standard Edition 6 Development Kit.

You can also download it from http://www.oracle.com/technetwork/java/javase/downloads/java-archive-downloads-javase6-419409.html#jdk-6u18-oth-JPR

Check installed version.

Download the Weblogic server 10.3.2 from oracle.com

Do not download any other versions of Weblogic because other versions doesnt work with grid control configuration.

This version is archived from oracle site .So getting this need to create "Contact US SR" to oracle to provide edelivery link to download the weblogic server.

Once download finish, We will proceed with small easy steps for configuration of Weblogic Server.

1.Click on installer & Run it as Administrator.
Alternately by command.exe

C:>java -jar wls1032_generic.jar

2.Click next & follow the instructions.

3.Choose the middle ware home.

4.Provide Oracle support identifier or leave without fill.

5.Choose Installation Type-Typical.

6.Browse JDK version

C:\Program Files\Java\jdk1.6.0_18

7.Choose Weblogic Server Installation directory.

8.Summary.

9.Installation in progress.Monitor it until finish.

10.Once Installation Completed , Need to apply patches.

We must apply WDJ7 patch through smart patch apply utility.

11.Run bsu.cmd through command prompt.

Click Ok & continue..

12.Unzip patch p9438213_10320_Generic.zip & copy files into C:\Oracle\Middleware\utils\bsu\cache_dir location.

13.Install patch by clicking on apply

Check all patches installed .

******************************************FINISH***********************************************

Oracle 10g/11g:Install & deploy interim patch ::19148376 for bug fixes

2014-12-22T10:33:00.003+05:30

Oracle 10g/11g:Install & deploy interim patch ::19148376 for bug fixes

Dear User,

In this post, We will apply small sized interim patch for bug fixes that affect the business.

This interim patch specially developed for specific operating system level.

This interim patch doesn't affect any major release or update the software binaries.

Its developed to fix the particular ORA errors comes in production to affect the problems.

This patch can be deployed easy steps to follow.

Pr-requisites.

1. PSU Patch 11.2.0.4.0 AIX64(any other OS) Bit database software

2. Free space of around 20 GB for taking oracle binary Backup and installing Patch sets during patching

3. Take TAR Backup of the ORACLE_HOME post DATABASE stop

4. Root, Grid, Oracle OS user password

5. OPatch utility version 11.2.0.4.0 or later

6. Hot/Cold backup of database.

1.Check the running database.

[oracle@dbexprt /home/oracle]$ps -ef | grep pmon

oracle 7667950        1   0   Jul 15      - 1:52 asm_pmon_+ASM
oracle 7667950        1   0   Jul 15      - 1:52 asm_pmon_dbexperdb
oracle 44565652 39716272   0 06:01:08 pts/0 0:00 grep pmon

2.Check Database home for running databases.

cat /etc/oratab.

3.Shutdown the listeners.

ps -ef|grep tns or ps -ef | grep lsn

[oracle@dbexprt /home/oracle]$lsnrctl stop LISTENER

4.Shutdown databases.

Shutdown ASM database (if configured.)

set ORACLE_HOME= /grid11g/11.2.0.4/product/apps/grid

sqlplus / as sysasm

sql>shutdown

5.Take TAR backup of oracle binaries.

mkdir -p /backup/TAR/oracle_home_122214

cd /backup/TAR/oracle_home_122214

[oracle@dbexprt /home/oracle]$echo $ORACLE_HOME
/orahome/11.2.0.4/product/11.2.0.4

tar -cvf ./file_name.tar /orahome/11.2.0.4/product/11.2.0.4

6.Check patch history.

opatch lsinventory

7.Check opatch per-requisite.

Go to patch locaton .

mkdir -p /backup/patch/patch_122214

cd /backup/patch/patch_122214

unzip downloded patch.

change location to uncompressed patch.

cd /backup/patch/patch_122214/19148376

e.g. opatch prereq CheckConflictAgainstOHWithDetail –phBaseDir (Patch Path)

opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /backup/patch/patch_122214/19148376

If any of the patch is conflict with current patch, need to check with MOS team for the same.

Else we can continue for apply the patch.

--Refer readme file from downloaded patch.

8.Apply patch.

[oracle@dbexpert /backup/patch/patch_122214/19148376]$ opatch apply

if this command is not worked then use full opatch location.

[oracle@dbexpert /backup/patch/patch_122214/19148376]$ /orahome/11.2.0.4/product/11.2.0.4/Opatch/opatch apply

Check opatch lsintventory.

9.Post deploy activity.

Start Listeners

Start ASM Database

Start Databases.

******Approx downtime of 2-3 hours****************

Oracle 11g:SYSAUX Tablespace purging when abnormally increases by size

2014-08-16T00:38:00.005+05:30

Oracle 11g:SYSAUX Tablespace purging when abnormally increases by size

It is very obvious when we enable feature of sql profiler by

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES or increases the retention period of awr reports.

We can get the all info of space utilization of sysaux tablespace by awrinfo.sql .

Collect the space occupied by objects.

Report o/p look something like this...

#######################################################
(I) AWR Snapshots Information
########################################################

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 36,037.3 MB ( 88% of 40,937.9 MB MAX with AUTOEXTEND OFF )
|
| Schema SYS occupies 35,717.3 MB ( 99.1% )
| Schema SYSMAN occupies 123.3 MB ( 0.3% )
| Schema SYSTEM occupies 93.1 MB ( 0.3% )
| Schema MDSYS occupies 56.1 MB ( 0.2% )
| Schema OLAPSYS occupies 15.6 MB ( 0.0% )
| Schema ORDDATA occupies 10.3 MB ( 0.0% )
| Schema WMSYS occupies 7.4 MB ( 0.0% )
| Schema CTXSYS occupies 6.3 MB ( 0.0% )
| Schema EXFSYS occupies 3.9 MB ( 0.0% )
| Schema DBSNMP occupies 3.1 MB ( 0.0% )
| Schema ORDSYS occupies 0.6 MB ( 0.0% )
| Schema DMSYS occupies 0.3 MB ( 0.0% )
| Schema APPQOSSYS occupies 0.3 MB ( 0.0% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ----------------
| SQL_MANAGEMENT_BASE SYS 31,764.9 MB
| SM/AWR SYS 2,371.9 MB
| EM SYSMAN 123.3 MB
| SM/ADVISOR SYS 108.1 MB
| SM/OPTSTAT SYS 92.9 MB
| LOGMNR SYSTEM 91.6 MB
| SDO MDSYS 56.1 MB
| AO SYS 31.8 MB
| XSOQHIST SYS 31.8 MB
| JOB_SCHEDULER SYS 22.2 MB
| XSAMD OLAPSYS 15.6 MB
| SM/OTHER SYS 10.8 MB
| ORDIM/ORDDATA ORDDATA 10.3 MB
| SMON_SCN_TIME SYS 7.4 MB
| WM WMSYS 7.4 MB
| TEXT CTXSYS 6.3 MB
| EXPRESSION_FILTER EXFSYS 3.9 MB
| PL/SCOPE SYS 3.8 MB
| EM_MONITORING_USER DBSNMP 3.1 MB
| LOGSTDBY SYSTEM 1.4 MB
| STREAMS SYS 1.0 MB
..
..
*************************************************************
(1d) SYSAUX usage - Unaccounted space in registered schemas
*************************************************************
|
| This section displays unaccounted space in the registered
| schemas of V$SYSAUX_OCCUPANTS.
|
| Unaccounted space in SYS/SYSTEM 1,270.7 MB
|
| Total space 1,270.7 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
| AWR size/day 52.6 MB (2,245 K/snap * 24 snaps/day)
| AWR size/wk 368.3 MB (size_per_day * 7) per instance
|
| Estimates based on 24 snaps in past 24 hours:
| AWR size/day 52.6 MB (2,245 K/snap and 24 snaps in past 24 hours)
| AWR size/wk 368.3 MB (size_per_day * 7) per instance
|

**********************************
(3a) Space usage by AWR components (per database)
**********************************

COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
ASH 1,525.3 64.3 1,444 33.8 236.8 90% : 10%
FIXED 462.1 19.5 437 10.2 71.7 55% : 45%
EVENTS 154.8 6.5 146 3.4 24.0 52% : 48%
SQL 78.1 3.3 74 1.7 12.1 66% : 34%

Based on report we estimate that
| SQL_MANAGEMENT_BASE taking more space.

So 1st we must delete any unwanted SQL Plan Baselines from the SMB.

Use below query to remove sql plan baselines from dataabase in chunks.

declare
pgn number;
begin
for I in (select distinct sql_handle from dba_sql_plan_baselines where rownum<1000)
loop
pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>i.sql_handle);
end loop;
end;

Alternatively change the query by monthwise deletion in parallel session.

declare
pgn number;
begin
for I in (select distinct sql_handle from dba_sql_plan_baselines where trunc(created,'MM')=trunc(sysdate-30,'MM'))
loop
pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>i.sql_handle);
end loop;
end;

Do it for sysdaete-60,90,180...

Now we need to shrink sysaux occupants.

lter table "SYS"."SQL$" enable row movement;
alter table "SYS"."SQL$TEXT" enable row movement;
alter table "SYS"."SQLOBJ$AUXDATA" enable row movement;
alter table "SYS"."SQL$" shrink space cascade;
alter table "SYS"."SQL$TEXT" shrink space cascade;
alter table "SYS"."SQLOBJ$AUXDATA" shrink space cascade;
alter table "SYS"."SQL$" disable row movement;
alter table "SYS"."SQL$TEXT" disable row movement;
alter table "SYS"."SQLOBJ$AUXDATA" disable row movement;
alter table "SYS"."SQLOBJ$" shrink space cascade;
alter table "SYS"."SQLOBJ$DATA" shrink space cascade;
alter table "SYS"."SQL$" modify lob ("SPARE2") (shrink space cascade);
alter table "SYS"."SQL$TEXT" modify lob ("SPARE2") (shrink space cascade);
alter table "SYS"."SQL$TEXT" modify lob ("SQL_TEXT") (shrink space cascade);
alter table "SYS"."SQLOBJ$" modify lob ("SPARE2") (shrink space cascade);
alter table "SYS"."SQLOBJ$DATA" modify lob ("COMP_DATA") (shrink space cascade);
alter table "SYS"."SQLOBJ$DATA" modify lob ("SPARE2") (shrink space cascade);
alter table "SYS"."SQLOBJ$AUXDATA" modify lob ("SPARE2") (shrink space cascade);

Now you may also disable

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESto false.

Reset awr report retention period to 7 days or more.

For complete info by metalink note.

https://support.oracle.com/epmos/faces/DocumentDisplay?id=1499542.1

Oracle 11g:Upgrade 11.2.0.x version to latest 11.2.0.4.1 patchset:Upgrade

2014-07-29T00:27:00.002+05:30

Oracle 11g:Upgrade 11.2.0.x version to latest 11.2.0.4.1 patchset:Upgrade

Once We finish the installation of 11.2.0.4 on different then we will go further for upgrade database.

For Installation,

http://oracle-mssql-dba.blogspot.in/2014/07/oracle-11gupgrade-1120x-version-to.html

Step 2.Run Pre-upgrade Information Tool

SQL>spool /utlu112i.log
SQL>@ORACLE_HOME/rdbms/admin/utlu112i.sql

Check timezone .Need to upgrade if it is require by tool.

Step 3.Run dbupgdiag.sql.Check any invalid objects in dba_registry.
Valid all Dba_registry object by running utlrp.sql

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

Step 4.Backup database.

a)take rman backup with latest spfile & controlfile.
b)take cold backup by mount the database after shutdown normal db.

Step 5.Shut down database normal.

SQL>shutdown normal.

Step 6.Set New Oracle_HOME,PATH,LIBRARY_PATH.Copy tnsnames.ora,listener.ora,spfile,pfile & password to new oracle_home location.Update /etc/oratab.

a)update oratab.
#oraupg:/u01/oracle/product/11.2.0/dbhome_1:N
oraupg:/u01/oracle/product/11.2.0.4:N
b)export ORACLE_HOME=/u01/oracle/product/11.2.0.4/
export ORACLE_SID=oraupg
c) Copy pfile,spfile & password files
cp *ora /u01/oracle/product/11.2.0.4/dbs/
d)Copy tnsnames.ora,listener.ora & sqlnet.ora
cp *ora /u01/oracle/product/11.2.0.4/network/admin/

Step 7.Upgrade database manually.

a)
[oracle@localhost admin]$ echo $ORACLE_HOME
/u01/oracle/product/11.2.0.4

cd $ORACLE_HOME/rdbms/admin

sql>sqlplus / as sysdba
sql>spool /tmp/catupgrd.log
sql>startup upgrade
sql>set echo on;
sql>@catupgrd.sql
sql>spool off;
sql>shutdown normal

catupgrd.sql will take around 5 hours to finish on single database.Do parallel if more than 1 databases.

b)Check catupgrd.sql spool file for errors.

c)Restart database normal.

d)SQL>@$ORACLE_HOME/rdbsm/admin/catuppst.sql

e)SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

f)Run dbupgdiag.sql scirpt for invalid objects in dba_registry. and no invalid objects in dba_objects.

Step 8.Gather stats of whole database.

******************************************FINISH*******************************************************

Oracle 11g:Upgrade 11.2.0.x version to latest 11.2.0.4.1 patchset:Installation

2014-07-28T23:16:00.000+05:30

Oracle 11g:Upgrade 11.2.0.x version to latest 11.2.0.4.1 patchset

For Applying patchset on oracle 11.2.0.1 database.We need to 1st download all the requiesite downloads from support.oracle.com.

Before upgrade start,Download the below files from oracle.com

1.p18139609_112040_Linux-x86-64

2.p13390677_112040_Linux-x86-64_1of7

3.p13390677_112040_Linux-x86-64_2of7

4.p13390677_112040_Linux-x86-64_3of7

5.p13390677_112040_Linux-x86-64_4of7

6.p13390677_112040_Linux-x86-64_5of7

7.p13390677_112040_Linux-x86-64_6of7

8.p13390677_112040_Linux-x86-64_7of7

Now, follow below steps to complete the task of upgradation of 11.2.0.1 to 11.2.0.4 patchset.

Estimated time for downtime is around 10hours.

Step1.Install Oracle 11.2.0.4 yo different home.

a) Create directory inside current home

mkdir /u01/oracle/product/11.2.0.4

chown -R oracle:dba /u01/oracle/product/11.2.0.4

b)unzip the above all .zip files to particular location.

c)Connect to oracle & run the installer
cd /oracle11g/11.2.0.4/database

./runInstaller

Click next,
Click on fix & check agaian.
After that run /tmp/CVU_11.2.0.1.0_oracle/runfixup.sh at root level.
Click Ok to continue.

check ignore all.

Click Yes.

For real time production, Do not go further untill all requirement fulfill.

It will take next 30 minutes to finish the installation.

Run root.sh at root level.

Logs--

[root@localhost 11.2.0.4]# /u01/oracle/product/11.2.0.4/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/oracle/product/11.2.0.4

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
[root@localhost 11.2.0.4]#

Click Ok to finish the installation.

Go to Upgrade .

http://oracle-mssql-dba.blogspot.in/2014/07/oracle-11gupgrade-1120x-version-to_29.html

Oracle 11g:RMAN restore database with skip tablespace

2014-07-24T01:46:00.000+05:30

Oracle 11g:RMAN restore database with skip tablespace

Dear User,

Sometime we need to restore particular tablespace from rman full backup.

Sometime we need some data from one particular tablespace not whole database.

Oracle allow selection of tablespaces to be excluded from restoration.

Follow below steps for do testing.

1.Restore database with different database.

Set db_name=orcl

set db_unique_name=orcl1.

2.Start database in nomount.Restore controlfile from backup.

rman target /

startup nomount

restore controlfile from '/u01/backup/ctl_c-1381347609-20140723-00';

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/orcl1/control01.ctl
output file name=/u01/oradata/orcl1/control02.ctl
Finished restore at 24-JUL-14
RMAN>

3.Catalog backup pieces.

rman target /

catalog start with '/u01/backup'

4.Create restore recover script.

run{
set newname for datafile 1 to '/u01/oradata/orcl1/system01.dbf';
set newname for datafile 2 to '/u01/oradata/orcl1/sysaux01.dbf';
set newname for datafile 3 to '/u01/oradata/orcl1/undotbs01.dbf';
set newname for datafile 4 to '/u01/oradata/orcl1/users01.dbf';
set newname for datafile 5 to '/u01/oradata/orcl1/example01.dbf';
set newname for datafile 6 to '/u01/oradata/orcl1/include1.dbf';
set newname for datafile 7 to '/u01/oradata/orcl1/exclude1.dbf';
restore database skip tablespace "EXCLUDE1";
switch datafile all;
recover database skip tablespace "EXCLUDE1";
sql'alter database datafile 7 offline drop';
sql'alter database open resetlogs';
}

Starting restore at 24-JUL-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/oradata/orcl/include1.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/rman_l0_02pe5b38_1_1.rman
channel ORA_DISK_1: piece handle=/u01/backup/rman_l0_02pe5b38_1_1.rman tag=TAG20140723T232112
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 24-JUL-14

Starting recover at 24-JUL-14
using channel ORA_DISK_1

Executing: alter database datafile 7 offline
Finished recover at 24-JUL-14

RMAN> sql'alter database open resetlogs';

sql statement: alter database open resetlogs

5.Check database.If its present then drop it manually.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
INCLUDE1

7 rows selected.

SQL>

Oracle 11g:Daily export metadata of databases.

2014-07-13T18:40:00.000+05:30

Oracle 11g:Daily export metadata of databases.

Sometime we almost forgot about to take backup of sources like PL/SQL.table DDL before updating.

We already have fully sync DR Database and we lost when we had done mistake on our work.Fully sync will also will make big trouble.

So its better to keep backup of metadata of whole database atleast twice a day.

some prerequisite to do setup automate it.

a) Create mount point with sufficient space for one month of db backup.its almost one gb require for one day.SO Mount point should have sufficient space like 30Gb.

b)DB Directory created in db.Full access of SYSTEM User to access directory in read, write mode.

1.Use below script.FULL_METADATA_EXPORT.sh

#!/usr/bin/ksh
######## For Daily Database FULL metadata export####
set -a

export ORACLE_SID=DBEXPERT
export ORACLE_HOME=/oracle11g/11.2.0.4/products/apps/11.2.0.4
export PATH=$ORACLE_HOME/bin:$PATH

ddy=`date +%a`
dt=`date +%Y%m%d`
echo $dt
cd /metadata/
expdp system/**** CONTENT=METADATA_ONLY COMPRESSION=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=metadata_$dt.dmp logfile=metadata_$dt.log full=y

status=`tail -10 /metadata/FULL_METADATA_EXPORT.log|grep "successfully"`
echo $status
len=${#status}
echo $len

if [[ $len -eq 0 ]];
then
uuencode /metadata/FULL_METADATA_EXPORT.log DBEXPERT_METADATA.log | mailx -s "DBEXPERT:Metadata export failed" dba@de.com
else
uuencode /metadata/FULL_METADATA_EXPORT.log DBEXPERT_METADATA.log | mailx -s "DBEXPERT:Metadata export successfully completed" dba@de.com
fi;

Oracle AIX 11g:Automate archive destination notification in mail

2014-07-13T18:22:00.003+05:30

Oracle AIX 11g:Automate archive destination notification in mail

This post to get the notification when filesystem archive destination reach at threshold values.

1.Use below script.automate_arc.sh

#!/usr/bin/ksh
######## For Automate disk untilization Mail####
set -a

export ORACLE_SID=DBEXPERT
export ORACLE_HOME=/oracle11g/11.2.0.4/products/apps/11.2.0.4
export PATH=$ORACLE_HOME/bin:$PATH

cd /oracle11g/products/apps/11.2/scripts

sqlplus -s   / as sysdba <<EOF
set lines 150;
set pages 100;
spool /oracle11g/products/apps/11.2/scripts/loc.log
archive log list
spool off

EOF

arc_loc=`cat /oracle11g/products/apps/11.2/scripts/loc.log|grep "Archive destination"`
arc_dest=${arc_loc:21}

echo $arc_dest

df -gt $arc_dest > res.log
capacity=`awk -F" " '{ print $5 }' res.log`
echo $capacity
perc=${capacity:5}
perc1=`echo $perc| tr -d '%'`
echo $perc1
if [[ $perc1 -gt 90 ]]; then
echo "Dear TEAM," >/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log

echo "Kindly log a call with Oracle DB Team."   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Call Type           SR Call"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Service Type / Domain       Oracle DBA"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Classification           Automated Mail:DB Archive destionation almost 90% full"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Priority               High"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Engineer Name           By default Auto Alert Staff"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Call source           Email"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Title               Automated Mail:DB Archive destionation almost 90% full"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Call Description   "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Dear Team,"   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Please do disk clean up on archive destination."   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "Its is almost 90%."   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "                     "   >>/oracle11g/products/apps/11.2/scripts/ticket.log
echo "**This is automated mail generated**"   >>/oracle11g/products/apps/11.2/scripts/ticket.log

mailx -s "Automated Mail:DB Archive destionation almost 90% full" dba@de.com dba2 de.com< /oracle11g/products/apps/11.2/scripts/ticket.log
fi

rm /oracle11g/products/apps/11.2/scripts/loc.log
rm /oracle11g/products/apps/11.2/scripts/ticket.log
rm /oracle11g/products/apps/11.2/scripts/res.log

2.Run the script.

bash automate_arc.sh

Oracle 11g:RMAN Backup Database in NOARCHIVELOG MODE:Mount state

2014-07-13T17:56:00.000+05:30

Oracle 11g:RMAN Backup Database in NOARCHIVELOG MODE

Dear User,

Today we'll see how to take rman compressed backup when database in noarchivelog mode.

For that ,Database should be in mount state because database needs to be in consistent state for taking compressed full backup where no changes happened during backup.

1.Use below script.

vi RMAN_DBEXPERT_COMPRESSED_LO.sh

export ORATAB=/etc/oratab

export ORACLE_BASE=/orahome/11.2.0.4

export ORACLE_HOME=/orahome/11.2.0.4/product/11.2.0.4/

export TNS_ADMIN=$ORACLE_HOME/network/admin

export PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID=dbexport

touch /rman/Offline/dbexpert_rman/bckstart.time

sleep 60

ddy=`date +%a`

dt=`date +%Y%m%d`

echo "Backup Started on `date`" > /rman/Offline/dbexpert_rman/${dt}_${ddy}_dbexpert_lvl0.log

rman target / trace /rman/Offline/dbexpert_rman/${dt}_${ddy}_dbexpert_lvl0.log << EOF

RUN {

ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;

ALLOCATE CHANNEL disk2 DEVICE TYPE DISK;

ALLOCATE CHANNEL disk3 DEVICE TYPE DISK;

ALLOCATE CHANNEL disk4 DEVICE TYPE DISK;

ALLOCATE CHANNEL disk5 DEVICE TYPE DISK;

SHUTDOWN IMMEDIATE

STARTUP MOUNT

backup as compressed backupset incremental level 0 FORMAT '/rman/Offline/dbexpert_rman/%T_${ddy}_lvl0_%d_%U.%p' database maxsetsize 30G;

release channel disk1;

release channel disk2;

release channel disk3;

release channel disk4;

release channel disk5;

ALLOCATE CHANNEL disk3 DEVICE TYPE DISK;

backup format '/rman/Offline/dbexpert_rman/%T_${ddy}_lvl0_Controlfile_%s.ctl' current controlfile;

backup format '/rman/Offline/dbexpert_rman/%T_${ddy}_lvl0_Spfile_%s.ora' spfile;

report schema;

}

EOF

touch /rman/Offline/dbexpert_rman/ftp.time

exit;

2. Run in background.Check db is running.

nohup sh RMAN_DBEXPERT_COMPRESSED_LO.sh &

Oracle 11g:Installation of Redhat linux version 6 on Oracle VM for Oracle 11g RAC

2014-06-26T23:41:00.000+05:30

Oracle 11g:Installation of Redhat linux version 6 on Oracle VM for Oracle 11g RAC

Dear User,

We are now configuring Oracle VM For REDHAT Linux-06 for installation of Oracle 11g Grid Infrastructure for Oracle 11g RAC.

Follow step without making any changes.

Click option 1st.

Choose Skip.

Select Language

Choose Option 1

Choose Re-Initialize all.

Provide Hostname with domain name.

TImezone Asia/Kolkata.

provide root password.Here we gave root@123

Choose installation type for disk space .

Choose Write changes to disk.

Choose appropriate option as per your requirements.

use ==

Use this option if you are not creating user.

disable kdump.

disable firewall.

Change the display settings.reboot the machine.

Now ready to work for Oracle RAC.