Oracle SQL Tuning Advisor Steps
sqlplus / as sysdba begin dbms_sqltune.drop_tuning_task(task_name => 'my_sql_tuning_task'); end; / declare my_task_name varchar2(30); begin my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_id => '&sql_id', --make sure that you enter proper SQL_ID task_name => 'my_sql_tuning_task', time_limit => 1800, description => 'Task to tune a query '); END; / begin DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' ); end; / spool Report.out set long 10000000 select dbms_sqltune.report_tuning_task('my_sql_tuning_task') from dual / spool off |
Wednesday, October 25, 2017
Oracle SQL Tuning Advisor
Wednesday, September 11, 2013
Fast method to dump large oracle table to CSV text file
Example below by using UTL file ultity to extracting 1.2 millions table(temp_activation_01092013) in 30 seconds which usually took 2 hours by oracle spool.
1. Use below procedure. Replace Columns and table name as needed. Example here is for table temp_activation_01092013 table:
IS
CURSOR c
IS
SELECT * FROM eaidba.temp_activation_01092013;
line VARCHAR2 (500);
SR_ID VARCHAR2 (50 BYTE);
TIME_STAMP TIMESTAMP (6);
NT TIMESTAMP (6);
NETWORK_TIME TIMESTAMP (6);
BT TIMESTAMP (6);
BILLING_TIME TIMESTAMP (6);
ST TIMESTAMP (6);
SIEBEL_TIME TIMESTAMP (6);
CC TIMESTAMP (6);
CC_TIME TIMESTAMP (6);
TT TIMESTAMP (6);
TOTAL_TIME TIMESTAMP (6);
BEGIN
f := UTL_FILE.fopen (directory_name, file_name, 'W');
OPEN c;
LOOP
FETCH c INTO c1;
line :=
trim(c1.SR_ID
|| ','
|| c1.TIME_STAMP
|| ','
|| c1.NT
|| ','
|| c1.NETWORK_TIME
|| ','
|| c1.BT
|| ','
|| c1.BILLING_TIME
|| ','
|| c1.ST
|| ','
|| c1.SIEBEL_TIME
|| ','
|| c1.CC
|| ','
|| c1.CC_TIME
|| ','
|| c1.TT
|| ','
|| c1.TOTAL_TIME);
UTL_FILE.put_line (f, line);
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
UTL_FILE.fclose (f);
END;
/
########## Below Procedure could be use directly for SQL statement:
CREATE OR REPLACE PROCEDURE prc_file_mult_column_generate(
p_file_dir VARCHAR2, -- mandatory (Oracle directory name)
p_file_name VARCHAR2, -- mandatory
p_sql_query VARCHAR2, -- Multiple column SQL SELECT statement that needs to be executed and processed
p_delimiter CHAR -- column delimiter
)
AS
l_cursor_handle INTEGER;
l_dummy NUMBER;
l_col_cnt INTEGER;
l_rec_tab DBMS_SQL.DESC_TAB;
l_current_col NUMBER(16);
l_current_line VARCHAR2(2047);
l_column_value VARCHAR2(300);
l_file_handle UTL_FILE.FILE_TYPE;
l_print_text VARCHAR2(100);
l_record_count NUMBER(16) := 0;
BEGIN
/* Open file for append*/
l_file_handle := UTL_FILE.FOPEN(p_file_dir, p_file_name, 'a', 2047); --Append Mode, 2047 chars per line max, possibly increasable
l_cursor_handle := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(l_cursor_handle, p_sql_query, DBMS_SQL.native);
l_dummy := DBMS_SQL.EXECUTE(l_cursor_handle);
/* Output column names and define them for latter retrieval of data */
DBMS_SQL.DESCRIBE_COLUMNS(l_cursor_handle, l_col_cnt, l_rec_tab); -- get column names
/* Append to file column headers */
l_current_col := l_rec_tab.FIRST;
IF (l_current_col IS NOT NULL) THEN
LOOP
DBMS_SQL.DEFINE_COLUMN(l_cursor_handle, l_current_col, l_column_value, 300);
l_print_text := l_rec_tab(l_current_col).col_name || p_delimiter;
UTL_FILE.PUT (l_file_handle, l_print_text);
l_current_col := l_rec_tab.NEXT(l_current_col);
EXIT WHEN (l_current_col IS NULL);
END LOOP;
END IF;
UTL_FILE.PUT_LINE (l_file_handle,' ');
/* Append data for each row */
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cursor_handle) = 0; -- no more rows to be fetched
l_current_line := '';
/* Append data for each column */
FOR l_current_col IN 1..l_col_cnt LOOP
DBMS_SQL.COLUMN_VALUE (l_cursor_handle, l_current_col, l_column_value);
l_print_text := l_column_value || p_delimiter;
l_current_line := l_current_line || l_column_value || p_delimiter;
END LOOP;
l_record_count := l_record_count + 1;
UTL_FILE.PUT_LINE (l_file_handle, l_current_line);
END LOOP;
UTL_FILE.FCLOSE (l_file_handle);
DBMS_SQL.CLOSE_CURSOR(l_cursor_handle);
EXCEPTION
WHEN OTHERS THEN
-- Release resources
IF DBMS_SQL.IS_OPEN(l_cursor_handle) THEN
DBMS_SQL.CLOSE_CURSOR(l_cursor_handle);
END IF;
IF UTL_FILE.IS_OPEN (l_file_handle) THEN
UTL_FILE.FCLOSE (l_file_handle);
END IF;
--RAISE ;
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_stack);
END;
/
Execution example:
CREATE OR REPLACE
PROCEDURE utl_write_file (directory_name IN VARCHAR2,
file_name IN VARCHAR2)IS
CURSOR c
IS
SELECT * FROM eaidba.temp_activation_01092013;
c1 c%ROWTYPE;
f UTL_FILE.file_type;line VARCHAR2 (500);
SR_ID VARCHAR2 (50 BYTE);
TIME_STAMP TIMESTAMP (6);
NT TIMESTAMP (6);
NETWORK_TIME TIMESTAMP (6);
BT TIMESTAMP (6);
BILLING_TIME TIMESTAMP (6);
ST TIMESTAMP (6);
SIEBEL_TIME TIMESTAMP (6);
CC TIMESTAMP (6);
CC_TIME TIMESTAMP (6);
TT TIMESTAMP (6);
TOTAL_TIME TIMESTAMP (6);
BEGIN
f := UTL_FILE.fopen (directory_name, file_name, 'W');
OPEN c;
LOOP
FETCH c INTO c1;
line :=
trim(c1.SR_ID
|| ','
|| c1.TIME_STAMP
|| ','
|| c1.NT
|| ','
|| c1.NETWORK_TIME
|| ','
|| c1.BT
|| ','
|| c1.BILLING_TIME
|| ','
|| c1.ST
|| ','
|| c1.SIEBEL_TIME
|| ','
|| c1.CC
|| ','
|| c1.CC_TIME
|| ','
|| c1.TT
|| ','
|| c1.TOTAL_TIME);
UTL_FILE.put_line (f, line);
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
UTL_FILE.fclose (f);
END;
/
Create Directory UTL_EXPORT_DIR on DB server and run this procedure.File will be created on DB server.
create
directory UTL_EXPORT_DIR as
'/oradata/bslprod/data01/export';
exec
utl_write_file('UTL_EXPORT_DIR' ,'temp_activation_01092013.csv');########## Below Procedure could be use directly for SQL statement:
CREATE OR REPLACE PROCEDURE prc_file_mult_column_generate(
p_file_dir VARCHAR2, -- mandatory (Oracle directory name)
p_file_name VARCHAR2, -- mandatory
p_sql_query VARCHAR2, -- Multiple column SQL SELECT statement that needs to be executed and processed
p_delimiter CHAR -- column delimiter
)
AS
l_cursor_handle INTEGER;
l_dummy NUMBER;
l_col_cnt INTEGER;
l_rec_tab DBMS_SQL.DESC_TAB;
l_current_col NUMBER(16);
l_current_line VARCHAR2(2047);
l_column_value VARCHAR2(300);
l_file_handle UTL_FILE.FILE_TYPE;
l_print_text VARCHAR2(100);
l_record_count NUMBER(16) := 0;
BEGIN
/* Open file for append*/
l_file_handle := UTL_FILE.FOPEN(p_file_dir, p_file_name, 'a', 2047); --Append Mode, 2047 chars per line max, possibly increasable
l_cursor_handle := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(l_cursor_handle, p_sql_query, DBMS_SQL.native);
l_dummy := DBMS_SQL.EXECUTE(l_cursor_handle);
/* Output column names and define them for latter retrieval of data */
DBMS_SQL.DESCRIBE_COLUMNS(l_cursor_handle, l_col_cnt, l_rec_tab); -- get column names
/* Append to file column headers */
l_current_col := l_rec_tab.FIRST;
IF (l_current_col IS NOT NULL) THEN
LOOP
DBMS_SQL.DEFINE_COLUMN(l_cursor_handle, l_current_col, l_column_value, 300);
l_print_text := l_rec_tab(l_current_col).col_name || p_delimiter;
UTL_FILE.PUT (l_file_handle, l_print_text);
l_current_col := l_rec_tab.NEXT(l_current_col);
EXIT WHEN (l_current_col IS NULL);
END LOOP;
END IF;
UTL_FILE.PUT_LINE (l_file_handle,' ');
/* Append data for each row */
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cursor_handle) = 0; -- no more rows to be fetched
l_current_line := '';
/* Append data for each column */
FOR l_current_col IN 1..l_col_cnt LOOP
DBMS_SQL.COLUMN_VALUE (l_cursor_handle, l_current_col, l_column_value);
l_print_text := l_column_value || p_delimiter;
l_current_line := l_current_line || l_column_value || p_delimiter;
END LOOP;
l_record_count := l_record_count + 1;
UTL_FILE.PUT_LINE (l_file_handle, l_current_line);
END LOOP;
UTL_FILE.FCLOSE (l_file_handle);
DBMS_SQL.CLOSE_CURSOR(l_cursor_handle);
EXCEPTION
WHEN OTHERS THEN
-- Release resources
IF DBMS_SQL.IS_OPEN(l_cursor_handle) THEN
DBMS_SQL.CLOSE_CURSOR(l_cursor_handle);
END IF;
IF UTL_FILE.IS_OPEN (l_file_handle) THEN
UTL_FILE.FCLOSE (l_file_handle);
END IF;
--RAISE ;
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_stack);
END;
/
Execution example:
exec prc_file_mult_column_generate('UTL_EXPORT_DIR','Pkg_Con_Post2Post.csv','select
* from eaidba.TEMP_Blackberry_sub_T1_N',',');
Monday, May 28, 2012
Splitting datafiles to new file system for restoration
For replication of database to new file system, we can use below script to split
production datafiles. It will generate set newname datafile output by evenly splitting based on available storage and checking & correcting duplicate file names.
Script input: List of file new file system name and size in GB for parameters
file_system_name (0) and file_system_size (0) in below script. Parameter m_threshold := 100 will keep 100GB free space for each file system.
SET serveroutput on
SET serveroutput on size 1000000
SET LINESIZE 120
DECLARE
TYPE str_array IS TABLE OF VARCHAR2 (100)
INDEX BY BINARY_INTEGER;
TYPE num_array IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
file_system_name str_array;
file_system_size num_array;
path_selection NUMBER := 0;
m_threshold NUMBER := 0;
no_space_available EXCEPTION;
PROCEDURE rman_rename (
str_array_in str_array,
num_array_in IN OUT num_array,
threshold IN NUMBER
)
IS
BEGIN
FOR file_name1 IN
(SELECT file#, BYTES / 1024 / 1024 / 1024 AS gb, concat(RTRIM(file_name, '.dbf'),'.dbf') file_name
FROM (SELECT SUBSTR (NAME,
INSTR (NAME, '/', -1, 1) + 1
) file_name
FROM v$datafile
GROUP BY SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1)
HAVING COUNT (1) = 1),
v$datafile
WHERE SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1) = file_name
UNION ALL
SELECT file#, BYTES / 1024 / 1024 / 1024 AS gb, file_name
FROM (SELECT BYTES, file#,
SUBSTR
(SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1),
1,
INSTR (SUBSTR (NAME,
INSTR (NAME, '/', -1,
1)
+ 1
),
'.',
1,
1
)
- 1
)
|| '_'
|| CHR
( 96
+ ROW_NUMBER () OVER (PARTITION BY file_name ORDER BY file_name)
)
|| '.dbf' file_name
FROM (SELECT COUNT (1),
SUBSTR (NAME,
INSTR (NAME, '/', -1, 1) + 1
) file_name
FROM v$datafile
GROUP BY SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1)
HAVING COUNT (1) > 1),
v$datafile
WHERE SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1) =
file_name)
ORDER BY 2 DESC)
LOOP
<<main_loop>>
LOOP
IF ((num_array_in (path_selection) - threshold) >= file_name1.gb
)
THEN
num_array_in (path_selection) :=
num_array_in (path_selection)
- file_name1.gb;
DBMS_OUTPUT.put_line ( 'set newname for DATAFILE '
|| file_name1.file#
|| ' to '
|| ''''
|| str_array_in (path_selection)
|| file_name1.file_name
|| ''''
|| ';'
--|| num_array_in (path_selection)
-- || '-->'
-- || file_name1.gb
);
IF (path_selection = str_array_in.COUNT - 1)
THEN
path_selection := 0;
ELSE
path_selection := path_selection + 1;
END IF;
EXIT main_loop;
ELSE
IF (path_selection < str_array_in.COUNT - 1)
THEN
path_selection := path_selection + 1;
GOTO main_loop;
ELSE
RAISE no_space_available;
END IF;
END IF;
END LOOP;
END LOOP;
DBMS_OUTPUT.put_line ('Remaining Space');
DBMS_OUTPUT.put_line ('---------------');
FOR i IN 1 .. num_array_in.COUNT
LOOP
DBMS_OUTPUT.put_line ( 'PATH-->'
|| str_array_in (i - 1)
|| ' SPACE(GB)-->'
|| num_array_in (i - 1)
);
END LOOP;
EXCEPTION
WHEN no_space_available
THEN
DBMS_OUTPUT.put_line ('No Space Available !!');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('OTHERS Exception');
END rman_rename;
BEGIN
file_system_name (0) := '/oradata/brmpp/data01/';
file_system_size (0) := 9800.00;
file_system_name (1) := '/oradata/brmpp/data02/';
file_system_size (1) := 9800.00;
file_system_name (2) := '/oradata/brmpp/data03/';
file_system_size (2) := 9800.00;
file_system_name (3) := '/oradata/brmpp/data04/';
file_system_size (3) := 9800.00;
m_threshold := 100;
rman_rename (file_system_name, file_system_size, m_threshold);
END;
/
production datafiles. It will generate set newname datafile output by evenly splitting based on available storage and checking & correcting duplicate file names.
Script input: List of file new file system name and size in GB for parameters
file_system_name (0) and file_system_size (0) in below script. Parameter m_threshold := 100 will keep 100GB free space for each file system.
SET serveroutput on
SET serveroutput on size 1000000
SET LINESIZE 120
DECLARE
TYPE str_array IS TABLE OF VARCHAR2 (100)
INDEX BY BINARY_INTEGER;
TYPE num_array IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
file_system_name str_array;
file_system_size num_array;
path_selection NUMBER := 0;
m_threshold NUMBER := 0;
no_space_available EXCEPTION;
PROCEDURE rman_rename (
str_array_in str_array,
num_array_in IN OUT num_array,
threshold IN NUMBER
)
IS
BEGIN
FOR file_name1 IN
(SELECT file#, BYTES / 1024 / 1024 / 1024 AS gb, concat(RTRIM(file_name, '.dbf'),'.dbf') file_name
FROM (SELECT SUBSTR (NAME,
INSTR (NAME, '/', -1, 1) + 1
) file_name
FROM v$datafile
GROUP BY SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1)
HAVING COUNT (1) = 1),
v$datafile
WHERE SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1) = file_name
UNION ALL
SELECT file#, BYTES / 1024 / 1024 / 1024 AS gb, file_name
FROM (SELECT BYTES, file#,
SUBSTR
(SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1),
1,
INSTR (SUBSTR (NAME,
INSTR (NAME, '/', -1,
1)
+ 1
),
'.',
1,
1
)
- 1
)
|| '_'
|| CHR
( 96
+ ROW_NUMBER () OVER (PARTITION BY file_name ORDER BY file_name)
)
|| '.dbf' file_name
FROM (SELECT COUNT (1),
SUBSTR (NAME,
INSTR (NAME, '/', -1, 1) + 1
) file_name
FROM v$datafile
GROUP BY SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1)
HAVING COUNT (1) > 1),
v$datafile
WHERE SUBSTR (NAME, INSTR (NAME, '/', -1, 1) + 1) =
file_name)
ORDER BY 2 DESC)
LOOP
<<main_loop>>
LOOP
IF ((num_array_in (path_selection) - threshold) >= file_name1.gb
)
THEN
num_array_in (path_selection) :=
num_array_in (path_selection)
- file_name1.gb;
DBMS_OUTPUT.put_line ( 'set newname for DATAFILE '
|| file_name1.file#
|| ' to '
|| ''''
|| str_array_in (path_selection)
|| file_name1.file_name
|| ''''
|| ';'
--|| num_array_in (path_selection)
-- || '-->'
-- || file_name1.gb
);
IF (path_selection = str_array_in.COUNT - 1)
THEN
path_selection := 0;
ELSE
path_selection := path_selection + 1;
END IF;
EXIT main_loop;
ELSE
IF (path_selection < str_array_in.COUNT - 1)
THEN
path_selection := path_selection + 1;
GOTO main_loop;
ELSE
RAISE no_space_available;
END IF;
END IF;
END LOOP;
END LOOP;
DBMS_OUTPUT.put_line ('Remaining Space');
DBMS_OUTPUT.put_line ('---------------');
FOR i IN 1 .. num_array_in.COUNT
LOOP
DBMS_OUTPUT.put_line ( 'PATH-->'
|| str_array_in (i - 1)
|| ' SPACE(GB)-->'
|| num_array_in (i - 1)
);
END LOOP;
EXCEPTION
WHEN no_space_available
THEN
DBMS_OUTPUT.put_line ('No Space Available !!');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('OTHERS Exception');
END rman_rename;
BEGIN
file_system_name (0) := '/oradata/brmpp/data01/';
file_system_size (0) := 9800.00;
file_system_name (1) := '/oradata/brmpp/data02/';
file_system_size (1) := 9800.00;
file_system_name (2) := '/oradata/brmpp/data03/';
file_system_size (2) := 9800.00;
file_system_name (3) := '/oradata/brmpp/data04/';
file_system_size (3) := 9800.00;
m_threshold := 100;
rman_rename (file_system_name, file_system_size, m_threshold);
END;
/
Wednesday, February 15, 2012
Oracle 10g onward tablespace point in time Recovery
When TSPITR is initiated with RMAN without specifying AUX instance then rman
create auxillary instance based on target database configuration.
In this scenerio restore channel information must be already configured in target database so aux instance can use them in restore/recovery.
The location of the datafiles for the auxillary instance are specified using the
AUXILIARY DESTINATION clause
RECOVER TABLESPACE DATA_TBS UNTIL LOGSEQ 2992 THREAD 1 AUXILIARY DESTINATION '/oracle/data/';
The tablespace is taken offline, restored from a backup, recovered to the specified point-in-time in the auxillary instance
and re-imported into the target database.
The tablespace in the target database should then be backed up and the tablespace brought back online.
BACKUP TABLESPACE DATA_TBS;
ALTER TABLESPACE DATA_TBS ONLINE;
On successful completion the auxillary instance will be cleaned up automatically.
example:
rman target /
run {
recover tablespace users until time “to_date(’2012-02-12 22:55:00′, ‘YYYY-MM-DD HH24:MI:SS’)”
auxiliary destination = ‘/oracle/data/’;
backup tablespace users ;
sql ‘alter tablespace users online’;
}
create auxillary instance based on target database configuration.
In this scenerio restore channel information must be already configured in target database so aux instance can use them in restore/recovery.
The location of the datafiles for the auxillary instance are specified using the
AUXILIARY DESTINATION clause
RECOVER TABLESPACE DATA_TBS UNTIL LOGSEQ 2992 THREAD 1 AUXILIARY DESTINATION '/oracle/data/';
The tablespace is taken offline, restored from a backup, recovered to the specified point-in-time in the auxillary instance
and re-imported into the target database.
The tablespace in the target database should then be backed up and the tablespace brought back online.
BACKUP TABLESPACE DATA_TBS;
ALTER TABLESPACE DATA_TBS ONLINE;
On successful completion the auxillary instance will be cleaned up automatically.
example:
rman target /
run {
recover tablespace users until time “to_date(’2012-02-12 22:55:00′, ‘YYYY-MM-DD HH24:MI:SS’)”
auxiliary destination = ‘/oracle/data/’;
backup tablespace users ;
sql ‘alter tablespace users online’;
}
Database migration from ASM to filesystem and filesystem to ASM
Move database from file system to ASM
______________________________________
1)Prepare the init pfile from original pfile to move to ASM.
Instead of control_files parameter use db_create_file_dest and db_recovery_file_dest to point to ASM diskgoup.
2)Note controlfile location(v$control) eg:/oracle/data/control.dbf
3)Backup database as RMAN copy
rman target /
run
{
backup as copy database format ‘+DATA’;
}
4)shutdown database after backup completion.
Startup nomount with new pfile to move to ASM and restore controlfile to ASM
rman target /
RMAN>restore controlfile from '/oracle/data/control.dbf';
RMAN>alter database mount;
RMAN>switch database to copy;
RMAN>recover database;
RMAN>alter database open;
5)Add new redolog file and new temporary tablespace in ASM diskgroup
and drop the old redo logs and temporary tablespace.
Also create spfile to ASM using new pfile.
Moving database from ASM to file system
_______________________________________
1)Create a backup as copy of the database to a directory in the file system.
rman target /
run
{
backup as copy database format ‘/oracle/oradata/%U’;
}
--> You can allocate multiple disk channel and specify format '‘/oracle/oradata/%b' to keep same file name under new directory structure.
2)Note the controlfile path on ASM and use this path to restore controlfile
eg:'+DATA/controlfile/current.112.734565445';
Shutdown database.
3)Prepare the init pfile with control_files pointing to file system
and startup nomount
rman target /
RMAN>restore controlfile from '+DATA/controlfile/current.112.734565445';
RMAN>alter database mount;
RMAN> switch database to copy;
RMAN> recover database;
RMAN> alter database open;
4)Add new redolog files and new temporary tablespace in file system
and drop the old redo logs and temporary tablespace from ASM.
Also create spfile to file system using new pfile.
______________________________________
1)Prepare the init pfile from original pfile to move to ASM.
Instead of control_files parameter use db_create_file_dest and db_recovery_file_dest to point to ASM diskgoup.
2)Note controlfile location(v$control) eg:/oracle/data/control.dbf
3)Backup database as RMAN copy
rman target /
run
{
backup as copy database format ‘+DATA’;
}
4)shutdown database after backup completion.
Startup nomount with new pfile to move to ASM and restore controlfile to ASM
rman target /
RMAN>restore controlfile from '/oracle/data/control.dbf';
RMAN>alter database mount;
RMAN>switch database to copy;
RMAN>recover database;
RMAN>alter database open;
5)Add new redolog file and new temporary tablespace in ASM diskgroup
and drop the old redo logs and temporary tablespace.
Also create spfile to ASM using new pfile.
Moving database from ASM to file system
_______________________________________
1)Create a backup as copy of the database to a directory in the file system.
rman target /
run
{
backup as copy database format ‘/oracle/oradata/%U’;
}
--> You can allocate multiple disk channel and specify format '‘/oracle/oradata/%b' to keep same file name under new directory structure.
2)Note the controlfile path on ASM and use this path to restore controlfile
eg:'+DATA/controlfile/current.112.734565445';
Shutdown database.
3)Prepare the init pfile with control_files pointing to file system
and startup nomount
rman target /
RMAN>restore controlfile from '+DATA/controlfile/current.112.734565445';
RMAN>alter database mount;
RMAN> switch database to copy;
RMAN> recover database;
RMAN> alter database open;
4)Add new redolog files and new temporary tablespace in file system
and drop the old redo logs and temporary tablespace from ASM.
Also create spfile to file system using new pfile.
Monday, February 13, 2012
CSS Timeout Computation in Oracle Clusterware
The CSS misscount parameter represents the maximum time, in seconds, that a network heartbeat can be missed before entering into a cluster reconfiguration to evict the node. The following are the default values for the misscount parameter and their respective versions when using Oracle Clusterware* in seconds:
OS 10g (R1 &R2) 11g
Linux 60 30
Unix 30 30
VMS 30 30
Windows 30 30
*CSS misscount default value when using vendor (non-Oracle) clusterware is 600 seconds. This is to allow the vendor clusterware ample time to resolve any possible split brain scenarios.
CSS HEARTBEAT MECHANISMS AND THEIR INTERRELATIONSHIP
The synchronization services component (CSS) of the Oracle Clusterware maintains two heartbeat mechanisms
1.) the disk heartbeat to the voting device and
2.) the network heartbeat across the interconnect which establish and confirm valid node membership in the cluster.
Both of these heartbeat mechanisms have an associated timeout value. The disk heartbeat has an internal i/o timeout interval (DTO Disk TimeOut), in seconds, where an i/o to the voting disk must complete. The misscount parameter (MC), as stated above, is the maximum time, in seconds, that a network heartbeat can be missed. The disk heartbeat i/o timeout interval is directly related to the misscount parameter setting. There has been some variation in this relationship
between versions as described below:
9.x.x.x
NOTE, MISSCOUNT WAS A DIFFERENT ENTITY IN THIS RELEASE
10.1.0.2
No one should be on this version
10.1.0.3
DTO = MC - 15 seconds
10.1.0.4
DTO = MC - 15 seconds
10.1.0.4+Unpublished Bug 3306964
DTO = MC - 3 seconds
10.1.0.4 with CRS II Merge patch
DTO =Disktimeout (Defaults to 200 seconds) Normally OR Misscount seconds only during initial Cluster formation or Slightly before reconfiguration
10.1.0.5
IOT = MC - 3 seconds
10.2.0.1 +Fix for unpublished Bug 4896338
IOT=Disktimeout (Defaults to 200 seconds) Normally OR Misscount seconds only during initial Cluster formation or Slightly before reconfiguration
10.2.0.2
Same as above (10.2.0.1 with Patch Bug:4896338
10.1 - 11.2
During node join and leave (reconfiguration) in a cluster we need to reconfigure, in that particular case we use Short Disk TimeOut (SDTO) which is in all versions SDTO = MC – reboottime (usually 3 seconds)
Misscount drives cluster membership reconfigurations and directly effects the availability of the cluster. In most cases, the default settings for MC should be acceptable. Modifying the default value of misscount not only influences the timeout interval for the i/o to the voting disk, but also influences the tolerance for missed network heartbeats across the interconnect.
OS 10g (R1 &R2) 11g
Linux 60 30
Unix 30 30
VMS 30 30
Windows 30 30
*CSS misscount default value when using vendor (non-Oracle) clusterware is 600 seconds. This is to allow the vendor clusterware ample time to resolve any possible split brain scenarios.
CSS HEARTBEAT MECHANISMS AND THEIR INTERRELATIONSHIP
The synchronization services component (CSS) of the Oracle Clusterware maintains two heartbeat mechanisms
1.) the disk heartbeat to the voting device and
2.) the network heartbeat across the interconnect which establish and confirm valid node membership in the cluster.
Both of these heartbeat mechanisms have an associated timeout value. The disk heartbeat has an internal i/o timeout interval (DTO Disk TimeOut), in seconds, where an i/o to the voting disk must complete. The misscount parameter (MC), as stated above, is the maximum time, in seconds, that a network heartbeat can be missed. The disk heartbeat i/o timeout interval is directly related to the misscount parameter setting. There has been some variation in this relationship
between versions as described below:
9.x.x.x
NOTE, MISSCOUNT WAS A DIFFERENT ENTITY IN THIS RELEASE
10.1.0.2
No one should be on this version
10.1.0.3
DTO = MC - 15 seconds
10.1.0.4
DTO = MC - 15 seconds
10.1.0.4+Unpublished Bug 3306964
DTO = MC - 3 seconds
10.1.0.4 with CRS II Merge patch
DTO =Disktimeout (Defaults to 200 seconds) Normally OR Misscount seconds only during initial Cluster formation or Slightly before reconfiguration
10.1.0.5
IOT = MC - 3 seconds
10.2.0.1 +Fix for unpublished Bug 4896338
IOT=Disktimeout (Defaults to 200 seconds) Normally OR Misscount seconds only during initial Cluster formation or Slightly before reconfiguration
10.2.0.2
Same as above (10.2.0.1 with Patch Bug:4896338
10.1 - 11.2
During node join and leave (reconfiguration) in a cluster we need to reconfigure, in that particular case we use Short Disk TimeOut (SDTO) which is in all versions SDTO = MC – reboottime (usually 3 seconds)
Misscount drives cluster membership reconfigurations and directly effects the availability of the cluster. In most cases, the default settings for MC should be acceptable. Modifying the default value of misscount not only influences the timeout interval for the i/o to the voting disk, but also influences the tolerance for missed network heartbeats across the interconnect.
Tuesday, February 7, 2012
Tuning using SQL Tuning advisor
Tuning for SQL_ID=d86a5g6dm7shd using SQL Tuning advisor
1) Use SQL_TEXT or SQL_ID to define tuning task
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'd86a5g6dm7shd',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 300,
task_name => 'd86a5g6dm7shd_tuning_task',
description => 'Tuning task for statement d86a5g6dm7shd.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
2) Execute the tuning task
EXEC DBMS_SQLTUNE.execute_tuning_task( task_name => 'd86a5g6dm7shd_tuning_task');
3) Check status of tuning task
SELECT task_name, status FROM dba_advisor_log WHERE task_name='d86a5g6dm7shd_tuning_task';
4) Report of tuning task
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('d86a5g6dm7shd_tuning_task') AS recommendations FROM dual;
5) Drop the define tuning task
exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'd86a5g6dm7shd_tuning_task');
Another eg using sql text
1. Create the tuning task:
declare
stmt_task VARCHAR2(64);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'select * from service_subscriptions s2 where s2.group_code = ''MEH'' and tss_id not in (''800'', ''93'', ''939'', ''9341'')', task_name => 'SRVC_SUB_TUNING');
end;
2. Execute the tuning task:
exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'SRVC_SUB_TUNING');
3. After the execution finishes display result(Connect as system user):
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SRVC_SUB_TUNING') from dual;
1) Use SQL_TEXT or SQL_ID to define tuning task
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'd86a5g6dm7shd',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 300,
task_name => 'd86a5g6dm7shd_tuning_task',
description => 'Tuning task for statement d86a5g6dm7shd.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
2) Execute the tuning task
EXEC DBMS_SQLTUNE.execute_tuning_task( task_name => 'd86a5g6dm7shd_tuning_task');
3) Check status of tuning task
SELECT task_name, status FROM dba_advisor_log WHERE task_name='d86a5g6dm7shd_tuning_task';
4) Report of tuning task
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('d86a5g6dm7shd_tuning_task') AS recommendations FROM dual;
5) Drop the define tuning task
exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'd86a5g6dm7shd_tuning_task');
Another eg using sql text
1. Create the tuning task:
declare
stmt_task VARCHAR2(64);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'select * from service_subscriptions s2 where s2.group_code = ''MEH'' and tss_id not in (''800'', ''93'', ''939'', ''9341'')', task_name => 'SRVC_SUB_TUNING');
end;
2. Execute the tuning task:
exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'SRVC_SUB_TUNING');
3. After the execution finishes display result(Connect as system user):
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SRVC_SUB_TUNING') from dual;
Subscribe to:
Posts (Atom)