Follow my latest Doc

Please follow my latest doc "The Patch Impact Analysis using the Patch Wizard in Oracle Application Manager.". Feedback and suggestions are always welcome.

Exoprt and Import Schema

Hi Friends hope my previous post helped at-least some of you.
Taking export and import of schemas is a day to day activity for DBAs and other Oracle practitioners. The Data pump technique is one of the most convenient one.

Three steps to export a schema:-
Step 1 --> Create a directory for the dump files location--
Say location name is /TESTDB/SchemaDump/ ----- (for Linux, HP-UX, IBM-AIX, etc)
eg:-
$cd /TESTDB
$mkdir  SchemaDump
$cd  SchemaDump

 Step 2 --> Create virtual directory and grant permissions (schema say TestSchema) --
eg :-
Connect to sql and use the following commands--

create directory dump_dir as '/TESTDB/SchemaDump/';

grant read,write on directory dump_dir to TestSchema;
Grant succeeded.

 Step 3 --> Take the export --
eg:-
$expdp system@<database name with domain> schemas=TestSchema LOGFILE=TestSchema.log DUMPFILE=TestSchema.dmp flashback_time=systimestamp DIRECTORY=dump_dir

This command will create the dump and log file in the location "/TESTDB/SchemaDump"
flashback_time=systimestamp ---> This is optional
Please give the system password to process.

Command to import a schema:-
You should have the dump file located in the dump directory, once it is done use the following command:-
eg:-
$impdp system@<database name with domain> schemas=TestSchema directory=dump_dir dumpfile=TestSchema.dmp logfile=TestSchemaImp.log
Please give the system password to process.

This technique can also be used to export and import Tables as-well, the export backup of an entire database is also possible. The syntax for Table export is almost the same, please feel free to question if any one face any issues in using the commands.

Please comment if something needs to be added. Good Luck.!

Patch Analysis(adpatch)

As a DBA we often need to apply Patches. This is a brief process to analysis the patch impact in our environments, before applying the patch.

Patch analysis:-
------------------------------------------------------------------------------------------------------------
Login to Unix/linux/solaris server that contain the Database.

$sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 18 12:17:48 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
SAMPLEDB

SQL> select release_name from apps.fnd_product_groups;

RELEASE_NAME
--------------------------------------------------
11.5.10.2

SQL> select patch_level from apps.fnd_product_installations where patch_level like '%AD%';

PATCH_LEVEL
------------------------------
11i.AD.I.7
(Similarly check for other modules as well.)

SQL> select LANGUAGE_CODE, NLS_LANGUAGE, INSTALLED_FLAG from apps.fnd_languages_vl where INSTALLED_FLAG = 'I';

LANG NLS_LANGUAGE                   I
---- ------------------------------ -
ZHS  SIMPLIFIED CHINESE             I
ZHT  TRADITIONAL CHINESE            I
(This is for my sample environment, there may be other languages also)

SQL> select count(1) from dba_objects where status='INVALID';

  COUNT(1)
----------
        53

SQL> select object_type, count(1) from dba_objects where status='INVALID' group by object_type;

OBJECT_TYPE           COUNT(1)
------------------- ----------
PROCEDURE                    9
VIEW                        10
SYNONYM                      4
PACKAGE BODY                26
PACKAGE                      4

SQL> select owner, count(1) from dba_objects where status='INVALID' group by owner;

OWNER                            COUNT(1)
------------------------------ ----------
SYSTEM                                  2
EAR                                     1
EAPTIB                                  4
SUPPORT                                 2
CHUK                                    2
APPS                                   33
EDMSRUN                                 2
MFM                                     1
PERFSTAT                                2
TIBCO                                   4

SQL> select host||'.'||domain from apps.fnd_nodes where support_admin='Y';

HOST||'.'||DOMAIN
--------------------------------------------------------------------------------
eaad380.ams.sample.com
------------------------------------------------------------------------------------------------------------
Oracle also provides us the Patch Wizard in the Oracle Application Manager(OAM) for performing the patch analysis. Please wait for my next post on using the OAM.
I believe the above information shall help new DBAs in their activities. Please feel free to comment for any further information.