The Madytos

fiziksel standby - incremental backup uygulama

2011-08-24T06:33:00.000-07:00

standby>SQL> select CURRENT_SCN from v$database;

CURRENT_SCN
-----------
3584975657

primary>SQL> select CURRENT_SCN from v$database;

CURRENT_SCN
-----------
3586326579

primary>backup incremental from scn 3584975657 database format '/oracle/yedek/standby_%U';

standby>catalog start with '/oracle/yedek/standby';

primary>alter database create standby controlfile as '/oracle/yedek/standby.ctl';

primmary>alter database register logfile '1_6696_746463756.dbf';

http://jarneil.wordpress.com/2008/06/03/applying-an-incremental-backup-to-a-physical-standby/

Redo log Dosyalarında Bozulma (ORA-00312)

2010-07-14T05:41:00.000-07:00

O an kullanılmayan (statusu current olmayan) Redo log dosyalarında bozulma olursa, Archiver redo log dosyasını kullanmaya çalıştığında durur ve veritabanı DML işlemini tamamlayamaz. Örnek bir redo log bozulması durumunda alert log’da göreceğimiz mesajlara bakalım:

Errors in file /oracle/diag/rdbms/oratest/oratest/trace/oratest_arc3_10426.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/oracle/oradata/oratest/redo01.log’
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
Bu durumda yapılması gereken hatalı olan redo logları sıfırlamaktır. Hata mesajlarında görüldüğü üzere 1. gruptaki redo log dosyasında (redo1.log) problem var. Bozulmuş olan redo log dosyasını aşağıdaki komutlar ile temizleyebilirsiniz:

ALTER DATABASE CLEAR UNARCHIVED LOGFILE ‘/oracle/oradata/oratest/redo1.log’;

veya

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
Alternatif olarak bu log dosyasını DROP edip yeniden oluşturabilirsiniz. Log file’ı temizlerken kullanılan UNARCHIVED parametresi, log file’ın henüz archivelenmediğini, yeniden kullanıldıktan sonra arşivleme yapılmasının istendiğini gösterir. Bu işlemden sonra archiever prosesi çalışmaya devam edecektir:

Completed: ALTER DATABASE CLEAR UNARCHIVED LOGFILE group 1
Wed May 26 22:50:43 2009
Archiver process freed from errors. No longer stopped
Wed May 26 22:50:50 2009
Starting background process SMCO
Wed May 26 22:50:50 2009
SMCO started with pid=15, OS id=16754
Wed May 26 22:52:41 2009
Thread 1 cannot allocate new log, sequence 14
Private strand flush not complete
Current log# 1 seq# 13 mem# 0: /oracle/oradata/oratest/redo01.log
Thread 1 advanced to log sequence 14 (LGWR switch)
Current log# 2 seq# 14 mem# 0: /oracle/oradata/oratest/redo02.log
Thread 1 cannot allocate new log, sequence 15
Checkpoint not complete
Archiver prosesi çalışmasına devam etmesine rağmen eğer “checkpoint not complete” uyarısı veriyorsa manuel olarak checkpoint yaptırmamız gerekir:

ALTER SYSTEM CHECKPOINT;
Eğer sorunlu olan redo log dosyası “o an kullanımda olan” (statusu current) redolog dosyası ise hata archiver yerine logwiter (LGWR) prosesi tarafından tespit edilecek ve veritabanı kapanacaktır. Yeniden açtığınız zaman veritabanı, redo log’un silinmesine izin vermez. Bu durumda recovery işlemi yapmanız gerekir:

RMAN> RESTORE DATABASE;
SQL> RECOVER DATABASE UNTIL CANCEL;
SQL> ALTER DATABASE OPEN RESETLOGS;
---------------------------------------
http://www.gokhanatil.com/2009/07/redo-log-dosyalarinda-bozulma-ora-00312/

CP of Datafile for Online Backup Returns "A system call received a parameter that is not valid."

2010-07-08T01:45:00.000-07:00

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.3
AIX5L Based Systems (64-bit)

Symptoms
Coping a datafile after a 'begin backup' with the database is open returns an error:
A system call received a parameter that is not valid.

Cause
This problem is caused with the use of JFS2 file systems, CIO not enabled, and the parameter FILESYSTEMIO_OPTIONS set to SETALL.

Note 272520.1 - Direct I/O or Concurrent I/O on AIX 5L:
External utilities or tools cannot read datafiles on JFS2 file systems if you use the values SETALL or DIRECTIO for the FILESYSTEMIO_OPTIONS parameter. For more information, refer to the AIX
documentation which covers the open() restriction in cio mode.

Solution
1. Enable CIO on JFS2 file system

OR

2. Change FILESYSTEMIO_OPTIONS=asynch

Since its a dynamic initialization parameter you can use the ALTER SESSION,
ALTER SYSTEM commands to do this.

Parameter type
String

Syntax
FILESYSTEMIO_OPTIONS = {none | setall | directIO | asynch}

Default value
There is no default value.

Parameter class
Dynamic: ALTER SESSION, ALTER SYSTEM

References
Note 257338.1 - Direct I/O (DIO) and Concurrent I/O (CIO) on AIX 5L
Note 272520.1 - Direct I/O or Concurrent I/O on AIX 5L
----------------------------------------------------
http://ebsblog.blog.163.com/blog/static/127949789200981533133531/
http://forums.sdn.sap.com/message.jspa?messageID=3129195

Solaris Oracle kurulumu Hazırlıkları

2010-06-26T00:21:00.000-07:00

network ip sabitleme;
/etc/hostname
/etc/hosts
/etc/nodename
----------------------
Creating Required Operating System Groups and Users

/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/groupadd oper
id nobody
id -a oracle
useradd -g oinstall -G dba,oper -m -d /export/home/oracle -s /bin/bash oracle
# id -p
uid=0(root) gid=0(root) projid=1(user.root)
# prctl -n project.max-sem-ids -i project user.root
#prctl -n project.max-shm-memory -i project 1
#prctl -n project.max-shm-memory -v 6gb -r -i project 1
#projadd -p 101 -c 'ORA DBA' -U oracle -G dba -K 'project.max-shm-memory=(privileged,6G,deny)' ORA
/etc/user_attr
oracle::::project=ORA
id -p
uid=100(oracle) gid=100(oinstall) projid=101(ORA)
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmni=100
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=256
--swap alanı ekleme
swap -l
zfs create -V 1G -b 4k rpool/swap2
zfs volsize=1G rpool/swap2
swap -a /dev/zvol/dsk/rpool/swap2
http://docs.sun.com/app/docs/doc/817-5093/gizfl?a=view

TDP for Oracle for Solaris RMAN backup

2010-06-26T00:11:00.000-07:00

root>pkgadd -d TIVsmCapi.pkg
root>./TDPoraclex86.bin
set LD_LIBRARY_PATH=$ORACLE_HOME/lib
shutdown db
$ ln -s /usr/lib/amd64/libobk.so $ORACLE_HOME/lib/libobk.so
$ ln -s /opt/tivoli/tsm/client/oracle/bin64/libobk.so $ORACLE_HOME/lib/libobk.so
veya
ln -s /opt/tivoli/tsm/client/oracle/bin/libobk.so $ORACLE_HOME/lib/libobk.so
start db
/usr/bin/dsm.opt
SERVERNAME TBM
traceflag config
tracefile /export/home/oracle/log/tsm_trace.out /opt/tivoli/tsm/client/oracle/bin64
/usr/bin/dsm.sys
SErvername TBM
COMMMethod TCPip
TCPPort 1500
TCPServeraddress ipaddress
NODENAME TBM
PASSWORDACCESS prompt
COMPRESSION yes
root ile
Export DSMO_NODE=TBM
Export DSMI_CONFIG=/usr/bin/dsm.opt
Export DSMI_LOG=/opt/tivoli/tsm/client/oracle/bin
DSMI_DIR
/opt/tivoli/tsm/client/oracle/bin/tdp.opt
DSMI_ORC_CONFIG /usr/bin/dsm.opt
DSMI_LOG /opt/tivoli/tsm/client/oracle/bin64
TDPO_FS orc8_db
TDPO_NODE TBMI
TDPO_OWNER root
TDPO_PSWDPATH /opt/tivoli/tsm/client/oracle/bin64
resolv.conf
nsswitch.conf
/opt/tivoli/tsm/client/oracle/bin/tdpoconf password veya showenv
backup;
shutdown immediate;
startup mount;
--------------------------
hata verirse log dosyasına yazma izni verilmesi yeterli!
--------------------------
run
{
allocate channel t1 type 'sbt_tape' parms
'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.opt)';
backup
format 'df_%t_%s_%p'
(database);
}

run
{
allocate channel 'c1' device type sbt_tape
PARMS='SBT_LIBRARY=/opt/tivoli/tsm/client/oracle/bin/libobk.so';
}

TDP for Oracle for Windows RMAN backup

2010-06-24T03:41:00.000-07:00

1. Register a node on the TSM server.
register node nodename password domain=domainname
parola-123
2. Stop all Oracle services.
OK
3. Install TDP for Oracle for Windows.
E diskine kuruldu
4. Configure the client options file (dsm.opt).
commmethod tcpip
tcpport 1500
TCPServeraddress ip
Nodename TBMI
passwordaccess generate
5. Generate the encrypted password using aobpswd.exe.
E:\Program Files\Tivoli\TSM\AgentOBA\tdpoconf password - 123
E:\Program Files\Tivoli\TSM\AgentOBA\tdpoconf show
6. Test a TDP for Oracle for Windows RMAN backup.
set ORACLE_HOME
rman nocatalog
connect target user/pass@
shutdown immediate
startup mount
ora12514
lissner
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(GLOBAL_DBNAME = orcl)
)

run {
allocate channel t1 type 'sbt_tape' parms
'ENV=(DSMO_DEBUG=49)';
backup (database);}
rman>list backup;
tsm>q occupancy
tsm>select * from backups where node_name='nodename'
rman>allocate channel for delete type 'sbt_tape';
rman>list backup;
rman>change backuppiece '03lh1hgg_1_1' delete; -tsm LL_NAME:03lh1hgg_1_1
rman>change backuppiece '06lh1meb_1_1' delete;
ANS4994S DP Oracle Win32 ANU0599 TDP for Oracle: (848): => () ANS1126E (RC27) The
file space cannot be deleted because this node does not have
permission to delete archived or backed up data.
tsm>update node backdel=y

7. Install the TSM backup-archive client (optional).

job listesi

2010-06-24T00:04:00.001-07:00

EXEC dbms_job.broken(jobno, FALSE);
select job,broken from user_jobs;
select text from user_source
where type = 'PROCEDURE'
and name = procedure_name;
SELECT text FROM all_source WHERE name like '%CTRL%';

SQL> CREATE OR REPLACE PROCEDURE CTRL IS
2 begin
3 dbms_utility.exec_ddl_statement('alter database backup controlfile to trace as ''/oracle/yedek/ctrlbackup.trc'' reuse');
4 end CTRL;
5 /

Procedure created.

SQL> execute CTRL;
SQL> SET ARRAYSIZE 1
SQL> SET MAXDATA 60000
SHOW ERRORS PROCEDURE
DROP PROCEDURE

sqlplus ile bağlanma methodları

2010-06-23T23:25:00.000-07:00

sqlplus system/pass@tnsnames_sid

sqlplus system/pass@//host:port/SID

sqlplus system/pass@'(description=(address=(protocol=tcp)(host=host_name)(port=port_))
(connect_data=(server=dedicated)(service_name=SID)))'

SAP -sm37 tablo bilgileri

2010-05-28T23:31:00.001-07:00

SELECT
*
FROM
TBTCP --as of timestamp to_timestamp('29/05/2010 07:00', 'DD/MM/Yyyy hh24:mi')
where jobname like 'Z100%GERC%'

SELECT
*
FROM
V_OP --as of timestamp to_timestamp('29/05/2010 07:00', 'DD/MM/Yyyy hh24:mi')
where jobname like 'Z100%GERC%'

FROM "TBTCO" T0001, "TBTCP" T0002
WHERE T0001."JOBNAME" = T0002."JOBNAME"
AND T0001."JOBCOUNT" = T0002."JOBCOUNT";

create table ZTBTCO
as select * from TBTCO

Oracle - redo log group eklem

2010-04-14T23:31:00.000-07:00

SELECT * FROM V$LOG;
select GROUP#,MEMBER,TYPE from v$logfile order by 1 ;

ALTER DATABASE ADD LOGFILE GROUP 5 ('/oracle/sid/origlogA/log_g15m1.dbf','/oracle/sid/origlogA/log_g15m2.dbf') size 100M;

alter database drop logfile group 5;
--------------------veya;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/oracle/sid/origlogA/log_g15m1.dbf') size 100M;

alter database add logfile member '/oracle/sid/origlogA/log_g15m2.dbf' to group 5 ;

alter database drop logfile member '/oracle/sid/origlogA/log_g15m2.dbf'
-----------------------
ALTER DATABASE CLEAR LOGFILE GROUP 5;

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

http://www.filibeto.org/sun/lib/nonsun/oracle/11.1.0.6.0/B28359_01/server.111/b28310/onlineredo003.htm

Oracle - iostat, vmstat , filestat ve çift kayıt silme

2010-04-14T07:07:00.000-07:00

http://adminschoice.com/iostat-vmstat-netstat
http://vsbabu.org/oracle/sect02.html
http://www.dba-oracle.com/art_disk_iostat.htm
https://netfiles.uiuc.edu/jstrode/www/oraview/V$FILESTAT.html
http://download-uk.oracle.com/docs/cd/F49540_01/DOC/server.815/a67775/ch20_io.htm#2688
------------------------------
create table SAPSR3.zTPALOG as select * from SAPSR3.TPALOG

SAPSR3.CSMCLSMAP

WHERE ROWID IN (
SELECT t.ROWID AS targetrowid
FROM TEST t,
(SELECT tst_id, tst_name, MAX (ROWID) AS maxrowid
FROM SAPSR3.TPALOG
GROUP BY tst_id, tst_name
HAVING COUNT (*) > 1) m
WHERE t.ROWID != m.maxrowid AND t.tst_id = m.tst_id)

SELECT MAX (ROWID) AS maxrowid
FROM SAPSR3.TPALOG

SELECT TRTIME, TRKORR, TARSYSTEM,TRCLI,TRSTEP,ALLCLI,PROJECT,TRUSER,RETCODE,HOST,ADMIN,TPSTAT_KEY,LINESeQUENCE
FROM SAPSR3.TPALOG
GROUP BY TRTIME, TRKORR, TARSYSTEM,TRCLI,TRSTEP,ALLCLI,PROJECT,TRUSER,RETCODE,HOST,ADMIN,TPSTAT_KEY,LINESeQUENCE
HAVING count('x') > 1;

create table SAPSR3.TPALOG as

create table SAPSR3.TPALOG2 as select distinct * from SAPSR3.TPALOG
create table SAPSR3.TPALOG tablespace PSAPSR3 as select distinct * from SAPSR3.TPALOG2

drop table SAPSR3.TPALOG

rename SAPSR3.TPALOG2 to SAPSR3.TPALOG;

select * from sapsr3.CSMCLSMAP

Oracle -audit komutları

2010-04-14T04:46:00.000-07:00

select * from SYS.AUD$
select * from DBA_AUDIT_TRAIL

DELETE FROM SYS.AUD$;

SELECT sql_text FROM dba_common_audit_trail;
-------------------------------
AUDIT ALL BY sapsr3

AUDIT SESSION BY sapsr3 by access;

AUDIT SELECT TABLE, UPDATE TABLE BY SAPSR3 BY ACCESS;

AUDIT select ANY TABLE;

AUDIT SELECT, INSERT, DELETE
ON sapsr3.vbak
BY ACCESS
WHENEVER SUCCESSFUL;
----------------------------
select * from sys.fga_log$

AUDIT SESSION
BY SAPSR3

NOAUDIT session;

NOAUDIT ALL;

SELECT * FROM DBA_PRIV_AUDIT_OPTS; -- aktif auditleri gosterir.

http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm

Oracle - session, process sayisi

2010-04-14T01:30:00.000-07:00

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5671284058977

ps -auxww | grep

set autotrace on statistics;
select username from v$session where username is not
null;

(1.1*processes)+5. That means process=415 will give me sessions=461

300 - 335

ps -eaf
-------------------------------------------
the number of dirty buffers that are waiting to be written;

select dirty, count(*) from v$bh group by dirty;
update big_table.big_table set id=id where rownum < 1000;
select dirty, count(*) from v$bh group by dirty;
alter system checkpoint;
select dirty, count(*) from v$bh group by dirty;
-------------------------------------------------
alter session set sql_trace=true;

select p.spid
from v$session s, v$process p
where s.paddr = p.addr
and s.audsid = userenv('sessionid');

---------------------------------
select username,count(*)
from v$session
group by username;
select *
from v$resource_limit
where resource_name in ('processes','sessions');

oracle ornek komutlar- unix için kurulum

2010-03-30T04:02:00.001-07:00

select * from dba_registry_history
---------------------------------
select * from dba_audit_trail a
where
a.username='SYS' and
a.logoff_time> sysdate - 100
----------------------------
groupadd -g 102 dba
useradd -d /export/home/oruser -m -s /bin/bash -g dba -u 101 orauser
passwd orauser
usermod -u 101 orauser

ls -alF /export/home/orauser
chown -R orafmi /export/home/orauser
ORACLE_HOME ve ORACLE_SID set
mkdir oracle
ls -alF /oracle
chown -R orauser /oracle
vi /etc/system
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmni=100
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=256

http://download.oracle.com/docs/cd/B19306_01/install.102/b15697/pre_install.htm#BABIBGFA

id -p
prctl -n project.max-shm-memory -i project 101

-prctl -n project.max-shm-memory -r -v 24gb -i project 100

projadd -p 101 -c 'ORA_DBA' -U orauser -G dba -K 'project.max-shm-memory=(privileged,6G,deny)' ORA

etc/user_attr
orasti::::project:ORA

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:454420165038
-------------------------------------
http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnSolaris10.php
usermod -K project=OABS oracle
user_attr

Oracle conrole file yaratma

2010-02-17T06:25:00.000-08:00

iki şekilde control file yaratılır.
trace ile yaratma üzerine duracağız;
alter database backup controlfile to trace; dememiz yeterli.
oluşturduğumuz control file scriptini oluşturabilememiz için datafileların mevcut olması gerekir. özellikle sistem datafile nin header kısmına bakacaktır...

plan table

2010-01-27T05:48:00.001-08:00

SQL> explain plan for select count(*) from tablo;
SQL> @?/rdbms/admin/utlxpls.sql

On version up to and including 9i, use the utlxplan.sql script to createthe plan table as instructed below.
SQL> @?/rdbms/admin/utlxplan
On 10g and above there is a new script - catplan.sql - to create the plan
table that creates a public plan table as a global temporary table
accessible from any schema.
SQL> @?/rdbms/admin/catplan
Note that the plan table format can change between versions so ensure
that it is created using the utlxplan script from the current version.

sapnotes:#1303908

instance bilgileri sql komutları

2010-01-04T03:41:00.000-08:00

SELECT NAME, description FROM v$bgprocess ORDER BY NAME, description

select * from v$version
where banner like 'Oracle%';

select * from v$database

select * from v$license;

select * from v$version;

rman tsm backup

2010-01-04T03:21:00.000-08:00

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/sid/102_64/dbs/snapcf_BPD.f'; # default

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oracle/sid/yedek/ora_df_%t_%s_%p';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/sid/yedek/%F';
CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO TSM;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'TSM' TO 'AUTOBACKUP_%F';
CONFIGURE DEVICE TYPE TSM PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE TSM TO 1;
CONFIGURE CHANNEL DEVICE TYPE 'TSM' PARMS 'ENV=(tdpo_optfile=/usr/tivoli/tsm/tdp_r3/ora64/dsm.sys)' FORMAT 'BACKUP_%D%M%Y_%d_%I_%U';
CONFIGURE CHANNEL DEVICE TYPE 'BOYDAKTSM' PARMS 'ENV=(tdpo_optfile=TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' FORMAT 'BACKUP_%D%M%Y_%d_%I_%U';

XINT_PROFILE=//init.utl,PROLE_PORT=portnumber

CONFIGURE CHANNEL DEVICE TYPE 'TSM' MAXPIECESIZE 100 M;
CONFIGURE CHANNEL DEVICE TYPE 'TSM' clear;

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(tdpo_optfile=/tsm/conf/client/tdpo.opt.tst)' FORMAT 'BACKUP_%D%M%Y_%d_%I_%U';

SHOW CHANNEL FOR DEVICE TYPE TSM;

http://forums.oracle.com/forums/thread.jspa?threadID=697031

v$backup_device

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(NSR_SERVER=tape_svr, NSR_CLIENT=oracleclnt, NSR_GROUP=oracle_tapes)' FORMAT "BACKUP_%U";

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/api/bin/dsm.sys.smp)';

solaris oracle kurulumu

2010-01-04T03:09:00.001-08:00

phgadd -d . SUNWbtool
http://ivan.kartik.sk/oracle/install_ora10gR2_solaris.html
http://blogs.sun.com/mandalika/entry/oracle_on_solaris_10_fixing
useradd -R printadm -m -d /export/deneme -s /usr/bin/bash deneme
http://www.kobhi.com/solaris/user_management/solaris_user_modify.htm
http://belgeler.org/lis/archive-tlkg-lis-7.2.html
http://www.ugurileri.com/2009/05/solaris-10da-network2u-restart-etmek.html
http://forums.oracle.com/forums/thread.jspa?threadID=382389
http://www.lazydba.com/oracle/0__53774.html

ifconfig -a pcn0

netstat -rn
route add default 10.200.0.111
etc/defaultrouter 10.200.0.111

/etc/nsswitch.conf host files dns
/etc/resolv.conf - nameserver -ip-

oracle enterprise manager

2010-01-04T03:02:00.000-08:00

emca -config dbcontrol db -repos create -- manager console oluşturulur

emca -deconfig dbcontrol db -repos drop -- console silinir.

1) Set the environment variable ORACLE_SID to SID
2) /oracle/SID/102_64/bin/emctl stop dbconsole
3) /oracle/SID/102_64/bin/emctl config emkey -repos -sysman_pwd pass < Password for SYSMAN user >
4) /oracle/SID/102_64/bin/emctl secure dbconsole -sysman_pwd < Password for SYSMAN user >
5) /oracle/SID/102_64/bin/emctl start dbconsole

Database istatistik toplama

2009-12-23T05:04:00.000-08:00

procedure;

CREATE OR REPLACE PROCEDURE SYS.gds IS

BEGIN

dbms_stats.gather_database_stats();

END gds;
/
----------------------------------
job;

BEGIN
SYS.DBMS_JOB.REMOVE(6);
COMMIT;
END;
/

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'SYS.GDS;'
,next_date => to_date('23.12.2009 15:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+7)'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

ORACLE HATALARI

2009-12-07T22:14:00.001-08:00

- ORA-00600: internal error code, arguments: [2141], [147671159], [147633032], [], [], [], [], []
- Error: Controlfile was changed externally while mounted Please check if another Oracle database is running and accessing the same controlfile notes: ID 1035098.6

- ORA-19502: write error on file "/oracle/sid/oraarch/sidarch1_21018_644937590.dbf", blockno 32769 (blocksize=512)
ORA-27063: number of bytes read/written is incorrect
- IBM AIX RISC System/6000 Error: 28: No space left on device

Dataguard BROKER -2 dg2 ora

2009-11-19T05:48:00.000-08:00

syandbydg2_initorcl.ora;
orcl.__db_cache_size=432013312
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=167772160
orcl.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\orcl\STBYCF.F'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.DB_UNIQUE_NAME='dg2'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg2_XPT)(INSTANCE_NAME=dg2)(SERVER=dedicated)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg1_XPT)(SERVER=dedicated)))'
*.INSTANCE_NAME='dg2'
*.job_queue_processes=10
*.log_archive_config='dg_config=(dg1)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:\archives
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=dg2'
orcl.log_archive_dest_1='location="C:\archives"','valid_for=(ALL_LOGFILES,ALL_ROLES)'
*.LOG_ARCHIVE_DEST_2='SERVICE=dg1
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=dg1'
orcl.log_archive_dest_3='location="C:\arc"','valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
orcl.log_archive_dest_state_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
orcl.log_archive_dest_state_3='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
orcl.log_archive_format='log_%t_%s_%r_%d.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
orcl.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='dg2'
*.sga_target=612368384
orcl.standby_archive_dest='C:\arc'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'

standbydg2_2_initorcl.ora;

orcl.__db_cache_size=427819008
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=171966464
orcl.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\orcl\STBYCF.F'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.DB_UNIQUE_NAME='dg2'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg2_XPT)(INSTANCE_NAME=dg2)(SERVER=dedicated)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg1_XPT)(SERVER=dedicated)))'
*.INSTANCE_NAME='dg2'
*.job_queue_processes=10
*.log_archive_config='dg_config=(dg1)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:\archives
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=dg2'
orcl.log_archive_dest_1='location="C:\archives"','valid_for=(ALL_LOGFILES,ALL_ROLES)'
*.log_archive_dest_2=''
orcl.log_archive_dest_2='location="C:\arc"','valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'
orcl.log_archive_dest_3=''
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
orcl.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
orcl.log_archive_dest_state_2='ENABLE'
orcl.log_archive_dest_state_3='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
orcl.log_archive_format='log_%t_%s_%r_%d.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
orcl.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='dg2'
*.sga_target=612368384
orcl.standby_archive_dest='C:\arc'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'

primarydg2_initorcl.ora;
orcl.__db_cache_size=427819008
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=171966464
orcl.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\orcl\STBYCF.F'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.DB_UNIQUE_NAME='dg2'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg2_XPT)(INSTANCE_NAME=dg2)(SERVER=dedicated)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg1_XPT)(SERVER=dedicated)))'
*.INSTANCE_NAME='dg2'
*.job_queue_processes=10
*.log_archive_config='dg_config=(dg1)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:\archives
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=dg2'
orcl.log_archive_dest_1='location="C:\archives"','valid_for=(ALL_LOGFILES,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg1_XPT)(INSTANCE_NAME=orcl)(SERVER=dedicated)))"',' ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="dg1" register net_timeout=180 valid_for=(online_logfile,primary_role)'
orcl.log_archive_dest_3=''
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
orcl.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
orcl.log_archive_dest_state_3='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
orcl.log_archive_format='log_%t_%s_%r_%d.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
orcl.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='dg2'
*.sga_target=612368384
orcl.standby_archive_dest=''
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'

Dataguard BROKER -2 dg1 ora değişiklikleri

2009-11-19T05:46:00.000-08:00

primarydg1_initorcl.ora;

orcl.__db_cache_size=385875968
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=213909504
orcl.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\orcl\control01.ctl','C:\oracle\product\10.2.0\oradata\orcl\control02.ctl','C:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.DB_UNIQUE_NAME='dg1'
*.db_writer_processes=2
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.FAL_CLIENT='dg1'
*.FAL_SERVER='dg2'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:\archives
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=dg1'
orcl.log_archive_dest_1='location="C:\archives"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg2_XPT)(INSTANCE_NAME=dg2)(SERVER=dedicated)))"',' ARCH SYNC NOAFFIRM delay=3 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="dg2" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
orcl.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
orcl.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
orcl.log_archive_trace=127
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='dg1'
*.sga_target=612368384
orcl.standby_archive_dest=''
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'

primarydg1_2initorcl.ora;
orcl.__db_cache_size=381681664
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=218103808
orcl.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\orcl\control01.ctl','C:\oracle\product\10.2.0\oradata\orcl\control02.ctl','C:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.DB_UNIQUE_NAME='dg1'
*.db_writer_processes=2
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg1_XPT)(INSTANCE_NAME=orcl)(SERVER=dedicated)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg2_XPT)(SERVER=dedicated)))'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:\archives
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=dg1'
orcl.log_archive_dest_1='location="C:\archives"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg2_XPT)(INSTANCE_NAME=dg2)(SERVER=dedicated)))"',' ARCH SYNC NOAFFIRM delay=3 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="dg2" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
orcl.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
orcl.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
orcl.log_archive_trace=127
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='dg1'
*.sga_target=612368384
orcl.standby_archive_dest=''
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'

standbydg1_initorcl.ora;

orcl.__db_cache_size=385875968
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=213909504
orcl.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\orcl\control01.ctl','C:\oracle\product\10.2.0\oradata\orcl\control02.ctl','C:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.DB_UNIQUE_NAME='dg1'
*.db_writer_processes=2
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg1_XPT)(INSTANCE_NAME=orcl)(SERVER=dedicated)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg2_XPT)(SERVER=dedicated)))'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:\archives
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=dg1'
orcl.log_archive_dest_1='location="C:\archives"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2=''
orcl.log_archive_dest_2='location="dgsby_dg1"','valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
orcl.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
orcl.log_archive_dest_state_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
orcl.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
orcl.log_archive_trace=127
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='dg1'
*.sga_target=612368384
orcl.standby_archive_dest='dgsby_dg1'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'

Dataguard BROKER -2

2009-11-19T05:43:00.000-08:00

dg1.ora;
orcl.__db_cache_size=385875968
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=213909504
orcl.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\orcl\control01.ctl','C:\oracle\product\10.2.0\oradata\orcl\control02.ctl','C:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.DB_UNIQUE_NAME='dg1'
*.db_writer_processes=2
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.FAL_CLIENT='dg1'
*.FAL_SERVER='dg2'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:\archives
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=dg1'
orcl.log_archive_dest_1='location="C:\archives"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg2_XPT)(INSTANCE_NAME=dg2)(SERVER=dedicated)))"',' ARCH SYNC NOAFFIRM delay=3 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="dg2" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
orcl.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
orcl.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
orcl.log_archive_trace=127
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='dg1'
*.sga_target=612368384
orcl.standby_archive_dest=''
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'

dg2.listner;
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HLD-KAY-DG1)(PORT = 1521))
)
)

dg1.tnsnames;
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

DG2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.200.0.97)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg2)
)
)

DG1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.200.0.96)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg1)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HLD-KAY-DG1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

dg2.ora;
orcl.__db_cache_size=432013312
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=167772160
orcl.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\orcl\STBYCF.F'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.DB_UNIQUE_NAME='dg2'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg2_XPT)(INSTANCE_NAME=dg2)(SERVER=dedicated)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dg1_XPT)(SERVER=dedicated)))'
*.INSTANCE_NAME='dg2'
*.job_queue_processes=10
*.log_archive_config='dg_config=(dg1)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:\archives
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=dg2'
orcl.log_archive_dest_1='location="C:\archives"','valid_for=(ALL_LOGFILES,ALL_ROLES)'
*.LOG_ARCHIVE_DEST_2='SERVICE=dg1
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=dg1'
orcl.log_archive_dest_3='location="C:\arc"','valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
orcl.log_archive_dest_state_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
orcl.log_archive_dest_state_3='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
orcl.log_archive_format='log_%t_%s_%r_%d.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
orcl.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='dg2'
*.sga_target=612368384
orcl.standby_archive_dest='C:\arc'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'

dg2.listner;
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)

)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = HLD-KAY-DG2)(PORT = 1521))
)
)

dg2.tns;
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

DG2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.200.0.97)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg2)
)
)

DG1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.200.0.96)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg1)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HLD-KAY-DG2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

Dataguard BROKER -1

2009-11-19T05:42:00.000-08:00

http:// stanford.edu/dept/itss/docs/oracle/10g/server.101/b10822/cli.htm#i1005666
http://download.oracle.com/docs/cd/B10501_01/server.920/a96629/dgmgrl.htm#73729

show parameter DG_BROKER_START --- dmon process

ALTER SYSTEM SET DG_BROKER_START=TRUE;
--restart isteyebilir
SQL> SHOW PARAMETER DB_UNIQUE NAME;

DGMGRL> CREATE CONFIGURATION 'DRTest' AS
PRIMARY DATABASE IS 'dg1'
CONNECT IDENTIFIER IS dg1;

SHOW CONFIGURATION;

DGMGRL> ADD DATABASE 'dg2' AS
CONNECT IDENTIFIER IS dg2
MAINTAINED AS PHYSICAL;

-----------
DGMGRL> EDIT DATABASE 'dg2' SET PROPERTY 'LogArchiveFormat'='log_%t_%s_%r_%d.arc';
Property "LogArchiveFormat" updated.

DGMGRL> EDIT DATABASE 'dg2' SET PROPERTY 'StandbyArchiveLocation'='C:\archives';
Property "StandbyArchiveLocation" updated.

SHOW DATABASE VERBOSE 'dg2';

ENABLE CONFIGURATION;

SHOW CONFIGURATION;

ORA-19527: physical standby redo log must be renamed
# Uncomment is filename conversion is needed--sid ler farklı ise
#DB_FILE_NAME_CONVERT=("/primary","/standby")
#LOG_FILE_NAME_CONVERT=("/primary","/standby")
C:\oracle\product\10.2.0\oradata\orcl

enable database 'dg2';
--iptal EDIT DATABASE 'dg2' SET PROPERTY 'LogXptMode'='SYNC';

EDIT DATABASE 'dg1' SET PROPERTY 'LogArchiveTrace'='127';

EDIT DATABASE 'dg2' SET STATE='READ-ONLY';

EDIT DATABASE 'dg1' SET STATE='Offline';

EDIT DATABASE 'dg2' SET STATE='ONLINE' WITH APPLY INSTANCE='dg2';

EDIT DATABASE 'dg2' SET PROPERTY 'delaymins'='30';

DGMGRL> SHOW LOG LATEST ON SITE 'dg2';

DGMGRL> SHOW LOG ALERT LATEST ON SITE 'dg2'

DGMGRL> SHOW RESOURCE 'dg2';

DGMGRL> SHOW RESOURCE db ;

DGMGRL> SHOW RESOURCE db 'InconsistentProperties' ;

DGMGRL> SHOW SITE 'dg2';

DGMGRL> SHOW SITE VERBOSE 'dg2';

C:\Documents and Settings\ferhantekin>dgmgrl
DGMGRL for 32-bit Windows: Version 10.2.0.1.0 - Production

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

DGMGRL ÷≡esine ho■ geldiniz, bilgi iτin "yard²m"² t²klat²n.
DGMGRL> connect sys@dg1
Password:
Ba≡lan²ld².
DGMGRL> show configuration;

Configuration
Name: DRTest
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
dg1 - Primary database
dg2 - Physical standby database

Geτerli "DRTest" statⁿsⁿ:
SUCCESS

DGMGRL> show database verbose dg1;

Database
Name: dg1
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
orcl

Properties:
InitialConnectIdentifier = 'dg1'
LogXptMode = 'ARCH'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'HLD-KAY-DG1'
SidName = 'orcl'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG1)
(PORT=1521))'
StandbyArchiveLocation = 'dgsby_dg1'
AlternateLocation = ''
LogArchiveTrace = '127'
LogArchiveFormat = '%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Geτerli "dg1" statⁿsⁿ:
SUCCESS

DGMGRL> show database verbose dg2;

Database
Name: dg2
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
orcl

Properties:
InitialConnectIdentifier = 'dg2'
LogXptMode = 'ARCH'
Dependency = ''
DelayMins = '3'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = 'dg2'
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'HLD-KAY-DG2'
SidName = 'orcl'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=HLD-KAY-DG2)
(PORT=1521))'
StandbyArchiveLocation = 'C:\arc'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'log_%t_%s_%r_%d.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Geτerli "dg2" statⁿsⁿ:
SUCCESS

DGMGRL>

DATAGUARD manuel

2009-11-19T05:37:00.000-08:00

-yogun ilgli uzerine bir kac ay once yaptigimiz data guard uygulamalarını tekrar gundeme aldım :)

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10823/create_ps.htm
ilk olarak primary database ayarlarını yapıyoruz
dg1;
ALTER DATABASE FORCE LOGGING;
tns ler değişti.
*.DB_UNIQUE_NAME=dg1
*.SERVICE_NAMES=dg1
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.LOG_ARCHIVE_DEST_1=
'LOCATION=C:\arcihves
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=dg1
*.LOG_ARCHIVE_DEST_2=
'SERVICE=dg2
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=dg2
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.FAL_SERVER=dg2
*.FAL_CLIENT=dg1
#*.DB_FILE_NAME_CONVERT=
'/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'
#*.LOG_FILE_NAME_CONVERT=
'/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'
*.STANDBY_FILE_MANAGEMENT=AUTO

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Create a Control File for the Standby Database
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'C:\STBYCF.F';
SQL> ALTER DATABASE OPEN;

dg2;
*.DB_UNIQUE_NAME=dg2
*.SERVICE_NAMES=dg2
inti ora değiştirildi.
ALTER TABLESPACE TEMP ADD TEMPFILE
'C:\temp01.dbf'
SIZE 40M REUSE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

alter database recover managed standby database cancel;
alter database open;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

http://www.oracle.com/technology/deploy/availability/htdocs/dataguardprotection.html

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
FROM V$MANAGED_STANDBY;

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

select log_mode, controlfile_type, open_mode, database_role, switchover_status from v$database;

log_mode, controlfile_type, open_mode, database_role, switchover_status,

alter database open;

dg1.ora;
orcl.__db_cache_size=406847488
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=192937984
orcl.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\orcl\control01.ctl','C:\oracle\product\10.2.0\oradata\orcl\control02.ctl','C:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.DB_UNIQUE_NAME='dg1'
*.db_writer_processes=2
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.FAL_CLIENT='dg1'
*.FAL_SERVER='dg2'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:\archives
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=dg1'
*.LOG_ARCHIVE_DEST_2='SERVICE=dg2
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=dg2'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='dg1'
*.sga_target=612368384
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'

dg1. listner;
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HLD-KAY-DG1)(PORT = 1521))
)
)

dg1.tns names;
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

DG2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.200.0.97)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg2)
)
)

DG1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.200.0.96)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg1)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HLD-KAY-DG1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

dg2.ora;
orcl.__db_cache_size=432013312
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=167772160
orcl.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\orcl\STBYCF.F'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.DB_UNIQUE_NAME='dg2'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.FAL_CLIENT='dg2'
*.FAL_SERVER='dg1'
*.INSTANCE_NAME='dg2'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:\archives
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=dg2'
*.LOG_ARCHIVE_DEST_2='SERVICE=dg1
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=dg1'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='dg2'
*.sga_target=612368384
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'

dg2.listner;
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)

)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = HLD-KAY-DG2)(PORT = 1521))
)
)

dg2.tns;
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

DG2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.200.0.97)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg2)
)
)

DG1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.200.0.96)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg1)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HLD-KAY-DG2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

----------------------------
startup nomount
alter database mount standby database;
select GROUP#,MEMBER,TYPE from v$logfile
alter database recover managed standby database disconnect from session;

ORACLE invalid objects

2009-11-09T23:41:00.000-08:00

invalid obje daha önce compile edilmemiş obje manasındadır. siz ne zaman o sql çalıştırırsanız validr duruma geçecektir.
invalid object list;
break on c1 skip 2

set pages 999

col c1 heading 'owner' format a15
col c2 heading 'name' format a40
col c3 heading 'type' format a10

ttitle 'Invalid|Objects'

select
owner c1,
object_type c3,
object_name c2
from
dba_objects
where
status != 'VALID'
order by
owner,
object_type
;
----------------------------------------
You can invoke the utl_recomp package to recompile invalid objects:

EXEC UTL_RECOMP.recomp_serial('schema name');

Oracle highly recommends running this script towards the end of of any migration/upgrade/downgrade.

Set heading off;

set feedback off;

set echo off;

Set lines 999;

Spool run_invalid.sql

select

'ALTER ' || OBJECT_TYPE || ' ' ||

OWNER || '.' || OBJECT_NAME || ' COMPILE;'

from

dba_objects

where

status = 'INVALID'

and

object_type in ('PACKAGE','FUNCTION','PROCEDURE')

;

spool off;

set heading on;

set feedback on;

set echo on;

@run_invalid.sql
----------------------

ORACLE kullanıcı bilgileri

2009-11-09T05:17:00.000-08:00

çalışma süresi
set timing on
select * from dual;

-------------------
kullanıcılar hakkında bilgiler
SELECT username, created, PROFILE, default_tablespace, temporary_tablespace
FROM dba_users
ORDER BY username
desc dba_users;
---------------------------oracle alan hesaplaması
SELECT tablespace_name, SUM (BYTES) total_free_space,
MAX (BYTES) largest_free_extent
FROM dba_free_space
GROUP BY tablespace_name
-------------------------------DATABASE BAĞLI KULLANICILARIN ÇALIŞTIRDIĞI SORGULAR
SELECT /*+ ORDERED */
SUBSTR (RPAD (s.sql_text, 64), 1, 64) sqltxt
FROM v$session v, v$sqltext_with_newlines s
WHERE v.osuser = ' ferhan '
AND s.address = v.sql_address
AND s.hash_value = v.sql_hash_value
ORDER BY piece

Ayrıca hangi user ile bağlı olduğunu bulmak için

select sys_context('USERENV','OS_USER')
from dual;
---------------------------------------
SELECT owner, NAME, TYPE
FROM dba_source
------------------------------------------HER ZAMAN EL ALTINDA OLMASI GEREKENLERDEN BAZILARI

Server/OS Information

Server identification Representative Query

Host name where the instance is running -select host_name from v$instance;

Operating system platform - select platform_name from v$database –-(10g)

File Information

Oracle file locations - Representative Query

Control files - select name from v$controlfile;

Datafiles - select file_name from Dba_data_files;

Temp files - select file_name from Dba_temp_files;

Log files - select member from v$logfile;

Archived logs - select name from v$archived_log

Flash recovery area- select name from v$recovery_file_dest

Other points of access on the file system indicated by parameters - select * from v$parameter where value like '%/%' or value like '%/%';

Programmatic access to the file system - select directory_path from dba_directories

Process Information

Processor/Processes - Representative Query

Session Processes - select p.spid, s.username, s.program from v$process p, v$session s where p.addr=s.paddr order by 2, 3, 1

Processes related to parallelism - select slave_name, status from v$PQ_SLAVE

Memory Information

Memory- Representative Query

Program Global Area - select * from V$PGASTAT

System Global Area - select * from v$sga

--------------------- table space bilgileri
SELECT tablespace_name, initial_extent, next_extent, min_extents,
max_extents, pct_increase, status, CONTENTS
FROM dba_tablespaces
desc dba_tablespaces;
-------------VERİTABANINDAKİ DOSYALARIN YERİNİ BULMA
SELECT owner, table_name
FROM dba_tables dt
WHERE NOT EXISTS (
SELECT 'TRUE'
FROM dba_constraints dc
WHERE dc.table_name = dt.table_name
AND dc.constraint_type = 'P')
AND owner = 'HR'
ORDER BY owner, table_name

SELECT banner product_versions
FROM v$version

SELECT 'Archived Log Directory' "Filename", VALUE "Location"
FROM v$parameter
WHERE NAME = 'log_archive_dest'
UNION
SELECT 'Control Files' "Filename", VALUE "Location"
FROM v$parameter
WHERE NAME = 'control_files'
UNION
SELECT 'Datafile' "Filename", NAME "Location"
FROM v$datafile
UNION
SELECT 'LogFile Member' "Filename", MEMBER "Location"
FROM v$logfile

Burada

SELECT 'Datafile' "Filename", NAME "Location"
FROM v$datafile
where name like '%kriterim%'
ORDER BY tablespace_name
------------------------İNDEX BULUNAN TABLOLARIN LİSTESİ
İndex bulunan tabloların listesini aşağida script ile bulabiliriz. Ayrıca bu tabloda kaç adet index olduğunuda listeler.

SELECT table_owner, table_name, column_name, COUNT (*) index_count
FROM dba_ind_columns
WHERE table_owner = 'HR'
GROUP BY table_owner, table_name, column_name

İndex bulunmayan tabloların listesini aşağida script ile bulabiliriz.
SELECT owner, table_name
FROM (SELECT owner, table_name
FROM dba_tables
--Burada where kosulu sema sinirlandirmasi yapilabilir.
MINUS
SELECT table_owner, table_name
FROM dba_indexes) orasnap_noindex
WHERE owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, table_name

Ağaıdaki sorguda PK (birincil anahtar) olmayan tabloları döndürür.

SELECT owner, table_name
FROM dba_tables dt
WHERE NOT EXISTS (
SELECT 'TRUE'
FROM dba_constraints dc
WHERE dc.table_name = dt.table_name
AND dc.constraint_type = 'P')
AND owner = 'HR'
ORDER BY owner, table_name
Bu sorguda aşağıdaki kriterlere göre farklı aramalarda yapılabilir. Tercih sizin.

CONSTRAINT_TYPE VARCHAR2(1) Type of constraint definition:

* C (check constraint on a table)
* P (primary key)
* U (unique key)
* R (referential integrity)
* V (with check option, on a view)
* O (with read only, on a view)

SQL> desc all_constraints
----------------------------Hangi tablespace ne kadar alan kullanıyor
SELECT ts.tablespace_name, "File Count", TRUNC ("SIZE(MB)", 2) "Size(MB)",
TRUNC (fr."FREE(MB)", 2) "Free(MB)",
TRUNC ("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free",
RPAD ('*',
TRUNC (CEIL ((fr."FREE(MB)" / df."SIZE(MB)") * 100) / 10),
'*'
) "Graph"
FROM (SELECT tablespace_name, SUM (BYTES) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM (BYTES) / (1024 * 1024) "SIZE(MB)",
COUNT (*) "File Count",
SUM (maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name(+)
AND fr.tablespace_name = ts.tablespace_name(+)
ORDER BY "% Free"
cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql cpu apply
SQL> QUIT

Check the log files that were created by catbundle.sql. These log files are available in the following directory:

$ORACLE_HOME/cfgtoollogs/catbundle/
catbundle_CPU__APPLY_.log
catbundle_CPU__GENERATE_.log
------------------------------
> $ORACLE_HOME/OPatch/opatch apply

> $ORACLE_HOME/OPatch/opatch rollback -id [CPU_No]

> $ORACLE_HOME/OPatch/opatch lsinventory
PATH=/oracle/BPQ/102_64/OPatch:$PATH

http://onlineappsdba.com/index.php/2007/06/16/upgrade-oracle-database-to-10202-soa-suite-install-part-ii/
--------------------------------------

OracleDatabase10g-OCP_Certification All-in-One Exam Guide.pdf
21-08.2009 chapter 1 Basic Oracle Concepts
02-09.2009 chapter 2 Installing Oracle Database 10gs 70

ADM100_-_2005-Q2_-_A4_-_SAP_Web_AS_Administration_I.pdf
21-08.2009 Unit 1 - Basics 130
http://www.ceturk.com/veri-tabani/oracle-high-availability-real-application-clustersrac-data-guard-flashback-video.html

http://www.mcs.csueastbay.edu/support/oracle/doc/10.2/server.102/b14210/architectures.htm#i1008376
http://books.google.com.tr/books?id=VrW4_e-BDg0C&pg=PA29&lpg=PA29&dq=Oracle+Streams&source=bl&ots=v6chqbB1ZX&sig=-6Y-1rb2lFm7joXUHpVKwG7SP0w&hl=tr&ei=WUONSrfcNqLUmgPPqZz9DQ&sa=X&oi=book_result&ct=result&resnum=6#v=onepage&q=&f=false

SELECT
NAME,
DETECTED_USAGES,
CURRENTLY_USED,
FIRST_USAGE_DATE
FROM
DBA_FEATURE_USAGE_STATISTICS
WHERE
VERSION = (SELECT VERSION FROM V$INSTANCE) AND
(DETECTED_USAGES > 0 OR CURRENTLY_USED != 'FALSE');
*-----------------------------------------------------
SELECT
obj.inst_id as inst_id, obj.TO_OWNER as owner,
obj.TO_NAME as table_name,
sum(ds.bytes/1024)/(max(sq.child_number)+1) as size_kb,
obj.to_type as type, ds.buffer_pool as bufferpool,
upper(substr(sq.sql_text,1,6)) as operation, sq.sql_text as sql_text,
sq.module as sql_module, sq.action as action,
sum(sq.executions) as executions,
CASE WHEN sum(sq.disk_reads)>sum(sq.buffer_gets) THEN 9999
ELSE decode(sum(sq.buffer_gets), 0, 0,
100*(1-(sum(sq.disk_reads)/sum(sq.buffer_gets))))
END as cache_hit_rat, sum(sq.disk_reads) as disk_reads, decode(sy.value, 0, 0,
sum(sq.disk_reads/sy.value)) as disk_read_rat,
sum(sq.buffer_gets) as buffer_gets,
decode(sum(sq.buffer_gets)+sum(sq.disk_reads), 0, 0,
sum(sq.buffer_gets)/(sum(sq.buffer_gets)+sum(sq.disk_reads))) as log_read_rat, sum(sq.rows_processed) as rows_proc,
decode(sum(sq.executions), 0, 0,
sum(sq.rows_processed)/sum(sq.executions)) as rows_per_exec,
decode(sum(sq.rows_processed), 0, 0,
sum(sq.buffer_gets)/sum(sq.rows_processed)) as buffgets_per_row,
sum(sq.sorts) as sorts, sum(sq.cpu_time/1000) as cpu_time,
sum(sq.users_opening) as user_open,
sum(sq.open_versions) as opening_vers,
sum(sq.loaded_versions) as load_vers,
max(sq.child_number)+1 as nb_childs, sum(sq.sharable_mem) as sh_mem,
sum(sq.persistent_mem) as pers_mem,
sum(sq.runtime_mem) as runtime_mem,
sum(sq.invalidations) as invalidations,
sum(sq.parse_calls) as parse_call
FROM
gv$sql sq, gv$object_dependency obj, dba_segments ds, gv$sysstat sy
WHERE
sq.address=obj.from_address and sq.hash_value=obj.from_hash and
obj.TO_NAME=ds.segment_name and sq.inst_id=obj.inst_id and sq.inst_id=
sy.inst_id and ds.segment_type='TABLE' and sy.name='physical reads'
GROUP BY
obj.inst_id, obj.to_owner, obj.to_name, obj.to_type, ds.buffer_pool,
sq.sql_text, sq.module, sq.action, sy.value
ORDER BY
obj.to_name,obj.inst_id, operation

---------------
select * from v$license;

select banner from v$version where BANNER like '%Edition%';

select decode(count(*), 0, 'No', 'Yes')
from dba_part_tables
where owner not in ('SYSMAN', 'SH', 'SYS', 'SYSTEM') and rownum = 1;

select decode(count(*), 0, 'No', 'Yes')
from all_sdo_geom_metadata where rownum = 1;

select decode(count(*), 0, 'No', 'Yes')
from v$active_instances where rownum <= 2;
-----------------------

UTIL_FILE example

2009-11-09T05:15:00.000-08:00

-öncelik ile yükleme yapacağımız tabloyu yaratalım.

create table kisiler(adi varchar2(30),
soyadi varchar2(30),
yasi number);

--Küçük bir not ekleyelim hemen araya. burada directory adını verirken buyuk harf ile vermeyi

--unutmayalım. dosyadan okuduğu için buyuk hart vermeniz gerekecektir.
--paketimizi yaratalım.

CREATE OR REPLACE PROCEDURE dosyadan_yukleme
AS
v_line VARCHAR2 (2000); -- dosyadan okunacak her bir satır
v_file UTL_FILE.file_type; -- Verilerin olduğu dosya
v_dir VARCHAR2 (250); -- dosyanın bulunduğu dizin
v_filename VARCHAR2 (50); -- dosyamızın adı
v_1st_virgul NUMBER; --ilk virgulun olduğu yer
v_2nd_virgul NUMBER; --ikinci virgülün olduğu yer
v_adi VARCHAR2(30); --alanlarımız için değişkenler ADI
v_soyadi VARCHAR2 (30); --alanlarımız için değişkenler SOYADI
v_yasi NUMBER; --alanlarımız için değişkenler YASI
BEGIN
v_dir := 'dosya_yolum';
v_filename := 'kisiler.txt';
v_file := UTL_FILE.fopen (v_dir, 'kisiler.txt', 'r',1000);

-- --------------------------------------------------------
--DOSYA SONUNA GELENE KADAR OKU
-- --------------------------------------------------------
LOOP
BEGIN
UTL_FILE.get_line (v_file, v_line);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;

v_1st_virgul := INSTR (v_line, ',', 1, 1);
v_2nd_virgul := INSTR (v_line, ',', 1, 2);
v_adi := SUBSTR (v_line, 1, v_1st_virgul - 1);
v_soyadi :=
SUBSTR (v_line, v_1st_virgul + 1, v_2nd_virgul - v_1st_virgul - 1);
v_yasi := SUBSTR (v_line, v_2nd_virgul + 1);
DBMS_OUTPUT.put_line (v_adi|| ' - ' || v_soyadi || ' - ' || v_yasi);

-- ------------------------------------------
-- Kayıtları oluşturduğumuz tabloya eklemeyi yapıyoruz.
-- ------------------------------------------
INSERT INTO kisiler
VALUES (upper(v_adi), UPPER (v_soyadi), v_yasi);
END LOOP;

UTL_FILE.fclose (v_file);
COMMIT;
END;
/

--dosyamda aşağıdaki gibi kayıtlar var.
--ad,a,12
--al,at25
--nur,ak,24
--ak,alçın,30

BEGIN
DOSYADAN_YUKLEME;
COMMIT;
END;
-----------------

ORACLE parameter

2009-11-09T05:11:00.000-08:00

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

Oracle Mimarisi
System Admin Advanced Level
Data Guard for Oracle
Disaster for Oracle
Performans Tuning for Oracle
Backup for Oracle
Oracle on SAP
------------------------

first_rows
show parameter optimizer
buffer busy waits - buffer cache küçükken
db file scattered read - full table ve index taramada artıyor.
db file sequential read - index üzerinden erişildiğinde
timed_statistic=true olmalı wait eventleri görmek için
session ile system event sistem restart da sıfırlanır.
v$session view - row_wait_obj. hangi obje beklemeye neden oluyor.
v$active_session_history
temproray table space kullanmış pga kullanmasını sağlamalıyız.
lock-latch buffer daki lock

db file sequential read - düşürme yolları; increasing the number of freelist on the indexes
show parameter job_que
keep buffer pool büyüttü samples table nı keep
show parameter db_keep
db_cache_size; keep pool(buffer cache %10) recycle pool default pool
show parameter db_keep
alter system set db_keep_cahce_size=10M scope=spfile;
desc user_tables;
select id, name, current_size from v$buffer_ pool;
alter table hr.department storage v$buffer_ pool_statistic
show parameter query_rewrite_enabled
shoq parameter replication
select index_name from user_indexes;

select name, value from v$parameter

where name='sga_target'

statistics_level'

SELECT * FROM v$pq_sesstat;

select
statistic,
value
from
v$pq_sysstat
where
statistic = 'Servers Busy';

show parameter optimizer_features_enable
------------------------
--parametre dosyalarının konumunu öğrenmek için aşağıdaki sorgu işinize yarayabilir.
--ben burada dizin ile başlayanlara göre bir kırılım yaptım.
SELECT name,value,display_value
FROM sys.v_$parameter where value like '/%'
-----------------------------
--BLOCK CHANGE TRACKING (BCT)database'deki değişkliklerin yapıldığı block'ları tutan dosyadır. rman buraya bakıyor.
SELECT filename, status, BYTES
FROM v$block_change_tracking;
-------

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
--dosyayı ben belirliyorum.
USING FILE 'c:\oracle\product\flash_recovery_area\ORABASE\bctf01.log';
disable yapmak için ise
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
disable yaptığınızda dosyanız silinecektir.
dosyanın adını ve konumunu değiştirmek için MOUNT moda geçtikten sonra aşağıdaki işlemi yapmak gerekiyor.Aksi halde aşağıdaki hataları alırsınız.

ORA-01511: günlük/veri dosyalarını adlandırmada hata
ORA-19771: veritabanı açıkken değişiklik izleme dosyası yeniden adlandırılamaz.

ALTER DATABASE RENAME FILE
'c:\oracle\product\flash_recovery_area\ORABASE\bctf01.log'
TO 'c:\oracle\product\flash_recovery_area\ORABASE\new_name.log';

--------------
select name,open_mode,log_mode,platform_name,CURRENT_SCN from v$database
-----------------
select * from V$DATABASE_BLOCK_CORRUPTION

bozuk bir block'ta olan tabloya erişmeye çalışdığımızda aşağıdaki hatayı alırız.
ORA-01578: ORACLE data block corrupted (file # 6, block # 256)
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/orcl/bozuk_dosya.dbf’
aklımıza ilk gelen durum burada RMAN ile bağlanarak aşağıdaki sorguyu çalıştırmaktır.
BLOCKRECOVER DATAFILE 6 BLOCK 256
----------------

v$ tables perf, create user , pfile

2009-11-09T05:07:00.000-08:00

todays date SELECT SYSDATE FROM DUAL
Oracle provides CPU statistics in V$SQL
check V$SESS_TIME_MODEL for database CPU usage
buffer gets at the database level; check V$SESSTAT and V$SQL
wait events listed in V$SESSION_WAIT
V$ACTIVE_SESSION_HISTORY view contains a sampled history of session activity

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

SELECT
/*+
first_ro
*/
s.module
FROM
v$sessio
WHERE
s.sql_ad
'HOST' )
ORDER BY
t.piece#

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

alter user hr acount unlock; /unlock etme
alter user hr identified by hr; /şifre koyma

sqlplus hr/hr@fer

select table_name from tabs;
desc departments;
select * from departments;
create table test (id int, name varchar2(20));
inser into test values(1, 'test1');
exp system/oracle@orcl file=c:\exp_hr.dmp owner=hr
possible varsa türkçe karakterler gidiyor.
hlm\software\oracle\key_OraDb10g_home1\ NLS_LANG sunucudaki ile aynı olacak.
nvarchar2 varchar2 den daha fazla dil destekler

select user from dual; /dual table

shutdwon immediate / db kapatıldı
startup /db aç

sqlplus-
ORA-12514 - listener.ora
(SID_DESC =
(SID_NAME = fer)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(GLOBAL_DBNAME = fer)

lsnrctl status
lsnrctl stop
lsnrctl start

emctl status dbconsole
emca -config dbcontrol db -repos create
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos recreate

sqlplus sys/sys@oracle as sysdba
tnsping fer

select a+nvl(b,0) from table

set pagesize 10000
select ...

select count(*) from table
where rownum<=1 -kayıt var mı?

select sysdate from dual -bir tane donuyor

--restricted
SELECT LOGINS FROM V$INSTANCE
SELECT b.grantee, a.grantee || '(Role)' AS granted
FROM dba_sys_privs a, dba_role_privs b
WHERE a.privilege = 'RESTRICTED SESSION'
AND a.grantee = b.granted_role
UNION
SELECT b.username, 'User (Direct)'
FROM dba_sys_privs a, dba_users b
WHERE a.privilege = 'RESTRICTED SESSION'
AND a.grantee = b.username;

GRANT RESTRICTED SESSION TO DB_USER;
REVOKE RESTRICTED SESSION FROM DB_USER;
ALTER SYSTEM ENABLE RESTRICTED SESSION;

SELECT 'ALTER SYSTEM KILL SESSION''' || sid || ', ' || serial#|| ''' IMMEDIATE; '
FROM V$SESSION WHERE TYPE!= 'BACKGROUND' AND USERNAME NOT IN
( SELECT b.grantee FROM dba_sys_privs a, dba_role_privs b
WHERE a.privilege = 'RESTRICTED SESSION' AND a.grantee = b.granted_role
UNION
SELECT b.username FROM dba_sys_privs a, dba_users b
WHERE a.privilege = 'RESTRICTED SESSION' AND a.grantee = b.username );
ALTER SYSTEM DISABLE RESTRICTED SESSION;

SHUTDOWN IMMEDIATE
SHUTDOWN ABORT hard crash gibi

ALTER SYSTEM CHECKPOINT; - ALTER SYSTEM SWITCH LOGFILE;
SHUTDOWN ABORT;
STARTUP RESTRICT;
SHUTDOWN IMMEDAITE;

STARTUP NOMOUNT
STARTUP MOUNT
STARTUP
STARTUP RESTRICT
STARTUP MIGRATE
STARTUP FORCE

COLUMN PLATFORM_NAME FORMAT A32
SELECT * FROM V$TRANSPORTABLE_PLATFORM;
SQL> COLUMN tablespace_name FORMAT A20
SQL> COLUMN name FORMAT A50
SQL> select tablespace_name, name from v$datafile_header;

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(' USERS, DATA_TS', TRUE); -self contained
SELECT * FROM TRANSPORT_SET_VIOLATIONS; --self contained
no rows selected olmalı

show parameter job_queue_process;
desc v$parameter;
create pfile from spfile;
1. SPFILE dosyasından, metin formatında bir PFILE oluşturuyoruz:
SQL> create pfile='/tmp/deneme.text' from spfile;
2. PFILE dosyasını açıp, log_archive_dest parametresinde ilgili değişiklikleri yapıyoruz.
SQL> ! vi /tmp/deneme.text
...
3. Çalışmakta olan bir veritabanının spfile'ini ezemeyeceğimiz için veritabanını kapatıyoruz.
SQL> shutdown immediate;
4. Değişiklik yaptığımız pfile'i kullanarak yeni bir spfile oluşturuyoruz.
SQL> create spfile from pfile='/tmp/deneme.text';
5. Veritabanını açıyoruz.:
SQL> alter database open;

create spfile='/data2/spfile_yedek.ora'
from pfile='/tmp/deneme.text';
1. spfile.ora
2. spfile.ora
3. init.ora
4. init.ora
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';
SELECT VALUE FROM V$PARAMETER WHERE NAME='spfile';

STARTUP PFILE='/tmp/deneme.text';

SPFILE=/klasor/adresi/spfile_adi.ora
STARTUP PFILE='/tmp/calistir.text';

select ad||' '||soyad from table
where department.id is null -- boş mu

CREATE USER "HR" PROFILE "DEFAULT" IDENTIFIED BY "*******" ACCOUNT UNLOCK
GRANT "CONNECT" TO "HR"

CREATE SMALLFILE TABLESPACE "HR_TS" DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\HR_TS' SIZE 10M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

ALTER USER "HR" DEFAULT TABLESPACE "HR_TS"
GRANT CREATE TABLE TO "HR"

ERR--CREATE TABLE "SYS"."USERS" ( "USER_ID" NUMBER(8), "USER_ROLE" VARCHAR2(10), "EMAIL" VARCHAR2(50), "FIRST_NAME" VARCHAR2(30), "LAST_NAME" VARCHAR2(30), "STREET_ADDRESS" VARCHAR2(40), "CITY" VARCHAR2(30), "STATE_PROVINCE" VARCHAR2(25), "POSTAL_CODE" VARCHAR2(12), "COUNTRY_ID" CHAR(2), CONSTRAINT "USR_PK" PRIMARY KEY ("USER_ID") VALIDATE , CONSTRAINT "USR_UK" UNIQUE ("EMAIL") VALIDATE )

REVOKE CREATE TABLE FROM "HR"
GRANT CREATE TABLE TO "HR" WITH ADMIN OPTION

GRANT ALTER TABLESPACE TO "HR" WITH ADMIN OPTION

REVOKE ALTER TABLESPACE FROM "HR"
REVOKE CREATE TABLE FROM "HR"
GRANT UNLIMITED TABLESPACE TO "HR"

GRANT "RESOURCE" TO "HR"
ALTER USER "HR" DEFAULT ROLE ALL

CREATE TABLE "HR"."USERS" ( "USER_ID" NUMBER(8), "USER_ROLE" VARCHAR2(10), "EMAIL" VARCHAR2(50), "FIRST_NAME" VARCHAR2(30), "LAST_NAME" VARCHAR2(30), "STREET_ADDRESS" VARCHAR2(40), "CITY" VARCHAR2(30), "STATE_PROVINCE" VARCHAR2(25), "POSTAL_CODE" VARCHAR2(12), "COUNTRY_ID" CHAR(2), CONSTRAINT "USR_PK" PRIMARY KEY ("USER_ID") VALIDATE , CONSTRAINT "USR_UK" UNIQUE ("EMAIL") VALIDATE )

CREATE TABLE "HR"."PRODUCTS" ( "PROD_ID" NUMBER(8), "NAME" VARCHAR2(50), "IMAGE" VARCHAR2(4000), "DESCRIPTION" VARCHAR2(4000), CONSTRAINT "PRD_PK" PRIMARY KEY ("PROD_ID") VALIDATE ) TABLESPACE "HR_TS"

CREATE TABLE "HR"."EXPERTISE_AREAS" ( "PROD_ID" NUMBER(8), "USER_ID" NUMBER(8), "EXPERTISE_LEVEL" VARCHAR2(30), "NOTES" VARCHAR2(4000), CONSTRAINT "EXA_PK" PRIMARY KEY ("PROD_ID", "USER_ID") VALIDATE , CONSTRAINT "EXA_PRD_FK" FOREIGN KEY ("PROD_ID") REFERENCES "HR"."PRODUCTS" ("PROD_ID") VALIDATE , CONSTRAINT "EXA_USR_FK" FOREIGN KEY ("USER_ID") REFERENCES "HR"."USERS" ("USER_ID") VALIDATE ) TABLESPACE "HR_TS"

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

JOB ile analiz

2009-11-09T01:44:00.000-08:00

http://www.elliott-technologies.com/oracle.php

BEGIN
SYS.DBMS_JOB.REMOVE(28);
COMMIT;
END;
/

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'begin
dbms_stats.gather_schema_stats(ownname=> ''SAPSR3'' , cascade=> TRUE);
end;'
,next_date => to_date('07.11.2009 16:21:07','dd/mm/yyyy hh24:mi:ss')
,interval => 'null'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
------------------------------------
select * from user_jobs;

ps -auxww | grep snp._$ORACLE_SID

select * from v$version

SELECT RAWTOHEX(paddr) paddr_hex, name FROM v$bgprocess
WHERE RAWTOHEX(paddr) <> HEXTORAW(0)
AND name LIKE 'SNP%';

select * from dba_jobs_running;

select object_name,object_type from all_OBJECTS where object_name like '%DBA_JOBS_RUNNING%'

select object_name,object_type from dba_OBJECTS where object_name like '%DBA_JOBS_RUNNING%';

select text from all_views where view_name='DBA_JOBS_RUNNING';

select v.SID, v.id2 JOB, j.FAILURES,
LAST_DATE, substr(to_char(last_date,'HH24:MI:SS'),1,8) LAST_SEC,
THIS_DATE, substr(to_char(this_date,'HH24:MI:SS'),1,8) THIS_SEC,
j.field1 INSTANCE
from sys.job$ j, v$lock v
where v.type = 'JQ' and j.job (+)= v.id2

---------------------
CREATE OR REPLACE PROCEDURE SAPSR3.test IS

BEGIN

dbms_stats.gather_table_stats(ownname=> 'SAPSR3', tabname=> 'AAACC_OBJ_ERR', partname=> NULL);

END test;
/

----------
BEGIN
SYS.DBMS_JOB.REMOVE(21);
COMMIT;
END;
/

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

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'test;'
,next_date => to_date('30.10.2009 10:01:16','dd/mm/yyyy hh24:mi:ss')
,interval => 'NULL'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

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

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'BEGIN dbms_stats.gather_table_stats(ownname=> ''SAPSR3'', tabname=> ''AAACD2'', partname=> NULL); END;'
,next_date => to_date('30.10.2009 10:50:16','dd/mm/yyyy hh24:mi:ss')
,interval => 'NULL'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm

Buffer Cache Advisory - SQL

2009-11-07T04:08:00.000-08:00

COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';

Buffer Pool Hit Ratios;

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS;

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('db block gets from cache', 'consistent gets from cache',
'physical reads cache');

Find the number of buffers in the instance: ;
SELECT NAME, BLOCK_SIZE, SUM(BUFFERS)
FROM V$BUFFER_POOL
GROUP BY NAME, BLOCK_SIZE
HAVING SUM(BUFFERS) > 0;

/* Formatted on 07.11.2009 14:42:34 (QP5 v5.115.810.9015) */
SELECT NAMESPACE,
PINS,
PINHITS,
RELOADS,
INVALIDATIONS
FROM V$LIBRARYCACHE
ORDER BY NAMESPACE;select sum(pinhits) / sum(pins) "Library Cache Hit Ratio"
FROM V$LIBRARYCACHE

/* Formatted on 07.11.2009 14:42:49 (QP5 v5.115.810.9015) */
SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool',

SELECT SUM(VALUE) || ' BYTES' "TOTAL MEMORY FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;

SELECT SUM (VALUE) || ' BYTES' "TOTAL MAX MEM FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory max'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME = 'redo buffer allocation retries';

Sizing the Log Buffer;
MAX(0.5M, (128K * number of cpus))

Good initial values for the parameter PGA_AGGREGATE_TARGET might be:

* For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
* For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

SELECT * FROM V$PGASTAT;

SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM
FROM V$PROCESS;

SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE TOTAL_EXECUTIONS != 0;

SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE TOTAL_EXECUTIONS != 0;

optimal memory requirement of at least 64 KB;
SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
onepass_count, round(onepass_count*100/total, 2) onepass_perc,
multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM
(SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
sum(OPTIMAL_EXECUTIONS) optimal_count,
sum(ONEPASS_EXECUTIONS) onepass_count,
sum(MULTIPASSES_EXECUTIONS) multipass_count
FROM v$sql_workarea_histogram
WHERE low_optimal_size > 64*1024);

Querying V$SQL_WORKAREA_ACTIVE;
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,
trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM,
trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS,
trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;

The following query finds the top 10 work areas requiring most cache memory:

SELECT *
FROM
( SELECT workarea_address, operation_type, policy, estimated_optimal_size
FROM V$SQL_WORKAREA
ORDER BY estimated_optimal_size )
WHERE ROWNUM <= 10;

Using the hash value and address of a particular cursor, the following query displays the cursor execution plan, including information about the associated work areas.

col "O/1/M" format a10
col name format a20
SELECT operation, options, object_name name,
trunc(bytes/1024/1024) "input(MB)",
trunc(last_memory_used/1024) last_mem,
trunc(estimated_optimal_size/1024) optimal_mem,

Querying V$PGA_TARGET_ADVICE
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;

trunc(estimated_onepass_size/1024) onepass_mem,
decode(optimal_executions, null, null,
optimal_executions||'/'||onepass_executions||'/'||
multipasses_executions) "O/1/M"
FROM V$SQL_PLAN p, V$SQL_WORKAREA w
WHERE p.address=w.address(+)
AND p.hash_value=w.hash_value(+)
AND p.id=w.operation_id(+)
AND p.address='88BB460C'
AND p.hash_value=3738161960;

RESOLVING CORRUPT BLOCK -2

2009-11-03T03:45:00.000-08:00

DB_FILE_MULTIBLOCK_READ_COUNT SAYISI DÜŞÜRÜLEREK ÇOKLU BLOCK OKUMASI ENGELLENEBILIR. VE BOYLECE PROBLEM ONLENIR.

ORA-7445: EXCEPTION ENCOUNTERED: CORE DUMP [] [] [] [] PATCH 4257187 GEÇILMELI .
Doc ID: 452951.1

ORA-01013: user requested cancel of current operation Doc ID: 365043.1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

select /*+ ordered use_nl(lob pn ses) */
decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3,
'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.kglnaobj object_name,
pn.kglpnmod lock_mode_held,
pn.kglpnreq lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
from v$session_wait vsw,
x$kglob lob,
x$kglpn pn,
v$session ses
where vsw.event = 'library cache lock'
and vsw.p1raw = lob.kglhdadr
and lob.kglhdadr = pn.kglpnhdl
and pn.kglpnmod != 0
and pn.kglpnuse = ses.saddr

order by pn.kglpnmod desc, pn.kglpnreq desc
/

http://www.freelists.org/post/oracle-l/library-cache-lock,15

select * from v$sqlarea where parsing_schema_name='PSAPSR3'

desc v$sqlarea

select segment_name,tablespace_name from user_segments

where segment_name='S' ;

SELECT product_id
, store_id
, clearance_price
, effective_date
, out_of_stock_date
, reset_date
, flag
FROM mdo_pre_temp_retek_price;

select * FROM MDO.MDO_BSE_TEMP_RETEK_PRICE

Select * from gv$session_wait where event='library cache lock';

select ses.sid, ses.serial#,lck.* from x$kgllk lck , v$session ses
where kgllkhdl in
(select kgllkhdl from x$kgllk where kgllkreq >0)
and lck.KGLLKUSE = ses.saddr
Order by lck.KGLNAOBJ

RESOLVING CORRUPT BLOCK

2009-11-03T03:40:00.000-08:00

KASIM 2 08:16:10 2009 TARIHINDE FILE 47, BLOCK 425345 UNDAN READ EDILEMIYOR.
BLOKLAR CROSS CHECK EDILMESI;
SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER
FROM DBA_EXTENTS
WHERE FILE_ID =
AND BETWEEN BLOCK_ID
AND BLOCK_ID + BLOCKS -1;
SELECT * FROM DBA_FREE_SPACE WHERE FILE_ID=
AND BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS -1;
USER KULLANICISIYLA GIRIP DUMP LAR ICIN TABLOYU YARATIYORUZ;

CREATE TABLE S (
N NUMBER,
C VARCHAR2(4000)
) NOLOGGING TABLESPACE ;

SELECT SEGMENT_NAME,TABLESPACE_NAME FROM USER_SEGMENTS
WHERE SEGMENT_NAME='S' ;

CREATE OR REPLACE TRIGGER CORRUPT_TRIGGER
AFTER INSERT ON USER.S
REFERENCING OLD AS P_OLD NEW AS NEW_P
FOR EACH ROW
DECLARE
CORRUPT EXCEPTION;
BEGIN
IF (DBMS_ROWID.ROWID_BLOCK_NUMBER(:NEW_P.ROWID)=&BLOCKNUMBER) THEN
RAISE CORRUPT;
END IF;
EXCEPTION
WHEN CORRUPT THEN
RAISE_APPLICATION_ERROR(-20000, 'CORRUPT BLOCK HAS BEEN FORMATTED');
END;
/

ALTER TABLE SCOTT.S
ALLOCATE EXTENT (DATAFILE 'E:\XXXX\TEST.ORA' SIZE 1M);
BLOCKLARI LISTELTIYORUZ;
SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER
FROM DBA_EXTENTS
WHERE FILE_ID =
AND BETWEEN BLOCK_ID
AND BLOCK_ID + BLOCKS -1 ;

OLUŞTURDUĞUMUZ TABLOYA BLOCKLARI INSERT EDIYORUZ.FARKLI YONTEMLER VAR BIRI;
BEGIN
FOR I IN 1..1000000000 LOOP
FOR J IN 1..1000 LOOP
INSERT INTO SCOTT.S VALUES(I,'X');
END LOOP;
COMMIT;
END LOOP;
END;
TRIGER ILE INSERT EDERKEN HATALI BLOKU HATA VERECEK.
DAHA SONRA HANGI DATAFILE LAR DA PORBLEM VARSA VERIFY YAPILIR.
DATAFILE DA HATA YOKSA RAPOR DA GOZUKMEYECEKTIR.
metalik :Doc ID: 336133.1
https://metalink.oracle.com/CSP/ui/flash.html#tab=KBHome%28page=KBHome&id=%28%29%29,%28page=KBNavigator&id=%28from=BOOKMARK&bmDocType=PROBLEM&bmDocDsrc=KB&bmDocTitle=How%20to%20Format%20%3Cb%3ECorrupted%3C/b%3E%20%3Cb%3EBlock%3C/b%3E%20Not%20Part%20of%20Any%20Segment&viewingMode=1143&bmDocID=336133.1%29%29

Shared pool size, PGA USAGE TEST,STATISTICS level sql

2009-08-18T06:09:00.000-07:00

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
----------------------------------

Sql Trace

2009-08-17T23:35:00.000-07:00

--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ı

Tablo Analiz

2009-08-17T22:58:00.000-07:00

/*
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

DATA GUARD

2009-08-13T21:44:00.000-07:00

>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

RMAN errors

2009-08-12T02:49:00.000-07:00

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

ORA-00600: internal error code, arguments: [kqlfFillBindData:1]

2009-08-11T01:24:00.000-07:00

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

RMAN Recovery -2

2009-08-05T06:55:00.000-07:00

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"

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

RMAN Recovery

2009-08-05T05:34:00.000-07:00

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;

Check Database - 2

2009-08-05T03:46:00.001-07:00

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

Compute Statistics

2009-08-05T03:21:00.000-07:00

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

Check Database

2009-08-05T01:51:00.001-07:00

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

Patch Upgrade

2009-08-03T07:57:00.001-07:00

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的安装。

Data File

2009-08-03T06:30:00.000-07:00

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/

Tablespace Monitoring -3

2009-08-03T00:45:00.000-07:00

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

Tablespace Monitoring -2

2009-08-03T00:12:00.000-07:00

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 ''
RESIZE M;
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

Tablespace Monitoring

2009-08-02T23:43:00.000-07:00

AUTOEXTEND ON Next Size
In a CREATE TABLESPACE command, the DATAFILE clause is the Physical Specification component.

In pre- 9i/10g OMF manner, the DATAFILE must be specified. A (initial) filesize must also be specified. However, Autoextend's NEXT size is not mandatory and Oracle can "default". Very unfortunately, the default AUTOEXTEND ON NEXT size is 1 Database block (based on the blocksize of the tablespace).

But if you create your Tablespace using OMF (i.e. where "db_create_file_dest" is configured), then Oracle defaults the initial size to 100MB and *also* defaults the AUTOEXTEND to ON with a of 100MB ! That is much neater.

Why is the default 1 Database block bad ? Because when the datafile is full, Oracle will extend it 1 block at-a-time, making a call to the OS to add 1 block on each occassion. Obviously, even if you are extending a table or index with an Extent of 64KB, Oracle has to make 8 calls to the OS (with a datafile block size of 8KB). That is where you will see "data file init write" waits.

In the example below, the (automatic) datafile for Tablespace AN_OMF_TBS get's created with both the initial and increment at 100MB and AutoExtend ON. However, for Tablespace REGULAR_TBS, I have to specify the initial size for the datafile. If I do not specify AutoExtend, the file is created with AutoExtend OFF. For the third tablespace, called ANOTHER_TBS, when I designate AutoExtend ON but do not specify the incremental size, Oracle defaults it to 1 Oracle Block.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

SQL> alter system set db_create_file_dest='/var/tmp';
SQL> create tablespace an_omf_tbs;

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

SQL> create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf';
create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf'

SQL> create tablespace REGULAR_TBS datafile '/oracle_fs/Databases/ORT24FS/regular_tbs.dbf' size 100M;

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

SQL> create tablespace ANOTHER_TBS datafile '/oracle_fs/Databases/ORT24FS/another_tbs.dbf' size 100M autoextend on ;

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

You would be well-adviced to remember that when you create or add a non-OMF datafile, you should specify the Increment size with the AutoExtend ON. Else, you might suffer the overheads of Oracle having to make multiple calls to the OS whenever extending the datafile (imagine extending a datafile 1 block at each call for an extent of 64MB !)

SQL> create tablespace LAST_TBS datafile '/oracle_fs/Databases/ORT24FS/last_tbs.dbf' size 100M autoextend on next 100M;

Tablespace created.

SQL> select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;

Tablespace LAST_TBS's datafile is created with a meaningful Increment for AutoExtend.

otes on Specifying Datafiles and Tempfiles

*

For operating systems that support raw devices, the REUSE keyword of datafile_tempfile_spec has no meaning when specifying a raw device as a datafile. Such a CREATE TABLESPACE statement will succeed whether or not you specify REUSE.
*

You can create a tablespace within an Automatic Storage Management disk group by providing only the disk group name in the datafile_tempfile_spec. In this case, Automatic Storage Management creates a datafile in the specified disk group with a system-generated filename. The datafile is auto-extensible with an unlimited maximum size and a default size of 100 MB. You can use the autoextend_clause to override the default size.
*

If you use one of the reference forms of the ASM_filename, which refers to an existing file, then you must also specify REUSE.

http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10759/statements_7003.htm
---------------------------------------------------------------------------
CREATE TABLESPACE
DATAFILE SIZE sayı K|M [AUTOEXTEND OFF | {AUTIOEXTEND
ON | NEXT sayı K|M | MAXSIZE UNLIMITED | sayi K|M |}
[ONLINE | OFFLINE ]
[PERMANENT | TEMPORARY ]

Kullanılan ifadelerin açıklamaları :
Tablespace ismi : Oluşturulacak tablespace verilecek addır.
DATAFILE : Tablespace’i oluşturan veri dosyasının tanımlandığı bölümdür.
Dosya ismi : DATAFILE’ın path’ini belirtildiği bölümdür.
AUTOEXTEND OFF : Datafile otomatik artışını iptal etmekte kullanılan komuttur.

AUTOEXTEND ON : Datafile otomatik artışını sağlayan komuttur.
NEXT : Datafile’ ın genişleme miktarı.
MAXSIZE : Datafile’ın genişleyebileceği en son miktardır.
UNLIMITED : Datafile’ın sınırsız büyüyebileceğini göstermektedir. Belirtilmese dahi standart olarak seçilidir.
ONLINE : Tablespace’i oluşturduktan hemen sonra aktif olacağını belirten bölümdür. Standart olarak seçilidir.

OFFLINE : Tablespace’i oluşturduktan hemen sonra pasif olacağını belirten bölümdür.

PERMANENT : Tablespace içinde tutulacak olan nesnelerin kalıcı olarak saklanacağını belirten bölümdür. Standart olarak seçilidir.

TEMPORARY : Tablespace’te geçici nesnelerin tutulacağının belirtildiği bölümdür.

NOT : Tablespace oluşturmak ve yönetmek için STORAGE MANAGER kullanılmasını tavsiye ederiz.

RMAN Backup

2009-07-31T05:54:00.001-07:00

1) SQL> archive log list;

komutu ile archivelog da olup olmadığını anla.

2) DOS> rman target sys/oracle@orcl

3) Yedekleri e:\vtyedek klasörüne alacağız. 1 backup saklama stratejisi ile backup alacağız. Klasörü RMAN yaratmaz

CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
'E:\vtyedek\ora_df_%t_%s_%p';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'E:\vtyedek\%F';
CONFIGURE BACKUP OPTIMIZATION ON;

------------------------------------------------------------------------------------------
redundancy 1 yerine bu satırı da verebilirsin: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; -- son 7 gün lük yedeği sakla demek
yedek yeri belirtilirken max dosya boyutu da verilebilir: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
'e:\vtyedek\ora_df_%t_%s_%p' maxpiecesize 2G; -10 gb ise 2 gb a böluyor.

show all;

tüm veritabanını yedeğini alalım:

BACKUP DATABASE PLUS ARCHIVELOG;

--crosscheck archivelog all

Veritabanı yedeğinin sağlıklı olup olmadığını, eksik backup dosyası olup olmadığını

RESTORE DATABASE VALIDATE;

komutuyla görebiliriz. Eksik dosya olursa hata mesajı verir ve eksik dosyayı söyler.

İşin ne kadarının bittiğini gösterir:

SQL> SELECT SID, SERIAL#, OPNAME,CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
--AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
/
----------------------------
create or replace function yedek_yuzde_kac return varchar2 is
yuzde varchar2(20);
begin

select '%'|| ROUND (SOFAR/TOTALWORK*100,2)
into yuzde
from v$session_longops
where opname like 'RMAN%'
and opname not like '%aggregate%'
and TOTALWORK <> 0
and SOFAR <> TOTALWORK;
return yuzde;

end;

grant execute
on yedek_yuzde_kac
to public

create public synonym yyk for yedek_yuzde_kac
---------------------

datafile ları tek tek yedekle:

REPORT SCHEMA;
--------------
report obsolete; eski yedekleri listeler

delete noprompt obsolete;

crosscheck backup;

BACKUP VALIDATE DATABASE

----
BACKUP DATAFILE 5;

tablespace düzeyinde yedekle:

BACKUP TABLESPACE EXAMPLE;

incremental backup al:

BACKUP INCREMENTAL LEVEL 0 DATABASE;

datafile düzeyinde incremental backup alabiliriz:

BACKUP INCREMENTAL LEVEL 1 DATAFILE 5;

archivelogları yedekliyoruz:

BACKUP ARCHIVELOG ALL;

backup ihtiyacı olan dosyalar:

REPORT NEED BACKUP;

eski backup dosyalarını göster:

REPORT OBSOLETE;

eski backup dosyalarını sil:

DELETE OBSOLETE;

database ve archivelog yedeklerini validate edelim:

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

recover edilemeyecek olanları gör:

REPORT UNRECOVERABLE;

bugün yedeğinin alındığından eminseniz eski archivelogları boşaltmak için:

DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1';

crosscheck yaparak backupsetlerin yerlerinde olup olmadığına bakabiliriz:

CROSSCHECK BACKUP OF DATABASE;

datafile, tablespace ve controlfile yedekleme bilgilerini görebiliriz:

LIST BACKUP OF DATAFILE 5;

LIST BACKUP OF CONTROLFILE;

Archive log Mode

2009-07-31T04:02:00.000-07:00

exp system/oracle@orcl file=c:\exp_hr.dmp owner=hr
possible charset conversion
NLS_LANG

alter user hr identified by hr account unlock;

select * from job_history
drop table job_history purge
flashback table job_history to before drop

create table kopya_emp5 as
select * from employees

select * from kopya_emp5
as of timestamp to_timestamp('06/05/2009 10:10', 'DD/MM/YYYY HH24:MI')
minus
select * from kopya_emp5

alter table employees enable row movement

alter system switch logfile;

select * from v$log;

show parameter undo_retention;

show parameter sessions

select name, value
from v$parameter
where name like '%spfile%'

create spfile from pfile;

archive log list;

shutdown immediate; normal - transactional - abort

grant advisor to hr;

alter system set undo_retention=900 scope=both; spfile - memory

show parameter control_files;

LOG_ARCHIVE_DEST_1=’LOCATION=C:\vtyedek\archives’ - mandatory reopen=300

Archivelog formatı verelim:

LOG_ARCHIVE_FORMAT='ARC_%S_%R_%T.ARC'

Veritabanını arşiv moda geçirmek için:

SQL> shutdown immediate;

İle kapayıyoruz.

SQL> startup mount;

Komutu ile mount modunda açıyoruz.

SQL> alter database archivelog;

Komutu ile veritabanını arşiv moduna geçiriyoruz.

SQL> alter database open;

Komutu ile veritabanını açıyoruz

Şimdi archive loga geçip geçmediğini test etmek için;

SQL> alter system switch logfile;

Komutuyla archive log üretiyoruz. (C:\archives klasöründe archive dosyaları oluşmaya başlayacak. )

select name, log_mode from v$database;

DBA SQL AWR

2009-07-29T23:40:00.000-07:00

DIKKAT :

Asagidaki tum SQL komutlari, ORACLE AWR raporlarindan bilgiyi alir. Bu yuzden oncelikle sistem uzerinde AWR raporlarinin calismis oldugundan emin olmalisiniz. Ayrica AWR raporlarinin calisma zamanlari en az yarimsaat olmali ki daha dogru sonuclar alinabilsin.

Oracle 10g uzerinde RAC sistemleri mevcut ise insantce degerlerinden bu gorulebilir. Eger yoksa instance_number sadece 1 olacaktir.

AWR raporlarinin baslangic tarih ve saat ile bitis tarih ve saat degerlerini istediginiz gibi girebilirsiniz. Ozellikle is saatleri arasinda alacaginiz raporlamalar size daha dogru sonuclar verecektir.

/* ###
/* ### Oracle CPU Kullanim Oranlari (CPU yogunlugunun gorulmesi)
/* ###

select smr.INSTANCE_NUMBER,

min(smr.MINVAL) min, max(smr.MAXVAL) max,

round(avg(smr.MINVAL)) minavg, round(avg(smr.MAXVAL)) maxavg,

round(avg(smr.AVERAGE)) avg

from DBA_HIST_SYSMETRIC_SUMMARY smr,

v$database d

where

smr.BEGIN_TIME >= to_date('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

smr.END_TIME <= to_date('YYYY-AA-GG S2’,'YYYY-MM-DD HH24') and

smr.DBID=d.DBID and

smr.METRIC_NAME='Database CPU Time Ratio'

group by smr.INSTANCE_NUMBER;

INSTANCE_NUMBER MIN MAX MINAVG MAXAVG AVG

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

2 0 41.5811655 0 26 18

1 0 34.9719813 0 20 12

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

/* ###
/* ### Oracle Bekleme Oranlari (Oracle sisteminin kullanicilardan is bekleme zaman degerleri)
/* ###

select smr.INSTANCE_NUMBER,

min(smr.MINVAL) min, max(smr.MAXVAL) max,

round(avg(smr.MINVAL)) minavg, round(avg(smr.MAXVAL)) maxavg,

round(avg(smr.AVERAGE)) avg

from DBA_HIST_SYSMETRIC_SUMMARY smr,

v$database d

where

smr.BEGIN_TIME >= to_date('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

smr.END_TIME <= to_date('YYYY-AA-GG S2','YYYY-MM-DD HH24') and

smr.DBID=d.DBID and

smr.METRIC_NAME='Database Wait Time Ratio'

group by smr.INSTANCE_NUMBER;

INSTANCE_NUMBER MIN MAX MINAVG MAXAVG AVG

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

2 0 98.7966488 0 92 82

1 0 99.4121159 0 95 88

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

/* ###
/* ### En çok CPU kullanan -yoran- ilk 10 SQL (CPU TIME Degerine göre)
/* ###

select * from

(

select sqs.INSTANCE_NUMBER, sqs.SQL_ID,

sum(EXECUTIONS_DELTA) EXECUTIONS,

round(sum(ELAPSED_TIME_DELTA/1000/1000)) ELAPSED_TIME,

round(sum(CPU_TIME_DELTA/1000/1000)) CPU_TIME,

sum(BUFFER_GETS_DELTA) BUFFER_GETS

from DBA_HIST_SQLSTAT sqs,

DBA_HIST_SNAPSHOT snp,

v$database d

where

snp.BEGIN_INTERVAL_TIME >= to_timestamp('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

snp.END_INTERVAL_TIME <= to_timestamp('YYYY-AA-GG S2’,'YYYY-MM-DD HH24') and

sqs.DBID=d.DBID and

sqs.DBID=snp.DBID and

sqs.INSTANCE_NUMBER=snp.INSTANCE_NUMBER and

sqs.SNAP_ID=snp.SNAP_ID

group by sqs.INSTANCE_NUMBER,sqs.SQL_ID

order by CPU_TIME desc

)

where rownum<=10;

INSTANCE_NUMBER SQL_ID EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS

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

2 b5hzmkcpbmn23 29148 14241 7619 211526710

1 6ahddxp5un7a3 7 4333 3760 79255773

1 gcfyqusdngbv4 207011 5213 3746 701526739

1 53wbuxzx13ndw 313 67766 2941 92357833

1 b5hzmkcpbmn23 9073 4675 2774 64520393

2 g5jnvm7kgtyah 1586 4345 2210 235469477

2 gxknp4j7gd8g0 6331013 2809 2040 186057685

1 829355xf7xjj4 3513 2592 1356 42605109

1 1z6rfm3gfbzmv 125 27509 1127 29389172

1 gqau08wu3zrgp 286155 13099 981 6029967

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

/* ###
/* ### En uzun süren ilk 10 SQL (Elepsad_time degerine gore):
/* ###

select * from

(

select sqs.INSTANCE_NUMBER, sqs.SQL_ID,

sum(EXECUTIONS_DELTA) EXECUTIONS,

round(sum(ELAPSED_TIME_DELTA/1000/1000)) ELAPSED_TIME,

round(sum(CPU_TIME_DELTA/1000/1000)) CPU_TIME,

sum(BUFFER_GETS_DELTA) BUFFER_GETS

from DBA_HIST_SQLSTAT sqs,

DBA_HIST_SNAPSHOT snp,

v$database d

where

snp.BEGIN_INTERVAL_TIME >= to_timestamp('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

snp.END_INTERVAL_TIME <= to_timestamp('YYYY-AA-GG S2','YYYY-MM-DD HH24') and

sqs.DBID=d.DBID and

sqs.DBID=snp.DBID and

sqs.INSTANCE_NUMBER=snp.INSTANCE_NUMBER and

sqs.SNAP_ID=snp.SNAP_ID

group by sqs.INSTANCE_NUMBER,sqs.SQL_ID

order by ELAPSED_TIME desc

)

where rownum<=10;

INSTANCE_NUMBER SQL_ID EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS

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

1 53wbuxzx13ndw 313 67766 2941 92357833

1 1z6rfm3gfbzmv 125 27509 1127 29389172

2 gt1sjbrcc75y7 26497 25423 57 1467588

2 cm273h7pxw8k9 52906 25214 127 1641933

2 af582nx1dpb7y 74522 24179 118 973147

1 17hz8amdfpkth 305 21702 406 4864716

2 4xvr2wj20qjna 5228 20716 357 16711832

1 dbcc5nqf4dyxf 104817 18855 244 6366708

1 d0k0dj5v6yc6p 287381 17005 960 5148445

1 20wbqjfrqmfdx 85300 17001 236 7369135

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

/* ###
/* ### En çok mantiksal okuma yapan ilk 10 SQL (Buffer Gets degerine gore)
/* ###

select * from

(

select sqs.INSTANCE_NUMBER, sqs.SQL_ID,

sum(EXECUTIONS_DELTA) EXECUTIONS,

round(sum(ELAPSED_TIME_DELTA/1000/1000)) ELAPSED_TIME,

round(sum(CPU_TIME_DELTA/1000/1000)) CPU_TIME,

sum(BUFFER_GETS_DELTA) BUFFER_GETS

from DBA_HIST_SQLSTAT sqs,

DBA_HIST_SNAPSHOT snp,

v$database d

where

snp.BEGIN_INTERVAL_TIME >= to_timestamp('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

snp.END_INTERVAL_TIME <= to_timestamp('YYYY-AA-GG S2’,'YYYY-MM-DD HH24') and

sqs.DBID=d.DBID and

sqs.DBID=snp.DBID and

sqs.INSTANCE_NUMBER=snp.INSTANCE_NUMBER and

sqs.SNAP_ID=snp.SNAP_ID

group by sqs.INSTANCE_NUMBER,sqs.SQL_ID

order by BUFFER_GETS desc

)

where rownum<=10;

INSTANCE_NUMBER SQL_ID EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS

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

1 gcfyqusdngbv4 207011 5213 3746 701526739

2 g5jnvm7kgtyah 1586 4345 2210 235469477

2 b5hzmkcpbmn23 29148 14241 7619 211526710

2 gxknp4j7gd8g0 6331013 2809 2040 186057685

1 53wbuxzx13ndw 313 67766 2941 92357833

2 gcc76mbkfzbrg 19962 1347 909 87347052

1 6ahddxp5un7a3 7 4333 3760 79255773

1 b5hzmkcpbmn23 9073 4675 2774 64520393

1 79aunmuktf6sf 8613 1811 956 47733280

2 a859cj7gtwggw 3909566 535 326 43179158

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

/* ###
/* ### En çok DISK okuma yapan ilk 10 SQL (DISK_READS degeri gore)
/* ###

select * from

(

select sqs.INSTANCE_NUMBER, sqs.SQL_ID,

sum(EXECUTIONS_DELTA) EXECUTIONS,

round(sum(ELAPSED_TIME_DELTA/1000/1000)) ELAPSED_TIME,

round(sum(CPU_TIME_DELTA/1000/1000)) CPU_TIME,

sum(BUFFER_GETS_DELTA) BUFFER_GETS,

sum(DISK_READS_DELTA) DISK_READS

from DBA_HIST_SQLSTAT sqs,

DBA_HIST_SNAPSHOT snp,

v$database d

where

snp.BEGIN_INTERVAL_TIME >= to_timestamp('YYYY-AA-GG S1','YYYY-MM-DD HH24') and

snp.END_INTERVAL_TIME <= to_timestamp('YYYY-AA-GG S2’,'YYYY-MM-DD HH24') and

sqs.DBID=d.DBID and

sqs.DBID=snp.DBID and

sqs.INSTANCE_NUMBER=snp.INSTANCE_NUMBER and

sqs.SNAP_ID=snp.SNAP_ID

group by sqs.INSTANCE_NUMBER,sqs.SQL_ID

order by DISK_READS desc

)

where rownum<=10;

INSTANCE_NUMBER SQL_ID EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS

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

1 53wbuxzx13ndw 313 67766 2941 92357833 4428838

2 33ug56m93d3za 0 12325 285 7445226 3156943

2 3n4p7gcjah2tb 1 11248 228 7907331 2527719

2 fwygkuyv8csvk 1 10730 200 7075954 2191880

1 1z6rfm3gfbzmv 125 27509 1127 29389172 1637314

1 17hz8amdfpkth 305 21702 406 4864716 1245073

2 4xvr2wj20qjna 5228 20716 357 16711832 1176634

1 gdna3tyy8ntu8 379 16587 419 9084651 1144990

1 8acwusvy4jx71 102 11412 497 19688222 894536

2 c0njscvt37djh 1 2404 75 1347772 788968

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

DBA SQL -3 Advanced

2009-07-29T23:39:00.002-07:00

/* ### Top 10 by Buffer Gets:#####
/* ### Buffer uzerinden 10000'den fazla bilgi alan ilk 10 sql listesi

set linesize 250
set pagesize 250
SELECT * FROM (SELECT substr(sql_text,1,40) sql, buffer_gets, executions, buffer_gets/executions "Gets/Exec", hash_value,address
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <= 10;
/* ### Top 10 by Physical Reads:#####
/* ### Disk uzerinden okuma sayisi 1000'den fazla olan ilk 10 sql listesi.
/* ### Disk uzerinden okuma ne kadar fazla ise performans o kadar kotudur. Bu yuzden bu sql'lerin tekrardan gozden gecirilmesi gerekir.

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
disk_reads, executions,-- disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10;
/* ### Top 10 by Executions:#####
/* ### sql bazinda calisan sayisi 100'den fazla olan 10 SQL cumleciginin listesi.
/* ### Bir sql ne kadar cok calisiyorsa o sql'lin yaptigi is kullanicilar tarafindan cok kullaniliyor demektir. Bu tur is kurallarini gozden gecirmek tavsiye edilir.

set linesize 100
set pagesize 100
SELECT * FROM (SELECT substr(sql_text,1,40) sql, executions, rows_processed, rows_processed/executions "Rows/Exec", hash_value, address
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10;
/* ### Top 10 by Parse Calls:#####
/* ### Degiskenlerin parametre olarak gecmedigi tipteki sql'lerin sayisinin 1000'den fazla olan 10 SQL cumleciginin listesi.

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql, parse_calls, executions, hash_value,address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <= 10;

/* ### Top 10 by Shareble Memory:#####
/* ### 1048576 byte'dan daha fazla bellek kullanan en ustteki 10 SQL cumleciginin listesi.
/* ### Bir sql calistigi ortamda cok daha fazla bellek tuketmemesi gerekir. Buna dikkat edilmesi tavsiye edilir.

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,sharable_mem, executions, hash_value,address
FROM V$SQLAREA
WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <= 10;
/* ### Top 10 by Version Count:#####
/* ### Degisik parametrelerle calistirilan sql'lerin version sayisi 20'den fazla olan 10 SQL cumleciginin listesi.
/* ### Bir sql degisik parametrelerle calistiriliyorsa her defasinda derlenir bu da hem performans hem de bellek acisindan olumsuz durum demektir. BIND variable degerleri kullanarak sql'lerin her defasinda degisik sekilde calistirilmamasina dikkat edilmesi tavsiye edilir.

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
version_count, executions, hash_value,address
FROM V$SQLAREA
WHERE version_count > 20
ORDER BY version_count DESC)
WHERE rownum <= 10;

DBA SQL - 2

2009-07-29T23:39:00.001-07:00

/* ### session toplam ve aktif izleme #####
/* ### oracle ait sessionlar secilmemistir (tipi background olmayanlar)
/* ### sistem uzerinde log on olan toplam kullanıcı ve o an aktif olanlari gosterir
/* ### bu sql, oracle 10g rac sisteminde 2 makina olarak var olan bir sistem icindir.

select count(*) TOPLAM,
(select count(*) from gv$session where inst_id=1 and type != 'BACKGROUND' ) TOPLAMN1,
(select count(*) from gv$session where status = 'ACTIVE' and inst_id=1 and type != 'BACKGROUND' ) AKTIFN1,
(select count(*) from gv$session where inst_id=2 and type != 'BACKGROUND') TOPLAMN2,
(select count(*) from gv$session where status = 'ACTIVE' and inst_id=2 and type != 'BACKGROUND') AKTIFN2
from gv$session group by 1,2,3;
/* ### instance bazinda calisan ACTIVE sql toplamlari ###
/* ### sql cumleciklerinin ilk 70 karakterini ve ayni SQL'lerden 3'den fazla olanlari gosterir
/* ### gv$session tablosu RAC sisteminin tumunu kapsar.

SELECT SUBSTR (sql_text, 1, 70) SQL, COUNT (1) adet, s.inst_id INSTANCE, SYSDATE tarih
FROM gv$session s, gv$sql t
WHERE s.sql_hash_value = t.hash_value
AND sql_hash_value <> 0
AND status = 'ACTIVE'
GROUP BY SUBSTR (sql_text, 1, 70), s.inst_id
HAVING COUNT (1) > 3
ORDER BY adet DESC /* ### sistemde tablolara FULL ACCESS erisim yapan sql'leri ve onu kullananlarinin listesini verir.
/* ### object_owner'da XXXXX ile tablolari yaratan schema name kullanilacaktir.

select sql_text,p.operation,p.options,p.object_name,e.machine,e.username,e.status,s.sql_id,event,sid
from v$sql_plan p , v$sqlarea s , v$session e
where operation='TABLE ACCESS' and options='FULL' and object_owner='XXXXXXXX'
and p.plan_hash_value=s.plan_hash_value
and e.sql_id=s.SQL_Id
order by p.address /* ### sistemde o an calisan ACTIVE SQL'lerin listesi */

select e.sql_id, e.sid, s.sql_text
from v$sqlarea s , v$session e
where s.sql_id = e.sql_id and e.status='ACTIVE' and e.type !='BACKGROUND'
order by 1 /* ### sistemde bugunden 2 gun once logon olmus ve hala ACTIVE olan sessionlarin listesi *
/* ### birsekilde sistemden normal sekilde cikmamis ve sistemde takili kalmis sessionlari bulup ve kill etmek icin kullanilir.
/* ### KILL etmeden once session gercekten aktif olup olmadigini kontrol ediniz !

select s.* from v$session s, v$process p
where s.schemaname='ESASLIVE' and s.status='ACTIVE'
and s.paddr=p.addr
and logon_time/* ### istenilen sessionlari UNIX uzerinden silmek icin isletim sistemindeki PID numarasi ile bulup kill -9 ile dusurmek icin
/* ### v$sql.sql_id='XXXXXXXXXXX' sql_id bilinen durumlar icindir.
/* ### v$session.sid=XXXX session SID biliniyorsa yukaridaki deger yerine kullanilir.

select 'kill -9 '||spid from v$process where addr in
(
select paddr from v$session, v$sql
where v$session.sql_id = v$sql.sql_id
and v$session.status='ACTIVE'
and v$sql.sql_id='79wkdjjz4p693'
and type != 'BACKGROUND'
) ; /* ### istenilen sessionlari ORACLE uzerinden silmek icin SID ve SERIAL# degerlerini alarak ALTER SYSTEM KILL SESSION komutu ile dusurmek icin örnekler...
/* ### bu sql calistirildiktan sonra listenen SQL'leri tekrar secip calistirmak gerekmektedir.

select 'alter system kill session '||''''||SID||','||SERIAL#||''''||';' from v$session
where sql_id='1z6rfm3gfbzmv' -- bu sql_id sahip olan sessionlarin hepsini secer

select 'alter system kill session '||''''||SID||','||SERIAL#||''''||' IMMEDIATE;' from v$session where schemaname='XXXXXX' and status='INACTIVE' order by logon_time desc; -- XXXXX schema kullanan, durumu INACTIVE olan ve baglanti zamani eski olan sessionlari listeler. daha sonra bu sessionlari toplu olarak sistemden dusurebilirsiniz. /* ### hash value sunu bildigin sql in çalışan gercek execution planı
/* ### zaman zaman sql'lerin execution planlari farkliliklar gosterebilir.

select distinct id, parent_id, lpad (' ', depth) || operation operation,options, object_name, cost
from v$sql_plan
where hash_value = 1752547357
order by id; /* ### RAC sistemlerinde makinalarda o anda hangi islemlerin oldugunun listesini almak icindir.
/* ### EVENT'lerin sayisina gore buyukten kucuge dogru sirali olarak listelenmistir.

select inst_id,SCHEMANAME,event,count(*)
from gv$session
group by inst_id,SCHEMANAME,event
order by 4 desc

DBA SQL - 1

2009-07-29T23:38:00.000-07:00

/* ### Tablolarda alan aciklamalarinin toplu olarak girilmesi #####
/* ### Bazi alan isimleri kullanim amaclarina bagli olarak farkli tablolarda ayni isimle yer alabilir. (CRE_DATE, MOD_USER, FATURA_NO vb.) Bunlara tek seferde aciklama girmek icin asagidaki sql kullanilabilir.
/* ### owner='XXXXXX' sizin islem yapacaginiz schema adini girmeniz gerekecektir.
/* ### ZZZZZZZZZ yerine alan aciklamasini girilmelidir.
/* ### column_name='QQQQQQQQ' yerine istediginiz alan adi girilmelidir.
/* ### Bu sql calistirildiktan sonra listelenen SQL cümlecikleri secilip tekrar calistirilmalidir.

select 'COMMENT ON COLUMN XXXXXXXE.'||table_name||'.'||column_name||' IS '||''''||'ZZZZZZZZZZ'||''''||';'
from dba_col_comments where owner='XXXXXXXX' and comments is null
and column_name='QQQQQQQQ'

/* ### schema'ya ait tablolarin index kullanılip/kullanmadiginin monitor edilmesi ###
/* ### sql cumlecikleri calistikca tablolara ait indexleri kullanirsa indexin monitor degeri YES olur. Boylece o indexin kullanilmis oldugunu anlasilir.

-- scheman'in indexlerinin cikartilmasi ve toplu olarak acilmasi(schemaadi yerine sizin schema adini girmelisiniz)
select 'alter index schemaadi.'||index_name||' monitoring usage;' from dba_indexes where owner=schemaadi order by table_name;

-- monitor'Un acilmasi tek bir index icin (index.adi yerine istediginiz indexin adini giriniz)
alter index "index.adi" monitoring usage;

-- izlenmesi ((schemaadi yerine sizin schema adini girmelisiniz)
select * from v$object_usage where owner=schemaadi;

-- monitor'un kapatilmasi (index.adi yerine istediginiz indexin adini giriniz)
alter index "index.name" nomonitoring usage; /* ### table, index ve partition'larin toplam buyuklukleri
/* ### ozellikle partitionlarin buyukluklerini takip etmek icin kullanilir.

select segment_name,segment_type,bytes/1024 from dba_segments order by bytes desc /* ### Yanlis bir tablespace uzerinde acilan indexlerin dogru tablespace uzerinde rebuild edilmesi
/* ### HEDEFTS dogru tablespace adi
/* ### YANLISTS yanlis kullanilan tablespace adi.
/* ### SCMADI indexleri tasinacak scema adi.

select 'alter index '||owner||'.'||index_name||' rebuild tablespace HEDEFTS ;'
from dba_indexes
where tablespace_name='YANLISTS' and owner='SCHMADI' and index_type='NORMAL'
order by owner /* ### table ve index'lerin initrans değerinin yükseltilmesi
/* ### XXXXXX schemasina ait table ve indexlerin ini_trans degeri 250'den kucuk olanlarin secilmesi.
/* ### Asagidaki sql calistirildiktan sonra listelenen SQL cumlecikleri secilerek tekrar calistirilmalidir.

-- table's of schema
select 'alter table ' || owner || '.' ||table_name || ' initrans 255;'
from dba_tables
where owner in ('XXXXXX','') and ini_trans <250

-- index's of schema
select 'alter index ' || owner || '.' ||index_name || ' initrans 255;'
from dba_indexes
where owner in ('XXXXX','') and ini_trans <250
/* ### Index istatistik degerlerinin kontrol edilmesi.
/* ### Eger kayit sayisi fazla olan tablolarda indexli kolonlarda tekrar edilen deger 1000 den kucukse o zaman bu indexleri tekrar gozden gecirmek gerekir.
/* ### v$session.sid=XXXX session SID biliniyorsa yukaridaki deger yerine kullanilir.
/* ### XXXXXXXXX olarak schema adi girilmelidir.
/* ### burada tablolardaki kayit sayisi 1.000.000 dan fazla olanlar secilmektedir.

select c.TABLE_NAME,c.INDEX_NAME,c.column_name,s.DISTINCT_KEYS,s.NUM_ROWS
from dba_ind_columns c,dba_ind_statistics s,dba_tables t
where c.INDEX_OWNER='XXXXXXXX'
and c.index_NAME=s.index_NAME
and s.OWNER='XXXXXXXX'
and t.OWNER='XXXXXXXX'
and t.table_name=s.TABLE_NAME
and s.DISTINCT_KEYS<1000 and t.num_rows>1000000 and s.partition_name is null
order by 1,4 /* ### SQL PLUS uzerinden calisan sql'in execution plan bilgilerinin alinmasi (Bu ornek UNIX sistemler uzerinden verilmistir.)

-- 1. İzleme işlemini yapacak kullanıcının öncelikle bir PLAN_TABLE tablosuna sahip olması gerekir. Genellikle $ORACLE_HOME/rdbms/admin dizini altına yer alan utlxplan.sql script’i, izleme işlemini yapacak kullanıcı tarafından SQL*Plus’tan çalıştırılır.
SQL> @utlxplan

-- 2. İzlenecek sorgu SQL*Plus’tan aşağıdaki şekilde çalıştırılır.
SQL> explain plan
set statement_id=’MUSTERI’ for
select MUSTERI_ADI
from MUSTERI
where MUSTERI_NO=1;

-- 3. Plan, aşağıdaki sorgu ile görüntülenir.
SQL> select lpad(‘ ‘,2*(level-1)) || operation || ‘ ‘
|| options || ‘ ‘ || object_name || ‘ ‘ ||
Decode(id,0, ‘Cost = ‘ || position) “Query Plan”
from PLAN_TABLE
start with id=0 and
statement_id=’MUSTERI’
connect by prior id=parent_id
and statement_id=’MUSTERI’;
/* ### Index analiz edilmesi (SQL PLUS Uzerinden calistirilacaktir.)
/* ### XXXXXX ile schema adi ve "indexadi" ile analiz edilecek index girilmelidir.
/* ### Burada index'in %100 degeri kontrol edilecektir. istenirse bu deger %25, %50 olarak girilebilir.
/* ### Degree => 4 ile paralelizm 4 olarak set edilmistir. sistemdeki cpu sayisina bagli olarak 1,2,4,8 gibi degerler girilebilir.

BEGIN
SYS.DBMS_STATS.GATHER_INDEX_STATS (
OwnName => 'XXXXX'
,IndName => 'indexadi'
,Estimate_Percent => 100
,Degree => 4
,No_Invalidate => FALSE);
END;
/

-- ayni islemin daha basit ve default degerlerle yapiabilecek hali.
ANALYZE INDEX XXXXXX.indexadi COMPUTE STATISTICS ONLINE /

ORACLE VERİTABANI İPUÇLARI

2009-07-29T23:35:00.001-07:00

- Büyük tablolarınızı, önemli tablolarınızı, bir instance uzerindeki farkli schema bilgilerini, ayrı table space uzerinde tutun. Ozellikle restore işleminde daha az sorun yaşarsınız. Restore ederken tum DB yerine sadece istediginiz tablespace geri donebilirsiniz.

- tablolarınızı ayri bir tablespace'de indexlerinizi ayri bir tablespace üzerinde tutmanız size bakım, taşıma gibi konularda avantaj getirir.

- ORACLE 10g ile gelen flash ozelligini inceleyin, gerekebilecek durumlar mutlaka olacaktır.

- Tablolarınızdaki satır sayısı arttığında yazdığınız sql'lerin execution planları değişiklik gösterecektir. Arada AWR calistirarak sistemde uzun süren SQL'leri, diskten cok okuma yapan ya da "full access" yapan sql'leri görebilirsiniz.

- Index kullanımının, Tablo satır sayısının %7 ila %10 gibi bir değere erişileceği durumlarda faydalı olacağını bilmelisiniz.

- ORACLE 10g ile gelen "Enterprise Manager" çalışması özellikle makina üzerine yoğunluk olduğunda sorunlu olabiliyor. Bu yüzden özellikle EM üzerinde job tanımlamalarını yapmamanızı bunun yerine DBMS SCHUDULER kullanmanızı öneririm.

- Enterprise Management üzerindeki saat dilim ayarlamaları sorun yaratabileceğini unutmayin. Saatlerin ileri veya geri alınması durumlarında tanımladığınız saatte çalışacak job'lar da sorunlar yaşayabilirsiniz.

- Procedurler uzerinde özellikle tarih ve saat formatlari çalıştığınız PC ile server sistemleri arasında farklı olabileceğinden "ALTER SESSION NLS DATE FORMAT" komutunu kullanmayı alışkanlık haline getirin.

- AWR raporlarının iş saatlerinde yarım saatlik dilimlerde, iş dışı saatlerinde bir saatlik dilimlerde çalışmasını set edin. Unutmayın ki, AWR raporları sistemde bir sorun olduğu anda neden olduğuna dair bilgiyi vermez. Bir zaman dilimi tam olarak bitmeden AWR raporu tamam olmayacaktır. Zaman dilimlerini kısa tutarsanız sistem üzerine yük getirecektir !

- Partition özelliğini kullanmanız size çalıştırdığınız sorgularda performans sağlayacaktır. Ancak partition edilecek alanların nasıl olması gerektiği konusunda titiz davranın. Daha çok hangi alanlar üzerinden sorgulamalar yapıyorsanız o alanlari (genellikle tarih olur) kullanmayı tercih edin.

- Partition tablolarındaki eski partition tablolarının taşınması, düşürülmesinin o kadar kolay olmayacağını unutmayin. Özellikle global index kullanımı mevcutsa indexler tekrardan yaratilacağından bu işlem uzun sürebilir.

- insert, delete işlemleri sonrası zamanla tablolarda performans sorunları yaşayabilirsiniz. Eğer zamanınız ve yeriniz varsa tabloları export ve başka tablespace üzerine import ederek performans artışı sağlayabilirsiniz. Bu işlem öncesi backup almayi unutmayın !

http://www.bilgisite.com/oracle/ora_03.htm

Oracle Performance

2009-07-28T07:30:00.000-07:00

select * from sapsr3.TPFYPROPTY
where obj_name='ENQUE/TABLE_SIZE'

SELECT job_name, owner, enabled, state
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'GATHER_STATS_JOB';

select * from V$SGA_CURRENT_RESIZE_OPS

select * from V$SGA_RESIZE_OPS;

select component, current_size, granule_size from v$sga_dynamic_components;

select * from V$SGA_DYNAMIC_FREE_MEMORY;

OBJECT_NAME = 'COEP~Z02' AND

SELECT
SUBSTR(OBJECT_NAME, 1, 40) SEGMENT_NAME,SUBSTR(STATISTIC_NAME, 1, 20) STATISTIC_NAME,VALUE "NUMBER"
FROM V$SEGMENT_STATISTICS
WHERE
STATISTIC_NAME IN ('logical reads', 'physical reads');

SELECT SUBSTR(OBJECT_OWNER, 1, 10) OWNER,
SUBSTR(OBJECT_NAME, 1, 25) OBJECT,
SUBSTR(OPERATION, 1, 15) OPERATION,
SUBSTR(OPTIONS, 1, 15) OPTIONS,
COUNT(*) "NUMBER"
FROM V$SQL_PLAN
GROUP BY OBJECT_OWNER, OBJECT_NAME, OPERATION, OPTIONS;

SELECT BYTES FROM V$SGASTAT
WHERE POOL = 'shared pool' AND NAME = 'free memory';

select * from V$ARCHIVE_DEST
-----------------------------
SELECT job_name, owner, enabled, state
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'GATHER_STATS_JOB';

-------------------------------------
select * from sapsr3.TPFYPROPTY
where obj_name='ENQUE/TABLE_SIZE'

select * from V$ARCHIVE_DESt

select * from v$parameter
where name like '%archive%'

select * from v$_lock

----
abap/buffersize : Program Buffer Size
500MB 000 1500MB 000 kb
enque/table_size
16384 96MB 000
ipc/shm_psize_10
150MB 000 000 600MB 000 000
ipc/shm_psize_40 Size of shared memory segment
124MB 000 000 250MB 000 000 byte

RSPLSE
enque/table_size or abap/shared_objects_size_MB

http://sapkb.blogspot.com/search/label/Oracle
http://benstudycenter.blogspot.com/2008_01_01_archive.html

ORACLE Trace and Dump file Alert sql

2009-07-17T05:49:00.001-07:00

create or replace procedure external_alert_log as
path_bdump varchar2(4000);
name_alert varchar2(100);
begin

select
value into path_bdump
from
sys.v_$parameter
where
name = 'background_dump_dest';

select
'alert_' || value || '.log' into name_alert
from
sys.v_$parameter
where
name = 'db_name';

execute immediate 'create or replace directory background_dump_dest_dir as ''' ||
path_bdump || '''';

execute immediate
'create table alert_log_external ' ||
' (line varchar2(4000) ) ' ||
' organization external ' ||
' (type oracle_loader ' ||
' default directory background_dump_dest_dir ' ||
' access parameters ( ' ||
' records delimited by newline ' ||
' nobadfile ' ||
' nologfile ' ||
' nodiscardfile ' ||
' fields terminated by ''#$~=ui$X''' ||
' missing field values are null ' ||
' (line) ' ||
' ) ' ||
' location (''' || name_alert || ''') )' ||
' reject limit unlimited ';
end;
/
---------------------------------------------------------------------
begin
external_alert_log;
end;
/
---------------------------------------------------------------------
select * from alert_log_external;

http://www.adp-gmbh.ch/ora/admin/read_alert/index.html

ORACLE Tuning Advisor

2009-07-17T04:59:00.001-07:00

ORACLE PERFORMANS İYİLEŞTİRME
#1 - SQL Sorgu Çalışma Planının İzlenmesi
Yazar: Gizem Gürsel
© : www.oracledanismanlik.com ve E-Kitap.org

Her hakkı saklıdır.

Oracle veritabanında çalıştırılan her SQL sorgusu, bir çalıştırma planı (execution plan) doğrultusunda işletilir. Bu plan ile, hangi indekslere (varsa ve uygunsa) erişileceği, hangi tip “join” işlemlerinin gerçekleştirileceğine karar verilir. Çalışma planı, bir yerden bir yere giderken izlenecek birçok yol arasında en hızlı ulaşımı sağlayacak güzergahın seçilmesi olarak da düşünülebilir. Doğru seçilmemiş bir güzergah nedeniyle, 10 dakikada gidilebilecek bir yere 1 saatte ulaşılabilir1.

Neden SQL İzleme-İyileştirme?

İyi çalışan bir sorgu öncelikle kullanıcıya en uygun sürede hizmetin verilmesi için gereklidir. Kimlik Paylaşım Sistemi kullanılarak bir TC kimlik numarası sorgulaması yapılıyorsa, kullanıcının cevabını çok hızlı şekilde alması gerekmektedir. Öte yandan, mevcut donanım kaynaklarının verimli şekilde kullanımı için de sorguların iyi çalışması gerekmektedir. Kötü çalışan bir sorgu; disk, bellek ve CPU açısından da darboğazlara yol açabilmektedir. Sorgular üzerinde bazı hallerde yapılacak ufak rötuşlar bile çok önemli performans kazançları sağlayabilmektedir.

SQL iyileştirme işlemi ile genel olarak şunlar hedeflenmektedir2 :

*

Büyük tablolar üzerindeki gereksiz “full-table” erişimlerin indeksli erişime dönüştürülmesi,
*

Küçük tablolar üzerindeki “full-table” erişimleri cach’lemek,
*

İndeks kullanımının optimum düzeyde olduğunu garantilemek,
*

Optimal JOIN teknikleri kullanmak,
*

Tune complex subqueries to remove redundant access

Ne Zaman SQL İzleme-İyileştirme?

Bir SQL sorgusunun iyileştirilmesini çeşitli koşullar tetikleyebilir. Bunlar arasında kullanıcı tarafından işlemin uzun sürdüğü şeklinde yapılan geri bildirimler olabileceği gibi, yapılan izleme, istatistik toplama çalışmaları kapsamında sorgunun fazla kaynak kullandığının tespit edilmesi de yer alabilir. Üzerinde çalışılması gereken sorguya karar verildiğinde, inceleme ve test çalışmalarının, olabildiği ölçüde uygulamadan ve diğer ara katmanlardan bağımsız olarak gerçekleştirilmesi yararlı olacaktır. Buradaki amaç uygulamanın kendi yapısından kaynaklanan farklı işlemleri devre dışı bırakarak, sadece SQL’in optimizasyonuna yoğunlaşılmasıdır. Bu nedenle, kullanılan izleme araçları ile, sorunlu olduğu düşünülen SQL sorgusu tespit edildikten sonra doğrudan sorgu üzerinde çalışılmalıdır.

Sorgularda dikkat edilmesi gereken bir diğer husus da, “literal” veya “bind” kullanımıdır. “Literal”de, aşağıdaki örnekte olduğu gibi, where koşulu içinde bir değer (value) belirtilmiştir.

SQL> select ad,soyad from calisan where calisan_id=765;

“Bind”da ise aşağıda belirtildiği gibi bir parametre kullanımı vardır (B1)3.

SQL> select ad,soyad from calisan where calisan_id=:B1;

“Bind” ve “literal” kullanımda farklı çalışma planları ortaya çıkabileceğinden, orijinal sorguda hangisi kullanılmışsa, iyileştirme çalışmalarının da bunun üzerinde yoğunlaşması gerekir.

Production ortamındaki veritabanlarını olabildiği ölçüde yansıtan test veritabanları mevcut ise, sorgu iyileştirme çalışmalarının test veritabanlarında yapılması daha sağlıklı olacaktır. Her sorgudan önce buffer cache’in flush edilmesi veya veritabanının kapatılıp açılması ile daha doğru ölçüm değerleri elde edilebilecektir.

SQL İzleme Yöntemleri
1. TRACE Kullanımı

TRACE, bir sorgunun, batch işlemin veya tüm sistemin ölçümü için kullanılabilecek bir yöntemdir. Sistem üzerindeki darboğazların oluştuğu noktaların tespiti için yararlı olan geniş kapsamlı bir yöntemdir. TRACE4;

*

Sorguyu çalıştırır, çalışan sorguyla ilgili olarak istatistik üretir,
*

Uygulama geliştiricilerin sorgunun her bir bölümünü analiz etmesine yardımcı olur.

TRACE ile yapılan izlemede, user_dump_dest initora parametresi ilen belirtilen dizine ora_nnnnn.trc söz diziminde bir trace dosyası oluşturulur.

TRACE’in bulunulan session’dan başlatılması mümkün olduğu gibi, bir başka session’ın çalışması da izlenebilir.

Bulunulan session için TRACE işlemi :

SQL> alter system set timed_statistics=true;

SQL> alter session set max_dump_file_size=20000;

SQL> show parameter user_dump

NAME TYPE VALUE

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

user_dump_dest string /usr/oracle10/rdbms/log/udump

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;

Dump dosyasının oluştuğu dizinde, ilgili trace dosyasının içeriği incelenir.

Dump file /usr/oracle10/rdbms/log/udump/tst1_ora_1105.trc

……

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

PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=164924072960 hv=789637826 ad='7b4fa270'

alter session set sql_trace true

END OF STMT

EXEC #1:c=1952,e=2048,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=164924072960

*** SESSION ID:(1633.1093) 2007-05-16 13:55:46.154

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

PARSING IN CURSOR #5 len=28 dep=0 uid=0 oct=3 lid=0 tim=164932504576 hv=4187904027 ad='7b29b108'

select MUSTERI_ADI from MUSTERI where MUSTERI_NO=1;

……

Başka session için TRACE işlemi :

İzlenmesi istenen sesssion’a ait SID ve SERIAL# bilgileri elde edilir (Örnek olarak TEST kullanıcısı seçilmiştir).

SQL> alter system set timed_statistics=true;

SQL> alter session set max_dump_file_size=20000; -- OS block

SQL> select SID, SERIAL# from v$session where username=’TEST’;

SID SERIAL#

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

163 110

Elde edilen bu SID ve SERIAL# değerleri, aşağıdaki şekilde kullanılarak trace başlatılır.

SQL>execute dbms_system.set_sql_trace_in_session('163','110',true);

Trace’in sonlandırılması için aynı komut, false parametresi ile çalıştırılır (Aşağıdaki komu işletilmezse izlenen kullanıcının o session’dan çıkana kadar yapacağı sonraki işlemler de aynı trace dosyasına eklenir).

SQL>execute dbms_system.set_sql_trace_in_session('163','110',false);

Dump dosyasının oluştuğu dizinde, ilgili trace dosyasının içeriği incelenir.

Dump file /usr/oracle10/rdbms/log/udump/tst1_ora_1105.trc

……

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

PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=164924072960 hv=789637826 ad='7b4fa270'

alter session set sql_trace true

END OF STMT

EXEC #1:c=1952,e=2048,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=164924072960

*** SESSION ID:(1633.1093) 2007-05-16 13:55:46.154

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

PARSING IN CURSOR #5 len=28 dep=0 uid=0 oct=3 lid=0 tim=164932504576 hv=4187904027 ad='7b29b108'

select MUSTERI_ADI from MUSTERI where MUSTERI_NO=1;

……

2. EXPLAIN PLAN Kullanımı

EXPLAIN PLAN ile, sorgu çalıştırılmaksızın, Oracle optimizer tarafından izlenecek plan görüntülenir. Bu yöntem, sorgunun çok uzun sürdüğü durumlarda tercih edilebilir. Sorgunun süresine, getireceği kayıtlara ihtiyaç olmadan sadece çalışma planının elde edilmesi için yararlıdır.

1.

İzleme işlemini yapacak kullanıcının öncelikle bir PLAN_TABLE tablosuna sahip olması gerekir. Genellikle $ORACLE_HOME/rdbms/admin dizini altına yer alan utlxplan.sql script’i, izleme işlemini yapacak kullanıcı tarafından SQL*Plus’tan çalıştırılır.

SQL> @utlxplan

2.

İzlenecek sorgu SQL*Plus’tan aşağıdaki şekilde çalıştırılır.

SQL> explain plan

set statement_id=’MUSTERI’ for

select MUSTERI_ADI

from MUSTERI

where MUSTERI_NO=1;

3.

Plan, aşağıdaki sorgu ile görüntülenir.

SQL> select lpad(‘ ‘,2*(level-1)) || operation || ‘ ‘

|| options || ‘ ‘ || object_name || ‘ ‘ ||

Decode(id,0, ‘Cost = ‘ || position) “Query Plan”

from PLAN_TABLE

start with id=0 and

statement_id=’MUSTERI’

connect by prior id=parent_id

and statement_id=’MUSTERI’;

Query Plan

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

SELECT STATEMENT Cost = 220

TABLE ACCESS BY INDEX ROWID MUSTERI

INDEX RANGE SCAN MUSTERI_IDX_NO

Daha basit planlar için aşağıdaki sorgu formatı da kullanılabilir.

SQL> select operation, options, object_name, parent_id

from PLAN_TABLE

where statement_id=’MUSTERI’

order by id;

OPERATION OPTIONS OBJECT_NAME PARENT_ID

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

SELECT STATEMENT

TABLE ACCESS BY INDEX ROWID MUSTERI 0

INDEX RANGE SCAN MUSTERI_IDX_NO 1

3. AUTOTRACE Kullanımı

AUTOTRACE özelliği, sorgunun EXPLAIN PLAN’ının yansıra TRACE ve TKPROF’da yer alan istatistiklerin çoğunu (fiziksel okuma sayısı, toplam okuma vb) verir. AUTOTRACE’in aşağıdaki şekilde parametreleri vardır:

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)

SQL> SET AUTOTRACE ON

SQL> select count(name) from scott.emp

where name=’JOHN’;

Count(Name) --- Sorgunun Sonucu

-----------

10

Query Plan --- Sorgunun Çalışma Planı

----------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE

2 1 INDEX (RANGE SCAN) OF ‘EMP_IDX’ (NON_UNIQUE)

Statistics --- Sorguyla İlgili İstatistik

----------

0 recursive calls

0 db block gets

1 consistent gets

1 physical reads

0 redo size

223 bytes sent via SQL*Net to client

274 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disks)

1 rows processed

4. V$SQL_PLAN Kullanımı

İyileştirilmesi istenen sorgunun izlenmesinin yanı sıra, o an çalışmakta olan bir sorgunun çalışma planının alınmasında da kullanılabilecek bir başka yöntem de V$SQL_PLAN view’ının kullanımıdır. Bu yöntemle, TEST adlı kullanıcının çalıştırmakta olduğu sorgunun nasıl izlenebileceği aşağıda örneklenmiştir.

*

SYS kullanıcısı, TEST kullanıcısının SID bilgisini aşağıdaki şekilde alır.

SQL> select SID from v$session where username=’TEST’;

SID

----------

1628

*

Alınan bu SID bilgisi aşağıdaki sorguya parametre olarak verilir.

SQL> select sql_id, sql_child_number from v$session

where sid=1628;

SQL_ID SQL_CHILD_NUMBER

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

9y8jx3vvy33d8 0

TEST kullanıcısının çalıştırmakta olduğu SQL’in değerleri yukarıdak sorgu ile alındıktan sonra, aşağıdaki sorguya parametre olarak verilir.

SQL> set pages 20000

SQL> set lines 120

SQL> column operations format a30

SQL> column object_name format a25

SQL> column options format a15

SQL>

select lpad(' ', level ) || operation operations,

object_name, options, cardinality

from (select * from v$sql_plan

where sql_id='9y8jx3vvy33d8'

and child_number=0)

connect by prior id=parent_id

start with id=0

order by id, position;

OPERATION OPTIONS OBJECT_NAME PARENT_ID

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

SELECT STATEMENT

TABLE ACCESS BY INDEX ROWID MUSTERI 0

INDEX RANGE SCAN MUSTERI_IDX_NO 1

*

Çalıştırılan sorgu “bind” kullanıyorsa, bu durumda hangi gerçek değerlerle sorgunun çalıştırıldığı da, aşağıdaki sorgu ile görüntülenir.

SQL>

col name format a10

col value_string format a50

select name, datatype, value_string

from v$sql_bind_capture

where sql_id='9y8jx3vvy33d8' and child_number=0

order by position;

Bind Kullanımı

İzlenen SQL sorgusunda “bind” kullanımı varsa, yapılan iyileştirme çalışmaları kapsamında yine bind kullanımının simule edilmesi gereklidir. “Bind” kullanımında parametrelere nasıl değer aktarılacağı aşağıda örneklenmiştir.

Orijinal sorgunun aşağıdaki gibi A1 ve A2 ile temsil eden bind parametrelerle çalıştırıldığını varsayalım.

select count(*) from MUSTERI

where bolge=:A1 and musteri_adi=:A2;

SQL*Plus’tan yapılacak izleme çalışması öncesinde aşağıdaki şekilde tanımlamalar yapılmalıdır.

SQL> variable A1 number;

SQL> variable A2 varchar2(20);

SQL> execute :A1 := 34;

SQL> execute :A2 := ‘YILMAZ’;

Bu tanımlamaların ardından, sorgunun izlenmesine başlanır. Örnekte AUTOTRACE kullanımı yer almaktadır.

SQL> set autotrace trace exp stat

SQL> select count(*) from MUSTERI

Where bolge=:A1 and musteri_adi=:A2;

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (FULL) OF 'MUSTERI' (TABLE)

Statistics

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

0 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

613 bytes sent via SQL*Net to client

660 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

DEĞERLENDİRME

SQL izleme yöntemleri arasında yer alan AUTOTRACE ve EXPLAIN PLAN kullanımı, sorgular üzerinde varsayımsal çalışma planı oluşturmaktadır. TRACE ve V$SQL_PLAN ise, sorgu için uygulanan gerçek çalışma planını oluşturur. Bu nedenle TRACE ve SQL$PLAN’ın diğer yöntemlere göre kesin sonuç ürettiği değerlendirmesi yapılabilir. Bununla birlikte, AUTOTRACE, gerek kullanımının kolaylığı gerekse aynı session içinde izlemenin ve sorgu çalıştırma işleminin bir arada yapılabilmesi nedeniyle çoğu durumda tercih edilebilir.

1 Oracle 10g sürümü ile birlikte “rule-based” olarak nitelenen optimizer devre dışı kaldığından bu doküman, “cost-based” optimizer kullanımına göre düzenlenmiştir.

2 Oracle Tuning The Definitive Reference, Donald K.Burleson, Alexey B.Danchenkov, Rampant Techpress, s.555

3 Genel olarak ifade edilmesi gerekirse, bind kullanımı, kullanılan parametre değerleri dışında herşeyi aynı olan sorguların tekrar parse edilmesi gerekliliğini ortadan kaldırır, böylece parse zamanından ve CPU kullanımından tasarruf sağlar.

4 Oracle 9i Performance Tuning Tips&Techniques, Richard J.Niemiec, Oracle Press, s.206.
--------

Burada yer alan bilgi ve belgelerin kullanımı birden çok kişi ve kurumu ilgilendirebilmektedir. Bu nedenle hiçbir biçimde yazılı, görsel ve sayısal ortamlarda çoğaltılmaması gerekmekte yalnızca E-Kitap.org sitesi üzerinden kullanılması gerekmektedir. Buradaki bilgilerin kullanılmasından ötürü çıkabilecek hiçbir zarar E-Kitap.org'u bağlamaz. Kullanıcılar bunu peşinen kabul etmiş sayılırlar.

http://www.e-kitap.org/OraclePerformansIyilestirme_No1.html

ORACLE Trace and Dump file Alert

2009-07-16T00:37:00.001-07:00

Oracle Database Alerts;
-Trace and Dump file Alert
root> which sqlplus
root> pwd
root> find . -print|grep –i dbmspool.sql
background_dump_destination
# Cleanup trace files more than 7 days old
root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7 -exec rm {} \;

Note that the first part of this script (before the –exec) displays all trace files that are more than 7 days old.
root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7

Using Trace Files

This section discusses the following trace file subjects:

$ORACLE_HOME/admin/db_name/bdump on UNIX operating systems
%ORACLE_HOME%\admin\db_name\bdump on Windows NT and Windows 2000 operating systems

The Oracle database creates a different trace file for each background thread. The name of the trace file contains the name of the background thread followed by the extension .trc, such as:

siddbwr.trc
sidsmon.trc

sid`lckn.trc`	Trace file for the Global Cache Service Processes (LMSn). This trace file shows lock requests for other background processes.
sid`lmdn.trc`	Trace file for the LMDn process. This trace file shows lock requests.
sid`lmon.trc`	Trace file for the LMON process. This trace file shows the status of the cluster.
sid`p00n.trc`	Trace file for the parallel execution processes.

The sidalrt.log file is in the directory specified by the BACKGROUND_DUMP_DEST parameter in the initdb_name.ora initialization parameter file. If you do not set the BACKGROUND_DUMP_DEST parameter, the sidalrt.log file is generated in:

$ORACLE_BASE/admin/db_name/bdump on UNIX operating systems
%ORACLE_HOME%\admin\db_name\bdump on Windows NT and Windows 2000 operating systems

SELECT pid "Oracle Process Id",
       name
    from v$PROCESS, V$BGPROCESS
    where V$PROCESS.addr = V$BGPROCESS.paddr;

SQL>select round(sum(BYTES/1024/1024),2) as "Total SGA in Mb" from v$sgastat;

Total SGA in Mb
---------------

SQL>select round(sum(BYTES/1024/1024),2) as "Used SGA in Mb" from v$sgastat where NAME not like '%free%';

Used SGA in Mb
--------------

select * from v$parameter
where name like '%background_dump_%'
--Oracle free space script
SELECT
a.tablespace_name,
a.file_name,
a.bytes allocated_bytes,
b.free_bytes
FROM
dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE
a.file_id=b.file_id
ORDER BY
a.tablespace_name;

-------------------
SELECT a.name, b.tablespace_name,
substr('Free : '||sum(b.bytes)/1024/1024,1,30) File_Size_MB
FROM dba_free_space b, v$database a
GROUP BY b.tablespace_name, a.name
UNION
SELECT a.name, b.tablespace_name,
substr('Total: '||sum(b.bytes)/1024/1024,1,30)
FROM dba_data_files b, v$database a
GROUP BY b.tablespace_name, a.name
ORDER BY 1,2,3
----------------------
rem run this script first, to create the free_space view;
drOP VIEW SYS.FREE_SPACE;

CREATE VIEW SYS.FREE_SPACE AS
SELECT
TABLESPACE_NAME TABLESPACE,
FILE_ID,
COUNT(*) PIECES,
SUM(BYTES) FREE_BYTES,
SUM(BLOCKS) FREE_BLOCKS,
MAX(BYTES) LARGEST_BYTES,
MAX(BLOCKS) LARGEST_BLKS
FROM
SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;
-----------------
rem tsfree.sql - Shows all free space within tablespaces.

Prompt be sure that you have run free_space.sql prior to this script

clear breaks;
clear computes;
set verify off;
set pagesize 66;
set linesize 79;
set newpage 0;

column temp_col new_value spool_file noprint;
column today new_value datevar noprint;
column TABLESPACE_NAME FORMAT A15 HEADING 'Tablespace';
COLUMN PIECES FORMAT 9,999 HEADING 'Tablespace|Pieces';
COLUMN FILE_MBYTES FORMAT 99,999 HEADING 'Tablespace|Mbytes';
cOLUMN FREE_MBYTES FORMAT 99,999 HEADING 'Free|Mbytes';
COLUMN CONTIGUOUS_FREE_MBYTES FORMAT 99,999 HEADING 'Contiguous|Free|Mbytes';
COLUMN PCT_FREE FORMAT 999 HEADING 'Percent|FREE';
COLUMN PCT_CONTIGUOUS_FREE FORMAT 999 HEADING 'Percent|FREE|Contiguous';

ttitle left datevar right sql.pno -
center ' Instance Data File Storage' SKIP 1 -
center ' in ORACLE Megabytes (1048576 bytes)' -
skip skip;

BREAK ON REPORT
COMPUTE SUM OF FILE_MBYTES ON REPORT

select to_char(sysdate,'mm/dd/yy') today,
TABLESPACE_NAME,
PIECES,
(D.BYTES/1048576) FILE_MBYTES,
(F.FREE_BYTES/1048576) FREE_MBYTES,
((F.FREE_BLOCKS / D.BLOCKS) * 100) PCT_FREE,
(F.LARGEST_BYTES/1048576) CONTIGUOUS_FREE_MBYTES,
((F.LARGEST_BLKS / D.BLOCKS) * 100) PCT_CONTIGUOUS_FREE
from SYS.DBA_DATA_FILES D, SYS.FREE_SPACE F
where D.STATUS = 'AVAILABLE' AND
D.FILE_ID= F.FILE_ID AND
D.TABLESPACE_NAME = F.TABLESPACE
order by TABLESPACE_NAME;

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

Diagnostic Data	Previous Location 10g R2	ADR Location
Database Alert log File	BACKGROUND_DUMP_DEST	ADR HOME/alert /log.xml ADR HOME/trace/alert_.log
Background Process Trace Files	USER_DUMP_DEST	ADR HOME/trace
Foreground Process Trace Files	BACKGROUND_DUMP_DEST	ADR HOME/

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

ORACLE Alert

2009-07-15T22:39:00.000-07:00

select file_name from dba_data_files; ------------------------------------- column file_name format a40 column tablespace format a15 column bytes format 999,999,999 select file_name, t.tablespace_name tablespace, bytes from dba_data_files d, dba_tablespaces t where t.tablespace_name = d.tablespace_name order by BYTES desc; ----------------------------------- alter system set db_create_file_dest='C:\ORA\PRODUCT\10.2.0\DB_1\ORADATA\ORCL\'; create tablespace test;
show parameter background_dump --alert logların yeri

Controlling the Size of Trace Files --

MAX_DUMP_FILE_SIZE

ALTER SESSION SET SQL_TRACE TRUE;  -performans kaybına neden olabilir. defaultta false

Use the DBMS_SESSION or the DBMS_MONITOR packages if you want to control SQL tracing for a session.

DBA_THRESHOLDS;

SELECT metrics_name, warning_value, critical_value, consecutive_occurrences
FROM DBA_THRESHOLDS
WHERE metrics_name LIKE '%CPU Time%';

View	Description
`DBA_THRESHOLDS`	Lists the threshold settings defined for the instance
`DBA_OUTSTANDING_ALERTS`	Describes the outstanding alerts in the database
`DBA_ALERT_HISTORY`	Lists a history of alerts that have been cleared
`V$ALERT_TYPES`	Provides information such as group and type for each alert
`V$METRICNAME`	Contains the names, identifiers, and other information about the system metrics
`V$METRIC`	Contains system-level metric values
`V$METRIC_HISTORY`	Contains a history of system-level metric values

Monitoring Lock;

View	Description
`V$LOCK`	Lists the locks currently held by Oracle Database and outstanding requests for a lock or latch
`DBA_BLOCKERS`	Displays a session if it is holding a lock on an object for which another session is waiting
`DBA_WAITERS`	Displays a session if it is waiting for a locked object
`DBA_DDL_LOCKS`	Lists all DDL locks held in the database and all outstanding requests for a DDL lock
`DBA_DML_LOCKS`	Lists all DML locks held in the database and all outstanding requests for a DML lock
`DBA_LOCK`	Lists all locks or latches held in the database and all outstanding requests for a lock or latch
`DBA_LOCK_INTERNAL`	Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch

Process and Session Views;

View	Description
`V$PROCESS`	Contains information about the currently active processes
`V$LOCKED_OBJECT`	Lists all locks acquired by every transaction on the system
`V$SESSION`	Lists session information for each current session
`V$SESS_IO`	Contains I/O statistics for each user session
`V$SESSION_LONGOPS`	Displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle Database release.
`V$SESSION_WAIT`	Lists the resources or events for which active sessions are waiting
`V$SYSSTAT`	Contains session statistics
`V$RESOURCE_LIMIT`	Provides information about current and maximum global resource utilization for some system resources
`V$SQLAREA`	Contains statistics about shared SQL area and contains one row for each SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution
`V$LATCH`	Contains statistics for nonparent latches and summary statistics for parent latches

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

CPU Alerts

CPU Overload Alert – We use vmstat to monitor for high run queue values and track periods when the Oracle database server is overloaded.
----------------------
display the number of CPUs on their Oracle server:
  
§ Linux Command:

cat /proc/cpuinfo|grep processor|wc –l

§ Solaris Command:

psrinfo -v|grep "Status of processor"|wc –l

§ AIX Command:

lsdev -C|grep Process|wc –l

§ HP/UX Command:

ioscan -C processor | grep processor | wc -l

win; tasklist /S %computername% /SVC /FO CSV > C:\tasklist.csv

RAM Alerts

High RAM Page and scan rate – In UNIX, we constantly monitor for RAM memory high page-in values and high scan rates. overloads and provide our clients with complete reports showing time when the Oracle database server experiences shortages of RAM memory.
vmstat 2
tail -f /var/log/messages
tail -f /var/log/messages | grep failed ile messages log dosyası içinde failed kelimesi arayabilirsiniz.
swapper: page allocation failure. order:0, mode:0×20
benzer mesajlar alıyorsanız sistem RAM i yetersiz demektir. Şu andaki RAM kullanımını görmek içinse:

# free -m
total used free shared buffers cached
Mem: 2010 1844 166 0 12 1290
-/+ buffers/cache: 541 1469
Swap: 3967 0 3967
Total başlığı sistemdeki toplam RAM miktarını, used başlığı ise kullanılan miktarı göteriyor. Ancak buradaki asıl önemli konu cache in ne kadarının kullanıldığı. Çünkü uygulamaların ne kadar hafıza kullandığı bu değerden anlaşılır. En iyi performans için, kullanılan cache miktarı toplam bellekten daha az olmalıdır. (541 <>
İşletim sistemleri RAM canavarlarıdır, sistemde ne kadar çok RAM varsa o kadar geniş geniş çalışırlar. Örneğin benim sistemimdeki 2 GB RAM’in 1.8 GB ı kullanılmış durumda. 1 GB iken de yaklaşık 900 MB. ı kullanımda idi. Yani işletim sistemi RAM kullanımında “ayağını yorganına göre uzatıyor”.
ps -aux komutu ile çalışan uygulamaların sistem kaynağı kullanımını (o an için) görebilirsiniz.
vmstat komutu takas belleği* kullanılıyorsa, ilgili istatistikleri görüntüler. (swap, Takas Bellek, sistem belleğinin yetişmemesi durumunda sabit disk üzerinde bir bölümün RAM bellek gibi kullanılması)
# vmstat 1 2
vmstat 1 2 -> 1 değeri 1 sn aralıkla, 2 ise 2 kez güncel değerleri getirir.

si (swap in) ve so (swap out) değerleri 2–3 interval in ardından 0 a inmeliler. bi (swap e giren byte, byte in) ve bo (byte out) değerleri ise çok büyük değerlerde olmamalı. Sondan bir önceki sütundaki id (CPU idle, cpu nun boşta olma zamanı) değeri ise 100 e ne kadar yakında o kadar iyidir. (İşlemcinin o kadar serbest olduğunu gösterir)
http://www.trsecurity.net/

Disk Alerts

Low free space in archived redo log directory - If your archived redo log directory is becoming full, we e-mail an alert to your DBA so they can add space before the Oracle database hangs.

UNIX mount point - The script checks all UNIX mount points for Oracle, including the UNIX Oracle home directory. Because most Oracle9i databases use the autoextend option for data files, we must be constantly alert for file systems that may not be able to extend. If the free space in any mount point is less than the specified threshold, an e-mail alert will be sent to the DBA.

Oracle Database Alerts

We use leading-edge technology to monitor every component of your Oracle database, and we offer the most comprehensive and sophisticated Oracle alert monitoring anywhere. We are proud that we detect potential problems before they cause a production outage.

Trace and Dump file Alert – We can immediately detect and e-mail Oracle trace or dump files for fast problem resolution.

Alert log messages - Every minute, we check for new alert log messages and e-mail them to your DBA staff.

Object cannot extend Alert - This report will alert the Oracle DBA whenever an Oracle table or index does not have room to take another extent

Tablespace > 95% free Alert - This report sends an e-mail alert whenever any tablespaces contain less space than specified and the datafile is not using the autoextend option.

Object > nnn extents Alert - This report detects tables and indexes that experience unexpected growth. Whenever a table or index exceeds the number defined, an e-mail alert will be sent to the DBA.

Hot File Alert - We report on all files whose read or write I/O are greater than (25 percent or 50 percent or 75 percent) of total I/O. This code compares the individual I/O for a file from stats$filestatxs with the overall I/O for the period in stats$sysstat.

Data Buffer Hit Ratio Alert - This report detects those times when the data buffer hit ratio falls below the preset threshold. This script also reports on all three data buffers, including the KEEP and RECYCLE pools, and it can be customized to report on individual pools.

High Disk Sorts Alert - We detect whenever a disproportional amount od disk sorts occurs. This report is very useful for monitoring the amount of activity against the TEMP tablespace, and it is also useful for ensuring that sort_area_size is set to an optimal level.

I/O Wait Alert - We detect any Oracle files with an excessive amount of wait activity. If the number of I/O waits appears excessive, we investigate the cause of the waits. High I/O waits on files are commonly associated with buffer busy waits, and may be caused by tables with too few freelists or freelist groups.

Buffer Busy Wait Alert - For non-ASSM datafiles, we detect high buffer busy waits.

Redo Log Space Requests Alert - If constantly monitor for high redo log space requests and make appropriate adjustments to the log_buffer parameter. A high number of redo log space requests indicates a high level of update activity, and the Oracle log buffer is having trouble keeping up with the volume of redo log images.

Chained Row Alert - We monitor for continued row fetches greater than 10,000/hr.

Shared Pool Contention Alert - We monitor for enqueue deadlocks that can indicate contention within the shared pool and locking related problems. Enqueue deadlocks are associated with the deadly embrace condition where one task is locking resources and another task that is holding resources requests a lock on the resources of the first task.

Full Table Scan Alert - We can monitor for large-table full-table scans within your library cache to alert you to potential SQL tuning.

Background Wait Alert - We monitor Oracle to find events with high waits. When background events experience more than 100 time-outs/hr, you may have a locking problem.

System Waits Alert - We monitor the Oracle event structures to locate events with excessive wait times. If you experience waits on latch free, enqueue, LGWR waits, or buffer busy waits, you need to locate the cause of the contention.

Library Cache Misses Alert - We monitor for excessive library cache miss ratios. When the library cache miss ratio is too high, we increase the shared_pool_size.

Database Writer Contention alert - We monitor Oracle for high values in summed dirty queue length, write requests, and DBWR checkpoints. When the write request length is greater than 3 (or your Database Writer checkpoint waits, we look at tuning the database writer processes.

Data Dictionary Miss Ratio Alert - We also monitor the Oracle data dictionary to compute data dictionary gets, data dictionary cache misses, and the data dictionary hit ratio.

oracle siteleri

2009-07-15T07:21:00.001-07:00

http://ordba.blogspot.com/

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm

http://infolab.stanford.edu/~ullman/fcdb/oracle.html

http://www.sqlsnippets.com/en/home.html

http://www.oracle-developer.net/index.php

http://gzdalhan.blogspot.com/

http://vimeo.com/user983017/videos

http://www.oracle-developer.net/display.php?id=412
http://www.oracle.com/technology/pub/articles/10gdba/week1_10gdba.html
http://www.cagataycebi.com/oracle/index.html
http://www.oracle-base.com/articles/9i/DataGuard.php#SetupPrimaryDatabase
http://www.oracle.com/technology/documentation/oem.html
http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_34.shtml
http://forums.sdn.sap.com/message.jspa?messageID=8103327#8103327
http://forums.sdn.sap.com/thread.jspa?forumID=220&threadID=1528632
http://sappandit.wordpress.com/category/oracle-upgrade-9-2-0-6-o-10-2-0-4/
http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm
http://www.oracle-base.com/articles/9i/DataGuard.php#SetupPrimaryDatabase
http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10763/downgrade.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/downgrade.htm

http://help.sap.com/saphelp_nw04/helpdata/en/12/bb0a412a06f023e10000000a155106/frameset.htm Administration Shared Memory
http://help.sap.com/saphelp_nw04/helpdata/EN/e8/f48b33f9a3423c9e688dfa56330e79/frameset.htm
Note 997510 - Troubleshooting J2EE Engine 6.40/7.0 Bootstrap Issues
http://java.sun.com/j2se/1.4.2/download.html
http://java.sun.com/javase/downloads/index.jsp
https://www.sdn.sap.com/irj/sdn/sap-gui#section6
http://support.microsoft.com/kb/841290
ftp://ftp.sap.com/pub/sapgui/java/710r8/
http://downloads.checkpoint.com/dc/download.htm?ID=8040
http://www.dbspecialists.com/files/presentations/buffercache.html
http://www.adp-gmbh.ch/ora/concepts/cache.html
http://oganozdogan.blogspot.com/2009/04/automatic-workload-repository.html

ORACLE Flashback

2009-07-15T03:31:00.000-07:00

fashback default da 15 dk . en az 2 saat olmalıdır.
hr hesabı ilk kurulumda kilitlidir.
alter user hr identified by hr account unlock;
conn hr/hr -- hr ile bağlanıyoruz.

select rowid, employee_id, first_name from employees;

rowid -- db veri girildiğinde hangi datafile hangi block ve sırasını tutar, rowid değişmez.

buffer cache küçükse wait event artar.
oracle read e kilit vurmaz. undotablespace bunun için var.
commit edilince undotable space siliniyor. flash back olunca tutuyor.

ör:
select * from $table_name
drop table $table_name
flashback table $table_name to before drop

10 g ile recyclebin geldi. flashback buradan alıyor.
purge dersek flashback ile de geri alınamaz.

create table kopya_emp5 as
select * from employees

delete from kopya_emp5 where employee_id=107;
commit;

select * from kopya_emp5
as of timestamp to_timestamp('15/07/2009 14:04', 'DD/MM/Yyyy hh24:mi')
minus
select * from kopya_emp5

tablo üzerinde ddl varsa flash back yapılamıyor.

alter table employees disable|enable row movement -- 10g ile default geliyor.

Select
SCN,
timestamp,
session# session_num,
sql_redo
From V$LOGMNR_CONTENTS
where upper(sql_redo) like '%KOPYA_EMP5%'
Order by 1
-------------------
declare

cursor c_tablo is
select table_name
from user_tables
order by table_name;
sayi number;

begin

for r_tablo in c_tablo
loop
execute immediate 'select count(*) from '||r_tablo.table_name into sayi;
dbms_output.put_line('Tablo:'||r_tablo.table_name||' Sayi'||sayi);
end loop;
end;
-----------------
--tablo analiz
create or replace procedure tablo_analiz is
cursor c_tablo is
select table_name
from user_tables;
begin
for r_tablo in c_tablo
loop
execute immediate 'analyze table '||r_tablo.table_name||' compute statistics';
end loop;
end;

exec tablo_analiz
---------------------
select * from v$log;
alter system switch logfile;
show parameter undo_retention;
select name from v$parameter;
show parameter sessions
--dolu geliyorsa spfile ile başladı
--null ise init.ora ile başladı.
select name, value fromv$parameter where name like '%spfile%' -- spfile ile mi başlıyor?
create pfile from spfile;
archive log list;

shutdwon [normal]
immediate -- işlemleri roolback yapar, kullanıcıyı atar,
transactional-- işlemlerin bitmesini bekleri, kullanıcıyı atar
abort-- hemen atar
-------
drop table kopya_emp

create table kopya_emp as
select * from employees

insert into kopya_emp
select * from kopya_emp

select count(*)
from kopya_emp
where department_id in (select distinct department_id
from kopya_emp
where last_name='King')
----------
declare
sayi number;
begin

for i in 1..100
loop
select count(*)
into sayi
from kopya_emp
where department_id in (select distinct department_id
from kopya_emp
where last_name='King');
end loop;

end;
---advisor hr
grant advisor to hr
alter system set undo_retention=900 scope=both;

2009-07-10T23:27:00.000-07:00

sqlplus system/oracle

alter user hr identified by hr account unlock; -- hr hesabında ki kilidi açıyoruz.

conn hr/hr

undo retention -
drop ettiğiniz tabloyu roll back ile geri alamazsınız!!

flashback table $table_name to before drop --undo tablespace süresi onemli.

Oracle Monitoring and Managing Memory

2009-07-10T01:50:00.000-07:00

Memory Management Mode	You Set	Oracle Database Automatically Tunes
Automatic memory management	Total memory size for this instance (Optional) Maximum memory size for this instance	Total SGA size SGA component sizes Instance PGA size Individual PGA sizes
Automatic shared memory management and automatic PGA memory management (Automatic memory management disabled)	SGA target size (Optional) SGA maximum size Instance PGA target size	SGA component sizes Individual PGA sizes
Manual shared memory management and automatic PGA memory management (Automatic memory management and automatic shared memory management disabled)	Shared pool size Buffer cache size Java pool size Large pool size Instance PGA target size	Individual PGA sizes

Memory Management Mode

You Set

Oracle Database Automatically Tunes

Automatic memory management

Total memory size for this instance
(Optional) Maximum memory size for this instance

Total SGA size
SGA component sizes
Instance PGA size
Individual PGA sizes

Automatic shared memory management and automatic PGA memory management

(Automatic memory management disabled)

SGA target size
(Optional) SGA maximum size
Instance PGA target size

SGA component sizes
Individual PGA sizes

Manual shared memory management and automatic PGA memory management

(Automatic memory management and automatic shared memory management disabled)

Shared pool size
Buffer cache size
Java pool size
Large pool size
Instance PGA target size

Individual PGA sizes

The System Global Area

* SGA components are sized in granules. A granule is an area of contiguous memory.
o The exception to this is the log buffer, which is not sized in granules.
o Regardless of what size you specify for a particular SGA component, it will be rounded up to the next whole granule.
+ The exclusion is again the log buffer
* Made up of the following required components:
o Shared Pool
o Log Buffer
o Db Buffer Cache Default Pool

* Following are optional requirements:
o Large Pool
o Streams Pool
o Java Pool
o DB Buffer Cache Keep Pool
o DB Buffer Cache Recycle Pool
o DB Buffer Cache nK Block Size Pools
* LOG_BUFFER and SGA_MAX_SIZE are two of the only static SGA parameters.

The Shared Pool

* Sized according to the SHARED_POOL_SIZE parameter. If set too low, the instance will not start.
* Has 36 components which are sized automatically.
o SQL area & Library Cache – recently executed SQL statements & execution plan
o Data Dictionary Cache (aka row cache) – data dictionary info being used to parse SQL statements
o Active Session History – recent activity by users which is flushed to the AWR
* There is a Shared Pool Advisor to determine how large it should be.

The Database Buffer Cache

The LRU List and the Checkpoint Queue

* Every buffer in the db buffer cache will be in one of three states: pinned, dirty or free.
o A pinned buffer is a buffer that is in use.
o A dirty buffer is one that has not yet been flushed to disk.
o A free or clean buffer is when the block has been copied into the cache and not yet changed or it has been changed and already flushed to disk.
* There should be many free buffers in the cache at any time. The decision on which buffer to use will be determined by the LRU List. The oldest accessed block will be overwritten.
* Another list is the Checkpoint Queue. This is a list of dirty buffers waiting to be written to disk by DBWn.
* The process that searches for free buffers in the LRU List also notifies the Checkpoint Queue of dirty buffers and adds them to the list. The LRU blocks will be written to disk first.
* When data gets written to disk:
o A server process takes too long to find a free buffer.
o The checkpoint queue is too long.
o Every 3 seconds, DBWn writes dirty buffers to disk.
* The only time that all dirty buffers get written to disk is when a checkpoint occurs (ex. SHUTDOWN, or alter system checkpoint).
* Tablespace checkpoints occur:
o tablespace is dropped
o tablespace made read-only
o tablespace put into hot backup mode
o tablespace is taken offline
* Log switches do not trigger a checkpoint.

The Default, Keep and Recycle Pools

* Each pool within the DB buffer cache has its own LRU list and checkpoint queue.
* The default pool is the only required pool and has its size determined by DB_CACHE_SIZE.
* To ensure that blocks of segments that are constantly used are always in memory, create a db buffer cache “keep” pool by setting the DB_KEEP_CACHE_SIZE parameter.
o SQL> alter index emp_name_idx(storage buffer_pool keep);
* To ensure that blocks of segments that are unlikely to be reaccessed are pushed out of memory as quickly as possible, create a db buffer cache “recycle” pool by setting the DB_RECYCLE_CACHE_SIZE parameter.
o SQL> alter table emp(storage buffer_pool recycle);

The Nonstandard Block Size Pools

* The DB_BLOCK_SIZE parameter is used for formatting the SYSTEM and SYSAUX and temporary tablespace datafiles. It can NEVER be changed.
* In order to create tablespaces with different block sizes you must set the appropriate db buffer cache pools:
o DB_2K_CACHE_SIZE
o DB_4K_CACHE_SIZE
o DB_8K_CACHE_SIZE
o DB_16K_CACHE_SIZE
o DB_32K_CACHE_SIZE
o You cannot set the above parameter if it is already your default. It will throw an error.
* By creating additional buffer pools, you will be able to copy tablespaces between databases with different block sizes.
o SQL> alter system set db_16k_cache_size=4m;
o SQL> create tablespace ts_16k datafile ‘ts_16k.dbf’ size 400m blocksize 16k;
* You cannot create a tablespace of nonstandard block size that is of type temporary.
* The keep and recycle buffers can only be of the default block size.

Sizing the Database Buffer Cache

* The cache should be large enough to cache blocks that are repeatedly accessed, but not so large that it caches blocks that are used only once.
* The Database Buffer Cache Advisor is available via V$DB_CACHE_ADVICE

The Log Buffer

* Short term staging area for changes before they are written to the redo logs.
* LGWR writes to disk:
o When a user commits a transaction
o When the log buffer is 1/3 full (even if no commit has been issued)
o Right before DBWn, writes (redo logs must have data before datafiles)
o Every three seconds
* The LOG_BUFFER parameter determines the size

Other SGA Areas

The Large Pool

* The purpose of the large pool is to reduce strain on the shared pool
* Will be used instead of the shared pool for the following:
o Shared Server configuration – UGAs will be stored in the large pool instead of shared
o Parallel execution servers
o I/O slave processes
o RMAN
* Size is determined by the dynamic LARGE_POOL_SIZE parameter.
* To monitor usage, view V$SGASTAT

The Java Pool

* Its purpose is to provide room for the runtime memory structures used by a Java application
* It’s technically “optional” but from a practical standpoint, necessary.
* Controlled by three instance parameters:
o JAVA_POOL_SIZE – dynamic; creates the Java pool and is limited by SGA_MAX_SIZE. Default is 24MB.
o JAVA_MAX_SESSIONSPACE_SIZE – static; max space for any one session
o JAVA_SOFT_SESSIONSPACE_LIMIT – static; if a session’s Java pool goes above this limit then a message is written to a trace file

The Streams Pool

* Stores changes that exist in the redo logs in order to propagate changes to other databases at remote sites.
* The changes from the redo logs are physical change records and can only be directly applied to datafiles, not applied logically to tables.

Automatic Shared Memory Management (ASMM)

* ASMM manages and auto-tunes the following components if they are set to zero or not set (if SGA_TARGET has a value):
o DB buffer cache default pool, DB_CACHE_SIZE
o Shared pool, SHARED_POOL_SIZE
o Large pool, LARGE_POOL_SIZE
o Java pool, JAVA_POOL_SIZE
* If the parameters have been set then Oracle will not lower the values below the setting
* These structures must be set manually:
o DB buffer cache keep pool, DB_KEEP_CACHE_SIZE
o DB buffer cache recycle pool, DB_RECYCLE_POOL_SIZE
o DB buffer cache nonstandard block size pools, DB_nK_CACHE_SIZE
o Streams pool, STREAMS_POOL_SIZE
o Log buffer, LOG_BUFFER
* To enable ASMM set the parameter SGA_TARGET
* ASMM will not work if the STATISTICS_LEVEL is set to BASIC. It needs to gather stats in order to monitor and adjust the SGA.
* Use of an spfile allows Oracle to remember the values of the various components for the next startup.
* You cannot change the SGA_TARGET to a value greater than SGA_MAX_SIZE while the instance is running (you will get an error). However, if set in the spfile, then the SGA_MAX_SIZE will be adjusted to SGA_TARGET on startup.
* ASMM starts an additional background process called MMAN. It controls the tuning to the various SGA components.

The Program Global Area (PGA)

* The PGA stores information private to a particular session. It is created at the start of each session and a separate PGA (each of variable sizes) exists for each session.
* Information contained in the PGA is:
o Sort space for sorting, joining, and aggregating rows (perhaps most important)
o Bind information
o Session variables
o Stack space
o Cursors
* The UGA is a part of the PGA.
* Two important parameters exist for the PGA:
o WORKAREA_SIZE_POLICY – [AUTO | MANUAL] Should be set to AUTO
o PGA_AGGREGATE_TARGET – Total amt of memory used for all PGAs (defaults to 20% of the SGA size)
o
+ If all memory in PGA_AGGREGATE_TARGET is used up, then Oracle will take memory from one PGA and distribute it to another
o
+ In the rare case that there is no memory that can be pulled away, the session will either use temporary space or break the PGA_AGGREGATE_TARGET setting.
* V$PGASTAT & V$PGA_TARGET_ADVICE are views used to monitor and size the PGA.
* If the WORKAREA_SIZE_POLICY is set to MANUAL, the PGA will rely on the following parameters (if set to AUTO the following will be ignored):
o SORT_AREA_SIZE
o HASH_AREA_SIZE
o BITMAPMERGE_AREA_SIZE
o CREATE_BITMAP_AREA_SIZE
o The problem with MANUAL policy is that PGA will grow but never shrink. Oracle will be unable to reassign memory between PGAs.

Parameters

* SGA_MAX_SIZE – SGA can’t exceed this limit
* SHARED_POOL_SIZE
* STATISTICS_LEVEL – must be set to TYPICAL in order to use V$SHARED_POOL_ADVICE
* DB_CACHE_SIZE – size of db buffer cache default pool
* DB_KEEP_CACHE_SIZE – size of db buffer cache keep pool
* DB_RECYCLE_CACHE_SIZE – size of db buffer cache recycle pool
* LOG_BUFFER – size of the log buffer in the SGA
* CPU_COUNT – (depending on the value) determines the size of the LOG_BUFFER
* LARGE_POOL_SIZE – size of the large pool
* WORKAREA_SIZE_POLICY – PGA policy which should be set to AUTO
* PGA_AGGREGATE_TARGET – Total amt of memory used for PGAs (defaults to 20% of the SGA size)

Views & Tables

* V$SGASTAT – sizes of various SGA components
* V$SGAINFO – see granule size
* V$SHARED_POOL_ADVICE – help size your shared pool
* V$DB_CACHE_ADVICE
* V$PGASTAT – see current allocations on the PGA
* V$PGA_TARGET_ADVICE – similar to other advice views; has factor field and predictions about effects

source:http://mnsinger.wordpress.com/2006/09/05/chapter-34-monitoring-and-managing-memory/
http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b28301/instance004.htm

Oracle Managing Process

2009-07-09T23:44:00.000-07:00

server process, kullanıcı için serverda oluşturulmuş processdir. kullanıcıdan gelen talepleri yani sorgularu çalıştırarak tekrar kullanıcıya döndürür.
server processesleri iki çeşittir:
- dedicated server process sadece bir user içindir. ve her kullaıcı içn bir server process oluşturulur. kullanıcılarin bir birleri ile iletişimi yoktur. bu demektir ki her kullanıcı için ayrı birer pga alanı ayrılması gerekir. buda total pga dan karşılanır. kısaca kullanıcılara ayrılan pga alanları kullanıcı sayısını bağlı olmak la birlikte küçülür. özellikte sort ve merge tarzı büyük sorgularda işlemlerin pga ya sığmayarak. diske yönelmek demektir. buda performans kaybına neden olur.
not:pga heap alanından her oracle process için ayrılmış bellek alanıdır. PGA_AGGREGATE_TARGET parametresiyle max. boyutu belirlenebilir. dedicated serverlar da pga içinda uga barındırılı. uga ise her kullanıcı için bir session state tutar.

- shared server process çoklu kullanıcılar içindir. bir dispatcher process olabilir ve hepsine sırayla cevap verir. uzun süreli işlemlerde kullanıcılar bir birini beklemek zorunda kalabilir. bu nedenle wait eventlar artabilir. dispatcher kullanımından dolayı daha az server process olur ve pga alanı artar.

Oracle Database Background Processes;

Database writer (DBWn):Database Writer işlemi, buffer cache’te bulunan değişmiş blockları diskte bulunan dosyalara yazmaktadır. Oracle en fazla 20 adet database writer işlemine izin vermektedir. parametresi: DB_WRITER_PROCESSES process sayısı belirtilir.

Log writer (LGWR):Log writer işlemi, redo log kayıtlarını diske yazmaktadır. Redo log kayıtları ilk önce SGA’da (System Global Area) bulunan redo log buffer üzerinde tutulur. Daha sonra log writer işlemi, sırasıyla bu kayıtları online redo log dosyalarına kaydeder.

Checkpoint (CKPT):Belirli zamanlarda, SGA’da bulunan database buffer verilerinin tamamı database writer işlemi tarafından disklerdeki datafile’lara yazılır. Bu olaya checkpoint denmektedir. Checkpoint işlemi, database writer işlemini tetikleyerek datafile’ların güncellenmesinden ve control file’ların en son checkpoint numarasını gösterecek şekilde düzenlenmesinden sorumludur.

System monitor (SMON):System Monitor işlemi çakılmış bir veritabanı instance’ının doğru bir şekilde açılmasından sorumludur.

Process monitor (PMON):Process monitor, kullanıcı işlemlerini takip eder ve çakılmış olanları kurtarır. Çakılmış işlem tarafından işgal edilmiş memory kaynaklarını temizleyerek kullanılabilir alan oluşturur.

Archiver (ARCn):Veritabanı, archive log mode’da iken çalışır. Bu işlem ile log switch gerçekleştiğinde ya da redo log dosyaları dolduğunda arşiv olarak kopyalamakla yükümlüdür.

Recoverer (RECO):Geri kurtarıcı dağıtık veritabanında sistem veya ağ hatalarından dolayı bekleyen işlemleri düzenler. Belli aralıklarla, yerel RECO uzaktaki veritabanına bağlanıp yereldeki dağıtık işlemlerle ilgili "commit" ve "rollback" işlemlerini yapmaktadır.

Dispatcher (Dnnn):Dispatcher lar çoklu ortamlarda isteğe bağlı olarak çalıştırılmaktadırlar. Her iletişim protokolü için en az bir dispatcher işlemi (D000,....,Dnnn) oluşturulmaktadır. Her dispatcher işlemi kullanıcı işlemlerinden gelen istekleri sunucu işlemlerine yönlendirmekte ve gelen cevapları da uygun kullanıcılara tekrar döndürmekten sorumludur.

Global Cache Service (LMS):RAC ortamında veritabanı örnekleri arasındaki kilitleme mekanizması için kullanılır.

Kilit(Lock-LCKO)
Kilit işlemleri birden fazla veritabanı oturumunun çalıştığı sistemlerde veritabanları arasında gereken bir takım kilitleme işlemlerini gerçekleştirir.

İş Kuyruğu(Job Queue-SNPn)
Dağıtık veri tabanı uygulamalarında 38 adetten fazla (SNP0,...,SNP9,SNPA,....SNPZ) iş kuyruğu işlemi tablo snapshotlarını otomatik olarak güncelleyebilir. Bu işlemler periyodik olarak başlatılır

Kuyruk Monitörü (QMNn) : Oracle Advanced Queuing opsiyonuna ait olan mesaj kuyruklarını izlemesi için yaratılan opsiyonel işlemlerdir. En çok on adet kuyruk mönitörü işlemi tanımlanabilir.

Parallel Execution
Oracle Database Enterprise Edition ile gelen bir özelliktir. oracle in bir tek process ile yapacağı işi bir den çok küçük parçaçıklara ayırarak çalışmasıdır.

Parallel Execution uygulamadan önce iki şeyden emin olmalısınız.

1-) Büyük bir taskınız olmalı (Mesela 50 gb full scan data)
2-) Yeterli kaynağınız oldugundan emin olmalısınız yeterli cpu ve I/O gibi (Paralel Processlerin çalışması için).50 gb verininde bieden çok diske dagılması ve disklerden datayı getirmek için I/O kanallarının yeterli olması beklenir.

PARALLEL_MAX_SERVERS >0 olmalı

Paralel Query
alter table table_name paralel ;

ALTER SESSION DISABLE||ENABLE PARALLEL DDL;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 5;

V$PQ_SESSTAT
SELECT * FROM v$pq_sesstat;
Unix de |ps -ef|grep "ora_p"
select
statistic,
value
from
v$pq_sysstat
where
statistic = 'Servers Busy';

Slave processlerin hangi sql li çalıştırdığını da viewleri joinleyerek bakabiliriz. Burda processler aktif olmak zorundadır.

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

• Parallel execution kullanmadan once araştırmalı eger imkan varsa initial testler yapılmalıdır aksi halde büyük problemlerle karşılaşabilinir.
• Parallel Execution donanımı en yoğun kullanmak için tasarlanmıştır. Tek cpu, iki 512 MB lik disk le çok büyük performans artışı beklemek yanlış olacaktır.
• Parallel Execution kullanırken once sql tune edin edin kötü yazılmış ve tasarlanmış uygulamaya parallel execution uygulamaya kalkmayın.
• Kısa süren transactionlarda PX kullanmanız çok az iyileşmeye hatta daha yavaş çalışmaya mahkum olabilir. Kısa sorgu için tüm kaynakları kullanmış olursunuz.
• Parallel Executions geceyarıları batch operasyonları için ya da multiple streams joblarında genelde performans gösterir ama yine de bu uygulamanın türüne bağlıdır.
• Create as Select ya da büyük bulk insert into parallel olarak gerçekleştiriliyorsa NOLOGING modunda çalışırsa performans artar çünkü redo log’a yazdıgınız herşey extra I/O dur aslında ama recovery durumunda logları manuel olarak oluşturmanız gerekecektir.
• PARALLEL_AUTOMATIC_TUNING parametresini true yapın

External Procedure
Harici yordamların kullanılabilmesi için Ağ Yönetiminde gerekli ayarların yapılması gerekmektedir.
The environment for calling external procedures, consisting of tnsnames.ora and listener.ora entries, is configured by default during the installation of your database.

Terminating Sessions
ALTER SYSTEM KILL SESSION '7,15';
SELECT SID, SERIAL#, STATUS
FROM V$SESSION
WHERE USERNAME = '$username'

active session kill hataları;
ORA-00028: your session has been killed
ORA-01012: not logged on

Monitoring the Operation of Your Database
To retrieve threshold values, use the GET_THRESHOLD procedure. For example:

DECLARE
warning_operator BINARY_INTEGER;
warning_value VARCHAR2(60);
critical_operator BINARY_INTEGER;
critical_value VARCHAR2(60);
observation_period BINARY_INTEGER;
consecutive_occurrences BINARY_INTEGER;
BEGIN
DBMS_SERVER_ALERT.GET_THRESHOLD(
DBMS_SERVER_ALERT.CPU_TIME_PER_CALL, warning_operator, warning_value,
critical_operator, critical_value, observation_period,
consecutive_occurrences, 'inst1',
DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE, 'main.regress.rdbms.dev.us.oracle.com');
DBMS_OUTPUT.PUT_LINE('Warning operator: ' || warning_operator);
DBMS_OUTPUT.PUT_LINE('Warning value: ' || warning_value);
DBMS_OUTPUT.PUT_LINE('Critical operator: ' || critical_operator);
DBMS_OUTPUT.PUT_LINE('Critical value: ' || critical_value);
DBMS_OUTPUT.PUT_LINE('Observation_period: ' || observation_period);
DBMS_OUTPUT.PUT_LINE('Consecutive occurrences:' || consecutive_occurrences);
END;
/

You can also check specific threshold settings with the DBA_THRESHOLDS view. For example:

SELECT metrics_name, warning_value, critical_value, consecutive_occurrences
FROM DBA_THRESHOLDS
WHERE metrics_name LIKE '%CPU Time%';

Viewing Alert Data

The following dictionary views provide information about server alerts:

* DBA_THRESHOLDS lists the threshold settings defined for the instance.
* DBA_OUTSTANDING_ALERTS describes the outstanding alerts in the database.
* DBA_ALERT_HISTORY lists a history of alerts that have been cleared.
* V$ALERT_TYPES provides information such as group and type for each alert.
* V$METRICNAME contains the names, identifiers, and other information about the system metrics.
* V$METRIC and V$METRIC_HISTORY views contain system-level metric values in memory

Monitoring the Database Using Trace Files and the Alert File

The alert file, or alert log, is a special trace file. The alert file of a database is a chronological log of messages and errors, and includes the following items:

* All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur
* Administrative operations, such as CREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statements
* Messages and errors relating to the functions of shared server and dispatcher processes
* Errors occurring during the automatic refresh of a materialized view
* The values of all initialization parameters that had nondefault values at the time the database and instance start

Initialization parameters controlling the location and size of trace files are:

* BACKGROUND_DUMP_DEST
* USER_DUMP_DEST
* MAX_DUMP_FILE_SIZE

This example enables the SQL trace facility for a specific session:

ALTER SESSION SET SQL_TRACE TRUE;

Use the DBMS_SESSION or the DBMS_MONITOR package if you want to control SQL tracing for a session
SQL trace facility and using TKPROF and trcsess to interpret the generated trace

Monitoring Locks

View Description
*V$LOCK Lists the locks currently held by Oracle Database and outstanding requests for a lock or latch
*DBA_BLOCKERS Displays a session if it is holding a lock on an object for which another session is waiting
*DBA_WAITERS Displays a session if it is waiting for a locked object
*DBA_DDL_LOCKS Lists all DDL locks held in the database and all outstanding requests for a DDL lock
*DBA_DML_LOCKS Lists all DML locks held in the database and all outstanding requests for a DML lock
*DBA_LOCK Lists all locks or latches held in the database and all outstanding requests for a lock or latch
*DBA_LOCK_INTERNAL Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch

Monitoring Wait Events

*V$PROCESS Contains information about the currently active processes
*V$LOCKED_OBJECT Lists all locks acquired by every transaction on the system
*V$SESSION Lists session information for each current session
*V$SESS_IO Contains I/O statistics for each user session
*V$SESSION_LONGOPS Displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle Database release.
*V$SESSION_WAIT Lists the resources or events for which active sessions are waiting
*V$SYSSTAT Contains session statistics
*V$RESOURCE_LIMIT Provides information about current and maximum global resource utilization for some system resources
*V$SQLAREA Contains statistics about shared SQL area and contains one row for each SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution
*V$LATCH Contains statistics for nonparent latches and summary statistics for parent latches

Event Wait sql

2009-07-09T23:35:00.001-07:00

SELECT
EVENT,
TOTAL_WAITS,
TIME_WAITED,
AVG_MS,
ROUND(RATIO_TO_REPORT(TIME_WAITED) OVER () * 100) PERCENT
FROM
( SELECT
SUBSTR(EVENT, 1, 30) EVENT,
TOTAL_WAITS,
TIME_WAITED,
ROUND(TIME_WAITED_MICRO / TOTAL_WAITS / 1000, 2) AVG_MS
FROM V$SYSTEM_EVENT
WHERE
WAIT_CLASS != 'Idle' AND
EVENT NOT IN
('db file parallel write', 'log file parallel write',
'log file sequential read', 'control file parallel write',
'control file sequential read', 'Log archive I/O')
UNION
SELECT 'CPU' EVENT, NULL, VALUE, NULL
FROM V$SYSSTAT
WHERE STATISTIC# = 12
ORDER BY 3 DESC)
WHERE ROWNUM <= 10;