DATA PUMP Scenarios

DATA PUMP Scenarios

1) Import 11g data dump into 10g
You can use Oracle data dump to export data dump from 11g server, and import into 10g using the data Pump parameter called Version. When you use Version, Datapump exports all objects that are compatible for your target version
 So, If your database is 11g and you want to export 11g and import into 10g
 from 11g db ,
$  expdp  Test_schema_name/passs directory=datapump  schemas=Test_schema_name Version=10.2.0.4.0.
 Once the export is done, you do the regular import from 10g server.

2) Import multiple dump files
If the size of the dump file is large, usually they will be split into smaller chunks for easier ftp upload/download.
 If you are trying to import a dump file that is split into many chunks, then you need to modify the DUMPFILE paratermeter to include %U
 Ex:  If the dump files are named EXP_PROD_1.dmp, EXP_PROD_2.dmp etc ,    then  DUMPFILE=EXP_PROD_%U.DMP

3) How to kill data pump jobs
When you import or export using data pump impdp or expdp commands, the import/export is done by a job. You have an option to provide a job name using JOB_NAME parameter too
 Following sql will give you the list of data pump jobs
                   select * from dba_datapump_jobs
If you want to kill your impdp or expdp
 1) Make sure that your impdp/expdp command prompt window is active
 2) Press Control-C , It will pause the job. Don't press another Control-C or close the command prompt. This will just close the window, but the job will still be running in the background
 3) Type Kill_Job
ex: Import> kill_job
Are you sure you wish to stop this job (y/n): y

If by mistake, you closed the window and your import/export job is still running,
 1) Get the name of the job using
select * from dba_datapump_jobs
2) Open a new command prompt window. If you want to kill your import job type
impdp username/password@database attach=name_of_the_job
3) Once you are attached to job, Type Kill_Job
ex: Import> kill_job
Are you sure you wish to stop this job (y/n): y
And your job is killed, it will no longer show in dba_datapump_jobs

4) REUSE_DUMPFILE : ( Overwrite existing dumpfile)
This is the option with data pump expdp utility. Normally when you perform the export using expdp utility and if the dumpfile is present in the export directory it will throw an error “ORA-27038: created file already exists”. This situation happens when you wanted to perform the repetitive exports using the same dumpfile. Oracle provides an option reuse_dumpfile=[Y/N] to avoid this error. You should mention the parameter value as Y to overwrite the existing dump file. By default the option considered as N.
$ expdp   scott/tiger   directory=exp_dir     dumpfile = x.dmp     table s= example    reuse_dumpfiles = y

Data pump Schema Refresh

Data pump Schema Refresh

Schema refresh is an regular job for any DBA specially during migration projects, so today I decide to post about a schema refresh

Assuming here schema(SCOTT) is  refreshed  from source(PROD) to Target(TEST) on oracle 11g server

SQL>  select  banner  from  v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

On Source side (PROD) ,

Create a directory or use an existing directory (ex: data_pump_dir) and grant read and write permissions on this directory to user ‘SYSTEM‘

SQL >   grant  read, write  on  directory  data_pump_dir  to   system;

Grant Succeeded.

Step 1:   Exporting the data from prod(source)

$   vi   expdp_refresh_schema.sh

$  expdp  system/****@sourcehostname   dumpfile=expdpschema.dmp   Directory=data_pump_dir    logfile=export.log   schemas= scott

$  nohup  sh  expdp_refresh_schema.sh>refresh_schema.out &

Step 2 :  Copying the dumpfiles from source to target

For copying Dumpfiles from one server to another server we can use either Winscp(Graphical tool for copying files from windows to linux and  vice versa),FTP, SFTP, SCP, etc.

$ scp  expdpschema.dmp   system@TargetHostname:/home/oracle/datapump

Here I’m copying dumpfile from source to the target /home/oracle/datapump  location

Step 3 :  Importing data from dumpfile into target database

Before importing dunpfile into target(TEST) make sure you delete or backup all the objects in that schema, to clear all objects from particular schema run the script from here 

$ impdp  system/****@targethostname   dumpfile=expdpschema.dmp   Directory=data_pump_dir    logfile=import.log   remap_schema= scott:newscott

Step 4 :   Verify target database objects with source

SQL>   select   count(*)  from  dba_objects   where  owner=’NEWSCOTT’ ;

SQL>   select  count(*)  from  dba_tables  where  owner =’NEWSCOTT’;

The above results  should be same as that of source  ‘scott’  schema

Alert log & Trace file location in Oracle 11gR2

Alert log & Trace file location in Oracle 11gR2

Oracle 11gR2 is a major release upgrade from 10gR2 and 11gR1 both. Many previous parameters like user_dump_dest etc have been deprecated. There is a single parameter called diagnostic_dest.  Many DBA’s are so used to the location of alert log and trace files , that it becomes quite inconvenient when you start using Oracle 11g . But Oracle 11g has a powerful new utility called ADR CLI to navigate around the alert log and other trace files. I will demonstrate a few ways to check the alert log and trace files using “adrci” command.
C:\Documents and Settings\test>adrci
ADRCI: Release 11.2.0.1.0 – Production on Thu Mar 31 11:56:54 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
ADR base = “e:\app\testdb”
 adrci> help
HELP [topic]

 Available Topics:
 CREATE REPORT
 ECHO
 EXIT
 HELP
 HOST
 IPS
 PURGE
 RUN
 SET BASE
 SET BROWSER
 SET CONTROL
 SET ECHO
 SET EDITOR
 SET HOMES | HOME | HOMEPATH
 SET TERMOUT
 SHOW ALERT
 SHOW BASE
 SHOW CONTROL
 SHOW HM_RUN
 SHOW HOMES | HOME | HOMEPATH
 SHOW INCDIR
 SHOW INCIDENT
 SHOW PROBLEM
 SHOW REPORT
 SHOW TRACEFILE
 SPOOL

There are other commands intended to be used directly by Oracle, type
 “HELP EXTENDED” to see the list
adrci> show alert
1: diag\clients\user_system\host_3726408541_76
 2: diag\rdbms\testdb\testdb
 3: diag\tnslsnr\test\listener
 Q: to quit

Please select option:
Many of the adrci commands are very similar to the standard Unix vi commands. For example
SHOW ALERT -TAIL (to view the last 10 entries)
SHOW ALERT -TAIL 50 (to view the last 50 entries)
SHOW ALERT -TAIL -F (keeps the alert log open to view as entries arrive until Control C is pressed)
show alert -p “message_text like ‘%ORA-07445%’” (to show all occurences of the string ‘ORA-07445′ in the alert log.)

Purging Alert Log Content

The adrci command ‘purge’ can be used to purge entries from the alert log. Note that this purge will only apply to the XML based alert log and not the text file based alert log which still has to be maintained using OS commands.
The purge command takes the input in minutes and specifies the number of minutes for which records should be retained.
So to purge all alert log entries older than 7 days the following command will be used:
adrci > purge -age 10080 -type ALERT
It is a powerful utility with much more functionality.
P.S: The general location of alert log on windows is : E:\app\<hostname>\diag\rdbms\<sid>\<sid>\trace

Finding Trace Files

Trace files are stored in the Automatic Diagnostic Repository (ADR), in the trace directory under each ADR home. To help you locate individual trace files within this directory, you can use data dictionary views. For example, you can find the path to your current session's trace file or to the trace file for each Oracle Database process.

To find the trace file for your current session:
•Submit the following query:
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';

The full path to the trace file is returned.
To find all trace files for the current instance:
•Submit the following query:
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

The path to the ADR trace directory for the current instance is returned.
To determine the trace file for each Oracle Database process:
• Submit the following query:

SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS;

Oracle Drop Database in LINUX

Oracle Drop Database in LINUX

Dropping a database will remove all corresponding datafiles, redo log files and control files. The database must be mounted in exclusive and restricted mode.

Drop database command will not do any effect on archived log files and backups of the database. Drop database command will not delete the files on RAW disks.
If you created your database with DBCA, you can use the same DBCA tool to drop the database.

Example.

$export ORACLE_SID=DEVDB
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 28 18:18:41 2012

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>DROP DATABASE;

DROP DATABASE
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

NOTE :  Drop database does not work when the database is opened in normal open mode

SQL > shut immediate
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 282352256 bytes
Fixed Size                  2132856 bytes
Variable Size            1644174848 bytes
Database Buffers         1140850688 bytes
Redo Buffers               36388864 bytes
Database mounted.

SQL> alter system enable restricted session;
System altered.

Or open the database using “startup mount Restrict” command

SQL> drop database;
Database dropped.
Disconnected from 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>
Now, all the Control files, redo log files and Datafiles are automatically removed but you can still see the Parameter file and archivelog files.

Note:If your database is running on windows you have to remove the registry entries manually.

Adworker Fails

What to do if a worker fails during adpatch application on R12

In a different window constantly monitor the patch worker using adctrl command.

1. set the application environment file of R12

2. run adctrl

check the status of the worker using option 1 [Show worker status].

If a worker fails.

1. Check adwork01.log [check the failed worker number] file under $APPL_TOP/admin//log directory for details about the error.

2. Fix the issue mentioned in worker file.

3. Use Option 2 [Tell worker to restart a failed job] to restart the failed job in adctrl window.

4. If the issue is fixed properly the worker will start and complete the job and your adpatch will continue executing.


Sometimes, you need to restart all the workers and exit the adpatch session and restart it from the failed location will do your job.

Difference Between 10g, 11g and 12c

Oracle Database 10g New Features
=========================:
NID utility has been introduced to change the database name and id.

Automated Storage Management (ASM). ASMB, RBAL, ARBx are the new background processes related to ASM.

New memory structure in SGA i.e. Streams pool (streams_pool_size parameter), useful for datapump activities & streams replication.

From Oracle 10g, the spool command can append to an existing one.
SQL> spool result.log append

Ability to rename tablespaces (except SYSTEM and SYSAUX), whether permanent or temporary, using the following command:
SQL> ALTER TABLESPACE oldname RENAME TO newname;

Oracle Database 11g New Features
=========================:

Ability to mark a table as read only.
SQL> alter table table-name read only;

Temporary tablespace or it's tempfile can be shrinked, up to specified size.
SQL> alter tablespace temp-tbs shrink space;
SQL> alter tablespace temp-tbs shrink space keep n{K|M|G|T|P|E};

Introduction of ADR, single location of all error and trace data in the database, diagnostic_dump parameter

A new parameter is added in 11g, called MEMORY_TARGET, automatic memory management for both the SGA and PGA.

A system privelege is introduced in 11g called SYSASM, and this is granted to users who need to perform ASM related tasks.

Oracle Database 12c New Features
=========================:

Moving and Renaming datafile is now ONLINE, no need to put datafile in offline.
SQL> alter database move datafile 'path' to 'new_path';

No need to shutdown database for changing archive log mode.

Like sysdba, sysoper & sysasm, we have new privileges, in Oracle 12.1.0.
sysbackup for backup operations
sysdg for Data Guard operations
syskm for key management

Patching:
Centralised patching.
We can retrieve OPatch information using sqlplus query, using DBMS_QOPATCH package
SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;
We can test patches on database copies, rolling patches out centrally once testing is complete.

New Commands
create pluggable database ...
alter pluggable database ...
drop pluggable database ...


New background processes - LREG (Listener Registration), SA (SGA Allocator), RM.

Oracle Database 12c Data Pump will allow turning off redo for the import operation (only).
$ impdp ... transform=disable_archive_logging:y