1.AMAÇ VE KAPSAM
Başka bir yazımda Physical standby veritabanı kurulumunu anlatmıştım, bu yazımda ise kurmuş olduğumuz physical standby database’i logical standby’a çevireceğiz.
2.UYGULAMA
2.1. Primary database üstünde asagidaki SQL sorgusunu yaziyoruz, Sorgular sonucu veritabaninda hangi tablolar logical standbyda desteklenmiyor onlari buluyoruz.
SQL> Select * from dba_logstdby_unsupported; OWNER TABLE_NAME COLUMN_NAME DATA_TYPE --------------------- -------------------------- ---------------- --------------- OE CATEGORIES_TAB CATEGORY_ID Object Table NUMBER OE CATEGORIES_TAB PARENT_CATEGORY_ID Object Table OWNER TABLE_NAME COLUMN_NAME DATA_TYPE --------------------- -------------------------- ---------------- --------------- WMSYS WM$UDTRIG_INFO TRIG_CODE LONG WMSYS WM$VERSIONED_TABLES UNDO_CODE WM$ED_UNDO_CODE_TABLE_TYPE
2.2. Primary key veya non-null unique indexes bulunmayan tablolari asagidaki sorguyla bulabiliyoruz.
N = Logical standby için tablo içinde yeterli sayida column bilgisine ulasabildigini söylüyor
Y = LONG, CLOB veya BLOB gibi birbirine bagli olmayan datatypelari bulmaniza yariyor.Supplemental loggingi açarak bu kisimlarida tanimlanabilir yapabiliriz.
SQL> SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED); OWNER TABLE_NAME B ------------------------------ ------------------------------ - SCOTT BONUS N SCOTT SALGRADE N SH SALES N SH COSTS N SH SUPPLEMENTARY_DEMOGRAPHICS N
2.3. Supplemental log açikmi diye bakiyoruz.
SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database; SUP SUP --- --- NO NO
2.4.Eger açik degilse primary key ve unique index için supplemental loggingi açiyoruz ve yeni bir log üretmesini sagliyoruz.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX) COLUMNS; Database altered. SQL> ALTER SYSTEM SWITCH LOGFILE; System altered.
2.5. Paralizim açik degilse açiyoruz. // her dbde gerekmiyor.
SQL> ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=BOTH;
2.6. Physcal standby database’in loglari islemesini engelliyoruz.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered.
2.7. Primary database’in log archive dest 1 All_LOGFILES ve ALL_ROLES diye tanimli bunu ONLINE_LOGFILES ve ALL_ROLES diye degistiriyoruz. Log_archive_dest_3 e ise tamamen yeniden yapilandirip aktif yapiyorum ve 3 parametresi üstünde bazi oynamalar yapmamiz gerekiyor.
SQL> show parameter log NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string LOCATION=/u01/app/oracle/datab ases/prim/redo/ VALID_FOR=(ALL_LOGFILES,ALL_RO LES) DB_UNIQUE_NAME=bugra log_archive_dest_2 string service=stdby lgwr async noaff irm valid_for=(online_logfiles ,primary_role) db_unique_name= stdby log_archive_dest_3 string log_archive_dest_state_1 string ENABLE log_archive_dest_state_2 string enable log_archive_dest_state_3 string enable remote_login_passwordfile string EXCLUSIVE sec_case_sensitive_logon boolean TRUE sec_max_failed_login_attempts integer 10 SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/databases/prim/redo/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bugra' scope=both; System altered. SQL> Alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/databases/prim/redo2/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=bugra' scope=both; System altered. SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both; System altered.
2.8. Redo log sözlügünü olusturmamiz gerekiyor böylelikle LOG_ARCHIVE_DEST_1 e tanimladigimiz ONLINE_LOGFILE çalisabilecek. Bunun için LogMiner sözlügünü yaratiyoruz.
SQL> EXECUTE DBMS_LOGSTDBY.BUILD; PL/SQL procedure successfully completed.
2.9.Redo loglar artik Logical standby kurulumu için uygun hale geldi.Physical standby makinasina gidip logical standby içni recover islemini baslatiyoruz.
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY stdby; Standby Alertloglari Media Recovery Complete (stdby) tkcrrxms: Killing 4 processes (all RFS) RESETLOGS after incomplete recovery UNTIL CHANGE 904454 Resetting resetlogs activation ID 772557144 (0x2e0c4958) Online log /u01/app/oracle/product/11.1.0/oradata/stdby/redogroup_1.262.720476831: Thread 1 Group 1 was previously cleared Online log /u01/app/oracle/product/11.1.0/oradata/stdby/redogroup_1.263.720476839: Thread 1 Group 1 was previously cleared Online log /u01/app/oracle/product/11.1.0/oradata/stdby/redogroup_2.264.720476843: Thread 1 Group 2 was previously cleared Online log /u01/app/oracle/product/11.1.0/oradata/stdby/redogroup_2.265.720476851: Thread 1 Group 2 was previously cleared Standby became primary SCN: 904452 Sat Sep 18 02:28:01 2010 Setting recovery target incarnation to 3 Converting standby mount to primary mount. ACTIVATE STANDBY: Complete - Database mounted as primary (stdby) *** DBNEWID utility started *** DBID will be changed from 772585308 to new DBID of 1331092977 for database BUGRA DBNAME will be changed from BUGRA to new DBNAME of STDBY Starting datafile conversion Setting recovery target incarnation to 1 Datafile conversion complete Failed to find temporary file: /u01/app/oracle/product/11.1.0/oradata/stdby/data/temp.268.720476923 Database name changed to STDBY. Modify parameter file and generate a new password file before restarting. Database ID for database STDBY changed to 1331092977. All previous backups and archived redo logs for this database are unusable. Database has been shutdown, open with RESETLOGS option. Succesfully changed database name and ID. *** DBNEWID utility finished succesfully *** Completed: ALTER DATABASE RECOVER TO LOGICAL STANDBY stdby Sat Sep 18 02:28:14 2010 destination database instance is 'started' not 'mounted'
2.10. Logical standbyimiz nomount posizyonunda açilmis oldu simdi 1,2 ve 3. log_archive_dest parametrelerini değiştireceğiz.
SQL> Show parameter log NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string location=/u01/app/oracle/produ ct/11.1.0/oradata/stdby/arc1/ valid_for=(all_logfiles,all_ro les) db_unique_name=stdby log_archive_dest_2 string service=bugra lgwr async noaff irm valid_for=(online_logfiles ,primary_role) db_unique_name= bugra log_archive_dest_3 string log_archive_dest_state_1 string enable log_archive_dest_state_2 string ENABLE log_archive_dest_state_3 string enable SQL> alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/product/11.1.0/oradata/stdby/arc1/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'scope=both; System altered. SQL> Alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=bugra ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bugra' scope=both; System altered. SQL> Alter system set LOG_ARCHIVE_DEST_3= 'LOCATION=/u01/app/oracle/product/11.1.0/oradata/stdby/arc2/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stdby' scope=both; System altered. SQL> Alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both; System altered. SQL> Alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both; System altered. SQL> Alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both; System altered.
2.11. Database’i kapatip mount posizyonunda açiyoruz sonrada resetlogs yapip databasei open hale getiriyoruz.Start logical standby apply immediate dedikten sonra redolar islenmeye basliyacaktir.
SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount; ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started. Total System Global Area 146472960 bytes Fixed Size 1298472 bytes Variable Size 92278744 bytes Database Buffers 50331648 bytes Redo Buffers 2564096 bytes Database mounted. SQL> ALTER DATABASE OPEN RESETLOGS; Database altered. SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; Database altered.
2.12. Standby Alert Loglarına bakalım.
Completed: alter database open ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE ALTER DATABASE START LOGICAL STANDBY APPLY (stdby) with optional part IMMEDIATE Attempt to start background Logical Standby process Sat Sep 18 03:32:48 2010 LSP0 started with pid=24, OS id=32724 Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE LOGMINER: Parameters summary for session# = 1 LOGMINER: Number of processes = 3, Transaction Chunk Size = 201 LOGMINER: Memory Size = 30M, Checkpoint interval = 150M LOGMINER: SpillScn 0, ResetLogScn 522753 Sat Sep 18 03:33:12 2010 LOGMINER: session#=1, reader MS00 pid=28 OS id=32739 sid=82 started Sat Sep 18 03:33:14 2010 LOGMINER: session#=1, builder MS01 pid=29 OS id=32741 sid=79 started Sat Sep 18 03:33:14 2010 LOGMINER: session#=1, preparer MS02 pid=30 OS id=32743 sid=78 started LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 41, /u01/app/oracle/product/11.1.0/oradata/stdby/arc1/1_41_720476831.dbf LOGMINER: Turning ON Log Auto Delete
2.13. Son olarak V$database sorgu çekip database’in çalışma şeklini görebiliriz.
SQL> select log_mode,open_mode , database_role from v$database; LOG_MODE OPEN_MODE DATABASE_ROLE ------------ ---------- ---------------- ARCHIVELOG READ WRITE LOGICAL STANDBY
Bir Cevap Yazın