Dataguard BROKER -2 dg2 ora

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

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

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

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

-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

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

ç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

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

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

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

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

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

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