During backup and recovery practice sessions, we often struggle to perform block recovery scenario.
This is because we find it difficult to corrupt an Oracle block.
I performed this test on Oracle 10g Release 2 (10.2.0.4) on Linux (Unix).
In this article, I will discuss how to corrupt an Oracle data block, but before beginning this discussion, I would like to answer: Why to corrupt an Oracle Block?
We will be corrupting an Oracle block in order to practice recovery procedures involved when one encounters a Block Corruption in a production environment.
If a block gets corrupted in any of our production databases we will be in a position to rectify and correct the error instead of wandering for help.
This is purely for educational purpose and please do not practice this on any of your production/development/testing databases, rather create a new database for this purpose
and practice it there. For the purpose of this test, I have created a separate tablespace and a new schema.
SQL> create tablespace corrupt_it_tbs
datafile '/home/oracle/oradata/MYDB1/corrupt_it_.dbf' size 20m;
Tablespace created.
SQL> create user corr identified by corr default tablespace corrupt_it_tbs;
User created.
SQL> grant connect, resource to corr;
Grant succeeded.
Create and populate test table with dummy data as shown:
SQL> connect corr/corr
Connected.
SQL>
SQL> create table tab_all_objects
as
select rownum rowno, object_name
from all_objects;
Table created.
SQL> select count(*) from tab_all_objects;
COUNT(*)
----------
2893
Insert a record into this table which we will be corrupting:
SQL> insert into tab_all_objects values (8888, 'CORRUPT THIS');
1 row created.
SQL> commit;
Commit complete.
Let us take RMAN full database backup before we corrupt the block.
RMAN> backup format '/home/oracle/admin/MYDB1/backup/fulldb_%U' database plus archivelog;
Starting backup at 01-FEB-08 current log archived : :
piece handle=C:\MYDB\RMAN\FULLDB_0LJ7LIML_1_1 tag=TAG20080201T234641 comment=NONE channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:28
Finished backup at 02-FEB-08 Starting backup at 02-FEB-08 current log archived using channel ORA_DISK_1 channel
ORA_DISK_1: starting archive log backupset channel
ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=105 recid=105 stamp=645581560 channel
ORA_DISK_1: starting piece 1 at 02-FEB-08 channel
ORA_DISK_1: finished piece 1 at 02-FEB-08 piece handle=C:\MYDB\RMAN\FULLDB_0MJ7LINS_1_1 tag=TAG20080202T001242 comment=NONE channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:04 Finished backup at 02-FEB-08
RMAN>
Take the tablespace offline so that we can make changes to the datafile. There are many freeware and shareware Hex Editors available in the market.
I am using UltraEdit editor to make changes in our datafile.
SQL> alter tablespace corrupt_ts offline;
Tablespace altered.
Open datafile “'c:\mydb\data\corrupt01.dbf” using UltraEdit (press “Ctrl+h” to toggle between Hex Mode).
Search for our record entry “LET ME CORRUPT” in the file and changed “CORRUPT” to “NORRUPT” and save the file and close UltraEdit. I just changed “C” to “N”. Bring back the tablespace to online mode.
SQL> alter tablespace corrupt_ts online;
Tablespace altered.
You may notice that Oracle doesn’t complain when it brings the datafile online because the file header wasn’t modified. Oracle will complain only when it tries to access the corrupt blocks.
Let’s see what happens when we try to query table “T1”.
SQL> conn test/test Connected.
SQL> select * from t1;
RNO OBJECT_NAME
---------- ------------------------------
1 AQ$_AGENT
2 AQ$_DEQUEUE_HISTORY
:
:
30 AQ$_JMS_NAMEARRAY
ERROR:
ORA-01578: ORACLE data block corrupted (file # 6, block # 13)
ORA-01110: data file 6: 'C:\MYDB\DATA\CORRUPT01.DBF'
30 rows selected.
Query returns 30 records and then complains of block corruption in file 6. Block numbered 13 is being reported as corrupt. Let us see what all blocks are corrupt in “corruption01.dbf” datafile by running dbv utility.
C:\ora10g\BIN>dbv file=C:\MYDB\data\corrupt01.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Mon Feb 4 00:00:11 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = C:\MYDB\data\corrupt01.dbf Page 13 is marked corrupt Corrupt block relative dba: 0x0180000d (file 6, block 13) Bad check value found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x0180000d last change scn: 0x0000.0039aa9f seq: 0x3 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xaa9f0603 check value in block header: 0x85b0 computed block checksum: 0x1b00
DBVERIFY - Verification complete
Total Pages Examined : 1280 Total Pages Processed (Data) : 4
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 11
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1264
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 3779231 (0.3779231)
C:\ora10g\BIN> This utility scans all the blocks in a given datafile and outputs the corrupt ones. In my case, I have one block marked as corrupt. Make a note of all the corrupt blocks as we need to recover them to previous state. Start RMAN session and recover all the corrupt blocks. The beauty of RMAN is that it leaves the entire datafile online except the corrupted blocks and we need to recover only those corrupt blocks instead of entire datafile.
RMAN> blockrecover datafile 6 block 13;
Starting blockrecover at 04-FEB-08 using target database control file instead of recovery catalog allocated channel:
ORA_DISK_1 channel ORA_DISK_1: sid=44 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece C:\MYDB\RMAN\FULLDB_0KJ7LH72_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1 piece handle=C:\MYDB\RMAN\FULLDB_0KJ7LH72_1_1 tag=TAG20080201T234641
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:36
starting media recovery archive log thread 1 sequence 105 is already on disk as file C:\MYDB\FRA\MYDB\ARCHIVELOG\2008_02_02\O1_MF_1_10 5_3T72T48S_.ARC
archive log thread 1 sequence 106 is already on disk as file C:\MYDB\FRA\MYDB\ARCHIVELOG\2008_02_03\O1_MF_1_10 6_3TD97K0Z_.ARC
media recovery complete, elapsed time: 00:00:35
Finished blockrecover at 04-FEB-08
RMAN>
RMAN reports success of block recovery command. Let us query the table again by logging in to SQL*Plus:
SQL> select * from t1;
RNO OBJECT_NAME
---------- ------------------------------
1 AQ$_AGENT
2 AQ$_DEQUEUE_HISTORY
:
:
41 AQ$_JMS_ARRAY_ERROR_INFO
42 AQ$_JMS_ARRAY_ERRORS
99 LET ME CORRUPT
43 rows selected.
SQL>
Wow, the query runs successfully and our original record is restored. Similar article on block recovery in UNIX environment can be found here. Happy recovery (block)!
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
/
declare
corr_count int;
BEGIN
dbms_repair.CHECK_OBJECT(
schema_name => 'CORR',
OBJECT_NAME => 'TAB_ALL_OBJECTS',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => corr_count);
dbms_output.put_line('Deteched '|| corr_count || ' block(s).');
END;
/
DECLARE
num_fix INT;
BEGIN
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'CORR',
OBJECT_NAME => 'TAB_ALL_OBJECTS',
object_type => dbms_repair.table_object,
repair_table_name => 'REPAIR_TABLE',
fix_count => num_fix);
dbms_output.put_line('num fix: '|| to_char(num_fix));
end;
/
BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'CORR',
OBJECT_NAME => 'TAB_ALL_OBJECTS',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.skip_flag);
END;
/
Monday, May 19, 2014
SESSION_CACHED_CURSORS - how to figure out if it can be increased to see performance gains
lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from ( select max(s.value) used
from v$statname n,
v$sesstat s
where n.name = 'session cursor cache count'
and s.statistic# = n.statistic#
),
( select value
from v$parameter
where name = 'session_cached_cursors'
)
union all
select 'open_cursors', lpad(value, 5),
to_char(100 * used / value, '990') || '%'
from ( select max(sum(s.value)) used
from v$statname n,
v$sesstat s
where n.name in ( 'opened cursors current',
'session cursor cache count')
and s.statistic# = n.statistic#
group by s.sid
),
( select value
from v$parameter
where name = 'open_cursors');
PARAMETER VALUE USAGE
---------------------- ----- -----
session_cached_cursors 30 100%
open_cursors 65535 0%
ALTER SYSTEM SESSION_CACHED_CURSORS=
Alternatively, a database level trigger can be created like below to automatically set this value.
create or replace trigger ssc_trig after logon on database
begin
execute immediate 'alter session set session_cached_cursors = 100';
end;
/
Tuesday, June 29, 2010
Generating an AWR report
#!/bin/bash
# Usage: daily_awr.sh
#
#
# Check format of command line
if [ "$#" != 1 ]
then
echo "Usage: daily_awr.sh"
exit 1
fi
# set environment
. ~/.profile > /dev/null
. ~/dba/lib/include > /dev/null
ORACLE_SID=$1
export ORACLE_SID
oracle_running_locally_check
MAIL_LIST=
sqlplus -s '/ as sysdba' <
column begin_snap new_value begin_snap
column end_snap new_value end_snap
column report_name new_value report_name
column full_report_name new_value full_report_name
column instance_number new_value instance_number
column dbid new_value dbid
column output format a80
select dbid from v\$database;
select instance_number from v\$instance;
select min(snap_id) as begin_snap,
max(snap_id) as end_snap
from dba_hist_snapshot
where dbid = &dbid
and instance_number = &instance_number
and snap_level >= 1 -- 5
and begin_interval_time between (trunc(sysdate) + 3.9/24) and (trunc(sysdate) + 16.2/24);
select '$LOG_DIR/sp_' || to_char(sysdate, 'MMDDYY') || '_4am-4pm.html' as report_name
from dual;
select '&&report_name' as full_report_name
from dual;
set feedback off termout off
spool &full_report_name
set verify off pages 0
select output
from table
(
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML
(
&dbid,
&instance_number,
&begin_snap,
&end_snap
)
)
/
spool off
! uuencode &&full_report_name &&full_report_name | $MAILX -s "${ORACLE_SID} DB Statspack Report 4am to 4pm" $MAIL_LIST
quit
EOF
# Usage: daily_awr.sh
#
#
# Check format of command line
if [ "$#" != 1 ]
then
echo "Usage: daily_awr.sh
exit 1
fi
# set environment
. ~/.profile > /dev/null
. ~/dba/lib/include > /dev/null
ORACLE_SID=$1
export ORACLE_SID
oracle_running_locally_check
MAIL_LIST=
sqlplus -s '/ as sysdba' <
column begin_snap new_value begin_snap
column end_snap new_value end_snap
column report_name new_value report_name
column full_report_name new_value full_report_name
column instance_number new_value instance_number
column dbid new_value dbid
column output format a80
select dbid from v\$database;
select instance_number from v\$instance;
select min(snap_id) as begin_snap,
max(snap_id) as end_snap
from dba_hist_snapshot
where dbid = &dbid
and instance_number = &instance_number
and snap_level >= 1 -- 5
and begin_interval_time between (trunc(sysdate) + 3.9/24) and (trunc(sysdate) + 16.2/24);
select '$LOG_DIR/sp_' || to_char(sysdate, 'MMDDYY') || '_4am-4pm.html' as report_name
from dual;
select '&&report_name' as full_report_name
from dual;
set feedback off termout off
spool &full_report_name
set verify off pages 0
select output
from table
(
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML
(
&dbid,
&instance_number,
&begin_snap,
&end_snap
)
)
/
spool off
! uuencode &&full_report_name &&full_report_name | $MAILX -s "${ORACLE_SID} DB Statspack Report 4am to 4pm" $MAIL_LIST
quit
EOF
Friday, June 25, 2010
11g Silent Install (Software only)
This script can be use to generate a response file.
echo "oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=dba
INVENTORY_LOCATION=/apps/oracle/$ORASID/product/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/apps/oracle/$ORASID/product/db11gR2
ORACLE_BASE=/apps/oracle/$ORASID/product
oracle.install.db.InstallEdition=EE
oracle.install.db.isCustomInstall=false
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.config.starterdb.memoryOption=false
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.enableSecuritySettings=true
#oracle.install.db.config.starterdb.control=DB_CONTROL
oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false
oracle.install.db.config.starterdb.automatedBackup.enable=false
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true" > /tmp/silent_install_software_only.rsp
Run this command from the unzipped tar of the downloaded Oracle binaries.
./runInstaller -silent -responseFile /tmp/silent_install_software_only.rsp -ignorePrereq
echo "oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=dba
INVENTORY_LOCATION=/apps/oracle/$ORASID/product/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/apps/oracle/$ORASID/product/db11gR2
ORACLE_BASE=/apps/oracle/$ORASID/product
oracle.install.db.InstallEdition=EE
oracle.install.db.isCustomInstall=false
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.config.starterdb.memoryOption=false
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.enableSecuritySettings=true
#oracle.install.db.config.starterdb.control=DB_CONTROL
oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false
oracle.install.db.config.starterdb.automatedBackup.enable=false
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true" > /tmp/silent_install_software_only.rsp
Run this command from the unzipped tar of the downloaded Oracle binaries.
./runInstaller -silent -responseFile /tmp/silent_install_software_only.rsp -ignorePrereq
Monday, May 3, 2010
Oracle Auditing for failure login attempts
So let's enable auditing by changing this init.ora parameter and bouncing the database.
SQL> alter system set audit_trail=db scope=spfile
SQL> /
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 1436884992 bytes
Fixed Size 2148072 bytes
Variable Size 788535576 bytes
Database Buffers 637534208 bytes
Redo Buffers 8667136 bytes
Database mounted.
show parameter audit
Database opened.
SQL>
SQL> audit session whenever not successful ;
Audit succeeded.
SQL> connect blah/blah
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect /as sysdba
Connected.
SQL> SQL>
SQL> col os_username format a15
SQL> col userhost format a15
SQL> col userhost format a15
SQL> col timestamp format a25
SQL> set pages 120 lines 120
SQL> col logoff_dlock format a15
SQL> select os_username,
2 username,
3 userhost,
4 to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,
5 returncode
6 from dba_audit_session
7 where action_name = 'LOGON'
8 and returncode > 0
9 order by timestamp ;
OS_USERNAME USERNAME USERHOST TIMESTAMP RETURNCODE
--------------- ------------------------------ --------------- ------------------------- ----------
oracle BLAH fcqaodbs01 05/03/2010 11:12:31 1017
Simple isn't it!
We can do a number of different fine-grained auditing in 11g (better than 10g). Keep an eye out for more information on this blog!
SQL> alter system set audit_trail=db scope=spfile
SQL> /
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 1436884992 bytes
Fixed Size 2148072 bytes
Variable Size 788535576 bytes
Database Buffers 637534208 bytes
Redo Buffers 8667136 bytes
Database mounted.
show parameter audit
Database opened.
SQL>
SQL> audit session whenever not successful ;
Audit succeeded.
SQL> connect blah/blah
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect /as sysdba
Connected.
SQL> SQL>
SQL> col os_username format a15
SQL> col userhost format a15
SQL> col userhost format a15
SQL> col timestamp format a25
SQL> set pages 120 lines 120
SQL> col logoff_dlock format a15
SQL> select os_username,
2 username,
3 userhost,
4 to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,
5 returncode
6 from dba_audit_session
7 where action_name = 'LOGON'
8 and returncode > 0
9 order by timestamp ;
OS_USERNAME USERNAME USERHOST TIMESTAMP RETURNCODE
--------------- ------------------------------ --------------- ------------------------- ----------
oracle BLAH fcqaodbs01 05/03/2010 11:12:31 1017
Simple isn't it!
We can do a number of different fine-grained auditing in 11g (better than 10g). Keep an eye out for more information on this blog!
Wednesday, April 28, 2010
Two New and (v) Important defaults with 11g
In Oracle 11g password is case sensitive and the default login failure attempts are set to 10 at the database level.
In Oracle 10g and before we all know that passwords are not case sensitive, so PASSWORD, Password, password would let you in and are all the same.
If you upgrade to Oracle 11g (I know lot of you are waiting for 11gR2), you will find that passwords are case sensitive. Here is an example of case sensitive passwords.
$>sqlplus user/user@mydb1
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 28 11:04:00 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Lets try to connect with a upper case password now...
$>sqlplus user/user@mydb1
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 28 11:04:00 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
So what does this mean to apps running with 91 or 10g, that get to run against a 11g database have to make sure that the password set in it's configuration files is using the correct case.
You can also revert to 9i/10g behavior by changing the database-level parameter sec_case_sensitive_logon parameter to FALSE (its TRUE by default)
alter system set sec_case_sensitive_logon=FALSE;
Also, if you are using DEFAULT profile, it will inherit max login attempts to 10 which is the DEFAULT for 11g databases.
You can set it to an acceptable number by the following:
alter system set sec_max_failed_login_attempts=20 scope=spfile;
You need a database bounce for the above..
In Oracle 10g and before we all know that passwords are not case sensitive, so PASSWORD, Password, password would let you in and are all the same.
If you upgrade to Oracle 11g (I know lot of you are waiting for 11gR2), you will find that passwords are case sensitive. Here is an example of case sensitive passwords.
$>sqlplus user/user@mydb1
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 28 11:04:00 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Lets try to connect with a upper case password now...
$>sqlplus user/user@mydb1
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 28 11:04:00 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
So what does this mean to apps running with 91 or 10g, that get to run against a 11g database have to make sure that the password set in it's configuration files is using the correct case.
You can also revert to 9i/10g behavior by changing the database-level parameter sec_case_sensitive_logon parameter to FALSE (its TRUE by default)
alter system set sec_case_sensitive_logon=FALSE;
Also, if you are using DEFAULT profile, it will inherit max login attempts to 10 which is the DEFAULT for 11g databases.
You can set it to an acceptable number by the following:
alter system set sec_max_failed_login_attempts=20 scope=spfile;
You need a database bounce for the above..
Saturday, April 17, 2010
DDL Logging
ENABLE_DDL_LOGGING
With 11g you can now log ddl into your alert.log (which I thought was cool)
SQL> alter system set enable_ddl_logging=true;
SQL>
SQL> create table x (y number, z timestamp);
following is in trace/alert_MYDB1.log
..
..
Sat Apr 11 12:20:48 2010
create table x (y number, z timestamp)
..
..
With 11g you can now log ddl into your alert.log (which I thought was cool)
SQL> alter system set enable_ddl_logging=true;
SQL>
SQL> create table x (y number, z timestamp);
following is in trace/alert_MYDB1.log
..
..
Sat Apr 11 12:20:48 2010
create table x (y number, z timestamp)
..
..
Thursday, April 15, 2010
9i/10g -> 11g timezone issue
Here's a quick way documented on Metalink ID 396670.1. They take no responsibility of the code and neither do I!
Please make sure you review the code and use it at your own risk.
There are 3 scripts:
1. prepare_zuv9.sql
2. restore_zuv9.sql
3. clean_zuv9.sql
You can use Script 1 to prepare look for all tables that might have potential issue with upgrading to 11g. (Note: if the tables are huge, you might want to use merge or come up with other solutions to make sure your outage window remains small)
Once you upgrade the second script is used to restore the data back to the tables and the 3rd script to clean up all these temp tables.
Here are the scripts.
prepare_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'CREATE TABLE '|| r1.table_owner || '.' || 'BACKUP_';
stmt := stmt || r1.object_id || '_' || r1.column_id || ' (ORIG_ROWID ';
stmt := stmt || 'ROWID PRIMARY KEY, SAVED_VALUE VARCHAR2(256))';
execute immediate stmt;
ln := 'Backup table ' || r1.table_owner || '.' || 'BACKUP_';
ln := ln || r1.object_id || '_' || r1.column_id || ' for ' || r1.table_owner;
ln := ln || '.' || r1.table_name || '(' || r1.column_name || ') created';
dbms_output.put (ln);
stmt := 'INSERT INTO ' || r1.table_owner || '.' || 'BACKUP_' || r1.object_id;
stmt := stmt || '_' || r1.column_id || ' SELECT ROWID, TO_CHAR(' ;
stmt := stmt || r1.column_name || ', ''YYYY-MM-DD HH24:MI:SSXFF TZR'') ';
stmt := stmt || 'FROM ' || r1.table_owner || '.' || r1.table_name||' WHERE ';
stmt := stmt || 'UPPER(TO_CHAR(' || r1.column_name || ',''TZR'')) ';
stmt := stmt || 'IN (SELECT UPPER(TIME_ZONE_NAME) FROM ';
stmt := stmt || 'SYS.SYS_TZUV2_AFFECTED_REGIONS)';
execute immediate stmt;
dbms_output.put_line (', ' || SQL%ROWCOUNT || ' row(s) inserted.');
end loop;
end;
/
restore_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'UPDATE ' || r1.table_owner || '.' || r1.table_name || ' T ';
stmt := stmt || 'SET T.' || r1.column_name||'=(SELECT TO_TIMESTAMP_TZ';
stmt := stmt || '(T1.SAVED_VALUE, ''YYYY-MM-DD HH24:MI:SSXFF TZR'') FROM ';
stmt := stmt || r1.table_owner || '.BACKUP_' || r1.object_id || '_';
stmt := stmt || r1.column_id || ' T1 WHERE T.ROWID=T1.ORIG_ROWID) ';
stmt := stmt || 'WHERE EXISTS (SELECT ORIG_ROWID FROM ' || r1.table_owner ;
stmt := stmt || '.BACKUP_' || r1.object_id || '_'|| r1.column_id;
stmt := stmt || ' T1 WHERE T.ROWID=T1.ORIG_ROWID)';
execute immediate stmt;
ln := SQL%ROWCOUNT || ' row(s) in column ' || r1.table_owner || '.';
ln := ln || r1.table_name || '(' || r1.column_name || ') updated.';
dbms_output.put_line (ln);
end loop;
end;
/
clean_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'DROP TABLE ' || r1.table_owner || '.BACKUP_';
stmt := stmt || r1.object_id || '_' || r1.column_id;
execute immediate stmt;
ln := 'Backup table ' || r1.table_owner || '.BACKUP_';
ln := ln || r1.object_id || '_' || r1.column_id || ' dropped.';
dbms_output.put_line (ln);
end loop;
execute immediate 'drop table sys.sys_tzuv2_temptab';
dbms_output.put_line ('Table SYS.SYS_TZUV2_TEMPTAB dropped.');
execute immediate 'drop table sys.sys_tzuv2_affected_regions';
dbms_output.put_line ('Table SYS.SYS_TZUV2_AFFECTED_REGIONS dropped.');
end;
/
Here's how the RESTORE output would look like:
SQL> @restore_zuv9.sql
415 row(s) in column
QA4.VENDORPREFERENCN_FV(SET_LAST_UPDATE_DATE) updated.
415 row(s) in column
QA4.VENDORPREFERENCES_FV(SET_CREATION_DATE) updated.
518 row(s) in column
QA4.VENDORPREFER_FV(PREFERENCE_START_DATE) updated.
518 row(s) in column
QA4.VENDORPREFERE_FV(PREFERENCE_END_DATE) updated.
518 row(s) in column QA4.VENDOCEATTRBEAN_FV(LAST_UPDATE_DATE)
updated.
18 row(s) in column QA4.TIMER_SERVICE_JOURNAL(CREATION_TIME)
updated.
834 row(s) in column QA4.FR_ALL_NLSTG(SERVICE_START_TIME)
updated.
834 row(s) in column QA4.FR_ALL_VERTICALS_NLSTG(SERVICE_END_TIME)
updated.
1492 row(s) in column QA4.FR_ALL_DATA(SERVICE_START_TIME)
updated.
1492 row(s) in column QA4.FR_ALL_DATA(SERVICE_END_TIME)
updated.
49 row(s) in column QA4.COST_DETAIL(START_DATE) updated.
49 row(s) in column QA4.COST_DETAIL(END_DATE) updated.
PL/SQL procedure successfully completed.
SQL>
Please make sure you review the code and use it at your own risk.
There are 3 scripts:
1. prepare_zuv9.sql
2. restore_zuv9.sql
3. clean_zuv9.sql
You can use Script 1 to prepare look for all tables that might have potential issue with upgrading to 11g. (Note: if the tables are huge, you might want to use merge or come up with other solutions to make sure your outage window remains small)
Once you upgrade the second script is used to restore the data back to the tables and the 3rd script to clean up all these temp tables.
Here are the scripts.
prepare_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'CREATE TABLE '|| r1.table_owner || '.' || 'BACKUP_';
stmt := stmt || r1.object_id || '_' || r1.column_id || ' (ORIG_ROWID ';
stmt := stmt || 'ROWID PRIMARY KEY, SAVED_VALUE VARCHAR2(256))';
execute immediate stmt;
ln := 'Backup table ' || r1.table_owner || '.' || 'BACKUP_';
ln := ln || r1.object_id || '_' || r1.column_id || ' for ' || r1.table_owner;
ln := ln || '.' || r1.table_name || '(' || r1.column_name || ') created';
dbms_output.put (ln);
stmt := 'INSERT INTO ' || r1.table_owner || '.' || 'BACKUP_' || r1.object_id;
stmt := stmt || '_' || r1.column_id || ' SELECT ROWID, TO_CHAR(' ;
stmt := stmt || r1.column_name || ', ''YYYY-MM-DD HH24:MI:SSXFF TZR'') ';
stmt := stmt || 'FROM ' || r1.table_owner || '.' || r1.table_name||' WHERE ';
stmt := stmt || 'UPPER(TO_CHAR(' || r1.column_name || ',''TZR'')) ';
stmt := stmt || 'IN (SELECT UPPER(TIME_ZONE_NAME) FROM ';
stmt := stmt || 'SYS.SYS_TZUV2_AFFECTED_REGIONS)';
execute immediate stmt;
dbms_output.put_line (', ' || SQL%ROWCOUNT || ' row(s) inserted.');
end loop;
end;
/
restore_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'UPDATE ' || r1.table_owner || '.' || r1.table_name || ' T ';
stmt := stmt || 'SET T.' || r1.column_name||'=(SELECT TO_TIMESTAMP_TZ';
stmt := stmt || '(T1.SAVED_VALUE, ''YYYY-MM-DD HH24:MI:SSXFF TZR'') FROM ';
stmt := stmt || r1.table_owner || '.BACKUP_' || r1.object_id || '_';
stmt := stmt || r1.column_id || ' T1 WHERE T.ROWID=T1.ORIG_ROWID) ';
stmt := stmt || 'WHERE EXISTS (SELECT ORIG_ROWID FROM ' || r1.table_owner ;
stmt := stmt || '.BACKUP_' || r1.object_id || '_'|| r1.column_id;
stmt := stmt || ' T1 WHERE T.ROWID=T1.ORIG_ROWID)';
execute immediate stmt;
ln := SQL%ROWCOUNT || ' row(s) in column ' || r1.table_owner || '.';
ln := ln || r1.table_name || '(' || r1.column_name || ') updated.';
dbms_output.put_line (ln);
end loop;
end;
/
clean_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'DROP TABLE ' || r1.table_owner || '.BACKUP_';
stmt := stmt || r1.object_id || '_' || r1.column_id;
execute immediate stmt;
ln := 'Backup table ' || r1.table_owner || '.BACKUP_';
ln := ln || r1.object_id || '_' || r1.column_id || ' dropped.';
dbms_output.put_line (ln);
end loop;
execute immediate 'drop table sys.sys_tzuv2_temptab';
dbms_output.put_line ('Table SYS.SYS_TZUV2_TEMPTAB dropped.');
execute immediate 'drop table sys.sys_tzuv2_affected_regions';
dbms_output.put_line ('Table SYS.SYS_TZUV2_AFFECTED_REGIONS dropped.');
end;
/
Here's how the RESTORE output would look like:
SQL> @restore_zuv9.sql
415 row(s) in column
QA4.VENDORPREFERENCN_FV(SET_LAST_UPDATE_DATE) updated.
415 row(s) in column
QA4.VENDORPREFERENCES_FV(SET_CREATION_DATE) updated.
518 row(s) in column
QA4.VENDORPREFER_FV(PREFERENCE_START_DATE) updated.
518 row(s) in column
QA4.VENDORPREFERE_FV(PREFERENCE_END_DATE) updated.
518 row(s) in column QA4.VENDOCEATTRBEAN_FV(LAST_UPDATE_DATE)
updated.
18 row(s) in column QA4.TIMER_SERVICE_JOURNAL(CREATION_TIME)
updated.
834 row(s) in column QA4.FR_ALL_NLSTG(SERVICE_START_TIME)
updated.
834 row(s) in column QA4.FR_ALL_VERTICALS_NLSTG(SERVICE_END_TIME)
updated.
1492 row(s) in column QA4.FR_ALL_DATA(SERVICE_START_TIME)
updated.
1492 row(s) in column QA4.FR_ALL_DATA(SERVICE_END_TIME)
updated.
49 row(s) in column QA4.COST_DETAIL(START_DATE) updated.
49 row(s) in column QA4.COST_DETAIL(END_DATE) updated.
PL/SQL procedure successfully completed.
SQL>
Upgrade 9i to 11g (Manually)
The following for upgrading a 9.2.0.8 DB to 11.2 version on Solaris
Required packges for installing 11g software: (see equivalent)
--------------------------------------------
unixODBC-devel-2.2.11
libaio-devel-0.3.105
elfutils-libeif-devel-0.97
gcc..
..
etc
Install 11g software in new ORACLE HOME...
/apps/oracle/product/db11gR2 happens to be mine..
Note ID: 429825.1 Database Upgrade steps from 9i to 11g:
--------------------------------------------------------
Step 1:
-------
Log in to the system as the owner of the new 11gR2 ORACLE_HOME and copy the following files from the 11gR1 ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the $HOME/migration in my case:
mkdir $HOME/migration
cp $ORACLE_BASE/product/db11gR2/rdbms/admin/utlu11*i.sql $HOME/migration
cp $ORACLE_BASE/product/db11gR2/rdbms/admin/utltzuv2.sql $HOME/migration
Step 2:
-------
$ sqlplus "/ as sysdba"
SQL> @?/rdbms/admin/utlrp.sql
Keep record of invalid objects to check after the upgrade to 11g to make sure you are re-compiling any objects that became INVALID during migration.
Step 3:
-------
Deprecated CONNECT Role
CONNECT role has only the CREATE SESSION privilege.
So you need to re-grant these privileges to users who have connect role. This SQL will help you save the result in somewhere:
SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
Step 4:
-------
Create script to save DBLINKS create statements:
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','PUBLIC ')||'DATABASE LINK ' ||
DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||
'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''
||L.HOST||''''||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#
Step 5:
-------
Convert the 9i database from TIMEZONE version 1 to version 4:
Download this interm patch..Extract..opatch apply => very simple
Then this query must result version 4:
SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then CASE COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 END
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;
VERSION
----------
4
If the above doesn't work (which it wouldn't), do the following steps. Depending on how much data you have, you might have to try different techniques to make sure you don't pass the downtime window.
http://oramadness.blogspot.com/2010/04/9i10g-11g-timezone-issue.html
Step 6:
-------
Run the script you extracted before from 11g binaries
spool utlu111i.log
@utlu112i.sql
spool off
This script will give you information about the tablespaces if they need to adjusted according to 11g and also give info about other initialization parameters that need to be modified and also Obsolete/Deprecated ones and also deprecated roles like connect.
Keep the log it will be helpful.
Step 7:
-------
Remove the stats for the dictionary ( you will be gathering them again when you are fully upgraded)
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
Step 8:
-------
Check for invalid and corrupt objects in the db.
Set verify off space 0 line 120 heading off
Set feedback off pages 1000
spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES'
/
spool off
exit;
SQL> @?/rdbms/admin/utlvalid.sql
SQL> @analyze.sql
Make sure there is no invalid objects by query'ing the table INVALID_ROWS.
SQL> select * from invalid_rows;
no rows selected
SQL>
Step 9:
-------
a) Stop the listener for the database:
$ lsnrctl stop
b)Create a new listener in Oracle 11g for this db.
Step 10:
--------
Ensure no files need media recovery or in backup mode:
SELECT * FROM v$recover_file;
SELECT * FROM v$backup WHERE status!='NOT ACTIVE';
Step 11:
--------
Resolve any outstanding unresolved distributed transaction:
SQL> select * from dba_2pc_pending;
If this returns rows you should do the following:
SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
Step 12:
--------
Ensure the users sys and system have 'system' as their default tablespace.
SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');
Step 13:
--------
Ensure that the aud$ is in the system tablespace when auditing is enabled.
SELECT tablespace_name
FROM dba_tables
WHERE table_name='AUD$';
Step 14:
--------
Check whether database has any externally authenticated SSL users.
SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';
If any SSL users are found, go thru the upgrade guide for further instructions.
Step 15:
-------
Put the database in noarchivelog mode to minimize the upgrade and finishing in the upgrade window.
Step 16:
-------
Note down the location of datafiles, redo logs, control files.
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT member FROM v$logfile;
After, noting down the the locations, Shutdown the database.
Step 17:
-------
Take cold backup (after shutting down the db and restarting it again)
or
if you have your database in archivelog mode then you can do this,
$ rman target / notcatalog
RMAN>run
{
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/admin/MYDB1/backup/%U';
backup current controlfile;
backup database format '/home/oracle/admin/MYDB1/backup/%U' TAG before_upgrade;
}
Step 18:
--------
Create the SYSAUX tablespace for 11g.
CREATE TABLESPACE SYSAUX
DATAFILE 'sysaux_01.dbf' size 2048M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
Step 19:
-------
Make a backup of the spfile.
Comment out these obsoleted parameters:
LOGMNR_MAX_PERSISTENT_SESSIONS
PLSQL_COMPILER_FLAGS
DDL_WAIT_FOR_LOCKS
Change the following deprecated parameters:
BACKGROUND_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
CORE_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
USER_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
COMMIT_WRITE
INSTANCE_GROUPS
LOG_ARCHIVE_LOCAL_FIRST
PLSQL_DEBUG (replaced by PLSQL_OPTIMIZE_LEVEL)
PLSQL_V2_COMPATIBILITY
REMOTE_OS_AUTHENT
STANDBY_ARCHIVE_DEST
TRANSACTION_LAG attribute (of the CQ_NOTIFICATION$_REG_INFO object)
Set the COMPATIBLE parameter to 10.1.0 if you want to have the option to downgrade. To use all new features of 11g, you need to use
compatible=11.1.0
When done copy the pfile to the new 11g $ORACLE_HOME/dbs
Step 20:
-------
Create .profile11g under oracle user home directory to point to 11g new software and other relevant environment variables.
Step 20:
-------
Update the oratab entry:
/etc/oratab for linux
/var/opt/oracle/oratab for solaris
#ORCL:/u01/oracle/ora9i:Y
ORCL:/u01/oracle/ora11g:Y
Step 21:
========
Upgrading Database to 11gR1...
run .profile11g
Startup the DB in upgrade mode:
------------------------------
cd $HOME/migration
sqlplus '/ as sysdba'
startup UPGRADE
start the upgrade script:
------------------------
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off
At the end, the db will be shutdown by catupgrd.sql script.
Restart the Instance NORMALLY to reinitialize the system parameters for normal operation.
Run the Post-Upgrade Status Tool:
--------------------------------
@?/rdbms/admin/utlu111s.sql
Recompile any remaining stored PL/SQL:
-------------------------------------
@?/rdbms/admin/catuppst.sql
@?/rdbms/admin/utlrp.sql
There may be duplicate objects between SYS and SYSTEM so I followed the Note and dropped system duplicate objects:
You can use this query i wrote to find those duplicates:
select distinct 'drop ' || b.object_type || ' SYSTEM.'||b.object_name || ';'
from all_objects a,
all_objects b
where a.owner = 'SYS'
and b.owner = 'SYSTEM'
and a.object_name = b.object_name
order by 1
/
drop PACKAGE BODY SYSTEM.DBMS_REPCAT_AUTH;
drop PACKAGE SYSTEM.DBMS_REPCAT_AUTH;
drop SYNONYM SYSTEM.CATALOG;
drop SYNONYM SYSTEM.COL;
drop SYNONYM SYSTEM.PUBLICSYN;
drop SYNONYM SYSTEM.SYSCATALOG;
drop SYNONYM SYSTEM.SYSFILES;
drop SYNONYM SYSTEM.TAB;
drop SYNONYM SYSTEM.TABQUOTAS;
drop TABLE SYSTEM.AQ$_SCHEDULES;
drop TABLE SYSTEM.DEF$_AQCALL;
drop TABLE SYSTEM.DEF$_CALLDEST;
drop TABLE SYSTEM.DEF$_DEFAULTDEST;
drop TABLE SYSTEM.DEF$_ERROR;
drop TABLE SYSTEM.DEF$_LOB;
Post Upgrade Steps:
##################
Step 22:
--------
Check listener.ora for any modifications needed to listen on the upgraded DB.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PF11)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /apps/oracle/product/db11gR2)
(SID_NAME = PF11)
)
)
Start the listener:
lsnrctl start
Step 23:
--------
Oracle recommends that you lock all Oracle supplied accounts except for SYS and SYSTEM:
ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;
Step 24:
-------
Change the compatability version to use the new 11g features:
alter system set compatible='11.1.0.6' scope=spfile;
shutdown immediate;
startup;
Step 25:
-------
Now you can gather SYS schema stats.
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS', options => 'GATHER', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
You have a cooked 11g database...
Required packges for installing 11g software: (see equivalent)
--------------------------------------------
unixODBC-devel-2.2.11
libaio-devel-0.3.105
elfutils-libeif-devel-0.97
gcc..
..
etc
Install 11g software in new ORACLE HOME...
/apps/oracle/product/db11gR2 happens to be mine..
Note ID: 429825.1 Database Upgrade steps from 9i to 11g:
--------------------------------------------------------
Step 1:
-------
Log in to the system as the owner of the new 11gR2 ORACLE_HOME and copy the following files from the 11gR1 ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the $HOME/migration in my case:
mkdir $HOME/migration
cp $ORACLE_BASE/product/db11gR2/rdbms/admin/utlu11*i.sql $HOME/migration
cp $ORACLE_BASE/product/db11gR2/rdbms/admin/utltzuv2.sql $HOME/migration
Step 2:
-------
$ sqlplus "/ as sysdba"
SQL> @?/rdbms/admin/utlrp.sql
Keep record of invalid objects to check after the upgrade to 11g to make sure you are re-compiling any objects that became INVALID during migration.
Step 3:
-------
Deprecated CONNECT Role
CONNECT role has only the CREATE SESSION privilege.
So you need to re-grant these privileges to users who have connect role. This SQL will help you save the result in somewhere:
SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
Step 4:
-------
Create script to save DBLINKS create statements:
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','PUBLIC ')||'DATABASE LINK ' ||
DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||
'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''
||L.HOST||''''||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#
Step 5:
-------
Convert the 9i database from TIMEZONE version 1 to version 4:
Download this interm patch..Extract..opatch apply => very simple
Then this query must result version 4:
SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then CASE COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 END
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;
VERSION
----------
4
If the above doesn't work (which it wouldn't), do the following steps. Depending on how much data you have, you might have to try different techniques to make sure you don't pass the downtime window.
http://oramadness.blogspot.com/2010/04/9i10g-11g-timezone-issue.html
Step 6:
-------
Run the script you extracted before from 11g binaries
spool utlu111i.log
@utlu112i.sql
spool off
This script will give you information about the tablespaces if they need to adjusted according to 11g and also give info about other initialization parameters that need to be modified and also Obsolete/Deprecated ones and also deprecated roles like connect.
Keep the log it will be helpful.
Step 7:
-------
Remove the stats for the dictionary ( you will be gathering them again when you are fully upgraded)
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
Step 8:
-------
Check for invalid and corrupt objects in the db.
Set verify off space 0 line 120 heading off
Set feedback off pages 1000
spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES'
/
spool off
exit;
SQL> @?/rdbms/admin/utlvalid.sql
SQL> @analyze.sql
Make sure there is no invalid objects by query'ing the table INVALID_ROWS.
SQL> select * from invalid_rows;
no rows selected
SQL>
Step 9:
-------
a) Stop the listener for the database:
$ lsnrctl stop
b)Create a new listener in Oracle 11g for this db.
Step 10:
--------
Ensure no files need media recovery or in backup mode:
SELECT * FROM v$recover_file;
SELECT * FROM v$backup WHERE status!='NOT ACTIVE';
Step 11:
--------
Resolve any outstanding unresolved distributed transaction:
SQL> select * from dba_2pc_pending;
If this returns rows you should do the following:
SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
Step 12:
--------
Ensure the users sys and system have 'system' as their default tablespace.
SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');
Step 13:
--------
Ensure that the aud$ is in the system tablespace when auditing is enabled.
SELECT tablespace_name
FROM dba_tables
WHERE table_name='AUD$';
Step 14:
--------
Check whether database has any externally authenticated SSL users.
SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';
If any SSL users are found, go thru the upgrade guide for further instructions.
Step 15:
-------
Put the database in noarchivelog mode to minimize the upgrade and finishing in the upgrade window.
Step 16:
-------
Note down the location of datafiles, redo logs, control files.
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT member FROM v$logfile;
After, noting down the the locations, Shutdown the database.
Step 17:
-------
Take cold backup (after shutting down the db and restarting it again)
or
if you have your database in archivelog mode then you can do this,
$ rman target / notcatalog
RMAN>run
{
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/admin/MYDB1/backup/%U';
backup current controlfile;
backup database format '/home/oracle/admin/MYDB1/backup/%U' TAG before_upgrade;
}
Step 18:
--------
Create the SYSAUX tablespace for 11g.
CREATE TABLESPACE SYSAUX
DATAFILE 'sysaux_01.dbf' size 2048M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
Step 19:
-------
Make a backup of the spfile.
Comment out these obsoleted parameters:
LOGMNR_MAX_PERSISTENT_SESSIONS
PLSQL_COMPILER_FLAGS
DDL_WAIT_FOR_LOCKS
Change the following deprecated parameters:
BACKGROUND_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
CORE_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
USER_DUMP_DEST (replaced by DIAGNOSTIC_DEST)
COMMIT_WRITE
INSTANCE_GROUPS
LOG_ARCHIVE_LOCAL_FIRST
PLSQL_DEBUG (replaced by PLSQL_OPTIMIZE_LEVEL)
PLSQL_V2_COMPATIBILITY
REMOTE_OS_AUTHENT
STANDBY_ARCHIVE_DEST
TRANSACTION_LAG attribute (of the CQ_NOTIFICATION$_REG_INFO object)
Set the COMPATIBLE parameter to 10.1.0 if you want to have the option to downgrade. To use all new features of 11g, you need to use
compatible=11.1.0
When done copy the pfile to the new 11g $ORACLE_HOME/dbs
Step 20:
-------
Create .profile11g under oracle user home directory to point to 11g new software and other relevant environment variables.
Step 20:
-------
Update the oratab entry:
/etc/oratab for linux
/var/opt/oracle/oratab for solaris
#ORCL:/u01/oracle/ora9i:Y
ORCL:/u01/oracle/ora11g:Y
Step 21:
========
Upgrading Database to 11gR1...
run .profile11g
Startup the DB in upgrade mode:
------------------------------
cd $HOME/migration
sqlplus '/ as sysdba'
startup UPGRADE
start the upgrade script:
------------------------
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off
At the end, the db will be shutdown by catupgrd.sql script.
Restart the Instance NORMALLY to reinitialize the system parameters for normal operation.
Run the Post-Upgrade Status Tool:
--------------------------------
@?/rdbms/admin/utlu111s.sql
Recompile any remaining stored PL/SQL:
-------------------------------------
@?/rdbms/admin/catuppst.sql
@?/rdbms/admin/utlrp.sql
There may be duplicate objects between SYS and SYSTEM so I followed the Note and dropped system duplicate objects:
You can use this query i wrote to find those duplicates:
select distinct 'drop ' || b.object_type || ' SYSTEM.'||b.object_name || ';'
from all_objects a,
all_objects b
where a.owner = 'SYS'
and b.owner = 'SYSTEM'
and a.object_name = b.object_name
order by 1
/
drop PACKAGE BODY SYSTEM.DBMS_REPCAT_AUTH;
drop PACKAGE SYSTEM.DBMS_REPCAT_AUTH;
drop SYNONYM SYSTEM.CATALOG;
drop SYNONYM SYSTEM.COL;
drop SYNONYM SYSTEM.PUBLICSYN;
drop SYNONYM SYSTEM.SYSCATALOG;
drop SYNONYM SYSTEM.SYSFILES;
drop SYNONYM SYSTEM.TAB;
drop SYNONYM SYSTEM.TABQUOTAS;
drop TABLE SYSTEM.AQ$_SCHEDULES;
drop TABLE SYSTEM.DEF$_AQCALL;
drop TABLE SYSTEM.DEF$_CALLDEST;
drop TABLE SYSTEM.DEF$_DEFAULTDEST;
drop TABLE SYSTEM.DEF$_ERROR;
drop TABLE SYSTEM.DEF$_LOB;
Post Upgrade Steps:
##################
Step 22:
--------
Check listener.ora for any modifications needed to listen on the upgraded DB.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PF11)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /apps/oracle/product/db11gR2)
(SID_NAME = PF11)
)
)
Start the listener:
lsnrctl start
Step 23:
--------
Oracle recommends that you lock all Oracle supplied accounts except for SYS and SYSTEM:
ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;
Step 24:
-------
Change the compatability version to use the new 11g features:
alter system set compatible='11.1.0.6' scope=spfile;
shutdown immediate;
startup;
Step 25:
-------
Now you can gather SYS schema stats.
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS', options => 'GATHER', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
You have a cooked 11g database...
Thursday, January 14, 2010
11g Vitual Columns howto and performance
Let's do some basic testing on Virtual columns.
1 create table employees
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100),
6 loweremail as (lower(email)),
7 emp_full_name as (firstname || ' ' || lastname)
8* )
SQL> /
Table created.
Elapsed: 00:00:00.15
And try to insert some values....
insert into employees values (1,'ALLEN', 'BECK','AllenBeck@aol.com')
*
ERROR at line 1:
ORA-00947: not enough values
Elapsed: 00:00:00.00
Bamm...it failed. So it needs values for virtual columns too ??
insert into employees values (2,'Blah', 'Jlah', 'blah.jlah@msn.com', 'blah.jlah@msn.com','Blah Jlah')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
Elapsed: 00:00:00.01
Not really. Then ?? Why did it fail ? Let's try doing something different...
SQL> insert into employees (empno,firstname, lastname, email) values (1,'ALLEN', 'BECK','AllenBeck@aol.com');
1 row created.
Elapsed: 00:00:00.00
So we know using virtual columns puts some limitations on how we do inserts.
Let's retrieve this data back..
1* select * from employees
SQL> /
EMPNO FIRSTNAME LASTNAME EMAIL LOWEREMAIL EMP_FULL_NAME
---------- ---------- ------------------------- -------------------- -------------------- ------------------------------
1 ALLEN BECK AllenBeck@aol.com allenbeck@aol.com ALLEN BECK
Elapsed: 00:00:00.00
SQL>
How to make sure the column is Virtual ?
1 select table_name, column_name, data_type, hidden_column
2 from dba_tab_cols
3 where table_name = 'EMPLOYEES'
4* and virtual_column = 'YES'
SQL> /
TABLE_NAME COLUMN_NAME DATA_TYPE HID
--------------- ------------------------------ --------------- ---
EMPLOYEES EMP_FULL_NAME VARCHAR2 NO
EMPLOYEES LOWEREMAIL VARCHAR2 NO
SQL>
Now, let's look at some performance metrics between this table and a table with no virtual columns.
SQL> create table employees_nonv
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100)
6 );
Table created.
SQL> create table employees_v
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100),
6 loweremail as (lower(email)),
7 emp_full_name as (firstname || ' ' || lastname)
8 );
Table created.
SQL>
Let's insert 100000 records in a table with no virtual columns.
set timing on
declare
i number:=0;
begin
for i in 1..100000
loop
insert into employees_nonv values (i,'Fredreck', 'Herbert','Fredreck.Herbert@myemailaddress.com');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:29.74
SQL>
Now let's insert 100000 records in a table with virtual columns.
declare
i number:=0;
begin
for i in 1..100000
loop
insert into employees_v (empno, firstname, lastname, email) values (i,'Fredreck', 'Herbert','Fredreck.Herbert@myemailaddress.com');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:29.79
SQL>
Pretty much the same results. Now, let's try to select from them.
Let's do a test by fetching a column from the table without virtual columns.
declare
i number:=0;
j number;
begin
for i in 1..100000
loop
select empno into j
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Let's do the test by fetching a single column from the table with virtual columns.
Elapsed: 00:00:22.26
SQL> SQL>
declare
i number:=0;
j number;
begin
for i in 1..100000
loop
select empno into j
from employees_v
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:22.47
Another one...
declare
i number:=0;
email varchar2(100);
begin
for i in 1..100000
loop
select email into email
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.06
declare
i number:=0;
loweremail varchar2(100);
begin
for i in 1..100000
loop
select loweremail into loweremail
from employees_v
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.36
Let's do another test by fetching multiple columns from the tables without virtual columns.
declare
i number:=0;
firstname varchar2(100);
lastname varchar2(100);
full_name varchar2(100);
email varchar2(100);
begin
for i in 1..100000
loop
select firstname, lastname, firstname || ' ' || lastname full_name, email into firstname, lastname, full_name, email
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.73
Now, let's try to select multiple columns from the tables with virtual columns.
declare
i number:=0;
firstname varchar2(100);
lastname varchar2(100);
full_name varchar2(100);
email varchar2(100);
begin
for i in 1..100000
loop
select firstname, lastname, full_name, loweremail into firstname, lastname, full_name, email
from employees_v
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.79
So, it is clear there is no apparent degradation for using Virtual columns. If there is an index created on a virtual which is a Function based index on the underlying column, may have some performance issues. That is true for any Function based index created. But generally if it's a pseudo column which is used by a PK or other indexed column, there shouldn't be any issues.
1 create table employees
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100),
6 loweremail as (lower(email)),
7 emp_full_name as (firstname || ' ' || lastname)
8* )
SQL> /
Table created.
Elapsed: 00:00:00.15
And try to insert some values....
insert into employees values (1,'ALLEN', 'BECK','AllenBeck@aol.com')
*
ERROR at line 1:
ORA-00947: not enough values
Elapsed: 00:00:00.00
Bamm...it failed. So it needs values for virtual columns too ??
insert into employees values (2,'Blah', 'Jlah', 'blah.jlah@msn.com', 'blah.jlah@msn.com','Blah Jlah')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
Elapsed: 00:00:00.01
Not really. Then ?? Why did it fail ? Let's try doing something different...
SQL> insert into employees (empno,firstname, lastname, email) values (1,'ALLEN', 'BECK','AllenBeck@aol.com');
1 row created.
Elapsed: 00:00:00.00
So we know using virtual columns puts some limitations on how we do inserts.
Let's retrieve this data back..
1* select * from employees
SQL> /
EMPNO FIRSTNAME LASTNAME EMAIL LOWEREMAIL EMP_FULL_NAME
---------- ---------- ------------------------- -------------------- -------------------- ------------------------------
1 ALLEN BECK AllenBeck@aol.com allenbeck@aol.com ALLEN BECK
Elapsed: 00:00:00.00
SQL>
How to make sure the column is Virtual ?
1 select table_name, column_name, data_type, hidden_column
2 from dba_tab_cols
3 where table_name = 'EMPLOYEES'
4* and virtual_column = 'YES'
SQL> /
TABLE_NAME COLUMN_NAME DATA_TYPE HID
--------------- ------------------------------ --------------- ---
EMPLOYEES EMP_FULL_NAME VARCHAR2 NO
EMPLOYEES LOWEREMAIL VARCHAR2 NO
SQL>
Now, let's look at some performance metrics between this table and a table with no virtual columns.
SQL> create table employees_nonv
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100)
6 );
Table created.
SQL> create table employees_v
2 (empno number,
3 firstname varchar2(100),
4 lastname varchar2(100),
5 email varchar2(100),
6 loweremail as (lower(email)),
7 emp_full_name as (firstname || ' ' || lastname)
8 );
Table created.
SQL>
Let's insert 100000 records in a table with no virtual columns.
set timing on
declare
i number:=0;
begin
for i in 1..100000
loop
insert into employees_nonv values (i,'Fredreck', 'Herbert','Fredreck.Herbert@myemailaddress.com');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:29.74
SQL>
Now let's insert 100000 records in a table with virtual columns.
declare
i number:=0;
begin
for i in 1..100000
loop
insert into employees_v (empno, firstname, lastname, email) values (i,'Fredreck', 'Herbert','Fredreck.Herbert@myemailaddress.com');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:29.79
SQL>
Pretty much the same results. Now, let's try to select from them.
Let's do a test by fetching a column from the table without virtual columns.
declare
i number:=0;
j number;
begin
for i in 1..100000
loop
select empno into j
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Let's do the test by fetching a single column from the table with virtual columns.
Elapsed: 00:00:22.26
SQL> SQL>
declare
i number:=0;
j number;
begin
for i in 1..100000
loop
select empno into j
from employees_v
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:22.47
Another one...
declare
i number:=0;
email varchar2(100);
begin
for i in 1..100000
loop
select email into email
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.06
declare
i number:=0;
loweremail varchar2(100);
begin
for i in 1..100000
loop
select loweremail into loweremail
from employees_v
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.36
Let's do another test by fetching multiple columns from the tables without virtual columns.
declare
i number:=0;
firstname varchar2(100);
lastname varchar2(100);
full_name varchar2(100);
email varchar2(100);
begin
for i in 1..100000
loop
select firstname, lastname, firstname || ' ' || lastname full_name, email into firstname, lastname, full_name, email
from employees_nonv
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.73
Now, let's try to select multiple columns from the tables with virtual columns.
declare
i number:=0;
firstname varchar2(100);
lastname varchar2(100);
full_name varchar2(100);
email varchar2(100);
begin
for i in 1..100000
loop
select firstname, lastname, full_name, loweremail into firstname, lastname, full_name, email
from employees_v
where empno = i;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.79
So, it is clear there is no apparent degradation for using Virtual columns. If there is an index created on a virtual which is a Function based index on the underlying column, may have some performance issues. That is true for any Function based index created. But generally if it's a pseudo column which is used by a PK or other indexed column, there shouldn't be any issues.
Tuesday, January 12, 2010
Is ROW MOVEMENT expensive ?
Create NON Partition table
SQL> create table non_part (x number, y number);
Table created.
SQL>
Create Primary Key on this table.
SQL> alter table non_part add primary key(x);
Table altered.
SQL>
Let's create Partitioned table
SQL> CREATE TABLE part
2 ( x NUMBER,
3 y number)
4 PARTITION BY RANGE (y)
5 ( PARTITION part_y_1 VALUES LESS THAN (1),
6 PARTITION part_y_2 VALUES LESS THAN (2),
7 PARTITION part_y_3 VALUES LESS THAN (MAXVALUE)
8 );
Table created.
Elapsed: 00:00:00.09
SQL>
alter table part add primary key (x)
SQL> /
Table altered.
Elapsed: 00:00:00.89
Let's populate the tables now....
declare
i number:=0;
begin
for i in 1..100000
loop
insert into non_part values (i,1);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL>
declare
i number:=0;
begin
for i in 1..100000
loop
insert into part values (i,1);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL>
SQL> select count(1) from non_part;
COUNT(1)
----------
100000
SQL>
SQL> select count(1) from part partition(part_y_1);
COUNT(1)
----------
0
1* select count(1) from part partition(part_y_2)
SQL> /
COUNT(1)
----------
100000
1* select count(1) from part partition(part_y_3)
SQL> /
COUNT(1)
----------
0
SQL> update non_part
2 set y = 3;
100000 rows updated.
Elapsed: 00:01:15.12
So, it took 1 minute and 15 seconds to update 100000 rows.
SQL> update part
2 set y=3;
100000 rows updated.
Elapsed: 00:04:50.19
SQL> select count(1) from part partition(part_y_3);
COUNT(1)
----------
100000
So, the conclusion is that it took almost 4 times as much time to update 100k rows in a partition table with row movement and we made sure all 100k rows were moved.
So each row that took .69 millisecond would take 2.7 millisecond. Generally, in an OLTP system this isn't good but your application should be purely subjective.
SQL> create table non_part (x number, y number);
Table created.
SQL>
Create Primary Key on this table.
SQL> alter table non_part add primary key(x);
Table altered.
SQL>
Let's create Partitioned table
SQL> CREATE TABLE part
2 ( x NUMBER,
3 y number)
4 PARTITION BY RANGE (y)
5 ( PARTITION part_y_1 VALUES LESS THAN (1),
6 PARTITION part_y_2 VALUES LESS THAN (2),
7 PARTITION part_y_3 VALUES LESS THAN (MAXVALUE)
8 );
Table created.
Elapsed: 00:00:00.09
SQL>
alter table part add primary key (x)
SQL> /
Table altered.
Elapsed: 00:00:00.89
Let's populate the tables now....
declare
i number:=0;
begin
for i in 1..100000
loop
insert into non_part values (i,1);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL>
declare
i number:=0;
begin
for i in 1..100000
loop
insert into part values (i,1);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL>
SQL> select count(1) from non_part;
COUNT(1)
----------
100000
SQL>
SQL> select count(1) from part partition(part_y_1);
COUNT(1)
----------
0
1* select count(1) from part partition(part_y_2)
SQL> /
COUNT(1)
----------
100000
1* select count(1) from part partition(part_y_3)
SQL> /
COUNT(1)
----------
0
SQL> update non_part
2 set y = 3;
100000 rows updated.
Elapsed: 00:01:15.12
So, it took 1 minute and 15 seconds to update 100000 rows.
SQL> update part
2 set y=3;
100000 rows updated.
Elapsed: 00:04:50.19
SQL> select count(1) from part partition(part_y_3);
COUNT(1)
----------
100000
So, the conclusion is that it took almost 4 times as much time to update 100k rows in a partition table with row movement and we made sure all 100k rows were moved.
So each row that took .69 millisecond would take 2.7 millisecond. Generally, in an OLTP system this isn't good but your application should be purely subjective.
Thursday, January 7, 2010
ROWID datatype
Now there is a ROWID datatype and you don't have to put that in an char or varchar2 datatype.
1 declare
2 rowids rowid;
3 begin
4 select rowid into rowids
5 from dual;
6 dbms_output.put_line('rowid is '|| rowids);
7* end;
8 /
rowid is AAAABzAABAAAAEmAAA
PL/SQL procedure successfully completed.
SQL>
1 declare
2 rowids rowid;
3 begin
4 select rowid into rowids
5 from dual;
6 dbms_output.put_line('rowid is '|| rowids);
7* end;
8 /
rowid is AAAABzAABAAAAEmAAA
PL/SQL procedure successfully completed.
SQL>
Tuesday, March 31, 2009
Pickler Fetch
SQL> create user rdba identified by rdba;
User created.
SQL> grant dba to rdba;
Grant succeeded.
SQL>
SQL> connect rdba/rdba
Connected.
SQL>
1 create table big_all_objects
2 as
3 select * from all_objects
4 union all
5 select * from all_objects
6 union all
7 select * from all_objects
8 union all
9 select * from all_objects
10 union all
11 select * from all_objects
12 union all
13* select * from all_objects
SQL> /
Table created.
Elapsed: 00:01:04.67
SQL> select count(1) from big_all_objects;
COUNT(1)
----------
141828
Elapsed: 00:00:00.50
SQL>
1* select /* Without Pickler fetch */ object_id from big_all_objects where object_id in (3,30,50,100,150,200)
OBJECT_ID
----------
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
30 rows selected.
Elapsed: 00:00:00.88
Let's prepare for pickler fetch:
SQL> create or replace type inListTable as table of number
2 /
Type created.
SQL> create or replace function str2tbl( p_str in varchar2 ) return inListTable
2 as
3 l_str long default p_str || ',';
4 l_n number;
5 l_data inListTable := inListTable();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
12 l_str := substr( l_str, l_n+1 );
13 end loop;
14 return l_data;
15 end;
16 /
Function created.
SQL> SQL>
1 select /* with pickler fetch */ object_id
2 from big_all_objects
3 WHERE object_id IN (select *
4* from table ( cast ( str2tbl('3,30,50,100,150,200') as inListTable)))
SQL> /
OBJECT_ID
----------
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
30 rows selected.
Elapsed: 00:00:00.81
SQL>
Both of these take about the same time in running in the 2nd, 3rd iteration onwards.
Let's see what v$sqlarea has to say about these two.
First time after running query with pickler fetch..
First time after running query after pickler fetch.
Here are the results after running the above queries 10 times each.
From the above analysis we can conclude that after the procedure stays in shared pool and/or pinned, the execution time and resources used to run the SQL is the same. The big benefit we get is to have a non-fragmented shared pool. This is applicable when the number of arguments in an in list is random.
User created.
SQL> grant dba to rdba;
Grant succeeded.
SQL>
SQL> connect rdba/rdba
Connected.
SQL>
1 create table big_all_objects
2 as
3 select * from all_objects
4 union all
5 select * from all_objects
6 union all
7 select * from all_objects
8 union all
9 select * from all_objects
10 union all
11 select * from all_objects
12 union all
13* select * from all_objects
SQL> /
Table created.
Elapsed: 00:01:04.67
SQL> select count(1) from big_all_objects;
COUNT(1)
----------
141828
Elapsed: 00:00:00.50
SQL>
1* select /* Without Pickler fetch */ object_id from big_all_objects where object_id in (3,30,50,100,150,200)
OBJECT_ID
----------
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
30 rows selected.
Elapsed: 00:00:00.88
Let's prepare for pickler fetch:
SQL> create or replace type inListTable as table of number
2 /
Type created.
SQL> create or replace function str2tbl( p_str in varchar2 ) return inListTable
2 as
3 l_str long default p_str || ',';
4 l_n number;
5 l_data inListTable := inListTable();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
12 l_str := substr( l_str, l_n+1 );
13 end loop;
14 return l_data;
15 end;
16 /
Function created.
SQL> SQL>
1 select /* with pickler fetch */ object_id
2 from big_all_objects
3 WHERE object_id IN (select *
4* from table ( cast ( str2tbl('3,30,50,100,150,200') as inListTable)))
SQL> /
OBJECT_ID
----------
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
3
50
30
100
150
30 rows selected.
Elapsed: 00:00:00.81
SQL>
Both of these take about the same time in running in the 2nd, 3rd iteration onwards.
Let's see what v$sqlarea has to say about these two.
First time after running query with pickler fetch..
EXECUTIONS HASH_VALUE SQL_TEXT SORTS CPU_TIME BUFFER_GETS DISK_READS PARSE_CALLS LOADS
---------- ---------- ------------------------------ ---------- ---------- ----------- ---------- ----------- ----------
1 3492981496 with pickler fetch 0 1370000 11953 9852 1 4
First time after running query after pickler fetch.
EXECUTIONS HASH_VALUE SQL_TEXT SORTS CPU_TIME BUFFER_GETS DISK_READS PARSE_CALLS LOADS
---------- ---------- ------------------------------ ---------- ---------- ----------- ---------- ----------- ----------
1 3532977385 without pickler fetch 0 530000 9174 9562 1 2
Here are the results after running the above queries 10 times each.
EXECUTIONS HASH_VALUE SQL_TEXT SORTS CPU_TIME BUFFER_GETS DISK_READS PARSE_CALLS LOADS
---------- ---------- ------------------------------ ---------- -------- ----------- ---------- ----------- ----------
10 1169982563 without pf 0 4970000 90745 90610 10 1
10 582411935 with pickler fetch 0 5270000 93657 90610 10 4
From the above analysis we can conclude that after the procedure stays in shared pool and/or pinned, the execution time and resources used to run the SQL is the same. The big benefit we get is to have a non-fragmented shared pool. This is applicable when the number of arguments in an in list is random.
Thursday, January 29, 2009
sql date formats
A little FYI...
FORMAT MEANING
D Day of the week
DD Day of the month
DDD Day of the year
DAY Full day for ex. ‘Monday’, ’Tuesday’, ’Wednesday’
DY Day in three letters for ex. ‘MON’, ‘TUE’,’FRI’
W Week of the month
WW Week of the year
MM Month in two digits (1-Jan, 2-Feb,…12-Dec)
MON Month in three characters like “Jan”, ”Feb”, ”Apr”
MONTH Full Month like “January”, ”February”, ”April”
RM Month in Roman Characters (I-XII, I-Jan, II-Feb,…XII-Dec)
Q Quarter of the Month
YY Last two digits of the year.
YYYY Full year
YEAR Year in words like “Nineteen Ninety Nine”
HH Hours in 12 hour format
HH12 Hours in 12 hour format
HH24 Hours in 24 hour format
MI Minutes
SS Seconds
FF Fractional Seconds
SSSSS Milliseconds
J Julian Day i.e Days since 1st-Jan-4712BC to till-date
RR If the year is less than 50 Assumes the year as 21ST Century.
If the year is greater than 50 then assumes the year in 20th Century.
FORMAT MEANING
D Day of the week
DD Day of the month
DDD Day of the year
DAY Full day for ex. ‘Monday’, ’Tuesday’, ’Wednesday’
DY Day in three letters for ex. ‘MON’, ‘TUE’,’FRI’
W Week of the month
WW Week of the year
MM Month in two digits (1-Jan, 2-Feb,…12-Dec)
MON Month in three characters like “Jan”, ”Feb”, ”Apr”
MONTH Full Month like “January”, ”February”, ”April”
RM Month in Roman Characters (I-XII, I-Jan, II-Feb,…XII-Dec)
Q Quarter of the Month
YY Last two digits of the year.
YYYY Full year
YEAR Year in words like “Nineteen Ninety Nine”
HH Hours in 12 hour format
HH12 Hours in 12 hour format
HH24 Hours in 24 hour format
MI Minutes
SS Seconds
FF Fractional Seconds
SSSSS Milliseconds
J Julian Day i.e Days since 1st-Jan-4712BC to till-date
RR If the year is less than 50 Assumes the year as 21ST Century.
If the year is greater than 50 then assumes the year in 20th Century.
Wednesday, June 25, 2008
Sga Auto sizing ! Good or Bad ?
Have you bumped accross SIMULATOR LRU LATCH or SIMULATOR HASH LATCH ?
I have. I'm sure you have too.
What is it ? Why does it happen ?
Let's start from the basics.
What is ASMM ? Auto Shared Memory Management ?
Sure, it manages Oracle SGA memory dynamically. All you have to do is specify an upper limit using
alter system set sga_target = scope=spfile;
(and reboot !)
and Alas ! Oracle automagically sizes the buffer cache, shared pool, streams pool and the java pool. No need to measure any sizes of what buffer cache or shared pool should be.
Right ?
Wrong !
Let's dig more....
What is this ?
This means that ASMM is asking to increase the shared pool area and taking away space from buffer cache and that could be a reason why you will see simulator lru latch and simulator hash latch waits.
All good ?
Here's what I found. Although shared pool has enough free space available, SGA resizing still takes place.
What is this ?
The means there is 800 Megs of free space in the shared pool and ASMM is still asking for more space.
Why is that ?
I have asked Oracle. They had a wierd answer. Library object hits could be benfitial with having a larger shared pool.
Really ?
EVEN WITH FREE SPACE AVAILABLE ???
Here's the latest !
"It is cheaper for Oracle to move granules from buffer cache to Shared Pool than to use the free memory already avaiable in Shared Pool" - Doesn't make sense, does it ?
Well, this above is TRUE. ASMM works on hit ratios and when it thinks Shared Pool could do better by re-sizing it, it so does it !
Moral: Don't rely too much on the AUTOMATIC bells and whistles that Oracle provides.
Study them first !
I have. I'm sure you have too.
What is it ? Why does it happen ?
Let's start from the basics.
What is ASMM ? Auto Shared Memory Management ?
Sure, it manages Oracle SGA memory dynamically. All you have to do is specify an upper limit using
alter system set sga_target =
(and reboot !)
and Alas ! Oracle automagically sizes the buffer cache, shared pool, streams pool and the java pool. No need to measure any sizes of what buffer cache or shared pool should be.
Right ?
Wrong !
Let's dig more....
SQL> select
2 component,
3 parameter,
4 initial_size,
5 target_size,
6 final_size,
7 status,
8 to_char(start_time,'dd-mon hh24:mi:ss') start_time,
9 to_char(end_time,'dd-mon hh24:mi:ss') end_time
10 from
v$sga_resize_ops
10 where
start_time > sysdate -1/12
11* order by
start_time
COMPONENT PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
--------- -------------- ------------ ----------- ---------- ------ --------------- -----------
DEFAULT buffer cache db_cache_size 1.5469E+10 1.5452E+10 1.5452E+10 COMPLETE 04-jun 17:54:40 04-jun 17:54:40
shared pool shared_pool_size 3506438144 3523215360 3523215360 COMPLETE 04-jun 17:54:40 04-jun 17:54:40
SQL>
What is this ?
This means that ASMM is asking to increase the shared pool area and taking away space from buffer cache and that could be a reason why you will see simulator lru latch and simulator hash latch waits.
All good ?
Here's what I found. Although shared pool has enough free space available, SGA resizing still takes place.
SQL> select *
2 from v$sgastat
3 where pool like 'shared pool'
4 and name like 'free%';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool free memory 805474392
What is this ?
The means there is 800 Megs of free space in the shared pool and ASMM is still asking for more space.
Why is that ?
I have asked Oracle. They had a wierd answer. Library object hits could be benfitial with having a larger shared pool.
Really ?
EVEN WITH FREE SPACE AVAILABLE ???
Here's the latest !
"It is cheaper for Oracle to move granules from buffer cache to Shared Pool than to use the free memory already avaiable in Shared Pool" - Doesn't make sense, does it ?
Well, this above is TRUE. ASMM works on hit ratios and when it thinks Shared Pool could do better by re-sizing it, it so does it !
Moral: Don't rely too much on the AUTOMATIC bells and whistles that Oracle provides.
Study them first !
Tuesday, June 24, 2008
Different Yardsticks in Oracle
1) Shared Pool has a 4k Chunk Size.
2) Granule is 16Mb (these days) for an SGA over 128Mb.
3) Shared Pool Library Cache Threshold >2048K (> 2MB)
2) Granule is 16Mb (these days) for an SGA over 128Mb.
3) Shared Pool Library Cache Threshold >2048K (> 2MB)
Monday, June 16, 2008
Linux - One by One the Penguins steal my sanity.
List of some commands I like and you will too !
screen
- Running a script and got disconnected ?
- Running a script in background and forgot to pipe the output ?
These are some and numerous other reasons why you should use screen command rather than doing the above.
screen and hit enter
ctl -a d - to detach from the screen.
screen -ls (to view all disconnected screens like follows)
There are screens on:
6295.ttyp1 (Detached)
22270.ttyp3.server (Detached)
24315.ttyp5.server (Detached)
3 Sockets in /tmp/screens/S-username
screen -r 22270
lets you can get into session 22270
Having fun ?
What happens when i wnat screen's inside screens ?
well. You do the following.
ctl - a - n
gets you in the next screen.
ctl - a - p
gets you in the previous screen.
watch
Great utility ! Ever tried doing iostat -x 3. Well it gives io statictics update every 3 seconds.
Now try this,
watch -n 3 -d iostat
It also refreshes iostat every 3 seconds. But check out the difference.
watch enables the same display over and over again and the deviation portion highlighted.
Probably one of the little great utilites i have every used.
screen
- Running a script and got disconnected ?
- Running a script in background and forgot to pipe the output ?
These are some and numerous other reasons why you should use screen command rather than doing the above.
screen and hit enter
ctl -a d - to detach from the screen.
screen -ls (to view all disconnected screens like follows)
There are screens on:
6295.ttyp1 (Detached)
22270.ttyp3.server (Detached)
24315.ttyp5.server (Detached)
3 Sockets in /tmp/screens/S-username
screen -r 22270
lets you can get into session 22270
Having fun ?
What happens when i wnat screen's inside screens ?
well. You do the following.
ctl - a - n
gets you in the next screen.
ctl - a - p
gets you in the previous screen.
watch
Great utility ! Ever tried doing iostat -x 3. Well it gives io statictics update every 3 seconds.
Now try this,
watch -n 3 -d iostat
It also refreshes iostat every 3 seconds. But check out the difference.
watch enables the same display over and over again and the deviation portion highlighted.
Probably one of the little great utilites i have every used.
Friday, June 6, 2008
Redo copy latch, allocation latches & log file sync
What is redo copy latch ?
Generation of redo requires a redo copy latch to be acquired by a process. This is done so that the LGWR knows the data is being copied and so it doesn't flushes that data in the REDO LOG.
What is redo allocation latch ?
Redo allocation latch is obtained to allocated space in the log buffer. This is done to know which log buffer blocks are used and which are free.
If you see this event, it could be due to a number of things.
1) You don't have enough space in your log buffer and processes are waiting for space allocatioin, or lgwr is slow due to slower disks.
2) dbwr gets a latch to see whether these blocks are written to the disk so it can write these blocks to the data files.
3) The sessions that are waiting on log file sync cycle thru and acquire redo allocation latch to check on the log buffer blocks are written to the redo logs or not.
These are the basic reasons why you will see redo allocation latch. Ask me if you need to know how to fix these issues.
What is log file sync ?
Sessions waiting on the return on the commit from the log buffer to guarantee recovery.
DBWR waiting on LGWR to write redo blocks to the redo buffer so dbwr could write the corresponding blocks in the datafiles.
Slower disks
Smaller log buffer.
Generation of redo requires a redo copy latch to be acquired by a process. This is done so that the LGWR knows the data is being copied and so it doesn't flushes that data in the REDO LOG.
What is redo allocation latch ?
Redo allocation latch is obtained to allocated space in the log buffer. This is done to know which log buffer blocks are used and which are free.
If you see this event, it could be due to a number of things.
1) You don't have enough space in your log buffer and processes are waiting for space allocatioin, or lgwr is slow due to slower disks.
2) dbwr gets a latch to see whether these blocks are written to the disk so it can write these blocks to the data files.
3) The sessions that are waiting on log file sync cycle thru and acquire redo allocation latch to check on the log buffer blocks are written to the redo logs or not.
These are the basic reasons why you will see redo allocation latch. Ask me if you need to know how to fix these issues.
What is log file sync ?
Sessions waiting on the return on the commit from the log buffer to guarantee recovery.
DBWR waiting on LGWR to write redo blocks to the redo buffer so dbwr could write the corresponding blocks in the datafiles.
Slower disks
Smaller log buffer.
Subscribe to:
Posts (Atom)