Tuesday, June 28, 2016
Monday, June 27, 2016
DATA PUMP
Datapump in Oracle
------------------
Oracle Data Pump is a newer, faster and more flexible alternative to the "exp" and "imp" utilities used in previous Oracle versions.
The Oracle Data Pump is a feature of Oracle 10g and later databases that enables very fast bulk data and metadata movement between Oracle databases.
The Oracle Data Pump provides new high-speed, parallel Export and Import utilities (expdp and impdp).
Data Pump Export and Import utilities are faster than the original Export and Import Utilities.
A single thread of Data Pump Export is about twice as fast as original Export, while Data Pump Import is several times faster than original Import.
The list of features for both the Data Pump import and export can be displayed using the HELP parameter.
expdp system/password help=y
Requirements
------------
Create Directory Object
-----------------------
CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY TO ;
Ensure Oracle user performing Data Pump operation has adequate permissions.
It is common to include a meaningful job name for all Data Pump operations so as a job can be ideally managed.
Export PUMP
------------
expdp user_name/password
DIRECTORY=dir_obj_name
DUMPFILE=dump_file_name
FULL=y|n
JOB_NAME=job_name
[COMPRESSION=type]
expdp system/password DUMPFILE=db1.dmp DIRECTORY=datapump FULL=y JOB_NAME=expDB LOGFILE=exportDatabase.log
Import
------
impdp user_name/password
DIRECTORY=dir_obj_name
DUMPFILE=dump_file_name
FULL=y|n
JOB_NAME=job_name
impdp system/password DUMPFILE=db1.dmp DIRECTORY=datapump FULL=y JOB_NAME=impDB LOGFILE=impDatabase.log
Parameter File (.par)
---------------------
Export
------
# expdp system/password PARFILE=expDatabase.par COMPRESSION=ALL
DIRECTORY=datapump
DUMPFILE=db1.dmp
FULL=Y
JOB_NAME=expDB
LOGFILE=expDatabase.log
Import
------
# impdp system/password PARFILE=impDatabase.par
DIRECTORY=datapump
DUMPFILE=db1.dmp
FULL=Y
JOB_NAME=impDB
LOGFILE=impDatabase.log
TABLE_EXISTS_ACTION=replace
Killing a Data Pump Job
----------------------
Standard Method
--------------
Determine the active Data Pump job name.
Attach to the Data Pump console using the identified jobname.
Issue the command to kill job.
SQL> SELECT j.owner_name, j.job_name,
j.job_mode, j.state, s.session_type, s.saddr
FROM dba_datapump_jobs j,dba_datapump_sessions s
WHERE UPPER(j.job_name) = UPPER(s.job_name);
Owner JOB_NAME JOB_MODE STATE SESSION_TYPE SADDR
--------- --------------- ------------ ------------ --------------- --------
SYSTEM xSCHEMA SCHEMA EXECUTING WORKER AD09C2D8
SYSTEM xSCHEMA SCHEMA EXECUTING MASTER AD09E8A8
SYSTEM xSCHEMA SCHEMA EXECUTING DBMS_DATAPUMP AD0BFA08
OS> expdp system/password attach=EXPORTSCHEMA
Export> kill_job
Export> Are you sure you wish to stop this job ([yes]/no): yes
If this is an import then use impdp correspondingly.
Advanced Method
---------------
If the standard method does not work and the job is no longer active.
Log into SQLPlus as SYS.
Drop the corresponding job table.
DROP TABLE.;
OS> sqlplus sys/password as sysdba
SQL> DROP TABLE SYSTEM.EXPORTSCHEMA;
------------------
Oracle Data Pump is a newer, faster and more flexible alternative to the "exp" and "imp" utilities used in previous Oracle versions.
The Oracle Data Pump is a feature of Oracle 10g and later databases that enables very fast bulk data and metadata movement between Oracle databases.
The Oracle Data Pump provides new high-speed, parallel Export and Import utilities (expdp and impdp).
Data Pump Export and Import utilities are faster than the original Export and Import Utilities.
A single thread of Data Pump Export is about twice as fast as original Export, while Data Pump Import is several times faster than original Import.
The list of features for both the Data Pump import and export can be displayed using the HELP parameter.
expdp system/password help=y
Requirements
------------
Create Directory Object
-----------------------
CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY
Ensure Oracle user performing Data Pump operation has adequate permissions.
It is common to include a meaningful job name for all Data Pump operations so as a job can be ideally managed.
Export PUMP
------------
expdp user_name/password
DIRECTORY=dir_obj_name
DUMPFILE=dump_file_name
FULL=y|n
JOB_NAME=job_name
[COMPRESSION=type]
expdp system/password DUMPFILE=db1.dmp DIRECTORY=datapump FULL=y JOB_NAME=expDB LOGFILE=exportDatabase.log
Import
------
impdp user_name/password
DIRECTORY=dir_obj_name
DUMPFILE=dump_file_name
FULL=y|n
JOB_NAME=job_name
impdp system/password DUMPFILE=db1.dmp DIRECTORY=datapump FULL=y JOB_NAME=impDB LOGFILE=impDatabase.log
Parameter File (.par)
---------------------
Export
------
# expdp system/password PARFILE=expDatabase.par COMPRESSION=ALL
DIRECTORY=datapump
DUMPFILE=db1.dmp
FULL=Y
JOB_NAME=expDB
LOGFILE=expDatabase.log
Import
------
# impdp system/password PARFILE=impDatabase.par
DIRECTORY=datapump
DUMPFILE=db1.dmp
FULL=Y
JOB_NAME=impDB
LOGFILE=impDatabase.log
TABLE_EXISTS_ACTION=replace
Killing a Data Pump Job
----------------------
Standard Method
--------------
Determine the active Data Pump job name.
Attach to the Data Pump console using the identified jobname.
Issue the command to kill job.
SQL> SELECT j.owner_name, j.job_name,
j.job_mode, j.state, s.session_type, s.saddr
FROM dba_datapump_jobs j,dba_datapump_sessions s
WHERE UPPER(j.job_name) = UPPER(s.job_name);
Owner JOB_NAME JOB_MODE STATE SESSION_TYPE SADDR
--------- --------------- ------------ ------------ --------------- --------
SYSTEM xSCHEMA SCHEMA EXECUTING WORKER AD09C2D8
SYSTEM xSCHEMA SCHEMA EXECUTING MASTER AD09E8A8
SYSTEM xSCHEMA SCHEMA EXECUTING DBMS_DATAPUMP AD0BFA08
OS> expdp system/password attach=EXPORTSCHEMA
Export> kill_job
Export> Are you sure you wish to stop this job ([yes]/no): yes
If this is an import then use impdp correspondingly.
Advanced Method
---------------
If the standard method does not work and the job is no longer active.
Log into SQLPlus as SYS.
Drop the corresponding job table.
DROP TABLE
OS> sqlplus sys/password as sysdba
SQL> DROP TABLE SYSTEM.EXPORTSCHEMA;
Saturday, June 25, 2016
Instance Status
set linesize 1000
col strtd hea 'STARTED' format a20
col instance_name for a8 hea 'INSTANCE'
col host_name for a15 hea 'HOSTNAME'
col version for a10
select instance_name, version, host_name, status
, database_status, to_char(startup_time,'DD-MON-YYYY HH:MI:SS') strtd
from v$instance;
col inst_id for 9
col instance_name format a7
col host_name format a20
col version format a10
col thread# format 9
col database_status for a10
col linesize 180
select inst_id,instance_name,host_name,version,thread#,database_status from gv$instance;
---------------------------------------------------------------------------------------------------
--Use GV$INSTANCE for RAC Database
select instance_name, version, host_name, status
, database_status,to_char(startup_time,'DD-MON-YYYY HH:MI:SS') strtd
from gv$instance;
--Or use V$THREAD
select instance, status, enabled, open_time, checkpoint_time
from v$thread;
col strtd hea 'STARTED' format a20
col instance_name for a8 hea 'INSTANCE'
col host_name for a15 hea 'HOSTNAME'
col version for a10
select instance_name, version, host_name, status
, database_status, to_char(startup_time,'DD-MON-YYYY HH:MI:SS') strtd
from v$instance;
col inst_id for 9
col instance_name format a7
col host_name format a20
col version format a10
col thread# format 9
col database_status for a10
col linesize 180
select inst_id,instance_name,host_name,version,thread#,database_status from gv$instance;
---------------------------------------------------------------------------------------------------
--Use GV$INSTANCE for RAC Database
select instance_name, version, host_name, status
, database_status,to_char(startup_time,'DD-MON-YYYY HH:MI:SS') strtd
from gv$instance;
--Or use V$THREAD
select instance, status, enabled, open_time, checkpoint_time
from v$thread;
What Type of Index
set wrap off linesize 132 pages 80;
column pos format 990;
column col format a10;
column index format a25;
column table format a25;
column type format a20;
column tablespace format a25;
select t.table_name "Table",decode(t.index_type,'NORMAL','BTree','BITMAP','Bitmap' ,'FUNCTION-BASED NORMAL','Function-Based BTree',t.index_type) "Type"
,t.index_name "Index",c.column_name "Col", c.column_position "Pos"
,t.tablespace_name "Tablespace"
from user_indexes t, user_ind_columns c
where t.table_name = c.table_name
and t.index_name = c.index_name
order by t.index_type desc, t.table_name, t.index_name, c.column_position;
column pos format 990;
column col format a10;
column index format a25;
column table format a25;
column type format a20;
column tablespace format a25;
select t.table_name "Table",decode(t.index_type,'NORMAL','BTree','BITMAP','Bitmap' ,'FUNCTION-BASED NORMAL','Function-Based BTree',t.index_type) "Type"
,t.index_name "Index",c.column_name "Col", c.column_position "Pos"
,t.tablespace_name "Tablespace"
from user_indexes t, user_ind_columns c
where t.table_name = c.table_name
and t.index_name = c.index_name
order by t.index_type desc, t.table_name, t.index_name, c.column_position;
find indexed table-columns
SELECT
a.owner,
a.table_name,
b.index_name,
b.column_name
FROM dba_tables a
LEFT JOIN dba_ind_columns b
ON a.table_name=b.table_name
WHERE a.owner ='&owner' and a.table_name='&table_name'
ORDER BY a.table_name,b.index_name,b.column_name;
a.owner,
a.table_name,
b.index_name,
b.column_name
FROM dba_tables a
LEFT JOIN dba_ind_columns b
ON a.table_name=b.table_name
WHERE a.owner ='&owner' and a.table_name='&table_name'
ORDER BY a.table_name,b.index_name,b.column_name;
Index Checking
set linesize 125
set pages 1000
col c1 hea "TABLE NAME" format a20
col c2 hea "INDEX NAME" format a20
col c3 hea "INDEX TYPE" format a10
col c4 hea "UNIQUENESS" format a10
col c5 hea "COLUMN NAME" format a15
col c6 hea "COLUMN|POSITION" format 99999999
col c7 hea "STATUS" format a10
col c8 hea "T.SPACE" format a12
col c9 hea "PRTN" format a5
break on report on c1 skip 1 on c2 on c3 on c4 on c5 on c6
select a.table_name c1,a.index_name c2,index_type c3,uniqueness
c4,column_name c5,column_position c6, status c7, tablespace_name c8,
partitioned c9
from user_ind_columns a,user_indexes b where a.table_name=upper('&table')
and a.index_name=b.index_name order by c1,c2,c6;
set pages 1000
col c1 hea "TABLE NAME" format a20
col c2 hea "INDEX NAME" format a20
col c3 hea "INDEX TYPE" format a10
col c4 hea "UNIQUENESS" format a10
col c5 hea "COLUMN NAME" format a15
col c6 hea "COLUMN|POSITION" format 99999999
col c7 hea "STATUS" format a10
col c8 hea "T.SPACE" format a12
col c9 hea "PRTN" format a5
break on report on c1 skip 1 on c2 on c3 on c4 on c5 on c6
select a.table_name c1,a.index_name c2,index_type c3,uniqueness
c4,column_name c5,column_position c6, status c7, tablespace_name c8,
partitioned c9
from user_ind_columns a,user_indexes b where a.table_name=upper('&table')
and a.index_name=b.index_name order by c1,c2,c6;
Kill Session
select 'alter system kill session '||''''||sid||','||serial#||''';'
from (select SID,s.SERIAL# from v$session s,v$process p,v$transaction t
where s.osuser is not null and
s.username = upper('&username') and
s.status =upper('&status') and
-- and logon_time < (sysdate-15/1440)
s.paddr = p.addr and
s.taddr = t.addr (+));
from (select SID,s.SERIAL# from v$session s,v$process p,v$transaction t
where s.osuser is not null and
s.username = upper('&username') and
s.status =upper('&status') and
-- and logon_time < (sysdate-15/1440)
s.paddr = p.addr and
s.taddr = t.addr (+));
Friday, June 24, 2016
ONLINE PATCHING CYCLE (ADOP)
Online Patching Cycle
---------------------------
ADPATCH utility is no longer used in R12.2.0 and is being replaced by ADOP – AD Online Patching. There are five phases or life cycles of ADOP which are:
3 Apply Phase -
adop phase=apply patches=, workers=
After patch has been successfully applied,complete the patch cycle
Oracle E-Business Suite Release 12.2 installation comes with a new feature called “Online Patching” with two editions (versions) of the application code and seed data. The file system contains two complete copies of the Oracle E-Business Suite and technology files. In the database, we use the Edition-based Redefinition feature to create a new database edition for each online patching cycle.
The "Run Edition" is the code and data used by the running application. As a developer, you will connect to the Run Edition whenever you are engaged in normal development activity on the system.
The "Patch Edition" is an alternate copy of Oracle E-Business Suite code and seed data that is updated by Online Patching.
The Oracle E-Business Suite application-tier files are installed in a root directory of the customer's choosing. Within that root directory you will now find three important sub-directories:
1) How to see, the RUN, PATCH & NON-EDITIONED Filesystems?
fs1 - file system 1 (either run or patch edition)
fs2 - file system 2 (alternate of file system 1)
fs_ne - non-editioned file system, for data files
By just setting the ebs environment, shows the filesystems.
[applmgr@punitchi:applmgr]> . /u02/applR12/PRODDB/fs1/EBSapps/EBSapps.env
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u02/applR12/PRODDB/fs1/EBSapps/appl
PATCH File System : /u02/applR12/PRODDB/fs2/EBSapps/appl
Non-Editioned File System : /u02/applR12/PRODDB/fs_ne
DB Host: punitc.lab.com Service/SID: PRODDB
Sourcing the RUN File System ...
[applmgr@punitchi:applmgr]>
2) How to identify the PATCH & RUN edition of filesystem?
[applmgr@punitchi:applmgr]>pwd
/u02/applR12/PRODDB/fs1/EBSapps/appl
[applmgr@punitchi:applmgr]> . EBSapps.env
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u02/applR12/PRODDB/fs1/EBSapps/appl
PATCH File System : /u02/applR12/PRODDB/fs2/EBSapps/appl
Non-Editioned File System : /u02/applR12/PRODDB/fs_ne
DB Host: punitchi Service/SID: PRODDB
E-Business Suite Environment Setting
------------------------------------
- Enter [R/r] for sourcing Run File System Environment file, or
- Enter [P/p] for sourcing Patch File System Environment file, or
- Enter anything else to exit
Please choose the environment file you wish to source [R/P]:R
Sourcing the RUN File System ...
[applmgr@punitchi:applmgr]>
3) How to connect to the PATCH & RUN edition of filesystem?
[applmgr@punitchi:applmgr]> . /u02/applR12/PRODDB/fs1/EBSapps.env run
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u02/applR12/PRODDB/fs1/EBSapps/appl
PATCH File System : /u02/applR12/PRODDB/fs1/EBSapps/appl
Non-Editioned File System : /u02/applR12/PRODDB/fs_ne
DB Host: punitc.lab.com Service/SID: punitchi
Sourcing the RUN File System ...
[applmgr@punitchi:applmgr]> echo $FILE_EDITION
run
[applmgr@punitchi:applmgr]>
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u02/applR12/PRODDB/fs1/EBSapps/appl
PATCH File System : /u02/applR12/PRODDB/fs2/EBSapps/appl
Non-Editioned File System : /u02/applR12/PRODDB/fs_ne
DB Host: punitchi.lab.com Service/SID: PRODDB
E-Business Suite Environment Setting
------------------------------------
- Enter [R/r] for sourcing Run File System Environment file, or
- Enter [P/p] for sourcing Patch File System Environment file, or
- Enter anything else to exit
Please choose the environment file you wish to source [R/P]:P
Sourcing the PATCH File System ...
[applmgr@punitchi:applmgr]> echo $FILE_EDITION
patch
[applmgr@punitchi:applmgr]>
4) How to display edition status ?
To help keep track of what environment and edition you are connected to, it can be helpful to set the TWO_TASK or FILE_EDITION environment variable as your shell prompt.
[applmgr@punitchi:applmgr]> . EBSapps.env patch
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u02/applR12/PRODDB/fs1/EBSapps/appl
PATCH File System : /u02/applR12/PRODDB/fs2/EBSapps/appl
Non-Editioned File System : /u02/applR12/PRODDB/fs_ne
DB Host: punitchi.lab.com Service/SID: PRODDB
E-Business Suite Environment Setting
------------------------------------
- Enter [R/r] for sourcing Run File System Environment file, or
- Enter [P/p] for sourcing Patch File System Environment file, or
- Enter anything else to exit
Please choose the environment file you wish to source [R/P]:P
Sourcing the PATCH File System ...
[applmgr@punitchi:applmgr]> PS1='$TWO_TASK> '
punitchi_patch>
5) How to find out whether a system is in an Online Patching cycle using the "adop -status" command.
[applmgr@punitchi:applmgr]> adop -status
Enter the APPS username: apps
Enter the APPS password:
Current Patching Session ID: 7
Node Name Node Type Phase Status Started Finished Elapsed
--------------- --------------- ----------- --------------- ------------------------------ ------------------------------ ------------
punitc master PREPARE COMPLETED 05-Jun-16 03:57:39 +00:00 06-Jun-16 04:55:57 +00:00 36:58:18
FINALIZE COMPLETED 06-Jun-16 06:09:01 +00:00 06-Jun-16 06:54:41 +00:00 0:45:40
CUTOVER COMPLETED 06-Jun-16 06:59:47 +00:00 06-Jun-16 07:26:32 +00:00 0:26:45
CLEANUP COMPLETED 06-Jun-16 11:26:07 +00:00 06-Jun-16 11:28:53 +00:00 0:02:46
APPLY COMPLETED
File System Synchronization Used in this Patching Cycle: Full
For more information, run ADOP Status Report by using -detail option
Generating ADOP Status Report at location: /u02/applR12/PRODDB/fs_ne/EBSapps/log/status_20160625_172245/adzdshowstatus.out
Please wait...
Done...!
adop exiting with status = 0 (Success)
6) How to find names and status of past and present database editions using the ADZDSHOWED.sql script.
The below lists the existing database editions and identifies the OLD, RUN, and PATCH editions.
[applmgr@punitchi:applmgr]> sqlplus apps/********* @ADZDSHOWED.sql
SQL*Plus: Release 10.1.0.5.0 - Production on Thu May 29 16:37:18 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
=========================================================================
= Editions
=========================================================================
Edition Name Type Status Current?
--------------- -------- -------- --------
ORA$BASE RETIRED
V_20140202_0749 OLD RETIRED
V_20140405_2132 RUN ACTIVE CURRENT
V_20140508_1528 PATCH ACTIVE
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[applmgr@punitchi:applmgr]>
7) How to change to the patch edition of database using SQL*Plus? /u02/applR12/PRODDB/fs1/EBSapps/appl/ad/12.0.0/sql
[applmgr@punitchi:applmgr]> sqlplus apps/********* @ADZDSHOWED.sql
SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jun 25 17:03:50 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> exec ad_zd.set_edition('PATCH');
8) What are the Tools and Scripts for Edition-based Development?
[applmgr@punitchi:applmgr]> . /u02/app/applmgr/120/punitchi/EBSapps.env run
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u02/app/applmgr/120/punitchi/fs1/EBSapps/appl
PATCH File System : /u02/app/applmgr/120/punitchi/fs2/EBSapps/appl
Non-Editioned File System : /u02/app/applmgr/120/punitchi/fs_ne
DB Host: punitc.lab.com Service/SID: punitchi
Sourcing the RUN File System ...
[applmgr@punitchi:applmgr]> which adop
/u02/applR12/PRODDB/fs1/EBSapps/appl/ad/bin/adop
[applmgr@punitchi:applmgr]> which xdfgen.pl
/u02/applR12/PRODDB/fs1/EBSapps/applfnd/12.0.0/bin/xdfgen.pl
[applmgr@punitchi:applmgr]> which xdfcmp.pl
/u02/applR12/PRODDB/fs1/EBSapps/appl/fnd/12.0.0/bin/xdfcmp.pl
[applmgr@punitchi:applmgr]>
9) Useful SQL*Plus scripts that provides information about the state of your editioned development environment. All ADZD* scripts are found under $AD_TOP/sql.
Add this directory to the SQLPATH environment variable so that you can refer to the scripts by simple name.
[applmgr@punitchi:applmgr]> SQLPATH=$AD_TOP/sql; export SQLPATH
[applmgr@punitchi:applmgr]> echo $SQLPATH
/u02/applR12/PRODDB/fs1/EBSapps/applad/12.0.0/sql
[applmgr@punitchi:applmgr]>
ADZDDBCC - database compliance checker, shows violations of the database object development standards documented in the Oracle E-Business Suite Developer's Guide, Part No. E22961. Warning: this script takes a long time to run.
ADZDSHOWED - Show database editions and current edition.
ADZDSHOWLOG - Show full diagnostic log for online patching infrastructure
ADZDSHOWLOGEVT - Show only event and error messages from online patching diagnostic log (a useful summary, without the detailed statement text).
ADZDSHOWLOGERR - Show only error messages from online patching diagnostic log.
ADZDSHOWEV TABLE_SYNONYM_NAME - Show editioning view column mapping for table.
ADZDSHOWTAB TABLE_SYNONYM_NAME - Show table information and related objects.
ADZDSHOWMV MVIEW_NAME - Show materialized view information and related objects.
ADZDSHOWTS - Show important tablespace status. Ensure that you have enough SYSTEM tablespace.
ADZDCMPED - Compare Patch Edition with Run Edition. Warning: this script may take a long time to run.
ADZDSHOWDDLS - Show stored DDL summary by phase.
ADZDALLDDLS - Show stored DDL statement text and status.
ADZDDDLERROR - Show stored DDL execution errors and messages.
adutlrcmp - Recompile all objects, with before/after status report. Warning: this script may take a long time to run.
The following scripts are for experts:
ADZDSHOWOBJS - Show Object Summary per edition. Counts of actual and stub (inherited) editioned object per edition.
ADZDSHOWAOBJS - Show Actual Objects in the current edition. These are the editioned objects that have been changed by the patch.
ADZDSHOWIOBJS - Show Inherited Objects in the current edition. These are the editioned objects that remain untouched in the Patch Edition.
ADZDSHOWCOBJS - Show Covered Object Summary per edition. Count of objects in old editions that have a replacement in the run edition.
ADZDSHOWCOBJX - Show Covered Object List. List of objects in old editions that have a replacement in the run edition.
ADZDSHOWSM - Show Seed Manager status.
ADZDSHOWTM - Show Table Manager status.
ADZDSHOWAD - AD (online patching) database object status
ADZDSHOWSES - Show sessions connected to the database (by edition).
ADZDSHOWDEP OBJECT_NAME - Show objects that OBJECT_NAME depends on.
ADZDSHOWDEPTREE OBJECT_NAME - Show full dependency tree of objects that OBJECT_NAME depends on.
---------------------------
Phases of ADOP(online patching)
ADPATCH utility is no longer used in R12.2.0 and is being replaced by ADOP – AD Online Patching. There are five phases or life cycles of ADOP which are:
a) PREPARE
b) APPLY
c) FINALIZE
d) CUTOVER
e) CLEANUP
c) FINALIZE
d) CUTOVER
e) CLEANUP
Steps:-
Download the patch and unzip on patch_top and go through the read me to check,what its going to do.
Prepare the system for patching
Prepare the system for patching
1. Run environment file(optional)
Note: The adop utility sets its own environment. There is therefore no need to source the environment before running it.
Note: The adop utility sets its own environment. There is therefore no need to source the environment before running it.
2.adop phase=prepare
3 Apply Phase -
adop phase=apply patches=, workers=
After patch has been successfully applied,complete the patch cycle
4. Finalize phase - Used to perform the final patching operations that can be executed while the application is still online:
adop phase=finalize workers= (called automatically)
adop phase=finalize workers= (called automatically)
5. Cutover phase - Used to perform the transition to the patched environment:
adop phase=cutover workers=
adop phase=cutover workers=
6. Cleanup phase - Used to remove old objects that are no longer needed:
adop phase=cleanup (called automatically)
Now synchronise the technology level between patch and run fc_clone.
adop phase=cleanup (called automatically)
Now synchronise the technology level between patch and run fc_clone.
7. adop phase=fs_clone
PATCHING CYCLE
----------------------------
The "Run Edition" is the code and data used by the running application. As a developer, you will connect to the Run Edition whenever you are engaged in normal development activity on the system.
The "Patch Edition" is an alternate copy of Oracle E-Business Suite code and seed data that is updated by Online Patching.
The Oracle E-Business Suite application-tier files are installed in a root directory of the customer's choosing. Within that root directory you will now find three important sub-directories:
1) How to see, the RUN, PATCH & NON-EDITIONED Filesystems?
fs1 - file system 1 (either run or patch edition)
fs2 - file system 2 (alternate of file system 1)
fs_ne - non-editioned file system, for data files
By just setting the ebs environment, shows the filesystems.
[applmgr@punitchi:applmgr]> . /u02/applR12/PRODDB/fs1/EBSapps/EBSapps.env
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u02/applR12/PRODDB/fs1/EBSapps/appl
PATCH File System : /u02/applR12/PRODDB/fs2/EBSapps/appl
Non-Editioned File System : /u02/applR12/PRODDB/fs_ne
DB Host: punitc.lab.com Service/SID: PRODDB
Sourcing the RUN File System ...
[applmgr@punitchi:applmgr]>
2) How to identify the PATCH & RUN edition of filesystem?
[applmgr@punitchi:applmgr]>pwd
/u02/applR12/PRODDB/fs1/EBSapps/appl
[applmgr@punitchi:applmgr]> . EBSapps.env
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u02/applR12/PRODDB/fs1/EBSapps/appl
PATCH File System : /u02/applR12/PRODDB/fs2/EBSapps/appl
Non-Editioned File System : /u02/applR12/PRODDB/fs_ne
DB Host: punitchi Service/SID: PRODDB
E-Business Suite Environment Setting
------------------------------------
- Enter [R/r] for sourcing Run File System Environment file, or
- Enter [P/p] for sourcing Patch File System Environment file, or
- Enter anything else to exit
Please choose the environment file you wish to source [R/P]:R
Sourcing the RUN File System ...
[applmgr@punitchi:applmgr]>
3) How to connect to the PATCH & RUN edition of filesystem?
[applmgr@punitchi:applmgr]> . /u02/applR12/PRODDB/fs1/EBSapps.env run
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u02/applR12/PRODDB/fs1/EBSapps/appl
PATCH File System : /u02/applR12/PRODDB/fs1/EBSapps/appl
Non-Editioned File System : /u02/applR12/PRODDB/fs_ne
DB Host: punitc.lab.com Service/SID: punitchi
Sourcing the RUN File System ...
[applmgr@punitchi:applmgr]> echo $FILE_EDITION
run
[applmgr@punitchi:applmgr]>
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u02/applR12/PRODDB/fs1/EBSapps/appl
PATCH File System : /u02/applR12/PRODDB/fs2/EBSapps/appl
Non-Editioned File System : /u02/applR12/PRODDB/fs_ne
DB Host: punitchi.lab.com Service/SID: PRODDB
E-Business Suite Environment Setting
------------------------------------
- Enter [R/r] for sourcing Run File System Environment file, or
- Enter [P/p] for sourcing Patch File System Environment file, or
- Enter anything else to exit
Please choose the environment file you wish to source [R/P]:P
Sourcing the PATCH File System ...
[applmgr@punitchi:applmgr]> echo $FILE_EDITION
patch
[applmgr@punitchi:applmgr]>
4) How to display edition status ?
To help keep track of what environment and edition you are connected to, it can be helpful to set the TWO_TASK or FILE_EDITION environment variable as your shell prompt.
[applmgr@punitchi:applmgr]> . EBSapps.env patch
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u02/applR12/PRODDB/fs1/EBSapps/appl
PATCH File System : /u02/applR12/PRODDB/fs2/EBSapps/appl
Non-Editioned File System : /u02/applR12/PRODDB/fs_ne
DB Host: punitchi.lab.com Service/SID: PRODDB
E-Business Suite Environment Setting
------------------------------------
- Enter [R/r] for sourcing Run File System Environment file, or
- Enter [P/p] for sourcing Patch File System Environment file, or
- Enter anything else to exit
Please choose the environment file you wish to source [R/P]:P
Sourcing the PATCH File System ...
[applmgr@punitchi:applmgr]> PS1='$TWO_TASK> '
punitchi_patch>
5) How to find out whether a system is in an Online Patching cycle using the "adop -status" command.
[applmgr@punitchi:applmgr]> adop -status
Enter the APPS username: apps
Enter the APPS password:
Current Patching Session ID: 7
Node Name Node Type Phase Status Started Finished Elapsed
--------------- --------------- ----------- --------------- ------------------------------ ------------------------------ ------------
punitc master PREPARE COMPLETED 05-Jun-16 03:57:39 +00:00 06-Jun-16 04:55:57 +00:00 36:58:18
FINALIZE COMPLETED 06-Jun-16 06:09:01 +00:00 06-Jun-16 06:54:41 +00:00 0:45:40
CUTOVER COMPLETED 06-Jun-16 06:59:47 +00:00 06-Jun-16 07:26:32 +00:00 0:26:45
CLEANUP COMPLETED 06-Jun-16 11:26:07 +00:00 06-Jun-16 11:28:53 +00:00 0:02:46
APPLY COMPLETED
File System Synchronization Used in this Patching Cycle: Full
For more information, run ADOP Status Report by using -detail option
Generating ADOP Status Report at location: /u02/applR12/PRODDB/fs_ne/EBSapps/log/status_20160625_172245/adzdshowstatus.out
Please wait...
Done...!
adop exiting with status = 0 (Success)
6) How to find names and status of past and present database editions using the ADZDSHOWED.sql script.
The below lists the existing database editions and identifies the OLD, RUN, and PATCH editions.
[applmgr@punitchi:applmgr]> sqlplus apps/********* @ADZDSHOWED.sql
SQL*Plus: Release 10.1.0.5.0 - Production on Thu May 29 16:37:18 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
=========================================================================
= Editions
=========================================================================
Edition Name Type Status Current?
--------------- -------- -------- --------
ORA$BASE RETIRED
V_20140202_0749 OLD RETIRED
V_20140405_2132 RUN ACTIVE CURRENT
V_20140508_1528 PATCH ACTIVE
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[applmgr@punitchi:applmgr]>
7) How to change to the patch edition of database using SQL*Plus? /u02/applR12/PRODDB/fs1/EBSapps/appl/ad/12.0.0/sql
[applmgr@punitchi:applmgr]> sqlplus apps/********* @ADZDSHOWED.sql
SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jun 25 17:03:50 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> exec ad_zd.set_edition('PATCH');
8) What are the Tools and Scripts for Edition-based Development?
[applmgr@punitchi:applmgr]> . /u02/app/applmgr/120/punitchi/EBSapps.env run
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u02/app/applmgr/120/punitchi/fs1/EBSapps/appl
PATCH File System : /u02/app/applmgr/120/punitchi/fs2/EBSapps/appl
Non-Editioned File System : /u02/app/applmgr/120/punitchi/fs_ne
DB Host: punitc.lab.com Service/SID: punitchi
Sourcing the RUN File System ...
[applmgr@punitchi:applmgr]> which adop
/u02/applR12/PRODDB/fs1/EBSapps/appl/ad/bin/adop
[applmgr@punitchi:applmgr]> which xdfgen.pl
/u02/applR12/PRODDB/fs1/EBSapps/applfnd/12.0.0/bin/xdfgen.pl
[applmgr@punitchi:applmgr]> which xdfcmp.pl
/u02/applR12/PRODDB/fs1/EBSapps/appl/fnd/12.0.0/bin/xdfcmp.pl
[applmgr@punitchi:applmgr]>
9) Useful SQL*Plus scripts that provides information about the state of your editioned development environment. All ADZD* scripts are found under $AD_TOP/sql.
Add this directory to the SQLPATH environment variable so that you can refer to the scripts by simple name.
[applmgr@punitchi:applmgr]> SQLPATH=$AD_TOP/sql; export SQLPATH
[applmgr@punitchi:applmgr]> echo $SQLPATH
/u02/applR12/PRODDB/fs1/EBSapps/applad/12.0.0/sql
[applmgr@punitchi:applmgr]>
ADZDDBCC - database compliance checker, shows violations of the database object development standards documented in the Oracle E-Business Suite Developer's Guide, Part No. E22961. Warning: this script takes a long time to run.
ADZDSHOWED - Show database editions and current edition.
ADZDSHOWLOG - Show full diagnostic log for online patching infrastructure
ADZDSHOWLOGEVT - Show only event and error messages from online patching diagnostic log (a useful summary, without the detailed statement text).
ADZDSHOWLOGERR - Show only error messages from online patching diagnostic log.
ADZDSHOWEV TABLE_SYNONYM_NAME - Show editioning view column mapping for table.
ADZDSHOWTAB TABLE_SYNONYM_NAME - Show table information and related objects.
ADZDSHOWMV MVIEW_NAME - Show materialized view information and related objects.
ADZDSHOWTS - Show important tablespace status. Ensure that you have enough SYSTEM tablespace.
ADZDCMPED - Compare Patch Edition with Run Edition. Warning: this script may take a long time to run.
ADZDSHOWDDLS - Show stored DDL summary by phase.
ADZDALLDDLS - Show stored DDL statement text and status.
ADZDDDLERROR - Show stored DDL execution errors and messages.
adutlrcmp - Recompile all objects, with before/after status report. Warning: this script may take a long time to run.
The following scripts are for experts:
ADZDSHOWOBJS - Show Object Summary per edition. Counts of actual and stub (inherited) editioned object per edition.
ADZDSHOWAOBJS - Show Actual Objects in the current edition. These are the editioned objects that have been changed by the patch.
ADZDSHOWIOBJS - Show Inherited Objects in the current edition. These are the editioned objects that remain untouched in the Patch Edition.
ADZDSHOWCOBJS - Show Covered Object Summary per edition. Count of objects in old editions that have a replacement in the run edition.
ADZDSHOWCOBJX - Show Covered Object List. List of objects in old editions that have a replacement in the run edition.
ADZDSHOWSM - Show Seed Manager status.
ADZDSHOWTM - Show Table Manager status.
ADZDSHOWAD - AD (online patching) database object status
ADZDSHOWSES - Show sessions connected to the database (by edition).
ADZDSHOWDEP OBJECT_NAME - Show objects that OBJECT_NAME depends on.
ADZDSHOWDEPTREE OBJECT_NAME - Show full dependency tree of objects that OBJECT_NAME depends on.
Tablespace
Tablespace
-------------
set pagesize 300
set linesize 100
column tablespace_name format a30 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total Size(GB)'
column Tot_Free format 999999.99 Heading 'Total Free(Mb)'
column Pct_Free format 999.99 heading '% Free'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
ttitle center 'Tablespaces With Less Than 10% Free Space' skip 2
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
-------------
set pagesize 300
set linesize 100
column tablespace_name format a30 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total Size(GB)'
column Tot_Free format 999999.99 Heading 'Total Free(Mb)'
column Pct_Free format 999.99 heading '% Free'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
ttitle center 'Tablespaces With Less Than 10% Free Space' skip 2
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
Sessions Details
* SESSIONS :
col command format a60
set linesize 125
set linesize 1000
clear columns
set pages 10000
alter session set nls_date_format='DD-Mon-YYYY HH24:MI:SS';
col osuser form a10
col username form a10
col command form a10
col sid form "99999"
col machine form a25
col pgram form a15
break on username on osuser on machine skip 1
select s.username,s.osuser,substr(machine,1,25) machine,SID,s.SERIAL#,spid bg_pid,logon_time,s.status,
decode(s.command,0,'',1,'Create Table',2,'Insert',3,'Select',6,'Update',7,'Delete',9,'Create Index',15,'Alter Table',21,'Create View',23,'Validate Index',35,'Alter Database',39,'Create Tablespace',41,'Drop Tablespace',
40,'Alter Tablespace',53,'Drop User',47,'Exec.Procedure',62,'Analyze Table',63,'Analyze Index',s.command||': Other') command
from v$session s,v$process p,v$transaction t where (s.osuser is not null and s.username != 'oracle8i') and
s.paddr = p.addr and s.taddr = t.addr (+)
order by username,osuser,machine,logon_time desc,s.status;
Following script can be used to get information like OS process ID, PGA allocation, SQL_ID, etc. associated with a database session. The script takes SID and SERIAL# as input.
set lines 300;col "DB User" for a10;col "OS User" for a10;col "SQL ID" for a25;col "OS PID" for a10;select p.spid as "OS PID",p.pga_used_mem as "PGA Used",p.pga_alloc_mem as "PGA Allocated",p.pga_freeable_mem as "PGA Reclaimable",s.sql_id as "SQL ID",s.username as "DB User",s.status,s.osuser as "OS User",s.state "Session State"from gv$process pinner join gv$session son p.addr=s.paddrand s.sid=&sidand s.serial#=&serial/
Subscribe to:
Posts (Atom)