Dataguard kurulan sistemde primary database'de çok fazla archive dosyası üretilmiyor ise bunun standby database'e gönderilmeside o kadar seyrek olacaktir. Standby database'de controlfile time kontrolu ile yapılacak olan güncellik kontrolleri surekli gecikme var diye uyarı verecektir.
Bunu aşmak için
Primary database ile standby database' arasında sql ile kontrol yapılabilir.
Primary database de cron'a alter system switch logfile ile archive dosyası üretilmesi sağlanabilir
En kolayı primary database'de ile 1800 saniyede bir logların switch edilmesi sağlanır.
alter system set archive_lag_target=1800 scope=BOTH;
3 Eylül 2014 Çarşamba
DATAGUARD ORA-08181: specified number is not a valid system change number
Standby database eğer geriden geliyor ise aşağıdaki hata alınabilir.
Hatanın giderilmesi için dataguard beslemesinin restart edilmesi gerekmektedir.
HATA
SQL> BEGIN
2 DBMAIN.DB_CHECK_STANDBY;
3 COMMIT;
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-20081: DBMAIN.DB_CHECK_STANDBY de hata olustu -20061 ORA-20061:
dbmain.DB_CHECK_STANDBY send_message_sms_email hata olustu !! -600 ORA-00600:
internal error code, arguments: [psdnopGetACL-4], [8181], [ORA-08181: specified
number is not a valid system change number
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 651
], [], [], [], [], [], [], [], [], []
ORA-06512: at "DBMAIN.DB_CHECK_STANDBY", line 350
ORA-06512: at line 2
Alertlog'da
Errors in file /oracle/diag/rdbms/drated/DRATED/trace/DRATED_ora_26427.trc (incident=69786):
ORA-00600: internal error code, arguments: [psdnopGetACL-4], [8181], [ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 651
], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/drated/DRATED/incident/incdir_69786/DRATED_ora_26427_i69786.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Sep 03 15:30:03 2014
Dumping diagnostic data in directory=[cdmp_20140903153003], requested by (instance=1, osid=26427), summary=[incident=69786].
Wed Sep 03 15:30:05 2014
Sweep [inc][69786]: completed
Sweep [inc2][69786]: completed,
oracle@pratestb:/oracle/diag/rdbms/drated/DRATED/trace>more /oracle/diag/rdbms/drated/DRATED/trace/DRATED_ora_24197.trc
Trace file /oracle/diag/rdbms/drated/DRATED/trace/DRATED_ora_24197.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2.0.3/db
System name: HP-UX
Node name: pratestb
Release: B.11.31
Version: U
Machine: ia64
Instance name: DRATED
Redo thread mounted by this instance: 1
Oracle process number: 57
Unix process pid: 24197, image: oracle@pratestb
*** 2014-09-03 15:49:37.035
*** SESSION ID:(293.63) 2014-09-03 15:49:37.035
*** CLIENT ID:() 2014-09-03 15:49:37.035
*** SERVICE NAME:() 2014-09-03 15:49:37.035
*** MODULE NAME:(Toad.exe) 2014-09-03 15:49:37.035
*** ACTION NAME:() 2014-09-03 15:49:37.035
in internal exception handler, error2: 8181
resignalling error
in outer exception handler, error: 8181
resignalling error
DDE: Problem Key 'ORA 600 [psdnopGetACL-4]' was flood controlled (0x2) (incident: 70057)
ORA-00600: internal error code, arguments: [psdnopGetACL-4], [8181], [ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 651
], [], [], [], [], [], [], [], [], []
*** 2014-09-03 15:50:06.714
in internal exception handler, error2: 8181
resignalling error
in outer exception handler, error: 8181
resignalling error
DDE: Problem Key 'ORA 600 [psdnopGetACL-4]' was flood controlled (0x2) (incident: 70058)
ORA-00600: internal error code, arguments: [psdnopGetACL-4], [8181], [ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 651
], [], [], [], [], [], [], [], [], []
oracle@pratestb:/oracle/diag/rdbms/drated/DRATED/trace>
Çözüm
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
Session altered.
SQL> select controlfile_time from v$database;
CONTROLFILE_TIME
-------------------
03.09.2014 13:43:02
Sonra archive dosyalarıda gelsin diye primary database'de
alter system switch logfile yapin :)
Hatanın giderilmesi için dataguard beslemesinin restart edilmesi gerekmektedir.
HATA
SQL> BEGIN
2 DBMAIN.DB_CHECK_STANDBY;
3 COMMIT;
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-20081: DBMAIN.DB_CHECK_STANDBY de hata olustu -20061 ORA-20061:
dbmain.DB_CHECK_STANDBY send_message_sms_email hata olustu !! -600 ORA-00600:
internal error code, arguments: [psdnopGetACL-4], [8181], [ORA-08181: specified
number is not a valid system change number
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 651
], [], [], [], [], [], [], [], [], []
ORA-06512: at "DBMAIN.DB_CHECK_STANDBY", line 350
ORA-06512: at line 2
Alertlog'da
Errors in file /oracle/diag/rdbms/drated/DRATED/trace/DRATED_ora_26427.trc (incident=69786):
ORA-00600: internal error code, arguments: [psdnopGetACL-4], [8181], [ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 651
], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/drated/DRATED/incident/incdir_69786/DRATED_ora_26427_i69786.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Sep 03 15:30:03 2014
Dumping diagnostic data in directory=[cdmp_20140903153003], requested by (instance=1, osid=26427), summary=[incident=69786].
Wed Sep 03 15:30:05 2014
Sweep [inc][69786]: completed
Sweep [inc2][69786]: completed,
oracle@pratestb:/oracle/diag/rdbms/drated/DRATED/trace>more /oracle/diag/rdbms/drated/DRATED/trace/DRATED_ora_24197.trc
Trace file /oracle/diag/rdbms/drated/DRATED/trace/DRATED_ora_24197.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2.0.3/db
System name: HP-UX
Node name: pratestb
Release: B.11.31
Version: U
Machine: ia64
Instance name: DRATED
Redo thread mounted by this instance: 1
Oracle process number: 57
Unix process pid: 24197, image: oracle@pratestb
*** 2014-09-03 15:49:37.035
*** SESSION ID:(293.63) 2014-09-03 15:49:37.035
*** CLIENT ID:() 2014-09-03 15:49:37.035
*** SERVICE NAME:() 2014-09-03 15:49:37.035
*** MODULE NAME:(Toad.exe) 2014-09-03 15:49:37.035
*** ACTION NAME:() 2014-09-03 15:49:37.035
in internal exception handler, error2: 8181
resignalling error
in outer exception handler, error: 8181
resignalling error
DDE: Problem Key 'ORA 600 [psdnopGetACL-4]' was flood controlled (0x2) (incident: 70057)
ORA-00600: internal error code, arguments: [psdnopGetACL-4], [8181], [ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 651
], [], [], [], [], [], [], [], [], []
*** 2014-09-03 15:50:06.714
in internal exception handler, error2: 8181
resignalling error
in outer exception handler, error: 8181
resignalling error
DDE: Problem Key 'ORA 600 [psdnopGetACL-4]' was flood controlled (0x2) (incident: 70058)
ORA-00600: internal error code, arguments: [psdnopGetACL-4], [8181], [ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 651
], [], [], [], [], [], [], [], [], []
oracle@pratestb:/oracle/diag/rdbms/drated/DRATED/trace>
Çözüm
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
Session altered.
SQL> select controlfile_time from v$database;
CONTROLFILE_TIME
-------------------
03.09.2014 13:43:02
Sonra archive dosyalarıda gelsin diye primary database'de
alter system switch logfile yapin :)
Dataguard'dan mail Atma
Mail atma işleminde hata alınırsa
begin
SYS.UTL_MAIL.SEND (
'ali.ates@xxxx.com.tr',
'ali.ates@xxxx.com.tr',
nvl(null,''),
nvl(null,''),
'TEST',
'test_mesaj',
-- 'text/html; charset=us-ascii',
-- 'text/plain; charset=us-ascii',
'html',
1
);
end;
Error at line 2
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 14
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 2
Script Terminated on line 19.
Çözüm
alter system set smtp_out_server='10.112.2.26:25' scope=both;
begin
SYS.UTL_MAIL.SEND (
'ali.ates@XXXX.com.tr',
'ali.ates@XXXX.com.tr',
nvl(null,''),
nvl(null,''),
'TEST DRATED',
'test_mesaj',
-- 'text/html; charset=us-ascii',
-- 'text/plain; charset=us-ascii',
'html',
1
);
end;
begin
SYS.UTL_MAIL.SEND (
'ali.ates@xxxx.com.tr',
'ali.ates@xxxx.com.tr',
nvl(null,''),
nvl(null,''),
'TEST',
'test_mesaj',
-- 'text/html; charset=us-ascii',
-- 'text/plain; charset=us-ascii',
'html',
1
);
end;
Error at line 2
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 14
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 2
Script Terminated on line 19.
Çözüm
alter system set smtp_out_server='10.112.2.26:25' scope=both;
begin
SYS.UTL_MAIL.SEND (
'ali.ates@XXXX.com.tr',
'ali.ates@XXXX.com.tr',
nvl(null,''),
nvl(null,''),
'TEST DRATED',
'test_mesaj',
-- 'text/html; charset=us-ascii',
-- 'text/plain; charset=us-ascii',
'html',
1
);
end;
14 Ağustos 2014 Perşembe
Date VS Timestamp in Oracle
Timestamp ile sysdate arasındaki karşılaştığm en önemli fark birisinin
makinanın saatini alması diğerinin ise timezone'a göre zamanı vermesidir.
Uygulama ekipleri bazı date alanlarını timestamp tanımlayıp ona göre doldurabilirler.
Bir süre sonra database'in ya da session'ının timezone'u değiştiğinde
columlardaki date değeri sysdate'den farklı olacağı için size gelip database
zamanı yanlış gösteriyor diyebilirler.
Database
zamanı yanlış diye geldikleri zaman ilk kontrol edilmesi gerekenler:
1.
OS
(Operating System)sistem zamanı. Database zamanı OS'den alır.
Dbname
oracle@hostname:/home/oracle>date
Thu Aug 14 09:23:06 EETDST
2014
2.
Database sysdate
SQL> ALTER SESSION SET
NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
Session altered.
SQL> select sysdate from
dual;
SYSDATE
-------------------
2014 08 14 09:27:21
SQL> select
to_char(sysdate,'YYYY MM DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2014 08 14 09:28:06
3.
Database timezone kontrol edilir.
SELECT sessiontimezone FROM DUAL;
select dbtimezone from dual;
4.
Database timestamp değeri kontrol
edilir.
SQL> SELECT
CURRENT_TIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
14-AUG-14 09.29.28.339601 AM
+03:00
Timestamp column create etme ve
değer yükleme
create table timezone_test1 (timezone TIMESTAMP(6) WITH TIME ZONE , v_date date , id number)
insert into timezone_test1 values (sysdate,sysdate,1);
commit
insert into timezone_test1 values (CURRENT_TIMESTAMP,sysdate,3);
commit
select * from timezone_test1
ASM Disk problemi
Yeni kurulan bir makinada ilk OS restart işleminde sonra ASM açılmama problemi olabilir.
asm pmon'u ayakta olabilir ama açılırken hata almiştir.
dbname oracle@hostname:/oracle/admin/aliates/scripts/logs>ps -ef | grep pmon
oracle 5383 1 0 10:41:59 ? 0:00 asm_pmon_+ASM
Alerlog hatası
SQL> ALTER DISKGROUP ALL MOUNT
NOTE: Diskgroups listed in ASM_DISKGROUPS are
DISKGROUPNAME
NOTE: cache registered group DISKGROUPNAME number=1 incarn=0x74357628
NOTE: cache began mount (first) of group DISKGROUPNAME number=1 incarn=0x74357628
ERROR: no read quorum in group: required 2, found 0 disks
NOTE: cache dismounting (clean) group 1/0x74357628 (DISKGROUPNAME)
NOTE: messaging CKPT to quiesce pins Unix process pid: 4822, image: oracle@hostname
NOTE: dbwr not being msg'd to dismount
NOTE: lgwr not being msg'd to dismount
NOTE: cache dismounted group 1/0x74357628 (DISKGROUPNAME)
NOTE: cache ending mount (fail) of group DISKGROUPNAMEnumber=1 incarn=0x74357628
NOTE: cache deleting context for group DISKGROUPNAME1/0x74357628
GMON dismounting group 1 at 2 for pid 17, osid 4822
ERROR: diskgroup DISKGROUPNAME was not mounted
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DISKGROUPNAME" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DISKGROUPNAME"
ERROR: ALTER DISKGROUP ALL MOUNT
Thu Aug 14 10:34:19 2014
NOTE: No asm libraries found in the system
ASM Health Checker found 1 new failures
ASM diskleri göremediği için mount işleminde hata almiştir. Disklerin oracle userina verilmesi gerekmektedir.
Ayrıca asm'in pfile dosyasında disklerin pathleri belirtilmelidir.
asm_diskstring = "/dev/rdisk/*"
Diskler ASM'e gösterdikten sonra restart edilir. Olması gereken asm alertlog dosyası
SQL> ALTER DISKGROUP ALL MOUNT
NOTE: Diskgroups listed in ASM_DISKGROUPS are
DISKGROUPNAME
NOTE: cache registered group DISKGROUPNAME number=1 incarn=0xbfa62ae1
NOTE: cache began mount (first) of group RATED number=1 incarn=0xbfa62ae1
NOTE: Assigning number (1,0) to disk (/dev/rdisk/disk32)
NOTE: Assigning number (1,4) to disk (/dev/rdisk/disk37)
NOTE: Assigning number (1,3) to disk (/dev/rdisk/disk36)
NOTE: Assigning number (1,2) to disk (/dev/rdisk/disk34)
NOTE: Assigning number (1,1) to disk (/dev/rdisk/disk33)
NOTE: GMON heartbeating for grp 1
GMON querying group 1 at 3 for pid 17, osid 5414
NOTE: cache opening disk 0 of grp 1: DISKGROUPNAME path:/dev/rdisk/disk32
NOTE: F1X0 found on disk 0 au 2 fcn 0.0
NOTE: cache opening disk 1 of grp 1: DISKGROUPNAME_0001 path:/dev/rdisk/disk33
NOTE: cache opening disk 2 of grp 1: DISKGROUPNAME_0002 path:/dev/rdisk/disk34
NOTE: cache opening disk 3 of grp 1: DISKGROUPNAME_0003 path:/dev/rdisk/disk36
NOTE: cache opening disk 4 of grp 1: DISKGROUPNAME_0004 path:/dev/rdisk/disk37
NOTE: cache mounting (first) external redundancy group 1/0xBFA62AE1 (DISKGROUPNAME)
NOTE: cache recovered group 1 to fcn 0.9302730
NOTE: redo buffer size is 256 blocks (1053184 bytes)
NOTE: LGWR attempting to mount thread 1 for diskgroup 1 (DISKGROUPNAME )
NOTE: LGWR found thread 1 closed at ABA 2.300
NOTE: LGWR mounted thread 1 for diskgroup 1 (DISKGROUPNAME )
NOTE: LGWR opening thread 1 at fcn 0.9302730 ABA 3.301
NOTE: cache mounting group 1/0xBFA62AE1 (DISKGROUPNAME ) succeeded
NOTE: cache ending mount (success) of group DISKGROUPNAME number=1 incarn=0xbfa62ae1
GMON querying group 1 at 4 for pid 13, osid 5406
NOTE: Instance updated compatible.asm to 11.2.0.0.0 for grp 1
SUCCESS: diskgroup DISKGROUPNAME was mounted
SUCCESS: ALTER DISKGROUP ALL MOUNT
NOTE: diskgroup resource ora.DISKGROUPNAME .dg is online
asm pmon'u ayakta olabilir ama açılırken hata almiştir.
dbname oracle@hostname:/oracle/admin/aliates/scripts/logs>ps -ef | grep pmon
oracle 5383 1 0 10:41:59 ? 0:00 asm_pmon_+ASM
Alerlog hatası
SQL> ALTER DISKGROUP ALL MOUNT
NOTE: Diskgroups listed in ASM_DISKGROUPS are
DISKGROUPNAME
NOTE: cache registered group DISKGROUPNAME number=1 incarn=0x74357628
NOTE: cache began mount (first) of group DISKGROUPNAME number=1 incarn=0x74357628
ERROR: no read quorum in group: required 2, found 0 disks
NOTE: cache dismounting (clean) group 1/0x74357628 (DISKGROUPNAME)
NOTE: messaging CKPT to quiesce pins Unix process pid: 4822, image: oracle@hostname
NOTE: dbwr not being msg'd to dismount
NOTE: lgwr not being msg'd to dismount
NOTE: cache dismounted group 1/0x74357628 (DISKGROUPNAME)
NOTE: cache ending mount (fail) of group DISKGROUPNAMEnumber=1 incarn=0x74357628
NOTE: cache deleting context for group DISKGROUPNAME1/0x74357628
GMON dismounting group 1 at 2 for pid 17, osid 4822
ERROR: diskgroup DISKGROUPNAME was not mounted
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DISKGROUPNAME" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DISKGROUPNAME"
ERROR: ALTER DISKGROUP ALL MOUNT
Thu Aug 14 10:34:19 2014
NOTE: No asm libraries found in the system
ASM Health Checker found 1 new failures
ASM diskleri göremediği için mount işleminde hata almiştir. Disklerin oracle userina verilmesi gerekmektedir.
Ayrıca asm'in pfile dosyasında disklerin pathleri belirtilmelidir.
asm_diskstring = "/dev/rdisk/*"
Diskler ASM'e gösterdikten sonra restart edilir. Olması gereken asm alertlog dosyası
SQL> ALTER DISKGROUP ALL MOUNT
NOTE: Diskgroups listed in ASM_DISKGROUPS are
DISKGROUPNAME
NOTE: cache registered group DISKGROUPNAME number=1 incarn=0xbfa62ae1
NOTE: cache began mount (first) of group RATED number=1 incarn=0xbfa62ae1
NOTE: Assigning number (1,0) to disk (/dev/rdisk/disk32)
NOTE: Assigning number (1,4) to disk (/dev/rdisk/disk37)
NOTE: Assigning number (1,3) to disk (/dev/rdisk/disk36)
NOTE: Assigning number (1,2) to disk (/dev/rdisk/disk34)
NOTE: Assigning number (1,1) to disk (/dev/rdisk/disk33)
NOTE: GMON heartbeating for grp 1
GMON querying group 1 at 3 for pid 17, osid 5414
NOTE: cache opening disk 0 of grp 1: DISKGROUPNAME path:/dev/rdisk/disk32
NOTE: F1X0 found on disk 0 au 2 fcn 0.0
NOTE: cache opening disk 1 of grp 1: DISKGROUPNAME_0001 path:/dev/rdisk/disk33
NOTE: cache opening disk 2 of grp 1: DISKGROUPNAME_0002 path:/dev/rdisk/disk34
NOTE: cache opening disk 3 of grp 1: DISKGROUPNAME_0003 path:/dev/rdisk/disk36
NOTE: cache opening disk 4 of grp 1: DISKGROUPNAME_0004 path:/dev/rdisk/disk37
NOTE: cache mounting (first) external redundancy group 1/0xBFA62AE1 (DISKGROUPNAME)
NOTE: cache recovered group 1 to fcn 0.9302730
NOTE: redo buffer size is 256 blocks (1053184 bytes)
NOTE: LGWR attempting to mount thread 1 for diskgroup 1 (DISKGROUPNAME )
NOTE: LGWR found thread 1 closed at ABA 2.300
NOTE: LGWR mounted thread 1 for diskgroup 1 (DISKGROUPNAME )
NOTE: LGWR opening thread 1 at fcn 0.9302730 ABA 3.301
NOTE: cache mounting group 1/0xBFA62AE1 (DISKGROUPNAME ) succeeded
NOTE: cache ending mount (success) of group DISKGROUPNAME number=1 incarn=0xbfa62ae1
GMON querying group 1 at 4 for pid 13, osid 5406
NOTE: Instance updated compatible.asm to 11.2.0.0.0 for grp 1
SUCCESS: diskgroup DISKGROUPNAME was mounted
SUCCESS: ALTER DISKGROUP ALL MOUNT
NOTE: diskgroup resource ora.DISKGROUPNAME .dg is online
11 Ağustos 2014 Pazartesi
Takılan RMAN session’larını kill etme
Rman
sessionları Media Layer katmanından cevap alamadığında kill etmek için CTRL+C
işe yaramayabilir. Bu durumda session’i
kill etmek için aşağıdaki sorgu ile process id değeri bulunup kill edilir.
oracle@hostname:/oracle/product/11.2.0.3/db/lib>rman target / debug trace=/tmp/rman.log
Recovery Manager:
Release 11.2.0.3.0 - Production on Tue Aug 12 09:23:54 2014
Copyright (c) 1982,
2011, Oracle and/or its affiliates. All
rights reserved.
RMAN-06006:
connected to target database: DBNAME (not mounted)
RMAN> connect
catalog username/pass@rcatdb
RMAN-06008:
connected to recovery catalog database
RMAN> run
2> {
3> set until scn 11812341070106;
4> allocate channel t1 type 'SBT_TAPE' parms
'ENV=(NSR_CLIENT=targethostname,NSR_SERVER=backupserverhostname.ttnet.local)';
5> RESTORE CONTROLFILE;
6> RELEASE channel t1;
7> }
TRACE Çıktısı
DBGRPC: EXITED krmqgns with status 1
DBGRPC: krmxpoq - returning rpc_number: 17
with status: STARTED40 for channel t1
DBGRPC: krmxr - sleeping for 10 seconds
DBGRPC: ENTERED krmqgns
DBGRPC: krmqgns: looking for work for
channel default (krmqgns)
DBGRPC: krmqgns: commands remaining to be
executed: (krmqgns)
DBGRPC: CMD type=IRESTORE cmdid=1
status=STARTED
DBGRPC: 1 STEPstepid=1 cmdid=1
status=STARTED devtype=SBT_TAPE bs.stamp=855302866 step_size=0 Bytes
DBGRPC: 1 DON_F stepid=2
satisfied=TRUE
DBGRPC: krmqgns: no work found for channel
default (krmqgns)
DBGRPC: (krmqgns)
Kill İşlemi
sqlplus / as sysdba
SQL> COLUMN EVENT
FORMAT a17
SQL> COLUMN
SECONDS_IN_WAIT FORMAT 999
SQL> COLUMN STATE
FORMAT a10
SQL> COLUMN
CLIENT_INFO FORMAT a30
SQL> set linesize
200
SELECT 'kill -9 '||p.SPID, s.EVENT, s.SECONDS_IN_WAIT AS SEC_WAIT,
sw.STATE, s.CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION
s, V$PROCESS p
WHERE sw.EVENT LIKE '%MML%'
AND s.SID=sw.SID
AND s.PADDR=p.ADDR;
'KILL-9'||P.SPID EVENT SEC_WAIT STATE CLIENT_INFO
--------------------------------
----------------- ---------- ---------- ------------------------------
kill -9 6866 Backup: MML resto 36 WAITING rman channel=t1
re
backup piece
SQL>
Unix command
oracle@hostname:/tmp>
kill -9 6212
Eğer bu bilgi
bulunamıyor ise ve database ‘in kapatılmasında sakınca yok ise (Restore
testleri yapılıyor ise) DB açilip kapatılabilir.
AWR Reports
AWR raporu almak için
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
AWR raporlarını karşilaştirmak için aşağıdaki yöntemi kullanabilirsiniz.
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql
SQL komutu ile almak için
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(212121111,1,8947,8951,
212121111,1,8971,8975));
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
AWR raporlarını karşilaştirmak için aşağıdaki yöntemi kullanabilirsiniz.
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql
SQL komutu ile almak için
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(212121111,1,8947,8951,
212121111,1,8971,8975));
8 Ağustos 2014 Cuma
Restore Database
Recovery
Manager complete.
dbname
oracle@hostname:/home/oracle>sqldba
SQL*Plus:
Release 11.2.0.3.0 Production on Thu Aug 7 09:02:44 2014
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With
the Partitioning, Automatic Storage Management, OLAP, Data Mining
and
Real Application Testing options
SQL>
alter database mount;
Database
altered.
SQL>
exit
dbname
oracle@hostname:/home/oracle>vi dbname_restore.sh
export PATH=$PATH:/oracle/product/11.2.0.3/db/bin
export ORACLE_HOME=/oracle/product/11.2.0.3/db
export ORACLE_SID=dbname
rman <<EOF
connect target /
run
{
CONFIGURE DEFAULT DEVICE TYPE TO sbt;
CONFIGURE CHANNEL DEVICE TYPE sbt parms 'ENV=(NSR_CLIENT=sourcehostname,NSR_RECOVER_POOL=DDBoostORA,NSR_DATA_DOMAIN_INTERFACE=eth11a.562,N
SR_SERVER=backupserverhostname)';
CONFIGURE DEVICE TYPE sbt PARALLELISM 8;
restore DATABASE;
}
EOF
exit
dbname
oracle@hostname:/home/oracle>
dbname
oracle@hostname:/home/oracle>
dbname
oracle@hostname:/home/oracle>./dbname_restore.sh
Recovery
Manager: Release 11.2.0.3.0 - Production on Thu Aug 7 09:48:26 2014
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
RMAN>
connected
to target database: dbname (DBID=1556411790, not open)
RMAN>
2> 3> 4> 5> 6> 7>
using
target database control file instead of recovery catalog
new
RMAN configuration parameters:
CONFIGURE
DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new
RMAN configuration parameters are successfully stored
old
RMAN configuration parameters:
CONFIGURE
CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS
'ENV=(NSR_CLIENT=sourcehostname,NSR_SERVER=backupserverhostname,NSR_DATA_VOLUME_POOL=DDBoostORA)'
FORMAT 'DBNAME_FULL_%T_%u_%s_%p'
MAXOPENFILES 1;
new
RMAN configuration parameters:
CONFIGURE
CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS
'ENV=(NSR_CLIENT= sourcehostname,NSR_SERVER= backupserverhostname)';
new
RMAN configuration parameters are successfully stored
old
RMAN configuration parameters:
CONFIGURE
DEVICE TYPE 'SBT_TAPE' PARALLELISM 12 BACKUP TYPE TO BACKUPSET;
new
RMAN configuration parameters:
CONFIGURE
DEVICE TYPE 'SBT_TAPE' PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
new
RMAN configuration parameters are successfully stored
Starting
restore at 07-AUG-14
Starting
implicit crosscheck backup at 07-AUG-14
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=35 device type=DISK
Crosschecked
1 objects
Finished
implicit crosscheck backup at 07-AUG-14
Starting
implicit crosscheck copy at 07-AUG-14
using
channel ORA_DISK_1
Finished
implicit crosscheck copy at 07-AUG-14
searching
for all files in the recovery area
cataloging
files...
no
files cataloged
allocated
channel: ORA_SBT_TAPE_1
channel
ORA_SBT_TAPE_1: SID=129 device type=SBT_TAPE
channel
ORA_SBT_TAPE_1: NMDA Oracle v1.6.0
allocated
channel: ORA_SBT_TAPE_2
channel
ORA_SBT_TAPE_2: SID=156 device type=SBT_TAPE
channel
ORA_SBT_TAPE_2: NMDA Oracle v1.6.0
allocated
channel: ORA_SBT_TAPE_3
channel
ORA_SBT_TAPE_3: SID=190 device type=SBT_TAPE
channel
ORA_SBT_TAPE_3: NMDA Oracle v1.6.0
allocated
channel: ORA_SBT_TAPE_4
channel
ORA_SBT_TAPE_4: SID=221 device type=SBT_TAPE
channel
ORA_SBT_TAPE_4: NMDA Oracle v1.6.0
allocated
channel: ORA_SBT_TAPE_5
channel
ORA_SBT_TAPE_5: SID=4 device type=SBT_TAPE
channel
ORA_SBT_TAPE_5: NMDA Oracle v1.6.0
allocated
channel: ORA_SBT_TAPE_6
channel
ORA_SBT_TAPE_6: SID=96 device type=SBT_TAPE
channel
ORA_SBT_TAPE_6: NMDA Oracle v1.6.0
allocated
channel: ORA_SBT_TAPE_7
channel
ORA_SBT_TAPE_7: SID=36 device type=SBT_TAPE
channel
ORA_SBT_TAPE_7: NMDA Oracle v1.6.0
allocated
channel: ORA_SBT_TAPE_8
channel
ORA_SBT_TAPE_8: SID=67 device type=SBT_TAPE
channel
ORA_SBT_TAPE_8: NMDA Oracle v1.6.0
using
channel ORA_DISK_1
channel
ORA_SBT_TAPE_1: starting datafile backup set restore
channel
ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel
ORA_SBT_TAPE_1: restoring datafile 00012 to +DBNAME/dbname/datafile/tbs_dbname.278.849440549
channel
ORA_SBT_TAPE_1: reading from backup piece DBNAME_FULL_20140807_i2pfb08g_3650_1
channel
ORA_SBT_TAPE_2: starting datafile backup set restore
channel
ORA_SBT_TAPE_2: specifying datafile(s) to restore from backup set
channel
ORA_SBT_TAPE_2: restoring datafile 00007 to +DBNAME/dbname/datafile/tbs_dbname.273.849440543
channel
ORA_SBT_TAPE_2: reading from backup piece DBNAME_FULL_20140807_htpfb08g_3645_1
channel
ORA_SBT_TAPE_3: starting datafile backup set restore
channel
ORA_SBT_TAPE_3: specifying datafile(s) to restore from backup set
channel
ORA_SBT_TAPE_3: restoring datafile 00011 to +DBNAME/dbname/datafile/tbs_dbname.277.849440547
channel
ORA_SBT_TAPE_3: reading from backup piece DBNAME_FULL_20140807_i1pfb08g_3649_1
channel
ORA_SBT_TAPE_4: starting datafile backup set restore
channel
ORA_SBT_TAPE_4: specifying datafile(s) to restore from backup set
channel
ORA_SBT_TAPE_4: restoring datafile 00010 to +DBNAME/dbname/datafile/tbs_dbname.276.849440547
channel
ORA_SBT_TAPE_4: reading from backup piece DBNAME_FULL_20140807_i0pfb08g_3648_1
channel
ORA_SBT_TAPE_5: starting datafile backup set restore
channel
ORA_SBT_TAPE_5: specifying datafile(s) to restore from backup set
channel
ORA_SBT_TAPE_5: restoring datafile 00003 to +DBNAME/dbname/datafile/undotbs1.262.849427443
channel
ORA_SBT_TAPE_5: reading from backup piece DBNAME_FULL_20140807_hspfb08g_3644_1
channel
ORA_SBT_TAPE_6: starting datafile backup set restore
channel
ORA_SBT_TAPE_6: specifying datafile(s) to restore from backup set
channel
ORA_SBT_TAPE_6: restoring datafile 00015 to +DBNAME/dbname/datafile/tbs_dbname.281.849440553
channel
ORA_SBT_TAPE_6: reading from backup piece DBNAME_FULL_20140807_i5pfb08h_3653_1
channel
ORA_SBT_TAPE_7: starting datafile backup set restore
channel
ORA_SBT_TAPE_7: specifying datafile(s) to restore from backup set
channel
ORA_SBT_TAPE_7: restoring datafile 00008 to +DBNAME/dbname/datafile/tbs_dbname.274.849440543
channel
ORA_SBT_TAPE_7: reading from backup piece DBNAME_FULL_20140807_hupfb08g_3646_1
channel
ORA_SBT_TAPE_8: starting datafile backup set restore
channel
ORA_SBT_TAPE_8: specifying datafile(s) to restore from backup set
channel
ORA_SBT_TAPE_8: restoring datafile 00013 to +DBNAME/dbname/datafile/tbs_dbname.279.849440551
Control File Restore
Dbname
oracle@hostname:/home/oracle>rman target /
connected
to target database: dbname(DBID=1556411790, not open)
RMAN> connect catalog username/password@catalogdbtns
connected
to recovery catalog database
RMAN> list backup of controlfile;
List
of Backup Sets
===================
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
32255 Full
21.75M SBT_TAPE 00:00:04
06-AUG-14
BP Key: 32257 Status: AVAILABLE Compressed: NO Tag: TAG20140806T080650
Handle: c-1556411790-20140806-00 Media: DDBoostORA.001
Control File Included: Ckp SCN: 11810187652312 Ckp time: 06-AUG-14
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
32989 Full
21.75M SBT_TAPE 00:00:03
07-AUG-14
BP Key: 32991 Status: AVAILABLE Compressed: NO Tag: TAG20140807T072909
Handle:
dbname_FULL_20140807_kupfb4u7_3742_1
Media: DDBoostORA.001
Control File Included: Ckp SCN:
11810723943559 Ckp time: 07-AUG-14
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
33000 Full
21.75M SBT_TAPE 00:00:02
07-AUG-14
BP Key: 33002 Status: AVAILABLE Compressed: NO Tag: TAG20140807T072917
Handle: c-1556411790-20140807-00 Media: DDBoostORA.001
Control File Included: Ckp SCN: 11810723949892 Ckp time: 07-AUG-14
RMAN> run
{
set until scn
11810723949892;
allocate channel t1 type 'SBT_TAPE' parms
'ENV=(NSR_CLIENT=sourcehostname,NSR_SERVER=backupserverhostname)';
RESTORE CONTROLFILE;
RELEASE channel t1;
}
executing
command: SET until clause
allocated
channel: t1
channel
t1: SID=66 device type=SBT_TAPE
channel
t1: NMDA Oracle v1.6.0
Starting
restore at 07-AUG-14
released
channel: t1
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002:
failure of restore command at 08/07/2014 08:53:54
RMAN-06496: must use the TO clause when the database is mounted
or open
RMAN>
exit
Recovery
Manager complete.
dDBNAME
oracle@hostname:/home/oracle>sqldba
SQL*Plus:
Release 11.2.0.3.0 Production on Thu Aug 7 08:54:02 2014
Copyright
(c) 1982, 2011, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With
the Partitioning, Automatic Storage Management, OLAP, Data Mining
and
Real Application Testing options
SQL>
shu immediate;
ORA-01109:
database not open
Database
dismounted.
ORACLE
instance shut down.
SQL>
SQL> SQL> SQL>
SQL>
SQL>
SQL> startup nomount
pfile='/oracle/product/11.2.0.3/db/dbs/rman_pfile';
ORACLE
instance started.
Total
System Global Area 2.0510E+10 bytes
Fixed
Size 2191480 bytes
Variable
Size 2550140808 bytes
Database
Buffers 1.7918E+10 bytes
Redo
Buffers 40038400 bytes
SQL>
exit
Disconnected
from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
dDBNAME
oracle@hostname:/home/oracle>rman target /
Recovery
Manager: Release 11.2.0.3.0 - Production on Thu Aug 7 08:55:35 2014
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
connected
to target database: DBNAME (not mounted)
RMAN>
RMAN>
connect catalog username/password@catalogdbtns
connected
to recovery catalog database
run
{
set until scn
11810723949892;
allocate channel t1 type 'SBT_TAPE' parms
'ENV=(NSR_CLIENT=sourcehostname,NSR_SERVER= backupserverhostname)';
RESTORE CONTROLFILE;
RELEASE channel t1;
}
executing
command: SET until clause
allocated
channel: t1
channel
t1: SID=35 device type=SBT_TAPE
channel
t1: NMDA Oracle v1.6.0
Starting
restore at 07-AUG-14
channel
t1: starting datafile backup set restore
channel
t1: restoring control file
channel
t1: reading from backup piece c-1556411790-20140807-00
channel
t1: piece handle=c-1556411790-20140807-00 tag=TAG20140807T072917
channel
t1: restored backup piece 1
channel
t1: restore complete, elapsed time: 00:04:05
output file
name=+DBNAME/dDBNAME/controlfile/current.261.854728895
output file
name=+DBNAME/dDBNAME/controlfile/current.260.854728895
Finished restore at 07-AUG-14
released
channel: t1
RMAN>
exit
Kaydol:
Kayıtlar (Atom)