Archive for April, 2012

Oracle datapump Parameters / Commands

Oracle impdp parameters:


Parameter             	Parameter description
ATTACH                	Attach to existing job, e.g. ATTACH [=job name].
CONTENT            	Specifies data to load where the valid keywords are:(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY          	Directory object to be used for dump, log, and sql files.
DUMPFILE            	List of dumpfiles to import from (expdat.dmp),e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD   	Password key for accessing encrypted column data.This parameter is not valid for network import jobs.
ESTIMATE              	Calculate job estimates where the valid keywords are:(BLOCKS) and STATISTICS.
EXCLUDE               	Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN    	SCN used to set session snapshot back to.
FLASHBACK_TIME   	Time used to get the SCN closest to the specified time.
FULL                    Import everything from source (Y).
HELP                    Display help messages (N).
INCLUDE               	Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME           	Name of import job to create.
LOGFILE               	Log file name (import.log).
NETWORK_LINK          	Name of remote database link to the source system.
NOLOGFILE         	Do not write logfile.
PARALLEL            	Change the number of active workers for current job.
PARFILE               	Specify parameter file.
QUERY                 	Predicate clause used to import a subset of a table.
REMAP_DATAFILE        	Redefine datafile references in all DDL statements.
REMAP_SCHEMA          	Objects from one schema are loaded into another schema.
REMAP_TABLESPACE  	Tablespace object are remapped to another tablespace.
REUSE_DATAFILES       	Tablespace will be initialized if it already exists (N).
SCHEMAS               	List of schemas to import.
SKIP_UNUSABLE_INDEXES 	Skip indexes that were set to the Index Unusable state.
SQLFILE               	Write all the SQL DDL to a specified file.
STATUS                	Frequency (secs) job status is to be monitored where the default (0) will show new status when available.
STREAMS_CONFIGURATION 	Enable the loading of Streams metadata
TABLE_EXISTS_ACTION   	Action to take if imported object already exists.Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES                  Identifies a list of tables to import.
TABLESPACES           	Identifies a list of tablespaces to import.
TRANSFORM             	Metadata transform to apply to applicable objects.Valid transform keywords: SEGMENT_ATTRIBUTES, STORAGE OID, and PCTSPACE.
TRANSPORT_DATAFILES   	List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK  	Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES 	List of tablespaces from which metadata will be loaded. Only valid in NETWORK_LINK mode import operations.
VERSION               	Version of objects to export where valid keywords are: (COMPATIBLE), LATEST, or any valid database version.Only valid for NETWORK_LINK and SQLFILE.

Continue reading →

Advertisements
2012
04/29
CATEGORY
Oracle DBA
TAGS



Leave a comment

Oracle Datapump Various Scenario/Examples

I collected this content from this link.

Let’s experiment expdp & impdp utility for different scenarios. We have two database orcl, ordb. All the below scenarios are tested in Oracle10g R2 version.

Scenario 1 :

Export the whole orcl database.

Export Parfile content:

userid=system/password@orcl
dumpfile=expfulldp.dmp
logfile=expfulldp.log
full=y
directory=dumplocation Continue reading →

2012
04/29
CATEGORY
Oracle DBA
TAGS



3 comments

Generate DDL SQL Script for Specific Schema in Oracle

It’s straight forward to generate DDL SQL Script from any specific schema. However, it has nothing to do with expdp.


expdp system/sys123 schemas=hr directory=DATA_PUMP_DIR dumpfile=hr_25042012.dmp logfile=hr_25042012.log

Just specify sql file name using impdp, it will generate DDL SQL Script for any specific schema. Continue reading →

2012
04/29
CATEGORY
Oracle DBA
TAGS



Leave a comment

Enable/Disable OS authentication for Oracle in Windows Server

It is possible to disable Operating System authentication while connecting Oracle Database. In that case, ORA-01031: insufficient privileges will be thrown as shown below.


C:\>sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Apr 29 18:39:21 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges

Continue reading →

2012
04/29
CATEGORY
Oracle DBA
TAGS




Leave a comment

rman incremental backup level 0,1 backup

Just check out nls_date_format and set or export nls_date_format to your expected date format before start working with rman.


SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8MSWIN1252
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.1.0

Continue reading →

2012
04/28
CATEGORY
Oracle DBA
TAGS





Leave a comment

rman imagecopy type Backup

Two types of backup can be done using rman, those are backupset (default) and imagecopy. Second one is merely used and used for migration purpose only like ASM migration; 



RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'G:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\SNCFORCL1.ORA'; # default

Continue reading →

2012
04/27
CATEGORY
Oracle DBA
TAGS


Leave a comment

rman Basic Commands

Those are very basic rman Commands. For local Database, you can connect this way and for other Databases, provide proper connection credentials.



C:\>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Apr 2 09:01:28 2012

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

connected to target database: ORCL1 (DBID=1163774759)

RMAN>

Continue reading →

2012
04/24
CATEGORY
Oracle DBA
TAGS


Leave a comment
Advertisements