RAVI'S DBA

FAST_START_MTTR_TARGET in REDO LOG TUNNING

2012-12-18T04:54:00.002+05:30

This is a new feature introduced in Oracle 10g.

HOW REDO LOGS SIZE AFFECTS PERFORMANCE

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

The size of the redo log files can influence performance, because the behavior of

the database writer and archiver processes depend on the redo log sizes.

Generally, larger redo log files provide better performance, however

it must balanced out with the expected recovery time.Undersized log files

increase checkpoint activity and increase CPU usage.

Checkpoint frequency is affected by several factors, including log file size and

the setting of the FAST_START_MTTR_TARGET initialization parameter.

If the FAST_START_MTTR_TARGET parameter is set to limit the instance recovery time,

Oracle automatically tries to checkpoint as frequently as necessary.

Under this condition, the size of the log files should be large enough to avoid

additional checkpointing due to under sized log files.

Prior to 10g, it was not possible to provide a specific size recommendation for

redo log files, but redo log files in the range of a hundred megabytes to a few

gigabytes were considered reasonable.

As rule of thumb switching logs at most once every fifteen minutes.

The redo logfile sizing advisory is indeed a good feature in 10g wherein the redo logfile

size can tuned as per recommendation specified by column optimal_logfile_size of v$instance_recovery.

This feature requires setting the parameter "fast_start_mttr_target" for the advisory to take effect and

populate the column optimal_logfile_size.

You can try this with different settings of "FAST_START_MTTR_TARGET"

Also Remember-

If FAST_START_MTTR_TARGET is set to such a small value that it is impossible to

do a recovery within its time frame then target_mttr field of v$instance_recovery contains the effective

MTTR target which is larger than FAST_START_MTTR_TARGET.

If FAST_START_MTTR_TARGET is set to such a high value that even in worst case

entire buffer cache is dirty) recovery would not take that long, then

target_mttr field contains the estimated mttr in worst-case scenarios.

For more help on view "V$INSTANCE_RECOVERY" refer Note:180894.1

Example:

--------

Enabling MTTR Advisory Enabling MTTR Advisory involves setting two parameters:

STATISTICS_LEVEL = TYPICAL
FAST_START_MTTR_TARGET > 0

SQL> show parameter fast_start

NAME TYPE VALUE

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

fast_start_mttr_target integer 71

<<< Here you can see that value of parameter "fast_start_mttr_target" = 71.

This is best MTTR which the system can achieve for my system >>>>>>>

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

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

1 1 485 10485760 1 NO INACTIVE

2 1 486 10485760 1 NO INACTIVE

3 1 487 10485760 1 NO CURRENT

<<<<< Initially the size of redo logs are 10 MB >>>>>>

SQL> select ACTUAL_REDO_BLKS,TARGET_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR,

OPTIMAL_LOGFILE_SIZE,CKPT_BLOCK_WRITES from v$instance_recovery;

ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR OPTIMAL_LOGFILE_SIZE

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

942 18432 71 59 49

The recommended optimal redolog file size is 49 MB as seen from column -OPTIMAL_LOGFILE_SIZE.

This is as per the setting of "fast_start_mttr_target" = 71.

SQL> select ACTUAL_REDO_BLKS ,TARGET_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR,

OPTIMAL_LOGFILE_SIZE,CKPT_BLOCK_WRITES from v$instance_recovery;

ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR OPTIMAL_LOGFILE_SIZE

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

597 18432 71 59 49

<<<< Here redo logs are re-created as per recommendations 49 MB >>>>>>

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

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

4 1 490 51380224 1 NO INACTIVE

5 1 491 51380224 1 NO ACTIVE

6 1 492 51380224 1 NO CURRENT

SQL> select ACTUAL_REDO_BLKS ,TARGET_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR,

OPTIMAL_LOGFILE_SIZE,CKPT_BLOCK_WRITES from v$instance_recovery;

ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR OPTIMAL_LOGFILE_SIZE

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

113 18432 71 58 49

You can see that the "actual_redo_blks" column, that is current number of redo blocks

required to be read for recovery has reduced from 597 redo blocks earlier to 113

redo blocks once the log files are re-created with optimal settings.

This will speed up the Instance recovery time.

The MTTR target that your database can achieve given the current value of FAST_START_MTTR_TARGET is called the effective MTTR target. You can view your current effective MTTR by viewing the TARGET_MTTR column of the V$INSTANCE_RECOVERY view.

The default for fast_start_mttr_target is 300 seconds (5 Minutes). and maximum we can give 3600 ( 1 Hour).

Select * from V$MTTR_TARGET_ADVICE;

Whenever you set fast_start_mttr_target to a nonzero value, and while MTTR advisory is ON, Oracle Corporation recommends that you disable (set to 0) the following parameters:

LOG_CHECKPOINT_TIMEOUT

LOG_CHECKPOINT_INTERVAL

FAST_START_IO_TARGET

STEPS TO Create ACL, add privilege and assign the ACL

2012-10-20T03:12:00.000+05:30

If a user for ex - SCOTT is not allowed to connect to a specific server due to ACL restrictions then perform the following steps:-

1. Create an ACL with the ‘connect’ privilege for user SCOTT:

begin

dbms_network_acl_admin.create_acl (

acl => '/sys/acls/scott.xml',

description => 'HTTP Access',

principal => 'SCOTT',

is_grant => TRUE,

privilege => 'connect',

start_date => null,

end_date => null

);

commit;

end;

2. Now add resolve privilege as follows:

begin

dbms_network_acl_admin.add_privilege (

acl => '/sys/acls/scott.xml',

principal => ' SCOTT',

is_grant => TRUE,

privilege => 'resolve',

start_date => null,

end_date => null

);

commit;

end;

3. Now assign ACL created previously to IP address which SCOTT is allowed to connect:

begin

dbms_network_acl_admin.assign_acl (

acl => '/sys/acls/scott.xml',

host => '10.101.3.229',

lower_port => 25,

upper_port => NULL

);

commit;

end;

IF ACL is already created then do the following steps:

1. Add connect privilege:

begin

dbms_network_acl_admin.add_privilege (

acl => '/sys/acls/scott.xml',

principal => ' SCOTT',

is_grant => TRUE,

privilege => ' connect',

start_date => null,

end_date => null

);

commit;

end;

2. Add resolve privilege:

begin

dbms_network_acl_admin.add_privilege (

acl => '/sys/acls/scott.xml',

principal => ' SCOTT',

is_grant => TRUE,

privilege => 'resolve',

start_date => null,

end_date => null

);

commit;

end;

The DBA_NETWORK_ACLS view displays information about network and ACL assignments.

COLUMN host FORMAT A30

COLUMN acl FORMAT A30

SELECT host, lower_port, upper_port, acl

FROM dba_network_acls;

HOST LOWER_PORT UPPER_PORT ACL

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

10.1.10.* /sys/acls/test_acl_file.xml

192.168.2.3 80 80 /sys/acls/test_acl_file.xml

2 rows selected.

The DBA_NETWORK_ACL_PRIVILEGES view displays information about privileges associated with the ACL.

COLUMN acl FORMAT A30

COLUMN principal FORMAT A30

SELECT acl,

       principal,

       privilege,

       is_grant,

       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,

       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date

FROM   dba_network_acl_privileges;

ACL                            PRINCIPAL   PRIVILE IS_GR START_DATE  END_DATE

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

/sys/acls/test_acl_file.xml    TEST1       connect true  02-APR-2008

/sys/acls/test_acl_file.xml    TEST2       connect false

2 rows selected.

How to load data using SQL*Loader

2012-10-02T17:55:00.000+05:30

SQL*Loader uses the following files:

Loader control file: Contain input format, output tables, and optional conditions

Input data files: Contain data in the format defined in the control file

Parameter file (optional file): Consist of command line parameters for the load

Log file: Automatically created by SQL loader.

Bad file: Contain rejected records.

Discard file (if required): Store all records that did not satisfy the selection criteria.

SQL*Loader Control File Example

LOAD DATA

INFILE ’ /SQL_LOADER / Employee_Details.csv’

BADFILE ’ Employee_Details.bad’

DISCARDFILE ’ Employee_Details.dsc’

APPEND

INTO TABLE LGHR.employee

WHEN (57) = ’.’

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

(EMP_NO ,

ename "UPPER(:ename)",

DOJ "TO_TIMESTAMP(:DOJ,'MM/DD/YYYY HH:MI:SS AM')",

sal CHAR TERMINATED BY WHITESPACE "TO_NUMBER(:sal,’$99,999.99’)",

FLAG TERMINATED BY WHITESPACE

)

SQL*Loader Command

sqlldr userid=system/manager control=LGHR_employee_table.ctl log=LGHR_employee_table.log

Control file explanation:

1. LOAD DATA specifies the beginning of a new data load.

2. INFILE specifies the name of a file containing data that you want to load.

3. BADFILE specifies the name of a file into which rejected records are placed.

4. DISCARDFILE specifies the name of a file into which discarded records are placed.

5. APPEND for loading data into a table that is not empty. To load data into a table that is empty, you use the INSERT keyword.

6. INTO TABLE for identify tables, fields, and data types. It defines the relationship between records in the data file and tables in the database.

7. WHEN specifies one or more field conditions that each record must match. In this example SQL*Loader will only load the record if the 57th character is a decimal point.

8. TRAILING NULLCOLS tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

9. The remainder of the control file contains the field list, which provides information about column formats in the table that is being loaded.

HOW TO CREATE PHYSICAL STANDBY

2012-09-08T01:33:00.001+05:30

Primary Database Steps

1) Enable Forced Logging

SQL> ALTER DATABASE FORCE LOGGING;

2) Create a Password File

[csnetsouthdb:oem10g] > cd $ORACLE_HOME/dbs
[csnetsouthdb:oem10g] > orapwd file=orapwwebapp password=oracle force=y

3) Set Primary Database Initialization Parameters

LOG_ARCHIVE_DEST_1='LOCATION=/webapp_arch/arch'

LOG_ARCHIVE_DEST_2='SERVICE= WEBAPP_STDBY’

log_archive_dest_state_1='enable'

log_archive_dest_state_2='enable'

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
#Standby role parameters------------------------------------------FAL_SERVER='WEBAPP_STDBY' (SERVICE NAME for STNDBY)

FAL_CLIENT= 'WEBAPP_PROD' (SERVICE NAME for PROD)

STANDBY_FILE_MANAGEMENT='AUTO'

STANDBY_ARCHIVE_DEST ='/webapp_arch/arch'

4) Make a tns entry in tnsnames.ora in Production

WEBAPP_STDBY=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.201.22)(PORT = 1533))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = WEBAPP)

)

WEBAPP_PROD=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.201.12)(PORT = 1533))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = WEBAPP)

)

5) Make an entry in listener.ora in Production

ORA11G_PROD =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))

(ADDRESS = (PROTOCOL = TCP)(HOST =10.100.201.12)(PORT = 1533))

)

SID_LIST_ORA11G_PROD =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME=/ora11g/app/oracle/11.1.3)

(SID_NAME = WEBAPP)

)

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /ora11g/app/oracle/11.1.3)

(PROGRAM = extproc)

) )

STEPS NEEDS TO PERFORM ON TARGET STNADBY LOCATION

1. Log into the new standby server where UNIX team stored our database.

2. Make a tns entry in tnsnames.ora in new standby

WEBAPP_STDBY=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.201.22)(PORT = 1533))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = WEBAPP)

)

WEBAPP_PROD=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.201.12)(PORT = 1533))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = WEBAPP)

)

3. Make an entry in listener.ora in new standby

ORA11G_STDBY =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))

(ADDRESS = (PROTOCOL = TCP)(HOST =10.100.201.22)(PORT = 1533))

)

SID_LIST_ORA11G_STDBY=

(SID_LIST =

(SID_DESC =

(ORACLE_HOME=/ora11g/app/oracle/11.1.3)

(SID_NAME = WEBAPP)

)

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /ora11g/app/oracle/11.1.3)

(PROGRAM = extproc)

) )

4. Edit Parameter file with the following parameter in new standby.

FAL_SERVER='WEBAPP_PROD' (SERVICE NAME for PROD)

FAL_CLIENT='WEBAPP_STDBY' (SERVICE NAME for STNDBY)

STANDBY_FILE_MANAGEMENT='AUTO'

LOG_ARCHIVE_DEST_1='LOCATION=/webapp_arch/arch'

log_archive_dest_state_1='enable'

STANDBY_ARCHIVE_DEST ='/webapp_arch/arch'

5. LOG IN TO PRIMARY DATABASE and create standby control file.

alter database create standby controlfile as '/home/users/oem10g/ctlstdby.ctl';

6. copy created control file to target standby location at the same place where we have mentioned in control file.

7. conn to oracle instance with sysdba privilege

sqlplus " / as sysdba"

8. Then startup nomount

9. Then put the standby database in mount mode.

Alter database mount standby database;

10. Then recover database

recover standby database;

11. Start the redo apply:

SQL> alter database recover managed standby database disconnect from session;

12. On the Primary:

SQL> alter system switch logfile;

SQL> alter system archive log current;

13. Verify the Physical Standby Database Is Performing properly or not on both Primary and Standby

SQL>select SEQUENCE#, ARCHIVED, APPLIED, STATUS from v$archived_log;

SPREPORT CONFIGURATION STEPS

2012-09-04T21:24:00.000+05:30

1. Create PERFSTAT Tablespace:

CREATE TABLESPACE perfstat

DATAFILE '/wms_data04/orawms/LGWMS/system/perfstat01.dbf' SIZE 1000M;

2. Run the following Script:

@?/rdbms/admin/spcreate.sql;

3. Choose the parameters as follows:

- PERFSTAT user's password - perfstat

- Default tablespace – PERFSTAT

- Temporary tablespace

4. Set the STATSPACK Level:

Conn perfstat/perfstat

exec statspack.snap(i_snap_level => 7, i_modify_parameter => 'true');

5. View the STATSPACK Level by using following query:

Select SNAP_LEVEL from STATS$SNAPSHOT;

6. Create and View Snapshot:

Conn perfstat/perfstat

exec statspack.snap;

Select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')

"Date/Time" from stats$snapshot,v$database;

7. Run the following script to schedule snapshot collections on every hour:

Conn perfstat/perfstat

@?/rdbms/admin/spauto.sql;

2012-05-19T19:16:00.000+05:30

SCHEMA REFRESH USING DATA PUMP

1. Check for default tablespace and schema size

SELECT owner,tablespace_name,sum(bytes/1024/1024) FROM dba_segments WHERE OWNER='LGDMS_NEW' GROUP BY owner, tablespace_name;

SELECT username, temporary_tablespace FROM dba_users where USERNAME='LGDMS_NEW';

2. Check for objects

Select count(*) from DBA_OBJECTS where OWNER='LGDMS_NEW';

Select OWNER,OBJECT_TYPE,count(*) from dba_objects where OWNER='LGDMS_NEW' group by OWNER,OBJECT_TYPE;

3. Export Schema to the desired location using expdp

CREATE OR REPLACE DIRECTORY expdp_dir AS '/webapp_arch/arch/expdp/';

GRANT READ, WRITE ON DIRECTORY expdp_dir TO system;

expdp system/manager schemas=lgdms_new directory=expdp_dir dumpfile=lgdms_new.dmp logfile=expdplgdms_new.log

4. Transfer dmp file to the desired location

scp lgdms_new.dmp 11gtest@192.168.20.133:/w_data2/oradata/EXP_IMP/.

5. Drop user to be refreshed

drop user lgdms_new cascade;

6. No need to create user when importing using impdp

7. Check Space on destination DB

8. Import dmp file using impdp

CREATE OR REPLACE DIRECTORY expdp_dir AS '/w_data2/oradata/EXP_IMP/';

GRANT READ, WRITE ON DIRECTORY expdp_dir TO system;

impdp system/manager schemas=lgdms_new directory=expdp_dir dumpfile=lgdms_new.dmp logfile=impdplgdms_new.log

9. Compare Objects from both schema

Select OWNER,OBJECT_TYPE,count(*) from dba_objects where OWNER='LGDMS_NEW' group by OWNER,OBJECT_TYPE;

============================================================================

TO UPDATE SEQUENCES ON TEST SERVER

1. To drop sequences on test server run output of the following command

SELECT 'DROP SEQUENCE ' || sequence_name || ';'

FROM dba_sequences where SEQUENCE_OWNER='LGDMS_NEW'

ORDER BY sequence_name;

2. TO recreate sequences on test server do the following steps

*run the following query on production server

SELECT 'CREATE SEQUENCE ' || sequence_name ||

' INCREMENT BY ' || increment_by ||

' START WITH ' ||TO_CHAR(LAST_NUMBER+1) ||

' MINVALUE ' || MIN_VALUE ||

' MAXVALUE ' || MAX_VALUE ||

CASE cycle_flag WHEN 'N' THEN ' NOCYCLE ' ELSE ' CYCLE ' END ||

CASE order_flag WHEN 'N' THEN ' NOORDER ' ELSE ' ORDER ' END ||

' CACHE ' || CACHE_SIZE || ';'

FROM dba_sequences where SEQUENCE_OWNER='LGDMS_NEW'

ORDER BY sequence_name;

*Output of the above query need to be run on test server

3. Compare Objects from both schema

Select OWNER,OBJECT_TYPE,count(*) from dba_objects where OWNER='LGDMS_NEW' group by OWNER,OBJECT_TYPE;