shared pool size boş alan
SELECT TO_NUMBER (V$PARAMETER.VALUE)VALUE, V$SGASTAT.BYTES, (V$SGASTAT.BYTES/TO_NUMBER (v$parameter.VALUE))*100
percent
FROM V$SGASTAT, v$parameter
WHERE V$SGASTAT.NAME = 'free memory'
AND v$parameter.NAME = 'shared_pool_size';
select * from v$parameter
select * from V$SGASTAT where pool like 'shared pool'
------------------------
set linesize 500
column display_value format a5
select * from v$sql_workarea_active;
select p.PGA_USED_MEM / 1024 / 1024 pga_used,
p.PGA_ALLOC_MEM / 1024 / 1024 pga_alloc,
p.PGA_FREEABLE_MEM / 1024 / 1024 pga_freeable,
s.MODULE,
s.ACTION
from v$process p, v$session s
where p.ADDR = s.PADDR
and s.MODULE = 'PGA USAGE TEST'
order by p.PGA_ALLOC_MEM desc;
--------------------------------
select * from v$statistics_level
SELECT * FROM SYS.DBA_TABLESPACE_USAGE_METRICS;
SELECT * FROM V$FILESPACE_USAGE;
SELECT STATISTICS_NAME,
SESSION_STATUS,
SYSTEM_STATUS,
ACTIVATION_LEVEL,
SESSION_SETTABLE
FROM V$STATISTICS_LEVEL
WHERE UPPER(STATISTICS_NAME) LIKE 'THRESHOLD%';
SELECT STATISTICS_NAME,
SESSION_STATUS,
SYSTEM_STATUS,
ACTIVATION_LEVEL,
SESSION_SETTABLE
FROM v$statistics_level
ORDER BY 1
/
----------------------------------
select u.TIMESTAMP,
d.last_analyzed,
u.table_name,
u.inserts,
u.updates,
u.deletes,
d.num_rows,
TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99')
percent
from all_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = d.table_name
and d.owner = 'SAPSR3' and d.table_name like 'Z%'
and (u.inserts > 1 or u.updates > 1 or u.deletes > 1)
order by d.last_analyzed
/
select * from dba_tables
select
statistic,
value
from
v$pq_sysstat
where
statistic = 'Servers Busy';
select p.server_name,
sql.sql_text
from v$px_process p, v$sql sql, v$session s
WHERE p.sid = s.sid
and p.serial# = s.serial#
and s.sql_address = sql.address
and s.sql_hash_value = sql.hash_value
select * from v$session
----------------------------------
18 Ağustos 2009 Salı
17 Ağustos 2009 Pazartesi
Sql Trace
--TRACE Kullanımı
alter system set timed_statistics=true;
alter session set max_dump_file_size=20000;
show parameter user_dump_dest
select * from v$parameter where name like '%user_dump_dest%'
SQL> alter session set SQL_TRACE true;
SQL> select MUSTERI_ADI from MUSTERI where MUSTERI_NO=1;
SQL> select * from dual;
SQL> alter session set SQL_TRACE false;
select SID, SERIAL# from v$session where username='SAPSR3'
execute dbms_system.set_sql_trace_in_session('52','2',true);
execute dbms_system.set_sql_trace_in_session('52','2',false);
--V$SQL_PLAN Kullanımı
select SID from v$session where username='SYSTEM'
select sql_id, sql_child_number from v$session where sid=79;
SQL> set pages 20000
SQL> set lines 120
SQL> column operations format a30
SQL> column object_name format a25
SQL> column options format a15
select lpad(' ', level ) || operation operations,
object_name, options, cardinality
from (select * from v$sql_plan
where sql_id='4gjjtxgzsmw13'
and child_number=0)
connect by prior id=parent_id
start with id=0
order by id, position;
select * from v$sql where SQL_ID='4gjjtxgzsmw13'
if bind;
col name format a10
col value_string format a50
select name, datatype, value_string
from v$sql_bind_capture
where sql_id='4gjjtxgzsmw13' and child_number=0
order by position;
SQL> variable A1 number;
SQL> variable A2 varchar2(20);
SQL> execute :A1 := 34;
SQL> execute :A2 := 'fer';
set autotrace trace exp stat
select count(*) from MUSTERI
Where bolge=:A1 and musteri_adi=:A2;
--AUTOTRACE Kullanımı
SET AUTOTRACE ON
Sorgu çalıştırılır, sonucu, explain plan’ı ve istatistikleri görüntülenir
SET AUTOTRACE ON EXP
Sadece explain plan görüntülenir
SET AUTOTRACE ON STAT
Sadece istatistikler görüntülenir (sorgu çalıştırılır ama sonucu görüntülenmez)
SET AUTOTRACE TRACE
Sorgunun explain plan’ı ve istatistikleri görüntülenir (sorgu çalıştırılır ama sonucu görüntülenmez)
---EXPLAIN PLAN Kullanımı
alter system set timed_statistics=true;
alter session set max_dump_file_size=20000;
show parameter user_dump_dest
select * from v$parameter where name like '%user_dump_dest%'
SQL> alter session set SQL_TRACE true;
SQL> select MUSTERI_ADI from MUSTERI where MUSTERI_NO=1;
SQL> select * from dual;
SQL> alter session set SQL_TRACE false;
select SID, SERIAL# from v$session where username='SAPSR3'
execute dbms_system.set_sql_trace_in_session('52','2',true);
execute dbms_system.set_sql_trace_in_session('52','2',false);
--V$SQL_PLAN Kullanımı
select SID from v$session where username='SYSTEM'
select sql_id, sql_child_number from v$session where sid=79;
SQL> set pages 20000
SQL> set lines 120
SQL> column operations format a30
SQL> column object_name format a25
SQL> column options format a15
select lpad(' ', level ) || operation operations,
object_name, options, cardinality
from (select * from v$sql_plan
where sql_id='4gjjtxgzsmw13'
and child_number=0)
connect by prior id=parent_id
start with id=0
order by id, position;
select * from v$sql where SQL_ID='4gjjtxgzsmw13'
if bind;
col name format a10
col value_string format a50
select name, datatype, value_string
from v$sql_bind_capture
where sql_id='4gjjtxgzsmw13' and child_number=0
order by position;
SQL> variable A1 number;
SQL> variable A2 varchar2(20);
SQL> execute :A1 := 34;
SQL> execute :A2 := 'fer';
set autotrace trace exp stat
select count(*) from MUSTERI
Where bolge=:A1 and musteri_adi=:A2;
--AUTOTRACE Kullanımı
SET AUTOTRACE ON
Sorgu çalıştırılır, sonucu, explain plan’ı ve istatistikleri görüntülenir
SET AUTOTRACE ON EXP
Sadece explain plan görüntülenir
SET AUTOTRACE ON STAT
Sadece istatistikler görüntülenir (sorgu çalıştırılır ama sonucu görüntülenmez)
SET AUTOTRACE TRACE
Sorgunun explain plan’ı ve istatistikleri görüntülenir (sorgu çalıştırılır ama sonucu görüntülenmez)
---EXPLAIN PLAN Kullanımı
Tablo Analiz
/*
SAPSR3 un Z li tablolarının dml işlem yapılmış olanlarının istatistiklerinin toplanması
*/
declare
cursor c_tablo is
select u.table_name
from all_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = d.table_name
and d.owner = 'SAPSR3' and d.table_name like 'ZPPBOY%'
and (u.inserts > 1 or u.updates > 1 or u.deletes > 1)
order by d.last_analyzed;
sayi number;
begin
for r_tablo in c_tablo
loop
dbms_output.put_line('Tablo:'||r_tablo.table_name);
dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=>r_tablo.table_name, partname=> NULL);
end loop;
end;
----------------------------------------
---tablo değişikliklerini listeleme
select u.TIMESTAMP,
d.last_analyzed,
u.table_name,
u.inserts,
u.updates,
u.deletes,
d.num_rows,
TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99')
percent
from all_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = d.table_name
and d.owner = 'SAPSR3'
and (u.inserts > 1 or u.updates > 1 or u.deletes > 1)
order by d.last_analyzed
/
------------------------
begin
dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=> 'Z_table', partname=> NULL);
end;
begin
dbms_stats.gather_schema_stats(ownname=> 'SAPSR3' , cascade=> TRUE);
end;
-------------------------
Oracle Advisory BASIC TYPICAL ALL
-------------------------------------------
Buffer Cache Advice X X
MTTR Advice X X
Shared Pool Advice X X
Segment Level Statistics X X
PGA Advice X X
Timed Statistics X X
Timed OS Statistics X
Plan Execution Statistics X
col statistics_name for a30 head "Statistics Name"
col session_status for a10 head "Session|Status"
col system_status for a10 head "System|Status"
col activation_level for a10 head "Activation|Level"
col session_settable for a10 head "Session|Settable"
SELECT STATISTICS_NAME,
SESSION_STATUS,
SYSTEM_STATUS,
ACTIVATION_LEVEL,
SESSION_SETTABLE
FROM v$statistics_level
ORDER BY 1
/
Doc ID: 252597.1
Doc ID: 255452.1
SAPSR3 un Z li tablolarının dml işlem yapılmış olanlarının istatistiklerinin toplanması
*/
declare
cursor c_tablo is
select u.table_name
from all_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = d.table_name
and d.owner = 'SAPSR3' and d.table_name like 'ZPPBOY%'
and (u.inserts > 1 or u.updates > 1 or u.deletes > 1)
order by d.last_analyzed;
sayi number;
begin
for r_tablo in c_tablo
loop
dbms_output.put_line('Tablo:'||r_tablo.table_name);
dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=>r_tablo.table_name, partname=> NULL);
end loop;
end;
----------------------------------------
---tablo değişikliklerini listeleme
select u.TIMESTAMP,
d.last_analyzed,
u.table_name,
u.inserts,
u.updates,
u.deletes,
d.num_rows,
TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99')
percent
from all_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = d.table_name
and d.owner = 'SAPSR3'
and (u.inserts > 1 or u.updates > 1 or u.deletes > 1)
order by d.last_analyzed
/
------------------------
begin
dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=> 'Z_table', partname=> NULL);
end;
begin
dbms_stats.gather_schema_stats(ownname=> 'SAPSR3' , cascade=> TRUE);
end;
-------------------------
Oracle Advisory BASIC TYPICAL ALL
-------------------------------------------
Buffer Cache Advice X X
MTTR Advice X X
Shared Pool Advice X X
Segment Level Statistics X X
PGA Advice X X
Timed Statistics X X
Timed OS Statistics X
Plan Execution Statistics X
col statistics_name for a30 head "Statistics Name"
col session_status for a10 head "Session|Status"
col system_status for a10 head "System|Status"
col activation_level for a10 head "Activation|Level"
col session_settable for a10 head "Session|Settable"
SELECT STATISTICS_NAME,
SESSION_STATUS,
SYSTEM_STATUS,
ACTIVATION_LEVEL,
SESSION_SETTABLE
FROM v$statistics_level
ORDER BY 1
/
Doc ID: 252597.1
Doc ID: 255452.1
13 Ağustos 2009 Perşembe
DATA GUARD
>shutdown immediate;
tüm database in backup ını alıyoruz.
-tnsnames.ora -primary
prim1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primaryip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim1.world)
)
)
stby1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbyip)(PORT = 1512))
)
(CONNECT_DATA =
(SERVICE_NAME = stby1.world)
)
)
---------
primary database i archive log alıyoruz.
-archive log list;
no archive log;
create pfile from spfile;
--İnit.ora’da:
--LOG_ARCHIVE_START=true -9.i için.
LOG_ARCHIVE_DEST_1='LOCATION=C:\vtyedek\archives MANDATORY REOPEN=30'
LOG_ARCHIVE_DEST_2='SERVICE=stby1 LGWR SYNC AFFIRM'
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable
LOG_ARCHIVE_FORMAT=arc_%t_%r_%s.arc
REMOTE_ARCHIVE_ENABLE=true
---------------
create spfile and
STARTUP mount
alter database archivelog;
alter database open;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\stbycf.f'; -- standby için controlfile
daha once aldıgımın yedeğin içine controlfile ı atıyoruz.
------------------------------
stndby databasei aldığımız backupı koyuyoruz.
--tnsnames stndby
stby1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stby1.world)
)
)
PRIM1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim1.world)
)
)
---------------
stndby init.ora
SERVICE_NAMES = stby1
CONTROL_FILES=standby.ctl - control file ları siliyoruz. tekrar düzenliyoruz.
LOG_ARCHIVE_START=true
LOCK_NAME_SPACE=stby1
FAL_SERVER=PRIM1
FAL_CLIENT=stby1
# Uncomment is filename conversion is needed--sid ler farklı ise
#DB_FILE_NAME_CONVERT=("/primary","/standby")
#LOG_FILE_NAME_CONVERT=("/primary","/standby")
STANDBY_ARCHIVE_DEST=C:\vtyedek\archives
LOG_ARCHIVE_DEST_1='LOCATION=C:\vtyedek\archives'
LOG_ARCHIVE_TRACE=127
LOG_ARCHIVE_FORMAT=arc_%t_%r_%s.arc
STANDBY_FILE_MANAGEMENT=auto
REMOTE_ARCHIVE_ENABLE=true
--stndby lisner.ora
SID_listener ara 12514 hatası için sid ekleyebiliriz.
--examp
(SID_DESC =
(SID_NAME = fer)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(GLOBAL_DBNAME = fer)
---
lisner ı değiştiriyorz
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 1512))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 2481))
)
)
STANDBY_LISTENER = (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=stndbyip))
)
-----------------------
lsnrctl reload
lsnrctl stop
lsnrctl start
create spfile
STARTUP NOMOUNT
ALTER DATABASE MOUNT STANDBY DATABASE;
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
primary database;
ALTER DATABASE SET STANDBY DATABASE PROTECTED; illa standby a yolla demek.
hata verdi.
primary de test table oluştur.
commit
alter system switch logfile;
SQL> -- Cancel protected mode on primary
SQL> CONNECT sys/password@primary1 AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;
SQL>
SQL> -- Cancel recovery if necessary
SQL> CONNECT sys/password@standby1 AS SYSDBA
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
-------------------
SQL> -- Startup managed recovery
SQL> CONNECT sys/password@standby1 AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> -- Protect primary database
SQL> CONNECT sys/password@primary1 AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE PROTECTED;
---------------------------------------
select log_mode,open_mode , database_role from v$database;
--------------
Activating A Standby Database
SQL> -- Cancel recovery if necessary
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
----------------
http://www.oracle-base.com/articles/9i/DataGuard.php
--------------------------
logical database;
pri-
CREATE TABLESPACE logmnrts DATAFILE ’C:\vtyedek\logmnrts.dbf’ SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
ALTER DATABASE CREATE LOGICAL STANDBY CONTROLFILE AS 'C:\stndby.ctl';
stnd-
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY new-db_name;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
ALTER DATABASE GUARD ALL;
ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
select log_mode,open_mode , database_role from v$database;
logical standby
-----------------------------
-----------------------------------------ek not;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('C:\oracle\product\10.2.0\oradata\orcl\stdbyredo1.log') SIZE 50M;
cfgadm -al
format
luxadm -e port
luxadm -e dump_map
CREATE ROLLBACK SEGMENT rbs_dummy
TABLESPACE system
STORAGE (INITIAL 10k NEXT 10k MINEXTENTS 2);
select sequence#, to_char(2009-08-14 10:00,'YYYY-MM-DD HH24:MI') as first_time,
to_char(2009-08-14 14:00,'YYYY-MM-DD HH24:MI') as next_time, applied from v$archived_log order by sequence#;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/u02/oradata/amcpudb/archive2/primary/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=amcpudb_pri' scope=both;
alter system set log_archive_dest_1='LOCATION=/u02/oradata/amcpudb/archive1/standby/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=amcpudb_pri' scope=both;
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END
FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
----------------------------------
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
------------pri
LOG_ARCHIVE_DEST_3=
'LOCATION=/arch2/chicago/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_3=ENABLE
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
-------------stand
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY new-db_name;
SQL> STARTUP MOUNT;
LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/boston/
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
'SERVICE=chicago LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_3=
'LOCATION=/arch2/boston/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
----------------------------------------------------
--EXECUTE LOGMNR_D.SET_TABLESPACE 'logical_tblsp');
ALTER DATABASE GUARD ALL;
ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
select log_mode,open_mode , database_role from v$database;
alter database add supplemental log data
( primary key, unique index) columns;
ed
SELECT SUPPLEMENTAL_LOG_DATA_PK AS PK_LOG,
SUPPLEMENTAL_LOG_DATA_UI AS UI_LOG
* FROM V$DATABASE
select tablespace_name from dba_tablespaces
where contents = 'TEMPORARY'
select sequence#, first_time, next_time, dict_begin,dict_end
from dba_logstdby_log
order by 1;
select owner,table_name from dba_logstdby_not_unique
where (owner,table_name) not in
(select distinct owner,table_name from dba_logstdby_unsupported)
and bad_column='Y';
LOG_ARCHIVE_DEST_3=
'LOCATION=C:\vtyedek\archives
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=PRIM1'
LOG_ARCHIVE_DEST_STATE_3=ENABLE
alter system set UNDO_RETENTION=3600 scope=both;
------------------------
pri-
ALTER DATABASE FORCE LOGGING
SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER,TABLE_NAME;
SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS';
CREATE TABLESPACE logmnrts DATAFILE ’C:\vtyedek\logmnrts.dbf’ SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
ALTER DATABASE CREATE logical STANDBY CONTROLFILE AS 'c:\stbycf.f';
select guard_status from v$database
alter database guard none;
select max(sequence#) from v$log_history;
SELECT SEQUENCE#, FIRST_TIME, APPLIED
FROM DBA_LOGSTDBY_LOG
ORDER BY SEQUENCE#;
http://forums.oracle.com/forums/thread.jspa?messageID=3666921
https://metalink.oracle.com/CSP/ui/flash.html#tab=KBHome%28page=KBHome&id=%28%29%29,%28page=KBNavigator&id=%28bmDocID=404686.1&viewingMode=1143&from=BOOKMARK&bmDocType=PROBLEM&bmDocDsrc=KB&bmDocTitle=ORA-01031%20ORA-06512%20%3Cb%3EORA-16224%3C/b%3E%20EXP-00083%20While%20Doing%20Export%29%29
http://www.itpub.net/viewthread.php?tid=1200984
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10823/scenarios.htm
http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10823/manage_ls.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm
tüm database in backup ını alıyoruz.
-tnsnames.ora -primary
prim1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primaryip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim1.world)
)
)
stby1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbyip)(PORT = 1512))
)
(CONNECT_DATA =
(SERVICE_NAME = stby1.world)
)
)
---------
primary database i archive log alıyoruz.
-archive log list;
no archive log;
create pfile from spfile;
--İnit.ora’da:
--LOG_ARCHIVE_START=true -9.i için.
LOG_ARCHIVE_DEST_1='LOCATION=C:\vtyedek\archives MANDATORY REOPEN=30'
LOG_ARCHIVE_DEST_2='SERVICE=stby1 LGWR SYNC AFFIRM'
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable
LOG_ARCHIVE_FORMAT=arc_%t_%r_%s.arc
REMOTE_ARCHIVE_ENABLE=true
---------------
create spfile and
STARTUP mount
alter database archivelog;
alter database open;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\stbycf.f'; -- standby için controlfile
daha once aldıgımın yedeğin içine controlfile ı atıyoruz.
------------------------------
stndby databasei aldığımız backupı koyuyoruz.
--tnsnames stndby
stby1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stby1.world)
)
)
PRIM1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim1.world)
)
)
---------------
stndby init.ora
SERVICE_NAMES = stby1
CONTROL_FILES=standby.ctl - control file ları siliyoruz. tekrar düzenliyoruz.
LOG_ARCHIVE_START=true
LOCK_NAME_SPACE=stby1
FAL_SERVER=PRIM1
FAL_CLIENT=stby1
# Uncomment is filename conversion is needed--sid ler farklı ise
#DB_FILE_NAME_CONVERT=("/primary","/standby")
#LOG_FILE_NAME_CONVERT=("/primary","/standby")
STANDBY_ARCHIVE_DEST=C:\vtyedek\archives
LOG_ARCHIVE_DEST_1='LOCATION=C:\vtyedek\archives'
LOG_ARCHIVE_TRACE=127
LOG_ARCHIVE_FORMAT=arc_%t_%r_%s.arc
STANDBY_FILE_MANAGEMENT=auto
REMOTE_ARCHIVE_ENABLE=true
--stndby lisner.ora
SID_listener ara 12514 hatası için sid ekleyebiliriz.
--examp
(SID_DESC =
(SID_NAME = fer)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(GLOBAL_DBNAME = fer)
---
lisner ı değiştiriyorz
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 1512))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = stndbyip)(PORT = 2481))
)
)
STANDBY_LISTENER = (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=stndbyip))
)
-----------------------
lsnrctl reload
lsnrctl stop
lsnrctl start
create spfile
STARTUP NOMOUNT
ALTER DATABASE MOUNT STANDBY DATABASE;
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
primary database;
ALTER DATABASE SET STANDBY DATABASE PROTECTED; illa standby a yolla demek.
hata verdi.
primary de test table oluştur.
commit
alter system switch logfile;
SQL> -- Cancel protected mode on primary
SQL> CONNECT sys/password@primary1 AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;
SQL>
SQL> -- Cancel recovery if necessary
SQL> CONNECT sys/password@standby1 AS SYSDBA
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
-------------------
SQL> -- Startup managed recovery
SQL> CONNECT sys/password@standby1 AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> -- Protect primary database
SQL> CONNECT sys/password@primary1 AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE PROTECTED;
---------------------------------------
select log_mode,open_mode , database_role from v$database;
--------------
Activating A Standby Database
SQL> -- Cancel recovery if necessary
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
----------------
http://www.oracle-base.com/articles/9i/DataGuard.php
--------------------------
logical database;
pri-
CREATE TABLESPACE logmnrts DATAFILE ’C:\vtyedek\logmnrts.dbf’ SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
ALTER DATABASE CREATE LOGICAL STANDBY CONTROLFILE AS 'C:\stndby.ctl';
stnd-
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY new-db_name;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
ALTER DATABASE GUARD ALL;
ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
select log_mode,open_mode , database_role from v$database;
logical standby
-----------------------------
-----------------------------------------ek not;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('C:\oracle\product\10.2.0\oradata\orcl\stdbyredo1.log') SIZE 50M;
cfgadm -al
format
luxadm -e port
luxadm -e dump_map
CREATE ROLLBACK SEGMENT rbs_dummy
TABLESPACE system
STORAGE (INITIAL 10k NEXT 10k MINEXTENTS 2);
select sequence#, to_char(2009-08-14 10:00,'YYYY-MM-DD HH24:MI') as first_time,
to_char(2009-08-14 14:00,'YYYY-MM-DD HH24:MI') as next_time, applied from v$archived_log order by sequence#;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/u02/oradata/amcpudb/archive2/primary/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=amcpudb_pri' scope=both;
alter system set log_archive_dest_1='LOCATION=/u02/oradata/amcpudb/archive1/standby/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=amcpudb_pri' scope=both;
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END
FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
----------------------------------
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
------------pri
LOG_ARCHIVE_DEST_3=
'LOCATION=/arch2/chicago/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_3=ENABLE
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
-------------stand
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY new-db_name;
SQL> STARTUP MOUNT;
LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/boston/
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
'SERVICE=chicago LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_3=
'LOCATION=/arch2/boston/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
----------------------------------------------------
--EXECUTE LOGMNR_D.SET_TABLESPACE 'logical_tblsp');
ALTER DATABASE GUARD ALL;
ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
select log_mode,open_mode , database_role from v$database;
alter database add supplemental log data
( primary key, unique index) columns;
ed
SELECT SUPPLEMENTAL_LOG_DATA_PK AS PK_LOG,
SUPPLEMENTAL_LOG_DATA_UI AS UI_LOG
* FROM V$DATABASE
select tablespace_name from dba_tablespaces
where contents = 'TEMPORARY'
select sequence#, first_time, next_time, dict_begin,dict_end
from dba_logstdby_log
order by 1;
select owner,table_name from dba_logstdby_not_unique
where (owner,table_name) not in
(select distinct owner,table_name from dba_logstdby_unsupported)
and bad_column='Y';
LOG_ARCHIVE_DEST_3=
'LOCATION=C:\vtyedek\archives
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=PRIM1'
LOG_ARCHIVE_DEST_STATE_3=ENABLE
alter system set UNDO_RETENTION=3600 scope=both;
------------------------
pri-
ALTER DATABASE FORCE LOGGING
SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER,TABLE_NAME;
SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS';
CREATE TABLESPACE logmnrts DATAFILE ’C:\vtyedek\logmnrts.dbf’ SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
ALTER DATABASE CREATE logical STANDBY CONTROLFILE AS 'c:\stbycf.f';
select guard_status from v$database
alter database guard none;
select max(sequence#) from v$log_history;
SELECT SEQUENCE#, FIRST_TIME, APPLIED
FROM DBA_LOGSTDBY_LOG
ORDER BY SEQUENCE#;
http://forums.oracle.com/forums/thread.jspa?messageID=3666921
https://metalink.oracle.com/CSP/ui/flash.html#tab=KBHome%28page=KBHome&id=%28%29%29,%28page=KBNavigator&id=%28bmDocID=404686.1&viewingMode=1143&from=BOOKMARK&bmDocType=PROBLEM&bmDocDsrc=KB&bmDocTitle=ORA-01031%20ORA-06512%20%3Cb%3EORA-16224%3C/b%3E%20EXP-00083%20While%20Doing%20Export%29%29
http://www.itpub.net/viewthread.php?tid=1200984
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10823/scenarios.htm
http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10823/manage_ls.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm
12 Ağustos 2009 Çarşamba
RMAN errors
RMAN>shutdown abort;
RMAN>startup nomount;
RMAN>restore database;
ORA-01507
RMAN>alter database mount;
RMAN>restore database;
RMAN>recover database;
RMAN-00571
RMAN-00569
RMAN-00571
RMAN-03002
RMAN-06053 onemli
RMAN-06025
RMAN-06053 için;
RMAN>shutdown abort;
RMAN>startup nomount;
RMAN>restore controlfile;
RMAN-06563 -- Doc ID: 291432.1 --Note 22080.1
RMAN>show all;
tum parametreler default olmuş
RMAN>alter database mount;
RMAN>alter database open;
--
RMAN target /
RMAN>startup nomount
RMAN> run {
> allocate channel t1 type DISK
> restore controlfile from autobackup;
> sql 'alter database mount';
> restore database;
> switch datafile all;
> recover database;
> alter database open resetlogs;
> }
http://www.orafaq.com/forum/t/141370/2/
-----------------------------------
cold backup
>startup;
ORA-01157
ORA-01110
RMAN>startup nomount;
RMAN>restore database;
ORA-01507
RMAN>alter database mount;
RMAN>restore database;
RMAN>recover database;
RMAN-00571
RMAN-00569
RMAN-00571
RMAN-03002
RMAN-06053 onemli
RMAN-06025
RMAN-06053 için;
RMAN>shutdown abort;
RMAN>startup nomount;
RMAN>restore controlfile;
RMAN-06563 -- Doc ID: 291432.1 --Note 22080.1
RMAN>show all;
tum parametreler default olmuş
RMAN>alter database mount;
RMAN>alter database open;
--
RMAN target /
RMAN>startup nomount
RMAN> run {
> allocate channel t1 type DISK
> restore controlfile from autobackup;
> sql 'alter database mount';
> restore database;
> switch datafile all;
> recover database;
> alter database open resetlogs;
> }
http://www.orafaq.com/forum/t/141370/2/
-----------------------------------
cold backup
>startup;
ORA-01157
ORA-01110
11 Ağustos 2009 Salı
ORA-00600: internal error code, arguments: [kqlfFillBindData:1]
http://updates.oracle.com/ARULink/PatchDetails/process_form?aru=9130568&patch_password=&no_header=0
Subject: ORA-00600[kqlfFillBindData:1] Raised
Doc ID: 428018.1 Type: PROBLEM
Modified Date: 23-MAY-2008 Status: MODERATED
In this Document
Symptoms
Cause
Solution
References
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.2.0
This problem can occur on any platform.
Symptoms
The following errors are seen in the alert log:
ORA-00600: internal error code, arguments: [kqlfFillBindData:1]
The following errors may also occur:
ORA-00600: internal error code, arguments: [729]
ORA-00600: internal error code, arguments: [1234]
The stack trace may look like the following:
ksedst ksedmp ksfdmp kgerinv kgeasnmierr kqlfFillBindData kqlffc kqlfgo kgligo
Cause
This issue is caused by Bug 5638146 ORA-600[KQLFFILLBINDDATA:1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2.
Solution
To resolve this issue, you may choose any one of these options:
1. Apply the 10.2.0.4 patchset which was not available at the time of this writing (Apr 2007).
2. Check MetaLink for backport Patch 5638146 for your operating system and database release.
3. Use the workaround:
Use a smaller number of bind variables in the query.
References
Bug 5638146 - ORA-600[KQLFFILLBINDDATA:1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2
------------------------------------------
SQL ;
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kqlfFillBindData:1], [669], [468], [], [], [], [], []
Current SQL statement for this session:
insert into wrh$_sqlstat (snap_id, dbid, instance_number, sql_id, plan_hash_value, optimizer_cost, optimizer_mode, optimizer_env_hash_value, sharable_mem, loaded_versions, version_count, module, action, sql_profile, force_matching_signature, parsing_schema_id, parsing_schema_name, fetches_total, fetches_delta, end_of_fetch_count_total, end_of_fetch_count_delta, sorts_total, sorts_delta, executions_total, executions_delta, px_servers_execs_total, px_servers_execs_delta, loads_total, loads_delta, invalidations_total, invalidations_delta, parse_calls_total, parse_calls_delta, disk_reads_total, disk_reads_delta, buffer_gets_total, buffer_gets_delta, rows_processed_total, rows_processed_delta, cpu_time_total, cpu_time_delta, elapsed_time_total, elapsed_time_delta, iowait_total, iowait_delta, clwait_total, clwait_delta, apwait_total, apwait_delta, ccwait_total, ccwait_delta, direct_writes_total, direct_writes_delta, plsexec_time_total, plsexec_time_delta, javexec_time_total, javexec_time_delta, bind_data, flag) SELECT /*+ ordered use_nl(sql) index(sql kglobt03) */ :snap_id, :dbid, :instance_number, kglobt03, kglobt30, kglobtn0, decode(kglobt32, 0, 'NONE', 1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE', 4, 'CHOOSE', 'UNKNOWN'), kglobcceh, kglobhs0+kglobhs1+kglobhs2+kglobhs3+ kglobhs4+kglobhs5+kglobhs6, kglobclc, kglobccc, kglobts0, kglobts1, kglobts3, kglobt49, kglobt18, kglobts4, kglobt04, kglobdft, kglobt35, kglobdef, kglobt01, kglobdso, kglobt05, kglobdex, kglobt48, kglobdpx, kglhdldc, kglobdld, kglhdivc, kglobdiv, kglobt12, kglobdps, kglobt13, kglobddr, kglobt14, kglobdbf, kglobt15, kglobdro, kglobt06, kglobdcp, kglobt07, kglobdel, kglobwui, kglobdui, kglobwcl, kglobdcl, kglobwap, kglobdap, kglobwcc, kglobdcc, kglobwdw, kglobddw, kglobt42, kglobdpl, kglobt43, kglobdjv, kglobcbca, NULL FROM X$KEWRSQLIDTAB sie, X$KGLCURSOR_CHILD_SQLIDPH sql WHERE sie.sqlid_kewrsie = sql.kglobt03
----- Call Stack Trace -----
---------------------------------
Bug No: 5638146
Filed 01-NOV-2006 Updated 01-APR-2009
Product Oracle Server - Enterprise Edition Product Version 10.2.0.2.0
Platform AIX5L Based Systems (64-bit) Platform Version No Data
Database Version 10.2.0.2.0 Affects Platforms Generic
Severity Severe Loss of Service Status Development to Q/A
Base Bug N/A Fixed in Product Version 11.1.0.0
Problem statement:
ORA-600[KQLFFILLBINDDATA:1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2
*** 11/01/06 03:38 pm ***
PLEASE SEE TESTCASE AT *** RSPOWELL 02/19/07 05:12 am ***
*** 11/08/06 04:43 pm *** (CHG: Sta->16)
*** 11/08/06 04:43 pm ***
*** 11/09/06 03:05 am *** (CHG: Asg->NEW OWNER)
*** 11/09/06 03:39 am *** (CHG: Sta->10)
*** 11/09/06 03:39 am ***
*** 12/01/06 03:53 pm *** (CHG: Sta->16)
*** 12/01/06 03:53 pm ***
*** 12/01/06 03:56 pm ***
*** 12/04/06 01:12 am *** (CHG: Sta->10)
*** 12/04/06 01:12 am ***
The CTAS has to be taken when the problem ORA-600 occurs
so we can look at the content in relation to the trace
to see if we can see what may have caused the problem.
Do you have the trace matching the time when this CTAS
was done so we can try to cross reference.
*** 12/19/06 03:57 pm *** (CHG: Sta->16)
*** 12/19/06 03:57 pm ***
*** 12/20/06 03:42 am *** (CHG: Sta->10)
*** 12/20/06 03:42 am ***
*** 01/19/07 10:31 am *** (CHG: Sta->16)
*** 01/19/07 10:31 am ***
*** 01/22/07 09:41 am *** (CHG: Sta->10)
*** 01/22/07 09:41 am ***
*** 01/22/07 02:25 pm *** (CHG: Sta->16)
*** 01/22/07 02:25 pm ***
*** 01/23/07 02:37 am *** (CHG: Sta->10)
*** 01/23/07 02:37 am ***
I suspect "c" may raise questions. Something like
this may help catch the bad SQL_ID:
.
drop table xx_bug;
create table xx_bug (
SQL_ID VARCHAR2(13),
ADDR RAW(8)
);
declare
bnd raw(2000);
begin
for R in (select addr, KGLOBT03 from X$KGLCURSOR_CHILD_SQLIDPH)
loop
insert into xx_bug values(R.KGLOBT03, R.addr);
commit;
begin
-- See if this SQL_ID bind info throws ORA-600
select max(KGLOBCBCA) into bnd from X$KGLCURSOR_CHILD_SQLIDPH
where KGLOBT03=R.KGLOBT03;
exception
when no_data_found then
null; -- This is ok as row gone now
end;
-- If we got here we did not ORA-600. Delete the row.
delete from xx_bug;
commit;
end loop;
end;
/
set pages 1000
select * from xx_bug;
select * from X$KGLCURSOR_CHILD_SQLIDPH where KGLOBT03='&id';
.
.
This will scan X$KGLCURSOR_CHILD_SQLIDPH and for each
row insert the SQL_ID into xx_bug. It then tries to get
the bind info for that X$KGLCURSOR_CHILD_SQLIDPH sql_id .
If it succeeds this is not the bad row so it deletes from
xx_bug. If the row has gone it deletes the xx_bug row.
If the row throws ORA-600 the SQL_ID will be left in
xx_bug.
.
The select after the PLSQL block should confirm if that
is the problem SQL_ID. As much info as possible about that
SQL_ID would then be helpful, including all the events+dumps
listed.
.
*** 02/16/07 03:30 pm *** (CHG: Sta->16)
*** 02/16/07 03:30 pm ***
*** 02/19/07 05:12 am *** (CHG: Sta->11 SubComp->LIB CACHE)
*** 02/19/07 05:12 am ***
.
BDE Screening
~~~~~~~~~~~~~
Testcase
~~~~~~~~~~~~~~~~~
.
Files: BDETC.tar.Z (containing setup.sql, tc.sql)
.
Steps:
sqlplus /nolog @setup
Create a user TC, a table TST and then issues a SQL
statement which uses a large number of bind variables
(5480 binds) . The statement is run few times to ensure it
is cached in the SGA.
.
sqlplus /nolog @tc
Runs a select of KGLOBCBCA from X$KGLCURSOR_CHILD_SQLIDPH
^
ORA-600 [kqlfFillBindData:1], [1200], [636]
.
Reproduced
~~~~~~~~~~
Reproduced in 10.2.0.2
Reproduced in RDBMS_MAIN_LINUX_070216
.
.
Workaround/s
~~~~~~~~~~~~
Do not use > 5461 bind variables in a single SQL
.
.
*** 02/19/07 05:12 am *** (CHG: Asg->NEW OWNER)
*** 02/19/07 05:12 am ***
*** 02/21/07 05:00 am *** (CHG: DevPri->2)
*** 02/21/07 05:00 am *** (CHG: Confirmed Flag->Y)
*** 02/21/07 05:00 am *** (CHG: Asg->NEW OWNER)
*** 02/21/07 05:00 am ***
*** 02/21/07 05:00 am *** (CHG: Asg->NEW OWNER)
*** 02/23/07 07:00 am *** ESCALATED
*** 02/23/07 07:04 am ***
*** 02/24/07 08:36 am *** (CHG: Asg->NEW OWNER)
*** 02/24/07 08:36 am ***
*** 03/02/07 03:14 pm ***
*** 03/07/07 06:18 pm ***
*** 03/20/07 05:44 am ***
*** 03/20/07 09:37 am ***
*** 03/28/07 10:22 am ***
*** 04/03/07 07:49 am ***
*** 04/04/07 06:40 pm ***
REDISCOVERY INFORMATION:
If 'ORA-00600 [kqlfFillBindData:1]' is raised and there are
thousands of bind variables in the SQL statement, then you
may be encountering this particular problem.
WORKAROUND:
Try to reduce the number of bind variables.
RELEASE NOTES:
]]'ORA-00600 [kqlfFillBindData:1]' is no longer raised against
]]a SQL statement containing thousands of bind variables.
*** 04/04/07 06:47 pm *** (CHG: Fixed->11.1.0.0)
*** 04/04/07 06:47 pm *** (CHG: Sta->80)
*** 04/05/07 06:14 am ***
*** 04/05/07 08:43 am ***
*** 04/05/07 12:58 pm ***
*** 04/05/07 01:25 pm ***
*** 04/05/07 01:26 pm ***
*** 04/05/07 01:26 pm ***
*** 04/06/07 04:42 am ***
*** 04/06/07 04:04 pm ***
*** 04/08/07 11:28 am ***
*** 04/09/07 08:58 am ***
*** 04/09/07 03:19 pm ***
*** 04/10/07 07:09 am ***
*** 04/10/07 01:17 pm ***
*** 04/11/07 03:11 pm ***
*** 04/12/07 01:53 pm ***
*** 04/12/07 07:30 pm ***
*** 04/13/07 10:30 am ***
*** 04/15/07 04:31 pm ***
*** 04/15/07 04:32 pm ***
*** 04/15/07 04:32 pm ***
*** 04/15/07 04:34 pm ***
*** 04/15/07 04:35 pm ***
*** 04/15/07 04:36 pm ***
*** 04/15/07 04:36 pm ***
*** 04/15/07 04:37 pm ***
*** 04/15/07 04:37 pm ***
*** 04/16/07 07:22 am ***
*** 04/18/07 02:24 am ***
*** 04/18/07 03:50 am ***
*** 04/19/07 12:20 am ***
*** 04/19/07 04:22 am ***
*** 04/19/07 05:18 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:22 am ***
*** 04/19/07 05:28 am ***
*** 04/19/07 10:51 am ***
*** 04/23/07 02:50 am ***
*** 04/23/07 02:52 am ***
*** 04/24/07 05:59 am ***
*** 05/14/07 03:21 pm ***
*** 05/16/07 03:10 am ***
*** 05/16/07 11:27 pm ***
*** 05/18/07 03:26 am ***
*** 05/18/07 03:28 am ***
*** 05/18/07 04:02 am ***
*** 05/18/07 04:04 am ***
*** 05/18/07 04:13 am ***
*** 05/18/07 06:31 am ***
*** 05/25/07 06:54 pm ***
*** 05/29/07 07:47 pm ***
*** 06/01/07 01:33 pm ***
bug 5638146
*** 06/26/07 03:22 am ***
*** 06/28/07 01:08 am ***
*** 06/28/07 01:18 am ***
*** 07/30/07 02:02 am ***
*** 08/08/07 01:07 pm ***
*** 08/08/07 01:08 pm ***
*** 08/23/07 11:59 am ***
*** 08/29/07 02:08 am ***
*** 08/30/07 12:18 pm ***
*** 08/30/07 12:18 pm ***
*** 09/01/07 03:16 am ***
*** 09/07/07 07:05 am ***
*** 09/07/07 07:07 am ***
*** 10/26/07 01:01 am ***
*** 11/09/07 03:08 am ***
*** 11/09/07 03:20 am ***
*** 11/09/07 12:07 pm ***
*** 11/09/07 12:09 pm ***
*** 11/09/07 09:29 pm ***
*** 11/09/07 09:31 pm ***
*** 11/13/07 01:50 pm ***
*** 11/18/07 03:25 pm ***
*** 11/19/07 11:48 pm ***
*** 11/19/07 11:50 pm ***
*** 05/07/08 01:17 am ***
*** 05/07/08 02:42 am ***
*** 05/07/08 02:42 am ***
*** 12/17/08 04:42 pm ***
*** 12/24/08 05:19 am ***
*** 03/17/09 03:25 pm ***
*** 03/17/09 05:57 pm ***
*** 04/01/09 11:08 am ***
.
----------------------------------
README for 5638146
Patch Details
#
# WARNING: Failure to carefully read and understand these requirements may
# result in your applying a patch that can cause your Oracle Server to
# malfunction, including interruption of service and/or loss of data.
#
# If you do not meet all of the following requirements, please log an
# iTAR, so that an Oracle Support Analyst may review your situation. The
# Oracle analyst will help you determine if this patch is suitable for you
# to apply to your system. We recommend that you avoid applying any
# temporary patch unless directed by an Oracle Support Analyst who has
# reviewed your system and determined that it is applicable.
#
# Requirements:
#
# - You must have located this patch via a Bug Database entry
# and have the exact symptoms described in the bug entry.
#
# - Your system configuration (Oracle Server version and patch
# level, OS Version) must exactly match those in the bug
# database entry - You must have NO OTHER PATCHES installed on
# your Oracle Server since the latest patch set (or base release
# x.y.z if you have no patch sets installed).
#
# - [Oracle 9.2.0.2 & above] You must have Perl 5.00503 (or later)
# installed under the ORACLE_HOME, or elsewhere within the host
# environment. OPatch is no longer included in patches as of 9.2.0.2.
# Refer to the following link for details on Perl and OPatch:
# http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=189489.1
#
# - [IBM AIX O/S & Java patches for Oracle 9.2]
# In order to apply java class updates to IBM AIX based systems using
# java_131, you must update your java if you are running a version prior
# to Service Refresh build date 20030630a. This is
# necessary to fix IBM Defect#60472.
#
# To identify which java build date you are on, enter the following
# command ;
#
# > $ORACLE_HOME/jdk/bin/java -fullversion
# ... example response ...
# java full version "J2RE 1.3.1 IBM AIX build ca131-20030630a"
#
# The string ends in the date format YYYYMMDD or YYYYMMDDa where 'a'
# indicates an updated release to the original build. You should always
# apply the latest AIX Java SDK 1.3.1 Service Update available from IBM.
# As a minimum, the above service refresh can be found under
# APAR IY47055. The signature for the updated JVM is ca131-20030630a.
# Information on the latest available fixes, as well as how to apply
# the APARs to your AIX systems, is available at the IBM Java site.
#
# If you are running AIX 5L, you can safely ignore any comment against
# the APAR that says (AIXV43 only). The APAR is applicable to
# both AIX 4.3 and AIX 5L.
#
# Once you have updated your java installation you need to copy these
# updated files to Oracle's copies in $ORACLE_HOME/jdk.
# As the Oracle owner, simply issue the following commands;
#
# > cd /usr/java131
# > cp -fpR * $ORACLE_HOME/jdk
#
#
# If you do NOT meet these requirements, or are not certain that you meet
# these requirements, please log an iTAR requesting assistance with this
# patch and Support will make a determination about whether you should
# apply this patch.
#
#-------------------------------------------------------------------------
# Interim Patch for Base Bugs: 5638146
#-------------------------------------------------------------------------
#
# DATE: Wed Apr 18 22:32:51 2007
# -------------------------------
# Platform Patch for : AIX5L Based Systems (64-bit)
# Product Version # : 10.2.0.2
# Product Patched : RDBMS
#
# Bugs Fixed by this patch:
# -------------------------
# 5638146:ORA-600[KQLFFILLBINDDATA 1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2
#
# Patch Installation Instructions:
# --------------------------------
# To apply the patch, unzip the PSE container file:
#
# p5638146_10202_AIX64-5L.zip
#
# Set your current directory to the directory where the patch
# is located:
#
# % cd 5638146
# On AIX platforms only for 10201 Patches :
#
# Ensure environment variable OBJECT_MODE is set to 32_64
# e.g.
#
# % setenv OBJECT_MODE 32_64
# or
# $ export OBJECT_MODE=32_64
#
# Ensure that the directory containing the opatch script appears in
# your $PATH; then enter the following command:
#
# % opatch apply
#
# Patch Special Instructions:
# ---------------------------
# Make sure all instances running under the ORACLE_HOME being patched
# are cleanly shutdown before installing this patch. Also ensure that
# the tool used to terminate the instance(s) has exited cleanly.
#
# If the Oracle inventory is not setup correctly this utility will
# fail. To check accessibility to the inventory you can use the
# command
#
# % opatch lsinventory
#
# If you have any problems installing this PSE or are not sure
# about inventory setup please call Oracle support.
#
# Patch Deinstallation Instructions:
# ----------------------------------
# Use the following command:
#
# % cd 5638146
# % opatch rollback -id 5638146
#
----------------------------------
ora600_sapnote_0001033315
Bugs-5638146
Subject: ORA-00600[kqlfFillBindData:1] Raised
Doc ID: 428018.1 Type: PROBLEM
Modified Date: 23-MAY-2008 Status: MODERATED
In this Document
Symptoms
Cause
Solution
References
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.2.0
This problem can occur on any platform.
Symptoms
The following errors are seen in the alert log:
ORA-00600: internal error code, arguments: [kqlfFillBindData:1]
The following errors may also occur:
ORA-00600: internal error code, arguments: [729]
ORA-00600: internal error code, arguments: [1234]
The stack trace may look like the following:
ksedst ksedmp ksfdmp kgerinv kgeasnmierr kqlfFillBindData kqlffc kqlfgo kgligo
Cause
This issue is caused by Bug 5638146 ORA-600[KQLFFILLBINDDATA:1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2.
Solution
To resolve this issue, you may choose any one of these options:
1. Apply the 10.2.0.4 patchset which was not available at the time of this writing (Apr 2007).
2. Check MetaLink for backport Patch 5638146 for your operating system and database release.
3. Use the workaround:
Use a smaller number of bind variables in the query.
References
Bug 5638146 - ORA-600[KQLFFILLBINDDATA:1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2
------------------------------------------
SQL ;
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kqlfFillBindData:1], [669], [468], [], [], [], [], []
Current SQL statement for this session:
insert into wrh$_sqlstat (snap_id, dbid, instance_number, sql_id, plan_hash_value, optimizer_cost, optimizer_mode, optimizer_env_hash_value, sharable_mem, loaded_versions, version_count, module, action, sql_profile, force_matching_signature, parsing_schema_id, parsing_schema_name, fetches_total, fetches_delta, end_of_fetch_count_total, end_of_fetch_count_delta, sorts_total, sorts_delta, executions_total, executions_delta, px_servers_execs_total, px_servers_execs_delta, loads_total, loads_delta, invalidations_total, invalidations_delta, parse_calls_total, parse_calls_delta, disk_reads_total, disk_reads_delta, buffer_gets_total, buffer_gets_delta, rows_processed_total, rows_processed_delta, cpu_time_total, cpu_time_delta, elapsed_time_total, elapsed_time_delta, iowait_total, iowait_delta, clwait_total, clwait_delta, apwait_total, apwait_delta, ccwait_total, ccwait_delta, direct_writes_total, direct_writes_delta, plsexec_time_total, plsexec_time_delta, javexec_time_total, javexec_time_delta, bind_data, flag) SELECT /*+ ordered use_nl(sql) index(sql kglobt03) */ :snap_id, :dbid, :instance_number, kglobt03, kglobt30, kglobtn0, decode(kglobt32, 0, 'NONE', 1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE', 4, 'CHOOSE', 'UNKNOWN'), kglobcceh, kglobhs0+kglobhs1+kglobhs2+kglobhs3+ kglobhs4+kglobhs5+kglobhs6, kglobclc, kglobccc, kglobts0, kglobts1, kglobts3, kglobt49, kglobt18, kglobts4, kglobt04, kglobdft, kglobt35, kglobdef, kglobt01, kglobdso, kglobt05, kglobdex, kglobt48, kglobdpx, kglhdldc, kglobdld, kglhdivc, kglobdiv, kglobt12, kglobdps, kglobt13, kglobddr, kglobt14, kglobdbf, kglobt15, kglobdro, kglobt06, kglobdcp, kglobt07, kglobdel, kglobwui, kglobdui, kglobwcl, kglobdcl, kglobwap, kglobdap, kglobwcc, kglobdcc, kglobwdw, kglobddw, kglobt42, kglobdpl, kglobt43, kglobdjv, kglobcbca, NULL FROM X$KEWRSQLIDTAB sie, X$KGLCURSOR_CHILD_SQLIDPH sql WHERE sie.sqlid_kewrsie = sql.kglobt03
----- Call Stack Trace -----
---------------------------------
Bug No: 5638146
Filed 01-NOV-2006 Updated 01-APR-2009
Product Oracle Server - Enterprise Edition Product Version 10.2.0.2.0
Platform AIX5L Based Systems (64-bit) Platform Version No Data
Database Version 10.2.0.2.0 Affects Platforms Generic
Severity Severe Loss of Service Status Development to Q/A
Base Bug N/A Fixed in Product Version 11.1.0.0
Problem statement:
ORA-600[KQLFFILLBINDDATA:1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2
*** 11/01/06 03:38 pm ***
PLEASE SEE TESTCASE AT *** RSPOWELL 02/19/07 05:12 am ***
*** 11/08/06 04:43 pm *** (CHG: Sta->16)
*** 11/08/06 04:43 pm ***
*** 11/09/06 03:05 am *** (CHG: Asg->NEW OWNER)
*** 11/09/06 03:39 am *** (CHG: Sta->10)
*** 11/09/06 03:39 am ***
*** 12/01/06 03:53 pm *** (CHG: Sta->16)
*** 12/01/06 03:53 pm ***
*** 12/01/06 03:56 pm ***
*** 12/04/06 01:12 am *** (CHG: Sta->10)
*** 12/04/06 01:12 am ***
The CTAS has to be taken when the problem ORA-600 occurs
so we can look at the content in relation to the trace
to see if we can see what may have caused the problem.
Do you have the trace matching the time when this CTAS
was done so we can try to cross reference.
*** 12/19/06 03:57 pm *** (CHG: Sta->16)
*** 12/19/06 03:57 pm ***
*** 12/20/06 03:42 am *** (CHG: Sta->10)
*** 12/20/06 03:42 am ***
*** 01/19/07 10:31 am *** (CHG: Sta->16)
*** 01/19/07 10:31 am ***
*** 01/22/07 09:41 am *** (CHG: Sta->10)
*** 01/22/07 09:41 am ***
*** 01/22/07 02:25 pm *** (CHG: Sta->16)
*** 01/22/07 02:25 pm ***
*** 01/23/07 02:37 am *** (CHG: Sta->10)
*** 01/23/07 02:37 am ***
I suspect "c" may raise questions. Something like
this may help catch the bad SQL_ID:
.
drop table xx_bug;
create table xx_bug (
SQL_ID VARCHAR2(13),
ADDR RAW(8)
);
declare
bnd raw(2000);
begin
for R in (select addr, KGLOBT03 from X$KGLCURSOR_CHILD_SQLIDPH)
loop
insert into xx_bug values(R.KGLOBT03, R.addr);
commit;
begin
-- See if this SQL_ID bind info throws ORA-600
select max(KGLOBCBCA) into bnd from X$KGLCURSOR_CHILD_SQLIDPH
where KGLOBT03=R.KGLOBT03;
exception
when no_data_found then
null; -- This is ok as row gone now
end;
-- If we got here we did not ORA-600. Delete the row.
delete from xx_bug;
commit;
end loop;
end;
/
set pages 1000
select * from xx_bug;
select * from X$KGLCURSOR_CHILD_SQLIDPH where KGLOBT03='&id';
.
.
This will scan X$KGLCURSOR_CHILD_SQLIDPH and for each
row insert the SQL_ID into xx_bug. It then tries to get
the bind info for that X$KGLCURSOR_CHILD_SQLIDPH sql_id .
If it succeeds this is not the bad row so it deletes from
xx_bug. If the row has gone it deletes the xx_bug row.
If the row throws ORA-600 the SQL_ID will be left in
xx_bug.
.
The select after the PLSQL block should confirm if that
is the problem SQL_ID. As much info as possible about that
SQL_ID would then be helpful, including all the events+dumps
listed.
.
*** 02/16/07 03:30 pm *** (CHG: Sta->16)
*** 02/16/07 03:30 pm ***
*** 02/19/07 05:12 am *** (CHG: Sta->11 SubComp->LIB CACHE)
*** 02/19/07 05:12 am ***
.
BDE Screening
~~~~~~~~~~~~~
Testcase
~~~~~~~~~~~~~~~~~
.
Files: BDETC.tar.Z (containing setup.sql, tc.sql)
.
Steps:
sqlplus /nolog @setup
Create a user TC, a table TST and then issues a SQL
statement which uses a large number of bind variables
(5480 binds) . The statement is run few times to ensure it
is cached in the SGA.
.
sqlplus /nolog @tc
Runs a select of KGLOBCBCA from X$KGLCURSOR_CHILD_SQLIDPH
^
ORA-600 [kqlfFillBindData:1], [1200], [636]
.
Reproduced
~~~~~~~~~~
Reproduced in 10.2.0.2
Reproduced in RDBMS_MAIN_LINUX_070216
.
.
Workaround/s
~~~~~~~~~~~~
Do not use > 5461 bind variables in a single SQL
.
.
*** 02/19/07 05:12 am *** (CHG: Asg->NEW OWNER)
*** 02/19/07 05:12 am ***
*** 02/21/07 05:00 am *** (CHG: DevPri->2)
*** 02/21/07 05:00 am *** (CHG: Confirmed Flag->Y)
*** 02/21/07 05:00 am *** (CHG: Asg->NEW OWNER)
*** 02/21/07 05:00 am ***
*** 02/21/07 05:00 am *** (CHG: Asg->NEW OWNER)
*** 02/23/07 07:00 am *** ESCALATED
*** 02/23/07 07:04 am ***
*** 02/24/07 08:36 am *** (CHG: Asg->NEW OWNER)
*** 02/24/07 08:36 am ***
*** 03/02/07 03:14 pm ***
*** 03/07/07 06:18 pm ***
*** 03/20/07 05:44 am ***
*** 03/20/07 09:37 am ***
*** 03/28/07 10:22 am ***
*** 04/03/07 07:49 am ***
*** 04/04/07 06:40 pm ***
REDISCOVERY INFORMATION:
If 'ORA-00600 [kqlfFillBindData:1]' is raised and there are
thousands of bind variables in the SQL statement, then you
may be encountering this particular problem.
WORKAROUND:
Try to reduce the number of bind variables.
RELEASE NOTES:
]]'ORA-00600 [kqlfFillBindData:1]' is no longer raised against
]]a SQL statement containing thousands of bind variables.
*** 04/04/07 06:47 pm *** (CHG: Fixed->11.1.0.0)
*** 04/04/07 06:47 pm *** (CHG: Sta->80)
*** 04/05/07 06:14 am ***
*** 04/05/07 08:43 am ***
*** 04/05/07 12:58 pm ***
*** 04/05/07 01:25 pm ***
*** 04/05/07 01:26 pm ***
*** 04/05/07 01:26 pm ***
*** 04/06/07 04:42 am ***
*** 04/06/07 04:04 pm ***
*** 04/08/07 11:28 am ***
*** 04/09/07 08:58 am ***
*** 04/09/07 03:19 pm ***
*** 04/10/07 07:09 am ***
*** 04/10/07 01:17 pm ***
*** 04/11/07 03:11 pm ***
*** 04/12/07 01:53 pm ***
*** 04/12/07 07:30 pm ***
*** 04/13/07 10:30 am ***
*** 04/15/07 04:31 pm ***
*** 04/15/07 04:32 pm ***
*** 04/15/07 04:32 pm ***
*** 04/15/07 04:34 pm ***
*** 04/15/07 04:35 pm ***
*** 04/15/07 04:36 pm ***
*** 04/15/07 04:36 pm ***
*** 04/15/07 04:37 pm ***
*** 04/15/07 04:37 pm ***
*** 04/16/07 07:22 am ***
*** 04/18/07 02:24 am ***
*** 04/18/07 03:50 am ***
*** 04/19/07 12:20 am ***
*** 04/19/07 04:22 am ***
*** 04/19/07 05:18 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:20 am ***
*** 04/19/07 05:22 am ***
*** 04/19/07 05:28 am ***
*** 04/19/07 10:51 am ***
*** 04/23/07 02:50 am ***
*** 04/23/07 02:52 am ***
*** 04/24/07 05:59 am ***
*** 05/14/07 03:21 pm ***
*** 05/16/07 03:10 am ***
*** 05/16/07 11:27 pm ***
*** 05/18/07 03:26 am ***
*** 05/18/07 03:28 am ***
*** 05/18/07 04:02 am ***
*** 05/18/07 04:04 am ***
*** 05/18/07 04:13 am ***
*** 05/18/07 06:31 am ***
*** 05/25/07 06:54 pm ***
*** 05/29/07 07:47 pm ***
*** 06/01/07 01:33 pm ***
bug 5638146
*** 06/26/07 03:22 am ***
*** 06/28/07 01:08 am ***
*** 06/28/07 01:18 am ***
*** 07/30/07 02:02 am ***
*** 08/08/07 01:07 pm ***
*** 08/08/07 01:08 pm ***
*** 08/23/07 11:59 am ***
*** 08/29/07 02:08 am ***
*** 08/30/07 12:18 pm ***
*** 08/30/07 12:18 pm ***
*** 09/01/07 03:16 am ***
*** 09/07/07 07:05 am ***
*** 09/07/07 07:07 am ***
*** 10/26/07 01:01 am ***
*** 11/09/07 03:08 am ***
*** 11/09/07 03:20 am ***
*** 11/09/07 12:07 pm ***
*** 11/09/07 12:09 pm ***
*** 11/09/07 09:29 pm ***
*** 11/09/07 09:31 pm ***
*** 11/13/07 01:50 pm ***
*** 11/18/07 03:25 pm ***
*** 11/19/07 11:48 pm ***
*** 11/19/07 11:50 pm ***
*** 05/07/08 01:17 am ***
*** 05/07/08 02:42 am ***
*** 05/07/08 02:42 am ***
*** 12/17/08 04:42 pm ***
*** 12/24/08 05:19 am ***
*** 03/17/09 03:25 pm ***
*** 03/17/09 05:57 pm ***
*** 04/01/09 11:08 am ***
.
----------------------------------
README for 5638146
Patch Details
#
# WARNING: Failure to carefully read and understand these requirements may
# result in your applying a patch that can cause your Oracle Server to
# malfunction, including interruption of service and/or loss of data.
#
# If you do not meet all of the following requirements, please log an
# iTAR, so that an Oracle Support Analyst may review your situation. The
# Oracle analyst will help you determine if this patch is suitable for you
# to apply to your system. We recommend that you avoid applying any
# temporary patch unless directed by an Oracle Support Analyst who has
# reviewed your system and determined that it is applicable.
#
# Requirements:
#
# - You must have located this patch via a Bug Database entry
# and have the exact symptoms described in the bug entry.
#
# - Your system configuration (Oracle Server version and patch
# level, OS Version) must exactly match those in the bug
# database entry - You must have NO OTHER PATCHES installed on
# your Oracle Server since the latest patch set (or base release
# x.y.z if you have no patch sets installed).
#
# - [Oracle 9.2.0.2 & above] You must have Perl 5.00503 (or later)
# installed under the ORACLE_HOME, or elsewhere within the host
# environment. OPatch is no longer included in patches as of 9.2.0.2.
# Refer to the following link for details on Perl and OPatch:
# http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=189489.1
#
# - [IBM AIX O/S & Java patches for Oracle 9.2]
# In order to apply java class updates to IBM AIX based systems using
# java_131, you must update your java if you are running a version prior
# to Service Refresh build date 20030630a. This is
# necessary to fix IBM Defect#60472.
#
# To identify which java build date you are on, enter the following
# command ;
#
# > $ORACLE_HOME/jdk/bin/java -fullversion
# ... example response ...
# java full version "J2RE 1.3.1 IBM AIX build ca131-20030630a"
#
# The string ends in the date format YYYYMMDD or YYYYMMDDa where 'a'
# indicates an updated release to the original build. You should always
# apply the latest AIX Java SDK 1.3.1 Service Update available from IBM.
# As a minimum, the above service refresh can be found under
# APAR IY47055. The signature for the updated JVM is ca131-20030630a.
# Information on the latest available fixes, as well as how to apply
# the APARs to your AIX systems, is available at the IBM Java site.
#
# If you are running AIX 5L, you can safely ignore any comment against
# the APAR that says (AIXV43 only). The APAR is applicable to
# both AIX 4.3 and AIX 5L.
#
# Once you have updated your java installation you need to copy these
# updated files to Oracle's copies in $ORACLE_HOME/jdk.
# As the Oracle owner, simply issue the following commands;
#
# > cd /usr/java131
# > cp -fpR * $ORACLE_HOME/jdk
#
#
# If you do NOT meet these requirements, or are not certain that you meet
# these requirements, please log an iTAR requesting assistance with this
# patch and Support will make a determination about whether you should
# apply this patch.
#
#-------------------------------------------------------------------------
# Interim Patch for Base Bugs: 5638146
#-------------------------------------------------------------------------
#
# DATE: Wed Apr 18 22:32:51 2007
# -------------------------------
# Platform Patch for : AIX5L Based Systems (64-bit)
# Product Version # : 10.2.0.2
# Product Patched : RDBMS
#
# Bugs Fixed by this patch:
# -------------------------
# 5638146:ORA-600[KQLFFILLBINDDATA 1] ON INSERT INTO WRH$_SQLSTAT ON 10.2.0.2
#
# Patch Installation Instructions:
# --------------------------------
# To apply the patch, unzip the PSE container file:
#
# p5638146_10202_AIX64-5L.zip
#
# Set your current directory to the directory where the patch
# is located:
#
# % cd 5638146
# On AIX platforms only for 10201 Patches :
#
# Ensure environment variable OBJECT_MODE is set to 32_64
# e.g.
#
# % setenv OBJECT_MODE 32_64
# or
# $ export OBJECT_MODE=32_64
#
# Ensure that the directory containing the opatch script appears in
# your $PATH; then enter the following command:
#
# % opatch apply
#
# Patch Special Instructions:
# ---------------------------
# Make sure all instances running under the ORACLE_HOME being patched
# are cleanly shutdown before installing this patch. Also ensure that
# the tool used to terminate the instance(s) has exited cleanly.
#
# If the Oracle inventory is not setup correctly this utility will
# fail. To check accessibility to the inventory you can use the
# command
#
# % opatch lsinventory
#
# If you have any problems installing this PSE or are not sure
# about inventory setup please call Oracle support.
#
# Patch Deinstallation Instructions:
# ----------------------------------
# Use the following command:
#
# % cd 5638146
# % opatch rollback -id 5638146
#
----------------------------------
ora600_sapnote_0001033315
Bugs-5638146
5 Ağustos 2009 Çarşamba
RMAN Recovery -2
create tablespace;
CREATE SMALLFILE TABLESPACE "EXAMPLE" DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\example' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
CREATE SMALLFILE TABLESPACE "EXAMPLE" DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\example' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
----------------------------
create user;
CREATE USER "HR" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "EXAMPLE" ACCOUNT UNLOCK
GRANT "CONNECT" TO "HR"
----------------------------
create table;
CREATE TABLE "HR"."TB_REHBER" ( "AD" VARCHAR2(20), "SOYAD" VARCHAR2(20))
Failed to commit: ORA-01950: no privileges on tablespace 'EXAMPLE'
ALTER USER "HR" QUOTA UNLIMITED ON "EXAMPLE" --create etmek için
GRANT CREATE TABLE TO "HR"
GRANT ALTER TABLESPACE TO "HR"
DROP TABLE "HR"."TB_REHBER" CASCADE CONSTRAINTS
REVOKE ALTER TABLESPACE FROM "HR"
REVOKE CREATE TABLE FROM "HR"
---------------------------------
CREATE SMALLFILE TABLESPACE "EXAMPLE" DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\example' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
CREATE SMALLFILE TABLESPACE "EXAMPLE" DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\example' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
----------------------------
create user;
CREATE USER "HR" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "EXAMPLE" ACCOUNT UNLOCK
GRANT "CONNECT" TO "HR"
----------------------------
create table;
CREATE TABLE "HR"."TB_REHBER" ( "AD" VARCHAR2(20), "SOYAD" VARCHAR2(20))
Failed to commit: ORA-01950: no privileges on tablespace 'EXAMPLE'
ALTER USER "HR" QUOTA UNLIMITED ON "EXAMPLE" --create etmek için
GRANT CREATE TABLE TO "HR"
GRANT ALTER TABLESPACE TO "HR"
DROP TABLE "HR"."TB_REHBER" CASCADE CONSTRAINTS
REVOKE ALTER TABLESPACE FROM "HR"
REVOKE CREATE TABLE FROM "HR"
---------------------------------
RMAN Recovery
select table_name, tablespace_name from user_tables;
example tablespace siliyoruz.
select * from job_history;
hata verecektir.
bu konuda kullıcılardan ekran görüntüsü şart!!!
çalışan veritabanında illa yedekten donulmesi şart değil. test sunucuya gidip restore edip
hangi zaman aralığında veri kaybolduysa export edip appli sunucuya import edebiliriz.
tablespace düşürdüğümüz için sunucuyu kapatmaya gerek yok.
DataFile Recover Etmek:
1) hr ile bağlanıp employees tablosunu sorgulayalım:
sqlplus hr/hr@orcl
set pagesize 5000;
select * from employees;
2) Enterprise Manager ile employees tablosunun tablespace’ini önce offline yapıp sonra düşürelim.
3) SQL Plus’da 1.sıradaki sorguyu tekrar çalıştırıp employees tablosunun gittiğini görelim. Hangi datafile’ın eksik olduğunu numarasıyla birlikte söyleyecek.
4) datafile’ı rman ile recover edelim:
rman target sys/oracle@orcl;
restore datafile 5;
recover datafile 5;
RMAN> SQL ‘alter tablespace example online’;
Bu adımları tablespace üzerinden de yapabiliriz:
rman target sys/sarar@orcl;
restore tablespace EXAMPLE;
recover tablespace EXAMPLE;
RMAN> SQL ‘alter tablespace example online’;
5) Datafile’ın gelip gelmediğini görmek için 1.sıradaki sorguyu tekrar çalıştıralım.
Database Recover Etmek:
6) 1. sırdaki sorguyu çalıştırın.
7) shutdown abort komutu ile veritabanını kapatın.
oradata\orcl altındaki sonu *.dbf ile biten tüm dosyaları silin.
veritabanını startup mount komutuyla (RMAN> startup mount ) açın.
8) tüm veritabanını recover edelim:
rman target sys/oracle@orcl
restore database;
recover database;
alter database open;
9) 1. işlemdeki sorguyu çalıştırın:
INCOMPLETE RECOVERY:
Online redo logların olduğu hard disk göçerse, archived loglardan birkaç tanesi ya da hepsi kayıpsa, control file’ı kaybettiyseniz incomplete recovery yapmanız gerekir:
10) 1. sıradaki işlemi yapınız.
11) sys olarak bağlanıp dbid’yi alalım. Bu dbid’yi bir yere not edelim, çünkü control file’ı sileceğiz ve bu dbid ile geri dönebileceğiz.
Connect sys/oracle@orcl as sysdba
SELECT dbid
FROM v$database;
12) shutdown abort komutu ile veritabanını kapatın.
oradata\orcl altındaki tüm dosyaları silin.
13) 1.sıradaki işlemi çalıştırmaya ya da veritabanını açmaya çalışın.
14) RMAN’de aşağıdaki komutları yazın:
DOS> RMAN
SET DBID X;
CONNECT TARGET SYS/oracle@orcl;
STARTUP NOMOUNT;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\vtyedek\%F';
RESTORE CONTROLFILE FROM AUTOBACKUP;
PWD dosyasını d:\oracle\ora92\database altına kopyala
NLS_LANG SERVERLA Aynı olsun.
Not: Bir kaldırdığın backuptan tekrar kaldıramazsın.(O yüzden recovery yapmadan önce yedeğin yedeğini al). Veritabanını kaldırdığın backup geçersiz olacak. O yüzden ayağa kalktıktan sonra backup almakta fayda var.
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
Online redo loglarda kayıp olduğu için sonunda RMAN hataları oluşacak.
15) Online redo loglar kayıp olduğu için veritabanını onlar olmadan ve sequence’i sıfırlayarak açıyoruz.
RMAN> ALTER DATABASE OPEN RESETLOGS;
16) 1. sıradaki işlemi yapın :)
17) 4-5 kere switch yaparak biraz archive log üretelim.
alter system switch logfile;
/
/
/
/
18) rman’e bağlanıp backup ihtiyacı olan dosyalara bakalım.
rman target sys/oracle@orcl
RMAN> report need backup;
19) rman de yedek alalım.
rman> backup database plus archivelog;
20) eski dosyaları silelim.
rman> report obsolete;
rman> delete obsolete;
delete noprompt obsolete;
alter database backup controlfile to trace;
example tablespace siliyoruz.
select * from job_history;
hata verecektir.
bu konuda kullıcılardan ekran görüntüsü şart!!!
çalışan veritabanında illa yedekten donulmesi şart değil. test sunucuya gidip restore edip
hangi zaman aralığında veri kaybolduysa export edip appli sunucuya import edebiliriz.
tablespace düşürdüğümüz için sunucuyu kapatmaya gerek yok.
DataFile Recover Etmek:
1) hr ile bağlanıp employees tablosunu sorgulayalım:
sqlplus hr/hr@orcl
set pagesize 5000;
select * from employees;
2) Enterprise Manager ile employees tablosunun tablespace’ini önce offline yapıp sonra düşürelim.
3) SQL Plus’da 1.sıradaki sorguyu tekrar çalıştırıp employees tablosunun gittiğini görelim. Hangi datafile’ın eksik olduğunu numarasıyla birlikte söyleyecek.
4) datafile’ı rman ile recover edelim:
rman target sys/oracle@orcl;
restore datafile 5;
recover datafile 5;
RMAN> SQL ‘alter tablespace example online’;
Bu adımları tablespace üzerinden de yapabiliriz:
rman target sys/sarar@orcl;
restore tablespace EXAMPLE;
recover tablespace EXAMPLE;
RMAN> SQL ‘alter tablespace example online’;
5) Datafile’ın gelip gelmediğini görmek için 1.sıradaki sorguyu tekrar çalıştıralım.
Database Recover Etmek:
6) 1. sırdaki sorguyu çalıştırın.
7) shutdown abort komutu ile veritabanını kapatın.
oradata\orcl altındaki sonu *.dbf ile biten tüm dosyaları silin.
veritabanını startup mount komutuyla (RMAN> startup mount ) açın.
8) tüm veritabanını recover edelim:
rman target sys/oracle@orcl
restore database;
recover database;
alter database open;
9) 1. işlemdeki sorguyu çalıştırın:
INCOMPLETE RECOVERY:
Online redo logların olduğu hard disk göçerse, archived loglardan birkaç tanesi ya da hepsi kayıpsa, control file’ı kaybettiyseniz incomplete recovery yapmanız gerekir:
10) 1. sıradaki işlemi yapınız.
11) sys olarak bağlanıp dbid’yi alalım. Bu dbid’yi bir yere not edelim, çünkü control file’ı sileceğiz ve bu dbid ile geri dönebileceğiz.
Connect sys/oracle@orcl as sysdba
SELECT dbid
FROM v$database;
12) shutdown abort komutu ile veritabanını kapatın.
oradata\orcl altındaki tüm dosyaları silin.
13) 1.sıradaki işlemi çalıştırmaya ya da veritabanını açmaya çalışın.
14) RMAN’de aşağıdaki komutları yazın:
DOS> RMAN
SET DBID X;
CONNECT TARGET SYS/oracle@orcl;
STARTUP NOMOUNT;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\vtyedek\%F';
RESTORE CONTROLFILE FROM AUTOBACKUP;
PWD dosyasını d:\oracle\ora92\database altına kopyala
NLS_LANG SERVERLA Aynı olsun.
Not: Bir kaldırdığın backuptan tekrar kaldıramazsın.(O yüzden recovery yapmadan önce yedeğin yedeğini al). Veritabanını kaldırdığın backup geçersiz olacak. O yüzden ayağa kalktıktan sonra backup almakta fayda var.
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
Online redo loglarda kayıp olduğu için sonunda RMAN hataları oluşacak.
15) Online redo loglar kayıp olduğu için veritabanını onlar olmadan ve sequence’i sıfırlayarak açıyoruz.
RMAN> ALTER DATABASE OPEN RESETLOGS;
16) 1. sıradaki işlemi yapın :)
17) 4-5 kere switch yaparak biraz archive log üretelim.
alter system switch logfile;
/
/
/
/
18) rman’e bağlanıp backup ihtiyacı olan dosyalara bakalım.
rman target sys/oracle@orcl
RMAN> report need backup;
19) rman de yedek alalım.
rman> backup database plus archivelog;
20) eski dosyaları silelim.
rman> report obsolete;
rman> delete obsolete;
delete noprompt obsolete;
alter database backup controlfile to trace;
Check Database - 2
select value
from v$parameter
where name = 'background_dump_dest';
http://abakalidis.blogspot.com/2007/11/where-is-oracle-database-alert-log.html
--------------------------
SAPDBA: Error - opening alert log P:\oracle\QAS\saptrace\background\QASalrt.log
However Oracle Log is actually at the place Q:\oracle\QAS\817\RDBMS\trace\qasALRT.LOG
from v$parameter
where name = 'background_dump_dest';
http://abakalidis.blogspot.com/2007/11/where-is-oracle-database-alert-log.html
--------------------------
SAPDBA: Error - opening alert log P:\oracle\QAS\saptrace\background\QASalrt.log
However Oracle Log is actually at the place Q:\oracle\QAS\817\RDBMS\trace\qasALRT.LOG
Compute Statistics
table için;
begin
dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=> 'MARG', partname=> NULL);
end;
tablespace için;
begin
dbms_stats.gather_schema_stats(ownname=> 'SAPSR3' , cascade=> TRUE);
end;
http://blog.csdn.net/guozhenblog/category/260868.aspx
begin
dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=> 'MARG', partname=> NULL);
end;
tablespace için;
begin
dbms_stats.gather_schema_stats(ownname=> 'SAPSR3' , cascade=> TRUE);
end;
http://blog.csdn.net/guozhenblog/category/260868.aspx
Check Database
Oracle Database Administration
Oracle Database Information Page
* Oracle Database Recovery
* Oracle Database Backup
* Oracle Database Migration
* Oracle Database Monitoring Scripts:
o User
o Tablespaces
o Rollback Segment
o Redo Log
o Tables and Indexes
o Coalesce
o FreeList
o Database Buffer
o Extents
o Shared Pool Size
o Session Statstics
* Oracle Database Archiving
* Oracle Stored Procedures
* Y2K Inventory Database
* Other Issues
Oracle Database Recovery
Implementing a Restored and Recovery Strategy
Phase I - Steps for Diagnosing a Problem
1. Determine if the database instance is available and the database is open.
2. Attempt to start the instance and open the database.
3. Shut down the instance if problems occur while starting it or opening the database.
4. Check the trace files for possible problems.
5. Check the alert_SID.log file for the possible problems.
6. Determine the appropriate recovery method by asking the following questions for each scenario:
1. Which recovery operations are available?
1. Complete Recovery
Closed Database Recovery
Open Database, Offline Tablespace Recovery
Open Database, Offline Tablespace, Individual Datafile Recovery
2. Incomplete Media Recovery
Cancel Based Recovery
Time Based Recovery
Change Based Recovery
2. Which recovery operations are appropriate for the particular problem?
3. Are disaster recovery procedures in place?
4. What need to be restored to proceed with recovery?
Phase II - Restore Appropriate Files
1. Determine which file(s) to restore
2. Determine what state the instance and database must be in to perform the recovery.
Phase III - Recover Database
1. Perform the appropriate method of recovery
Phase IV - Backup Database
1. Determine if another full offline backup is required.
Time Based Recovery
This is used to recover the database up to a specific point in time:
* When data is lost; check the alert file for the approximate time of the error.
* When part of a non-mirrored online redo log becomes corrupt and the approximate time of the corruption is known
1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup(except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
8. svrmgrl> alter database open resetlogs;
9. svrmgrl> shutdown
10. svrmgrl> startup
11. Do an offline backup
Recently, I had to recover a database using time based recovery but due to some corrupted nightly backup files, I was not able to do a normal recovery. The error message was:
SVRMGR> recover database until time 1998-06-03:10:18:00
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/dbase/u04/oradata/PRD/temp01PRD.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
The way to get around this is:
1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup (except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> select * from v$recover_file: it displayed 3 datafile that are not recoverable:
* /dbase/u04/oradata/PRD/temp01PRD.dbf
* /dbase/u04/oradata/PRD/ndx01BUD.dbf
* /dbase/u04/oradata/PRD/ndx01HLP.dbf
8. svrmgrl> alter database datafile '/dbase/u04/oradata/PRD/temp01PRD.dbf' offline drop; repeat for the other 2 files.
9. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
10. svrmgrl> alter database open resetlogs;
11. svrmgrl> drop tablespace TEMP including contents; svrmgrl> drop tablespace BUD_NDX including contents cascade contraints;
12. Create new tablespaces.
13. Create new indexes.
14. Shutdown the database.
15. Startup the database.
16. Do an offline backup.
Oracle Database Migration
Two ways to move a database:
1. For a small database.
1. Do an Export.
2. Create a new empty database.
3. Do an Import.
2. For a large database (in the old system):
1. svrmgrl>alter database backup controlfile to trace.
2. Take a Cold Backup.
For a large database (in the new system):
3. Restore on the new system (all the datafiles and redo log files). Modified file name if necessary.
4. Create New initSID file.
5. Modify the trace file from step 1 to reflect changes in file names and change the database name if desired.
6. Set the ORACLE_SID to the new SID.
7. Run the sql from step 1
8. Open the database.
Oracle Database Backup
3 ways of doing database backup:
1. Full Offline(Cold) Backup
2. Full Online(Hot) Backup - archiving must be on
3. Export Utility Backup
Oracle Database Archiving
To Turn Archive Log On
1. Modified the initSID.ora file to uncomment these 3 lines:
log_archive_start = true
log_archive_dest = disk$rdbms:[oracle.archive]
log_archive_format = "%S.arc"
2. Goto svrmgrl
svrmgrl> connect internal
svrmgrl> startup mount pfile=initSID.ora
svrmgrl> alter database archivelog;
svrmgrl> alter database open
Oracle Stored Procedure
To Create a Procedure
in sqlplus
sql> create procedure scott.sal_incr
sql> (v_empno IN NUMBER,
sql> v_incr IN NUMBER)
sql> as begin
sql> update scott.emp
sql> set sal = sal + v_incr
sql> where empno = v_empno;
sql> end;
To Execute a Procedure
in sqlplus
sql> exec sal_incr(v_empno, v_incr);
To look for User Procedure
in sqlplus
sql> select * from user_objects where object_type = 'PROCEDURE';
To look at Procedure Code
in sqlplus
sql> select text from user_source where name = 'PROCEDURE_NAME';
Oracle Database Monitoring Scripts
Check for overextended tables or indexes
select substr(owner,1,10) "Owner",
substr(segment_name,1,10) "SegName",
segment_type "SegType",
substr(tablespace_name,1,10) "Tablespace",
substr(extents,1,4) "#Ext",
max_extents "Max",
initial_extent "InitExt",
next_extent "NextExt"
from sys.dba_segments
where extents > 5
and (segment_type = 'TABLE'
or segment_type = 'ROLLBACK'
or segment_type = 'INDEX')
and owner like '%DBA'
order by owner, extents desc, segment_name;
Check for full tablespace
select substr(owner,1,10) "Owner",
substr(segment_name,1,14) "SegName",
next_extent "NextExt",
substr(tablespace_name,1,14) "Tablespace"
from dba_segments ds
where next_extent >
(select max(bytes)
from dba_free_space
where tablespace_name=ds.tablespace_name);
Check for tablespace fragmentation
select TABLESPACE_NAME,
count(*) FREE_SEGMENTS
from dba_free_space
group by TABLESPACE_NAME
having count(*) > 10;
Check for free space by tablespace monitoring
select substr(b.file_id,1,3) "FID",
substr(b.file_name,23,16) "Filename",
substr(b.tablespace_name,1,10) "Tablespace",
b.bytes "# Bytes",
(b.bytes - sum(nvl(a.bytes,0))) "# Used",
sum(nvl(a.bytes,0)) "# Free",
(sum(nvl(a.bytes,0))/(b.bytes)) * 100 "% Free"
from sys.dba_free_space a, sys.dba_data_files b
where a.file_id(+) = b.file_id
group by b.tablespace_name, b.file_id, b.file_name, b.bytes
order by b.tablespace_name;
Tablespace Disk Usages
select tablespace_name,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by tablespace_name;
select tablespace_name,
sum(bytes) "Bytes Alloc"
from dba_data_files
group by tablespace_name;
Check for redo log contention
select name,
gets,
misses,
sleeps,
immediate_gets,
immediate_misses
from v$latch
where name in ('redo allocation', 'redo copy');
Check for rollback segments contention
select substr(r.name,1,10) "Rollback",
s.gets,
s.waits,
round (100 * s.waits/s.gets) "%Cont"
from v$rollstat s, v$rollname r
where s.usn = r.usn
and round (100 * s.waits/s.gets) > 1;
Check for rollback segments monitoring
select substr(name,1,10) "Rollback",
optsize,
shrinks,
aveshrink,
wraps,
extends,
rssize,
writes
from v$rollstat s, v$rollname r
where s.usn = r.usn;
select substr(segment_name,1,14),
substr(name,1,36),
substr(file_id,1,6),
bytes,
substr(tablespace_name,1,10)
from dba_rollback_segs,
v$datafile
where v$datafile.file# = dba_rollback_segs.file_id;
Check for transaction per rollback segments monitoring
select r.name rr,
nvl(s.username,'no transaction') us,
s.osuser os,
s.terminal ts
from V$LOCK l, V$SESSION s, V$ROLLNAME r
where l.sid =s.sid(+)
and trunc(l.ID1/65536) = r.usn
and l.type = 'TX'
and l.lmode = 6
order by r.name;
Check for user that use SYSTEM tablespace for work area
select substr(username,1,14) "User",
user_id "User ID",
substr(default_tablespace,1,14) "Default TSpace",
substr(temporary_tablespace,1,14) "Temp TSpace"
from dba_users
where temporary_tablespace = 'SYSTEM';
Check user privileges
select *
from sys.dba_role_privs
where GRANTEE like '%';
select * from sys.dba_sys_privs
where GRANTEE like '%';
select * from sys.dba_tab_privs
where GRANTEE like '%';
select username,
program,
sid,
serial#
from v$session;
User Disk Usages
select owner,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by owner;
Check for tablespace coalesce
select tablespace_name, percent_blocks_coalesced
from dba_free_space_coalesced
order by percent_blocks_coalesced;
alter tablespace XXX coalesce;
select * from dba_free_space
where tablespace_name = 'XXX';
Check for db_buffer
select name,
value
from v$sysstat
where name in ('consistent gets', 'db block gets', 'physical reads');
select * from dba_ts_quotas
where BYTES = MAX_BYTES;
Check for Freelist
select * from v$waitstat;
Check for segments running out of extents
select a.owner,
table_name "table",
a.tablespace_name "tablespace",
'T' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_tables a,
sys.dba_segments b
where table_name = segment_name
and a.max_extents - b.extents <= 10
union
select a.owner,
index_name "index",
a.tablespace_name "tablespace",
'I' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_indexes a,
sys.dba_segments b
where index_name = segment_name
and a.max_extents - b.extents <= 10;
Resize datafiles
alter database datafile 'XXX' resize 30M;
Collect session statstics
select username,
to_char(sysdate - (HSECS - s.value)/(3600*100*24),
'DD-MM-YYYY HH24:MI:SS')
from v$sesstst S,
v$session SE,
v$timer
where SE.SID = S.SID
and statistic#=14
and username is not null;
Shared Pool Information
select sum(pins) pins,
sum(reloads) reloads
from v$librarycache;
select sum(gets) gets,
sum(getmisses) getmisses
from v$rowcache;
Oracle Database Other Issues
Connection
To restrict other users to the database.
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
http://www.its.monash.edu.au/staff/systems/oracle/technical/ss_oracle.html
http://decipherinfosys.wordpress.com/2007/03/20/checking-database-parameters-in-oracle/
http://help.sap.com/saphelp_nw70/helpdata/EN/b0/a42100eb7ec5478ebf2c846e2912e9/frameset.htm
Oracle Database Information Page
* Oracle Database Recovery
* Oracle Database Backup
* Oracle Database Migration
* Oracle Database Monitoring Scripts:
o User
o Tablespaces
o Rollback Segment
o Redo Log
o Tables and Indexes
o Coalesce
o FreeList
o Database Buffer
o Extents
o Shared Pool Size
o Session Statstics
* Oracle Database Archiving
* Oracle Stored Procedures
* Y2K Inventory Database
* Other Issues
Oracle Database Recovery
Implementing a Restored and Recovery Strategy
Phase I - Steps for Diagnosing a Problem
1. Determine if the database instance is available and the database is open.
2. Attempt to start the instance and open the database.
3. Shut down the instance if problems occur while starting it or opening the database.
4. Check the trace files for possible problems.
5. Check the alert_SID.log file for the possible problems.
6. Determine the appropriate recovery method by asking the following questions for each scenario:
1. Which recovery operations are available?
1. Complete Recovery
Closed Database Recovery
Open Database, Offline Tablespace Recovery
Open Database, Offline Tablespace, Individual Datafile Recovery
2. Incomplete Media Recovery
Cancel Based Recovery
Time Based Recovery
Change Based Recovery
2. Which recovery operations are appropriate for the particular problem?
3. Are disaster recovery procedures in place?
4. What need to be restored to proceed with recovery?
Phase II - Restore Appropriate Files
1. Determine which file(s) to restore
2. Determine what state the instance and database must be in to perform the recovery.
Phase III - Recover Database
1. Perform the appropriate method of recovery
Phase IV - Backup Database
1. Determine if another full offline backup is required.
Time Based Recovery
This is used to recover the database up to a specific point in time:
* When data is lost; check the alert file for the approximate time of the error.
* When part of a non-mirrored online redo log becomes corrupt and the approximate time of the corruption is known
1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup(except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
8. svrmgrl> alter database open resetlogs;
9. svrmgrl> shutdown
10. svrmgrl> startup
11. Do an offline backup
Recently, I had to recover a database using time based recovery but due to some corrupted nightly backup files, I was not able to do a normal recovery. The error message was:
SVRMGR> recover database until time 1998-06-03:10:18:00
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/dbase/u04/oradata/PRD/temp01PRD.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
The way to get around this is:
1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup (except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> select * from v$recover_file: it displayed 3 datafile that are not recoverable:
* /dbase/u04/oradata/PRD/temp01PRD.dbf
* /dbase/u04/oradata/PRD/ndx01BUD.dbf
* /dbase/u04/oradata/PRD/ndx01HLP.dbf
8. svrmgrl> alter database datafile '/dbase/u04/oradata/PRD/temp01PRD.dbf' offline drop; repeat for the other 2 files.
9. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
10. svrmgrl> alter database open resetlogs;
11. svrmgrl> drop tablespace TEMP including contents; svrmgrl> drop tablespace BUD_NDX including contents cascade contraints;
12. Create new tablespaces.
13. Create new indexes.
14. Shutdown the database.
15. Startup the database.
16. Do an offline backup.
Oracle Database Migration
Two ways to move a database:
1. For a small database.
1. Do an Export.
2. Create a new empty database.
3. Do an Import.
2. For a large database (in the old system):
1. svrmgrl>alter database backup controlfile to trace.
2. Take a Cold Backup.
For a large database (in the new system):
3. Restore on the new system (all the datafiles and redo log files). Modified file name if necessary.
4. Create New initSID file.
5. Modify the trace file from step 1 to reflect changes in file names and change the database name if desired.
6. Set the ORACLE_SID to the new SID.
7. Run the sql from step 1
8. Open the database.
Oracle Database Backup
3 ways of doing database backup:
1. Full Offline(Cold) Backup
2. Full Online(Hot) Backup - archiving must be on
3. Export Utility Backup
Oracle Database Archiving
To Turn Archive Log On
1. Modified the initSID.ora file to uncomment these 3 lines:
log_archive_start = true
log_archive_dest = disk$rdbms:[oracle.archive]
log_archive_format = "%S.arc"
2. Goto svrmgrl
svrmgrl> connect internal
svrmgrl> startup mount pfile=initSID.ora
svrmgrl> alter database archivelog;
svrmgrl> alter database open
Oracle Stored Procedure
To Create a Procedure
in sqlplus
sql> create procedure scott.sal_incr
sql> (v_empno IN NUMBER,
sql> v_incr IN NUMBER)
sql> as begin
sql> update scott.emp
sql> set sal = sal + v_incr
sql> where empno = v_empno;
sql> end;
To Execute a Procedure
in sqlplus
sql> exec sal_incr(v_empno, v_incr);
To look for User Procedure
in sqlplus
sql> select * from user_objects where object_type = 'PROCEDURE';
To look at Procedure Code
in sqlplus
sql> select text from user_source where name = 'PROCEDURE_NAME';
Oracle Database Monitoring Scripts
Check for overextended tables or indexes
select substr(owner,1,10) "Owner",
substr(segment_name,1,10) "SegName",
segment_type "SegType",
substr(tablespace_name,1,10) "Tablespace",
substr(extents,1,4) "#Ext",
max_extents "Max",
initial_extent "InitExt",
next_extent "NextExt"
from sys.dba_segments
where extents > 5
and (segment_type = 'TABLE'
or segment_type = 'ROLLBACK'
or segment_type = 'INDEX')
and owner like '%DBA'
order by owner, extents desc, segment_name;
Check for full tablespace
select substr(owner,1,10) "Owner",
substr(segment_name,1,14) "SegName",
next_extent "NextExt",
substr(tablespace_name,1,14) "Tablespace"
from dba_segments ds
where next_extent >
(select max(bytes)
from dba_free_space
where tablespace_name=ds.tablespace_name);
Check for tablespace fragmentation
select TABLESPACE_NAME,
count(*) FREE_SEGMENTS
from dba_free_space
group by TABLESPACE_NAME
having count(*) > 10;
Check for free space by tablespace monitoring
select substr(b.file_id,1,3) "FID",
substr(b.file_name,23,16) "Filename",
substr(b.tablespace_name,1,10) "Tablespace",
b.bytes "# Bytes",
(b.bytes - sum(nvl(a.bytes,0))) "# Used",
sum(nvl(a.bytes,0)) "# Free",
(sum(nvl(a.bytes,0))/(b.bytes)) * 100 "% Free"
from sys.dba_free_space a, sys.dba_data_files b
where a.file_id(+) = b.file_id
group by b.tablespace_name, b.file_id, b.file_name, b.bytes
order by b.tablespace_name;
Tablespace Disk Usages
select tablespace_name,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by tablespace_name;
select tablespace_name,
sum(bytes) "Bytes Alloc"
from dba_data_files
group by tablespace_name;
Check for redo log contention
select name,
gets,
misses,
sleeps,
immediate_gets,
immediate_misses
from v$latch
where name in ('redo allocation', 'redo copy');
Check for rollback segments contention
select substr(r.name,1,10) "Rollback",
s.gets,
s.waits,
round (100 * s.waits/s.gets) "%Cont"
from v$rollstat s, v$rollname r
where s.usn = r.usn
and round (100 * s.waits/s.gets) > 1;
Check for rollback segments monitoring
select substr(name,1,10) "Rollback",
optsize,
shrinks,
aveshrink,
wraps,
extends,
rssize,
writes
from v$rollstat s, v$rollname r
where s.usn = r.usn;
select substr(segment_name,1,14),
substr(name,1,36),
substr(file_id,1,6),
bytes,
substr(tablespace_name,1,10)
from dba_rollback_segs,
v$datafile
where v$datafile.file# = dba_rollback_segs.file_id;
Check for transaction per rollback segments monitoring
select r.name rr,
nvl(s.username,'no transaction') us,
s.osuser os,
s.terminal ts
from V$LOCK l, V$SESSION s, V$ROLLNAME r
where l.sid =s.sid(+)
and trunc(l.ID1/65536) = r.usn
and l.type = 'TX'
and l.lmode = 6
order by r.name;
Check for user that use SYSTEM tablespace for work area
select substr(username,1,14) "User",
user_id "User ID",
substr(default_tablespace,1,14) "Default TSpace",
substr(temporary_tablespace,1,14) "Temp TSpace"
from dba_users
where temporary_tablespace = 'SYSTEM';
Check user privileges
select *
from sys.dba_role_privs
where GRANTEE like '%';
select * from sys.dba_sys_privs
where GRANTEE like '%';
select * from sys.dba_tab_privs
where GRANTEE like '%';
select username,
program,
sid,
serial#
from v$session;
User Disk Usages
select owner,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by owner;
Check for tablespace coalesce
select tablespace_name, percent_blocks_coalesced
from dba_free_space_coalesced
order by percent_blocks_coalesced;
alter tablespace XXX coalesce;
select * from dba_free_space
where tablespace_name = 'XXX';
Check for db_buffer
select name,
value
from v$sysstat
where name in ('consistent gets', 'db block gets', 'physical reads');
select * from dba_ts_quotas
where BYTES = MAX_BYTES;
Check for Freelist
select * from v$waitstat;
Check for segments running out of extents
select a.owner,
table_name "table",
a.tablespace_name "tablespace",
'T' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_tables a,
sys.dba_segments b
where table_name = segment_name
and a.max_extents - b.extents <= 10
union
select a.owner,
index_name "index",
a.tablespace_name "tablespace",
'I' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_indexes a,
sys.dba_segments b
where index_name = segment_name
and a.max_extents - b.extents <= 10;
Resize datafiles
alter database datafile 'XXX' resize 30M;
Collect session statstics
select username,
to_char(sysdate - (HSECS - s.value)/(3600*100*24),
'DD-MM-YYYY HH24:MI:SS')
from v$sesstst S,
v$session SE,
v$timer
where SE.SID = S.SID
and statistic#=14
and username is not null;
Shared Pool Information
select sum(pins) pins,
sum(reloads) reloads
from v$librarycache;
select sum(gets) gets,
sum(getmisses) getmisses
from v$rowcache;
Oracle Database Other Issues
Connection
To restrict other users to the database.
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
http://www.its.monash.edu.au/staff/systems/oracle/technical/ss_oracle.html
http://decipherinfosys.wordpress.com/2007/03/20/checking-database-parameters-in-oracle/
http://help.sap.com/saphelp_nw70/helpdata/EN/b0/a42100eb7ec5478ebf2c846e2912e9/frameset.htm
3 Ağustos 2009 Pazartesi
Patch Upgrade
Note 824432 - Current Patchset for Oracle 10.1.
http://hi.baidu.com/regards/blog/item/5a7abeec23a1234879f0552a.html/cmtid/c1974c2501df7e21d5074230
环境：AIX5.3L + Oracle 10G + SAP ECC6
1.查看当前的Oracle版本：
PRDT / # su - oraprt
PRDT:oraprt 1> sqlplus /nolog
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 17 13:25:29 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
SQL> connect /as sysdba
Connected.
SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio
NLSRTL Version 10.2.0.2.0 – Production
2.查看oracle已经打过的patch：
PRDT / # su - oraprt
PRDT:oraprt 1> cd $ORACLE_HOME/OPatch
PRDT:oraprt 2> pwd
/oracle/PRT/102_64/OPatch
PRDT:oraprt 3> ./opatch lsinventory
Invoking OPatch 10.2.0.2.0
Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /oracle/PRT/102_64
Central Inventory : /oracle/oraInventory
from : /oracle/PRT/102_64/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /oracle/PRT/102_64/oui
Log file location : /oracle/PRT/102_64/cfgtoollogs/opatch/opatch-00_Oct_17_16-06-48-GMT+08_Fri.log
Lsinventory Output file location : /oracle/PRT/102_64/cfgtoollogs/opatch/lsinv/lsinventory-00_Oct_17_16-06-48-GMT+08_Fri.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1 10.2.0.2.0
There are 2 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
3. 上传并解压Oracle patch和Oracle Client文件
(由于下载的是3部分类似于WINRAR的分卷文件，需要在DOS下进行合并)
D:\SAP SOFT>copy /B Patchset_10204_AIX5Laa.bin + Patchset_10204_AIX5Lab.bin + Patchset_10204_AIX5Lac.bin + Patchset_10204_AIX5Lad.bin Patchset_10204_AIX5La.bin
Patchset_10204_AIX5Laa.bin
Patchset_10204_AIX5Lab.bin
Patchset_10204_AIX5Lac.bin
Patchset_10204_AIX5Lad.bin
已复制 1 个文件。
修改Disk1和Oracle client文件为777
PRDT /sapsoft/sapsoft/orapatch # chmod -R 777 Disk1
PRDT /sapsoft/sapsoft/orapatch # chmod 777 OCL10264.SAR
4．备份SAP所在的VG：(smitty savevg)
5.安装oracle patch：
确认SAP和数据库关闭
# ps -ef | grep sap
root 974922 573570 0 16:17:02 pts/4 0:00 grep sap
# ps -ef | rep ora
/usr/bin/ksh: rep: not found.
# ps -ef | grep ora
root 974926 573570 0 16:17:12 pts/4 0:00 grep ora
PRDT:oraprt 11> ./runInstaller
********************************************************************************
Your platform requires the root user to perform certain pre-installation
OS preparation. The root user should execute '/usr/sbin/slibclean' on all the nodes before
you proceed with Patchset Installation.
Answer 'y' if root has run '/usr/sbin/slibclean' so you can proceed with Oracle
Patchset installation.
Answer 'n' to abort installation and then ask root to run '/usr/sbin/slibclean'.
********************************************************************************
Has '/usr/sbin/slibclean' been run by root? [y/n] (n)
./runInstaller[29]: y: 0403-012 A test command parameter is not valid.
Patchset Installation stopped to run '/usr/sbin/slibclean' by root.
# /usr/sbin/slibclean
设置DISPLAY实现远程GUI的调用:
PRDT:oraprt 11> setenv DISPLAY 10.38.5.12:0.0
PRDT:oraprt 12> ./runInstaller
完成oracle patch的安装。
6.安装oracle client：
PRDT:oraprt 7>SAPCAR -xvf OCL10264.SAR
PRDT:oraprt 8> ls -l
total 121888
drwxrwxrwx 5 root system 256 Oct 17 16:03 Disk1
-rwxrwxrwx 1 root system 56047058 Oct 17 15:50 OCL10264.SAR
-rwxrwxrwx 1 prtadm sapsys 6349944 Aug 02 2007 SAPCAR
drwxr-xr-x 2 oraprt dba 4096 Aug 01 23:29 instantclient_10204_V1
PRDT:oraprt 9> cd /oracle/ client/10*
PRDT:oraprt 16> ls -l
total 8
-rwxrwxr-x 1 prtadm sapsys 101 Mar 27 2006 dbclient.lst
lrwxrwxrwx 1 root sapinst 41 Feb 28 2008 instantclient -> /oracle/client/10x_64/instantclient_10202
drwxrwxr-x 2 prtadm sapsys 256 Mar 27 2006 instantclient_10202
PRDT:oraprt 16>rm instantclient
PRDT:oraprt17>cp –pr /sapsoft/sapsoft/orapatch/ instantclient_10204_V1 instantclient_10204_V1
PRDT:oraprt18>ln -s instantclient_10204_V1 instantclient
7.完成oracle client 10.2.0.4.0的安装，通过测试:
PRDT:prtadm 1> R3trans -d
This is R3trans version 6.14 (release 700 - 28.07.08 - 15:52:00).
unicode enabled version
R3trans finished (0000).
8.更新数据字典视图：
升级以后数据字典的一些基表的内容修改了,catalog.sql和catproc.sql这2个脚本需要在运行一下把数据字典视图在更新一下.
PRDT / # su - oraprt
PRDT:oraprt 1> sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 17 23:41:33 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> connect /as sysdba
Connected.
SQL>STARTUP UPGRADE
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
9.正常启动Sap，完成Oracle patch和oracle client的安装。
http://hi.baidu.com/regards/blog/item/5a7abeec23a1234879f0552a.html/cmtid/c1974c2501df7e21d5074230
环境：AIX5.3L + Oracle 10G + SAP ECC6
1.查看当前的Oracle版本：
PRDT / # su - oraprt
PRDT:oraprt 1> sqlplus /nolog
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 17 13:25:29 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
SQL> connect /as sysdba
Connected.
SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio
NLSRTL Version 10.2.0.2.0 – Production
2.查看oracle已经打过的patch：
PRDT / # su - oraprt
PRDT:oraprt 1> cd $ORACLE_HOME/OPatch
PRDT:oraprt 2> pwd
/oracle/PRT/102_64/OPatch
PRDT:oraprt 3> ./opatch lsinventory
Invoking OPatch 10.2.0.2.0
Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /oracle/PRT/102_64
Central Inventory : /oracle/oraInventory
from : /oracle/PRT/102_64/oraInst.loc
OPatch version : 10.2.0.2.0
OUI version : 10.2.0.2.0
OUI location : /oracle/PRT/102_64/oui
Log file location : /oracle/PRT/102_64/cfgtoollogs/opatch/opatch-00_Oct_17_16-06-48-GMT+08_Fri.log
Lsinventory Output file location : /oracle/PRT/102_64/cfgtoollogs/opatch/lsinv/lsinventory-00_Oct_17_16-06-48-GMT+08_Fri.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1 10.2.0.2.0
There are 2 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
3. 上传并解压Oracle patch和Oracle Client文件
(由于下载的是3部分类似于WINRAR的分卷文件，需要在DOS下进行合并)
D:\SAP SOFT>copy /B Patchset_10204_AIX5Laa.bin + Patchset_10204_AIX5Lab.bin + Patchset_10204_AIX5Lac.bin + Patchset_10204_AIX5Lad.bin Patchset_10204_AIX5La.bin
Patchset_10204_AIX5Laa.bin
Patchset_10204_AIX5Lab.bin
Patchset_10204_AIX5Lac.bin
Patchset_10204_AIX5Lad.bin
已复制 1 个文件。
修改Disk1和Oracle client文件为777
PRDT /sapsoft/sapsoft/orapatch # chmod -R 777 Disk1
PRDT /sapsoft/sapsoft/orapatch # chmod 777 OCL10264.SAR
4．备份SAP所在的VG：(smitty savevg)
5.安装oracle patch：
确认SAP和数据库关闭
# ps -ef | grep sap
root 974922 573570 0 16:17:02 pts/4 0:00 grep sap
# ps -ef | rep ora
/usr/bin/ksh: rep: not found.
# ps -ef | grep ora
root 974926 573570 0 16:17:12 pts/4 0:00 grep ora
PRDT:oraprt 11> ./runInstaller
********************************************************************************
Your platform requires the root user to perform certain pre-installation
OS preparation. The root user should execute '/usr/sbin/slibclean' on all the nodes before
you proceed with Patchset Installation.
Answer 'y' if root has run '/usr/sbin/slibclean' so you can proceed with Oracle
Patchset installation.
Answer 'n' to abort installation and then ask root to run '/usr/sbin/slibclean'.
********************************************************************************
Has '/usr/sbin/slibclean' been run by root? [y/n] (n)
./runInstaller[29]: y: 0403-012 A test command parameter is not valid.
Patchset Installation stopped to run '/usr/sbin/slibclean' by root.
# /usr/sbin/slibclean
设置DISPLAY实现远程GUI的调用:
PRDT:oraprt 11> setenv DISPLAY 10.38.5.12:0.0
PRDT:oraprt 12> ./runInstaller
完成oracle patch的安装。
6.安装oracle client：
PRDT:oraprt 7>SAPCAR -xvf OCL10264.SAR
PRDT:oraprt 8> ls -l
total 121888
drwxrwxrwx 5 root system 256 Oct 17 16:03 Disk1
-rwxrwxrwx 1 root system 56047058 Oct 17 15:50 OCL10264.SAR
-rwxrwxrwx 1 prtadm sapsys 6349944 Aug 02 2007 SAPCAR
drwxr-xr-x 2 oraprt dba 4096 Aug 01 23:29 instantclient_10204_V1
PRDT:oraprt 9> cd /oracle/ client/10*
PRDT:oraprt 16> ls -l
total 8
-rwxrwxr-x 1 prtadm sapsys 101 Mar 27 2006 dbclient.lst
lrwxrwxrwx 1 root sapinst 41 Feb 28 2008 instantclient -> /oracle/client/10x_64/instantclient_10202
drwxrwxr-x 2 prtadm sapsys 256 Mar 27 2006 instantclient_10202
PRDT:oraprt 16>rm instantclient
PRDT:oraprt17>cp –pr /sapsoft/sapsoft/orapatch/ instantclient_10204_V1 instantclient_10204_V1
PRDT:oraprt18>ln -s instantclient_10204_V1 instantclient
7.完成oracle client 10.2.0.4.0的安装，通过测试:
PRDT:prtadm 1> R3trans -d
This is R3trans version 6.14 (release 700 - 28.07.08 - 15:52:00).
unicode enabled version
R3trans finished (0000).
8.更新数据字典视图：
升级以后数据字典的一些基表的内容修改了,catalog.sql和catproc.sql这2个脚本需要在运行一下把数据字典视图在更新一下.
PRDT / # su - oraprt
PRDT:oraprt 1> sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 17 23:41:33 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> connect /as sysdba
Connected.
SQL>STARTUP UPGRADE
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
9.正常启动Sap，完成Oracle patch和oracle client的安装。
Data File
SELECT owner || '.' || object_name, SUM (VALUE)
FROM v$segment_statistics
WHERE statistic_name IN ('physical reads', 'physical writes')
AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
GROUP BY owner || '.' || object_name
ORDER BY 2 DESC;
-----------------------------
SELECT name "Datafile", phyrds "Okuma", phywrts "Yazma", phyrds + phywrts "Toplam IO" FROM V$DATAFILE, V$FILESTAT WHERE V$DATAFILE.file# = V$FILESTAT.file#;
http://www.gokhanatil.com/oracle-datafile-veri-dosyalari-istatistikleri/
FROM v$segment_statistics
WHERE statistic_name IN ('physical reads', 'physical writes')
AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
GROUP BY owner || '.' || object_name
ORDER BY 2 DESC;
-----------------------------
SELECT name "Datafile", phyrds "Okuma", phywrts "Yazma", phyrds + phywrts "Toplam IO" FROM V$DATAFILE, V$FILESTAT WHERE V$DATAFILE.file# = V$FILESTAT.file#;
http://www.gokhanatil.com/oracle-datafile-veri-dosyalari-istatistikleri/
Tablespace Monitoring -3
Step by step: How to extend tablespace for DB2 on AIX
The following steps are used to resolve full table space issue without having to re-create your database:
* 1. Find out which file containers are used for that tablespace (fully used)
* 2. Do you have enough space on the current file systems?
* 3. Increase the size for volume group
* 4. Increase size for logical volumes
* 5. Increase size for file systems
* 6. And finally, increase DB tablespace!
As your database tables grow, your originally planned disk space may simply run out. This is seen when you get the following error from DB2 db2diag.log (for both DB2 V8 and V9):
2007-08-29-13.02.27.267969-420 E14615A1054 LEVEL: Error
PID : 3743818 TID : 1 PROC : db2agent (PWTEST01) 0
INSTANCE: db2inst1 NODE : 000 DB : PWTEST01
APPHDL : 0-662 APPID: 10.0.4.50.54736.070829194050
AUTHID : FNRUN
FUNCTION: DB2 UDB, buffer pool services, sqlbAllocateExtent, probe:830
MESSAGE : ADM6044E The DMS table space "L002" (ID "105") is full. If this is
an autoresize or automatic storage DMS tablespace, the maximum table
space size may have been reached or the existing containers or
storage paths cannot grow any more. Additional space can be added to
the table space by either adding new containers or extending existing
ones using the ALTER TABLESPACE SQL statement. If this is an
autoresize or automatic storage DMS table space, additional space can
be added by adding containers to an autoresize table space or by
adding new storage paths to an automatic storage database.
You can alter tablespace (as recommended by the error message) to increase its size, but you may simply run out of disk space that the tablespace resides on. Many volume managers and file systems allow you to extend disk space as well, and that is what you have to do. The following steps apply to DB2 and AIX, but the process can work for other databases and operating systems as well. We expect you to replace commands used when working on other platforms.
1. Find out which file containers are used for that tablespace (which is full):
Normally this is done by search for the name of the tablespace in your
database definition script. Or if you don't have that (because the application
created it during installation), you can use the db2look command to dump
that to a file. Then do a grep on the name of the tablespace:
bash-3.00# grep L002 *
dbcreate.ddl:CREATE LARGE TABLESPACE L002 IN DATABASE PARTITION
dbcreate.ddl: USING (FILE '/db/a33/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a34/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a35/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a36/pwtest01/L002' 250M)
so we found the files used.
2. Do you have enough space on the current file systems?
# df -k
/dev/lv33 10256384 2387712 77% 27 1% /db/a33
/dev/lv34 10256384 2387704 77% 28 1% /db/a34
/dev/lv35 10256384 2387712 77% 27 1% /db/a35
/dev/lv36 10256384 2387704 77% 28 1% /db/a36
77% used. 10GB per file system, leaving about 230MB free space per.
You can check what else is on each of the file system:
bash-3.00# ls -l /db/a33/pwtest01/
total 15654912
-rw-rw-rw- 1 db2inst1 db2admi 0 Jul 24 02:42 .SQLCRT.FLG
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L001
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L002
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L003
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L004
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L005
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L006
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U027
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U029
3. Increase the size for volume group
If you think you need to extend tablespace size beyond 1GB (250Mx4), then you must increase
the file system (77% used already). But first, you need to extend size for logical volume,
but before you can do that, you need to extend size for the volume group that the logical
volume resides in ( we will use lv33 as an example, you just repeat the same for lv34,35,36).
First, find out the sizes for the volume group vg33:
bash-3.00# lsvg vg33
VOLUME GROUP: vg33 VG IDENTIFIER: 00ce3a8a00004c0000000113f77641d1
VG STATE: active PP SIZE: 32 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 319 (10208 megabytes)
MAX LVs: 256 FREE PPs: 5 (160 megabytes)
LVs: 2 USED PPs: 314 (10048 megabytes)
OPEN LVs: 2 QUORUM: 2
TOTAL PVs: 1 VG DESCRIPTORS: 2
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 1 AUTO ON: yes
MAX PPs per VG: 32512
MAX PPs per PV: 1016 MAX PVs: 32
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
So vg33 is only 10GB. needs to extend that; Let's see what hdisks are in vg33:
# datapath query device
...
DEV#: 33 DEVICE NAME: vpath33 TYPE: 2107900 POLICY: Optimized
SERIAL: 75FABV1103D
===========================================================================
Path# Adapter/Hard Disk State Mode Select Errors
0 fscsi0/hdisk39 OPEN NORMAL 6735259 0
1 fscsi1/hdisk79 OPEN NORMAL 6743800 0
So if vpath33 offers 10GB space, then we need to add more vpath to the vg so
that it gets more hdisks.
Performce vg extend task in smitty (assume you run on AIX):
___________________________________________________________________________
Force the creation of a volume group? no
* VOLUME GROUP name [vg33]
* PHYSICAL VOLUME names []
PHYSICAL VOLUME names
Move cursor to desired item and press F7.
ONE OR MORE items can be selected.
Press Enter AFTER making all selections.
> vpath40
> vpath41
> vpath42
___________________________________________________________________________
This adds 3 more vpaths (here we used the same size: 10GB each vpath) to get
an additional 30GB more disk space.
4. Increase size for logical volumes
lv33 on vg33 now can be extended. Note: if your logical volume (lv) has a
MAX LOGICAL PARTITIONS setting, you may need to increase it so that addtional
partitions can be created on the new disks. Otherwise, you will be capped
at the old size. For example, you may need to change MAX LOGICAL PARTITIONS from
512 to 1024 (done in smitty).
To increase lv size, simply do in smitty the following:
Increase the Size of a Logical Volume
Type or select values in entry fields.
Press Enter AFTER making all desired changes.
[Entry Fields]
* LOGICAL VOLUME name lv34
* Number of ADDITIONAL logical partitions [939] <=== new size!
PHYSICAL VOLUME names [vpath43 vpath44 vpath45] <== new disks!
POSITION on physical volume middle
RANGE of physical volumes minimum
MAXIMUM NUMBER of PHYSICAL VOLUMES [32]
to use for allocation
Allocate each logical partition copy yes
on a SEPARATE physical volume?
File containing ALLOCATION MAP []
Note: 939 is "additional" partitions. The final number of partitions will be 313+939.
This is now 40GB.
5. Increase size for file systems
Now that lv33 is increased, it is time to increase the file system size. Also do this in
smitty.
Type or select values in entry fields.
Press Enter AFTER making all desired changes.
[Entry Fields]
File system name /db/a33
NEW mount point [/db/a33]
SIZE of file system
Unit Size 512bytes
Number of units [20512768]
Mount GROUP []
Mount AUTOMATICALLY at system restart? yes
PERMISSIONS read/write
Mount OPTIONS []
Start Disk Accounting? no
Fragment Size (bytes) 4096
Number of bytes per inode 32768
Compression algorithm no
Large File Enabled true
Allocation Group Size (MBytes) 16
Some calculations here for clarity:
Number of unites, 20512768*512 = 10G
4 times that yields 20512768*4 = 82051072 (this should be 40GB).
Command: OK stdout: yes stderr: no
Before command completion, additional instructions may appear below.
Filesystem size changed to 82051072
After the above commands runs successfully, check the size of the file system:
$ df -k
/dev/lv33 41025536 33034296 20% 27 1% /db/a33
/dev/lv34 10256384 2387704 77% 28 1% /db/a34
/dev/lv35 10256384 2387712 77% 27 1% /db/a35
/dev/lv36 10256384 2387704 77% 28 1% /db/a36
Now /db/a33 is 40GB!
Repeat for 34,35 & 36. You should get to:
/dev/lv33 41025536 33034296 20% 27 1% /db/a33
/dev/lv34 41025536 33034288 20% 28 1% /db/a34
/dev/lv35 41025536 33034296 20% 27 1% /db/a35
/dev/lv36 41025536 33034288 20% 28 1% /db/a36
6. And finally, increase DB tablespace!
. ~db2inst1/sqllib/db2profile
db2 connect to pwtest01 user pwtest using pwtest1
db2 "ALTER TABLESPACE L002 RESIZE (ALL 2000 M)"
(this increases L002 from 1GB to 8GB, because ALL extends all 4 file containers)
Now, take a look at the new file containers for L002:
bash-3.00# ls -l /db/a33/pwtest01/
total 19238912
-rw-rw-rw- 1 db2inst1 db2admi 0 Jul 24 02:42 .SQLCRT.FLG
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L001
-rw------- 1 db2inst1 db2admi 2097152000 Aug 29 15:58 L002 <== new size!!
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L003
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L004
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L005
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L006
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 15:57 U027
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U029
Your database should be happy now!
http://www.performancewiki.com/extend-db-tablespace.html
The following steps are used to resolve full table space issue without having to re-create your database:
* 1. Find out which file containers are used for that tablespace (fully used)
* 2. Do you have enough space on the current file systems?
* 3. Increase the size for volume group
* 4. Increase size for logical volumes
* 5. Increase size for file systems
* 6. And finally, increase DB tablespace!
As your database tables grow, your originally planned disk space may simply run out. This is seen when you get the following error from DB2 db2diag.log (for both DB2 V8 and V9):
2007-08-29-13.02.27.267969-420 E14615A1054 LEVEL: Error
PID : 3743818 TID : 1 PROC : db2agent (PWTEST01) 0
INSTANCE: db2inst1 NODE : 000 DB : PWTEST01
APPHDL : 0-662 APPID: 10.0.4.50.54736.070829194050
AUTHID : FNRUN
FUNCTION: DB2 UDB, buffer pool services, sqlbAllocateExtent, probe:830
MESSAGE : ADM6044E The DMS table space "L002" (ID "105") is full. If this is
an autoresize or automatic storage DMS tablespace, the maximum table
space size may have been reached or the existing containers or
storage paths cannot grow any more. Additional space can be added to
the table space by either adding new containers or extending existing
ones using the ALTER TABLESPACE SQL statement. If this is an
autoresize or automatic storage DMS table space, additional space can
be added by adding containers to an autoresize table space or by
adding new storage paths to an automatic storage database.
You can alter tablespace (as recommended by the error message) to increase its size, but you may simply run out of disk space that the tablespace resides on. Many volume managers and file systems allow you to extend disk space as well, and that is what you have to do. The following steps apply to DB2 and AIX, but the process can work for other databases and operating systems as well. We expect you to replace commands used when working on other platforms.
1. Find out which file containers are used for that tablespace (which is full):
Normally this is done by search for the name of the tablespace in your
database definition script. Or if you don't have that (because the application
created it during installation), you can use the db2look command to dump
that to a file. Then do a grep on the name of the tablespace:
bash-3.00# grep L002 *
dbcreate.ddl:CREATE LARGE TABLESPACE L002 IN DATABASE PARTITION
dbcreate.ddl: USING (FILE '/db/a33/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a34/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a35/pwtest01/L002' 250M,
dbcreate.ddl: FILE '/db/a36/pwtest01/L002' 250M)
so we found the files used.
2. Do you have enough space on the current file systems?
# df -k
/dev/lv33 10256384 2387712 77% 27 1% /db/a33
/dev/lv34 10256384 2387704 77% 28 1% /db/a34
/dev/lv35 10256384 2387712 77% 27 1% /db/a35
/dev/lv36 10256384 2387704 77% 28 1% /db/a36
77% used. 10GB per file system, leaving about 230MB free space per.
You can check what else is on each of the file system:
bash-3.00# ls -l /db/a33/pwtest01/
total 15654912
-rw-rw-rw- 1 db2inst1 db2admi 0 Jul 24 02:42 .SQLCRT.FLG
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L001
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L002
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L003
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L004
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L005
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L006
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U027
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U029
3. Increase the size for volume group
If you think you need to extend tablespace size beyond 1GB (250Mx4), then you must increase
the file system (77% used already). But first, you need to extend size for logical volume,
but before you can do that, you need to extend size for the volume group that the logical
volume resides in ( we will use lv33 as an example, you just repeat the same for lv34,35,36).
First, find out the sizes for the volume group vg33:
bash-3.00# lsvg vg33
VOLUME GROUP: vg33 VG IDENTIFIER: 00ce3a8a00004c0000000113f77641d1
VG STATE: active PP SIZE: 32 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 319 (10208 megabytes)
MAX LVs: 256 FREE PPs: 5 (160 megabytes)
LVs: 2 USED PPs: 314 (10048 megabytes)
OPEN LVs: 2 QUORUM: 2
TOTAL PVs: 1 VG DESCRIPTORS: 2
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 1 AUTO ON: yes
MAX PPs per VG: 32512
MAX PPs per PV: 1016 MAX PVs: 32
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
So vg33 is only 10GB. needs to extend that; Let's see what hdisks are in vg33:
# datapath query device
...
DEV#: 33 DEVICE NAME: vpath33 TYPE: 2107900 POLICY: Optimized
SERIAL: 75FABV1103D
===========================================================================
Path# Adapter/Hard Disk State Mode Select Errors
0 fscsi0/hdisk39 OPEN NORMAL 6735259 0
1 fscsi1/hdisk79 OPEN NORMAL 6743800 0
So if vpath33 offers 10GB space, then we need to add more vpath to the vg so
that it gets more hdisks.
Performce vg extend task in smitty (assume you run on AIX):
___________________________________________________________________________
Force the creation of a volume group? no
* VOLUME GROUP name [vg33]
* PHYSICAL VOLUME names []
PHYSICAL VOLUME names
Move cursor to desired item and press F7.
ONE OR MORE items can be selected.
Press Enter AFTER making all selections.
> vpath40
> vpath41
> vpath42
___________________________________________________________________________
This adds 3 more vpaths (here we used the same size: 10GB each vpath) to get
an additional 30GB more disk space.
4. Increase size for logical volumes
lv33 on vg33 now can be extended. Note: if your logical volume (lv) has a
MAX LOGICAL PARTITIONS setting, you may need to increase it so that addtional
partitions can be created on the new disks. Otherwise, you will be capped
at the old size. For example, you may need to change MAX LOGICAL PARTITIONS from
512 to 1024 (done in smitty).
To increase lv size, simply do in smitty the following:
Increase the Size of a Logical Volume
Type or select values in entry fields.
Press Enter AFTER making all desired changes.
[Entry Fields]
* LOGICAL VOLUME name lv34
* Number of ADDITIONAL logical partitions [939] <=== new size!
PHYSICAL VOLUME names [vpath43 vpath44 vpath45] <== new disks!
POSITION on physical volume middle
RANGE of physical volumes minimum
MAXIMUM NUMBER of PHYSICAL VOLUMES [32]
to use for allocation
Allocate each logical partition copy yes
on a SEPARATE physical volume?
File containing ALLOCATION MAP []
Note: 939 is "additional" partitions. The final number of partitions will be 313+939.
This is now 40GB.
5. Increase size for file systems
Now that lv33 is increased, it is time to increase the file system size. Also do this in
smitty.
Type or select values in entry fields.
Press Enter AFTER making all desired changes.
[Entry Fields]
File system name /db/a33
NEW mount point [/db/a33]
SIZE of file system
Unit Size 512bytes
Number of units [20512768]
Mount GROUP []
Mount AUTOMATICALLY at system restart? yes
PERMISSIONS read/write
Mount OPTIONS []
Start Disk Accounting? no
Fragment Size (bytes) 4096
Number of bytes per inode 32768
Compression algorithm no
Large File Enabled true
Allocation Group Size (MBytes) 16
Some calculations here for clarity:
Number of unites, 20512768*512 = 10G
4 times that yields 20512768*4 = 82051072 (this should be 40GB).
Command: OK stdout: yes stderr: no
Before command completion, additional instructions may appear below.
Filesystem size changed to 82051072
After the above commands runs successfully, check the size of the file system:
$ df -k
/dev/lv33 41025536 33034296 20% 27 1% /db/a33
/dev/lv34 10256384 2387704 77% 28 1% /db/a34
/dev/lv35 10256384 2387712 77% 27 1% /db/a35
/dev/lv36 10256384 2387704 77% 28 1% /db/a36
Now /db/a33 is 40GB!
Repeat for 34,35 & 36. You should get to:
/dev/lv33 41025536 33034296 20% 27 1% /db/a33
/dev/lv34 41025536 33034288 20% 28 1% /db/a34
/dev/lv35 41025536 33034296 20% 27 1% /db/a35
/dev/lv36 41025536 33034288 20% 28 1% /db/a36
6. And finally, increase DB tablespace!
. ~db2inst1/sqllib/db2profile
db2 connect to pwtest01 user pwtest using pwtest1
db2 "ALTER TABLESPACE L002 RESIZE (ALL 2000 M)"
(this increases L002 from 1GB to 8GB, because ALL extends all 4 file containers)
Now, take a look at the new file containers for L002:
bash-3.00# ls -l /db/a33/pwtest01/
total 19238912
-rw-rw-rw- 1 db2inst1 db2admi 0 Jul 24 02:42 .SQLCRT.FLG
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L001
-rw------- 1 db2inst1 db2admi 2097152000 Aug 29 15:58 L002 <== new size!!
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L003
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L004
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L005
-rw------- 1 db2inst1 db2admi 262144000 Aug 29 13:13 L006
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 15:57 U027
-rw------- 1 db2inst1 db2admi 3221225472 Aug 29 13:13 U029
Your database should be happy now!
http://www.performancewiki.com/extend-db-tablespace.html
Tablespace Monitoring -2
select b.tablespace, b.segfile#, b.segblk#,
round(((b.blocks*p.value)/1024/1024),2) size_mb,
a.sid, a.serial#, a.username, a.osuser, a.program, a.status
from v$session a, v$sort
_usage b, v$process c, v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr
and a.paddr=c.addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
-----------------------------------------------------
http://www.olympos.org/belgeler/veritabani/oracle-veritabaninda-veri-ve-sistem-guvenligi-5348.html
--------------------------------------------------------
Oracle'da Hangi Session Ne Kadar Temp Kullanıyor
Aşağıdaki sorgu TEMPORARY tablespace'i kullanan sessionları ve bu sessionların ne kadar temporary alan kullandığının bilgisini verir(MB cinsinden)
select b.tablespace, b.segfile#, b.segblk#,
round(((b.blocks*p.value)/1024/1024),2) size_mb,
a.sid, a.serial#, a.username, a.osuser, a.program, a.status
from v$session a, v$sort
_usage b, v$process c, v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr
and a.paddr=c.addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
Aşağıdaki sorgu da temporary tablespace'in her bir seferde kaç Kilobayt extent aldığını, toplam alacağı extent sayısını, o an alınmış extent sayısını, boştaki extent sayısını,kullanılmış maximum extent sayısını verir
SELECT tablespace_name,extent
_size, total_extents, used_extents,free_extents, max_used_size FROM v$sort_segment;
-------------------------------------------------------
Oracle Tablespaces
Version 11.1
General
Data Dictionary Objects
Tablespaces ts$
dba_tablespaces
user_tablespaces
Tablespace Quotas tsq$
dba_ts_quotas
user_ts_quotas
Data Files dba_data_files
v_$backup_datafile
v_$datafile
v_$datafile_copy
v_$datafile_header
Free Space dba_free_space
Segments dba_segments
v_$segment_statistics
Extents dba_extents
Blocks v_$database_block_corruption
Groups dba_tablespace_groups
SYSAUX Tablespace v_$sysaux_occupants
Temp Tablespace dba_temp_files
Undo Tablespace dba_rollback_segs
dba_undo_extents
v_$rollstat
v_$undostat
Transportable Tablespaces transport_set_violations
Dictionary Management fet$
uet$
System Privileges
alter tablespace drop tablespace unlimited tablespace
create tablespace manage tablespace
GRANT create tablespace TO uwclass;
GRANT alter tablespace TO uwclass;
GRANT drop tablespace TO uwclass;
GRANT manage tablespace TO uwclass;
GRANT unlimited tablespace TO uwclass;
Permanent Tablespace
Permanent Tablespace On A File System Without Auto-extend CREATE [] TABLESPACE
DATAFILE ''
SIZE [REUSE] AUTOEXTEND
BLOCKSIZE
[]
[FORCE LOGGING]
[ENCRYPTION USING ''
IDENTIFIED BY [NO] SALT]
[DEFAULT < COMPRESS [FOR OPERATIONS] | NOCOMPRESS>]
[]
EXTENT MANAGEMENT LOCAL < AUTOALLOCATE | UNIFORM SIZE>
[SEGMENT SPACE MANAGEMENT]
[FLASHBACK];
CREATE TABLESPACE uwdata DATAFILE
'c:\oracle\oradata\orabase\uwdata01.dbf' SIZE 150M,
'c:\oracle\oradata\orabase\uwdata02.dbf' SIZE 100M
AUTOEXTEND OFF
BLOCKSIZE 8192
FORCE LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
FLASHBACK ON;
desc dba_tablespaces
set linesize 121
col tablespace_name format a20
SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_in, segment_space_management
FROM dba_tablespaces;
desc dba_data_files
col file_name format a45
SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by
FROM dba_data_files;
SELECT dbms_metadata.get_ddl('TABLESPACE', 'UWDATA')
FROM dual;
Permanent Tablespace On A Raw Device CREATE TABLESPACE
DATAFILE ''
SIZE
BLOCKSIZE
AUTOEXTEND OFF
EXTENT MANAGEMENT UNIFORM LOCAL SIZE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE tools LOGGING
DATAFILE '/u01/oradata/' SIZE 1024M
BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
Oracle Managed Permanent Auto-extendable Tablespace CREATE TABLESPACE;
ALTER SYSTEM SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';
CREATE TABLESPACE user_data;
Oracle Managed Permanent Fixed Size Tablespace CREATE TABLESPACE
DATAFILE AUTOEXTEND OFF;
ALTER SYSTEM
SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';
CREATE TABLESPACE user_data DATAFILE AUTOEXTEND OFF;
Set default tablespace type ALTER DATABASE SET DEFAULT TABLESPACE;
ALTER DATABASE DEFAULT BIGFILE TABLESPACE;
set linesize 121
col value$ format a20
col comment$ format a60
SELECT *
FROM props$
WHERE name LIKE '%DEF%'
ORDER BY by name;
Set tablespace as the default ALTER DATABASE DEFAULT TABLESPACE;
ALTER DATABASE DEFAULT TABLESPACE uwdata;
set linesize 121
col value$ format a20
col comment$ format a60
SELECT *
FROM props$
WHERE name LIKE '%DEF%'
ORDER BY by name;
SYSAUX Tablespace
Create SYSAUX Tablespace SELECT tablespace_name
FROM dba_tablespaces;
CREATE TABLESPACE sysaux
DATAFILE '/u01/oradata/sysaux01.dbf' SIZE 700M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Move SYSAUX Contents -- Move must be done using the indicated procedure
col occupant_name format a30
col schema_name format a30
col move_procedure format a50
SELECT occupant_name, schema_name, move_procedure
FROM v_$sysaux_occupants
ORDER BY 1;
Undo Tablespace
Create An UNDO Tablespace CREATE UNDO TABLESPACE
DATAFILE ''
SIZE
AUTOEXTEND
RETENTION;
CREATE UNDO TABLESPACE undotbs02
DATAFILE '/u01/oradata/undotbs02.dbf
SIZE 50000M REUSE AUTOEXTEND ON
RETENTION NOGUARANTEE;
desc dba_undo_extents
SELECT segment_name, tablespace_name, status, SUM(bytes)
FROM dba_undo_extents
GROUP BY segment_name, tablespace_name, status;
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' scope=BOTH;
Change The Current UNDO Tablespace -- identify existing tablespaces
SELECT tablespace_name
FROM dba_tablespaces
ORDER BY 1;
-- format SQL*Plus display
col name format a30
col value format a30
-- check current undo related parameters
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';
-- create the new undo tablespace
CREATE UNDO TABLESPACE testundo
DATAFILE '/u03/oradata/testundo.dbf'
SIZE 100M REUSE AUTOEXTEND ON
RETENTION NOGUARANTEE;
-- note that while it was created it is not being used
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';
-- switch undo tablespaces
ALTER SYSTEM SET undo_tablespace = TESTUNDO SCOPE=BOTH;
-- verify the change has been performed
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';
-- remove the old file
DROP TABLESPACE < old_tablespace's_name> INCLUDING CONTENTS AND DATAFILES;
Temporary Tablespaces
Create Temporary Tablespace CREATE TEMPORARY TABLESPACE
TEMPFILE ''
SIZE
AUTOEXTEND
TABLESPACE GROUP
EXTENT MANAGEMENT LOCAL UNIFORM SIZE;
Note:
You cannot use the ALTER TABLESPACE statement, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace.
CREATE TEMPORARY TABLESPACE temp_new
TEMPFILE '/u01/oradata/tempnew01.dbf'
SIZE 1G AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_in
FROM dba_tablespaces;
desc dba_temp_files
SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by
FROM dba_temp_files;
Add Tempfile ALTER TABLESPACE
ADD TEMPFILE '' SIZE M;
ALTER TABLESPACE temp_new
ADD TEMPFILE '/u02/oradata/tempnew02.dbf' SIZE 200M;
Resize Tempfile ALTER DATABASE TEMPFILE ''
RESIZEM;
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' RESIZE 250M;
Drop Tempfile ALTER DATABASE TEMPFILE '' DROP;
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' DROP;
Take Temporary Tablespace Off-line ALTER DATABASE TEMPFILE '' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' OFFLINE;
Place Temporary Tablespace On-line ALTER DATABASE TEMPFILE '' ONLINE;
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' ONLINE;
Changing the Default Temporary Tablespace ALTER DATABASE DEFAULT TEMPORARY TABLESPACE;
col property_value format a30
col description format a55
SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
SELECT file_name, tablespace_name
FROM dba_temp_files;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempnew;
DROP TABLESPACE temp;
Transportable Tablespaces
Determine Transportability dbms_tts.transport_set_check(
tablespace_name IN VARCHAR2, TRUE);
dbms_tts.transport_set_check('uwdata', TRUE);
View Violations; If Any SELECT * FROM TRANSPORT_SET_VIOLATIONS;
Generate A Transportable Set ALTER TABLESPACE READ ONLY;
ALTER TABLESPACE tools READ ONLY;
Export Tablespace
Although the Export utility is used, only data dictionary structural information (metadata) for the tablespaces is exported. EXP TRANSPORT_TABLESPACE=Y
TABLESPACES=()
TRIGGERS=Y CONSTRAINTS=N GRANTS=N FILE=
EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2)
TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=expdat.dmp
Import Tablespace IMP TRANSPORT_TABLESPACE= FILE=
DATAFILES=(')
TABLESPACES=()
TTS_OWNERS=()
FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)
IMP TRANSPORT_TABLESPACE=y FILE=expdat.dmp
DATAFILES=('/db/sales_jan','/db/sales_feb')
TABLESPACES=(sales_1,sales_2) TTS_OWNERS=(dcranney,jfee)
FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)
Import Parameter File TRANSPORT_TABLESPACE=y
FILE=expdat.dmp
DATAFILES=('/db/sales_jan','/db/sales_feb')
TABLESPACES=(sales_1, sales_2)
TTS_OWNERS=(dcranney, jfee)
FROMUSER=(dcranney, jfee)
TOUSER=(smith, williams)
Alter Permanent Tablespace
Add Datafile ALTER TABLESPACE ADD DATAFILE,
[REUSE]
NEXT
MAXSIZE
ALTER TABLESPACE tools ADD DATAFILE
'/u02/oracle/oradata/uwdata02.dbf' SIZE 25M
AUTOEXTEND OFF;
ALTER TABLESPACE tools ADD ADD DATAFILE
'c:\oracle\product\oradata\tools99.xxx' SIZE 10M
AUTOEXTEND ON;
Take Off-line ALTER TABLESPACE OFFLINE;
ALTER TABLESPACE tools OFFLINE;
Place On-line ALTER TABLESPACE ONLINE;
ALTER TABLESPACE tools ONLINE;
Make Read Only ALTER TABLESPACE READ ONLY;
ALTER TABLESPACE tools READ ONLY;
SELECT tablespace_name, status
FROM dba_tablespaces;
-- READ ONLY prevents DML ... not DDL including DROP and TRUNCATE.
Make A Tablespace Read Write ALTER TABLESPACE READ WRITE;
ALTER TABLESPACE tools READ WRITE;
SELECT tablespace_name, status
FROM dba_tablespaces;
Prepare Tablespace For Backup (archive logging must be active) ALTER TABLESPACE BEGIN BACKUP;
ALTER TABLESPACE tools BEGIN BACKUP;
End Tablespace Backup ALTER TABLESPACE END BACKUP;
ALTER TABLESPACE tools END BACKUP;
Rename ALTER TABLESPACE RENAME TO ;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT table_name
FROM dba_tables
WHERE tablespace_name = 'USERS';
ALTER TABLESPACE users RENAME TO user_data;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT table_name
FROM dba_tables
WHERE tablespace_name = 'USER_DATA';
Alter Undo Tablespace
Retention Guarantee ALTER TABLESPACE RETENTION ;
SELECT tablespace_name, retention
FROM dba_tablespaces;
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
select tablespace_name, retention
from dba_tablespaces;
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
select tablespace_name, retention
from dba_tablespaces;
Drop Tablespace
Drop Tablespace DROP TABLESPACE;
DROP TABLESPACE tools;
Drop Tablespace Including Contents DROP TABLESPACE
INCLUDING CONTENTS;
DROP TABLESPACE tools INCLUDING CONTENTS;
Drop Tablespace Including Contents & Datafiles DROP TABLESPACE
INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES;
Drop Tablespace Including Contents & Datafiles When There Are Referential Constraints DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;
DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Drop tablespace after datafile was accidentally dropped conn / as sysdba
CREATE TABLESPACE badidea
DATAFILE 'c:\temp\badidea.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT file_name
FROM dba_data_files;
shutdown immediate;
-- in an operating system window drop the file c:\temp\badidea.dbf
startup
-- record the error message
shutdown immediate;
startup mount;
alter database datafile 'c:\temp\badidea.dbf' offline drop;
alter database open;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT file_name
FROM dba_data_files;
drop tablespace badidea including contents;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT file_name
FROM dba_data_files;
Mandatory Tablespaces
System (must be named SYSTEM) - all versions
Always named system every Oracle instance must have one, and only one system tablespace. This tablespace contains the Oracle data dictionary tables and views. It is also where Oracle stores SQL programs such as stored procedures, functions, packages, and Java.
Undo (any name: the default is UNDOTSP1) - version 9i or above
Every Oracle instance must have one, and only one UNDO tablespace. The undo tablespace is a single large space into which Oracle stores and manager information for undo (rollback) and multi-versioning for all users and all transactions.
Temporary (any name but usually TEMP)
Every Oracle instance must have at least one temp tablespace and it can have any name. The default name is TEMP. The temp tablespace is used by Oracle to create temporary tables which it uses during processing of a request and for storing information for views and global temporary tables. Examples of transactions in which Oracle uses temp space are sorts and groupings.
One or more tablespaces for tables and indexes.
Every Oracle instance may have at least one and usually many tablespaces reserved for holding tables and indexes. These tablespaces can have any name but the default names are often like DATA01 and USERS.
It is advisable to spread I/O equally across multiple disks. And one way to accomplish this is to create separate tablespaces for tables and indexes and to store them on separate hard-drives if at all possible to improve system performance. In large systems it is usual to find each application stored in a separate tablespace and where tables of vastly different sizes are required to have tablespaces created to hold tables with small, medium, and large extents. By segregating tables by extent size into separate tablespaces it is possible to eliminate tablespace fragmentation which improves system performance and eliminates the wasting of disk space.
Definitions
The Number Of Extents - Dictionary vs. Locally Managed Tablespaces The number of extents has never been an issue for a segment. A table couldn't care less whether it is contained in 5 extents or 5000. But what does care is the data dictionary, since 5000 extents means 5000 rows in a data dictionary cluster that's been sized to expect no more than 5. That introduces cluster chaining in the data dictionary, and if the chaining is bad enough, then performance impacts involving dictionary access will be measurable.
Since LMTs don't touch the data dictionary for the purposes of recording extent acquisition, they do not suffer from cluster chaining, and do not experience a resulting performance degradation.
The performance issue also relates to the reading of the extent map in a single I/O instead of multiple I/Os ... since the extent map is stored inside an Oracle block, which is of finite size, too many extents mean that the map has to be stored in multiple blocks ... and any operation that needs to consult the extent map would therefore need multiple I/O operations to do so. You would really need thousands of extents, though, before that became a major issue).
The key advantage of LMTs is that they avoid a potential single point of contention on the data dictionary (but you'd have to have dozens of segments all simultaneously extending before that was an issue in the first place). And (better) they mean fragmentation is a thing of the past. And (the real killer) concern about a reasonable number of extents is wasted concern (reasonable being in the high hundreds to low thousands).
Logging Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. LOGGING is the default. This clause is not valid for a temporary or undo tablespace.
The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.
The force logging clause is used to force logging and to disregard no-logging instructions issued during object creation. You cannot specify FORCE LOGGING for an undo or temporary tablespace.
This clause only affects the logging of object creation and has no effect on logging of DML statements.
Segment Space Management When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:
MANUAL
Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.
MANUAL is the default.
AUTO
This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.
Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.
Automatic segment-space management delivers better space utilization than manual segment-space management, and it is self tuning in that it scales with increasing the number of users, as well as instances. For a Real Application Clusters environment, automatic segment-space management allows for a dynamic affinity of space to instances, thus avoiding the hard partitioning of space inherent with using free list groups.
For many standard workloads, application performance when using automatic segment space management is better than the performance of a well tuned application using manual segment-space management.
Related Queries
List tablespaces, their files, allocated space, free space, and next free extent clear breaks
set linesize 132
set pagesize 60
break on tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;
List datafiles, tablespace names, and size in MB col file_name format a50
col tablespace_name format a10
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;
List tablespaces, size, free space, and percent free
Thanks to Michael Lehmann for this query SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,
ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;
View For Schema Owner To Monitoring Free Space CREATE OR REPLACE VIEW freespace_view AS
SELECT tablespace_name, SUM(bytes/1024/1024) AVAILABLE
FROM user_free_space
GROUP BY tablespace_name;
Another Statement For
Tablespace Management set linesize 121
SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name;
Yet Another Statement For
Tablespace Management col tablespace_name format a15
col alloc_size format 999.999
col pct_used format 999.999
col free_space format 999.999
col maxnext format 999.999
col definitsz format 999.999
col defnextsz format 999.999
SELECT a.tablespace_name, a.datafile_sz, b.alloc_size,
(b.alloc_size)/a.datafile_sz*100 PCT_USED,
(a.datafile_sz-b.alloc_size) FREE_SPACE,
b.next_extent/1024/1024 MAXNEXT,
a.initial_extent/1024/1024 DEFINITSZ,
a.next_extent/1024/1024 DEFNEXTSZ
FROM (
SELECT a.tablespace_name, sum(b.bytes)/1024/1024
DATAFILE_SZ, a.initial_extent, a.next_extent
FROM dba_tablespaces a, dba_data_files b
WHERE a.tablespace_name = b.tablespace_name
GROUP BY a.tablespace_name, a.initial_extent, a.next_extent) A,
(
SELECT a.tablespace_name, sum(c.bytes)/1024/1024
ALLOC_SIZE, MAX(c.next_extent) NEXT_EXTENT
FROM dba_tablespaces a, dba_segments c
WHERE a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name) B
WHERE a.tablespace_name = b.tablespace_name (+)
ORDER BY 1;
And Yet Another Statement For Tablespace Management SELECT dfs.tablespace_name, ddf.total_size,
ddf.total_size - dfs.total_free TOTAL_USED,
dfs.total_free,
(ddf.total_size-dfs.total_free)/ddf.total_size * 100 CAP,
dfs.total_chunks, dfs.largest_chunk
FROM (
SELECT a.tablespace_name,
SUM(a.bytes)/1024/1024 TOTAL_FREE,
COUNT(a.bytes) TOTAL_CHUNKS,
MAX(a.bytes)/1024/1024 LARGEST_CHUNK
FROM dba_free_space a
GROUP BY a.tablespace_name) dfs,
(
SELECT b.tablespace_name,
SUM(b.bytes)/1024/1024 TOTAL_SIZE
FROM dba_data_files b
GROUP BY b.tablespace_name) ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
ORDER BY dfs.tablespace_name;
Calculation Of Minimum Tablespace Size (this takes a long time to run) SELECT SUBSTR(f.file_name,1,70) FILENAME,
MAX(e.block_id*(e.bytes/e.blocks)+e.bytes)/1024 MIN_SIZE
FROM dba_extents e, dba_data_files f
WHERE e.file_id = f.file_id
GROUP BY f.file_name;
Schemas In The SYSAUX Tablespace col occupant_name format a25
col schema_name format a20
col move_procedure format a30
col move_procedure_desc format a40
set linesize 131
SELECT occupant_name, schema_name, move_procedure, move_procedure_desc
FROM v_$sysaux_occupants;
Contiguous Space create table t_contig_space (
tablespace_name VARCHAR2(30),
file_id NUMBER,
block_id NUMBER,
starting_file_id NUMBER,
starting_block_id NUMBER,
blocks NUMBER,
bytes NUMBER)
tablespace uwdata;
CREATE OR REPLACE VIEW v_contig_space AS
SELECT SUBSTR(tablespace_name,1,20) TABLESPACE_NAME,
starting_file_id, starting_block_id, SUM(blocks) sum_blocks,
COUNT(blocks) count_blocks, MAX(blocks) max_blocks,
SUM(bytes)/1024/1024 SUM_MB
FROM tl_contig_space
GROUP BY tablespace_name, starting_file_id, starting_block_id;
DECLARE
CURSOR query IS
SELECT *
FROM dba_free_space
ORDER BY tablespace_name, file_id, block_id;
this_row query%ROWTYPE;
previous_row query%ROWTYPE;
old_file_id PLS_INTEGER;
old_block_id PLS_INTEGER;
BEGIN
OPEN query;
FETCH query INTO this_row;
previous_row := this_row;
old_file_id := previous_row.file_id;
old_block_id := previous_row.block_id;
WHILE query%FOUND loop
IF this_row.file_id = previous_row.file_id AND
this_row.block_id = previous_row.block_id+previous_row.blocks
THEN
INSERT INTO tl_contig_space
(tablespace_name, file_id, block_id, starting_file_id,
starting_block_id, blocks, bytes)
VALUES
(previous_row.tablespace_name, previous_row.file_id,
this_row.block_id, old_file_id, old_block_id, this_row.blocks,
this_row.bytes);
ELSE
INSERT INTO tl_contig_space
(tablespace_name, file_id, block_id, starting_file_id,
starting_block_id, blocks, bytes)
VALUES
(this_row.tablespace_name, this_row.file_id,
this_row.block_id, this_row.file_id, this_row.block_id,
this_row.blocks, this_row.bytes);
old_file_id := this_row.file_id;
old_block_id := this_row.block_id;
END IF;
previous_row := this_row;
FETCH query INTO this_row;
END LOOP;
COMMIT;
END;
/
col tablespace_name format a20
col sum_mb format 999.999
SELECT * FROM v_contig_space;
http://www.psoug.org/reference/tablespaces.html
round(((b.blocks*p.value)/1024/1024),2) size_mb,
a.sid, a.serial#, a.username, a.osuser, a.program, a.status
from v$session a, v$sort
_usage b, v$process c, v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr
and a.paddr=c.addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
-----------------------------------------------------
http://www.olympos.org/belgeler/veritabani/oracle-veritabaninda-veri-ve-sistem-guvenligi-5348.html
--------------------------------------------------------
Oracle'da Hangi Session Ne Kadar Temp Kullanıyor
Aşağıdaki sorgu TEMPORARY tablespace'i kullanan sessionları ve bu sessionların ne kadar temporary alan kullandığının bilgisini verir(MB cinsinden)
select b.tablespace, b.segfile#, b.segblk#,
round(((b.blocks*p.value)/1024/1024),2) size_mb,
a.sid, a.serial#, a.username, a.osuser, a.program, a.status
from v$session a, v$sort
_usage b, v$process c, v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr
and a.paddr=c.addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
Aşağıdaki sorgu da temporary tablespace'in her bir seferde kaç Kilobayt extent aldığını, toplam alacağı extent sayısını, o an alınmış extent sayısını, boştaki extent sayısını,kullanılmış maximum extent sayısını verir
SELECT tablespace_name,extent
_size, total_extents, used_extents,free_extents, max_used_size FROM v$sort_segment;
-------------------------------------------------------
Oracle Tablespaces
Version 11.1
General
Data Dictionary Objects
Tablespaces ts$
dba_tablespaces
user_tablespaces
Tablespace Quotas tsq$
dba_ts_quotas
user_ts_quotas
Data Files dba_data_files
v_$backup_datafile
v_$datafile
v_$datafile_copy
v_$datafile_header
Free Space dba_free_space
Segments dba_segments
v_$segment_statistics
Extents dba_extents
Blocks v_$database_block_corruption
Groups dba_tablespace_groups
SYSAUX Tablespace v_$sysaux_occupants
Temp Tablespace dba_temp_files
Undo Tablespace dba_rollback_segs
dba_undo_extents
v_$rollstat
v_$undostat
Transportable Tablespaces transport_set_violations
Dictionary Management fet$
uet$
System Privileges
alter tablespace drop tablespace unlimited tablespace
create tablespace manage tablespace
GRANT create tablespace TO uwclass;
GRANT alter tablespace TO uwclass;
GRANT drop tablespace TO uwclass;
GRANT manage tablespace TO uwclass;
GRANT unlimited tablespace TO uwclass;
Permanent Tablespace
Permanent Tablespace On A File System Without Auto-extend CREATE [
DATAFILE '
SIZE
BLOCKSIZE
[
[FORCE LOGGING]
[ENCRYPTION USING '
IDENTIFIED BY
[DEFAULT < COMPRESS [FOR
[
EXTENT MANAGEMENT LOCAL < AUTOALLOCATE | UNIFORM SIZE
[SEGMENT SPACE MANAGEMENT
[FLASHBACK
CREATE TABLESPACE uwdata DATAFILE
'c:\oracle\oradata\orabase\uwdata01.dbf' SIZE 150M,
'c:\oracle\oradata\orabase\uwdata02.dbf' SIZE 100M
AUTOEXTEND OFF
BLOCKSIZE 8192
FORCE LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
FLASHBACK ON;
desc dba_tablespaces
set linesize 121
col tablespace_name format a20
SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_in, segment_space_management
FROM dba_tablespaces;
desc dba_data_files
col file_name format a45
SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by
FROM dba_data_files;
SELECT dbms_metadata.get_ddl('TABLESPACE', 'UWDATA')
FROM dual;
Permanent Tablespace On A Raw Device CREATE TABLESPACE
DATAFILE '
SIZE
BLOCKSIZE
AUTOEXTEND OFF
EXTENT MANAGEMENT UNIFORM LOCAL SIZE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE tools LOGGING
DATAFILE '/u01/oradata/' SIZE 1024M
BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
Oracle Managed Permanent Auto-extendable Tablespace CREATE TABLESPACE
ALTER SYSTEM SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';
CREATE TABLESPACE user_data;
Oracle Managed Permanent Fixed Size Tablespace CREATE TABLESPACE
DATAFILE AUTOEXTEND OFF;
ALTER SYSTEM
SET DB_CREATE_FILE_DEST = $ORACLE_BASE/oradata';
CREATE TABLESPACE user_data DATAFILE AUTOEXTEND OFF;
Set default tablespace type ALTER DATABASE SET DEFAULT
ALTER DATABASE DEFAULT BIGFILE TABLESPACE;
set linesize 121
col value$ format a20
col comment$ format a60
SELECT *
FROM props$
WHERE name LIKE '%DEF%'
ORDER BY by name;
Set tablespace as the default ALTER DATABASE DEFAULT TABLESPACE
ALTER DATABASE DEFAULT TABLESPACE uwdata;
set linesize 121
col value$ format a20
col comment$ format a60
SELECT *
FROM props$
WHERE name LIKE '%DEF%'
ORDER BY by name;
SYSAUX Tablespace
Create SYSAUX Tablespace SELECT tablespace_name
FROM dba_tablespaces;
CREATE TABLESPACE sysaux
DATAFILE '/u01/oradata/sysaux01.dbf' SIZE 700M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Move SYSAUX Contents -- Move must be done using the indicated procedure
col occupant_name format a30
col schema_name format a30
col move_procedure format a50
SELECT occupant_name, schema_name, move_procedure
FROM v_$sysaux_occupants
ORDER BY 1;
Undo Tablespace
Create An UNDO Tablespace CREATE UNDO TABLESPACE
DATAFILE '
SIZE
AUTOEXTEND
RETENTION
CREATE UNDO TABLESPACE undotbs02
DATAFILE '/u01/oradata/undotbs02.dbf
SIZE 50000M REUSE AUTOEXTEND ON
RETENTION NOGUARANTEE;
desc dba_undo_extents
SELECT segment_name, tablespace_name, status, SUM(bytes)
FROM dba_undo_extents
GROUP BY segment_name, tablespace_name, status;
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' scope=BOTH;
Change The Current UNDO Tablespace -- identify existing tablespaces
SELECT tablespace_name
FROM dba_tablespaces
ORDER BY 1;
-- format SQL*Plus display
col name format a30
col value format a30
-- check current undo related parameters
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';
-- create the new undo tablespace
CREATE UNDO TABLESPACE testundo
DATAFILE '/u03/oradata/testundo.dbf'
SIZE 100M REUSE AUTOEXTEND ON
RETENTION NOGUARANTEE;
-- note that while it was created it is not being used
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';
-- switch undo tablespaces
ALTER SYSTEM SET undo_tablespace = TESTUNDO SCOPE=BOTH;
-- verify the change has been performed
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%undo%';
-- remove the old file
DROP TABLESPACE < old_tablespace's_name> INCLUDING CONTENTS AND DATAFILES;
Temporary Tablespaces
Create Temporary Tablespace CREATE TEMPORARY TABLESPACE
TEMPFILE '
SIZE
AUTOEXTEND
TABLESPACE GROUP
EXTENT MANAGEMENT LOCAL UNIFORM SIZE
Note:
You cannot use the ALTER TABLESPACE statement, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace.
CREATE TEMPORARY TABLESPACE temp_new
TEMPFILE '/u01/oradata/tempnew01.dbf'
SIZE 1G AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
SELECT tablespace_name, block_size, status, contents, retention, extent_management, allocation_type, plugged_in
FROM dba_tablespaces;
desc dba_temp_files
SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by
FROM dba_temp_files;
Add Tempfile ALTER TABLESPACE
ADD TEMPFILE '
ALTER TABLESPACE temp_new
ADD TEMPFILE '/u02/oradata/tempnew02.dbf' SIZE 200M;
Resize Tempfile ALTER DATABASE TEMPFILE '
RESIZE
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' RESIZE 250M;
Drop Tempfile ALTER DATABASE TEMPFILE '
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' DROP;
Take Temporary Tablespace Off-line ALTER DATABASE TEMPFILE '
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' OFFLINE;
Place Temporary Tablespace On-line ALTER DATABASE TEMPFILE '
ALTER DATABASE TEMPFILE '/u02/oradata/tempnew02.dbf' ONLINE;
Changing the Default Temporary Tablespace ALTER DATABASE DEFAULT TEMPORARY TABLESPACE
col property_value format a30
col description format a55
SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
SELECT file_name, tablespace_name
FROM dba_temp_files;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempnew;
DROP TABLESPACE temp;
Transportable Tablespaces
Determine Transportability dbms_tts.transport_set_check(
tablespace_name IN VARCHAR2, TRUE);
dbms_tts.transport_set_check('uwdata', TRUE);
View Violations; If Any SELECT * FROM TRANSPORT_SET_VIOLATIONS;
Generate A Transportable Set ALTER TABLESPACE
ALTER TABLESPACE tools READ ONLY;
Export Tablespace
Although the Export utility is used, only data dictionary structural information (metadata) for the tablespaces is exported. EXP TRANSPORT_TABLESPACE=Y
TABLESPACES=(
TRIGGERS=Y CONSTRAINTS=N GRANTS=N FILE=
EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2)
TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=expdat.dmp
Import Tablespace IMP TRANSPORT_TABLESPACE=
DATAFILES=('
TABLESPACES=(
TTS_OWNERS=(
FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)
IMP TRANSPORT_TABLESPACE=y FILE=expdat.dmp
DATAFILES=('/db/sales_jan','/db/sales_feb')
TABLESPACES=(sales_1,sales_2) TTS_OWNERS=(dcranney,jfee)
FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)
Import Parameter File TRANSPORT_TABLESPACE=y
FILE=expdat.dmp
DATAFILES=('/db/sales_jan','/db/sales_feb')
TABLESPACES=(sales_1, sales_2)
TTS_OWNERS=(dcranney, jfee)
FROMUSER=(dcranney, jfee)
TOUSER=(smith, williams)
Alter Permanent Tablespace
Add Datafile ALTER TABLESPACE ADD DATAFILE
[REUSE]
NEXT
MAXSIZE
ALTER TABLESPACE tools ADD DATAFILE
'/u02/oracle/oradata/uwdata02.dbf' SIZE 25M
AUTOEXTEND OFF;
ALTER TABLESPACE tools ADD ADD DATAFILE
'c:\oracle\product\oradata\tools99.xxx' SIZE 10M
AUTOEXTEND ON;
Take Off-line ALTER TABLESPACE
ALTER TABLESPACE tools OFFLINE;
Place On-line ALTER TABLESPACE
ALTER TABLESPACE tools ONLINE;
Make Read Only ALTER TABLESPACE
ALTER TABLESPACE tools READ ONLY;
SELECT tablespace_name, status
FROM dba_tablespaces;
-- READ ONLY prevents DML ... not DDL including DROP and TRUNCATE.
Make A Tablespace Read Write ALTER TABLESPACE
ALTER TABLESPACE tools READ WRITE;
SELECT tablespace_name, status
FROM dba_tablespaces;
Prepare Tablespace For Backup (archive logging must be active) ALTER TABLESPACE
ALTER TABLESPACE tools BEGIN BACKUP;
End Tablespace Backup ALTER TABLESPACE
ALTER TABLESPACE tools END BACKUP;
Rename ALTER TABLESPACE
SELECT tablespace_name
FROM dba_tablespaces;
SELECT table_name
FROM dba_tables
WHERE tablespace_name = 'USERS';
ALTER TABLESPACE users RENAME TO user_data;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT table_name
FROM dba_tables
WHERE tablespace_name = 'USER_DATA';
Alter Undo Tablespace
Retention Guarantee ALTER TABLESPACE
SELECT tablespace_name, retention
FROM dba_tablespaces;
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
select tablespace_name, retention
from dba_tablespaces;
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
select tablespace_name, retention
from dba_tablespaces;
Drop Tablespace
Drop Tablespace DROP TABLESPACE
DROP TABLESPACE tools;
Drop Tablespace Including Contents DROP TABLESPACE
INCLUDING CONTENTS;
DROP TABLESPACE tools INCLUDING CONTENTS;
Drop Tablespace Including Contents & Datafiles DROP TABLESPACE
INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES;
Drop Tablespace Including Contents & Datafiles When There Are Referential Constraints DROP TABLESPACE
CASCADE CONSTRAINTS;
DROP TABLESPACE tools INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Drop tablespace after datafile was accidentally dropped conn / as sysdba
CREATE TABLESPACE badidea
DATAFILE 'c:\temp\badidea.dbf' SIZE 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT file_name
FROM dba_data_files;
shutdown immediate;
-- in an operating system window drop the file c:\temp\badidea.dbf
startup
-- record the error message
shutdown immediate;
startup mount;
alter database datafile 'c:\temp\badidea.dbf' offline drop;
alter database open;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT file_name
FROM dba_data_files;
drop tablespace badidea including contents;
SELECT tablespace_name
FROM dba_tablespaces;
SELECT file_name
FROM dba_data_files;
Mandatory Tablespaces
System (must be named SYSTEM) - all versions
Always named system every Oracle instance must have one, and only one system tablespace. This tablespace contains the Oracle data dictionary tables and views. It is also where Oracle stores SQL programs such as stored procedures, functions, packages, and Java.
Undo (any name: the default is UNDOTSP1) - version 9i or above
Every Oracle instance must have one, and only one UNDO tablespace. The undo tablespace is a single large space into which Oracle stores and manager information for undo (rollback) and multi-versioning for all users and all transactions.
Temporary (any name but usually TEMP)
Every Oracle instance must have at least one temp tablespace and it can have any name. The default name is TEMP. The temp tablespace is used by Oracle to create temporary tables which it uses during processing of a request and for storing information for views and global temporary tables. Examples of transactions in which Oracle uses temp space are sorts and groupings.
One or more tablespaces for tables and indexes.
Every Oracle instance may have at least one and usually many tablespaces reserved for holding tables and indexes. These tablespaces can have any name but the default names are often like DATA01 and USERS.
It is advisable to spread I/O equally across multiple disks. And one way to accomplish this is to create separate tablespaces for tables and indexes and to store them on separate hard-drives if at all possible to improve system performance. In large systems it is usual to find each application stored in a separate tablespace and where tables of vastly different sizes are required to have tablespaces created to hold tables with small, medium, and large extents. By segregating tables by extent size into separate tablespaces it is possible to eliminate tablespace fragmentation which improves system performance and eliminates the wasting of disk space.
Definitions
The Number Of Extents - Dictionary vs. Locally Managed Tablespaces The number of extents has never been an issue for a segment. A table couldn't care less whether it is contained in 5 extents or 5000. But what does care is the data dictionary, since 5000 extents means 5000 rows in a data dictionary cluster that's been sized to expect no more than 5. That introduces cluster chaining in the data dictionary, and if the chaining is bad enough, then performance impacts involving dictionary access will be measurable.
Since LMTs don't touch the data dictionary for the purposes of recording extent acquisition, they do not suffer from cluster chaining, and do not experience a resulting performance degradation.
The performance issue also relates to the reading of the extent map in a single I/O instead of multiple I/Os ... since the extent map is stored inside an Oracle block, which is of finite size, too many extents mean that the map has to be stored in multiple blocks ... and any operation that needs to consult the extent map would therefore need multiple I/O operations to do so. You would really need thousands of extents, though, before that became a major issue).
The key advantage of LMTs is that they avoid a potential single point of contention on the data dictionary (but you'd have to have dozens of segments all simultaneously extending before that was an issue in the first place). And (better) they mean fragmentation is a thing of the past. And (the real killer) concern about a reasonable number of extents is wasted concern (reasonable being in the high hundreds to low thousands).
Logging Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. LOGGING is the default. This clause is not valid for a temporary or undo tablespace.
The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.
The force logging clause is used to force logging and to disregard no-logging instructions issued during object creation. You cannot specify FORCE LOGGING for an undo or temporary tablespace.
This clause only affects the logging of object creation and has no effect on logging of DML statements.
Segment Space Management When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:
MANUAL
Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.
MANUAL is the default.
AUTO
This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.
Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.
Automatic segment-space management delivers better space utilization than manual segment-space management, and it is self tuning in that it scales with increasing the number of users, as well as instances. For a Real Application Clusters environment, automatic segment-space management allows for a dynamic affinity of space to instances, thus avoiding the hard partitioning of space inherent with using free list groups.
For many standard workloads, application performance when using automatic segment space management is better than the performance of a well tuned application using manual segment-space management.
Related Queries
List tablespaces, their files, allocated space, free space, and next free extent clear breaks
set linesize 132
set pagesize 60
break on tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;
List datafiles, tablespace names, and size in MB col file_name format a50
col tablespace_name format a10
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;
List tablespaces, size, free space, and percent free
Thanks to Michael Lehmann for this query SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,
ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;
View For Schema Owner To Monitoring Free Space CREATE OR REPLACE VIEW freespace_view AS
SELECT tablespace_name, SUM(bytes/1024/1024) AVAILABLE
FROM user_free_space
GROUP BY tablespace_name;
Another Statement For
Tablespace Management set linesize 121
SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name;
Yet Another Statement For
Tablespace Management col tablespace_name format a15
col alloc_size format 999.999
col pct_used format 999.999
col free_space format 999.999
col maxnext format 999.999
col definitsz format 999.999
col defnextsz format 999.999
SELECT a.tablespace_name, a.datafile_sz, b.alloc_size,
(b.alloc_size)/a.datafile_sz*100 PCT_USED,
(a.datafile_sz-b.alloc_size) FREE_SPACE,
b.next_extent/1024/1024 MAXNEXT,
a.initial_extent/1024/1024 DEFINITSZ,
a.next_extent/1024/1024 DEFNEXTSZ
FROM (
SELECT a.tablespace_name, sum(b.bytes)/1024/1024
DATAFILE_SZ, a.initial_extent, a.next_extent
FROM dba_tablespaces a, dba_data_files b
WHERE a.tablespace_name = b.tablespace_name
GROUP BY a.tablespace_name, a.initial_extent, a.next_extent) A,
(
SELECT a.tablespace_name, sum(c.bytes)/1024/1024
ALLOC_SIZE, MAX(c.next_extent) NEXT_EXTENT
FROM dba_tablespaces a, dba_segments c
WHERE a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name) B
WHERE a.tablespace_name = b.tablespace_name (+)
ORDER BY 1;
And Yet Another Statement For Tablespace Management SELECT dfs.tablespace_name, ddf.total_size,
ddf.total_size - dfs.total_free TOTAL_USED,
dfs.total_free,
(ddf.total_size-dfs.total_free)/ddf.total_size * 100 CAP,
dfs.total_chunks, dfs.largest_chunk
FROM (
SELECT a.tablespace_name,
SUM(a.bytes)/1024/1024 TOTAL_FREE,
COUNT(a.bytes) TOTAL_CHUNKS,
MAX(a.bytes)/1024/1024 LARGEST_CHUNK
FROM dba_free_space a
GROUP BY a.tablespace_name) dfs,
(
SELECT b.tablespace_name,
SUM(b.bytes)/1024/1024 TOTAL_SIZE
FROM dba_data_files b
GROUP BY b.tablespace_name) ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
ORDER BY dfs.tablespace_name;
Calculation Of Minimum Tablespace Size (this takes a long time to run) SELECT SUBSTR(f.file_name,1,70) FILENAME,
MAX(e.block_id*(e.bytes/e.blocks)+e.bytes)/1024 MIN_SIZE
FROM dba_extents e, dba_data_files f
WHERE e.file_id = f.file_id
GROUP BY f.file_name;
Schemas In The SYSAUX Tablespace col occupant_name format a25
col schema_name format a20
col move_procedure format a30
col move_procedure_desc format a40
set linesize 131
SELECT occupant_name, schema_name, move_procedure, move_procedure_desc
FROM v_$sysaux_occupants;
Contiguous Space create table t_contig_space (
tablespace_name VARCHAR2(30),
file_id NUMBER,
block_id NUMBER,
starting_file_id NUMBER,
starting_block_id NUMBER,
blocks NUMBER,
bytes NUMBER)
tablespace uwdata;
CREATE OR REPLACE VIEW v_contig_space AS
SELECT SUBSTR(tablespace_name,1,20) TABLESPACE_NAME,
starting_file_id, starting_block_id, SUM(blocks) sum_blocks,
COUNT(blocks) count_blocks, MAX(blocks) max_blocks,
SUM(bytes)/1024/1024 SUM_MB
FROM tl_contig_space
GROUP BY tablespace_name, starting_file_id, starting_block_id;
DECLARE
CURSOR query IS
SELECT *
FROM dba_free_space
ORDER BY tablespace_name, file_id, block_id;
this_row query%ROWTYPE;
previous_row query%ROWTYPE;
old_file_id PLS_INTEGER;
old_block_id PLS_INTEGER;
BEGIN
OPEN query;
FETCH query INTO this_row;
previous_row := this_row;
old_file_id := previous_row.file_id;
old_block_id := previous_row.block_id;
WHILE query%FOUND loop
IF this_row.file_id = previous_row.file_id AND
this_row.block_id = previous_row.block_id+previous_row.blocks
THEN
INSERT INTO tl_contig_space
(tablespace_name, file_id, block_id, starting_file_id,
starting_block_id, blocks, bytes)
VALUES
(previous_row.tablespace_name, previous_row.file_id,
this_row.block_id, old_file_id, old_block_id, this_row.blocks,
this_row.bytes);
ELSE
INSERT INTO tl_contig_space
(tablespace_name, file_id, block_id, starting_file_id,
starting_block_id, blocks, bytes)
VALUES
(this_row.tablespace_name, this_row.file_id,
this_row.block_id, this_row.file_id, this_row.block_id,
this_row.blocks, this_row.bytes);
old_file_id := this_row.file_id;
old_block_id := this_row.block_id;
END IF;
previous_row := this_row;
FETCH query INTO this_row;
END LOOP;
COMMIT;
END;
/
col tablespace_name format a20
col sum_mb format 999.999
SELECT * FROM v_contig_space;
http://www.psoug.org/reference/tablespaces.html
Kaydol:
Kayıtlar (Atom)