ORACLE DATABASE ADMINISTRATION

ORACLE DATABASE ADMINISTRATION

recent posts

about

  • Oracle 19c|RMAN – Configure a Recovery Catalog and Registering a Database

    A base recovery catalog is a database schema that stores RMAN metadata for one or more target databases, serving as a central repository for backup and recovery information.

    This post covers how to create a Recovery Catalog, register a database, and optionally set up a Virtual Private Catalog (VPC) for multi-user backup environments.

    ⚙️ Step 1: Create a Recovery Catalog User

    First, connect to the recovery catalog database (a separate database from your target) and create a dedicated user.

    CREATE USER reco_user IDENTIFIED BY password
  DEFAULT TABLESPACE cat_tbs
  QUOTA UNLIMITED ON cat_tbs;

GRANT recovery_catalog_owner TO reco_user;

    Next, execute the dbmsrmansys.sql script to grant additional privileges required for the RECOVERY_CATALOG_OWNER role:

    @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql

    🧩 Step 2: Create the Recovery Catalog

    Start RMAN and connect to the recovery catalog database as the new user:

    RMAN> CONNECT CATALOG reco_user@catdb

    When prompted, enter the password. Once connected, create the catalog:

    RMAN> CREATE CATALOG;

    ✅ This command creates the recovery catalog schema objects in the reco_user schema.

    🔗 Step 3: Register the Target Database

    In the same RMAN session, connect to your target database (the one to be backed up) and register it with the catalog.

    RMAN> CONNECT TARGET /
RMAN> REGISTER DATABASE;
RMAN> EXIT;

    This associates the target database with the recovery catalog and enables RMAN to store backup metadata in it.

    🔒 Step 4: Creating a Virtual Private Catalog (Optional)

    A Virtual Private Catalog (VPC) allows separate users (such as backup operators) to manage RMAN metadata for specific databases — ideal for multi-database or shared environments.

    Create the VPC user and grant privileges:

    CREATE USER vpc1 IDENTIFIED BY password
  DEFAULT TABLESPACE vpcusers;

GRANT CREATE SESSION TO vpc1;

    Now connect to RMAN as the catalog owner and grant VPC privileges for a specific database:

    RMAN> 
CONNECT CATALOG reco_user@catdb
RMAN> 
GRANT CATALOG FOR DATABASE prd1 TO vpc1;

    When the GRANT command is issued, Oracle automatically creates the virtual private catalog for user vpc1.

    💾 Step 5: Using the Virtual Private Catalog

    The backup operator (vpc1) can now connect and register the database for use:

    RMAN> 
CONNECT CATALOG vpc1@catdb
RMAN> 
REGISTER DATABASE prod1;

    ✅ The virtual catalog is now ready. The user vpc1 has access to metadata only for the granted database (prod1), ensuring isolation and security.

    🧾 Summary

    TaskCommand / Description
    Create Recovery Catalog UserCREATE USER reco_user ...
    Grant Catalog Owner RoleGRANT recovery_catalog_owner TO reco_user;
    Create Recovery CatalogRMAN> CREATE CATALOG;
    Register DatabaseRMAN> REGISTER DATABASE;
    Create VPC UserCREATE USER vpc1 IDENTIFIED BY password;
    Grant Catalog AccessGRANT CATALOG FOR DATABASE prd1 TO vpc1;

    💡 Key Benefits

    • Centralized RMAN metadata repository
    • Simplified backup and restore management
    • Easier monitoring for multiple databases
    • Secure isolation with Virtual Private Catalogs

  • Oracle 19c Database Create Tablespace Practical Examples

    ⚙️ 1. Creating a Bigfile Tablespace

    A Bigfile Tablespace contains a single datafile that can be very large in size.

    CREATE BIGFILE TABLESPACE bigtbs_data01
  DATAFILE 'bigtbs_data01_f1.dbf'
  SIZE 200M AUTOEXTEND ON;

CREATE BIGFILE TABLESPACE bigtbs_data02
  DATAFILE '/data01/oracle/dbs/bigtbs_data02_f1.dbf'
  SIZE 200M AUTOEXTEND ON;

CREATE BIGFILE TABLESPACE bigtbs_data03
  DATAFILE '+data01'
  SIZE 200M AUTOEXTEND ON;

    🔁 2. Creating an Undo Tablespace

    Undo tablespaces store old values of data for read consistency and rollback operations.

    CREATE UNDO TABLESPACE undotbs1
   DATAFILE 'undotbs_1.dbf'
   SIZE 100M AUTOEXTEND ON
   RETENTION GUARANTEE;

    🧊 3. Creating a Temporary Tablespace

    Used for sorting operations and temporary data storage.

    CREATE TEMPORARY TABLESPACE temp01
   TEMPFILE 'temp01.dbf' SIZE 512M AUTOEXTEND ON;

    You can also define a default file destination:

    ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/data01/oracle/dbs';
CREATE TEMPORARY TABLESPACE temp02;

    🧩 4. Adding Temporary Tablespace to a Group

    Grouping temporary tablespaces helps balance load during large sort operations.

    CREATE TEMPORARY TABLESPACE temp03
  TEMPFILE 'temp03.dbf' SIZE 512M AUTOEXTEND ON
  TABLESPACE GROUP tbs_grp01;

    📦 5. Creating Basic Tablespaces

    These are standard permanent tablespaces used to store user data.

    CREATE TABLESPACE tbs_data01 
   DATAFILE 'tbs_data01_f1.dbf' SIZE 512M ONLINE;

CREATE TABLESPACE tbs_data02 
   DATAFILE 'tbs_data02_f1.dbf' SIZE 1024M LOGGING;

    🔄 6. Enabling Autoextend for a Tablespace

    Automatically grows the datafile as more data is added up to a maximum size of 100 megabytes.

    CREATE TABLESPACE tbs02 
   DATAFILE '/data01/oracle/tbs02_f1.dbf' SIZE 500K REUSE
   AUTOEXTEND ON NEXT 500K MAXSIZE 500M;

    📊 7. Creating a Locally Managed Tablespace

    Locally managed tablespaces improve space management efficiency.

    CREATE TABLESPACE tbs_data04 DATAFILE 'tbs_data04_f1.dbf' SIZE 100M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

CREATE TABLESPACE lmt_tbs01 DATAFILE 'lmt_tbs01_f1.dbf' SIZE 100M REUSE
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

CREATE TABLE lmt_table1 (col1 NUMBER, col2 VARCHAR2(20))
   TABLESPACE lmt_tbs01 STORAGE (INITIAL 2M);

    Without uniform extents:

    CREATE TABLESPACE lmt_tbs02 DATAFILE 'lmt_tbs02_f1.dbf' SIZE 100M REUSE 
  EXTENT MANAGEMENT LOCAL;

    🔐 8. Creating an Encrypted Tablespace

    Enhances data security by encrypting data at rest.

    ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet_password";

CREATE TABLESPACE encrypt_tbs
  DATAFILE '/data/oracle/dbs/encrypt_tbs_f1.dbf' SIZE 1M
  ENCRYPTION USING 'AES256' ENCRYPT;

CREATE TABLESPACE enctbs2 DATAFILE 'enctbs2_f1.dbf' SIZE 1G 
  ENCRYPTION USING AES256 MODE 'XTS' ENCRYPT;

    ⚡ 9. Segment Space Management

    Oracle can automatically manage free space within segments.

    CREATE TABLESPACE auto_seg_tbs DATAFILE 'auto_seg_tbs_f1.dbf' SIZE 1M
   EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO;

    🧰 10. Oracle Managed Files (OMF)

    Oracle automatically manages file names and locations when OMF is enabled.  

    Creates a tablespace with an Oracle-managed data file of 100M that is not autoextensible:

    ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/data01/oracle/dbs';
CREATE TABLESPACE omf_tbs1;

CREATE TABLESPACE omf_ts2 DATAFILE AUTOEXTEND OFF;

  • ⚙️ Image-Based Installation Overview

    🚀 Simplify your Oracle Database setup!
    Image-based installation is a modern and efficient way to install and configure Oracle Database software.

    🧩 Installation Overview

    To install Oracle Database using this method:
    1️⃣ Create a new Oracle Home directory
    2️⃣ Extract the database image file into the new Oracle Home
    3️⃣ Run the setup wizard to register the Oracle Database product

    ⚙️ Key Features

    ✅ Supports both Single-Instance and Cluster (RAC) configurations
    ✅ Automatically enables all Oracle Database options such as:

    • 🧮 Oracle OLAP (olap)
    • 🧪 Oracle Real Application Testing (rat)

    💡 Why Use Image-Based Installation?

    This approach streamlines the entire installation process, reduces manual intervention, and supports automation for large-scale custom deployments.

    You can also create and deploy customized images—pre-patched with the latest Release Updates (RUs) or Monthly Recommended Patches (MRPs)—ensuring faster, consistent, and reliable installations across multiple systems.

    Oracle’s image-based installation makes deploying databases much faster and cleaner.
    Instead of going through traditional installers, you simply:

    1️⃣ Create a new Oracle Home directory
    2️⃣ Extract the image file into that Oracle Home
    3️⃣ Run the appropriate setup wizard to begin installation

    • 🧰 For Oracle Database: use runInstaller
    • ⚙️ For Oracle Grid Infrastructure: use gridSetup.sh
    Image illustrating the steps for image-based Oracle Database installation, including extracting files, running setup, and registering the product.
    Visual guide to the image-based installation process for Oracle Database, outlining setup steps and key features.

    Configure Wizard Installation Options for Creating Images

    Before starting your Oracle Database or Oracle Grid Infrastructure installation, it’s essential to decide whether you want to use any of the image-creation options available. These options simplify setup, ensure consistency, and support automation for large-scale Oracle deployments.

    🧩 Available Image-Creation Options

    Oracle provides flexible image-creation options to help you build or reuse Oracle Home directories based on your needs.

    🏷️ Option📝 Description
    -createGoldImage🪙 Creates a gold image from the current Oracle Home.
    -destinationLocation📁 Specifies the path or location where the gold image will be created.
    -exclFiles🚫 Specifies the paths to files that should be excluded from the newly created gold image.
    -help💡 Displays help information for all available options.

    🧪 Example: Creating a Gold Image

    Here’s how you can create a Gold Image using the runInstaller utility:

    cd $ORACLE_HOME
./runInstaller -createGoldImage -destinationLocation /tmp/my_db_images -exclFiles /u01/app/oracle/product/19.0.0/dbhome_1/relnotes
    cd Grid_home
./gridSetup.sh -createGoldImage -destinationLocation /tmp/my_grid_images -exclFiles /u01/app/oracle/product/19.0.0/dbhome_1/relnotes

    Where:

    /tmp/my_db_images is a temporary file location where the image zip file is created.

    /tmp/my_grid_images is a temporary file location where the image zip file is created.

    /u01/app/oracle/product/19.0.0/dbhome_1/relnotes is the file to be excluded from the newly created gold image.

  • Creating and Managing Oracle CDBs: A Step-by-Step Guide

    Creating and Managing Oracle CDBs: A Step-by-Step Guide

    Using DBCA in Silent Mode

    Create a new database named ORCL using the General Purpose template

    $ORACLE_HOME/bin/dbca -silent -createDatabase 
-templateName General_Purpose.dbc -gdbname ORCL 
-sid ORCL -createAsContainerDatabase true 
-numberOfPDBs 1 -pdbName pdb1 -useLocalUndoForPDBs true 
-responseFile NO_VALUE -characterSet AL32UTF8 
-totalMemory 1800 -sysPassword Welcome_1 
-systemPassword Welcome_1 -pdbAdminPassword Welcome_1 
-emConfiguration DBEXPRESS -dbsnmpPassword Welcome_1 
-emExpressPort 5500 -enableArchive true 
-recoveryAreaDestination /u03/app/oracle/fast_recovery_area 
-recoveryAreaSize 15000 -datafileDestination /u02/app/oracle/oradata

    • Delete a database named ORCL

    $ORACLE_HOME/bin/dbca -silent -deleteDatabase -sourceDB ORCL -sid ORCL 
-sysPassword oracle

    Creating a CDB by Using a SQLPLUS

    Start the Instance:

    Create the initCDB1.ora file and set parameters:

    – CONTROL_FILES to CDB control file names

    – DB_NAME to a CDB name

    – ENABLE_PLUGGABLE_DATABASE to TRUE

    STARTUP NOMOUNT pfile=’/tmp/initcdb1.ora’

    =>CDB$ROOT + PDB$SEED created

    Execute the $ORACLE_HOME/rdbms/admin/catcdb.sql SQL script.

    @?/rdbms/admin/catcdb.sql

    Below are the detailed steps to create a new CDB using SQL*Plus.

    1. Before starting your database instance, create a parameter file with these details:
      • DB_NAME
      • CONTROL_FILES (if you’re not using OMF)
      • DB_BLOCK_SIZE
    2. Use the CREATE DATABASE command with ENABLE PLUGGABLE DATABASE to create a CDB, including the CDB root and CDB seed. Use the SEED FILE_NAME_CONVERT clause to specify locations for CDB seed files. If omitted, OMF assigns names and locations. Ensure directories exist and note that the character set is consistent for the CDB.
    3. Run the catcdb.sql script to build views on data dictionary tables and install PL/SQL packages. Optionally, execute catalog.sql, catproc.sql, and any other related SQL scripts.

    Using the SEED FILE_NAME_CONVERT Clause

    SQL>
 CREATE DATABASE cdb1 
USER SYS IDENTIFIED BY p1 USER SYSTEM IDENTIFIED BY p2
LOGFILE GROUP 1 ('/u01/app/oradata/CDB1/redo1a.log',
'/u02/app/oradata/CDB1/redo1b.log') SIZE 100M,
GROUP 2 ('/u01/app/oradata/CDB1/redo2a.log',
'/u02/app/oradata/CDB1/redo2b.log') SIZE 100M 
CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 
EXTENT MANAGEMENT LOCAL DATAFILE 
'/u01/app/oradata/CDB1/system01.dbf' SIZE 325M 
SYSAUX DATAFILE '/u01/app/oradata/CDB1/sysaux01.dbf' SIZE 325M 
DEFAULT TEMPORARY TABLESPACE tempts1 
TEMPFILE '/u01/app/oradata/CDB1/temp01.dbf' SIZE 20M 
UNDO TABLESPACE undotbs 
DATAFILE '/u01/app/oradata/CDB1/undotbs01.dbf' SIZE 200M
ENABLE PLUGGABLE DATABASE 
SEED FILE_NAME_CONVERT =('/u01/app/oradata/CDB1','/u01/app/oradata/CDB1/seed');

    One way to set the directory for PDB seed data files is by using the SEED
    FILE_NAME_CONVERT clause, which specifies the source directory for
    CDB root data files and the target seed directory. The directories /u01/app/oradata/CDB1 and
    /u01/app/oradata/CDB1/seed must be created.

    Using the ENABLE PLUGGABLE DATABASE Clause

    Without SEED FILE_NAME_CONVERT:

    • OMF: DB_CREATE_FILE_DEST=’/u02/app/oradata’

    SQL> CONNECT / AS SYSDBA
SQL> STARTUP NOMOUNT
SQL> CREATE DATABASE cdb2 
USER SYS IDENTIFIED BY p1 USER SYSTEM IDENTIFIED BY p2
EXTENT MANAGEMENT LOCAL 
DEFAULT TEMPORARY TABLESPACE temp
UNDO TABLESPACE undotbs
DEFAULT TABLESPACE users
ENABLE PLUGGABLE DATABASE;

    Or initialization parameter: PDB_FILE_NAME_CONVERT =

    ‘/u02/app/oradata/CDB2′,’/u02/app/oradata/seed’

    Oracle Managed Files

    If you do not use explicit data file names, use Oracle Managed Files (OMF):

    • Set the DB_CREATE_FILE_DEST initialization parameter with the value of the destination

    directory of the data files of the SYSTEM, SYSAUX, UNDO, and USERS tablespaces specified

    in the statement. Oracle chooses default sizes and properties for all data files, control files, and

    redo log files.

    • The /u02/app/oradata directory must exist.

    PDB_FILE_NAME_CONVERT Instance Parameter

    If you do not use the SEED FILE_NAME_CONVERT clause, use an initialization parameter:

    • The PDB_FILE_NAME_CONVERT initialization parameter maps names of existing files (the

    root data files in your case) to new file names (the seed data files in this case).

    • In the example, both /u02/app/oradata/CDB2 and /u02/app/oradata/seed

    directories must exist.

  • Oracle Database 19c Multitenant Architecture

    🏛️ Multitenant Architecture in Oracle Database

    The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB).
    A CDB includes zero, one, or many customer-created pluggable databases (PDBs).

    A PDB is a portable collection of schemas, schema objects, and non schema objects that appears to an Oracle Net client as a non-CDB.
    All Oracle databases before Oracle Database 12c were non-CDBs.

    A container is a logical collection of data or metadata within the multitenant architecture.

    🧩 Containers in a CDB

    Every CDB consists of the following containers:

    1. CDB Root Container (CDB$ROOT)

    • The CDB root is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong.
    • It stores Oracle-supplied metadata and common users.
    • Example of metadata: Source code for Oracle-supplied PL/SQL packages.
    • A common user is a database user known in every container.
    • The root container is named CDB$ROOT.

    2. System Container

    • The system container includes the CDB root and all PDBs in the CDB.
    • It serves as the logical container for the entire CDB.

    3. Application Containers

    • A CDB can include zero or more application containers.
    • Each application container consists of:
      • Exactly one application root, and
      • The PDBs plugged into this root.
    • The system container includes the CDB root and all PDBs within the CDB, whereas the application container includes only the PDBs plugged into the application root.
    • An application root belongs exclusively to the CDB root and no other container.

    4. User-Created PDBs

    • A PDB contains the data and code required for a specific set of features or applications.
    • Example: A PDB can support a human resources or sales application.
    • No PDBs exist at the time of CDB creation — they are added based on business requirements.
    • A PDB belongs to zero or one application container.
      • If it belongs to one, it is called an application PDB.
      • Example: cust1_pdb and cust2_pdb might belong to the saas_sales_ac application container.
    • An application seed is an optional application PDB used as a template to create new application PDBs quickly.

    5. Seed PDB (PDB$SEED)

    • The seed PDB is a system-supplied template used by the CDB to create new PDBs.
    • The seed PDB is named PDB$SEED.
    • You cannot add or modify objects in PDB$SEED.

  • Protected: Creation of a Pluggable database (PDB)

    This content is password-protected. To view it, please enter the password below.

  • Protected: Creation a Container Database (CDB)

    This content is password-protected. To view it, please enter the password below.

  • Hello World!

    Welcome to my Oracle Database Administration learning space! 🚀
    This blog is dedicated to sharing practical knowledge, real-time DBA commands, and hands-on activities to help you master Oracle Database Administration. You’ll find easy-to-follow guides, troubleshooting tips, and configuration steps used in real production environments. Whether you’re preparing for certification or strengthening your DBA skills, this is your place for continuous learning and knowledge sharing.