Oracle Peoplesoft DBA (Prasanth Bhaskaran): July 2014

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;

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

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;

Oracle Peoplesoft DBA (Prasanth Bhaskaran)

Tuesday, July 22, 2014

Sunday, July 20, 2014

PS Database queries