What options are installed in the Oracle Database

select comp_name, version, status from dba_registry;

select * from v$option;

Active sql

select s.sid,
s.status,
s.process,
s.osuser,
a.sql_text,
p.program
from v$session s,
v$sqlarea a,
v$process p
where s.sql_hash_value=a.hash_value
and s.sql_address=a.address
and s.paddr=p.addr
and s.schemaname='BKO_DATA'
and s.status='ACTIVE';

gen_truncate_tables.sql

SET PAGES 0 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF


PROMPT
PROMPT ***** Truncate all tables *****
PROMPT

SPOOL truncate_tables.sql

SELECT 'SET ECHO ON TIME ON' FROM dual;
SELECT 'SPOOL truncate_tables.log' FROM dual;
SELECT '' FROM dual;

SELECT 'TRUNCATE TABLE '||owner||'.'||table_name||';'
FROM dba_tables
WHERE owner='&1'
/

SELECT '' FROM dual;
SELECT 'SPOOL OFF' FROM dual;
SELECT 'SET ECHO OFF TIME OFF' FROM dual;

SPOOL OFF
SET PAGES 999 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON

PROMPT
PROMPT *******************************
PROMPT
PROMPT Output saved at truncate_tables.sql
PROMPT

gen_enable_triggers.sql

SET PAGES 0 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF


PROMPT
PROMPT ***** Enable All Triggers *****
PROMPT

SPOOL enable_triggers.sql

SELECT 'SET ECHO ON TIME ON' FROM dual;
SELECT 'SPOOL enable_triggers.log' FROM dual;
SELECT '' FROM dual;

SELECT 'ALTER TRIGGER ' || owner ||'.'|| trigger_name ||' ENABLE;'
FROM dba_triggers
WHERE table_owner=Upper('&1')
/

SELECT '' FROM dual;
SELECT 'SPOOL OFF' FROM dual;
SELECT 'SET ECHO OFF TIME OFF' FROM dual;

SPOOL OFF
SET PAGES 999 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON

PROMPT
PROMPT ********************************
PROMPT
PROMPT Output saved at enable_triggers.sql
PROMPT

gen_enable_constraints.sql

SET PAGES 0 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF


PROMPT
PROMPT ***** Enable All Constraints *****
PROMPT

SPOOL enable_constraints.sql

SELECT 'SET ECHO ON TIME ON' FROM dual;
SELECT 'SPOOL enable_constraints.log' FROM dual;
SELECT '' FROM dual;

SELECT '-- foreign keys' FROM dual;

SELECT 'ALTER TABLE ' || owner ||'.'|| table_name ||
' ENABLE CONSTRAINT ' || constraint_name ||';'
FROM dba_constraints
WHERE owner = '&1'
AND constraint_type = 'R'
and status = 'DISABLED'
/


SELECT '-- primary keys' FROM dual;

SELECT 'ALTER TABLE ' || owner ||'.'|| table_name ||
' DISABLE CONSTRAINT ' || constraint_name ||';'
FROM dba_constraints
WHERE owner = '&1'
AND constraint_type = 'P'
and status = 'DISABLED'
/

SELECT '-- unique keys' FROM dual;

SELECT 'ALTER TABLE ' || owner ||'.'|| table_name ||
' DISABLE CONSTRAINT ' || constraint_name ||';'
FROM dba_constraints
WHERE owner = '&1'
AND constraint_type = 'U'
and status = 'DISABLED'
/

SELECT '' FROM dual;
SELECT 'SPOOL OFF' FROM dual;
SELECT 'SET ECHO OFF TIME OFF' FROM dual;

SPOOL OFF
SET PAGES 999 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON

PROMPT
PROMPT ********************************
PROMPT
PROMPT Output saved at enable_constraints.sql
PROMPT

gen_drop_sequences.sql

SET PAGES 0 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF

PROMPT
PROMPT ***** Drop All Sequences *****
PROMPT

SPOOL drop_sequences.sql

SELECT 'SET ECHO ON TIME ON' FROM dual;
SELECT 'SPOOL drop_sequences.log' FROM dual;
SELECT '' FROM dual;

SELECT 'DROP SEQUENCE ' || sequence_owner ||'.'|| sequence_name ||';'
FROM dba_sequences
WHERE sequence_owner = '&1'
/

SELECT '' FROM dual;
SELECT 'SPOOL OFF' FROM dual;
SELECT 'SET ECHO OFF TIME OFF' FROM dual;

SPOOL OFF
SET PAGES 999 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON

PROMPT
PROMPT ******************************
PROMPT
PROMPT Output saved at drop_sequences.sql
PROMPT

gen_disable_triggers.sql

SET PAGES 0 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF


PROMPT
PROMPT ***** Disable All Triggers *****
PROMPT

SPOOL disable_triggers.sql

SELECT 'SET ECHO ON TIME ON' FROM dual;
SELECT 'SPOOL disable_triggers.log' FROM dual;
SELECT '' FROM dual;

SELECT 'ALTER TRIGGER ' || owner ||'.'|| trigger_name ||' DISABLE;'
FROM dba_triggers
WHERE table_owner=Upper('&1')
/

SELECT '' FROM dual;
SELECT 'SPOOL OFF' FROM dual;
SELECT 'SET ECHO OFF TIME OFF' FROM dual;

SPOOL OFF
SET PAGES 999 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON

PROMPT
PROMPT ********************************
PROMPT
PROMPT Output saved at disable_triggers.sql
PROMPT

gen_disable_constraints.sql

SET PAGES 0 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF


PROMPT
PROMPT ***** Disable All Constraints *****
PROMPT

SPOOL disable_constraints.sql

SELECT 'SET ECHO ON TIME ON' FROM dual;
SELECT 'SPOOL disable_constraints.log' FROM dual;
SELECT '' FROM dual;

SELECT '-- foreign keys' FROM dual;

SELECT 'ALTER TABLE ' || owner ||'.'|| table_name ||
' DISABLE CONSTRAINT ' || constraint_name ||';'
FROM dba_constraints
WHERE owner = '&1'
AND constraint_type = 'R'
/


SELECT '-- primary keys' FROM dual;

SELECT 'ALTER TABLE ' || owner ||'.'|| table_name ||
' DISABLE CONSTRAINT ' || constraint_name ||';'
FROM dba_constraints
WHERE owner = '&1'
AND constraint_type = 'P'
/

SELECT '-- unique keys' FROM dual;

SELECT 'ALTER TABLE ' || owner ||'.'|| table_name ||
' DISABLE CONSTRAINT ' || constraint_name ||';'
FROM dba_constraints
WHERE owner = '&1'
AND constraint_type = 'U'
/

SELECT '' FROM dual;
SELECT 'SPOOL OFF' FROM dual;
SELECT 'SET ECHO OFF TIME OFF' FROM dual;

SPOOL OFF
SET PAGES 999 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON

PROMPT
PROMPT ********************************
PROMPT
PROMPT Output saved at disable_constraints.sql
PROMPT

ORA-03113: end-of-file on communication channel - Due to alert log reaching maximum file size.

# Problem - pmon process crashed - nothing in the alert log - startup nomount fails with ORA-03113

[oracle@dev-oranode-6 bdump]$ sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jun 15 10:12:20 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected.
SQL> startup nomount;
ORA-03113: end-of-file on communication channel

# Cause :- Alert log had reached 2GB maximum file size.
# Solution :- Rename the alert log, and try again.

Restore standby controlfile

# on primary
alter database create standby controlfile as'/oraexport/cfd_stby.ctl';

# on secondary
show parameter controlfile;
shutdown abort;
scp new standby controlfile from primary
startup mount

rman
connect target /
CATALOG START WITH '/sl02cfd/oradata1/S02CFD/datafile';
SWITCH DATABASE TO COPY;

What priveleges have been granted to a user?

select grantee, granted_role
from dba_role_privs
where grantee in ('MYUSER','MYROLE')
order by grantee, granted_role;

select grantee, privilege
from dba_sys_privs
where grantee in ('MYUSER','MYROLE')
order by grantee, privilege;

select grantee, substr(privilege,1,20), owner||'.'||table_name
from dba_tab_privs
where grantee in ('MYUSER','MYROLE')
order by grantee, owner, table_name, privilege;

Check when a user last changed their password

select name, ptime from sys.user$ where ltime is null order by name;

Set diagwait on RAC to get more logs

# stop crs (as root)
/opt/oracle/product/10.2.0/crs/bin/crsctl stop crs
# confirm it is stopped
ps -ef |egrep "crsd.bin|ocssd.bin|evmd.bin|oprocd"
ps -ef | grep oracle
# set diagwait
/opt/oracle/product/10.2.0/crs/bin/crsctl set css diagwait 13 -force
# verify the change
crsctl get css diagwait
# reboot, or restart CRS
/opt/oracle/product/10.2.0/crs/bin/crsctl start crs

Database Vault enable ALTER SYSTEM

# enable alter system

connect dvowner
BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => 'ALTER SYSTEM',
rule_set_name => 'Allow System Parameters',
object_owner => '%',
object_name => '%',
enabled => 'N');
END;
/

# disable alter system

connect dvowner
BEGIN
DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(
command => 'ALTER SYSTEM',
rule_set_name => 'Allow System Parameters',
object_owner => '%',
object_name => '%',
enabled => 'Y');
END;
/