How to Check Oracle Database Table and Index Fragmentation

The following posts demonstrated how to check fragmentations in PostgreSQL:
How to Identify Fragmentation in PostgreSQL RDS
Code to Report Table Index Schema or Database Fragmentation in PostgreSQL

This post will show how to check table and index fragmentations in Oracle database.

Subscribe to get access

Read more of this content when you subscribe today.

ExaCC : How to Understand OCPU and How Many Databases can be Created ?

A client created an VM Cluster with four RAC nodes and eight ( 8 ) OCPU assigned in one of VM Clusters  Compartment.

So questions are how many CPUs each RAC node has ? and How many databases can be created in this RAC environment based on current available OCPUs?

Subscribe to get access

Read more of this content when you subscribe today.

ORA-65054: Cannot open a pluggable database in the desired mode

In RAC environment, we cannot open PDB with the following messages:

SQL> alter pluggable database open instances=all;

*
ERROR at line 1:
ORA-65054: Cannot open a pluggable database in the desired mode.

Check CDB still in Mount status:

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show con_name

CON_NAME
——————————
CDB$ROOT

QL> select name,open_mode from v$database;

NAME    OPEN_MODE
------  ---------
TESTDB  MOUNTED


SQL> alter database open ;

*
ERROR at line 1:
ORA-65054: Cannot open a pluggable database in the desired mode.

CAUSE and SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

ExaCC: How to Register a Database in OCI

Registering a Replacement Database

  • Create a database by using Oracle Database ExaCC console with the same database name, software version and character set.
  • Shutdown the newly created database.
  • Remove the database files and replace them with the replacement database. The replacement database files must be moved into the same location as the database you are replaced. You can manually copy the replacement database files into place from a cold backup, or you can restore from a backup using Oracle Recovery Manager (RMAN).
  • Start the replacement database.
  • Perform the registration prerequisite check and resolve any issues from the following command:

    # dbaascli registerdb prereqs –dbname dbname [ –db_unique_name dbuniquename ]

    The following are the common issues from the above prerequisite check:
    1) Required database attributes such as the database name, software version, or character set do not match the original database.
    2) The database is not in READ WRITE mode.
    3) Flashback, archive log mode and block change tracking are not enabled.
    4) Transparent Database Encryption (TDE) is not configured, or is not properly configured. For example, the TDE keystore is not the expected location, or the TDE keystore type is not AUTOLOGIN.
  • Register the database by running the following command as the root user on one of the database servers:
# dbaascli registerdb begin --dbname dbname [ --db_unique_name dbuniquename ]

Register a Manually Created or Migrated Database

  1. Manually create or migrate the database. Ensure that the database meets the following requirements:
    • The /etc/oratab file contains an entry for the database.
    • Oracle Transparent Database Encryption (TDE) is configured on the database.
    • The database is in READ WRITE mode.
    • Archive log mode and block change tracking are enabled.
  2. Generate a cloud registration file (creg.ini) by running the following command as the root user on one of the database servers:
# dbaascli registerdb registry --action initialize --db dbname [ --udb dbuniquename ]

3. Perform the registration prerequisite check and resolve any issues.

# dbaascli registerdb prereqs --dbname dbname [ --db_unique_name dbuniquename ]

4. Register the database by running the following command as the root user on one of the database servers:

# dbaascli registerdb begin --dbname dbname [ --db_unique_name dbuniquename 

ExaCC : Can We Change Oracle and Grid User Password?

In ExaCC environment, you should not change any default setting for default users.

So changing password of grid infrastructure user ( gird ) and Oracle RAC database user ( oracle) is not allowed in ExaCC environment.