Monthly Archives: November 2013

“DBA_REGISTRY” status “INVALID” after an upgrade


SQL> SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS=’INVALID’; COMP_NAME ——————————————————————————– Oracle Text Spatial Oracle interMedia Oracle Data Mining Oracle Workspace Manager  (Oracle support id :  753041.1) Solution I tried: Step 1: Login as SYSDBA using the below command $ sqlplus “/as sysdba” Step 2: Shutdown the database if it is running using the below command SQL> shutdown immediate Step […]

rman backup & restore backup


Rman Backup 1. Sign on to RMAN:                                                                 rman “target / nocatalog”                                                           2. Issue the following RMAN commands:                                               RUN                                                                                 {                                                                                   ALLOCATE CHANNEL chan_name TYPE DISK;                                               BACKUP DATABASE FORMAT ‘some_backup_directory%U’ TAG before_upgrade;                BACKUP CURRENT CONTROLFILE FORMAT ‘controlfile location and name’;                  }                                                                                                                                                                                                                                                                                                                                                   RESTORE Backup                                                                                                                                                      2. Sign on to RMAN:                                                                 rman “target / nocatalog”                                                           3. […]

restrict mode


SQL>startup restrict — Database mounted. — Database opened. SQL>alter system disable restricted session;

recover datafile


SQL> recover [automatic] datafile ‘/export2/m5/oradata/AZDB/TS_INDEX5.dbf’; Media recovery complete. SQL> alter database datafile ‘/export2/m5/oradata/AZDB/TS_INDEX5.dbf’ online;

noaudit


select ‘noaudit ‘||AUDIT_OPTION || ‘ by ‘||user_name||’;’  from dba_stmt_audit_opts where user_name is not null union select  ‘noaudit ‘||PRIVILEGE  || ‘ by ‘||user_name||’;’   from dba_priv_audit_opts where user_name is not null union select ‘noaudit ‘||AUDIT_OPTION || ‘;’  from dba_stmt_audit_opts where user_name is null union select  ‘noaudit ‘||PRIVILEGE  || ‘;’   from dba_priv_audit_opts  where user_name is null ;

Transparent Data Encryption


Amacı : Bir tablo içerisindeki kolonların veya bir tablespace’e ait tüm verilerin şifreli bir şekilde datafile içerisinde tutulması sağlanıyor. Bu sayede, datafile’da clear text olarak tutulan bilgilerin, illegal bir şekilde açılıp okunması engellenmiş oluyor. Detaylı bilgi için linkleri takip edebilirsiniz. http://www.oracle.com/technetwork/database/focus-areas/security/tde-faq-093689.html#A13020 http://www.oracle.com/technetwork/database/security/twp-transparent-data-encryption-bes-130696.pdf Genel kullanım aşağıdaki gibi. — sqlnet.ora içerisinde ki değişiklikler ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/export/azdb/oracle/app/oracle/product/11.2.0.3/dbs/wallet/))) — sql […]

Start and stop instance and Database


To start one or more instances, enter the following SRVCTL syntax from the command line: srvctl start instance -d    db_name    -i    “inst_name_list” To stop one or more instances, enter the following SRVCTL syntax from the command line: srvctl stop instance -d name -i “inst_name_list”  To start or stop your entire cluster database, that is, all […]

How to change Archivelog mode from RAC Database


Login to one of the nodes (i.e. linux1) and disable the cluster instance parameter by setting cluster_database to FALSE from the current instance: $ sqlplus “/ as sysdba” SQL> alter system set cluster_database=false scope=spfile sid=’orcl1′; Shutdown all instances accessing the clustered database:  $ srvctl stop database -d orcl Using the local instance, MOUNT the database:  […]

DB – patch apply conflict check


Run the following command to check whether there are any conflicts among the patches to be applied, which will check if there are any conflicts among the patches to be applied, by giving out the detailed information about the conflicts/supersets:Run the following command to check whether there are any conflicts between the patches to be […]

SERVICE OPENING of Oracle Grid


bash-3.00$ cd /var/opt/oracle bash-3.00$ ls ocr.loc      oraInst.loc  oratab       scls_scr bash-3.00$ vi oratab “oratab” 32 lines, 1210 characters *:/export/home/oracle/OracleHomes/db10g:N *:/export/home/oracle/OracleHomes/oms10g:N emrep:/export/home/oracle/OracleHomes/db10g:N EMREP11:/export/homet0/ora11/app/ora11/product/11.2.0/dbhome_1:N *:/export/homet0/ora11/Oracle/Middleware/oms11g:N *:/export/homet0/ora11/Oracle/Middleware/agent11g:N MYTESTDB:/export/homet0/ora11/app/ora11/product/11.2.0/dbhome_1:N GRIDDB:/export/homet0/ora11/app/ora11/product/11.2.0/dbhome_1:N *:/export/homet0/ora11g/Oracle/Middleware/oms11g:N *:/export/homet0/ora11g/Oracle/Middleware/agent11g:N SET OMS ORACLE_HOME  export ORACLE_HOME=/export/homet0/ora11g/Oracle/Middleware/oms11g Go to directoryse BIN cd /export/homet0/ora11g/Oracle/Middleware/oms11g/bin Ststus kontrol bash-3.00$ ./emctl status oms Oracle Enterprise Manager 11g Release 1 Grid Control  Copyright (c) 1996, 2010 […]

Logon Trigger’ının yazılması


Aşağıdaki Condition’daki her bir user’ için aşağıdaki logon trigger yaratılır. if v_USERNAME in (  ‘CHEQUE’,                     ‘CHEQUEAPP’,                     ‘ETCUSER’,                     ‘ETCUSEREXT’,                     ‘MASTER’,                     ‘MONLEON’                  ) CREATE OR REPLACE TRIGGER SYS.LOGON_TRIGGER_MASTER AFTER LOGON ON SYS.SCHEMA DECLARE    v_sid       number;    v_program   VARCHAR2 (128);    v_module    VARCHAR2 (128);    v_username  VARCHAR2 (128);    v_machine   VARCHAR2 […]

DB ye bağlanan Sessionların bilgilerinin toplanması


Her 15 dakika’da çalışan bir job ile v$session’dan USERNAME MODULE PROGRAM MACHINE OSUSER Bilgileri toplanır.   Bu toplanan bilgiler ile DB ye hangi sessionalrı erişip erişemiyeceğine karar verilecektir. Bu amaçla aşağıdaki script SYS user’da çalıştırılır. set serveroutput on create table  collect_table as     select  distinct  username ,program ,module ,machine ,osuser    from v$session; CREATE OR REPLACE procedure SYS.collect_data […]

Adding ASM disk / sample scenarios


export ORACLE_HOME=/oraclegrid/11.2.0/grid export ORACLE_SID=+ASM1 sqlplus / as sysasm Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production With the Real Application Clusters and Automatic Storage Management options SQL> alter diskgroup DATA add disk ‘/dev/rhdisk12’; SELECT name, header_status,total_mb/1024 “GB TOTAL DISK SIZE”,free_mb/1024 “GB FREE”, path FROM V$ASM_DISK; select name,total_mb/1024 “Total_size (GB)”,free_mb/1024 “free_size (GB)” […]

IF YOU LOST ALL CONTROLFILE(RECOVER CONTROLFILE)


bash-3.00$ pwd /export/d3120/oradata/JUNKDB bash-3.00$ cp /export/d3120/oradata/JUNKDB/backup/control01.ctl . bash-3.00$ cp control01.ctl control02.ctl bash-3.00$ cp control01.ctl control03.ctl bash-3.00$ ls *.ctl control01.ctl  control02.ctl  control03.ctl bash-3.00$ rm *.dbf bash-3.00$ bash-3.00$ bash-3.00$ ls arch           control01.ctl  control02.ctl  control03.ctl  redo01.log     redo02.log     redo03.log     backup bash-3.00$ rm *.ctl   bash-3.00$ cp ./backup/*.dbf . bash-3.00$ cp /export/d3120/oradata/JUNKDB/backup/control01.ctl . bash-3.00$ cp control01.ctl control02.ctl bash-3.00$ cp control01.ctl control03.ctl […]

If you lost all CONTROLFILE(create controlfile command)


bash-3.00$ ls -lrt total 3720312 -rw-r—–   1 oracle   oinstall 69214208 Nov 27 17:41 temp02.dbf -rw-r—–   1 oracle   oinstall 10493952 Nov 27 17:41 ttstest.dbf -rw-r—–   1 oracle   oinstall 52429312 Nov 27 17:45 redo02.log -rw-r—–   1 oracle   oinstall 52429312 Nov 27 18:14 redo03.log drwxr-xr-x   2 oracle   oinstall    1024 Nov 27 18:14 arch -rw-r—–   1 oracle   oinstall 5251072 […]

LOST if one or more CONTROLFILE


SQL> show parameter control_file NAME                                 TYPE        VALUE ———————————— ———– —————————— control_files                        string      /export/d3120/oradata/JUNKDB/c                                                  ontrol01.ctl, /export/d3120/or                                                  adata/JUNKDB/control02.ctl, /e                                                  xport/d3120/oradata/JUNKDB/con                                                  trol03.ctl SQL> shu abort ORACLE instance shut down. SQL> SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bit Production With the Partitioning, OLAP and Data Mining options bash-3.00$ ls […]

DB – Errorstack On/OFF


SQL> alter system set events ‘2049 trace name errorstack level 3’; System altered. SQL> alter system set events ‘2049 trace name errorstack off’;

A Different way of Designing


In the past, Fowler noted, systems design focused primarily on connecting networking with storage and the operating system to build a product – with an arm’s – length approach to applications. Oracle is able to archive so many new world records because the database team, the Java team, and the middleware team work together and […]

Moving Voting Disk


Notes: crsctl votedisk commands must be run as root If the new voting disk is on cluster file system, then it needs to be touched with proper ownership and permission before they can be added. If the old voting disk is on cluster file system, it needs to be deleted manually after crsctl delete css […]

Moving OCR Disk:


To replace or move the location of an OCR device: Notes: An ocrmirror must be in place before trying to replace the OCR device. The ocrconfig will fail with PROT-16, if there is no ocrmirror. If an OCR device is replaced with a device of a different size, the size of the new device will […]