OracleAppsDBAKK1

Thursday, March 7, 2024

Loading data into Autonomous Data Warehouse using Datapump

Oracle introduced Autonomous Data warehouse over a year ago, and one of the most common questions that customers ask me is how they can move their data/schema's to ADW (Autonomous Data Warehouse) with minimal efforts. My answer to that is to use datapump, also known as expdp/impdp. ADW doesn't support traditional import and export, so you have to use datapump. Oracle suggests using schema and parallel as a parameter while using datapump. Use the parallel depending upon the number of OCPU that you have for your ADW instance. Oracle also suggests to exclude index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link. This is done in order to save space and speed up the data load process. At present you can only use data_pump_dir as an option for directory. This is the default directory created in ADW. Also you don't have to worry about the performance of the database since ADW uses technologies like storage indexes, Machine learning, etc to achieve the optimal performance. You can use the file stored on Oracle Object Storage, Amazon S3 storage and Azure Blob Storage as your dumpfile location. I will be using Oracle Object storage in this article.

We will be using the steps below to load data:

1) Export the schema of your current database using expdp

2) Upload the .dmp file to object storage

3) Create Authentication Token

4) Login into ADW using SQLPLUS

5) Create credentials in Autonomous Data Warehouse to connect to Object Storage

6) Run import using Datapump

7) Verify the import

Instead of writing more, let's show you how easy it is to do it.

Step 1 : Export the schema of your current database using expdp

Use the expdp on your current database to run export. Copy that dump file put it in a location from where you can upload it to object storage.

Step 2 : Upload the .dmp file to object storage.

In order to upload the .dmp file on object storage log in into your cloud console and click object storage:

Once in object storage, select the compartment that you want to use and create a bucket. I am going to use compartment "Liftandshift" and create bucket "LiftandShiftADW".

Bucket in Object Storage

Next click on the bucket and click upload to upload the .dmp file.

Upload .dmp file

At this point either you can use CLI (Command line Interface) or GUI (Graphic User interface) to upload the .dmp file. If your .dmp file is larger that 2Gib then you have to use CLI. I am going to use GUI since i have a small schema for the demonstration purpose.

Select the .dmp file that you want to upload to object storage and then click upload object.

Once you're done, your .dmp file will show up under objects in your Bucket Details Section

Step 3 : Create Authentication Token

Authentication Token will help us access Object Storage from Autonomous DB.

Under Governance and Administration Section, Click on Identity tab and go to users

Click on authorized user id and then click on Auth Token under resources on the left side to generate the Auth token.

Click Generate Token, give it a description, and then click Generate token again and it will create the token for you. Remember to save the token. Once the token is created and saved, you won't be able to retrieve it again.

You can click on the copy button and copy the token to a notepad. Remember to save the token because you will not be able to see the token again. Once done, you can hit the close button on the screen.

Step 4 : Login into ADW using SQLPLUS

Go to ADW homepage and click on the ADW database you have created.

Once in the database page click on DB Connection.

Click on the Download button to download the wallet. Once the zip file is downloaded, hit the close button.

Download the latest version of instant-client from Oracle website : https://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html

Unzip all the files in one location. I used the location "C:\instantclient\instantclient_18_3" on my system. Once unzipped you will be able to use sqlplus.exe and impdp.exe at that location. Also move the compressed wallet file to that location and unzip the file.

Next update the entries in the sqlnet.ora file and point it to the location of your wallet. I have changed mine to "C:\instantclient\instantclient_18_3" as shown below.

Test the connectivity using sqlplus.exe and make sure you are able to connect using the user-id admin.

Step 5: Create credentials in Autonomous Data Warehouse to connect to Object Storage

Use the below script to create credentials in ADW, and use the Authentication token created earlier as the password.

BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'DEF_CRED_NAME',
username => 'oracleidentitycloudservice/ankur.saini@oracle.com',
password => '<password>' <------------ (Use Authentication token Value here instead of the password)
);
END;
/

Step 6 : Run import using Datapump

Since my ADW instance is built using 1 OCPU, I won't be using parallel as an option. I used the script below to run the import:

./impdp.exe admin/<Password>@liftandshift_high directory=data_pump_dir credential=def_cred_name dumpfile= https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/orasenatdoracledigital05/AnkurObject/hrapps.dmp exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link

Step 7: Verify the import

You can see how easy it is to move the data to ADW, and that there is not a huge learning curve. Now you can be more productive and focus on your business.

Reference:

https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/load-data.html#GUID-297FE3E6-A823-4F98-AD50-959ED96E6969

https://blogs.oracle.com/datawarehousing/post/loading-data-into-autonomous-data-warehouse-using-datapump

Wednesday, February 14, 2024

Creating an ACFS file system on OCI DB system

In an DB system on OCI if we want to add any file system then we can use the ACFS file system.

We can create an ACFS file system using below

1) Login to DB system with grid user

2) Create a Volume by picking space from DATA disk group.

[grid@foa ~]$ asmcmd lsdg

State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 512 4096 4194304 262144 4372 0 4372 0 Y DATA/

MOUNTED EXTERN N 512 512 4096 4194304 262144 257212 0 257212 0 N RECO/

asmcmd volcreate -G data -s 20G oradata1

3) Validate the volume created

[grid@foa ~]$ asmcmd volinfo -G data oradata1

Diskgroup Name: DATA

Volume Name: ORADATA1

Volume Device: /dev/asm/oradata1-377

State: ENABLED

Size (MB): 20480

Resize Unit (MB): 64

Redundancy: UNPROT

Stripe Columns: 8

Stripe Width (K): 1024

Usage:

Mountpath:

4) We can check the volume physical details.

[grid@foa ~]$ fdisk -l /dev/asm/oradata1-377

Disk /dev/asm/oradata1-377: 21.5 GB, 21474836480 bytes, 41943040 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 4096 bytes

I/O size (minimum/optimal): 4096 bytes / 1048576 bytes

5) Format the new volume as ACFS

[grid@foa ~]$ /sbin/mkfs -t acfs /dev/asm/oradata1-377

mkfs.acfs: version = 19.0.0.0.0

mkfs.acfs: on-disk version = 46.0

mkfs.acfs: volume = /dev/asm/oradata1-377

mkfs.acfs: volume size = 21474836480 ( 20.00 GB )

mkfs.acfs: Format complete.

6) Register the mount

Exit from grid user and connect as root.

mkdir /backup

/sbin/acfsutil registry -a /dev/asm/oradata1-377 /backup -u oracle

7) Verify

df -h

/dev/asm/oradata1-377 20G 414M 20G 3% /backup

https://www.funoracleapps.com/2022/11/creating-acfs-file-system-on-oci-db.html

Tuesday, February 6, 2024

Troubleshooting RAC RapidClone issues with Oracle Applications R12.0 & R12.1 (Doc ID 1303962.1)

Section 4: Known Issues

a) Final DB Startup fails if Flash Recovery is enabled

Cause:

Potential Solution/Workaround:

b) On Secondary nodes, adclonectx.pl fails with Instance Number n used by host

Cause:

Solution:

c) RAC to RAC : Inventory Registration still references the source cluster nodes

Problem:

Cause:

Solution:

d) RAC to non-RAC : Inventory Registration still contains the source cluster nodes

Problem: RAC to non-RAC

Cause:

Solution:

FAQ: Oracle E-Business Suite and the Oracle Multitenant Architecture (Doc ID 2567105.1)

Introduced with Oracle Database 12c, the multitenant architecture enables an Oracle database to function as a container database (CDB). A pluggable database (PDB) is a portable collection of schemas, schema objects, and non-schema objects, contained within a CDB.

This document presents a series of commonly asked questions and answers about using Oracle E-Business Suite (EBS) with the multitenant architecture: for example, how many PDBs are currently supported within a CDB.

The topics are grouped in the order they are most likely to be needed by those new to this area, but can be referred to in any desired order or combination.

Section 7: Oracle E-Business Suite Multitenant Database Diagnostics
- How do I find the location of the alert log and trace files for my multitenant database?
- How do I generate an AWR snapshot report for my pluggable database?

There is a change log at the end of this document.

Section 1: General Questions

How does Oracle E-Business Suite support the multitenant architecture?

Oracle E-Business Suite supports the multitenant architecture with a single CDB containing a single pluggable Oracle E-Business Suite database (PDB) in the following combinations:

Oracle E-Business Suite 12.2.3 and later with Oracle Database 19c on-premises
Oracle E-Business Suite 12.2.3 and later with Oracle Database 12cR1 VM DB Systems or Oracle Database 12cR1 in an Exadata DB System
Oracle E-Business Suite 12.1.3 with Oracle Database 19c on-premises
Oracle E-Business Suite 12.1.3 with Oracle Database 12cR1 VM DB Systems or Oracle Database 12cR1 in an Exadata DB System

How many PDBs are supported in a CDB that is running Oracle E-Business Suite?

At present, Oracle E-Business Suite supports a single PDB (single tenant) in a CDB.

Are there additional licensing requirements?

Oracle E-Business Suite currently supports a single pluggable database (single tenant) with a CDB architecture (see previous question). A single PDB in a CDB does not require licensing the Oracle Multitenant database option.

For more information, refer to the following resources:

Do I need to make any Oracle E-Business Suite Infrastructure changes?

Whether you need to make changes to your Oracle E-Business Suite environment depends on where your environment resides:

For on-premises Oracle E-Business Suite environments:
- As part of the upgrade of your Oracle E-Business Suite Database to 19c you will convert your database to the CDB architecture with a single pluggable database (PDB). For more information, refer to the following My Oracle Support knowledge documents:
  - Document 2552181.1, Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c
For Oracle E-Business Suite environments on Oracle Cloud Infrastructure (OCI):
- If you are moving to a database service (1-node or 2-node VM DB System, or Exadata DB System) as part of the migration of an Oracle E-Business Suite 12c database, you will need to convert the database to the CDB architecture.
  - For more information, refer to Section 3, Certifications and Automations, of My Oracle Support Knowledge Document 2517025.1, Getting Started with Oracle E-Business Suite and Oracle Cloud Infrastructure.

Section 2: Oracle E-Business Suite Container Database (CDB)

Note: Except for cloning, the questions and answers in this section apply both to on-premises and OCI environments.

How do I source the Oracle E-Business Suite CDB environment?

Oracle E-Business Suite database CDB environment files are located in the database ORACLE_HOME. Run the following commands:

$ cd $ORACLE_HOME
$ source <CDB_NAME>_<NODE_NAME>.env

How do I connect to the Oracle E-Business Suite CDB as SYSDBA?

Source the CDB environment file in the ORACLE_HOME and then use SQL*Plus to connect to the CDB as SYSDBA:

$ cd $ORACLE_HOME
$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"

How do I start the CDB that hosts the Oracle E-Business suite PDB?

You will use either SQL*Plus or srvctl, depending on whether you are using a single-node instance or Oracle RAC system.

On a single-node instance, run the following commands:
$ source <CDB_NAME>_<NODE_NAME>.env $ sqlplus "/ as sysdba" SQL> startup;
On an Oracle RAC system, run the following commands:
$ source <CDB_NAME>_<NODE_NAME>.env $ srvctl start database -d <CDB name>

How do I shut down the CDB that hosts the Oracle E-Business Suite PDB?

You will use either SQL*Plus or srvctl, depending on whether you are using a single-node instance or Oracle RAC system.

On a single-node instance, run the following commands:
$ source <CDB_NAME>_<NODE_NAME>.env $ sqlplus "/ as sysdba" SQL> shutdown normal;
On an Oracle RAC system, run the following commands:
$ source <CDB_NAME>_<NODE_NAME>.env $ srvctl stop database -d <CDB name>

How do I clone an Oracle E-Business Suite environment with a CDB?

In on-premises environments, the Oracle E-Business Suite Rapid Clone utility can be used to clone both the CDB and PDB together.

Section 3: Oracle E-Business Suite Pluggable Database (PDB)

How do I source the Oracle E-Business Suite PDB environment?

The Oracle E-Business Suite database PDB environment files are located in the database ORACLE_HOME:

$ cd $ORACLE_HOME
$ source <PDB_NAME>_<NODE_NAME>.env

How do I connect to the Oracle E-Business Suite PDB as SYSDBA?

Source the CDB environment file, export the Oracle E-Business Suite PDB name, then use SQL*Plus to connect to the PDB as SYSDBA:

$ source <CDB_NAME>_<NODE_NAME>.env
$ export ORACLE_PDB_SID=<PDB NAME>;
$ sqlplus "/ as sysdba"

How do I open the Oracle E-Business Suite PDB?

Source the CDB environment file, connect to the CDB as SYSDBA, then execute the SQL shown in the example to start the PDB:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> alter pluggable database <EBS PDB Name> open read write services=all;

How do I close the Oracle E-Business Suite PDB?

Source the CDB environment, connect to the CDB as SYSDBA, then execute the SQL shown in the example to close the PDB:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> alter pluggable database <EBS PDB Name> close immediate;

There is also the option to save the state of the PDB. Oracle will then preserve the mode when you restart the CDB. You can find the current saved state by querying DBA_PDB_SAVED_STATES:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> alter pluggable database <EBS PDB Name> save state;
SQL> alter pluggable database <EBS PDB Name> close immediate;

How do I find Oracle E-Business Suite PDB information and status?

The following SQL returns the values for the con_id, con_name, open mode, restricted values of all your PDBs.

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> show pdbs;

Alternatively, you could use the following SQL to return only the values for name and open mode:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> select name, open_mode from v$pdbs;

How do I access the Oracle E-Business Suite PDB while logged into the CDB?

Use the following command to set the environment, show the PDB name, and then connect to that PDB:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> show pdbs;
SQL> alter session set container=”PDBNAME”;

Where do I look for PDB errors if I encounter a problem?

Source the environment and then review any plugin violations, as shown in the following example:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> select name, cause, message, status from PDB_PLUG_IN_VIOLATIONS;

Can I unplug an Oracle E-Business Suite PDB from one CDB and plug it into another CDB?

At present, there is no supported procedure to unplug an Oracle E-Business Suite PDB and plug (relocate or clone) it to another CDB.

What parameters in the Oracle E-Business Suite database context file support the multitenant architecture?

Context variables used to support the multitenant architecture are as follows:

s_pluggable_database: This variable is set to TRUE in a multitenant database.
s_pdb_name: This variable is set to PDB name.
s_cdb_name: This variable is set to CDB SID.
s_cdb_unique_name: This variable is set to the DB unique name for the CDB.
s_dbService: This variable is set to ebs_<PDB name> in a multitenant database.
s_db_tenancy: This variable is set to multi-tenant in a multitenant database.
s_cdb_tnsadmin: This variable is set to $ORACLE_HOME/network/admin.

Before running AutoConfig on the database tier, do I source the PDB environment or CDB environment?

You should always source the Oracle E-Business Suite PDB environment before running AutoConfig. You can do so by running the following commands:

$ cd $ORACLE_HOME
$ source <PDB_NAME>_<NODE_NAME>.env

Can I export an Oracle 12c non-CDB database and import it into an Oracle 19c PDB?

Data from a non-CDB database can be directly imported into a PDB using the Data Pump export/import commands. Refer to My Oracle Support Knowledge Document 2554156.1, Export/Import Process for Oracle E-Business Suite Release 12.2 Database Instances Using Oracle Database 19c.

Section 4: Oracle E-Business Suite Multitenant Database SQL and Scripts

How do I set up the environment to run an Oracle E-Business Suite Script or SQL session?

Source the Oracle Home <CDB_NAME>_<HOSTNAME>.env and set the PDB environment variable ORACLE_PDB_SID=<PDB_NAME> before running Oracle E-Business Suite programs such as adgrants.sql.

$ source <CDB_NAME>_<NODE_NAME>.env
$ export ORACLE_PDB_SID=<PDB_NAME>
$ sqlplus "/ as sysdba"

Section 5: Oracle E-Business Suite Multitenant Database Patching

How do I run the EBS Technology Codelevel Checker (ETCC) against my Oracle E-Business Suite PDB?

The EBS Technology Codelevel Checker (ETCC) utility provides two scripts to help ensure you have the necessary database and application tier patches installed on your Oracle E-Business Suite Release 12.2 instance.

ETCC extracts environment-related information from the context file (using the location defined in $CONTEXT_FILE), so you need to source the Oracle E-Business Suite PDB environment before you run the database checker script. For OCI-based environments, you also need to add the cloud=y parameter.

Download ETCC as Patch 17537119 from My Oracle Support and unzip it into a working directory. Then proceed with whichever of the following steps applies to you:

For an on-premises environment, run the commands:
$ source <EBS PDB Name>_<NODE_NAME>.env $ ./checkDBpatch.sh
For an environment in Oracle Cloud Infrastructure, run the commands:
$ source <EBS PDB Name>_<NODE_NAME>.env $ ./checkDBpatch.sh cloud=y

How do I list the OPatch inventory for a multitenant database?

You can list the OPatch inventory of a multitenant database in the same way as for non-CDB. Run the following commands:

$ source <CDB_NAME>_<NODE_NAME>.env
$ export PATH=$PATH:$ORACLE_HOME/OPatch
$ opatch lsinventory -detail

How do I set up the environment to install a database patch in an Oracle E-Business Suite multitenant database?

Source the CDB environment and add the OPatch directory to the path, as shown in the following example:

$ source <CDB_NAME>_<NODE_NAME>.env
$ export PATH=$PATH:$ORACLE_HOME/OPatch

Note: Before running datapatch, ensure that the Oracle E-Business Suite PDB is open as datapatch will only apply or roll back SQL fixes for open PDBs. For more information, refer to My Oracle Support Knowledge Document 1585822.1, Datapatch: Database 12c Post Patch SQL Automation.

Section 6: Oracle E-Business Suite Performance Recommendations

What methodology should I follow to ensure I am getting the best performance out of Oracle E-Business Suite?

Refer to My Oracle Support Knowledge Document 2528000.1, Oracle E-Business Suite Performance Best Practices.

Where can I find real-world practical tips and lessons for tuning my Oracle E-Business Suite environment?

Refer to My Oracle Support Knowledge Document 2125596.1, Achieving Optimal Performance with Oracle E-Business Suite.

Where do I find the recommended performance patches for Oracle E-Business Suite?

Refer to My Oracle Support Knowledge Document 244040.1, Oracle E-Business Suite Recommended Performance Patches.

Where do I find the recommended initialization parameters for Oracle E-Business Suite?

Refer to My Oracle Support Knowledge Document 396009.1, Database Initialization Parameters for Oracle E-Business Suite Release 12. This document includes two main sections:

A common set of database initialization parameters that are applicable to all releases of the Oracle Database
An Oracle Database release-specific section

These two sections combine to provide a complete list of release-specific database initialization parameters.

For Oracle E-Business Suite Release 12.1 on Oracle Database 19c, refer to the following:

Document 396009.1 - Section 1: Common Database Initialization Parameters For All Releases
Document 396009.1 - Section 4: Release-Specific Database Initialization Parameters for Oracle 19c

For Oracle E-Business Suite Release 12.2 on Oracle Database 19c, also refer to the following:

Document 396009.1 - Section 5: Additional Database Initialization Parameters For Oracle E-Business Suite Release 12.2

Note: Each database section has a list of parameters in a removal list. These must be removed because they are obsolete or else the default database value is mandatory and no other value may be set.

Section 7: Oracle E-Business Suite Multitenant Database Diagnostics

How do I find the location of the alert log and trace files for my multitenant database?

Each CDB has an associated alert log, which is used to record log information, warnings and alerts about the Oracle E-Business Suite PDB. It is located in the "Diag Trace" of the container database. Also, all PDBs plugged in a given CDB write trace data to the "Diag Trace" of the container database, which can be found by querying v$diag_info dynamic view as in the following example:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> select value from v$diag_info where name = 'Diag Trace';

How do I generate an AWR snapshot report for my pluggable database?

In Oracle Database 12c Release 1, AWR snapshots are only created at the CDB-level (CDB root). This AWR snapshot is for the whole database system in that it contains the statistical information about the CDB as well as all the PDBs in a multitenant environment.

Oracle 19c multitenant supports AWRs at the CDB and PDB level. There are two new views:

AWR_ROOT view: This shows the AWR data stored only on a CDB root, which are generally equivalent to the DBA_HIST views.
AWR_PDB view: There are a few PDB-level metrics, but the vast majority are instance-wide statistics that are not shown at this level for security reasons. Hence these will not be flushed in AWR, and will always be shown as 0 in the AWR report.

Use the following commands as a basis to create a snapshot and specify AWR_ROOT or AWR_PDB as appropriate:

$ source <CDB_NAME>_<NODE_NAME>.env
$ export ORACLE_PDB_SID=<PDB NAME>;
$ sqlplus "/ as sysdba"
! Run awrrpt.sql file
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
...
Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
...

adcfgclone.pl Rapid clone Options E-Business suite 11, R12, R12.2

adcfgclone.pl Different Components There are different components with Rapid Clone that are used when cloning an Oracle Applications instance. These are: dbTechStack (RDBMS ORACLE_HOME) database (database only, including control file creation) dbconfig (database only, with no control file creation) dbTier (both dbTechStack and database) atTechStack (Tools and Web ORACLE_HOMEs) appltop (APPL_TOP only) appsTier (both atTechStack and appltop) Troubleshooting Rapid Clone issues with Oracle Applications R12.0 & R12.1 (Doc ID 603104.1)