Tag: automatic

Enable Automatic Memory Management (AMM)

Posted on by Osama Mustafa in Database
SQL> show parameter target

NAME                                               TYPE        VALUE
———————————— ———–       ——-
archive_lag_target                                     integer     0
db_flashback_retention_target                  integer     1440
fast_start_io_target                                   integer     0
fast_start_mttr_target                                integer     0
memory_max_target                                 big integer 26048M
memory_target                                         big integer 0
parallel_servers_target                              integer     2048
pga_aggregate_target                               big integer 6502M
sga_target                                                big integer 19520M

SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET = 16384M scope=spfile ;

SQL > SHUTDOWN IMMEDIATE;
SQL > STARTUP ;
SQL> ALTER SYSTEM SET SGA_TARGET = 0 scope=spfile ;
System altered.
SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 scope=spfile ;
System altered.
SQL> ALTER SYSTEM SET MEMORY_TARGET=12288M scope=spfile ;
System altered.
Thank you 
Osama Mustafa

Automatic Storage Management/ASM Part 2

Posted on by Osama Mustafa in Database

Templates:

Templates are named groups of attributes that can be applied to the files within a disk group

Examples :

 

-- Create a new template.
ALTER DISKGROUP disk_group_1 ADD TEMPLATE my_template ATTRIBUTES (MIRROR FINE);

-- Modify template.
ALTER DISKGROUP disk_group_1 ALTER TEMPLATE my_template ATTRIBUTES (COARSE);

-- Drop template.
ALTER DISKGROUP disk_group_1 DROP TEMPLATE my_template;

Available attributes include:

  • UNPROTECTED – No mirroring or striping regardless of the redundancy setting.
  • MIRROR – Two-way mirroring for normal redundancy and three-way mirroring for high redundancy. This attribute cannot be set for external redundancy.
  • COARSE – Specifies lower granuality for striping. This attribute cannot be set for external redundancy.
  • FINE – Specifies higher granularity for striping. This attribute cannot be set for external redundancy.

 

 

Directories

A directory heirarchy can be defined using the ALTER DISKGROUP statement to support ASM file aliasing

Examples :

-- Create a directory.
ALTER DISKGROUP disk_group_1 ADD DIRECTORY '+disk_group_1/my_dir';

-- Rename a directory.
ALTER DISKGROUP disk_group_1 RENAME DIRECTORY '+disk_group_1/my_dir' TO '+disk_group_1/my_dir_2';

-- Delete a directory and all its contents.
ALTER DISKGROUP disk_group_1 DROP DIRECTORY '+disk_group_1/my_dir_2' FORCE;

 

Aliases

Aliases allow you to reference ASM files using user-friendly names

-- Create an alias using the fully qualified filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
  FOR '+disk_group_1/mydb/datafile/my_ts.342.3';

-- Create an alias using the numeric form filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
  FOR '+disk_group_1.342.3';

-- Rename an alias.
ALTER DISKGROUP disk_group_1 RENAME ALIAS '+disk_group_1/my_dir/my_file.dbf'
  TO '+disk_group_1/my_dir/my_file2.dbf';

-- Delete an alias.
ALTER DISKGROUP disk_group_1 DELETE ALIAS

Files

Files are not deleted automatically if they are created using aliases, as they are not Oracle Managed Files (OMF), or if a recovery is done to a point-in-time before the file was created. For these circumstances it is necessary to manually delete the files, as shown below.

 

-- Drop file using an alias.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/my_dir/my_file.dbf';

-- Drop file using a numeric form filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1.342.3';

-- Drop file using a fully qualified filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/mydb/datafile/my_ts.342.3';

ASM Views:

Migrating to ASM Using RMAN:

  • Disable change tracking (only available in Enterprise Edition) if it is currently being used.
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
  • Shutdown the database.
    SQL> SHUTDOWN IMMEDIATE 
    • Modify the parameter file of the target database as follows:
      Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
      Remove the CONTROL_FILES parameter from the spfile so the control files will be moved to the DB_CREATE_* destination and the spfile gets updated automatically. If you are using a pfile the CONTROL_FILES parameter must be set to the appropriate ASM files or aliases.
  • Start the database in nomount mode.

                            RMAN> STARTUP NOMOUNT

 

  • Restore the controlfile into the new location from the old location.

 RMAN> RESTORE CONTROLFILE FROM ‘old_control_file_name’;

  • Mount the database.
  • Copy the database into the ASM disk group.
    RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';

  • Switch all datafile to the new ASM location.

    RMAN> SWITCH DATABASE TO COPY;
  • Open the database.

    RMAN> ALTER DATABASE OPEN;
  • Create new redo logs in ASM and delete the old ones.
  • Enable change tracking if it was being used.

    SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING; 

Automatic Storage Management (ASM) / Part 1

Posted on by Osama Mustafa in Database
using SQL*Plus connect to the idle instance.

export ORACLE_SID=+ASM
sqlplus / as sysdba

Startup and Shutdown of ASM Instances

 

ASM instance are started and stopped in a similar way to normal database instances. The options for the STARTUP command are:

  • FORCE – Performs a SHUTDOWN ABORT before restarting the ASM instance.
  • MOUNT – Starts the ASM instance and mounts the disk groups specified by the ASM_DISKGROUPS parameter.
  • NOMOUNT – Starts the ASM instance without mounting any disk groups.
  • OPEN – This is not a valid option for an ASM instance.

The options for the SHUTDOWN command are:

  • NORMAL – The ASM instance waits for all connected ASM instances and SQL sessions to exit then shuts down.
  • IMMEDIATE – The ASM instance waits for any SQL transactions to complete then shuts down. It doesn’t wait for sessions to exit.
  • TRANSACTIONAL – Same as IMMEDIATE.
  • ABORT – The ASM instance shuts down instantly.

 

ASM Disk Groups

  level of redundancy:

 

  • NORMAL REDUNDANCY – Two-way mirroring, requiring two failure groups.
  • HIGH REDUNDANCY – Three-way mirroring, requiring three failure groups.
  • EXTERNAL REDUNDANCY – No mirroring for disks that are already protected using hardware mirroring or RAID. If you have hardware RAID it should be used in preference to ASM redundancy, so this will be the standard option for most installations.

Create Example : 

CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY
  FAILGROUP failure_group_1 DISK
    '/devices/diska1' NAME diska1,
    '/devices/diska2' NAME diska2
  FAILGROUP failure_group_2 DISK
    '/devices/diskb1' NAME diskb1,
    '/devices/diskb2' NAME diskb2;

DROP Example : 

DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;

Using Alter to Add Disk : 

ALTER DISKGROUP disk_group_1 ADD DISK
  '/devices/disk*3',
  '/devices/disk*4';

Using Alter to Drop Disk :

ALTER DISKGROUP disk_group_1 DROP DISK diska2;

Using Alter to Re-size Disk : 

ALTER DISKGROUP disk_group_1
  RESIZE DISK diska1 SIZE 100G;

 Using Alter to Re-size all disk in Failure Group:

ALTER DISKGROUP disk_group_1
  RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 100G;

 Using Alter to Resize all Disk In DiskGroup :

ALTER DISKGROUP disk_group_1
  RESIZE ALL SIZE 100G;

Using Alter To dismiss pending disk to be Dropped :

ALTER DISKGROUP disk_group_1 UNDROP DISKS;

 Some Useful Command to Mount ASM disk group Manually : 

ALTER DISKGROUP ALL DISMOUNT;
ALTER DISKGROUP ALL MOUNT;
ALTER DISKGROUP disk_group_1 DISMOUNT;
ALTER DISKGROUP disk_group_1 MOUNT;

 Thank You 

Osama mustafa