Displays List of Sequence in Oracle Database

select  substr(sequence_owner,1,10)             "OWNER",
        substr(sequence_name,1,25)              "SEQ NAME",
        substr(to_char(min_value),1,3)          "MIN",
 max_value    "MAX",
 increment_by    "INC",
      cache_size    "CACHE",
 last_number    "LAST NUM"
from  all_sequences
order by 1 asc, 2 asc;

Users executing which type of command

SELECT
 sid,serial#,osuser,machine,module,action, v.schemaname,
 DECODE(COMMAND
 ,0,'None'
 ,2,'Insert'
 ,3,'Select'
 ,6,'Update'
 ,7,'Delete'
 ,8,'Drop'
 ,26,'Lock Table'
 ,44,'Commit'
 ,45,'Rollback'
 ,47,'PL/SQL Execute'
 ,'Other') command
FROM V$SESSION v
order by command

Useful script to show primary / foreign key relationships of all tables and views in a given schema

SELECT
D.TABLE_NAME "Table name",
D.CONSTRAINT_NAME "Constraint name",
DECODE(D.CONSTRAINT_TYPE,
 'P','Primary Key',
 'R','Foreign Key',
 'C','Check/Not Null',
 'U','Unique',
 'V','View Cons') "Type",
D.SEARCH_CONDITION "Check Condition",
P.TABLE_NAME "Ref Table name",
P.CONSTRAINT_NAME "Ref by",
M.COLUMN_NAME "Ref col",
M.POSITION "Position",
P.OWNER "Ref owner"
FROM
 DBA_CONSTRAINTS D
LEFT JOIN
 DBA_CONSTRAINTS P
 ON (D.R_OWNER=P.OWNER AND
D.R_CONSTRAINT_NAME=P.CONSTRAINT_NAME)
LEFT JOIN
 DBA_CONS_COLUMNS M
 ON (D.CONSTRAINT_NAME=M.CONSTRAINT_NAME)
WHERE
 D.TABLE_NAME
 IN (
 SELECT TABLE_NAME FROM DBA_TABLES WHERE
OWNER=UPPER(:b1)
 UNION ALL
 SELECT VIEW_NAME FROM DBA_VIEWS WHERE OWNER=UPPER(:b1)
 )
ORDER BY 1,2,3

UNDO TABLESPACE CORRUPTED

SQL> SHUTDOWN IMMEDIATE
ORACLE instance shut down.

change the undo_tablespce settings in pfile undo_tablespace='SYSTEM' AND undo_management='MANUAL'

SQL> STARTUP PFILE='D:\oracle\product\10.2.0\db_1\database\INITorcl.ora';
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             171968764 bytes
Database Buffers          432013312 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL>  create undo tablespace undotbs2 datafile 'C:\oracle\product\10.2.0\ORADATA
\ORCL\UNDOTBS02.dbf' size 100m AUTOEXTEND ON NEXT 10M;

Tablespace created.

SQL> SHUTDOWN IMMEDIATE

change the undo_tablespce settings in pfile undo_tablespace='UNDOTBS2' AND undo_management='AUTO'

ORACLE instance shut down.
SQL> STARTUP PFILE='D:\oracle\product\10.2.0\db_1\database\INITorcl.ora';
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             171968764 bytes
Database Buffers          432013312 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL>

Changing Case Sensitive Password In Oracle 11g

SQL> show parameter sec_case_sensitive_logon;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sec_case_sensitive_logon             boolean     TRUE

SQL> alter system set sec_case_sensitive_logon=false scope=both;

System altered.

SQL> show parameter sec_case_sensitive_logon

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sec_case_sensitive_logon             boolean     FALSE

[Now you can enter the password with or without CAPS]

Viewing the Alert Log

You can view the alert log with a text editor, with Enterprise Manager, or with the ADRCI utility.

To view the alert log with Enterprise Manager:

1.      Access the Database Home page in Enterprise Manager.

2.      Under Related Links, click Alert Log Contents.

The View Alert Log Contents page appears.

3.      Select the number of entries to view, and then click Go.

To view the alert log with a text editor:

1.      Connect to the database with SQL*Plus or another query tool, such as SQL Developer.

2.      Query the V$DIAG_INFO view

SELECT * FROM V$DIAG_INFO;

INST_ID NAME                  VALUE

------- --------------------- -------------------------------------------------------------

      1 Diag Enabled          TRUE

      1 ADR Base              /u01/oracle

      1 ADR Home              /u01/oracle/diag/rdbms/orclbi/orclbi

      1 Diag Trace            /u01/oracle/diag/rdbms/orclbi/orclbi/trace (txt log file)

      1 Diag Alert            /u01/oracle/diag/rdbms/orclbi/orclbi/alert (xml file)

      1 Diag Incident         /u01/oracle/diag/rdbms/orclbi/orclbi/incident

      1 Diag Cdump            /u01/oracle/diag/rdbms/orclbi/orclbi/cdump

      1 Health Monitor        /u01/oracle/diag/rdbms/orclbi/orclbi/hm

      1 Default Trace File    /u01/oracle/diag/rdbms/orclbi/orclbi/trace/orcl_ora_22769.trc

      1 Active Problem Count  8

      1 Active Incident Count 20

3.      To view the text-only alert log, without the XML tags, complete these steps:

a.       In the V$DIAG_INFO query results, note the path that corresponds to the Diag Trace entry, and open the directory to that path.

b.      Open file alert_SID.log with a text editor.

4.      To view the XML-formatted alert log, complete these steps:

a.       In the V$DIAG_INFO query results, note the path that corresponds to the Diag Alert entry, and change directory to that path.

b.      Open the file log.xml with a text editor.

To view the alert log with ADRCI in interactive mode:

1.       Start ADRCI in interactive mode.

·         Ensure that the ORACLE_HOEM and PATH environment variables are set properly

·         The PATH environment variable must include ORACLE_HOME/bin.

·         Open the command prompt ADRCI

·         TYPE SHOW ALERT

·         Select the option and [ENTER]

2.       Exit the editor to return to the ADRCI command prompt.

The following are variations on the SHOW ALERT command:

SHOW ALERT -TAIL

This displays the last portion of the alert log (the last 10 entries) in your terminal session.

SHOW ALERT -TAIL 50

This displays the last 50 entries in the alert log in your terminal session.

SHOW ALERT -TAIL -F

RPM commands

Installing new rpm package

# rpm -ihv package file name [Enter] 

Updating rpm package

# rpm -Uhv package file name [Enter] 

Installing rpm Package without dependencies

# rpm -ivh --nodeps package file name [Enter]

Uninstalling particular rpm package

# rpm -e package name [Enter] 

Remove an rpm package without dependencies

# rpm -ev --nodeps package name [Enter]

To check rpm particular package

# rpm -q application name [Enter]

Displays rpm package list

# rpm -qa [Enter] 

Check dependencies of rpm Package before Installing

# rpm -qpR package name [Enter]

Detailed view of the package info

# rpm -qi package name [Enter] 

Display list of packages that are installed in the appropriate package

# rpm -ql package name [Enter] 

Display of package names that contain the appropriate file

# rpm -qf file name [Enter] 

Display of the file that the package is dependent

# rpm -qR package name [Enter] 

Index size is more than table size to rebuild

select 'alter index '||a.index_owner||'.'||a.index_name||' rebuild online;' from
(select i.OWNER "INDEX_OWNER",i.INDEX_NAME,i.table_name,s.bytes/1024/1024 "INDEX_SIZE"
from dba_indexes i,dba_segments s
where i.index_name=s.SEGMENT_NAME and i.owner=s.owner and s.bytes/1024/1024 > 20) a,
(select t.OWNER "TABLE_OWNER",t.table_name,s.bytes/1024/1024 "TABLE_SIZE"
from dba_tables t,dba_segments s where t.table_name=s.SEGMENT_NAME and t.owner=s.owner and s.bytes/1024/1024 > 20) b
where a.index_owner=b.table_owner and a.table_name=b.table_name and a.index_size > b.table_size;

Which Session Generating More Redo

SELECT s.sid, s.serial#, s.username,s.osuser, s.machine,s.module, s.program,i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid ORDER BY 5 desc;

Oracle Database Size

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;

Database started and automatically going down because of PMON termination.

In the alert log:

PMON (ospid: 9776): terminating the instance due to error 472

Mon Jan 12 10:44:38 2015

Instance terminated by PMON, pid = 9776

Solution:

This error due to undo corruption in the database can be solved with creating undo tablespace and deleting old undo tablespace

Step1:Query Undo Parameters

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

Step2: Query for the undo tablespace name and location

SQL> select tablespace_name, file_name from dba_data_files

  2  where tablespace_name like '%UNDO%';

TABLESPACE FILE_NAME

---------- ----------------------------------------

UNDOTBS1    C:\ORACLE\ORADATA\MYDB1\UNDO_TBS01.DBF

Step3: Create New undo tablespace

create UNDO tablespace UNDOTBS DATAFILE 'C:\oracle\oradata\mydb1\UNDOTBS01.dbf' size 1615M REUSE AUTOEXTEND ON NEXT 4096k

Step4: Set undo tablespace parameter

SQL> alter system set undo_tablespace = 'UNDOTBS' scope=spfile;

Step5: Offline the old undo tablespace and drop

SQL> alter tablespace UNDOTBS1offline;

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Step6: Changed the undo management parameter to AUTO

SQL> alter system set undo_management='AUTO' scope=spfile;

Session blocking

select (select osuser from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select osuser from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

Check the Session accessing objects

SELECT /* + RULE */ LS.OSUSER OS_USER_NAME, LS.USERNAME USER_NAME,
DECODE (LS.TYPE, 'RW', 'ROW WAIT ENQUEUE LOCK', 'TM', 'DML ENQUEUE LOCK', 'TX',
'TRANSACTION ENQUEUE LOCK', 'UL', 'USER SUPPLIED LOCK') LOCK_TYPE,
O.OBJECT_NAME OBJECT, DECODE (LS.LMODE, 1, NULL, 2, 'ROW SHARE', 3,
'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', NULL)
LOCK_MODE, O.OWNER, LS.SID, LS.SERIAL# SERIAL_NUM, LS.ID1, LS.ID2
FROM SYS.DBA_OBJECTS O, (SELECT S.OSUSER, S.USERNAME, L.TYPE,
L.LMODE, S.SID, S.SERIAL#, L.ID1, L.ID2 FROM V$SESSION S,
V$LOCK L WHERE S.SID = L.SID) LS WHERE O.OBJECT_ID = LS.ID1 AND O.OWNER
<> 'SYS' ORDER BY O.OWNER, O.OBJECT_NAME

Find UDUMP file through SQL

select p.value || '/' || i.instance_name || '_ora_' || spid || '.trc' as "trace_file_name"
from v$parameter p,
v$process pro,
v$session s,
(Select sid from v$mystat where rownum = 1) m,
v$instance i
where lower (p.name) = 'user_dump_dest'
and pro.addr = s.paddr
and m.sid = s.sid;

REDO LOG RECOVERY

Original Post: https://jtechspot.com/recovering-after-the-loss-of-online-redo-log-files-scenarios/

SQL> startup

ORA-00314: log 1 of   thread 1 ,  expected sequence # does not   match 
ORA-00312:   online   log 1   thread 1: '/home/app/oracle/oradata/jazdb/redo01.log'

Check the status of Online redo log (whether current or inactive)

SQL> select group#, sequence#, archived, status from v$log

GROUP#	SEQUENCE#	ARCHIVED	STATUS
1	331	YES	INACTIVE
2	332	NO	CURRENT
3	330	NO	INACTIVE

scenario1:

If the damaged online log file is inactive and archive is YES

use clear command to clean up the file

SQL> alter database clear logfile group 1;

scenario2:

If the damaged online log file is inactive and archive is NO

use clear command to clean up the file

SQL> alter database clear unarchived logfile group 3;

open database and take backup

scenario3:

If the damaged or deleted online log file is current

When the database goes down

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/home/app/oracle/oradata/jazdb/redo02.log'

ORA-27041: unable to open file

SQL> startup mount

When try to clear logfile

SQL> alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance jazdb (thread 1)

ORA-00312: online log 2 thread 1: '/home/app/oracle/oradata/jazdb/redo02.log'

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;

SQL> recover database until cancel

type cancel

SQL> alter database open resetlogs;

After database successfully open do a full backup

Disable The Iptables Firewall in CentOS/RHEL

Check the Status of The Iptables Firewall
Check the Status of The Iptables IPv4 Firewall
Run the following command to check the status of the iptables IPv4 firewall :
# service iptables status

Check the Status of The Iptables IPv6 Firewall
Run the following command to check the status of the iptables IPv6 firewall :
# service iptables status

Disable The Iptables Firewall
Use the following commands to stop the Iptables Firewall and remove it from autostart.

Disable The Iptables IPv4 Firewall
Run the following commands to stop the iptables firewall for IPv4 :
# service iptables save
# service iptables stop
# chkconfig iptables off

Disable The Iptables IPv6 Firewall
Run the following commands to stop the iptables firewall for IPv6 :
# service iptables save
# service iptables stop
# chkconfig iptables off

Enable The Iptables Firewall
Use the following commands to start the Iptables Firewall and add it to autostart.

Enable The Iptables IPv4 Firewall
Run the following commands to start the iptables firewall for IPv4 :
# service iptables start
# chkconfig iptables on

Enable The Iptables IPv6 Firewall
Run the following commands to start the iptables firewall for IPv6 :
# service iptables start
# chkconfig iptables on

OCA-01: SQL Plus, iSQL Plus

http://www.net4saudi.com/OracleDba/Index.html

Redo Generated per day

SELECT trunc(first_time) DAY,
count(*) NB_SWITCHS,
trunc(count(*)*log_size/1024) TOTAL_SIZE_KB,
to_char(count(*)/24,'9999.9') AVG_SWITCHS_PER_HOUR
FROM v$loghist,
(select avg(bytes) log_size from v$log) GROUP BY trunc(first_time),log_size
order by 1 desc

Sql Query to know the Oracle Database Uptime

select 'Hostname      : ' || host_name "HOST NAME"
      ,'Instance Name : ' || instance_name "INSTANCE NAME"
      ,'Started At    : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS')  "STARTED TIME"
      ,'Uptime        : ' || floor(sysdate - startup_time) || ' days(s) ' ||
       trunc( 24*((sysdate-startup_time) -
       trunc(sysdate-startup_time))) || ' hour(s) ' ||
       mod(trunc(1440*((sysdate-startup_time) -
       trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
       mod(trunc(86400*((sysdate-startup_time) -
       trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from v$instance