ORACLE PERFORMANCE TUNNING

Important Queries for Performance Tunning:

select
    t.owner,t.table_name,t.num_rows,t.avg_row_len,
        t.blocks as blocks_belwo_hwm,t.empty_blocks,
    s.blocks as segment_blocks,
    s.bytes/1048576 as size_in_mb,
    to_char(t.last_analyzed,'yyyy-mm-dd hh24:mi') as last_analyzed
from all_tables t
inner join dba_segments s
 on t.owner=s.owner and table_name=s.segment_name
where T.owner='CT'
ORDER BY size_in_mb desc;

DG BROKER FOR ORACLE 11G

OBE FOR DG BROKER
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/ha/dataguard/dg_broker/dg_broker.htm

Oracle 11g Data Guard Role Transition (Switchover)

ORACLE 11G DATAGUARD MANUAL ROLE TRANSITION

After Successful implementation of Oracle 11g Data Guard, next step is to check role transition.

Steps on Primary Database.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

Now perform Shut down to change role of primary database into standby database.

SQL> shutdown immediate;

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1241513984 bytes

Fixed Size                  1273420 bytes

Variable Size             318767540 bytes

Database Buffers          905969664 bytes

Redo Buffers               15503360 bytes

Database mounted.

SQL> alter database open read only;

SQL> recover managed standby database disconnect using current logfile;

Steps on Standby Database.

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

Adding/Dropping a disk in an ASM Disk Group

Adding/Dropping a disk in an ASM Disk Group

One of the big selling points of ASM is the ability to reconfigure the storage online.

This is basically achievable because ASM distributes data across all disks in a disk group evenly, and assuming you have enough space, you can happily drop disks in a disk group and ASM will seamlessly migrate the data to the existing disks in the disk group.

Check Existing Space/Disks.

SQL> select sum(total_mb)/1024,sum(free_mb)/1024

  2  from V$ASM_DISK;

SUM(TOTAL_MB)/1024 SUM(FREE_MB)/1024

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

59.042968755        37.560546875

Now Add New Two Disks to existing Disk Group. I have added two Disks of 5GB size.

SQL>  alter diskgroup  data add disk

  2  '/dev/oracleasm/disks/DISK5',

  3   '/dev/oracleasm/disks/DISK6';

Now again Check Space/Disks

SQL> select sum(total_mb)/1024,sum(free_mb)/1024

  2  from V$ASM_DISK;

SUM(TOTAL_MB)/1024 SUM(FREE_MB)/1024

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

          69.03125        47.5429688

So, we see here that DATA_0005 and DATA_0006 are two disks (luns) in disk group 1.I have enough space so safely i can delete newly added disks.

alter diskgroup data drop disk DATA_0005; 

Diskgroup altered.

alter diskgroup data drop disk DATA_0006; 
Diskgroup altered.

This alter diskgroup command essentially shuffles extents from the disk you are removing and distributes them to the remaining disks in your disk group. While the operation is continuing you can check V$ASM_OPERATION for the progress you are making:

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE  EST_MINUTES

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

    4 REBAL RUN     1  1   100   42234       1007     41

Most of the columns here are self explanatory, however the SOFAR column tells you the number of Allocation Units (au) that have been moved, the EST_WORK and EST_RATE are also in au and au/minute.

Once the rebalance has moved all the Allocation Units the disk is removed from the disk group:

SQL>  select group_number, name, TOTAL_MB, FREE_MB

from V$asm_disk_stat;

GROUP_NUMBER NAME                             TOTAL_MB    FREE_MB

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

           1 DATA_0003                           15115      10498

           1 DATA_0002                           15115      10468

           1 DATA_0001                           15115      10467

           1 DATA_0000                           15115      10468

           1 DATA_0005                            5114       3392

           1 DATA_0004                            5114       3391

Dropping a disk in a disk group seemed to work as advertised, the real benefit of course, is instead of it being just a disk you were dropping but that it was a lun representing a whole storage array, then this has real potential for allowing you to upgrade storage or even migrate to a different storage platform entirely.

FIX- ORA-00119: invalid specification for system parameter LOCAL_LISTENER ORA-00132: syntax error or unresolved network name

Establish Connection with sqlplus and then create pfile from spfile.

[oracle@primary ~]$ sqlplus /  as sysdba

SQL> create spfile from pfile='/home/oracle/pfile.ora';

Edit pfile.

[oracle@primary ~]$ vim /home/oracle/pfile.ora

Delete LISTENER entry from pfile.
*.local_listener

and re-create spfile from pfile and start database.

 

Oracle Active Data Guard using RMAN

OBE:http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/ha/dataguard/physstby/physstdby.htm

 
PRIMARY SITE: (Information)

Machine IP: 192.168.56.101

Machine Name:linux1

Database name (db_name):test

Database Unique Name (db_unique_name):test_primary

TNS Service Name: standby (Through this service, the primary machine will be connected to STANDBY machine)

STANDBY SITE: (Information)

Machine IP: 192.168.56.102

Machine Name: linux2

Database name (db_name):test

Database Unique Name (db_unique_name): test_standby

TNS Service Name: primary (Through this service, the standby machine will be connected to PRIMARY machine)

CONFIGURATION ON PRIMARY SITE: -

Step1:

Create pfile from spfile on the primary database:

SQL>create pfile= ‘/backup/mypfile.ora’ from spfile;

Step2:

Add following settings in the pfile (/backup/mypfile.ora) on the PRIMARY Machine.

DB_UNIQUE_NAME='test_primary'

FAL_Client='primary'

FAL_Server='standby'

LOG_ARCHIVE_CONFIG=

 'DG_CONFIG= (test_primary, test_standby)'

standby_file_management=auto

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_DEST_1=

 'LOCATION=USE_DB_RECOVERY_FILE_DEST

  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

  DB_UNIQUE_NAME=test_primary'

LOG_ARCHIVE_DEST_2=

 'SERVICE=standby LGWR ASYNC

  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

  DB_UNIQUE_NAME=test_standby'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

Service_names='test_primary'

Step3:

Create password file using ‘Shell prompt’.

[oracle@linux1~]$ orapwd file=’/u01/app/oracle/product/10.2.0/db_1/dbs/orapwtest’ password=oracle entries=5 force=y ignorecase=y

Step4:

SQL>startup mount;

Step5:

Take the Primary database to Archive Log Mode.

SQL>alter database archiveLog;

Enable Force Logging.

SQL>alter database force logging;

Now shutdown the database:

SQL>shutdown immediate;
Now startup the database:
SQL>startup;

Step6:

Now add standby Redo Log group to the PRIMARY site.

SQL>Alter database add standby logfile (‘/u01/app/oracle/oradata/test/standbyredo.log’) size 100m;

Step7:

Startup the database from pfile.
SQL>startup pfile='/backup/mypfile.ora';
Create spfile from pfile to startup the database with spfile.

It is necessary for primary database to use spfile in a dataguard environment.

SQL>Create spfile from pfile=‘/backup/mypfile.ora’;

Now bounce the DB to take affect of new parameter from pfile.

Step8:

Restart the database

SQL> shut immediate;

SQL> startup;

Step9:

Configure tnsnames.ora on both servers to hold entries for both databases.

Now on PRIMARY site create following services in TNSnames.ora file. This file will be use on standby site also.

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRIMARY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))

    )

    (CONNECT_DATA =

(SERVER = DEDICATED)

      (SID = orcl)

    )

  )

STANDBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))

    )

    (CONNECT_DATA =

(SERVER = DEDICATED)

      (SID = orcl)

    )

  )

Note: This tnsnames.ora will be used on both side of dataguard env.

Step10:

Listener.ora for Primary Site.

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))

  )

ADR_BASE_LISTENER = /u01/app/oracle

Step11:

Listener.ora for Standby Site.

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))

  )

ADR_BASE_LISTENER = /u01/app/oracle

Step12:

Also configure net service name for primary database on primary site and standby service name on standby site.

[oracle@linux1~]$ netmgr

[oracle@linux1~]$ Lsnrctl status

[oracle@linux1~]$ Lsnrctl stop

[oracle@linux1~]$ Lsnrctl start

CONFIGURATION ON STANDBY SITE:-

Step1:

Create following directories on target standby

mkdir -p /u01/app/oracle/flash_recovery_area/orcl/archivelog
mkdir -p /u01/app/oracle/flash_recovery_area/orcl/onlinelog
mkdir -p /u01/app/oracle/flash_recovery_area/orcl/flashback
mkdir -p /u01/app/oracle/admin/orcl/dpdump
mkdir -p /u01/app/oracle/admin/orcl/adump

mkdir -p /u01/app/oracle/admin/orcl/scripts

mkdir -p /u01/app/oracle/admin/orcl/pfile

mkdir -p /u01/app/oracle/cfgtoollogs/orclca/orcl

mkdir -p /u01/app/oracle/cfgtoollogs/emca

mkdir -p /u01/app/oracle/cfgtoollogs/netca

mkdir -p /u01/app/oracle/oradata/orcl     

mkdir -p /u01/app/oracle/admin/orcl/adump

Step2:

Create pfile.

[oracle@linux2~]$ vi /backup/pfile.ora

Make following entry in pfile

Db_name=orcl

Step3:

Start database using above created pfile

SQL>startup nomount pfile=’/backup/pfile.ora’

Step4:

Create password file using ‘Shell prompt’.

[oracle@linux1~]$ orapwd file=’/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl’ password=oracle entries=5 force=y ignorecase=y

Step5:

Use this command to connect to rman

rman target=sys/oracle@primary auxiliary=sys/oracle@standby

Step6:

Run following script in rman prompt to create standby database using rman

run{

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate channel prmy5 type disk;

allocate auxiliary channel stby1 type disk; 

duplicate target database for standby from active database 

DORECOVER

spfile 

parameter_value_convert 'orcl','orcl'

set db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'

set

log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'

set 'db_unique_name'='standby'

set

control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/orac

e/flash_recovery_area/orcl/control02.ctl'

set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

set DB_RECOVERY_FILE_DEST_SIZE='52000'

nofilenamecheck;

}

Check Standby Archive Destination

Run following command at primary site to check archive dest_1 for standby .

select status, error from v$archive_dest where dest_id=2

On PRIMARY site enable Log_archive_dest_state_2 to start shipping archived redo logs.

SQL>Alter system set Log_archive_dest_state_2=ENABLE scope=both;

Start Apply Process

Start the apply process on standby server.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If you need to cancel the apply process, issue the following command.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

Test Log Transport

On the primary server, check the latest archived redo log and force a log switch.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time

FROM   v$archived_log

ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;

Check the new archived redo log has arrived at the standby server and been applied.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied

FROM   v$archived_log

ORDER BY sequence#;

Protection Mode

There are three protection modes for the primary database:

• Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
• Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
• Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.

By default, for a newly created standby database, the primary database is in maximum performance mode.

SELECT protection_mode FROM v$database;

PROTECTION_MODE

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

MAXIMUM PERFORMANCE

SQL>

The mode can be switched using the following commands. Note the alterations in the redo transport attributes.

-- Maximum Availability.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- Maximum Performance.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

-- Maximum Protection.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

ALTER DATABASE OPEN;

Read-Only Standby and Active Data Guard

Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.
To switch the standby database into read-only mode, do the following.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE OPEN READ ONLY;

To resume managed recovery, do the following.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE OPEN READ ONLY;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.

Step1:

Cancel the manager recovery.

SQL> recover managed standby database cancel;
Media recovery complete.

Step2:

Open the database in read-only mode.

SQL> alter database open read only;
Database altered.

Step3:

Restarts the Redo apply.

SQL> recover managed standby database disconnect using current logfile;
Media recovery complete.