FIND no.of ROWS and COLUMNS in a table

QUERIES to find no.of ROWS and COLUMNS in a TABLE


For ROWS

select table_name,sum(num_rows) from user_tables where table_name='<TABLE_NAME>' group by table_name;

select table_name,sum(num_rows) from all_tables where table_name='<TABLE_NAME>' and owner='<owner_name>'  group by table_name;




For COLUMNS

SELECT count(*) FROM user_tab_columns WHERE table_name = 'FOO'

select count(*) from all_tab_columns where owner='BAR' and table_name='FOO';

EXCLUDE INDIVISUAL TABLES FROM SCHEMAS

EXCLUDE PARTICULAR TABLES 



expdp directory=data_pump_dir dumpfile=full_database.dmp schemas=PITT logfile=full_database.log 
exclude=table:\"in\(\'QP_INVOICE\')\"




exclude=table:"in\('QP_INVOICE'\,'QP_INVOICE_LINE'\)"

Database Refreshment Using EXPDP/IMPDP

DB REFRESH USING LOGICAL BACKUP

DB Refresh steps:
=================

1. take export(expdp) of source database(ctsp).

expdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull.log full=y



2. move the dumpfile to target database(ctsd) using scp.. if both the database running in same server this step is not required.

scp ctsfull.dmp oracle@<Target server IP>:/u02/app/oracle



3. Drop the application users and dont drop database default users like sys,system,dbsnmb,xdb.

If you give below command it will show username,created date. Mostly database default users will be created in same day.

select username,created from dba_users;

drop user raja cascade;



4. Before doing import(impdp) check the used space of source database and freespace in the target database. tablespaces names should 

same between source and target database then Target database each tablespace size should be more than or equal to source database tablespace.

Source:

select tablespace_name,sum(bytes/1024/1024) from dba_segments group by tablespace_name;

Target:

select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;




5. Start the import in taget database.

impdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull_impdp.log full=y




6. once competed compare the object counts between source and target databases.

select object_type,count(*) from dba_objects where status='VALID' group by object_type;

Kill Unwanted SESSIONS in ORACLE

Kill Sessions in Oracle

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

OUTPUT:


   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
         1         30         15 3859       TEST       sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1         23        287 3834       SYS        sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1         40        387 4663                  oracle@oel5-11gr2.localdomain (J000)
         1         38        125 4665                  oracle@oel5-11gr2.localdomain (J001)


 then kill these types

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

DISCONNECT SESSION is another technique to kill sessions

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

DROP DATABASE

Drop DATABASE in SQL and RMAN

############# SQL ##############

startup mount exclusive restrict;
drop database;
exit


############# RMAN ###############

RMAN> CONNECT TARGET SYS@test1

target database Password: password
connected to target database: TEST1 (DBID=39525561)

RMAN> STARTUP FORCE MOUNT
RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;
RMAN> exit

FIND TABLE QUERY in SQL

FIND TABLE QUERY in SQL


select dbms_metadata.get_ddl('TABLE','QP_REQUISITION','SALES') from dual;

Set UNLIMITED PASSWORD for users

SETUP UNLIMITED PASSWORDS


CREATE PROFILE PASS_PRO LIMIT PASSWORD_LOCK_TIME UNLIMITED

ALTER USER <username> profile PASS_PRO;

commit;

Simple way to find the DB STARTUP and SHUTDOWN with Triggers

Simple way to check DB STARTUP  and SHUTDOWN time 

Whenever Instance was started or shutdown, These information not stored in Oracle Views/Tables. In this case used to refer Alert Log file when it was bounced.
Instead of reviewing we can create a table, along with this have to create two tirggers, One trigger will be activated once startup time and another trigger will be activated on shutdown. at the same time whenever trigger activated table Will be updated. So that we can view from Table all the history after creating the table with trigger.



STEP1- 

CREATE TABLE CKPT_DATABASE_ACTIVITY (NAME VARCHAR2(20 BYTE),TIME DATE,DESCRIPTION  VARCHAR2(30 BYTE)) TABLESPACE USERS;


STEP2- 

CREATE OR REPLACE TRIGGER CKPT_TRIG_STARTUP AFTER STARTUP ON DATABASE
 BEGIN
   INSERT INTO CKPT_DATABASE_ACTIVITY
   VALUES (USER, SYSDATE, 'STARTUP’);
 END;



STEP3- 

CREATE OR REPLACE TRIGGER CKPT_TRIG_SHUTDOWN BEFORE SHUTDOWN ON DATABASE
 BEGIN
   INSERT INTO CKPT_DATABASE_ACTIVITY
   VALUES (USER, SYSDATE, 'SHUTDOWN’);
 END;

ORA-600 error ( kcratr_scan_lastbwr ) solution

Solution for ORA-600 error (  kcratr_scan_lastbwr )


database is not opening
1. sqlplus / as sysdba

2.startup nomount

3.alter database mount;
  3.1 select instance_name,status fom v$instance;

4.recover database;
if not able to recover follow below procedure
  recover database until cancel using backup controlfile;

5.alter database open;
if not ------   alter database open resetlogs;
  5.1 select instance_name,status fom v$instance;

Change the TIME to CDT time

CDT time format


select * from qp_edisbursement_candidate where load_timestamp =to_timestamp_tz('Tue Mar 11 06:30:38 CDT 2014','Dy Mon DD HH:MI:SS TZD YYYY');




CDT time format

('Tue Mar 11 06:30:38 CDT 2014','Dy Mon DD HH:MI:SS TZD YYYY');

IMPORTANT COMMANDS on LINUX for RPM

RPM IMPORTANT COMMANDS



RPM --- www.rpm.org

1.Install RPM
rpm -ivh <RPM name>

2.Upgrade RPM package
rpm -Uvh <rpm name>

2. Check RPM
rpm -q <RPM name>    --- check rpm installed/not
rpm -qi <RPM name>   --- check detailed information
rpm -qf <rpm name>    --- location of RPM and files
rpm -qa --- list all installed rpms

3. Find dependencies of perticular RPM package
rpm -qpR <rpm name>

3. Remove rpm package
rpm -e <RPM name>  --- remove rpm
rpm -ev <rpm name> --- remove  rpm package
rpm -ev --nodeps <rpm name> --- remove rpm without checking dependencies
 
4.check recently installed packges
rpm -qa -last

4. Verify installed packges
rpm -V <rpm name>
rpm -Va

5.find dependencies
rpm -qR <rpm name>
6.dispaly less/more packges
rpm -qa | less   --- dispaly less list
rpm -qa | more   --- dispaly more list

RMAN-06035 RMAN is not starting

Error RMAN-06035 RMAN not starting


RMAN not starting in windows/linux 

ERROR-  RMAN-06035: wrong version of recover.bsq, expecting 11.2.0.1, found 11.2.0.4

solution is setup paths are correctly

WINDOWS
set ORACLE_HOME=d:\app\oracle\product\11.2.0\db_home\
set ORACLE_SID=ORPSCS90
set PATH=%ORACLE_HOME%\bin;%PATH%

LINUX
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home/
export ORACLE_SID=ORPSCS90
export PATH=$ORACLE_HOME\bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME\lib

SIMPLE TIP for EXPORT USING QUERY PARAMETER

EXPORT USING QUERY


1.Run this query by cheking the where clause is working or not

select <column name> FROM <table_name> WHERE <column_name> BETWEEN to_date('31-JAN-12') and to_date('30-MAY-12');



2.Run EXPORT(EXPDP) by using "QUERY" parameter like below

expdp dumpfile=1111.dmp tables=<tablename> query=\"where INSERT_TIME BETWEEN to_date\(\'01-JAN-2012\'\) AND to_date\(\'04-MAR-2012\'\)\"


TIP: If your query is not working in EXP/EXPDP follow these steps
        
       1. 1st put where clause in DOUBLE QUOTES( " ")
       2. then put SLASH( \ ) before each and every symbol in the query parameter
       3. please dont use SLASH( \ ) in the dates and column names

Tablespace Usage QUERY

CHECK THE TABLESPACE USAGE

rem SET TERMOUT OFF
rem
set linesi 132
set pagesi 1000
column tablespace format a30 heading Tablespace
column count(*) format 999,999 heading 'Blks Count'
column total_kb format 999,999,999 heading 'Total KB'
column free_kb format 99,999,999 heading 'Free KB'
column max_kb format 9,999,999 heading 'Max Free KB'
column min_kb format 9,999,999 heading 'Min Free KB'
column PerFree format 999 heading '%Free'
column Threshold format 999 heading 'Limit'
column Ext_count format 99,999 heading 'Ext Count'
rem
BREAK ON REPORT
COMPUTE SUM OF TOTAL_KB ON REPORT
COMPUTE SUM OF FREE_KB ON REPORT
rem
SET TERMOUT ON
SELECT ROUND((DECODE(A.FREE_BYTES,NULL,0,A.FREE_BYTES)/B.TOTAL_BYTES) * 100) PerFree,
       C.THRESHOLD Threshold,
       RPAD(B.TABLESPACE_NAME,30,'.') tablespace,
       B.TOTAL_BYTES/1024 total_kb,
       NVL(A.FREE_BYTES/1024, 0) free_kb, 
       A.MAX_BYTES/1024 max_kb, 
       A.MIN_BYTES/1024 min_kb,
       EXT_COUNT
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) FREE_BYTES,
              MAX(BYTES) max_BYTES, 
              MIN(BYTES) min_BYTES,
              count(*) EXT_COUNT
              FROM DBA_FREE_SPACE
              GROUP BY TABLESPACE_NAME) A, 
      (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL_BYTES
              FROM DBA_DATA_FILES 
              GROUP BY TABLESPACE_NAME )B,
      (select tablespace_name, threshold from OSA_ORACLE.tbs ) c
WHERE A.TABLESPACE_NAME(+) = B.TABLESPACE_NAME
and   C.TABLESPACE_NAME(+) = A.TABLESPACE_NAME

ORDER BY B.TABLESPACE_NAME;

STEPS TO SYNC MISSING ARCHIVES FROM PRIMARY TO STANDBY

DATAGUARD MISSING ARCHIVES SYNCING

step:1.
Check for GAP on standby(primary & standby)

 select max(sequence#) from v$log_history;

primary > SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"
          FROM V$ARCHIVED_LOG
          WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
          ORDER BY 1;



step:2
Check redo received on standby and Check redo applied on standby

standby > SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - 

APPL.SEQUENCE#) "Difference"
          FROM
         (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V

$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
         (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY 

GROUP BY THREAD#)) APPL
         WHERE
         ARCH.THREAD# = APPL.THREAD#
          ORDER BY 1;




step3:
Identify missing archive log files

standby > SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

-- if GAP
standby > SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;




step4:
Copy archive log files

After identifying a gap (as shown above), the DBA will need to query the primary database
to locate the archived redo logs on the primary database. The following query assumes the
local archive destination on the primary database is LOG_ARCHIVE_DEST_1:

primary > SELECT name
            FROM v$archived_log
            WHERE thread# = 1
              AND dest_id = 1
              AND sequence# BETWEEN 24 and 28;

scp /backup /standby lov/path/backup

or take incremental backup in RMAN 

BACKUP CHECK LOGICAL AS BACKUPSET INCREMENTAL LEVEL 1 DEVICE TYPE DISK TAG = 'HOURLY_INCREMENTAL_ARCHIVELOG' FORMAT 

'/u01/oradata/backups/hourly_incremental_archivelog_%d_t%t_s%s_p%p' ARCHIVELOG FROM TIME 'SYSDATE-1';






step5:

Register archive log files with standby

--  Copy the above redo log files to the physical standby database and register
    them using the ALTER DATABASE REGISTER LOGFILE ... SQL statement on the
    physical standby database.
   
    For example:

standby > ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s24.dbf';
standby > ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s25.dbf';
standby > ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s26.dbf';
standby > ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s27.dbf';
standby > ALTER DATABASE REGISTER LOGFILE '/u02/oraarchive/TESTDB/arch_t1_s28.dbf';





step6:
Restart the managed recovery operations


-- After the redo logs have been registered on the physical standby database,
   the DBA can restart the managed recovery operations.
  
   For example, to put the physical standby database into automatic recovery managed mode:


standby > alter database recover managed standby database cancel;
standby > alter database recover managed standby database disconnect from session;

CHECKING Dataguard ARCHIVE SYNC

SIMPLE STEPS TO CHECK ARCHIVE SYNC

PRIMARY:
Run this below query in Primary side

select max(sequence#), archived FROM V$LOG where status <>'CURRENT' group by archived;


STANDBY:
Run this below query at Standby side

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

RMAN BACKUPS RUNNING with WARNINGS

 CHECK ERROR FOR BACKUPS RUNNING WITH WARNINGS 

1.RUN the below query

select session_key,input_type,status,SESSION_STAMP,to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,to_char(end_time,'yyyy-mm-dd hh24:mi') 

end_time,output_bytes_display,time_taken_display from v$rman_backup_job_details order by session_key desc;


2.Note the TIMESTAMP number

3.RUN the below query for output

select output from v$rman_output where session_stamp=734738844;