Check free space in Temp tablespace

 To check the free space in a temporary tablespace in Oracle, you can query the DBA_FREE_SPACE view or use the V$TEMP_FREE_SPACE dynamic view. These views provide information about the free space available in the temporary tablespace.

Here is a commonly used query to check the free space in a temporary tablespace:

Query 1: Using DBA_TEMP_FREE_SPACE

This view provides information about the free space in the temporary tablespaces.

SELECT
    tablespace_name,
    SUM(bytes)/1024/1024 AS free_space_MB
FROM
    dba_temp_free_space
GROUP BY
    tablespace_name;

• This query will return the available free space in each temporary tablespace in MB.
• The bytes are divided by 1024*1024 to convert from bytes to megabytes.

Query 2: Using V$TEMP_SPACE_HEADER

You can also use V$TEMP_SPACE_HEADER, which provides information about the temporary tablespace files and their usage.

SELECT
    tablespace_name,
    file_id,
    SUM(bytes)/1024/1024 AS free_space_MB
FROM
    v$temp_space_header
GROUP BY
    tablespace_name, file_id;

This query will give you a detailed breakdown of free space available in each temporary file associated with the temporary tablespaces.

Query 3: Checking Free Space Using DBA_DATA_FILES

This query checks the free space in the temporary tablespace by querying the DBA_TEMP_FILES and DBA_DATA_FILES views:

SELECT
    t.tablespace_name,
    f.file_name,
    f.bytes / 1024 / 1024 AS total_size_MB,
    (f.bytes - NVL(s.bytes, 0)) / 1024 / 1024 AS free_space_MB
FROM
    dba_temp_files f
LEFT JOIN
    (SELECT file_id, SUM(bytes) AS bytes
     FROM v$temp_space_header
     GROUP BY file_id) s
    ON f.file_id = s.file_id
WHERE
    t.tablespace_name = f.tablespace_name;

This query joins DBA_TEMP_FILES and V$TEMP_SPACE_HEADER to show the total space and the free space (in MB) for each file in the temporary tablespace.

These queries can help you track the available free space in your Oracle database's temporary tablespace(s). If you need more granular details (such as how much space is actually being used), you can expand the query to get more detailed statistics from dynamic views like V$TEMP_FILE or V$SORT_SEGMENT.

Cache Fusion in Oracle RAC

 

In Oracle Real Application Clusters (RAC), Cache Fusion refers to the technology that enables multiple instances (nodes) within the RAC cluster to efficiently share and synchronize data in the memory (specifically, the buffer cache) across instances. It allows instances in a clustered environment to communicate directly with each other to share data blocks, ensuring consistency and coordination in accessing and modifying shared data. This is essential for the high availability and performance of Oracle RAC systems.

How Cache Fusion Works

In a typical Oracle RAC setup, each instance maintains its own local buffer cache, which stores recently accessed data blocks. However, data blocks in the buffer cache of one instance might need to be accessed or modified by other instances. This could lead to data inconsistency if not managed properly.

Cache Fusion solves this problem by allowing instances in the RAC environment to communicate and share data blocks directly through the Global Cache Service (GCS), which manages the distribution of data across all instances.

When one instance modifies a data block, the change must be propagated to other instances that might have the same data block in their buffer cache. Oracle RAC uses Cache Fusion to allow these instances to send and receive blocks from each other's memory, rather than from disk. This significantly improves performance compared to traditional disk I/O operations.

Key Components of Cache Fusion

1. Global Cache Service (GCS):
   GCS is responsible for managing the access and consistency of shared data blocks between instances. It ensures that when one instance changes a block, other instances can either receive the latest version of the block or be notified to take appropriate action (e.g., invalidating or updating their copies).

2. Global Enqueue Service (GES):
   GES is responsible for managing locks and ensuring that data consistency is maintained when multiple instances try to access or modify the same data block. It coordinates the cache and ensures that operations such as reads, writes, and cache transfers are serialized properly.

3. Global Cache Interconnect (GCI):
   This is the physical communication mechanism that connects instances in a RAC cluster. Data blocks are transferred across this interconnect between instances. The Cache Fusion process relies on this interconnect for high-speed data sharing and consistency enforcement.

Cache Fusion Operation Flow

Here is a simplified overview of how Cache Fusion works in Oracle RAC:

1. Block Request:
   Suppose instance 1 has a data block in its buffer cache, and instance 2 requests that block. Instead of fetching the block from disk, instance 1 sends the block over the interconnect to instance 2.

2. Block Modification:
   If instance 1 modifies the data block, it must notify other instances that have a copy of the block. If instance 2 already has the block in its cache, it will receive the updated block or be invalidated and will need to refetch the updated data.

3. Consistency Maintenance:
   GCS ensures that any change to a block in one instance is visible to all other instances that have cached that block. This ensures data consistency and prevents issues like "dirty reads" across the cluster.

Types of Cache Fusion Communication

Cache Fusion uses a variety of communication methods to ensure data consistency across RAC nodes:

1. Read Request: When an instance requests a block, if the block is available in the buffer cache of another instance, it is transferred to the requesting instance through Cache Fusion. This is more efficient than reading from disk.

2. Write Request: If an instance modifies a block, the change must be propagated to all other instances that hold a copy of the same block, to maintain consistency across the cluster.

3. Invalidate: If an instance writes to a block, it must ensure that other instances that have cached the block are notified to invalidate their copies. This prevents other instances from using stale data.

4. Transfer: If an instance has a block that another instance needs, the block is transferred from the source instance’s buffer cache to the requesting instance's buffer cache.

Advantages of Cache Fusion

• Improved Performance:
  By sharing data blocks directly between instances in memory (via Cache Fusion), Oracle RAC reduces the need for disk I/O, which improves overall performance and responsiveness in multi-instance environments.

• High Availability:
  Cache Fusion ensures that each node can access the latest data without having to wait for disk I/O, which helps in maintaining high availability, especially in the event of instance failures or high-load scenarios.

• Reduced Contention:
  Cache Fusion helps reduce contention for disk-based resources by allowing data sharing between instances at the memory level. This helps to avoid bottlenecks that might occur when multiple instances need to read or write the same data blocks from disk.

Cache Fusion and Global Cache

In Oracle RAC, the Global Cache mechanism works hand in hand with Cache Fusion. The Global Cache Service (GCS) manages the access and consistency of data blocks across instances. Whenever an instance modifies a block in its buffer cache, the change must be reflected across all instances that hold a copy of the block, and Cache Fusion is the mechanism that facilitates this communication.

Challenges in Cache Fusion

1. Network Traffic:
   Cache Fusion relies heavily on the interconnect (the network connection between RAC nodes) for block transfers. High network latency or bandwidth constraints could negatively impact performance, especially in large-scale RAC systems.

2. Cache Coherency Overhead:
   Maintaining cache coherency in a large RAC environment requires significant communication between instances, which can lead to overhead, particularly in high-transaction environments.

3. Locking and Contention:
   In cases of high contention for the same data blocks (e.g., when multiple instances frequently modify the same block), the overhead of locking and cache synchronization could degrade performance.

Cache Fusion vs. Traditional Database Clusters

In traditional database clusters (pre-RAC), each instance had to access the shared data stored on disk. In contrast, Oracle RAC's Cache Fusion technology minimizes disk I/O by transferring data blocks across instances directly in memory, making it much faster and more efficient.

Cache Fusion in Oracle RAC is a key component of Oracle's ability to offer high performance, high availability, and scalability in a clustered database environment. By allowing instances to share and synchronize their buffer caches over the interconnect, Cache Fusion improves the efficiency of data access and modification across multiple instances in a RAC cluster. This technology enables Oracle RAC to provide a seamless, high-performance solution for mission-critical applications running in a multi-instance cluster.

Oracle Database Real Application clusters (RAC)- Background processes

 In Oracle Real Application Clusters (RAC), several background processes are responsible for managing cluster-related tasks, ensuring high availability, and coordinating the activities of multiple instances running in the cluster. These processes are similar to the background processes in a single-instance Oracle Database, but with additional processes to handle the complexities of a clustered environment.

Here’s a list of key background processes in Oracle RAC:

1. Global Cache Service (GCS) and Global Enqueue Service (GES) Processes

• LGWR (Log Writer): As in a single-instance Oracle Database, LGWR is responsible for writing redo log entries to disk. In RAC, LGWR writes to all instances, coordinating with the Global Cache Service (GCS) to manage redo for all instances.

• LMSn (Lock Manager Slave Processes): These processes coordinate the global cache and manage locking of data blocks across all nodes in the RAC environment. For each instance, there are one or more LMS processes. They help in ensuring that the data is consistent across the cluster.

• LMD (Lock Manager Daemon): The LMD process manages the global enqueues and global locks within Oracle RAC. It ensures that all nodes in the cluster can access and update shared data blocks in a consistent manner.

• LCKn (Lock Controller Processes): These processes manage the global cache and enforce locking rules at the block level. Each node can have one or more LCK processes.

2. Oracle Clusterware (Grid Infrastructure) Processes

These are part of the Oracle Clusterware software and are involved in managing the cluster environment:

• CSS (Cluster Synchronization Service): The CSS process ensures that the various nodes in the cluster can communicate with each other and synchronize the status of all nodes. It is responsible for cluster membership and node fencing.

• CRS (Cluster Resource Services): CRS is responsible for the management of cluster resources, such as Oracle Database instances, listeners, etc. It monitors and restarts failed resources to ensure high availability.

• EVN (Event Notification): The EVN process is used for inter-node communication in Oracle RAC. It handles events related to cluster management, especially in failover or failback scenarios.

• CTSS (Cluster Time Synchronization Service): This ensures that all nodes in the RAC environment are synchronized to the same time, which is crucial for maintaining consistency in the cluster.

3. Database Instance Processes

Oracle RAC instances will also run the following standard Oracle background processes:

• DBWn (Database Writer): As in single-instance Oracle Database, the DBWn process writes dirty buffers from the buffer cache to disk. In RAC, DBWn works on the instance's local buffer cache but coordinates with other DBWn processes in the cluster for efficient buffer management.

• PMON (Process Monitor): PMON is responsible for cleaning up resources (e.g., orphaned sessions, locks) and recovering resources for terminated sessions. In RAC, PMON ensures that failed or terminated instances are cleaned up from the cluster.

• SMON (System Monitor): SMON is responsible for performing recovery operations during instance startup (e.g., recovering from an instance crash), cleaning up temporary segments, and managing space in the database. SMON in RAC works in coordination with other instances to handle instance-level recovery.

• CKPT (Checkpoint): CKPT signals the DBWn process to perform a checkpoint, ensuring that all changes in the buffer cache are written to disk and that the database's checkpoint position is updated.

• RECO (Recoverer): The RECO process is responsible for performing automatic recovery for distributed transactions that have failed or been interrupted. In RAC, RECO works across instances and is crucial for maintaining data integrity in distributed environments.

• ARCH (Archiver): The ARCH process is responsible for archiving redo logs to the archive destination. In RAC, each instance has its own ARCH process, which may archive redo logs to the same or different destinations.

4. Other RAC-Specific Processes

• OPN (Oracle Parallel Server) Process: In older versions of Oracle RAC (pre-10g), OPN processes were used to manage the communication between multiple instances. However, these processes have been largely replaced by the GCS and GES mechanisms in modern versions.

• GSN (Global Services Network): This is a process used in some versions to coordinate messaging and shared services across RAC instances.

5. Additional Oracle RAC Background Processes

• RMS (RAC Managed Services): These processes are responsible for the managed services that allow nodes and resources in the cluster to failover and be restarted appropriately.

• FAN (Fast Application Notification): FAN processes are used for notifications when a failure occurs in the Oracle Cluster, such as node or instance failures, and notifying client applications about these failures in real-time.

• RAC Listener Process: In RAC environments, the Oracle listener (with TNSLSNR process) can be configured for multiple instances and dynamically distribute client connections across instances.

Summary of Common RAC Background Processes:

• Global Cache Services (GCS) & Global Enqueue Services (GES) Processes: LMSn, LMD, LCKn, etc.
• Clusterware Processes: CSS, CRS, EVN, CTSS
• Instance Processes: DBWn, PMON, SMON, CKPT, RECO, ARCH
• Additional Processes: FAN, RMS, RAC Listener

Each of these processes ensures that Oracle RAC remains a highly available, scalable, and fault-tolerant system by managing resources, communication, synchronization, and instance coordination within the cluster.

How to enable Flashback in Oracle database

 To enable Flashback in an Oracle Database, you need to perform several key steps. Flashback allows you to retrieve data that was modified or deleted and to perform time-based queries, which can be helpful for recovery, auditing, and historical analysis.

Here’s a step-by-step guide to enable Flashback in Oracle:

### 1. **Check Database Compatibility**

Flashback features require your database to be running in a compatible mode, typically Oracle 9i or later. Ensure your database is using **ARCHIVELOG** mode (Flashback won't work if the database is running in **NOARCHIVELOG** mode).

SELECT LOG_MODE FROM V$DATABASE;

If the database is not in ARCHIVELOG mode, you need to enable it.

### 2. **Enable ARCHIVELOG Mode (if not already enabled)**

If the database is not in ARCHIVELOG mode, follow these steps to enable it:

1. **Shutdown the database**:

   SHUTDOWN IMMEDIATE;

2. **Mount the database**:

   STARTUP MOUNT;

3. **Enable ARCHIVELOG mode**:

   ALTER DATABASE ARCHIVELOG;

4. **Open the database**:

   ALTER DATABASE OPEN;

5. **Check ARCHIVELOG mode**:

   SELECT LOG_MODE FROM V$DATABASE;

Now, the database should be in ARCHIVELOG mode.

### 3. **Enable Flashback Logging**

Flashback features require Flashback Logging to be enabled in the database. To enable Flashback, ensure that the database has sufficient undo tablespace and the **flashback retention target** is set appropriately.

#### a. **Enable Flashback Logging**

Flashback logging is enabled by default in recent Oracle versions, but you can manually ensure it's enabled by running:

ALTER SYSTEM SET UNDO_RETENTION = <time_in_seconds>;

ALTER SYSTEM SET FLASHBACK_RETENTION_TARGET = <time_in_minutes>;

For example, to set the Flashback retention to 24 hours:

ALTER SYSTEM SET FLASHBACK_RETENTION_TARGET = 1440;

#### b. **Check Flashback Status**

You can check whether Flashback is enabled using the following query:

SELECT FLASHBACK_ON FROM V$DATABASE;

If the result is `YES`, Flashback is enabled.

### 4. **Create a Flash Recovery Area (FRA)**

To enable Flashback, you typically need a Flash Recovery Area (FRA) where Oracle stores archived logs, backups, and Flashback logs. Set up the FRA by running the following command (adjust the path and size according to your requirements):

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/path/to/flash_recovery_area';

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=10G;

To check the FRA status:

SELECT * FROM V$RECOVERY_FILE_DEST;

### 5. **Enable Flashback on Tablespaces (Optional)**

Flashback operations may require certain tablespaces to be Flashback-enabled. You can enable Flashback on a tablespace using the following command:

If you want to disable Flashback on a tablespace, use:

ALTER TABLESPACE <tablespace_name> FLASHBACK OFF;

### 6. **Perform Flashback Operations**

Once Flashback is enabled, you can perform various Flashback operations, such as:

- **Flashback Query** to see data as of a specific time or SCN:

  SELECT * FROM my_table AS OF TIMESTAMP TO_TIMESTAMP('2024-11-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS');

- **Flashback Table** to restore a table to a previous state:

  FLASHBACK TABLE my_table TO TIMESTAMP TO_TIMESTAMP('2024-11-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS');

- **Flashback Database** to restore the entire database to a previous point in time (requires the database to be in ARCHIVELOG mode and Flashback logging enabled):

  FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2024-11-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS');

- Flashback operations require undo tablespace and the **Flashback logs** to be retained for the duration of your retention period.

- Ensure your FRA has sufficient space to store Flashback logs; otherwise, Oracle will automatically purge them as needed.

  

By following these steps, Flashback will be enabled, and you’ll be able to take advantage of time-based recovery and auditing features in Oracle.

Check the FLASHBACK Status Using V$DATABASE View

 

The V$DATABASE view contains information about the database, including whether Flashback is enabled.

SELECT flashback_on
FROM v$database;

• If Flashback is enabled, the result will show:

  
  YES
  

• If Flashback is not enabled, it will show:

  
  NO

How to Create a Tablespace in Oracle Database

 

Prerequisites

Before creating a tablespace, ensure you have the following:

• Oracle Database Installed: This guide assumes Oracle Database 11g or later.
• DBA (Database Administrator) Privileges: You need to have sufficient privileges to create a tablespace.
• Sufficient Disk Space: Ensure there is enough disk space for the new tablespace.

Step-by-Step Guide to Creating a Tablespace

1. Connect to Oracle Database

First, connect to your Oracle Database using SQL*Plus or another SQL client (like Oracle SQL Developer).

sqlplus sys as sysdba

Enter your password to log in.

2. Check Existing Tablespaces

Before creating a new tablespace, it's useful to check the existing ones. Run the following query to list the current tablespaces in your Oracle database.

SELECT tablespace_name FROM dba_tablespaces;

This will return a list of all the tablespaces in the database.

3. Create the Tablespace

To create a tablespace, use the CREATE TABLESPACE command. Below is the syntax to create a basic tablespace.

CREATE TABLESPACE <tablespace_name>
DATAFILE '<file_path>/<filename>.dbf'
SIZE <size> [K | M | G]
AUTOEXTEND ON NEXT <increment> [K | M | G]
MAXSIZE <max_size> [K | M | G];

Example:

Let's create a tablespace called USER_DATA with a 500MB data file and enable auto-extension to handle growth:

CREATE TABLESPACE USER_DATA
DATAFILE '/u01/app/oracle/oradata/mydb/user_data01.dbf'
SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

• DATAFILE: Specifies the path where the tablespace data file will be stored.
• SIZE: Initial size of the data file (in this case, 500MB).
• AUTOEXTEND: Allows the data file to automatically grow when more space is needed.
• MAXSIZE: The maximum size the data file can grow to. In this example, it is unlimited.

4. Verify the Tablespace Creation

Once the tablespace is created, you can verify its existence by querying the dba_tablespaces table.

SELECT tablespace_name FROM dba_tablespaces WHERE tablespace_name = 'USER_DATA';

If the tablespace is created successfully, it will appear in the results.

5. Assign a Default Storage Location

You may want to assign the newly created tablespace as the default tablespace for users. Use the following command:

ALTER USER <username> DEFAULT TABLESPACE USER_DATA;

This command sets USER_DATA as the default tablespace for the specified user.

6. Add Datafiles (Optional)

In some cases, you may want to add additional data files to the tablespace to increase storage capacity. To do this, you can use the ALTER TABLESPACE command:

ALTER TABLESPACE USER_DATA
ADD DATAFILE '/u01/app/oracle/oradata/mydb/user_data02.dbf'
SIZE 500M;

This adds a second datafile to the USER_DATA tablespace.

Managing Tablespaces in Oracle

1. Altering a Tablespace

If you need to resize or change the properties of an existing tablespace, you can use the ALTER TABLESPACE command.

ALTER TABLESPACE USER_DATA
RESIZE 1G;

This resizes the tablespace to 1GB.

2. Dropping a Tablespace

To drop a tablespace, use the DROP TABLESPACE command. Be cautious, as this operation will remove all data associated with the tablespace.

DROP TABLESPACE USER_DATA INCLUDING CONTENTS AND DATAFILES;

• INCLUDING CONTENTS: Removes all objects within the tablespace.
• AND DATAFILES: Deletes the data files associated with the tablespace.

3. Checking Tablespace Usage

To monitor the usage of a tablespace, you can run a query on the dba_data_files and v$tablespace views:

SELECT tablespace_name, file_name, bytes/1024/1024 "Size (MB)"
FROM dba_data_files;

This query will return the current size of each tablespace in MB.

Query to check High Watermark in Datafiles

 set verify off

column file_name format a50 word_wrapped

column smallest format 999,990 heading "Smallest|Size|Poss."

column currsize format 999,990 heading "Current|Size"

column savings  format 999,990 heading "Poss.|Savings"

break on report

compute sum of savings on report

column value new_val blksize

select value from v$parameter where name = 'db_block_size';

/

select file_name,

       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

       ceil( blocks*&&blksize/1024/1024) currsize,

       ceil( blocks*&&blksize/1024/1024) -

       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_data_files a,

     ( select file_id, max(block_id+blocks-1) hwm

         from dba_extents

        group by file_id ) b

where a.file_id = b.file_id(+) order by savings desc

/

Query to check blocking sessions in Oracle database

 Query to check blocking sessions in Oracle database from v$lock:

SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess,
  id1,
  id2,
  lmode,
  request,
  type
FROM v$lock
WHERE (id1, id2, type) IN
  (SELECT id1, id2, type FROM v$lock WHERE request > 0
  )
ORDER BY id1,
  request;

Script to check free space and used space in Oracle Database tablespaces

select a.tablespace_name,
       a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
 a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
       nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
       (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
       sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
       from dba_data_files
       group by tablespace_name ) a,
     ( select tablespace_name, sum(bytes) tot_used
       from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
--and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and   a.tablespace_name not like 'UNDO%'
order by 1
 --order by 5
/ 

RMAN-03009: failure of allocate command on ORA_AUX_SBT_TAPE_1 channel

This is an error during Database Clone using RMAN. I was trying to restore database using RMAN Backup 

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

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

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

RMAN-03002: failure of Duplicate Db command
RMAN-05501: aborting duplication of target database

RMAN-03009: failure of allocate command on ORA_AUX_SBT_TAPE_1 channel 
ORA-19554: error allocating device, device type: SBT_TAPE, device name:

ORA-27211: Failed to load Media Management Library

Additional information: 

Cause: Backup Configuration was modified.

Solution:
Use the below command to Resolve the error and continue with Database Restore

rman auxiliary / 
run
{
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate database to 'DEV' backup location '/Daily_Backup/Backup_16042018';
}
exit;
EOF

How to add space in ACFS filesystem?

The below post explains the step-by-step process to add storage to a mountpoint in ACFS filesystem

1.Before extending the mountpoint storage we need to add an LVM of required Storage Capacity on the server.

For checking the LUN's available on the server, click here

For now, assume the LUN added is /dev/sdf1

2. As root User, check the available disks by running the below command

$ /usr/sbin/oracleasm listdisks
DATA01
OCR_VOTE01
OCR_VOTE02
OCR_VOTE03

3. Create a new disk using the below command

/usr/sbin/oracleasm createdisk DATA02 /dev/sdf1 

Where DATA02 is the name of new disk added
/dev/sdf1 is the LUN added for storage extension.

4. Verify the disk is added by running the below command.

 $/etc/init.d/oracleasm  listdisks
DATA01
DATA02
OCR_VOTE01
OCR_VOTE02
OCR_VOTE03

5. Navigate to the other cluster nodes and run the scandisks command to reflect the newly added disk on all the nodes of cluster.

$/etc/init.d/oracleasm  scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]

 $/etc/init.d/oracleasm  listdisks
DATA01
DATA02
OCR_VOTE01
OCR_VOTE02
OCR_VOTE03

6. Now login to the database server as GRID user (Where ASM instance is running) to add space to ACFS.

Set the environment as required


export ORACLE_HOME=/u01/app/12.1.0/grid
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=+ASM1

$sqlplus / as sysasm

SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/DATA02';

Diskgroup altered.


7. Increase the space on filesystem

As root user,

$acfsutil size +1G /oradata
acfsutil size: new file system size: 591833071616 (564416MB)

How to Enable Database Vault in Oracle database Version 11gR1?

Steps to enable Database Vault in Oracle Database 11gR1:

1. In the command prompt, use DVCA utility to enable Oracle Database Vault.

e.g.

dvca -action enable 
  -oh /u01/app/oracle/product/11.1.0/db_1
  -service conn_alias 
  -instance orcl 
  -dbname orcl 
  -owner_account lbrown_dvowner 
  -logfile dvcalog.txt 

Enter SYS password: sys_password
Enter owner password: owner_password

Parameter Description:
======================

-service is the name of the database specifier. The specifier can be a connect descriptor or net service name. 

-instance is the name of the database instance.

-dbname is the database name.

-sys_passwd is the SYS password. If you use a cleartext password on the command line, you must include the nodecrypt option. If you omit the password, DVCA prompts you for it. Preferably, omit the password and then enter it interactively when prompted.

-owner_account is the Oracle Database Vault Owner account name.

-owner_passwd is the Oracle Database Vault Owner account password. If you use a cleartext password on the command line, you must include the nodecrypt option. If you omit the password, DVCA prompts you for it. Preferably, omit the password and then enter it interactively when prompted.

-logfile is an optional flag to specify a log file name and location. You can enter an absolute path, or enter a path that is relative to the location of the $ORACLE_HOME/bin directory.

-silent is the option to run in command line mode. This option is required if you are not running DVCA in an xterm window.

-nodecrypt is the option to read plaintext passwords.

-lockout is the flag to use to disable SYSDBA operating system authentication.

2. Stop the database, Database Control console process, and listener.

Enable the Oracle Database Vault option as follows:
 The make command enables both Oracle Database Vault (dv_on) and Oracle Label Security (lbac_on).
 You must enable Oracle Label Security before you can use Database Vault.

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_on lbac_on

cd $ORACLE_HOME/bin
relink all

3.Restart the database, Database Control console process, and listener.

4. Verify Oracle Database Vault and Oracle Label Security are enabled.

SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';

SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

How to create directories in Oracle Database?

Database Directories are required to read/write to a filesystem location from plsql code.

Below is the syntax to create database directory

SQL> create directory PLSQL_DIR AS '/oratmp/code';

Directory created.

Grant Read/write privileges to Required Schema (e.g . APPS)

SQL> GRANT READ,WRITE ON DIRECTORY PLSQL_DIR TO APPS;

Grant succeeded.

How to use coe_xfr_sql_profile.sql to Improve performance of a sql query?

Oracle SQL Performance tuning is one of the frequently reported issues in a Oracle Database administrator's daily job.
Very often we notice that sql queries or concurrent Programs which were running within few minutes earlier are now taking hours to be completed.
Underlying reason is due to the plan_hash_value used by the query has been changed.
coe_xfr_sql_profile.sql is the script developed by Oracle Support which helps us to identify the plan_hash_value which will resolve the performance issue.

Steps to use the script coe_xfr_sql_profile.sql

1. Download the script to a temporary directory on the database server.
Script is also available in Oracle Metalink.

2. Before Running the coe_xfr_sql_profile.sql, we need to know the sql_id of the sql query which is taking longer time to complete than usual.

To find the sql_id use below query

2.1 If you know the database SID of the long running query.
select inst_id,sid,serial#,status,last_Call_et,sql_id from gv$session where sid= '&sessionid';

2.2 If you know the query test, Use gv$sql and gv$sqlarea views to get the sql_id.

3. Login to sqlplus as sysdba and run coe_xfr_sql_profile.sql at the SQL prompt.

$ sqlplus “/as sysdba”
SQL> @coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)

Enter value for 1: 0a3f7vuks8d7y   (--this is the sql_id of long running sql)
PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
1484137450 15.6
3622468234 4560.76

[Output shows the list of available plan hash values, which can be forced on to the sql query. We need to select the plan_hash_value with low ETA to resolve the performance issue]

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 1484137450 


++Based on the inputs provided, it generates a sql script with naming convention coe_xfr_sql_profile_0a3f7vuks8d7y_1484137450.sql as output

coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql

4. Run the script as sysdba

SQL>coe_xfr_sql_profile_0a3f7vuks8d7y_1484137450.sql 

5. Verify that Performance issue is resolved by Re-running the sql.

How to Resolve ACFS-03008 : The volume expansion limit has been reached?

Issue: ACFS Filesystem resize operation fails with "ACFS-03008: The volume could not be resized.  The volume expansion limit has been reached."

$acfsutil size +1G /oradata
acfsutil size: ACFS-03008: The volume could not be resized.  The volume expansion limit has been reached.
acfsutil size: ACFS-03216: The ADVM compatibility attribute for the diskgroup was below the required version (11.2.0.4.0) for unlimited volume expansions.

Analysis:

Compatible attribute for advm and rdbms is below the required version.

 SQL>  select group_number, name, value from v$asm_attribute where name like 'compatible%' ;

GROUP_NUMBER
------------
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
           1
compatible.asm
12.1.0.0.0

           1
compatible.rdbms
11.2.0.0.0

           1
compatible.advm
11.2.0.0.0

           2
compatible.asm

12.1.0.0.0

           2
compatible.rdbms
10.1.0.0.0




Solution:

SQL> ALTER DISKGROUP DATA SET ATTRIBUTE 'compatible.advm'='11.2.0.4.0' ;

Diskgroup altered.

SQL>  ALTER DISKGROUP DATA SET ATTRIBUTE 'compatible.rdbms'='11.2.0.4.0' ;

Diskgroup altered.

Now verify the issue is resolved.
$ acfsutil size +10G /oradata
acfsutil size: new file system size: 603644231680 (575680MB)

ACFS-03171: Insufficient contiguous free ASM Diskgroup space

Issue:  

ACFS file resize operation operation fails with ACFS-03171: Insufficient contiguous free ASM Diskgroup space


[root@erptestdb01 ~]# cd /sbin
[root@erptestdb01 sbin]# acfsutil size +100M /oradata
acfsutil size: ACFS-03171: Insufficient contiguous free ASM Diskgroup space.  Check the ASM alert log.

Solution:

Increase size in smaller chunks until the required size is reached
[root@erptestdb01 bin]# acfsutil size +10M /oradata
acfsutil size: new file system size: 590625112064 (563264MB)

ORA-32771: Cannot Add File To Bigfile Tablespace

When trying to add a datafile to a tablespace , got the error – ORA-32771: cannot add file to bigfile tablespace.

SQL> ALTER TABLESPACE BIG_TBSP1 add datafile ‘+DATA/df01.dbf’ size 130G;
ALTER TABLESPACE BIG_TBSP1 add datafile ‘+DATA/df01.dbf’ size 130G;
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace

SOLUTION:

A bigfile tablespace can contain only one datafile and no other datafile can be added to that.

SQL> select BIGFILE from dba_tablespaces where tablespace_name=’BIG_TBSP1’;

BIGFILE
———————– —
YES

How to increase space in Bigfile Tablespace?


BIGFILE tablespace stores its data in a single datafile with a much larger capacity.

We can resize the size of the datafile in BIGFILE tablespace using ALTER DATABASE Command


ALTER DATABASE DATAFILE '/+DATA/df0101.dbf' RESIZE 180G;

Since BIGFILE Tablespace has only one datafile, there is no need to identify the datafile and increase its size.
We can use ALTER TABLESPACE command to resize at the tablespace level.

ALTER TABLESPACE BIG_TBSP1 RESIZE 180G;

What is the difference between Switchover and Failover in Oracle Dataguard?

A switchover means just switching roles between the primary database and standby db.
nswitchover, the primary database chnaged to a standby role, and the standby database changed to the primary role.
This is typically done for planned maintenance of the primary db server.

A failover is when the primary database fails and one of the standby databases is 
transitioned to take over the primary role. Failover is performed only in the event 
of a catastrophic failure of the primary database, and there is no possibility of 
recovering the primary database in a timely manner. Failover may or may not result 
in data loss depending on the protection mode in effect at the time of the failover.

How to mirror database Privileges of a Oracle database user to another database user?

Script for Mirroring Privileges of a user:

Scenario: A Database schema named "TEST2" is created and business wants to grant the privileges for the user same as already existing Database user "TEST1".

Run the below queries as SYS or any Privileged user to get the DDL commands in Oracle for TEST1 user.

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','TEST1') FROM DUAL;

 SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','TEST1') FROM DUAL;

 SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','TEST1') FROM DUAL;


 The output will be a sequence of grant commands related to privileges assigned to TEST1 user. Now replace the string TEST1 with new user name "TEST2" and run the commands.

 This will grant all privileges same as TEST1 user to TEST2 user in Oracle Database.

Change Oracle Database to ArchiveLog Mode

Steps to change the database to Archivelog Mode:

1. Login to the database server. Connect to sqlplus as sysdba

$sqlplus / as sysdba

2. Shutdown the database

SQL> shutdown immediate

3. Take a full database Backup (Cold Backup in this case)

4. Startup the database in mount stage

SQL> startup mount

5. Enable Archivelog mode

SQL> alter database archivelog;

6. Open the Database.

SQL> Alter database open;

7. Verify the changes.

SQL> archive log list;