Tag Archives: Enable or disable

Managing Temporary Undo in Oracle 12c

Managing Temporary Undo in Oracle 12.1

Temporary table used same process to used the default undo tablespace like a permanent table in Oracle Database and generate redo logs for operation which little bit effects on performance.

From 12c, You can also create a separate temporary undo for temporary tables in Oracle Database and not generate redo logs.

Benefits:
–Temporary undo reduce the undo data in permanent undo tablepsace which helps undo to maintain realistic data as undo retention parameter.
–It effects on performance because less redo is generated.

Note: You can enable temporary undo for a specific session or system level.

Enable and Disable the Temporary undo

--Enable temporary undo for a session
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

--Disable temporary undo for a session
ALTER SESSION SET TEMP_UNDO_ENABLED = FALSE;

--Enable temporary undo for the system
ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;

--Disable temporary undo for the system
ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;

Check the status of Undo Space Transaction

-- monitoring and tuning the undo space
select * from V$undostat;

-- monitoring and tuning the temporary undo space
select * from V$TEMPUNDOSTAT;

Advertisements

Database Smart Flash Cache in Oracle

Database Smart Flash Cache in Oracle

Database Smart Flash Cache is the new feature in Oracle 12c. It will help to increase the database buffer cache size using Solid State Device. It will greatly improve the performance of Oracle database by reducing disk I/O.

Note:
1. It only supported on Oracle Linux or Solaries Operating System.
2. Database smart flash cache is configured as 2 time to 10 times of Database buffer cache size or SGA size

Configure the Database Smart Flash Cache

-- Set the parameter in the SPFILE
Alter system set db_flash_Cache_File = /dev/sda scope=spfile;
Alter system set db_flash_cache_size = 32G scope=spfile;

-- Restart the database
shutdown immediate
startup

Configure to Setup multiple paths parameters

DB_FLASH_CACHE_FILE = /dev/sda, /dev/sdb, /dev/sdc
DB_FLASH_CACHE_SIZE = 32G, 32G, 64G

Flush the Database Smart flash cache

ALTER SYSTEM FLUSH FLASH_CACHE;

For disable it you can size it to zero
Note: it is not a dynamic parameter

Alter system set db_flash_Cache_File = /dev/sda scope=spfile;
Alter system set db_flash_cache_size = 0G scope=spfile;

Shutdown immediate
Startup

Check the Usage of Database Smart Flash Cache

Select * from V$FLASHFILESTAT

Managing Lost Write Protection with Shadow Tablespaces

Managing Lost Write Protection with Shadow Tablespaces

Lost Write of data block is occurred when an I/O subsystem acknowledges the completion of the block write even when the write did not occurred.An undetected lost write may result in corruption of data.It is used to prevent data corruption of Sensitive data. It is not important to use it to track all data.
Shadow lost write protection can protect against lost writes for tablespaces or data files.

Shadow lost write protection can minimize data loss and the time required to repair a database.You can enable it at tablespace or datafiles level.

Note: This feature present in Oracle 18c

For configured it, We need to follow the following steps:
Note: For enable Shadow Lost Writer Protection the compatibility level must be 18.0.0 or higher

1. Creating Shadow Tablespaces for Shadow Lost Write Protection

CREATE BIGFILE TABLESPACE Shadowtablespace DATAFILE 'C:\oracle\oradata\shadow_tablespace01.dbf' SIZE 10M LOST WRITE PROTECTION;

2. Enable the Shadow Lost Write Protection for a Database

-- Fon Non CDB or CDB Root
ALTER DATABASE ENABLE LOST WRITE PROTECTION;

-- FOR PDB
ALTER PLUGGABLE DATABASE ENABLE LOST WRITE PROTECTION;

3. Enable for Databata files or Tablespace as you needed
Note: Database Lost Write must be enabled before tablespace or Datafile

-- At tablespace level:
ALTER TABLESPACE users ENABLE LOST WRITE PROTECTION;

-- At CDB root or NON CDB level
ALTER DATABASE DATAFILE 'C:\oracle\oradata\users01.dbf' ENABLE LOST WRITE PROTECTION;

-- At Pluggable database
ALTER PLUGGABLE DATABASE DATAFILE 'C:\oracle\oradata\users01.dbf' ENABLE LOST WRITE PROTECTION;

4. Disabling Shadow Lost Write Protection
Note: When shadow lost write protection is disabled then tracking data is persevered in the tablespace,

--FOR CDB or NON CDB
ALTER DATABASE DISABLE LOST WRITE PROTECTION;

--FOR PDB
ALTER PLUGGABLE DATABASE DISABLE LOST WRITE PROTECTION;

5. Remove the Shadow lost write protection
Note: Remove shadow lost write protection for a data file or a tablespace, then its tracking data is deleted.

-- at tablespace level
ALTER TABLESPACE users REMOVE LOST WRITE PROTECTION;

--Data File Used by a Non-CDB
ALTER DATABASE DATAFILE 'C:\oracle\oradata\users01.dbf' SUSPEND LOST WRITE PROTECTION;

--FOR PDB datafiles
ALTER PLUGGABLE DATABASE DATAFILE 'C:\oracle\oradata\users01.dbf' SUSPEND LOST WRITE PROTECTION;

6. Drop tablespace of Shadow lost write protection

DROP TABLESPACE Shadowtablespace INCLUDING CONTENTS

Manage the SQL Profile in the Oracle Database

Manage the SQL Profile in the Oracle Database

SQL Profile is used by Optimizer for use more efficient execution plan for the SQL Query to make in accepted State. It is found in DBA_SQL_PROFILES view. It used to choose better plan by optimizer if the SQL profile plan is having low cost then optimizer use it.

Check the SQL Profile in the Database.

select name, type, status, sql_text from dba_sql_profiles;

Check the SQL Profile internal hint information

SELECT
a.name
,b.comp_data
FROM dba_sql_profiles a
,dbmshsxp_sql_profile_attr b
WHERE a.name = b.profile_name;

Create the SQL Profiles
Two ways:
1. Creating the SQL Profiles while running the manual SQL tuning task
For manually creating with SQL Tuning task follows the link:
SQL Tuning Advisory

2. Automatic SQL Tuning job runs on a daily basis (in Oracle Database 11g or higher).
Check SQL profiles that have automatically been created having the value “AUTO” in the TYPE column of the DBA_SQL_PROFILES views

--Check
select name, type, status, sql_text from dba_sql_profiles;

-- Enable
exec DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');

-- Disable
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'FALSE');

Disable the SQL Profiles

select name, status from dba_sql_profiles;

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_789tfag56hjli0004',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/

Drop the SQL Profile

exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_9824ryfg6f7d78653');

--Drop all profiles in a database, using PL/SQL block:
declare
cursor c1 is select name from dba_sql_profiles;
begin
for r1 in c1 loop
dbms_sqltune.drop_sql_profile(r1.name);
end loop;
end;
/