Buffer Cache Advisory - SQL

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

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

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

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

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

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

>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