script to do full restore on Netezza database

2013-08-02 12:55:09.428218 PDT Info: NZ-00022: --- program 'restoresvr' (4080) starting on host 'nsdwt03' ... ---
2013-08-02 12:55:09.454282 PDT Info: Restore command: nzrestore -u admin -db XYZ -sourcedb ABC -dir /netback_old/backup/july -backupset 20130721195736 -npshost VIP
2013-08-02 12:55:09.454302 PDT Info: Setting the command line options
2013-08-02 12:55:09.776227 PDT Info: Restoring from base directory '/netback_old/backup/july'
2013-08-02 12:55:09.776362 PDT Info: Starting the restore process
2013-08-02 12:57:24.888546 PDT Info: Reading locations file
2013-08-02 12:57:25.459500 PDT Info: Warning: Invalid backupset, found /Netezza/VIP.petc.com/ABC/20130721195736/1/FULL in locations.txt, expected /Netezza/VIP/ABC/20130721195736/1/FULL.
2013-08-02 12:57:26.871784 PDT Info: Reading schema from /netback_old/backup/july/Netezza/<VIP>/ABC/20130721195736/1/FULL/md
2013-08-02 12:57:26.872418 PDT Info: Opening pipe for schema read ...
2013-08-02 12:57:27.252432 PDT Info: Restoring a 4.0 backup from NPS Release 6.0.2 (P-1) [Build 17433] created on xs system 'nsdwp01'
2013-08-02 12:57:27.252454 PDT Info: Restore of 92 dataslice backupset to 46 dataslice system
2013-08-02 12:57:27.657741 PDT Info: Operation txid and history table record 2773474
2013-08-02 12:57:27.669672 PDT Info: Restoring schema
2013-08-02 12:58:24.897130 PDT Info: Start restoring the data, compressed format.

script to check standby sync status with primary oracle database?

SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,'.')-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
);

script to identify the corrupted block name in oracle database?

select segment_name,segment_type,owner
from sys.dba_extents
where file_id=180
and (133571) between block_id and block_id + blocks -1;

script to list fragmented tables on oracle database schema?

select owner,table_name,round((blocks*8192),2)/1024/1024 "size (MB)" , round((num_rows*avg_row_len/1024/1024),2) "actual_data (MB)",
round((round((blocks*8192),2)/1024/1024 - round((num_rows*avg_row_len/1024/1024),2)),1) "wasted_space (MB)"
from dba_tables where (round((blocks*8192),2)/1024/1024 > round((num_rows*avg_row_len/1024/1024),2))
and (round((round((blocks*8192),2)/1024/1024 - round((num_rows*avg_row_len/1024/1024),2)),1)) > 100 and owner='ABC'
order by 5 desc;

script to compile all invalid object on oracle database?

select 'alter view man.'||object_name||' compile;' from dba_objects where owner='man' and status='INVALID' and object_type='VIEW';

select 'alter package man.'||object_name||' compile body;' from dba_objects where owner='man' and status='INVALID' and object_type='PACKAGE BODY';
select 'alter function man.'||object_name||' compile ;' from dba_objects where owner='man' and status='INVALID' and object_type='FUNCTION';
select 'alter procedure man.'||object_name||' compile ;' from dba_objects where owner='man' and status='INVALID' and object_type='PROCEDURE';
select 'alter view stage.'||object_name||' compile;' from dba_objects where owner='stage' and status='INVALID' and object_type='VIEW';
select 'alter procedure stage.'||object_name||' compile ;' from dba_objects where owner='stage' and status='INVALID' and object_type='PROCEDURE';

scrip to list top 10 hottest object in oracle database

col owner  format a20 trunc
col object_name format a30
col touches  format 9,999,999
select *
from (
 select count(*)
 , sum(tch) TOUCHES
 , u.name OWNER
 , o.name OBJECT_NAME
 from  x$bh x
 , obj$ o
 , user$ u
 where x.obj = o.obj#
 and o.owner# = u.user#
 group  by u.name, o.name
   order by 2 desc
 )
where rownum < 11;

script to find who is using UNDO tablespace in oracle?

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
           NVL(s.username, 'None') orauser,
           s.program,
           r.name undoseg,
           t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
      FROM sys.v_$rollname    r,
           sys.v_$session     s,
           sys.v_$transaction t,
           sys.v_$parameter   x
    WHERE s.taddr = t.addr
      AND r.usn   = t.xidusn(+)
      AND x.name  = 'db_block_size';

script to find who is using temp tablespace in oracle?

SELECT b.tablespace,
           ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
           a.sid||','||a.serial# SID_SERIAL,
           a.username,
           a.program
      FROM sys.v_$session a,
           sys.v_$sort_usage b,
           sys.v_$parameter p
     WHERE p.name  = 'db_block_size'
      AND a.saddr = b.session_addr
   ORDER BY b.tablespace, b.blocks;

script to find lon running queries on oracle database?

select sid,
message || '(' || time_remaining || ')' "Long Ops"
from v$session_longops
where time_remaining > 600;

important views in oracle database

Object related
dba_tables,
dba_views
dba_packages
dba_synonyms
dba_functions
dba_sequences
dba_triggers

roles related
dba_roles
dba_role_privs
dba_sys_privs
dba_tab_privs
role_role_privs

System related
v$backup
V$controlfile
V$log
V$archived_log
v$archived_gap
v$recover_file
v$session
v$session_longops

script to find out archive generation in hourly basis in oracle database?

elect to_char(first_time,'YYYY.MM.DD') day,
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'00',1,0)),'99') "00",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'01',1,0)),'99') "01",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'02',1,0)),'99') "02",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'03',1,0)),'99') "03",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'04',1,0)),'99') "04",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'05',1,0)),'99') "05",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'06',1,0)),'99') "06",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'07',1,0)),'99') "07",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'08',1,0)),'99') "08",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'09',1,0)),'99') "09",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'10',1,0)),'99') "10",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'11',1,0)),'99') "11",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'12',1,0)),'99') "12",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'13',1,0)),'99') "13",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'14',1,0)),'99') "14",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'15',1,0)),'99') "15",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'16',1,0)),'99') "16",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'17',1,0)),'99') "17",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'18',1,0)),'99') "18",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'19',1,0)),'99') "19",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'20',1,0)),'99') "20",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'21',1,0)),'99') "21",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'22',1,0)),'99') "22",
       to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'23',1,0)),'99') "23"
  from v$log_history
 group by to_char(first_time,'YYYY.MM.DD')
/

script to find what are the objects accessed by user?

SET LINESIZE 255
 SET VERIFY OFF
 COLUMN object FORMAT A30
SELECT a.object, a.type, a.sid, b.username, b.osuser, b.program FROM v$access a, v$session b WHERE a.sid = b.sid AND a.owner = UPPER('&1') ORDER BY a.object;

script to turn on orale database sessions

SQL> ALTER SYSTEM SET timed_statistics = true;
SQL> execute dbms_system.set_sql_trace_in_session(sid, serial#, true);
finaly you have to stop it.

script to check DG gap in oracle data gaurd?

 select max(sequence#),applied from v$archived_log group by applied;
 

script to list Top 10 big objects in oracle?

Col owner format a15
Col segment_name format a30
Col segment_type format a15
Col mb format 999,999,999
select  owner,segment_name,segment_type,mb
from(select owner,segment_name,segment_type,bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11

script to find blocking/locking session on oracle database?

select s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    from v$lock l1, v$session s1, v$lock l2, v$session s2
    where s1.sid=l1.sid and s2.sid=l2.sid
    and l1.BLOCK=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2 ;

script to find out sga component usage in oracle database?

elect name, round(sum(mb),1) mb, round(sum(inuse),1) inuse
            from (select case when name = 'buffer_cache'
                              then 'db_cache_size'
                           when name = 'log_buffer'
                             then 'log_buffer'
                            else pool
                        end name,
                     bytes/1024/1024 mb,
                        case when name <> 'free memory'
                       then bytes/1024/1024
                      end inuse
                 from v$sgastat
         )group by name;

script to find out oracle database size?

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
 round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select bytes from v$datafile
         union all
  select bytes from v$tempfile
  union  all
  select bytes from v$log) used,
  (select sum(bytes) as p from dba_free_space) free
        group by free.p
 /

script to manually refresh oracle MVIEWS?

execute DBMS_SNAPSHOT.REFRESH( 'MV_EMP','f');

script to kill oracle database process ID in windows OS level ?

C:> orakill ORACLE_SID spid

script to find most CPU consuming oracle database session

select a.username,a.logon_time,b.SQL_FULLTEXT from v$session a,v$sqlarea b where
a.sql_id=b.sql_id
and a.username='&USER'
order by a.logon_time desc;
SELECT CPU,Total_Rows,sql_text FROM(
SELECT (s.cpu_time/1000000) CPU, sa.rows_processed Total_Rows, SUBSTR(sa.sql_text,1,50) sql_text
FROM v$sqlarea sa, v$sql s
WHERE sa.parsing_user_id =
(SELECT user_id FROM dba_users WHERE username='USER'
AND s.sql_text = sa.sql_text)
ORDER BY s.cpu_time DESC)
WHERE ROWNUM <= 10;

script to take DDL of the DB link in oracle database?

SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) FROM dba_db_links a;

script to check oracle database last executed sql code?

select S.USERNAME||'('||s.sid||')-'||s.osuser     UNAM
--      ,s.program||'-'||s.terminal||'('||s.machine||')' PROG
      ,s.sid||'/'||s.serial# sid
     ,s.status "Status",p.spid
     ,sql_text sqltext
from v$sqltext_with_newlines t,V$SESSION s , v$process p
where t.address =s.sql_address
and p.addr=s.paddr(+)
and t.hash_value = s.sql_hash_value
order by s.sid,t.piece
/

script to find current sql code of the user or session?

SELECT OSUSER,SERIAL#,SQL_TEXT FROM V$SESSION, V$SQL WHERE V$SESSION.SQL_ADDRESS = V$SQL.ADDRESS AND V$SESSION.STATUS = 'ACTIVE';

SELECT OSUSER,SERIAL#,SQL_TEXT FROM V$SESSION, V$SQL WHERE V$SESSION.SQL_ADDRESS = V$SQL.ADDRESS AND V$SESSION.STATUS = 'ACTIVE' and sid=2017;

script to list snapshot in oracle AWR?

col "Date/Time" format a30
select snap_id
,       snap_level
, to_char(snap_time,'HH24:MI:SS DD-MM-YYYY') "Date/Time"
from stats$snapshot
, v$database
order by snap_id
/

script to generate to kill all session on oracle database?

select 'alter system kill session' '||sid||','||serial#||'' immediate;' from v$session where username='USER';

script to list content of temp files?

col useset pages 999 lines 100
rname format a15
col mb format 999,999
select  su.username
,       ses.sid
,       ses.serial#
,       su.tablespace
,       ceil((su.blocks * dt.block_size) / 1048576) MB
from    v$sort_usage    su
,       dba_tablespaces dt
,       v$session ses
where   su.tablespace = dt.tablespace_name
and     su.session_addr = ses.saddr
/

scripto to check free space on oracle tablespace?

select a.tablespace_name"TBS_NAME" ,
       a.mB Mbytes_used,
       b.mB Mbytes_free,
 round(((a.mB-b.mB)/a.mB)*100,2)"%USED"
from
(select tablespace_name,sum(bytes/1024/1024) mB from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes/1024/1024) mB from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 4 desc ;

How to check long running queries in oracle databasE?

select substr(username,1,15),target,to_char(start_time, 'DD-Mon-YYYY HH24:MI:SS' ), SOFAR,substr(MESSAGE,1,70) from v$session_longops;


select USERNAME, to_char(start_time, 'DD-Mon-YYYY HH24:MI:SS' ),substr(message,1,90),to_char(time_remaining) from v$session_longops;

How to extract DDL of objects in oracle adtabase?

set heading off echo off  pages 999 long 9999999 trimspool on


select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
 select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces

How to check oracle job details ?

select job, SCHEMA_USER,THIS_DATE,BROKEN,WHAT from dba_jobs;

select * from dba_running_jobs;

How to check oracle database bit version?

select length(addr)*4 '-bits' word_length from v$process where ROWNUM =1;

how to fetch data from SQL server to Oracle server?

There are two method we can use to fetch data from SQL server to oracle server

1.install Odbc driver on oracle server and communicate to sql server an fetch the server and this will eat more CPU.
2.This method will be good method and easy also.
but this required gatewy server between oracle and sql server

steps
1.put gateway server entry on oracle tnsnames.ora
2.put another entry on gateway server about oracle server,os that both can talk to each other
3.add entry on listener on gateway server
4.add entry on driver on gateway server
c:\oracle\11g\dg4msql\admin\init<dbname>.ora
5.create user on sql server windows auth
6.create DB link on oracle server using sql server user and start fetch the data

How to connect to data gaurd broker?

DGMGRL> connect
Username: sys
Password:
Connected.
DGMGRL> show configuration;
Configuration - ABCCONF
  Protection Mode: MaxPerformance
  Databases:
    ABC     - Primary database
    ABC_DR- Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

How to identify what was the Primary host and standby Host on NPS?

Identifying the Active and Standby Nodes

[root@HA1]# crm_resource -r nps -W

crm_resource[5377]: 2009/01/31_10:13:12 info: Invoked: crm_resource -r nps -W

resource nps is running on: HA1

How to check the netezza appliance HA cluster setup status?

Display cluster status
 clustat -i 5
 crm_mon -i5

How to check event in NPS?

nzevent show -template

how to check the corrupted block in oracle?

use the below query to find out block corruption in oracle database
if the block is index try to rebuild or drop/recreate it
if the block is table block reload data using expdp/impdp or db link


select segment_name,segment_type,owner
from sys.dba_extents
where file_id=180
and (133571) between block_id and block_id + blocks -1;

output of root.sh in oracle?

# sh /oracle/oraInventory/orainstRoot.sh

Changing permissions of /oracle/oraInventory.

Adding read,write permissions for group.

Removing read,write,execute permissions for world.

Changing groupname of /oracle/oraInventory to oinstall.

The execution of the script is complete.

# /oracle/product/11gR2_3/root.sh

Performing root user operation for Oracle 11g

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /oracle/product/11gR2_3

Enter the full pathname of the local bin directory: [/usr/local/bin]:

Creating /usr/local/bin directory...

   Copying dbhome to /usr/local/bin ...

   Copying oraenv to /usr/local/bin ...

   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

how to check the java version installed on server?

)/home/oracle>java -version
java version "1.4.2"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2)
Classic VM (build 1.4.2, J2RE 1.4.2 IBM AIX build ca142-20090307 (SR13) (JIT enabled: jitc))