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