Step 2 Oracle DBA

Automating AWR report Generation

2012-11-08T06:44:00.003-08:00

Here is steps for awr report automization.

=========AWR REPORT AUTOMIZATION===========

CREATE OR REPLACE PROCEDURE CreateAwrReports (begin_snap number,end_snap number, directory varchar2 ) as
v_Instance_number v$instance.instance_number%TYPE;
v_Instance_name v$instance.instance_name%TYPE;
v_dbid V$database.dbid%TYPE;
v_file UTL_FILE.file_type;
BEGIN
SELECT instance_number, instance_name into v_Instance_number,v_Instance_name FROM gv$instance ORDER BY 1;
SELECT dbid INTO v_dbid FROM v$database;
EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY AWR_DIR AS '''||directory||'''');
BEGIN
v_file := UTL_FILE.fopen('AWR_DIR', 'awr_' || v_Instance_name ||'_'|| v_Instance_number || '_' || begin_snap || '_' || (end_snap-1) || '.txt', 'w', 32767);
FOR c_AWRReport IN (
SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT( v_dbid, v_Instance_number, begin_snap,end_snap-1))
) loop
UTL_FILE.PUT_LINE(v_file, c_AWRReport.output); end loop;
UTL_FILE.fclose(v_file);
END;
EXECUTE IMMEDIATE('DROP DIRECTORY AWR_DIR');
END;

create or replace procedure generate_awrreport as
begin_snap number;
end_snap number;
begin
select (MAX(SNAP_ID)-1) into begin_snap from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME,'HH24') = '10';
select (MAX(SNAP_ID)-1) into end_snap from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME,'HH24') = '14';
CreateAwrReports(begin_snap, end_snap,'/export/home/oracle/');
end;
/

Automating ADDM report Generation

2012-11-08T06:41:00.000-08:00

Some times we may have a requirement to generate addm report for peak hours every day. Below are the steps , by which we can automate addm reports generation.

1) Create a task in DB.

begin
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM',
task_name => 'ADDM2',
task_desc => 'Addm report generation schedule');
end;
/

2) Create this procedure in database. This procedure can generate awr report for 10:00 AM to 14:00 AM.

create or replace procedure auto_addm as
start_time number;
end_time number;
BEGIN
DBMS_ADVISOR.RESET_TASK(task_name =>'ADDM1');
select (MAX(SNAP_ID)-1) into start_time from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME,'HH24') = '10';
select (MAX(SNAP_ID)-1) into end_time from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME,'HH24') = '14';
DBMS_ADVISOR.set_task_parameter (
task_name => 'ADDM1',
parameter => 'START_SNAPSHOT',
value => start_time);
DBMS_ADVISOR.set_task_parameter (
task_name => 'ADDM1',
parameter => 'END_SNAPSHOT',
value => end_time);
-- Execute the task.
DBMS_ADVISOR.execute_task(task_name =>'ADDM1');
END auto_addm;
/

3) Schedule the bleow script in crontab.

sqlplus -s '/as sysdba' << EOF
SET LONG 100000
SET PAGESIZE 50000
exec auto_addm;
spool addmrpt.txt
SELECT DBMS_ADVISOR.get_task_report('ADDM1') AS report FROM dual;
spool off;

exit;

EOF
cat addmrpt.txt |mailx -s "ADDMREPORT For TODAY" "MAIL-ID"

Steps for Configuring DATAGUARD in Oracle 11g

2012-08-28T10:28:00.000-07:00

DATAGUARD CONFIGURATION STEPS:-

Environment
Primary Database DB_UNIQUE_NAME: ORCL
Standby Database DB_UNIQUE_NAME: ORCL_DTGD
ORACLE_SID: ORCL

STEPS:-
1. Enable Force Logging on the Primary database

SQL> alter database force logging;

2. Create password file on Primary database.
3. Create listener.ora,tnsnames.ora on standby database.
LISTENER/TNS FOR PRIMARY DATABASE:-
LISTENERORCL=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.7.115)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENERORCL=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ORCL)
(ORACLE_HOME=/data/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=ORCL))
(SID_DESC=
(GLOBAL_DBNAME=ORCL_DGMGRL)
(ORACLE_HOME=/data/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=ORCL))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/data/oracle/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM=extproc)))

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.7.115)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORCL)
)
)

LISTENER/TNS FOR STANDBY DATABASE:-

ORCL_DTGD=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.7.116)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_ORCL_DTGD=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ORCL_DTGD)
(ORACLE_HOME=/data/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=ORCL_DTGD))
(SID_DESC=
(GLOBAL_DBNAME=ORCL_DTGD_DGMGRL)
(ORACLE_HOME=/data/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=ORCL_DTGD))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/data/oracle/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM=extproc)))

ORCL_DTGD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.7.116)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORCL_DTGD)
)
)

4. Create password file for standby database.
$orapwd file-orapw$ORACLE_SID password= force=y entries=10
5. Copy the password file of standby database on primary server and vice-versa.
6. Add the tns entry of primary server to standby server and vice-versa.
Now we should be albe to login on remote database using tns service name.

Note - ensure that the same password is used as the one used while creating the password file on the Primary host
7. Create a "scratch" init.ora file on the Standby host with just a single parameter

"initORCL_DTGD.ora" [New file]
DB_NAME=ORCL
DB_UNIQUE_NAME=ORCL_DTGD
Audit_file_dest=’’
Background_file_dest=’’

8. Create the required directories on the Standby host as mentioned in pfile.
9. Start standby database using pfile in nomount state.

On Primary database:

10. On the primary server, connect to Primary database(ORCL) as target and standby database(ORCL_DTGD) as auxiliary.
bash-3.00$ rman target sys/sys@ORCL auxiliary sys/sys@ORCL_DTGD

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Mar 30 14:26:29 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1353251360)
connected to auxiliary database: ORCL (DBID=1353251360)

RMAN>DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER
SPFILE SET DB_UNIQUE_NAME='ORCL_DTGD' SET LOG_ARCHIVE_DEST_2='service=ORCL LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)' Set STANDBY_FILE_MANAGEMENT='AUTO' SET FAL_SERVER='ORCL' SET FAL_CLIENT='ORCL_DTGD' SET CONTROL_FILES='/data/oracle/app/oracle/oradata/ORCL/control01.ctl’,'/data/oracle/app/oracle/fast_recovery_area/ORCL/control02.ctl’ NOFILENAMECHECK;

11. Add init.ora parameters related to redo transport and redo apply on primary database.

SQL> alter system set fal_server='ORCL_DTGD' scope=both;
System altered.
SQL> alter system set fal_client='ORCL' scope=both;
System altered.
SQL> alter system set standby_file_management=AUTO scope=both;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=ORCL_DTGD LGWR SYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_DTGD';

12. Shutdown the Standby database, add the Standby log files and then start real time recovery

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size 2153536 bytes
Variable Size 3154117568 bytes
Database Buffers 2147483648 bytes
Redo Buffers 40976384 bytes
Database mounted.
Database opened.

SQL> alter database add standby logfile group 4 size 50m;
Database altered.
SQL> alter database add standby logfile group 5 size 50m;
Database altered.
SQL> alter database add standby logfile group 6 size 50m;
Database altered.
SQL> alter database add standby logfile group 7 size 500m;
Database altered.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

SQL> !ps -ef |grep mrp
oracle 471268 1 0 10:51:16 - 0:02 ora_mrp0_ORCL_DTGD
oracle 475150 270568 1 11:02:47 pts/0 0:00 grep mrp

13. Change the Protection Level of the Standby Database to MAXIMIZE AVAILABILITY

On the Primary database:

SQL> shutdown immediate;

Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size 2153536 bytes
Variable Size 3154117568 bytes
Database Buffers 2147483648 bytes
Redo Buffers 40976384 bytes
Database mounted.

SQL> alter database add standby logfile group 4 size 500m;

Database altered.

SQL> alter database add standby logfile group 5 size 500m;

Database altered.

SQL> alter database add standby logfile group 6 size 500m;

Database altered.

SQL> alter database add standby logfile group 7 size 500m;

Database altered.

SQL> alter database set standby database to maximize availability;

Database altered.

SQL> alter database open;

Database altered.

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
14. Configuring DATAGUARD Broker.
bash-3.00$ dgmgrl
DGMGRL for Solaris: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL>CREATE CONFIGURATION ‘DATAGURAD’ PRIMARY DATABASE IS ‘ORCL’ CONNECT IDENTIFIER IS ‘ORCL’;
DGMGRL>ADD DATABASE ‘ORCL_DTGD’ AS CONNECT IDENTIFIER IS ‘ORCL_DTGD’;
DGMGRL>ENABLE CONFIGURATION;
DGMGRL>SHOW CONFIGURATION;

Oracle Binary Cloning

2012-08-26T03:44:00.002-07:00

Install Oracle Server from Previous Installation using Bindary Cloning.

1. Tar or copy the existing installation to be used:
tar cvf .tar
2. Untar or copy the existing installation to $HOME/ ( should be the full
version including patch level (for example, 10.2.0.1):
tar xvf .tar
3. Remove the following files and directories from the copy:
• *.log
• *.dbf
• Listener.ora
• Sqlnet.ora
• Tnsnames.ora
• $ORACLE_HOME/_
• $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole__
4. Use the Oracle Universal Installer to clone the existing installation into the inventory:
$HOME/oui/install/runInstaller \
-clone ORACLE_HOME= \
ORACLE_HOME_NAME=""
Tip: ORACLE_HOME can be attached and detached from the central Oracle inventory using:
• runInstaller -attachHome ORACLE_HOME=
ORACLE_HOME_NAME=

Segment Advisor In Oracle 11g

2012-08-26T03:39:00.001-07:00

STEPS FOR CREATING SEGMENT ADVISOR REPORT IN ORACLE 11g:-

SCHEMA NAME:- ORCL
SEGMENT NAME:- SALES
TABLESPACE NAME:- ORCL_TBS

1> Create a segment advisor task for the ORCL.SALES table.

DECLARE
l_object_id NUMBER;
BEGIN
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => 'SALES_SEGMENT_ADVISOR',
task_desc => 'Segment Advisor For SALES TABLE');
DBMS_ADVISOR.create_object (
task_name => 'SALES_SEGMENT_ADVISOR',
object_type => 'TABLE',
attr1 => 'ORCL',
attr2 => 'SALES',
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => 'SALES_SEGMENT_ADVISOR',
parameter => 'RECOMMEND_ALL',
value => 'TRUE');

DBMS_ADVISOR.execute_task(task_name => 'SALES_SEGMENT_ADVISOR');

2> Create a segment advisor task for the ORCL_TBS tablespace.

DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => 'ORCL_TBS_SEGMENT_ADVISOR',
task_desc => 'Segment Advisor For ORCL_TBS');
DBMS_ADVISOR.create_object (
task_name => 'ORCL_TBS_SEGMENT_ADVISOR',
object_type => 'TABLESPACE',
attr1 => 'ORCL_TBS',
attr2 => NULL,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => 'ORCL_TBS_SEGMENT_ADVISOR',
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => 'ORCL_TBS_SEGMENT_ADVISOR');
END;
/

3> Display the Segment Advisor Report:-

SET LINESIZE 600
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40
set pagesize 500
SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name IN ('SALES_SEGMENT_ADVISOR', 'ORCL_TBS_SEGMENT_ADVISOR')
ORDER BY f.task_name, f.impact DESC;

Steps For Implementing STREAMS in Oracle

2012-08-26T03:30:00.000-07:00

SOURCE DATABASE:- dbbackup
TARGET DATABASE:- TEST

1. ENABLE ARCHIVE LOG IN BOTH THE DATABASES.
2. CONNECT TO SOURCR DATABASE(DBBACKUP).

sql> CREATE USER STREAMADMIN IDENTIFIED BY STREAMADMIN; GRNAT CONNECT , RESOURCE,DBA TO STREAMADMIN;
sql> BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE=>'STREAMADMIN',GRANT_PRIVILEGES=>TRUE); END;/
sql> GRANT SELECT_CATALOG_ROLE, SELECT ANY DICTIONARY TO STREAMADMIN;

3. CONNECT TO TARGET DATABASE(TEST):-

sql> CREATE USER STREAMADMIN IDENTIFIED BY STREAMADMIN; GRNAT CONNECT , RESOURCE,DBA TO STREAMADMIN;
sql> BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE=>'STREAMADMIN',GRANT_PRIVILEGES=>TRUE); END;\
sql> GRANT SELECT_CATALOG_ROLE, SELECT ANY DICTIONARY TO STREAMADMIN;

4.CONNECT TO SOURCR DATABASE(DBBACKUP).
sql>alter system set global_names=true;
sql>alter system set streams_pool_size = 100 m;

5. CONNECT TO TARGET DATABASE(TEST):-

sql>alter system set global_names=true;
sql>alter system set streams_pool_size = 100 m;

6. CONNECT TO SOURCR DATABASE(DBBACKUP) AS STREAMADMIN USER.

sql>CREATE DATABASE LINK TEST CONNECT TO STREAMADMIN IDENTIFIED BY STREAMADMIN USING 'TEST';

7.CONNECT TO TARGET DATABASE(TEST) AS STREAMADMIN USER:-

sql>CREATE DATABASE LINK DBBACKUP CONNECT TO STREAMADMIN IDENTIFIED BY STREAMADMIN USING 'DBBACKUP';

8. SET UP SOURCE & DESTINATION QUEUE:-

A)CONNECT TO SOURCE DATABASE AS STREAMADMIN USER:-

sql>EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

B)CONNECT TO TARGET DATABASE(TEST) AS STREAMADMIN USER:-
sql>EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

9. CONNECT TO SOURCE DATABASE(dbbackup) AS SCOTT USER:-
sql> create table STREAM_TABLE ( no number primary key,name varchar2(20),ddate date);
sql>alter table STREAM_TABLE add supplemental log data (primary key,unique) columns;

10. CONNECT TO TARGET DATABASE(TEST) AS SCOTT USER:-

sql> create table STREAM_TABLE ( no number primary key,name varchar2(20),ddate date);
sql>alter table STREAM_TABLE add supplemental log data (primary key,unique) columns;

11. CONFIGURE CAPTURE PROCESS AT SOURCE DATABASE(dbbackup) AS STREAMADMIN USER:-

Begin DBMS_STREAMS_ADM.SET_UP_QUEUE (
QUEUE_TABLE=>'STREAMS_QUEUE_TABLE',
QUEUE_NAME =>'STREAMS_QUEUE',
QUEUE_USER=>'STREAMADMIN');
END;
/

sql> begin dbms_streams_adm.add_table_rules
( table_name => 'scott.STREAM_TABLE',
streams_type => 'capture',
streams_name => 'capture_stream',
queue_name=> 'STREAMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
end;
/

12. CONFIGURE PROPOGATION PROCESS AT SOURCE DATABASE(DBBACKUP) AS STREAMADMIN USER:-

sql> begin dbms_streams_adm.add_table_propagation_rules
( table_name => 'scott.STREAM_TABLE',
streams_name => 'DBBACKUP_TO_TEST',
source_queue_name => 'STREAMADMIN.STREAMS_QUEUE',
destination_queue_name => 'STREAMADMIN.STREAMS_QUEUE@TEST',
include_dml => true,
include_ddl => true,
source_database => 'DBBACKUP',
inclusion_rule => true);
end;
/

13. Set the instantiation system change number (SCN). CONNECT TO SOURCE DATABASE(DBBACKUP) AS STREAMADMIN USER:-
sql> declare source_scn number;
begin
source_scn := dbms_flashback.get_system_change_number();
dbms_apply_adm.set_table_instantiation_scn@TEST
( source_object_name => 'scott.STREAM_TABLE',
source_database_name => 'DBBACKUP',
instantiation_scn => source_scn);
end;/

14.CONFIGURE APPLY PROCESS AS DESTINATION DATABASE(TEST). CONNEC TO THE TEST DATABASE AS STREAMADMIN USER:-

Begin DBMS_STREAMS_ADM.SET_UP_QUEUE (
QUEUE_TABLE=>'STREAMS_QUEUE_TABLE',
QUEUE_NAME =>'STREAMS_QUEUE',
QUEUE_USER=>'STREAMADMIN');
END;
/

sql>begin dbms_streams_adm.add_table_rules
( table_name => 'scott.STREAM_TABLE',
streams_type => 'apply',
streams_name => 'apply_stream',
queue_name => 'STREAMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'DBBACKUP',
inclusion_rule => true);
end;

15. START CAPTURE & APPLY PROCESS AT SOURCE DATABASE(DBBACKUP). CONNECT TO THE DATABASE AS STREAMADMIN USER:-

sql> begin dbms_capture_adm.start_capture
( capture_name => 'capture_stream');
end;
/

16. START APPLY PROCESS AS DESTINATION DATABASE(TEST). CONNEC TO THE TEST DATABASE AS STREAMADMIN USER:-

SQL>begin dbms_apply_adm.set_parameter
( apply_name => 'apply_stream',
parameter => 'disable_on_error',
value => 'n');
end;
/

sql>begin
dbms_apply_adm.start_apply
( apply_name => 'apply_stream');
end;

Changing datatype for a table column in oracle

2012-08-22T23:13:00.001-07:00

1.)Check For any constraints on that table:-

select constraint_name , constraint_type from user_constraints where table_name='XXXX';

2.)Drop that constraint after taking the backup of DDL.

select dbms_metadata.get_ddl('CONSTRAINT','XXXX','SCHEMA_NAME') from dual;

3.)Adding Temporary column with new datatype:-

alter table XXXXX add (REFERENCE_TXN_ID_TEMP VARCHAR2(51));

4.)Copy existing column data to new temp column:-

update table XXXXXX set REFERENCE_TXN_ID_TEMP=REFERENCE_TXN_ID;
commit;

5.)Mark existing column as Unused:-

alter table XXXXX set unused column REFERENCE_TXN_ID;

6.)Rename Temp Column to Original Column:-

alter table XXXXX rename column REFERENCE_TXN_ID_TEMP to REFERENCE_TXN_ID;

7.)Drop Un-used Column:-

alter table drop unused column;

Automated Tablespace Point-In Time Recovery

2012-08-22T22:27:00.000-07:00

Pre-requistie for automated Tablespace Point-In Time Recovery:-

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

1. Specifiy the auxiliary location for rman to restore auxiliary datafiles.

2. Configure the auxiliary channel on target database.

Step:-

-----------

1. Run the below script.

run {

recover tablespace users

until logseq xxx thread 1

auxiliary destination '/u01/temp';

}

How rman works.

a. Rman creates a auxiliary instance , starts it up and connect to it ,

if there is no connection to auxiliary instance.

b. Rman makes the tablespace users offline , in target database.

c. Rman restores backup control file before point in time to the auxiliary instance.

d. Rman restores datafiles from the recovery set and the auxiliary

set to the auxiliary instance.

e. The restored data files are recovered till point in time in auxiliary instance.

f. Rman opens auxiliary instance with resetlogs option.

g. Rman exports the dictionary metadata about the object.

h. It shuts down the auxiliary instance.

i. Rman issues the switch command on the target database to the recovered auxiliary

instance tablespace.

j. Dictionay metadata are imported in the target database.

k. Deletion of auxiliary set files.

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