bind_cap.sql
column value_string format a30
column name format a30
column datatype_String format a20
select
sql_id,NAME,POSITION,DATATYPE_STRING,VALUE_STRING from v$sql_bind_capture where
sql_id = '&sqlid';
col_stats.sql
column histogram format a24
select
column_name,num_nulls,num_distinct,histogram,last_analyzed from dba_tab_columns
where table_name = '&tname' ORDER BY 1
/
Description: This
script will show you the user's OS name, Username in the database and the SQL
Text they are running
Code:
SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address =b.address
order by address, piece
-----------------------------------------------------------
To find full query on the basis of hash value
select SQL_TEXT from v$sqltext where
HASH_VALUE='&HASH_VALUE' order by piece
/
To get the hash value
====================
select
s.username,s.sid,s.serial#,s.sql_hash_value from v$session s,v$process p where
s.paddr=p.addr and p.spid=&PID;
To get the SQL_TEXT
===================
select
s.username,s.sid,s.serial#,s.sql_hash_value,substr(t.sql_text,1,100) from
v$session s,v$process p,v$sqlarea t
where s.paddr=p.addr and p.spid=15490 and t.address=s.sql_address;
OR
select
s.username,s.sid,s.serial#,s.sql_hash_value,t.sql_text from v$session
s,v$process p,v$sqlarea t where
s.paddr=p.addr and p.spid=15490 and t.address=s.sql_address;
Del_Stats
analyze table sysadm.PS_GP_EXCL_WRK delete statistics;
analyze table sysadm.PS_GP_HST_WRK delete statistics;
analyze table sysadm.PS_GP_JOB2_WRK delete statistics;
analyze table sysadm.PS_GP_PYE_HIST_WRK delete statistics;
analyze table sysadm.PS_GP_PYE_STAT_WRK delete statistics;
analyze table sysadm.PS_GP_RTO_PRC_WRK delete statistics;
analyze table sysadm.PS_GP_RTO_TRG_WRK1 delete statistics;
analyze table sysadm.PS_GP_RTO_TRGR_WRK delete statistics;
analyze table sysadm.PS_GP_TLPTM_WRK delete statistics;
analyze table sysadm.PS_GP_TLTRC_WRK delete statistics;
event.sql
select event, count(1) from v$session group by event order
by 2
/
Explain plan
@$ORACLE_HOME/rdbms/admin/utlxpls.sql
EXPLAIN PLAN FOR
SELECT BUSINESS
FROM PS_Z_BUSS_UNIT_VW
WHERE BUSINESS_LEVEL =2
AND BUSINESS_UNIT =
(SELECT
A.BUSINESS_UNIT
FROM PS_JOB A
WHERE A.EMPLID=
'090578'
AND A.EMPL_RCD=0
AND A.EFFDT =
(SELECT
MAX(B.EFFDT)
FROM PS_JOB B
WHERE A.EMPLID =
B.EMPLID
AND B.EMPL_RCD =
A.EMPL_RCD
AND B.EFFDT <=SYSDATE
)
AND A.EFFSEQ =
(SELECT
MAX(C.EFFSEQ)
FROM PS_JOB C
WHERE A.EMPLID =
C.EMPLID
AND A.EMPL_RCD =
C.EMPL_RCD
AND A.EFFDT = C.EFFDT
)
);
EXPLAIN PLAN FOR
SELECT BUSINESS
FROM ps_z_buss_unit_tbl
WHERE BUSINESS_LEVEL =2
AND BUSINESS_UNIT =
(SELECT
A.BUSINESS_UNIT
FROM PS_JOB A
WHERE A.EMPLID=
'090578'
AND A.EMPL_RCD=0
AND A.EFFDT =
(SELECT
MAX(B.EFFDT)
FROM PS_JOB B
WHERE A.EMPLID =
B.EMPLID
AND B.EMPL_RCD =
A.EMPL_RCD
AND B.EFFDT <=SYSDATE
)
AND A.EFFSEQ =
(SELECT
MAX(C.EFFSEQ)
FROM PS_JOB C
WHERE A.EMPLID =
C.EMPLID
AND A.EMPL_RCD =
C.EMPL_RCD
AND A.EFFDT = C.EFFDT
)
);
utlxpls.sql
AWR Report : $ORACLE_HOME/rdbms/admin/awrrpt.sql
Generate ADDM Report: @$ORACLE_HOME/rdbms/admin/addmrpt.sql
Find PID
set pages 500
set line 180
col MACHINE for a10
col PROGRAM for a30
col OSUSER for a10
col USERNAME for a10
col sql_text for a65
col a.sid for a5
col a.serial# for a5
select
c.sql_text,a.username,a.osuser,a.sid,a.serial#,a.program,a.machine,a.process
"APPPROC" ,
b.spid "DB PROC",hash_value
from v$session a , v$process b , v$sqlarea c
where a.sql_address=c.address (+) and
a.paddr=b.addr and
b.spid='&1'
handging.sql
set pagesize 100
col client_info format a40
col hangtime format a45
Select
VS.CLIENT_INFO,
' Hanging for '|| floor(vs.last_call_et/3600)||' Hours,'||
floor(mod(vs.last_call_et,3600)/60)||' Minutes, '||
mod(mod(vs.last_call_et,3600),60)||' Secs ' "HANGTIME",
vl.sql_text
From V$SqlArea VL, V$Process VP, V$Session VS
Where VP.Addr = VS.Paddr(+)
And Nvl(
VS.Sql_Address, 'FFFFFFFF') = VL.Address(+)
And Nvl( VS.SERIAL#,
0) > 1
and VS.Status =
'ACTIVE'
and vs.client_info
is not null
and last_call_et
> 180
/
Index_col
column column_name format a30
break on index_name
select index_name, column_name,column_position from
dba_ind_columns where table_name = '&tname' ORDER BY 1,3;
plan.sql
select * from table(dbms_xplan.display_cursor('&sqlid'))
/
Plana.sql
select * from table(dbms_xplan.display_awr('&sqlid'))
/
Planh.sql
set pages 500
set lines 145
col
BEGIN_INTERVAL_TIME format a35
select ss.snap_id,
ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000
avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))
avg_lio
from DBA_HIST_SQLSTAT
S, DBA_HIST_SNAPSHOT SS
where sql_id =
nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id =
S.snap_id
and
ss.instance_number = S.instance_number
and executions_delta
> 0 --and plan_hash_value=3099046451
order by 1, 2, 3
/
Psrun.sql
column event format a40
column lt format a25
select
sid,serial#,module,sql_id,to_char(logon_time,'dd-MON-YYYY hh24:mi:ss') LT
,event from v$session where username = 'SYSADM' and program like 'PSRUN%'
/
Table_stat.sql
select owner, table_name,
num_rows,sample_size,last_analyzed,partitioned from dba_tables where table_name
= '&tname'
/
Wait_event/sql
Description: This
script will display the waits on the instance since the instance has been
created. Good place to look for system bottle necks
Code:
col event form A50
col Prev form 999
col Curr form 999
col Tot form 999
select
event,
sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",
count(*) "Tot"
from
v$session_Wait
group by event
order by 4
view.sql
set long 10000
select text from dba_views where view_name = '&viewname'
/
Blockin_session.sql
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 ;
Blocking.sql
select 'SID ' ||
l1.sid ||' is blocking ' || l2.sid
blocking
from v$lock l1,
v$lock l2
where l1.block =1
and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
/
Lockobj.sql
column object_name format a27
column oracle_username format a15
column os_user_name format a10
select /*+ ORDERED */
b.object_name,
a.session_id,
a.oracle_username,
os_user_name ,
decode (a.locked_mode,1,'Null', 2,'Row-S', 3,'Row-X',
4,'Share', 5,'S/Row-X', 6,'Exclusive') lock_mode
from v$locked_object a, all_objects b
where a.object_id = b.object_id
order by b.object_name
/
Process in processing status
set lines 999
set pages 999
select b.sid,b.serial#,
prcsinstance,
prcstype,prcsname,
to_char(rqstdttm,'mm-dd-yy hh24:mi:ss') Start_Date,
oprid,
decode(runstatus,1,'Cancel',
2,
'Delete',
3, 'Error',
4, 'Hold',
5,
'Queued',
6,
'Initiated',
7,
'Processing',
8,
'Cancelled',
9,
'Success',
10,'No
Success',
11,'Posted',
12,'Not
Posted',
13,'Resend',
14,'Posting',
15,'Generated') "Status",
origprcsinstance
from SYSADM.PSPRCSQUE a, gv$session b
where runstatus='7'
and b.process=to_char(a.sessionidnum)
and b.client_info is not null
/
Privelage_gen.sql
set pagesize 10000
set echo off;
spool /home/oracle/scripts/05-04-2014_HYP_privilege.sql;
select 'set echo on;' from dual;
select 'spool privilege.log;' from dual;
select 'GRANT SELECT on '||TABLE_NAME|| ' to HYP;' from
DBA_TABLES where OWNER='SYSADM' and TABLE_NAME not in (select table_name from
dba_tab_privs where grantee='HYP');
select 'GRANT SELECT on '||VIEW_NAME|| ' to HYP;' from
DBA_VIEWS where OWNER='SYSADM' and VIEW_NAME not in (select table_name from
dba_tab_privs where grantee='HYP');
select 'spool off;' from dual;
spool off;
running_AE.sql
set pages 500
set lines 145
column LOGON_TIME format a24
SELECT
r.prcsinstance, h.module, h.action, sum(1) ash_secs
FROM v$active_Session_history h
, psprcsque q
, psprcsrqst r
WHERE r.prcsinstance = q.prcsinstance
AND h.module = 'PSAE.'||q.prcsname||'.'||q.sessionidnum
AND h.sample_time BETWEEN r.begindttm AND
NVL(r.enddttm,SYSDATE)
and r.prcsinstance = 867
GROUP BY r.prcsinstance, h.module, h.action
ORDER BY ash_secs DESC;
You can check whether or not your tables are histogramed by
looking in DBA_TAB_COL_STATISTICS:
SELECT OWNER, TABLE_NAME, HISTOGRAM, NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS;
To Generate Details database reports(IBM Script)
set lines 300 pages 50000 trims on feed off termout off
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
col rep_name new_value rep_name
select 'dbreport_' || instance_name || '_' ||
to_char(sysdate,'DDMONYYYY_HH24MI') || '.html' rep_name from v$instance;
set termout on
prompt
prompt This script will generate &rep_name File.
prompt
prompt Please wait...
set termout off
spool &rep_name
prompt <style type='text/css'>
prompt body {font:10pt Arial,Helvetica,sans-serif;
color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt;
vertical-align:top;}
prompt p {font:14pt Arial,Helvetica,sans-serif;
color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt;
vertical-align:top;}
prompt table,tr,td {font:10pt Arial,Helvetica,sans-serif;
color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px
0px;}
prompt th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699;
background:#cccc99; padding:0px 0px 0px 0px;}
prompt h1 {font:18pt Arial,Helvetica,Geneva,sans-serif;
color:#336699; background-color:White; border-bottom:1px solid #cccc99;
prompt margin-top:0pt;
margin-bottom:0pt; padding:0px 0px 0px 0px;}
prompt h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;
color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;}
prompt a {font:9pt Arial,Helvetica,sans-serif;
color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
prompt </style><title>SQL*Plus
Report</title>
prompt <BR><BR><H1><center>Database
Information Report</center></H1>
prompt <HR>
set head off pages 0;
select 'Report Generated on : ' ||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;
select '<p>' from dual;
set head on pages 50000;
set markup HTML on HEAD "<title>SQL*Plus
Report</title>" BODY "BGCOLOR=RED" TABLE "border='1'
align='center'" SPOOL OFF ENTMAP ON PREFORMAT OFF
Prompt
prompt Server Details
Prompt
select HOST_NAME "Host Name",(select value from
v$osstat where STAT_NAME like 'NUM_CPU_CORES') "Cores Sockets",
(select value from v$osstat where STAT_NAME like 'NUM_CPUS')
"CPUs",
(select value/1024/1024/1024 from v$osstat where STAT_NAME
like 'PHYSICAL_MEMORY_BYTES') "Memory(GB)" from v$instance;
Prompt
Prompt Database Details
Prompt
select name db_name, log_mode, database_role,
LPAD(force_logging,10) force_logging,
LPAD(supplemental_log_data_min,10)
SupLog_Min,
lpad(supplemental_log_data_pk,10)
SUPLOG_PK,
LPAD(supplemental_log_data_ui,10)
SUPLOG_UI,
LPAD(supplemental_log_data_fk,10)
SUPLOG_FK,
LPAD(supplemental_log_data_all,10)
"SUPLOG_ALL",
to_char(created,
'MM-DD-YYYY HH24:MI:SS') Created,
PLATFORM_NAME,
FLASHBACK_ON
from v$database;
Prompt
Prompt Instance Details
Prompt
select instance_number inst_no, instance_name, host_name,
startup_time, thread#, database_status from gv$instance;
Prompt
Prompt Database Properties
Prompt
SELECT property_name,
property_value
FROM
database_properties
ORDER BY property_name;
Prompt
Prompt Database Version Details
Prompt
select banner "Database Component Details" from
v$version;
Prompt
Prompt Database Features already being used
Prompt
SELECT NAME, DETECTED_USAGES, CURRENTLY_USED,
FIRST_USAGE_DATE
FROM DBA_FEATURE_USAGE_STATISTICS
WHERE VERSION = (SELECT VERSION FROM V$INSTANCE) AND
(DETECTED_USAGES > 0 OR CURRENTLY_USED != 'FALSE');
Prompt
Prompt Object Count by Schema
Prompt
select owner, count(*) from dba_objects
where owner not in ('SYS','SYSTEM','OUTLN','DBSNMP')
group by owner
order by owner;
Prompt
Prompt Object Count by Schema, Object Type
Prompt
select owner, object_type, count(*) objects from dba_objects
where owner not in ('SYS','SYSTEM','OUTLN','DBSNMP')
group by owner, object_type
order by owner, object_type;
Prompt
Prompt Control File Details
Prompt
select name from sys.v$controlfile;
Prompt
Prompt Redo Log File Locations
Prompt
select a.group#,b.thread#,a.member,b.bytes/1024/1024 as
size_mb
from sys.gv_$logfile
a,
v$log b
where a.group#=b.group#;
Prompt
Prompt Data Files Details
Prompt
select F.file_id "FILE#",
F.tablespace_name tablespace,
F.file_name,
F.bytes/(1024*1024) size_mb,
decode(F.status,'AVAILABLE','OK',F.status) status
from
sys.dba_data_files F
order by tablespace_name, FILE#;
Prompt
Prompt Tablespace Usage
Prompt
select a.tablespace_name,
round(nvl(a.asize,0)) "TOTAL",
round(nvl(a.asize-nvl(f.free,0),0)) "USED",
round(nvl(f.free,0))
"FREE",
round(((nvl(a.asize-f.free,0))/a.asize)*100) "USED %",
round((nvl(f.free,0)/a.asize)*100) "FREE %"
from (select
tablespace_name, sum(bytes)/1024/1024 "ASIZE"
from dba_data_files group by tablespace_name) a,
(select
tablespace_name, round(sum(bytes/(1024*1024))) free,round(max(bytes)/1024/1024)
maxfree
from dba_free_space
group by
tablespace_name) f
WHERE
a.tablespace_name = f.tablespace_name(+)
order by round((nvl(a.asize-f.free,0)/a.asize)*100);
Prompt
Prompt Redo Generation per Day
Prompt
select trunc(completion_time) rundate, inst_id
"instance", count(*) logswitch,
round((sum(blocks*block_size)/1024/1024)) redo_per_day
from gv$archived_log
where trunc(completion_time) >=
to_date(to_char(sysdate,'dd-mon-rr'),'dd-mon-rr') - 7
group by trunc(completion_time), inst_id
order by 1;
Prompt
Prompt Debug Enabled Objects
Prompt
select OWNER,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME from
sys.ALL_PROBE_OBJECTS where debuginfo='T';
Prompt
Prompt Resource Utilization
Prompt
select * from gv$resource_limit;
Prompt
Prompt Tables with CLOB,BLOB,LONG,NCLOB or LONG RAW Columns
Prompt
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM all_tab_columns
WHERE OWNER not in ('SYS', 'SYSTEM',
'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
AND data_type in ('CLOB', 'BLOB', 'LONG', 'LONG RAW',
'NCLOB', 'LOB')
ORDER BY OWNER;
Prompt
Prompt Initialization parameters having non-default values
Prompt
select name, value from v$parameter where ISDEFAULT <>
'TRUE' order by name;
Prompt
Prompt RMAN backup information
Prompt
select DISTINCT c.name, decode(BACKUP_TYPE,'D','DB INCR
BACKUP LEVEL 0','I','DB INCR BACKUP LEVEL 1','L','ARCHIVELOG
BACKUP',backup_type) backup1,
decode(b.status,'COMPLETED','SUCCESSFUL','FAILED','FAILED','RUNNING','RUNNING','COMPLETED
WITH WARNINGS') status,a.DEVICE_TYPE DEVICE_TYPE,
B.START_TIME,
B.end_time, b.TIME_TAKEN_DISPLAY elapse_time, (case WHEN backup_type = 'L' then
round((sum(original_input_bytes) over(Partition by
a.session_stamp,b.start_time,
command_id,a.session_recid
order by (b.start_time)))/1024/1024,1) ELSE NULL END) al_INPUT_BYTES_mb,
(case
WHEN backup_type in('D','I') then round((sum(original_input_bytes)
over(Partition by a.session_stamp,b.start_time,command_id,
a.session_recid
order by (b.start_time)))/1024/1024,1) ELSE NULL END) db_INPUT_BYTES_mb, round((sum(a.output_bytes)
over(partition by
a.session_stamp,b.start_time,command_id,a.session_recid
order by (b.start_time)))/1024/1024,2) output_bytes_mb
from V$BACKUP_SET_DETAILS
a,
V$rman_backup_job_details
b,
V$database
c
where a.session_recid=b.session_recid
and a.session_stamp=b.session_stamp
order by B.start_time
desc;
set markup html off;
SET PAGES 0 HEAD OFF FEED OFF;
select '<BR><BR>' FROM DUAL;
SET PAGES 50000 HEAD ON FEED ON;
spool off;
set termout on
prompt Done.
prompt Please check &rep_name file for errors, if any.
Prompt
Session lock find query
######Check Session Lock##########
Abghr#987!
select c.owner, c.object_name, c.object_type,b.sid,
b.serial#, b.status, b.osuser, b.machine from v$locked_object a ,v$session b,
dba_objects c;
SELECT a.sid,a.serial#,
a.username,c.os_user_name,a.terminal, b.object_id,substr(b.object_name,1,40)
object_name
from v$session a, dba_objects b, v$locked_object c where
a.sid = c.session_id and b.object_id = c.object_id;
ALTER SYSTEM KILL SESSION 'sid,serial#';
alter system kill session '28,46634';
alter system kill session '21,63485';
#########Database segment space ###########
SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE
SEGMENT_TYPE='TABLE';
######### PRCS Queue procss
##########3
select count(*) from psprcsrqst where runstatus<>'9';
delete from psprcsrqst where runstatus<>'9';
##########SYSADM SCHEMA REFRESH #################
Step 1:
Export SYSADM schema from host database
Copy .dmp file to target server
Step 2:
If SYSADM in target database contains objects, remove them:
> sqlplus sysadm/<password>
SQL> set heading off
SQL> set feedback off
SQL> set echo off
SQL> spool <name>.sql
SQL> select 'drop '||object_type||'
'||object_name||decode(object_type,'TABLE',' cascade constraints;',';')
from user_objects
where object_type in
('FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','TABLE','VIEW', 'INDEX',
'TRIGGER');
Edit <name>.sql and remove the non-drop statements at
the beginning and end of the file. Log onto the database as sysadm once again
and run the sql. Clean up by purging the recyclebin:
SQL> @<name>.sql
...
SQL> connect sys as sysdba
<password>
SQL> purge dba_recyclebin;
Step 3:
SYSADM should now contain no objects. Log onto grid control
and import from the .dmp file, or manually import using imp command
#################################################
Now, lets create a session as a DBA User to monitor the
system, this query will tell the locking and waiting SIDs.
SELECT vh.sid locking_sid,vs.status status,vs.program
program_holding,vw.sid waiter_sid,vsw.program program_waiting
FROM
v$lock vh,v$lock vw, v$session vs,v$session vsw WHERE (vh.id1, vh.id2) IN
(SELECT id1, id2 FROM v$lock WHERE request = 0 INTERSECT SELECT id1, id2 FROM
v$lock WHERE lmode = 0) AND vh.id1 = vw.id1 AND vh.id2 = vw.id2 AND vh.request
= 0 AND vw.lmode = 0 AND vh.sid = vs.sid
AND vw.sid = vsw.sid;
To Create A User Profile
SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
2> FROM DBA_TAB_PRIVS
3> WHERE TABLE_NAME = 'EMPLOYEES' and OWNER =
'HR';
select * from dba_sys_privs where grantee='CONNECT';
select * from dba_sys_privs where grantee='RESOURCE';
SQL> select * from dba_sys_privs where grantee='CONNECT';
GRANTEE
PRIVILEGE
ADM
---------- ---------------------------------------- ---
CONNECT CREATE
VIEW NO
CONNECT CREATE
TABLE NO
CONNECT ALTER
SESSION NO
CONNECT CREATE
CLUSTER NO
CONNECT CREATE
SESSION NO
CONNECT CREATE
SYNONYM NO
CONNECT CREATE
SEQUENCE NO
CONNECT CREATE
DATABASE LINK NO
8 rows selected.
SQL> select * from dba_sys_privs where
grantee='RESOURCE';
GRANTEE
PRIVILEGE
ADM
---------- ---------------------------------------- ---
RESOURCE CREATE
TYPE NO
RESOURCE CREATE
TABLE NO
RESOURCE CREATE
CLUSTER NO
RESOURCE CREATE
TRIGGER NO
RESOURCE CREATE
OPERATOR NO
RESOURCE CREATE
SEQUENCE NO
RESOURCE CREATE
INDEXTYPE NO
RESOURCE CREATE
PROCEDURE NO
8 rows selected.
create profile
BOIDEVELOPERS
limit
sessions_per_user
1
connect_time unlimited
idle_time 20
logical_reads_per_session
default
logical_reads_per_call
default
failed_login_attempts
3
password_life_time 30
password_reuse_time
12
password_reuse_max
unlimited
password_lock_time
default
password_grace_time
2
;
create user BOI12 identified by BOI12
PASSWORD EXPIRE
PROFILE BOIDEVELOPERS
DEFAULT TABLESPACE
PSDEFAULT
TEMPORARY TABLESPACE PSTEMP ;
Refresh Database Steps:
Steps to Refresh HRSTAGE from HRPROD COLD Backup
++++++++++++++++++++++++++++++++++++++++++++++++++
export ORACLE_SID=HRSTAGE
1. Shutdown HRSTAGE instance
2. Delete all physical files(datafiles, controlfile and
redolog)
3. Transfer controlfile trace and the datafiles to the
remote host using FTP/SFTP
4. Make necessary changes in the controlfile(DB name and
datafile/logfile locations)
Eg:-
CREATE CONTROLFILE RESET DATABASE "HRSTAGE"
RESETLOGS NOARCHIVELOG
-- SET STANDBY TO
MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 300
MAXINSTANCES 1
MAXLOGHISTORY 6806
LOGFILE
GROUP 1 (
'/u01/oradata/HRSTAGE/redo01.log'
) SIZE 100M,
GROUP 2 (
'/u01/oradata/HRSTAGE/redo02.log'
) SIZE 100M,
GROUP 3 (
'/u01/oradata/HRSTAGE/redo03.log'
) SIZE 100M
DATAFILE
'/u01/oradata/HRSTAGE/system01.dbf',
'/u01/oradata/HRSTAGE/undotbs01.dbf',
'/u01/oradata/HRSTAGE/cwmlite01.dbf',
'/u01/oradata/HRSTAGE/drsys01.dbf',
'/u01/oradata/HRSTAGE/example01.dbf',
'/u01/oradata/HRSTAGE/indx01.dbf',
'/u01/oradata/HRSTAGE/odm01.dbf',
'/u01/oradata/HRSTAGE/tools01.dbf',
'/u01/oradata/HRSTAGE/users01.dbf',
'/u01/oradata/HRSTAGE/xdb01.dbf',
'/u01/oradata/HRSTAGE/FAAPP.DBF',
'/u01/oradata/HRSTAGE/GPAPP.DBF',
'/u01/oradata/HRSTAGE/HRAPP.DBF',
'/u01/oradata/HRSTAGE/PTTBL.DBF',
'/u01/oradata/HRSTAGE/SAAPP.DBF',
'/u01/oradata/HRSTAGE/PTTLRG.DBF',
'/u01/oradata/HRSTAGE/PY0LRG.DBF',
'/u01/oradata/HRSTAGE/TLWORK.DBF',
'/u01/oradata/HRSTAGE/HRLARGE.DBF',
'/u01/oradata/HRSTAGE/PSIMAGE.DBF',
'/u01/oradata/HRSTAGE/PSINDEX.DBF',
'/u01/oradata/HRSTAGE/PSDEFAULT.DBF',
'/u02/oradata/HRSTAGE/PSUNDOTS.DBF',
'/u02/oradata/HRSTAGE/PSUNDOTS01.DBF',
'/u01/oradata/HRSTAGE/AAAPP.DBF',
'/u01/oradata/HRSTAGE/ADAPP.DBF',
'/u01/oradata/HRSTAGE/AMAPP.DBF',
'/u01/oradata/HRSTAGE/AVAPP.DBF',
'/u01/oradata/HRSTAGE/BDAPP.DBF',
'/u01/oradata/HRSTAGE/BNAPP.DBF',
'/u01/oradata/HRSTAGE/CCAPP.DBF',
'/u01/oradata/HRSTAGE/COAPP.DBF',
'/u01/oradata/HRSTAGE/DIAPP.DBF',
'/u01/oradata/HRSTAGE/DTAPP.DBF',
'/u01/oradata/HRSTAGE/EOAPP.DBF',
'/u01/oradata/HRSTAGE/EPAPP.DBF',
'/u01/oradata/HRSTAGE/ERAPP.DBF',
'/u01/oradata/HRSTAGE/FGAPP.DBF',
'/u01/oradata/HRSTAGE/FSAPP.DBF',
'/u01/oradata/HRSTAGE/GIAPP.DBF',
'/u01/oradata/HRSTAGE/HPAPP.DBF',
'/u01/oradata/HRSTAGE/HTAPP.DBF',
'/u01/oradata/HRSTAGE/INAPP.DBF',
'/u01/oradata/HRSTAGE/PAAPP.DBF',
'/u01/oradata/HRSTAGE/PCAPP.DBF',
'/u01/oradata/HRSTAGE/PIAPP.DBF',
'/u01/oradata/HRSTAGE/POAPP.DBF',
'/u01/oradata/HRSTAGE/PTAPP.DBF',
'/u01/oradata/HRSTAGE/PTPRC.DBF',
'/u01/oradata/HRSTAGE/PVAPP.DBF',
'/u01/oradata/HRSTAGE/PYAPP.DBF',
'/u01/oradata/HRSTAGE/SRAPP.DBF',
'/u01/oradata/HRSTAGE/STAPP.DBF',
'/u01/oradata/HRSTAGE/TLAPP.DBF',
'/u01/oradata/HRSTAGE/WAAPP.DBF',
'/u01/oradata/HRSTAGE/ERWORK.DBF',
'/u01/oradata/HRSTAGE/HRAPP1.DBF',
'/u01/oradata/HRSTAGE/HRAPP2.DBF',
'/u01/oradata/HRSTAGE/HRAPP3.DBF',
'/u01/oradata/HRSTAGE/HRAPP4.DBF',
'/u01/oradata/HRSTAGE/HRAPP5.DBF',
'/u01/oradata/HRSTAGE/HRAPP6.DBF',
'/u01/oradata/HRSTAGE/HRAPP7.DBF',
'/u01/oradata/HRSTAGE/HRSAPP.DBF',
'/u01/oradata/HRSTAGE/HRWORK.DBF',
'/u01/oradata/HRSTAGE/PIWORK.DBF',
'/u01/oradata/HRSTAGE/PSIMGR.DBF',
'/u01/oradata/HRSTAGE/PTAMSG.DBF',
'/u01/oradata/HRSTAGE/PTAPPE.DBF',
'/u01/oradata/HRSTAGE/PTLOCK.DBF',
'/u01/oradata/HRSTAGE/PTRPTS.DBF',
'/u01/oradata/HRSTAGE/PTTREE.DBF',
'/u01/oradata/HRSTAGE/PTWORK.DBF',
'/u01/oradata/HRSTAGE/PYWORK.DBF',
'/u01/oradata/HRSTAGE/SACAPP.DBF',
'/u01/oradata/HRSTAGE/STWORK.DBF',
'/u01/oradata/HRSTAGE/AALARGE.DBF',
'/u01/oradata/HRSTAGE/BNLARGE.DBF',
'/u01/oradata/HRSTAGE/CUAUDIT.DBF',
'/u01/oradata/HRSTAGE/CULARG1.DBF',
'/u01/oradata/HRSTAGE/CULARG2.DBF',
'/u01/oradata/HRSTAGE/CULARG3.DBF',
'/u01/oradata/HRSTAGE/CULARGE.DBF',
'/u01/oradata/HRSTAGE/EOBFAPP.DBF',
'/u01/oradata/HRSTAGE/EOCFAPP.DBF',
'/u01/oradata/HRSTAGE/EOCMAPP.DBF',
'/u01/oradata/HRSTAGE/EOCMLRG.DBF',
'/u01/oradata/HRSTAGE/EOCMWRK.DBF',
'/u01/oradata/HRSTAGE/EOCUAPP.DBF',
'/u01/oradata/HRSTAGE/EOCULRG.DBF',
'/u01/oradata/HRSTAGE/EODSAPP.DBF',
'/u01/oradata/HRSTAGE/EODSLRG.DBF',
'/u01/oradata/HRSTAGE/EOECAPP.DBF',
'/u01/oradata/HRSTAGE/EOECLRG.DBF',
'/u01/oradata/HRSTAGE/EOECWRK.DBF',
'/u01/oradata/HRSTAGE/EOEIAPP.DBF',
'/u01/oradata/HRSTAGE/EOEILRG.DBF',
'/u01/oradata/HRSTAGE/EOEWAPP.DBF',
'/u01/oradata/HRSTAGE/EOEWLRG.DBF',
'/u01/oradata/HRSTAGE/EOEWWRK.DBF',
'/u01/oradata/HRSTAGE/EOIUAPP.DBF',
'/u01/oradata/HRSTAGE/EOIULRG.DBF',
'/u01/oradata/HRSTAGE/EOIUWRK.DBF',
'/u01/oradata/HRSTAGE/EOLARGE.DBF',
'/u01/oradata/HRSTAGE/EOLTAPP.DBF',
'/u01/oradata/HRSTAGE/EOPPAPP.DBF',
'/u01/oradata/HRSTAGE/EOPPLRG.DBF',
'/u01/oradata/HRSTAGE/EOTPAPP.DBF',
'/u01/oradata/HRSTAGE/EOTPLRG.DBF',
'/u01/oradata/HRSTAGE/EPLARGE.DBF',
'/u01/oradata/HRSTAGE/ERLARGE.DBF',
'/u01/oradata/HRSTAGE/FALARGE.DBF',
'/u01/oradata/HRSTAGE/FGLARGE.DBF',
'/u01/oradata/HRSTAGE/GPDEAPP.DBF',
'/u01/oradata/HRSTAGE/HRIMAGE.DBF',
'/u01/oradata/HRSTAGE/HRLARG1.DBF',
'/u01/oradata/HRSTAGE/HRSARCH.DBF',
'/u01/oradata/HRSTAGE/HRSWORK.DBF',
'/u01/oradata/HRSTAGE/PALARGE.DBF',
'/u01/oradata/HRSTAGE/PCLARGE.DBF',
'/u01/oradata/HRSTAGE/PILARGE.DBF',
'/u01/oradata/HRSTAGE/PTAUDIT.DBF',
'/u01/oradata/HRSTAGE/PTPRJWK.DBF',
'/u01/oradata/HRSTAGE/PYLARGE.DBF',
'/u01/oradata/HRSTAGE/SALARGE.DBF',
'/u01/oradata/HRSTAGE/STLARGE.DBF',
'/u01/oradata/HRSTAGE/TLLARGE.DBF',
'/u01/oradata/HRSTAGE/HRSLARGE.DBF',
'/u01/oradata/HRSTAGE/PERFSTAT.DBF',
'/u01/oradata/HRSTAGE/PTCMSTAR.DBF',
'/u01/oradata/HRSTAGE/GPAPP01.DBF',
'/u01/oradata/HRSTAGE/PSINDEX01.DBF',
'/u01/oradata/HRSTAGE/PSINDEX02.DBF',
'/u02/oradata/HRSTAGE/psindex03.dbf',
'/u02/oradata/HRSTAGE/GPAPP02.DBF',
'/u01/oradata/HRSTAGE/PSIMAGE01.DBF',
'/u01/oradata/HRSTAGE/GPAPP03.DBF',
'/u01/oradata/HRSTAGE/PSINDEX04.DBF'
CHARACTER SET WE8ISO8859P15
;
5. startup nomount
6. create controlfile using...
@/u01/oradata/controlfile_new.trc
7. alter database open resetlogs;
8. Change the DBNAME in PSDBOWNER table n SYSADM password
update
psdbowner set DBNAME='DUMP';
commit;
alter
user sysadm identified by hrstage;
9. change the SYSADM password by connecting through data
mover
change_access_password
SYSADM1 hrstage;
10. change PPS password
update psoprdefn set operpswd='hrstage',encrypted=0 where
oprid='PPS';
encrypt_password PPS
Remove the cache from appserv;
Run prcsclr.dms through data mover
Run rptclr.dms
11. startup App/Prcssceduler
To update all users password or keep user name and password
same :
update psoprdefn set operpswd='password123',encrypted=0;
encrypt_password *;
update psoprdefn set operpswd='password123',encrypted=0;
encrypt_password *;
new password : password123
124443
100446
update psoprdefn set operpswd='PS',encrypted=0 where oprid='PS';
encrypt_password PS;
update psoprdefn set operpswd='select oprid from
psoprdefn',encrypted=0 where oprid='*' ;
encrypt_password *;
commit;
No comments:
Post a Comment