Automatic Storage Management (ASM)

Contents:- Automatic Storage Management Theatrical Concepts

Oracle ASM Storage Components

ASM Disks:-                             

ASM Disk is a basic storage devices which can be added to oracle ASM Disk Groups.

ASM Disk Group:           

ASM Disk Group is a collection of one or more disks.ASM Disk Group using Striping Technique and also provides Data Redundancy using Mirroring Algorithm.

ASM Allocation Units     

Is the Smallest units in ASM Disk. ASM Disk is divided into Several ASM Allocation Units. ASM Allocation Unit size can be selected at the time of Creation ASM Disk Group. Allocation unit size cannot be change after creation of ASM Disk Group. Large Allocation units provides high performance for data warehouse application.

ASM Extents                            

ASM Extents is a collection of one or more allocation units. It is row storage.

ASM Files

In Oracle ASM all database files are store in ASM Disk Group. Those files are called as ASM Files.Oracle  ASM Supports Database Related Files i.e Control File, Data Files, Redo log files, Archive Files, Backup Files, Flashback Files, parameter file, configuration files, change tracking bitmap files.

Oracle ASM File is completely stored in one disk group.

ACFS 

ASM Cluster File System Can store non-database files.

ASM File Extent Map

ASM File Extent Map is a list of Extent Points pointed to already Extents. Each Extent point is describing details of Allocation unit number, When an Oracle Database send request to open ASM File at that time ASM Provides ASM File 

Extent Map to the database instance. Database Instance keeps this ASM File Extent Map in Memory and according to that Database Instance Perform I/O to ASM File and ASM Disks.

ASM Instance not mount or open an database.

Features

Oracle-ASM Variable size Extents:     (DG.Compatible.RDBMS>=11)

Variable size Extents Feature Handling Big File more efficiently.

Notes:- When New ASM File is Created Extents are allocated by using following Logic

First 20000 Extents are  of DG AU Size.

Next 20000 Extents are of 4 Times AU Size.

Above 40000 Extents are of 16 Times AU Size.

Oracle-RDBMS Connection

During Oracle Database Instance and ASM Instance Connections  any operations i.e create ASM file, open ASM file, Delete ASM file there is one COD (Continuing operation directory) is created by ASM Instance.

ASM Striping

Whenever database create any ASM File at that time ASM File is divided into Stripe Set. And then each ASM disk will store one Stripe from the stripe set and Striping continuous repeated until last stripe set. Oracle ASM Stripe provides load balancing across all disk groups. Striping also reduces I/O Latency.

Oracle ASM Failure Group

Oracle ASM Failure Group is a subset of Disks in a Disk Group. Failure Group are used to store mirror copies of data. Each Mirror Copy of data are store in a disk in a different Failure Group. Each disk in a Disk can belong to only one failure group. You can define a Failure group during ASM Disk group creation.

ASM Disk Group Rebalance Operation

RBAL Background process co-ordinates Rebalance Activity. ARBn processes performs actual rebalance operation.

During Rebalance operation ASM Creates COD Entry to track Disk Group Rebalance Operation.

You can set ASM_POWER_LIMIT Parameter to perform rebalance operation.

V$ASM_OPERATION view provides progress for rebalance operation.

Dynamic Performance Views ASM

V$ASM_DISK, V$asm_diskgroup, V$asm_alias

v$asm_client, V$asm_template, V$asm_operation, V$asm_file

USER-MANAGED Incomplete Recovery

Contents:- User managed Recovery

USER-Managed Time Based Recovery

Definitions:  Decide to perform user-managed incomplete recovery to restore the database back to its state as of half hour ago

Steps

Sql> shut immediate;

$> cp /backup/*.dbf  +data/clone/datafile/

Sql> startup mount

sql> recover database until time '2005-11-28:11:44:00';

sql> alter database open resetlogs;

now any data entered after recovery time need to be re-entered.

USER-Managed Cancel-based recovery

Here suppose

V$archive_log confirms the absence of archive log sequence. (arc_000_01.log)

Steps

Sql> shut immediate;

Sql> alter database mount;

Restore datafile from most recent backup

Sql> recover database until cancel;

(when recover database until cancel command is executed, it recovers the database until it cannot find a log file. When you prompted for the file name, enter cancel and the recovery stops at that point in time)

Sql> alter database open resetlogs;

Backup Monitoring, Index Monitoring

Contents:- Backup Monitoring, Index Monitoring

Backup Monitoring

$> rman target /

Rman>run

{

Allocate channel c1 type disk;

Set command id to ‘mitesh’;

Backup datafile 6;

Release channel c1;

}

Toad>

select sid,spid,client_info from v$process p, V$session s where p.addr=s.paddr and client_info like '%id=mitesh%';

Query the V$session_longops view to get the status of the copy..

select sid,serial#,context,sofar,totalwork,round(sofar/totalwork*100,2) "%complete" fromV$session_longops where opname like 'RMAN:%' and opname not like 'RMAN:aggregate%' and totalwork!=0;

Index Monitoring

There is one primary key index on id column of the item table.

SQL> alter index item_id_pk monitoring usage;

SQL> select * from item where id=101;

SQL> select * from V$object_usage;

Autonomous Transactions

Contents:- Autonomous Transactions

Autonomous Transactions

Autonomous transactions allows you to leave the context of the calling transactions, perform an independent transaction.

Example

CREATE TABLE at_test (

  id           NUMBER       NOT NULL,

  description  VARCHAR2(50) NOT NULL

);

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');

INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');

SELECT * FROM at_test;

        ID DESCRIPTION

---------- --------------------------------------------------

         1 Description for 1

         2 Description for 2

Now, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.

DECLARE

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  FOR i IN 3 .. 10 LOOP

    INSERT INTO at_test (id, description)

    VALUES (i, 'Description for ' || i);

  END LOOP;

  COMMIT;

END;

/

PL/SQL procedure successfully completed.

SELECT * FROM at_test;

        ID DESCRIPTION

---------- --------------------------------------------------

         1 Description for 1

         2 Description for 2

         3 Description for 3

         4 Description for 4

         5 Description for 5

         6 Description for 6

         7 Description for 7

         8 Description for 8

         9 Description for 9

        10 Description for 10

SQL> ROLLBACK;

SQL> SELECT * FROM at_test;

        ID DESCRIPTION

---------- --------------------------------------------------

         3 Description for 3

         4 Description for 4

         5 Description for 5

         6 Description for 6

         7 Description for 7

         8 Description for 8

         9 Description for 9

        10 Description for 10

So here inner commit statement of anonymous block does not commit earlier data. It performs an independent transactions.

Datafile Location Change

Contents:- Change name and location of datafile using backup

We can change name and location of data files at the time of restoring backup.

Rman> run

{

Set newname for datafile 6 to ‘+data/clone/datafile/’;

Restore datafile 6;

Switch datafile all;

Recover datafile 6;

}

RMAN Connection Debug

RMAN Connection Debug 

We can debug rman connection by using debug option

i.e

$>rman target / catalog rman_user/rman_user@cdb debug trace.log

Database Rename

Contents:- Rename a database name

Steps

Current db name=clondb

Rename name=clone

$> . oraenv

Clonedb

Sql>shut immediate

$>nid target=clonedb dbname=clone

Sql> y

Change parameter file

$> vi /u01/app/oracle/admin/clonedb/pfile/initclonedb.ora

:1,$ s#clonedb#clone#g

Create Directory Structure

$>mkdir –p /u01/app/oracle/admin/clone/adump

$>mkdir –p /u01/app/oracle/admin/clone/dbdump

$>mkdir –p /u01/app/oracle/admin/clone/pfile

$> . oraenv

+ASM

$>asmcmd

Asmcmd> cd data

Asmcmd> mkdir clone

Asmcmd> cd ..

Asmcmd> cd flash

Asmcmd>mkdir clone

Add entry of new sid CLONE into /etc/oratab file.

Create spfile from newly created pfile.

$> . oraenv

Clone

$>sqlplus sys as sysdba

Sql> create spfile=’+data/clone/spfileclone.ora’ from pfile=’/u01/app/oracle/admin/clone/pfile/initclone.ora’;

Create pfile into dbs folder to call spfile.

$> vi $ORACLE_HOME/dbs/initclone.ora

Spfile=’+data/clone/spfileclone.ora’;

:wq

Set environment to newly database name

$> . oraenv

Clone

Login into database and start the database.

$>sqlplus sys as sysdba

Sql>startup mount;

Sql>alter database open resetlogs;

Sql>select name,dbid from v$database;

ORA-00845: MEMORY_TARGET not supported on this system

http://oracle4ryou.blogspot.com/2013/05/ora-00845-memorytarget-not-supported-on.html

Data File Various Scenarious

Contents:- Data File Recovery, Non-Critical Datafile Recovery, Critical Datafile Recovery, Rename Datafile, Datafile Recovery without Backup, Datafile and Controlfile Recovery Without backup

Notes:- Critical Datafiles Loss requires the Database to be restored from the Mount Stage. While Non-Critical Data Files that May be restored with the Database Open.

Recover Critical Data File (i.e System ,undo, Sysaux) if the System is in Archivelog Mode

Steps

Sql> shutdown abort;

Sql> startup mount;

SQL> Restore tablespace System;

SQL> Recover Tablespace System;

SQL> Alter Database Open;

Recover Image Copies by using Incremental Backup

Image Copies are updated with all changes up to the incremental backup SCN.

Incremental Backup reduces the time required for recovery.

In this Recovery Method, We use RMAN to recover a copy of data file, means we recovery the image copies to the specified point in time by applying incremental backups to image copy. The image copy is updated with all changes up through the SCN at which the incremental backup was taken.

Benefits of Applying Incremental Backup to Image Copies

You Reduce the time required for media recovery (using archive logs) because you need to apply archive logs only since the last incremental backup.

Rename a Data file.

To Move or Rename a Datafile to New Location

Steps

SQL> Shutdown immediate

Physically Change Datafile location or rename it.

SQL> startup mount;

SQL> alter database rename file ‘file_name’;

Recover a Lost Datafile with no Backup with RMAN with Catalog.

Steps

 è Take Fresh Backup.

 è SQL> alter tablespace debt add datafile ‘+data(datafile)’ size 10m;

 è SQL> shutdown immediate

 è Physically delete new data file

 è SQL> Startup;       [ After mount stage getting error]

 è SQL> select * from V$recover_file;

 è In RMAN Connect target with Catalog Database.

 è Rman>sql ‘shutdown immediate’;

 è Rman>sql ‘startup mount’;

 è Rman> restore datafile <datafile_numbeR>;

 è Rman>recover datafile <datafile_numbeR>;

 è Rman>sql ‘alter database open’;

Or

Recovery of data file whose backup is not available.

i.e

sql>create tablespace stc add datafile ‘+data(datafile)’ size 10m;

sql> create table demo(id number) tablespace stc;

insert some records into table demo

disaster : deleted stc datafile.

Sql> startup mount

Sql>alter database datafile <datafile_number> offline;

Sql> alter database open;

Sql> select file#,ts#,status,name from V$datafile;

Sql> shut immediate;

Sql> alter database create datafile ‘<delete datafile with path>’;

Sql> alter database datafile <datafile_numbeR> online;

Sql> select checkpoint_change#,file# from V$datafile_header;

here scn(checkpoint number of datafile 6 is not the same as other because it's not recovered yet).

Rman> run

{

Allocate channel c1 device type disk;

Recover tablespace <tablespace_name>;

}

Recover a Lost Datafile and all control files  with no Backup with RMAN with Catalog.

Steps

 è Take Fresh Backup.

 è Sql>alter tablespace debt add datafile ‘+data(datafile)’ size 10m;

 è Sql>shutdown immediate;

 è Physically delete newly created datafile and all controlfiles.

 è Sql> startup         [ after nomount stage error comes ]

 è In RMAN Connect to target database with catalog.

 è RMAN> sql ‘shutdown immdiate’;

 è Rman> sql ‘startup nomount’;

 è Rman> restore controlfile from autobackup;

 è Rman> SQL ‘Alter database mount’;

 è Rman> restore database;

 è Rman> recover database;

 è Rman> SQL ‘alter database open resetlogs’;

Database Recovery

Recovering after Loss of Whole Database

Scenarios can be Create i.e

Steps

 è Take Fresh backup.

 è Dbca to delete database.

 è Create directory structure

 è Create Instance (service of database)

Cmd> oradim –new –sid orcl

Cmd> set ORACLE_SID=orcl

Cmd> rman target /

Rman>startup nomount force;

Rman> restore spfile from autobackup;

Rman> sql ‘shutdown immdiate’;

Cmd> sqlplus sys as sysdba

Sql> create pfile=’$ORACLE_HOME/db1/dbs/initorcl.ora’ from spfile;

Sql> startup nomount;

Rman> restore controlfile from autobackup;

Rman> sql ‘alter database mount’;

Rman> restore database;

Rman> recover database;

(if any error)

Rman>run

{

Allocate channel c1 type disk;

Set until sequence <current_seq_no> thread 1;

Recover database;

Alter database open resetlogs;

}

Temporary Tablespace Management

Concepts:- Temporary File Concepts and Scenarios.

Temporary File Recovery

Automatic Temporary File Recovery

If any temporary file is lost then it can be automatically recovered at time of database startup.

Manually Recover Temp File                                     

We can manually recovery the temporary file.

SQL> alter tablespace temp add tempfile ‘+data(tempfile)’ size 100M;

SQL> Alter tablespace temp drop tempfile ‘+data/orcl/tempfile/temporary.12364.266.tmp’;