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;