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

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));

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