The Problem We were trying to run a RMAN script (running the duplicate database) from a Oracle 12c environment onto a 19c environment. The script worked perfectly fine on the 12c databases but was giving below error when run on a 19c database: RUN { allocate auxiliary channel d1 device type disk maxpiecesize 20G; allocate […]
oracle
How to get datafile information when PDB is Mounted/Read-Write/Restricted Mode
Question: How to get PDB Datafile information when PDB in different Mode? Case 1 – PDB in mounted Mode PDB Status: SQL> select pdb_id, pdb_name, con_uid,status from dba_pdbs; PDB_ID PDB_NAME CON_UID STATUS ———- ————– ———- ————- 3 [PDB12C] 515133920 NORMAL 2 PDB$SEED 4105546291 NORMAL SQL> select con_id, name, open_mode, restricted from v$pdbs; CON_ID NAME OPEN_MODE […]
Oracle Data Guard – Tempfiles created at primary are not automatically created at standby
Question: You created a temporary datafile in the Primary database, but it did not get created in the Standby database. How to create tempfiles at standby node? New tempfiles are not added automatically in the Physical Standby database because no redo is generated. Tempfiles in the Standby are only used when your Standby is opened […]
How to Audit Alter Tablespace in Oracle 12c database
A tablespace can be Audited in 12c by creating the audit policy for the tablespace action. 1. Create audit policy: SQL> create audit policy pol2 actions create tablespace, drop tablespace, alter tablespace; 2. Enable audit policy for SYSTEM user: SQL> audit policy pol2 by SYSTEM; 3. Connect to the database and run few alter statements. […]
Oracle Data Guard 12c New Feature: Far Sync Standby
A Far Sync Standby Database is a cascading Standby Database that acts as a Redo Log Repository for a Terminal Database. It does not contain any datafiles. Only Log Transport Services are active on a Far Sync Standby Database. The Advantage of a Far Sync Standby Database is that it can be a local ArchiveLog […]
How to use dbms_metadata.get_ddl to get Complete DDL for database objects
This post describes a simple way to retrieve an object definition, also called the DDL (Data Definition Language) of an object. Retrieving the complete definition (DDL) of a dictionary object (i.e. table, index, view, tablespace, etc.) can be useful in these circumstances: Confirm the object definition Transfer the object definition to another database Resolve a […]
How to Use DBMS_METADATA To Get The DDL For Objects
The DBMS_METADATA package is a powerful tool for obtaining the complete definition of a schema object. It enables you to obtain all of the attributes of an object in one pass. The object is described as DDL that can be used to (re)create it. The GET_DDL function is used to fetch the DDL for all […]
How to get DDL for User including Privileges and Roles Using dbms_metadata.get_ddl
Question: How to extract User DDL and all privileges granted including roles, system and object privileges, tablespace quota and non-default profile using dbms_metadata.get_granted_ddl? Connect to target database and execute with DBA privileges. Example for user “TEST” – Make sure to put the username in uppercase. set longchunksize 20000 pagesize 0 feedback off verify off trimspool […]
How to get the DDL for indexes using dbms_metadata
Let consider the below example, where we list all the indexes in the database. sql> select index_name from dba_indexes where tablespace_name =’EXAMPLE’ and index_name like ‘E%’ order by 1 asc; INDEX_NAME —————– EMP_EMAIL_UK EMP_EMP_ID_PK EMP_DEPARTMENT_IX EMP_JOB_IX EMP_MANAGER_IX EMP_NAME_IX Getting DDL To get the DDL: DBMS_METADATA.GET_DDL ( object_type IN VARCHAR2, name IN VARCHAR2, schema IN VARCHAR2 […]
How to rename a Pluggable Database, along with the respective directories, in Oracle 12c
Please go through the following steps to change the name of a PDB, along with the respective directories, in 12c. 1. Connect to the CDB and check the information about the PDB first: sql> select name, open_mode, restricted from v$pdbs; sql> select name, con_id, dbid,con_uid,guid from v$containers order by con_id; sql> select service_id,name,network_name,creation_date,pdb,con_id from cdb_services; […]