The alert log file (also referred to as the ALERT.LOG) is a chronological log of messages and errors written out by an Oracle Database.
Typical messages found in this file is: database startup, shutdown, log switches, space errors, etc.
This file should constantly be monitored to detect unexpected messages and corruptions.
Oracle will automatically create a new alert log file whenever the old one is deleted.
#######Location of the ALERT.LOG file
Oracle will write the alert.log file to the directory as specified by the BACKGROUND_DUMP_DEST parameter.
If this parameter is not set, the alert.log will be created in the ORACLE_HOME/rdbms/trace directory.
SQL> show parameter BACKGROUND_DUMP_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /app/oracle/diag/rdbms/o11gr1/
o11gr1/trace
##### Writing to the ALERT.LOG file
Users can write messages to the alter.log file. Example:
-- Write message to alert.log
exec dbms_system.ksdwrt(2, 'Look Ma, I can write to the alert.log file!');
PL/SQL procedure successfully completed.
-- Flush the buffer
exec dbms_system.ksdfls;
PL/SQL procedure successfully completed.
http://www.orafaq.com/wiki/Alert.log
Tuesday, March 29, 2011
Monday, March 28, 2011
Expdp / Impdp
Data pump is a new feature in Oracle10g that provides fast parallel data load. With direct path and parallel execution, data pump is several times faster then the traditional exp/imp. Traditional exp/imp runs on client side. But impdp/expdp runs on server side. So we have much control on expdp/expdp compared to traditional exp/imp. When compared to exp/imp, data pump startup time is longer. Because, it has to setup the jobs, queues, and master table. Also at the end of the export operation the master table data is written to the dump file set, and at the beginning of the import job the master table is located and loaded in the schema of the user.
Following are the process involved in the data pump operation:
Client Process : This process is initiated by client utility. This process makes a call to the data pump API. Once the data pump is initiated, this process is not necessary for the progress of the job.
Shadow Process : When client log into the database, foreground process is created. It services the client data pump API requests. This process creates the master table and creates Advanced queuing queues used for communication. Once client process ends, shadow process also go away.
Master Control Process : MCP controls the execution of the data pump job. There is one MCP per job. MCP divides the data pump job into various metadata and data load or unload jobs and hands them over to the worker processes.
Worker Process : MCP creates worker process based on the valule of the PARALLEL parameter. The worker process performs the task requested by MCP.
Advantage of Data pump
1. We can perform export in parallel. It can also write to multiple files on different disks. (Specify parameters PARALLEL=2 and the two directory names with file specification DUMPFILE=ddir1:/file1.dmp, DDIR2:/file2.dmp)
2. Has ability to attach and detach from job, monitor the job progress remotely.
3. Has more option to filter metadata objects. Ex, EXCLUDE, INCLUDE
4. ESTIMATE_ONLY option can be used to estimate disk space requirements before performs the job
5. Data can be exported from remote database by using Database link
6. Explicit DB version can be specified, so only supported object types are exported.
7. During impdp, we can change the target file names, schema, and tablespace. Ex, REMAP_SCHEMA, REMAP_DATAFILES, REMAP_TABLESPACE
8. Has the option to filter data rows during impdp. Traditional exp/imp, we have this filter option only in exp. But here we have filter option on both impdp, expdp.
9. Data can be imported from one DB to another without writing to dump file, using NETWORK_LINK parameter.
10. Data access methods are decided automatically. In traditional exp/imp, we specify the value for the parameter DIRECT. But here, it decides where direct path can not be used , conventional path is used.
11. Job status can be queried directly from data dictionary(For example, dba_datapump_jobs, dba_datapump_sessions etc)
Exp & Expdp common parameters: These below parameters exists in both traditional exp and expdp utility.
FILESIZE
FLASHBACK_SCN
FLASHBACK_TIME
FULL
HELP
PARFILE
QUERY
TABLES
TABLESPACES
TRANSPORT_TABLESPACES(exp value is Y/N, expdp value is name of the tablespace)
Comparing exp & expdp parameters: These below parameters are equivalent parameters between exp & expdp. Exp and corresponding Expdp parameters...
FEEDBACK => STATUS
FILE => DUMPFILE
LOG => LOGFILE
OWNER => SCHEMAS
TTS_FULL_CHECK => TRANSPROT_FULL_CHECK
New parameters in expdp Utility
ATTACH Attach the client session to existing data pump jobs
CONTENT Specify what to export(ALL, DATA_ONLY, METADATA_ONLY)
DIRECTORY Location to write the dump file and log file.
ESTIMATE Show how much disk space each table in the export job consumes.
ESTIMATE_ONLY It estimate the space, but does not perform export
EXCLUDE List of objects to be excluded
INCLUDE List of jobs to be included
JOB_NAME Name of the export job
KEEP_MASTER Specify Y not to drop the master table after export
NETWORK_LINK Specify dblink to export from remote database
NOLOGFILE Specify Y if you do not want to create log file
PARALLEL Specify the maximum number of threads for the export job
VERSION DB objects that are incompatible with the specified version will not be exported.
ENCRYPTION_PASSWORD The table column is encrypted, then it will be written as clear text in the dump file set when the password is not specified. We can define any string as a password for this parameter.
COMPRESSION Specifies whether to compress metadata before writing to the dump file set. The default is METADATA_ONLY. We have two values(METADATA_ONLY,NONE). We can use NONE if we want to disable during the expdp.
SAMPLE - Allows you to specify a percentage of data to be sampled and unloaded from the source database. The sample_percent indicates the probability that a block of rows will be selected as part of the sample.
Imp & Impdp common parameters: These below parameters exist in both traditional imp and impdp utility.
FULL
HELP
PARFILE
QUERY
SKIP_UNUSABLE_INDEXES
TABLES
TABLESPACES
Comparing imp & impdp parameters: These below parameters are equivalent parameters between imp & impdp. imp and corresponding impdp parameters...
DATAFILES => TRANSPORT_DATAFILES
DESTROY =>REUSE_DATAFILES
FEEDBACK =>STATUS
FILE =>DUMPFILE
FROMUSER =>SCHEMAS, REMAP_SCHEMAS
IGNORE =>TABLE_EXISTS_ACTION(SKIP,APPEND,TRUNCATE,REPLACE)
INDEXFILE, SHOW=>SQLFILE
LOG =>LOGFILE
TOUSER =>REMAP_SCHEMA
New parameters in impdp Utility
FLASHBACK_SCN Performs import operation that is consistent with the SCN specified from the source database. Valid only when NETWORK_LINK parameter is used.
FLASHBACK_TIME Similar to FLASHBACK_SCN, but oracle finds the SCN close to the time specified.
NETWORK_LINK Performs import directly from a source database using database link name specified in the parameter. The dump file will be not be created in server when we use this parameter. To get a consistent export from the source database, we can use the FLASHBACK_SCN or FLASHBACK_TIME parameters. These two parameters are only valid when we use NETWORK_LINK parameter.
REMAP_DATAFILE Changes name of the source DB data file to a different name in the target.
REMAP_SCHEMA Loads objects to a different target schema name.
REMAP_TABLESPACE Changes name of the source tablespace to a different name in the target.
TRANSFORM We can specify that the storage clause should not be generated in the DDL for import. This is useful if the storage characteristics of the source and target database are different. The valid values are SEGMENT_ATTRIBUTES, STORAGE. STORAGE removes the storage clause from the CREATE statement DDL, whereas SEGMENT_ATTRIBUTES removes physical attributes, tablespace, logging, and storage attributes.
TRANSFORM = name:boolean_value[:object_type], where boolean_value is Y or N.
For instance, TRANSFORM=storage:N:table
ENCRYPTION_PASSWORD It is required on an import operation if an encryption password was specified on the export operation.
CONTENT, INCLUDE, EXCLUDE are same as expdp utilities.
Prerequisite for expdp/impdp:
Set up the dump location in the database.
system@orcl> create directory dumplocation
2 as 'c:/dumplocation';
Directory created.
system@orcl> grant read,write on directory dumploc to scott;
Grant succeeded.
system@orcl>
Let us experiment expdp & impdp utility as different scenario...... We have two database orcl, ordb. All the below scenarios are tested in Oracle10g R2 version.
Scenario1 Export the whole orcl database.
Export Parfile content:
userid=system/password@orcl
dumpfile=expfulldp.dmp
logfile=expfulldp.log
full=y
directory=dumplocation
Scenario2 Export the scott schema from orcl and import into ordb database. While import, exclude some objects(sequence,view,package,cluster,table). Load the objects which came from RES tablespace into USERS tablespace in target database.
Export Parfile content:
userid=system/password@orcl
dumpfile=schemaexpdb.dmp
logfile=schemaexpdb.log
directory=dumplocation
schemas=scott
Import parfile content:
userid=system/password@ordb
dumpfile=schemaexpdb.dmp
logfile=schemaimpdb.log
directory=dumplocation
table_exists_action=replace
remap_tablespace=res:users
exclude=sequence,view,package,cluster,table:"in('LOAD_EXT')"
Scenario3 Export the emp table from scott schema at orcl instance and import into ordb instance.
Expdb parfile content:
userid=system/password@orcl
logfile=tableexpdb.log
directory=dumplocation
tables=scott.part_emp
dumpfile=tableexpdb.dmp
Impdp parfile content:
userid=system/password@ordb
dumpfile=tableexpdb.dmp
logfile=tabimpdb.log
directory=dumplocation
table_exists_action=REPLACE
Scenario4 Export only specific partition in emp table from scott schema at orcl and import into ordb database.
Expdp parfile content:
userid=system/password@orcl
dumpfile=partexpdb.dmp
logfile=partexpdb.log
directory=dumplocation
tables=scott.part_emp:part10,scott.part_emp:part20
Impdp parfile content: If we want to overwrite the exported data in target database, then we need to delete emp table for deptno in(10,20).
scott@ordb> delete part_emp where deptno=10;
786432 rows deleted.
scott@ordb> delete part_emp where deptno=20;
1310720 rows deleted.
scott@ordb> commit;
Commit complete.
userid=system/password@ordb
dumpfile=partexpdb.dmp
logfile=tabimpdb.log
directory=dumplocation
table_exists_action=append
Scenario5 Export only tables in scott schema at orcl and import into ordb database.
Expdp parfile content:
userid=system/password@orcl
dumpfile=schemaexpdb.dmp
logfile=schemaexpdb.log
directory=dumplocation
include=table
schemas=scott
Impdp parfile content:
userid=system/password@orcl
dumpfile=schemaexpdb.dmp
logfile=schemaimpdb.log
directory=dumplocation
table_exists_action=replace
Scenario6 Export only rows belonging to department 10 and 20 in emp and dept table from orcl database. Import the dump file in ordb database. While importing, load only deptno 10 in target database.
Expdp parfile content:
userid=system/password@orcl
dumpfile=data_filter_expdb.dmp
logfile=data_filter_expdb.log
directory=dumplocation
content=data_only
schemas=scott
include=table:"in('EMP','DEPT')"
query="where deptno in(10,20)"
Impdp parfile content:
userid=system/password@ordb
dumpfile=data_filter_expdb.dmp
logfile=data_filter_impdb.log
directory=dumplocation
schemas=scott
query="where deptno = 10"
table_exists_action=APPEND
Scenario7 Export the scott schema from orcl database and split the dump file into 50M sizes. Import the dump file into ordb datbase.
Expdp parfile content:
userid=system/password@orcl
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
filesize=50M
schemas=scott
include=table
As per the above expdp parfile, initially, schemaexp_split_01.dmp file will be created. Once the file is 50MB, the next file called schemaexp_split_02.dmp will be created. Let us say, the dump file size is 500MB, then it creates 10 dump file as each file size is 50MB.
Impdp parfile content:
userid=system/password@ordb
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
table_exists_action=replace
remap_tablespace=res:users
exclude=grant
Scenario8 Export the scott schema from orcl database and split the dump file into four files. Import the dump file into ordb datbase.
Expdp parfile content:
userid=system/password@orcl
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
parallel=4
schemas=scott
include=table
As per the above parfile content, initially four files will be created - schemaexp_split_01.dmp, schemaexp_split_02.dmp, schemaexp_split_03.dmp, schemaexp_split_04.dmp. Notice that every occurrence of the substation variable is incremented each time. Since there is no FILESIZE parameter, no more files will be created.
Impdp parfile content:
userid=system/password@ordb
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
table_exists_action=replace
remap_tablespace=res:users
exclude=grant
Scenario9 Export the scott schema from orcl database and split the dump file into three files. The dump files will be stored in three different location. This method is especially useful if you do not have enough space in one file system to perform the complete expdp job. After export is successful, import the dump file into ordb database.
Expdp parfile content:
userid=system/password@orcl
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=dump1:schemaexp_%U.dmp,dump2:schemaexp_%U.dmp,dump3:schemaexp_%U.dmp
filesize=50M
schemas=scott
include=table
As per above expdp par file content, it place the dump file into three different location. Let us say, entire expdp dump file size is 1500MB. Then it creates 30 dump files(each dump file size is 50MB) and place 10 files in each file system.
Impdp parfile content:
userid=system/password@ordb
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=dump1:schemaexp_%U.dmp,dump2:schemaexp_%U.dmp,dump3:schemaexp_%U.dmp
table_exists_action=replace
Scenario10 We are in orcl database server. Now export the ordb data and place the dump file in orcl database server. After expdp is successful, import the dump file into orcl database. When we use network_link, the expdp user and source database schema users should have identical privileges. If there no identical privileges, then we get the below error.
C:\impexpdp>expdp parfile=networkexp1.par
Export: Release 10.2.0.1.0 - Production on Sunday, 17 May, 2009 12:06:40
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user
Expdp parfile content:
userid=scott/tiger@orcl
logfile=netwrokexp1.log
directory=dumplocation
dumpfile=networkexp1.dmp
schemas=scott
include=table
network_link=ordb
As per the above parfile, expdp utility exports the ordb database data and place the dump file in orcl server. Since we are running expdp in orcl server. This is basically exporting the data from remote database.
Impdp parfile content:
userid=system/password@orcl
logfile=networkimp1.log
directory=dumplocation
dumpfile=networkexp1.dmp
table_exists_action=replace
Scenario11 Export scott schema in orcl and import into ordb. But do not write dump file in server. The expdp and impdp should be completed with out writing dump file in the server. Here we do not need to export the data. We can import the data without creating the dumpfile.
Here we run the impdp in ordb server and it contacts orcl DB and extract the data and import into ordb database. If we do not have much space in the file system to place the dump file, then we can use this option to load the data.
Impdp parfile content:
userid=scott/tiger@ordb
network_link=orcl
logfile=networkimp2.log
directory=dumplocation
table_exists_action=replace
Scenario12 Expdp scott schema in ordb and impdp the dump file in training schema in ordb database.
Expdp parfile content:
userid=scott/tiger@orcl
logfile=netwrokexp1.log
directory=dumplocation
dumpfile=networkexp1.dmp
schemas=scott
include=table
Impdp parfile content:
userid=system/password@ordb
logfile=networkimp1.log
directory=dumplocation
dumpfile=networkexp1.dmp
table_exists_action=replace
remap_schema=scott:training
Scenario 13 Expdp table on orcl database and imdp in ordb. When we export the data, export only 20 percent of the table data. We use SAMPLE parameter to accomplish this task.
SAMPLE parameter allows you to export subsets of data by specifying the percentage of data to be sampled and exported. The sample_percent indicates the probability that a block of rows will be selected as part of the sample. It does not mean that the database will retrieve exactly that amount of rows from the table. The value you supply for sample_percent can be anywhere from .000001 up to, but not including, 100.
If no table is specified, then the sample_percent value applies to the entire export job. The SAMPLE parameter is not valid for network exports.
Expdp parfile content:
userid=system/password@orcl
dumpfile=schemaexpdb.dmp
logfile=schemaexpdb.log
directory=dumplocation
tables=scott.part_emp
SAMPLE=20
As per the above expdp parfile, it exports only 20 percent of the data in part_emp table.
Impdp parfile content:
userid=system/password@ordb
dumpfile=schemaexpdb.dmp
logfile=schemaimpdb.log
directory=dumplocation
table_exists_action=replace
Managing Data Pump jobs
The datapump clients expdp and impdp provide an interactive command interface. Since each expdp and impdp operation has a job name, you can attach to that job from any computer and monitor the job or make adjustment to the job.
Here are the data pump interactive commands.
ADD_FILE Adds another file or a file set to the DUMPFILE set.
CONTINUE_CLIENT Changes mode from interactive client to logging mode
EXIT_CLIENT Leaves the client session and discontinues logging but leaves the current job running.
KILL_JOB Detaches all currently attached client sessions and terminates the job
PARALLEL Increase or decrease the number of threads
START_JOB Starts(or resume) a job that is not currently running. SKIP_CURRENT option can skip the recent failed DDL statement that caused the job to stop.
STOP_JOB stops the current job, the job can be restarted later
STATUS Displays detailed status of the job, the refresh interval can be specified in seconds. The detailed status is displayed to the output screen but not written to the log file.
Scenario14 Let us start the job and in between, we stop the job in middle and resume the job. After some time, let us kill the job and check the job status for every activity....
We can find what jobs are running currently in the database by using the below query.
SQL> select state,job_name from dba_datapump_jobs;
STATE JOB_NAME
------------------------------ ------------------------------
EXECUTING SYS_IMPORT_FULL_01
SQL>
C:\impexpdp>impdp parfile=schemaimp1.par
Import: Release 10.2.0.1.0 - Production on Sunday, 17 May, 2009 14:06:51
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": parfile=schemaimp1.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Import> stop_job
Are you sure you wish to stop this job ([yes]/no): yes
C:\impexpdp>
When we want to stop the job, we need press Control-M to return Import> prompt. Once it is returned to prompt(Import>), we can stop the job as above by using stop_job command.
After the job is stoped, here is the job status.
SQL> select state,job_name from dba_datapump_jobs;
STATE JOB_NAME
------------------------------ ------------------------------
NOT RUNNING SYS_IMPORT_FULL_01
SQL>
Now we are attaching job again..... Attaching the job does not restart the job.
C:\impexpdp>impdp system/password@ordb attach=SYS_IMPORT_FULL_01
Import: Release 10.2.0.1.0 - Production on Sunday, 17 May, 2009 14:17:11
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Job: SYS_IMPORT_FULL_01
Owner: SYSTEM
Operation: IMPORT
Creator Privs: FALSE
GUID: 54AD9D6CF9B54FC4823B1AF09C2DC723
Start Time: Sunday, 17 May, 2009 14:17:12
Mode: FULL
Instance: ordb
Max Parallelism: 1
EXPORT Job Parameters:
CLIENT_COMMAND parfile=schemaexp1.par
IMPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND parfile=schemaimp1.par
TABLE_EXISTS_ACTION REPLACE
State: IDLING
Bytes Processed: 1,086,333,016
Percent Done: 44
Current Parallelism: 1
Job Error Count: 0
Dump File: c:/impexpdp\networkexp1.dmp
Worker 1 Status:
State: UNDEFINED
Import>
After attaching the job, here is the job status.
SQL> select state,job_name from dba_datapump_jobs;
STATE JOB_NAME
------------------------------ ------------------------------
IDLING SYS_IMPORT_FULL_01
SQL>
Attaching the job does not resume the job. Now we are resuming job again.....
Import> continue_client
Job SYS_IMPORT_FULL_01 has been reopened at Sunday, 17 May, 2009 14:17
Restarting "SYSTEM"."SYS_IMPORT_FULL_01": parfile=schemaimp1.par
SQL> select state,job_name from dba_datapump_jobs;
STATE JOB_NAME
------------------------------ ------------------------------
EXECUTING SYS_IMPORT_FULL_01
SQL>
Now again we are killing the same job.... Before we kill, we need to press Control-C to return the Import> prompt.
Import> kill_job
Are you sure you wish to stop this job ([yes]/no): yes
C:\impexpdp>
Now the job is disappared in the database.
SQL> select state,job_name from dba_datapump_jobs;
no rows selected
SQL>
from Govind
Following are the process involved in the data pump operation:
Client Process : This process is initiated by client utility. This process makes a call to the data pump API. Once the data pump is initiated, this process is not necessary for the progress of the job.
Shadow Process : When client log into the database, foreground process is created. It services the client data pump API requests. This process creates the master table and creates Advanced queuing queues used for communication. Once client process ends, shadow process also go away.
Master Control Process : MCP controls the execution of the data pump job. There is one MCP per job. MCP divides the data pump job into various metadata and data load or unload jobs and hands them over to the worker processes.
Worker Process : MCP creates worker process based on the valule of the PARALLEL parameter. The worker process performs the task requested by MCP.
Advantage of Data pump
1. We can perform export in parallel. It can also write to multiple files on different disks. (Specify parameters PARALLEL=2 and the two directory names with file specification DUMPFILE=ddir1:/file1.dmp, DDIR2:/file2.dmp)
2. Has ability to attach and detach from job, monitor the job progress remotely.
3. Has more option to filter metadata objects. Ex, EXCLUDE, INCLUDE
4. ESTIMATE_ONLY option can be used to estimate disk space requirements before performs the job
5. Data can be exported from remote database by using Database link
6. Explicit DB version can be specified, so only supported object types are exported.
7. During impdp, we can change the target file names, schema, and tablespace. Ex, REMAP_SCHEMA, REMAP_DATAFILES, REMAP_TABLESPACE
8. Has the option to filter data rows during impdp. Traditional exp/imp, we have this filter option only in exp. But here we have filter option on both impdp, expdp.
9. Data can be imported from one DB to another without writing to dump file, using NETWORK_LINK parameter.
10. Data access methods are decided automatically. In traditional exp/imp, we specify the value for the parameter DIRECT. But here, it decides where direct path can not be used , conventional path is used.
11. Job status can be queried directly from data dictionary(For example, dba_datapump_jobs, dba_datapump_sessions etc)
Exp & Expdp common parameters: These below parameters exists in both traditional exp and expdp utility.
FILESIZE
FLASHBACK_SCN
FLASHBACK_TIME
FULL
HELP
PARFILE
QUERY
TABLES
TABLESPACES
TRANSPORT_TABLESPACES(exp value is Y/N, expdp value is name of the tablespace)
Comparing exp & expdp parameters: These below parameters are equivalent parameters between exp & expdp. Exp and corresponding Expdp parameters...
FEEDBACK => STATUS
FILE => DUMPFILE
LOG => LOGFILE
OWNER => SCHEMAS
TTS_FULL_CHECK => TRANSPROT_FULL_CHECK
New parameters in expdp Utility
ATTACH Attach the client session to existing data pump jobs
CONTENT Specify what to export(ALL, DATA_ONLY, METADATA_ONLY)
DIRECTORY Location to write the dump file and log file.
ESTIMATE Show how much disk space each table in the export job consumes.
ESTIMATE_ONLY It estimate the space, but does not perform export
EXCLUDE List of objects to be excluded
INCLUDE List of jobs to be included
JOB_NAME Name of the export job
KEEP_MASTER Specify Y not to drop the master table after export
NETWORK_LINK Specify dblink to export from remote database
NOLOGFILE Specify Y if you do not want to create log file
PARALLEL Specify the maximum number of threads for the export job
VERSION DB objects that are incompatible with the specified version will not be exported.
ENCRYPTION_PASSWORD The table column is encrypted, then it will be written as clear text in the dump file set when the password is not specified. We can define any string as a password for this parameter.
COMPRESSION Specifies whether to compress metadata before writing to the dump file set. The default is METADATA_ONLY. We have two values(METADATA_ONLY,NONE). We can use NONE if we want to disable during the expdp.
SAMPLE - Allows you to specify a percentage of data to be sampled and unloaded from the source database. The sample_percent indicates the probability that a block of rows will be selected as part of the sample.
Imp & Impdp common parameters: These below parameters exist in both traditional imp and impdp utility.
FULL
HELP
PARFILE
QUERY
SKIP_UNUSABLE_INDEXES
TABLES
TABLESPACES
Comparing imp & impdp parameters: These below parameters are equivalent parameters between imp & impdp. imp and corresponding impdp parameters...
DATAFILES => TRANSPORT_DATAFILES
DESTROY =>REUSE_DATAFILES
FEEDBACK =>STATUS
FILE =>DUMPFILE
FROMUSER =>SCHEMAS, REMAP_SCHEMAS
IGNORE =>TABLE_EXISTS_ACTION(SKIP,APPEND,TRUNCATE,REPLACE)
INDEXFILE, SHOW=>SQLFILE
LOG =>LOGFILE
TOUSER =>REMAP_SCHEMA
New parameters in impdp Utility
FLASHBACK_SCN Performs import operation that is consistent with the SCN specified from the source database. Valid only when NETWORK_LINK parameter is used.
FLASHBACK_TIME Similar to FLASHBACK_SCN, but oracle finds the SCN close to the time specified.
NETWORK_LINK Performs import directly from a source database using database link name specified in the parameter. The dump file will be not be created in server when we use this parameter. To get a consistent export from the source database, we can use the FLASHBACK_SCN or FLASHBACK_TIME parameters. These two parameters are only valid when we use NETWORK_LINK parameter.
REMAP_DATAFILE Changes name of the source DB data file to a different name in the target.
REMAP_SCHEMA Loads objects to a different target schema name.
REMAP_TABLESPACE Changes name of the source tablespace to a different name in the target.
TRANSFORM We can specify that the storage clause should not be generated in the DDL for import. This is useful if the storage characteristics of the source and target database are different. The valid values are SEGMENT_ATTRIBUTES, STORAGE. STORAGE removes the storage clause from the CREATE statement DDL, whereas SEGMENT_ATTRIBUTES removes physical attributes, tablespace, logging, and storage attributes.
TRANSFORM = name:boolean_value[:object_type], where boolean_value is Y or N.
For instance, TRANSFORM=storage:N:table
ENCRYPTION_PASSWORD It is required on an import operation if an encryption password was specified on the export operation.
CONTENT, INCLUDE, EXCLUDE are same as expdp utilities.
Prerequisite for expdp/impdp:
Set up the dump location in the database.
system@orcl> create directory dumplocation
2 as 'c:/dumplocation';
Directory created.
system@orcl> grant read,write on directory dumploc to scott;
Grant succeeded.
system@orcl>
Let us experiment expdp & impdp utility as different scenario...... We have two database orcl, ordb. All the below scenarios are tested in Oracle10g R2 version.
Scenario1 Export the whole orcl database.
Export Parfile content:
userid=system/password@orcl
dumpfile=expfulldp.dmp
logfile=expfulldp.log
full=y
directory=dumplocation
Scenario2 Export the scott schema from orcl and import into ordb database. While import, exclude some objects(sequence,view,package,cluster,table). Load the objects which came from RES tablespace into USERS tablespace in target database.
Export Parfile content:
userid=system/password@orcl
dumpfile=schemaexpdb.dmp
logfile=schemaexpdb.log
directory=dumplocation
schemas=scott
Import parfile content:
userid=system/password@ordb
dumpfile=schemaexpdb.dmp
logfile=schemaimpdb.log
directory=dumplocation
table_exists_action=replace
remap_tablespace=res:users
exclude=sequence,view,package,cluster,table:"in('LOAD_EXT')"
Scenario3 Export the emp table from scott schema at orcl instance and import into ordb instance.
Expdb parfile content:
userid=system/password@orcl
logfile=tableexpdb.log
directory=dumplocation
tables=scott.part_emp
dumpfile=tableexpdb.dmp
Impdp parfile content:
userid=system/password@ordb
dumpfile=tableexpdb.dmp
logfile=tabimpdb.log
directory=dumplocation
table_exists_action=REPLACE
Scenario4 Export only specific partition in emp table from scott schema at orcl and import into ordb database.
Expdp parfile content:
userid=system/password@orcl
dumpfile=partexpdb.dmp
logfile=partexpdb.log
directory=dumplocation
tables=scott.part_emp:part10,scott.part_emp:part20
Impdp parfile content: If we want to overwrite the exported data in target database, then we need to delete emp table for deptno in(10,20).
scott@ordb> delete part_emp where deptno=10;
786432 rows deleted.
scott@ordb> delete part_emp where deptno=20;
1310720 rows deleted.
scott@ordb> commit;
Commit complete.
userid=system/password@ordb
dumpfile=partexpdb.dmp
logfile=tabimpdb.log
directory=dumplocation
table_exists_action=append
Scenario5 Export only tables in scott schema at orcl and import into ordb database.
Expdp parfile content:
userid=system/password@orcl
dumpfile=schemaexpdb.dmp
logfile=schemaexpdb.log
directory=dumplocation
include=table
schemas=scott
Impdp parfile content:
userid=system/password@orcl
dumpfile=schemaexpdb.dmp
logfile=schemaimpdb.log
directory=dumplocation
table_exists_action=replace
Scenario6 Export only rows belonging to department 10 and 20 in emp and dept table from orcl database. Import the dump file in ordb database. While importing, load only deptno 10 in target database.
Expdp parfile content:
userid=system/password@orcl
dumpfile=data_filter_expdb.dmp
logfile=data_filter_expdb.log
directory=dumplocation
content=data_only
schemas=scott
include=table:"in('EMP','DEPT')"
query="where deptno in(10,20)"
Impdp parfile content:
userid=system/password@ordb
dumpfile=data_filter_expdb.dmp
logfile=data_filter_impdb.log
directory=dumplocation
schemas=scott
query="where deptno = 10"
table_exists_action=APPEND
Scenario7 Export the scott schema from orcl database and split the dump file into 50M sizes. Import the dump file into ordb datbase.
Expdp parfile content:
userid=system/password@orcl
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
filesize=50M
schemas=scott
include=table
As per the above expdp parfile, initially, schemaexp_split_01.dmp file will be created. Once the file is 50MB, the next file called schemaexp_split_02.dmp will be created. Let us say, the dump file size is 500MB, then it creates 10 dump file as each file size is 50MB.
Impdp parfile content:
userid=system/password@ordb
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
table_exists_action=replace
remap_tablespace=res:users
exclude=grant
Scenario8 Export the scott schema from orcl database and split the dump file into four files. Import the dump file into ordb datbase.
Expdp parfile content:
userid=system/password@orcl
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
parallel=4
schemas=scott
include=table
As per the above parfile content, initially four files will be created - schemaexp_split_01.dmp, schemaexp_split_02.dmp, schemaexp_split_03.dmp, schemaexp_split_04.dmp. Notice that every occurrence of the substation variable is incremented each time. Since there is no FILESIZE parameter, no more files will be created.
Impdp parfile content:
userid=system/password@ordb
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=schemaexp_split_%U.dmp
table_exists_action=replace
remap_tablespace=res:users
exclude=grant
Scenario9 Export the scott schema from orcl database and split the dump file into three files. The dump files will be stored in three different location. This method is especially useful if you do not have enough space in one file system to perform the complete expdp job. After export is successful, import the dump file into ordb database.
Expdp parfile content:
userid=system/password@orcl
logfile=schemaexp_split.log
directory=dumplocation
dumpfile=dump1:schemaexp_%U.dmp,dump2:schemaexp_%U.dmp,dump3:schemaexp_%U.dmp
filesize=50M
schemas=scott
include=table
As per above expdp par file content, it place the dump file into three different location. Let us say, entire expdp dump file size is 1500MB. Then it creates 30 dump files(each dump file size is 50MB) and place 10 files in each file system.
Impdp parfile content:
userid=system/password@ordb
logfile=schemaimp_split.log
directory=dumplocation
dumpfile=dump1:schemaexp_%U.dmp,dump2:schemaexp_%U.dmp,dump3:schemaexp_%U.dmp
table_exists_action=replace
Scenario10 We are in orcl database server. Now export the ordb data and place the dump file in orcl database server. After expdp is successful, import the dump file into orcl database. When we use network_link, the expdp user and source database schema users should have identical privileges. If there no identical privileges, then we get the below error.
C:\impexpdp>expdp parfile=networkexp1.par
Export: Release 10.2.0.1.0 - Production on Sunday, 17 May, 2009 12:06:40
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user
Expdp parfile content:
userid=scott/tiger@orcl
logfile=netwrokexp1.log
directory=dumplocation
dumpfile=networkexp1.dmp
schemas=scott
include=table
network_link=ordb
As per the above parfile, expdp utility exports the ordb database data and place the dump file in orcl server. Since we are running expdp in orcl server. This is basically exporting the data from remote database.
Impdp parfile content:
userid=system/password@orcl
logfile=networkimp1.log
directory=dumplocation
dumpfile=networkexp1.dmp
table_exists_action=replace
Scenario11 Export scott schema in orcl and import into ordb. But do not write dump file in server. The expdp and impdp should be completed with out writing dump file in the server. Here we do not need to export the data. We can import the data without creating the dumpfile.
Here we run the impdp in ordb server and it contacts orcl DB and extract the data and import into ordb database. If we do not have much space in the file system to place the dump file, then we can use this option to load the data.
Impdp parfile content:
userid=scott/tiger@ordb
network_link=orcl
logfile=networkimp2.log
directory=dumplocation
table_exists_action=replace
Scenario12 Expdp scott schema in ordb and impdp the dump file in training schema in ordb database.
Expdp parfile content:
userid=scott/tiger@orcl
logfile=netwrokexp1.log
directory=dumplocation
dumpfile=networkexp1.dmp
schemas=scott
include=table
Impdp parfile content:
userid=system/password@ordb
logfile=networkimp1.log
directory=dumplocation
dumpfile=networkexp1.dmp
table_exists_action=replace
remap_schema=scott:training
Scenario 13 Expdp table on orcl database and imdp in ordb. When we export the data, export only 20 percent of the table data. We use SAMPLE parameter to accomplish this task.
SAMPLE parameter allows you to export subsets of data by specifying the percentage of data to be sampled and exported. The sample_percent indicates the probability that a block of rows will be selected as part of the sample. It does not mean that the database will retrieve exactly that amount of rows from the table. The value you supply for sample_percent can be anywhere from .000001 up to, but not including, 100.
If no table is specified, then the sample_percent value applies to the entire export job. The SAMPLE parameter is not valid for network exports.
Expdp parfile content:
userid=system/password@orcl
dumpfile=schemaexpdb.dmp
logfile=schemaexpdb.log
directory=dumplocation
tables=scott.part_emp
SAMPLE=20
As per the above expdp parfile, it exports only 20 percent of the data in part_emp table.
Impdp parfile content:
userid=system/password@ordb
dumpfile=schemaexpdb.dmp
logfile=schemaimpdb.log
directory=dumplocation
table_exists_action=replace
Managing Data Pump jobs
The datapump clients expdp and impdp provide an interactive command interface. Since each expdp and impdp operation has a job name, you can attach to that job from any computer and monitor the job or make adjustment to the job.
Here are the data pump interactive commands.
ADD_FILE Adds another file or a file set to the DUMPFILE set.
CONTINUE_CLIENT Changes mode from interactive client to logging mode
EXIT_CLIENT Leaves the client session and discontinues logging but leaves the current job running.
KILL_JOB Detaches all currently attached client sessions and terminates the job
PARALLEL Increase or decrease the number of threads
START_JOB Starts(or resume) a job that is not currently running. SKIP_CURRENT option can skip the recent failed DDL statement that caused the job to stop.
STOP_JOB stops the current job, the job can be restarted later
STATUS Displays detailed status of the job, the refresh interval can be specified in seconds. The detailed status is displayed to the output screen but not written to the log file.
Scenario14 Let us start the job and in between, we stop the job in middle and resume the job. After some time, let us kill the job and check the job status for every activity....
We can find what jobs are running currently in the database by using the below query.
SQL> select state,job_name from dba_datapump_jobs;
STATE JOB_NAME
------------------------------ ------------------------------
EXECUTING SYS_IMPORT_FULL_01
SQL>
C:\impexpdp>impdp parfile=schemaimp1.par
Import: Release 10.2.0.1.0 - Production on Sunday, 17 May, 2009 14:06:51
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": parfile=schemaimp1.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Import> stop_job
Are you sure you wish to stop this job ([yes]/no): yes
C:\impexpdp>
When we want to stop the job, we need press Control-M to return Import> prompt. Once it is returned to prompt(Import>), we can stop the job as above by using stop_job command.
After the job is stoped, here is the job status.
SQL> select state,job_name from dba_datapump_jobs;
STATE JOB_NAME
------------------------------ ------------------------------
NOT RUNNING SYS_IMPORT_FULL_01
SQL>
Now we are attaching job again..... Attaching the job does not restart the job.
C:\impexpdp>impdp system/password@ordb attach=SYS_IMPORT_FULL_01
Import: Release 10.2.0.1.0 - Production on Sunday, 17 May, 2009 14:17:11
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Job: SYS_IMPORT_FULL_01
Owner: SYSTEM
Operation: IMPORT
Creator Privs: FALSE
GUID: 54AD9D6CF9B54FC4823B1AF09C2DC723
Start Time: Sunday, 17 May, 2009 14:17:12
Mode: FULL
Instance: ordb
Max Parallelism: 1
EXPORT Job Parameters:
CLIENT_COMMAND parfile=schemaexp1.par
IMPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND parfile=schemaimp1.par
TABLE_EXISTS_ACTION REPLACE
State: IDLING
Bytes Processed: 1,086,333,016
Percent Done: 44
Current Parallelism: 1
Job Error Count: 0
Dump File: c:/impexpdp\networkexp1.dmp
Worker 1 Status:
State: UNDEFINED
Import>
After attaching the job, here is the job status.
SQL> select state,job_name from dba_datapump_jobs;
STATE JOB_NAME
------------------------------ ------------------------------
IDLING SYS_IMPORT_FULL_01
SQL>
Attaching the job does not resume the job. Now we are resuming job again.....
Import> continue_client
Job SYS_IMPORT_FULL_01 has been reopened at Sunday, 17 May, 2009 14:17
Restarting "SYSTEM"."SYS_IMPORT_FULL_01": parfile=schemaimp1.par
SQL> select state,job_name from dba_datapump_jobs;
STATE JOB_NAME
------------------------------ ------------------------------
EXECUTING SYS_IMPORT_FULL_01
SQL>
Now again we are killing the same job.... Before we kill, we need to press Control-C to return the Import> prompt.
Import> kill_job
Are you sure you wish to stop this job ([yes]/no): yes
C:\impexpdp>
Now the job is disappared in the database.
SQL> select state,job_name from dba_datapump_jobs;
no rows selected
SQL>
from Govind
Wednesday, March 16, 2011
at command in linux
Purpose
Runs commands at a later time.
Syntax
To Schedule Jobs to Run at a Later Time
18-march in 00:00 am
at -t 03180000
/usr/tivoli/tsm/script/rman_full.sh > /usr/tivoli/tsm/script/RMANLOG.log
^D
at now
/usr/tivoli/tsm/script/rman_full.sh > /usr/tivoli/tsm/script/RMANLOG.log
^D
To Report Scheduled Jobs
at -l [ -v ] [ -o ] [ Job ... | -q Queue ]
at -n [ User ]
To Remove Scheduled Jobs
at -r [ -F ] [ -i ] Job ...
at -r [ -F ] [ -i ] -u User
Runs commands at a later time.
Syntax
To Schedule Jobs to Run at a Later Time
18-march in 00:00 am
at -t 03180000
/usr/tivoli/tsm/script/rman_full.sh > /usr/tivoli/tsm/script/RMANLOG.log
^D
at now
/usr/tivoli/tsm/script/rman_full.sh > /usr/tivoli/tsm/script/RMANLOG.log
^D
To Report Scheduled Jobs
at -l [ -v ] [ -o ] [ Job ... | -q Queue ]
at -n [ User ]
To Remove Scheduled Jobs
at -r [ -F ] [ -i ] Job ...
at -r [ -F ] [ -i ] -u User
Sunday, March 6, 2011
Count files in a directory
dir /a-d | find /c ".dmp" > D:\LOG\count_HLR01_%DATE:~7,2%_%DATE:~4,2%_%DATE:~10,4%.LOG
set /p count_HLR01=
set /p count_HLR01=
Wednesday, March 2, 2011
DROP TEMPORARY TABLESPACE
SQL>
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u02/oradata/HLRMAIN/TEMP2.DBF' SIZE 100M
AUTOEXTEND ON NEXT 1M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M ;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
Database altered.
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
Database altered.
SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
SQL> SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name = 'TEMP';
The normal size of my temporary tablespace is 512MB which has always been more than adequate for this environment.
A bad query, however, increased the size of this tablespace (my tempfile) to over 13GB and I would like to reclaim that space.
In the example below, I simply drop and recreate the tempfile:
SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING DATAFILES;
Database altered.
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 512m
AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;
Tablespace altered
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u02/oradata/HLRMAIN/TEMP2.DBF' SIZE 100M
AUTOEXTEND ON NEXT 1M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M ;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
Database altered.
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
Database altered.
SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
SQL> SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name = 'TEMP';
The normal size of my temporary tablespace is 512MB which has always been more than adequate for this environment.
A bad query, however, increased the size of this tablespace (my tempfile) to over 13GB and I would like to reclaim that space.
In the example below, I simply drop and recreate the tempfile:
SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING DATAFILES;
Database altered.
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 512m
AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;
Tablespace altered
Wednesday, February 23, 2011
Metadata processing is not available
ORA-39213: Metadata processing is not available
Cause: The Data Pump could not use the Metadata API.
Typically, this is caused by the XSL stylesheets not being set up properly.
Action:
Connect AS SYSDBA and execute dbms_metadata_util.load_stylesheets
to reload the stylesheets.
Cause: The Data Pump could not use the Metadata API.
Typically, this is caused by the XSL stylesheets not being set up properly.
Action:
Connect AS SYSDBA and execute dbms_metadata_util.load_stylesheets
to reload the stylesheets.
Monday, February 7, 2011
Killing Oracle Sessions
Make sure you know what you are doing There are a number of ways to kill rogue sessions both within Oracle and externally.
Identify the Session to be Killed
ALTER SYSTEM KILL SESSION
ALTER SYSTEM DISCONNECT SESSION
The Windows Approach
The UNIX Approach
Identify the Session to be Killed
Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.
Identify the offending session using the V$SESSION or GV$SESSION view as follows.
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 30 15 3859 TEST sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 23 287 3834 SYS sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 40 387 4663 oracle@oel5-11gr2.localdomain (J000)
1 38 125 4665 oracle@oel5-11gr2.localdomain (J001)
The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.
ALTER SYSTEM KILL SESSION
The basic syntax for killing a session is shown below.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#@inst_id';
The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.
In addition to the syntax described above, you can add the IMMEDIATE clause.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.
If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it's worth checking to see if it is performing a rollback. You can do this by running this script (session_undo.sql). If the USED_UREC value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.
ALTER SYSTEM DISCONNECT SESSION
Oracle 11g introduced the ALTER SYSTEM DISCONNECT SESSION syntax as a new method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately.
The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.
SQL> alter system disconnect session '30,7';
alter system disconnect session '30,7'
*
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword
SQL>
The inclusion of this command in 11g means you should no longer need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.
The Windows Approach
To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.
C:> orakill ORACLE_SID spid
The session thread should be killed immediately and all resources released.
The UNIX Approach
To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.
% kill spid
If after a few minutes the process hasn't stopped, terminate the session using the following.
% kill -9 spid
If in doubt check that the SPID matches the UNIX PROCESSID shown using.
% ps -ef | grep ora
The session thread should be killed immediately and all resources released.
Identify the Session to be Killed
ALTER SYSTEM KILL SESSION
ALTER SYSTEM DISCONNECT SESSION
The Windows Approach
The UNIX Approach
Identify the Session to be Killed
Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.
Identify the offending session using the V$SESSION or GV$SESSION view as follows.
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 30 15 3859 TEST sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 23 287 3834 SYS sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 40 387 4663 oracle@oel5-11gr2.localdomain (J000)
1 38 125 4665 oracle@oel5-11gr2.localdomain (J001)
The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.
ALTER SYSTEM KILL SESSION
The basic syntax for killing a session is shown below.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#@inst_id';
The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.
In addition to the syntax described above, you can add the IMMEDIATE clause.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.
If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it's worth checking to see if it is performing a rollback. You can do this by running this script (session_undo.sql). If the USED_UREC value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.
ALTER SYSTEM DISCONNECT SESSION
Oracle 11g introduced the ALTER SYSTEM DISCONNECT SESSION syntax as a new method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately.
The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.
SQL> alter system disconnect session '30,7';
alter system disconnect session '30,7'
*
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword
SQL>
The inclusion of this command in 11g means you should no longer need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.
The Windows Approach
To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.
C:> orakill ORACLE_SID spid
The session thread should be killed immediately and all resources released.
The UNIX Approach
To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.
% kill spid
If after a few minutes the process hasn't stopped, terminate the session using the following.
% kill -9 spid
If in doubt check that the SPID matches the UNIX PROCESSID shown using.
% ps -ef | grep ora
The session thread should be killed immediately and all resources released.
Thursday, January 27, 2011
flashback tables
to flashback update
select * from v$database;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
begin
dbms_logmnr.start_logmnr
(
starttime => '27-JAN-2011 11:25:10',
endtime => '27-JAN-2011 11:28:35',
options => dbms_logmnr.dict_from_online_catalog +
dbms_logmnr.continuous_mine +
dbms_logmnr.no_sql_delimiter +
dbms_logmnr.print_pretty_sql
);
end;
/
select seg_name ,seg_owner ,username, xid , operation , sql_redo
from v$logmnr_contents
where seg_owner = 'CCARE'
and username = 'DBAHAKKI';
--where seg_name like 'ACC' ;
-- from sys user
execute dbms_logmnr.end_logmnr;
begin
dbms_flashback.TRANSACTION_BACKOUT
(
numtxns => 1 ,
xids => xid_array('01001900A70C0000'),
options => DBMS_FLASHBACK.CASCADE
);
end;
/
--to flashback delete
select * from table_name as of timestamp to_timestamp(‘2011-01-19 16:00:00’,’yyyy-mm-dd hh24:mi:ss’);
insert into table_name
select * from table_name as of timestamp to_timestamp(‘2011-01-19 16:00:00’,’yyyy-mm-dd hh24:mi:ss’);
SQL>commit;
select * from v$database;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
begin
dbms_logmnr.start_logmnr
(
starttime => '27-JAN-2011 11:25:10',
endtime => '27-JAN-2011 11:28:35',
options => dbms_logmnr.dict_from_online_catalog +
dbms_logmnr.continuous_mine +
dbms_logmnr.no_sql_delimiter +
dbms_logmnr.print_pretty_sql
);
end;
/
select seg_name ,seg_owner ,username, xid , operation , sql_redo
from v$logmnr_contents
where seg_owner = 'CCARE'
and username = 'DBAHAKKI';
--where seg_name like 'ACC' ;
-- from sys user
execute dbms_logmnr.end_logmnr;
begin
dbms_flashback.TRANSACTION_BACKOUT
(
numtxns => 1 ,
xids => xid_array('01001900A70C0000'),
options => DBMS_FLASHBACK.CASCADE
);
end;
/
--to flashback delete
select * from table_name as of timestamp to_timestamp(‘2011-01-19 16:00:00’,’yyyy-mm-dd hh24:mi:ss’);
insert into table_name
select * from table_name as of timestamp to_timestamp(‘2011-01-19 16:00:00’,’yyyy-mm-dd hh24:mi:ss’);
SQL>commit;
Monday, January 17, 2011
how to find what is locking my table
1-
SELECT LPAD(' ',DECODE(l.xidusn,0,3,0)) || l.oracle_username "User Name",o.owner, o.object_name, o.object_type
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER BY o.object_id, 1 desc
2-
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
3-
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id, owner || '.' || object_name object,object_type
, decode( l.block,0, 'Not Blocking',1, 'Blocking',2, 'Global') status
, decode(v.locked_mode,0, 'None',1, 'Null',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share',5, 'S/Row-X (SSX)',6, 'Exclusive'
, TO_CHAR(lmode)) mode_held
from v$locked_object v, dba_objects d, v$lock l, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username, session_id
to kill the session
ALTER SYSTEM KILL SESSION 'sid,serial#';
SELECT LPAD(' ',DECODE(l.xidusn,0,3,0)) || l.oracle_username "User Name",o.owner, o.object_name, o.object_type
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER BY o.object_id, 1 desc
2-
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
3-
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id, owner || '.' || object_name object,object_type
, decode( l.block,0, 'Not Blocking',1, 'Blocking',2, 'Global') status
, decode(v.locked_mode,0, 'None',1, 'Null',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share',5, 'S/Row-X (SSX)',6, 'Exclusive'
, TO_CHAR(lmode)) mode_held
from v$locked_object v, dba_objects d, v$lock l, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username, session_id
to kill the session
ALTER SYSTEM KILL SESSION 'sid,serial#';
Tuesday, January 4, 2011
Case Sensitive Passwords in Oracle Database 11g
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL>
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
System altered.
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL>
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
System altered.
SQL>
Monday, January 3, 2011
Temporary Tablespace Groups
Temporary Tablespace Groups
Tablespace groups allow users to use more than one tablespace to store temporary segments. The tablespace group is created implicitly when the first tablespace is assigned to it:
-- Create group by adding existing tablespace.
ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;
-- Add a new tablespace to the group.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M
TABLESPACE GROUP temp_ts_group;The tablespaces assigned to a group can be viewed using:
SELECT * FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
TEMP_TS_GROUP TEMP2
2 rows selected.Once the group is created it can be assigned just like a tablespace to a user or as the default temporary tablespace:
-- Assign group as the temporary tablespace for a user.
ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;
-- Assign group as the default temporary tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;A tablespace can be removed from a group using:
ALTER TABLESPACE temp2 TABLESPACE GROUP '';
SELECT * FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
1 row selected.There is no theoretical maximum limit to the number of tablespaces in a tablespace group, but it must contain at least one. The group is implicitly dropped when the last member is removed. The last member of a group cannot be removed if the group is still assigned as the default temporary tablespace. In this example the following must be done to remove the last member from the group.
-- Switch from the group to a specific tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
-- Remove the tablespace from the group.
ALTER TABLESPACE temp TABLESPACE GROUP '';
-- Check the group has gone.
SELECT * FROM dba_tablespace_groups;
no rows selected
Tablespace groups allow users to use more than one tablespace to store temporary segments. The tablespace group is created implicitly when the first tablespace is assigned to it:
-- Create group by adding existing tablespace.
ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;
-- Add a new tablespace to the group.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M
TABLESPACE GROUP temp_ts_group;The tablespaces assigned to a group can be viewed using:
SELECT * FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
TEMP_TS_GROUP TEMP2
2 rows selected.Once the group is created it can be assigned just like a tablespace to a user or as the default temporary tablespace:
-- Assign group as the temporary tablespace for a user.
ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;
-- Assign group as the default temporary tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;A tablespace can be removed from a group using:
ALTER TABLESPACE temp2 TABLESPACE GROUP '';
SELECT * FROM dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
1 row selected.There is no theoretical maximum limit to the number of tablespaces in a tablespace group, but it must contain at least one. The group is implicitly dropped when the last member is removed. The last member of a group cannot be removed if the group is still assigned as the default temporary tablespace. In this example the following must be done to remove the last member from the group.
-- Switch from the group to a specific tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
-- Remove the tablespace from the group.
ALTER TABLESPACE temp TABLESPACE GROUP '';
-- Check the group has gone.
SELECT * FROM dba_tablespace_groups;
no rows selected
Wednesday, December 29, 2010
Kill Job in oracle expdp utility
When you are inside the session that started the expdp,
then just ctrl+C will get you to the
EXPDP> prompt from where you can issue KILL_JOB command to kill the session you are in.
Issue help for more commands in that control utility.
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([y]/n): y
The job is placed in a stopped state and exits the client.
After finishing his other work, the DBA wants to resume the export job and the client machine from where he actually started the job is locked because, the user has locked his/her cabin. So now the DBA will go to another client machine and he reattach to the job by typing the following command
$expdp system/oracle ATTACH=SYS_EXPORT_SCHEMA_01
expdp backup ATTACH=SYS_EXPORT_SCHEMA_16
After the job status is displayed, he can issue the CONTINUE_CLIENT command to resume logging mode and restart the myfulljob job.
Export> CONTINUE_CLIENT
select * from DBA_DATAPUMP_JOBS;
then just ctrl+C will get you to the
EXPDP> prompt from where you can issue KILL_JOB command to kill the session you are in.
Issue help for more commands in that control utility.
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([y]/n): y
The job is placed in a stopped state and exits the client.
After finishing his other work, the DBA wants to resume the export job and the client machine from where he actually started the job is locked because, the user has locked his/her cabin. So now the DBA will go to another client machine and he reattach to the job by typing the following command
$expdp system/oracle ATTACH=SYS_EXPORT_SCHEMA_01
expdp backup ATTACH=SYS_EXPORT_SCHEMA_16
After the job status is displayed, he can issue the CONTINUE_CLIENT command to resume logging mode and restart the myfulljob job.
Export> CONTINUE_CLIENT
select * from DBA_DATAPUMP_JOBS;
Sunday, December 26, 2010
Tuesday, December 14, 2010
recreate emctl
#emca -repos recreate
Database SID: bossarc
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
#emca -deconfig dbcontrol db
#emca -config dbcontrol db
Database SID: bossarc
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
#emca -deconfig dbcontrol db
#emca -config dbcontrol db
session browser
SELECT ROUND (BITAND (s.ownerid, 65535)) parent_session_sid,
ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,
s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.port,
s.terminal, UPPER (s.program) program, s.TYPE, s.sql_address,
s.sql_hash_value, s.sql_id, s.sql_child_number, s.sql_exec_start,
s.sql_exec_id, s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.top_level_call#, s.logon_time, s.last_call_et,
s.pdml_enabled, s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.final_blocking_session_status, s.final_blocking_instance,
s.final_blocking_session, s.seq#, s.event#, s.event, s.p1text, s.p1,
s.p1raw, s.p2text, s.p2, s.p2raw, s.p3text, s.p3, s.p3raw,
s.wait_class_id, s.wait_class#, s.wait_class, s.wait_time,
s.seconds_in_wait, s.state, s.wait_time_micro,
s.time_remaining_micro, s.time_since_last_wait_micro, s.service_name,
s.sql_trace, s.sql_trace_waits, s.sql_trace_binds,
s.sql_trace_plan_stats, s.session_edition_id, s.creator_addr,
s.creator_serial#, s.ecid
FROM v$session s
WHERE ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND')
)
ORDER BY "USERNAME", ownerid
ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,
s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.port,
s.terminal, UPPER (s.program) program, s.TYPE, s.sql_address,
s.sql_hash_value, s.sql_id, s.sql_child_number, s.sql_exec_start,
s.sql_exec_id, s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.top_level_call#, s.logon_time, s.last_call_et,
s.pdml_enabled, s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.final_blocking_session_status, s.final_blocking_instance,
s.final_blocking_session, s.seq#, s.event#, s.event, s.p1text, s.p1,
s.p1raw, s.p2text, s.p2, s.p2raw, s.p3text, s.p3, s.p3raw,
s.wait_class_id, s.wait_class#, s.wait_class, s.wait_time,
s.seconds_in_wait, s.state, s.wait_time_micro,
s.time_remaining_micro, s.time_since_last_wait_micro, s.service_name,
s.sql_trace, s.sql_trace_waits, s.sql_trace_binds,
s.sql_trace_plan_stats, s.session_edition_id, s.creator_addr,
s.creator_serial#, s.ecid
FROM v$session s
WHERE ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND')
)
ORDER BY "USERNAME", ownerid
Monday, December 13, 2010
Wednesday, December 8, 2010
Monday, November 15, 2010
Database Migration with Unix-pipe and Export/Import Table of Contents
Database Migration with Unix-pipe and Export/Import Table of Contents
A. Introduction...............................
B. Required utilities.........................
1. What is Unix Pipe?.........................
2. What is import/export ?....................
3. How fast a table is imported?..............
C. Migration Scripts..........................
D. What I did to finish in 5 Hours............
E. Contact Information........................
A. Introduction Migration is the process of transforming from one version to another.
Export/Import is only available utility to migrate the database across the platform.
Migration of production database across the platform using export/import needs downtime and this effort becomes critical for those companies
whose production database is 24x7 and can’t afford long duration of production downtime.
The aim of this document is to share the technical knowledge of using export/import with Unix pipe,
which needs minimal downtime.
Before finalizing this scripts I tested all combinations of export/import.
The results are based on my experience, which may vary and depends upon server capacity.
It took a little time to fix the scripts but we migrated the production database of 160 GB in 5 hours.
B. Required utilities Migration scripts need following three utilities.
Unix pipe
Oracle export (exp)
Oracle import (imp)
1. What is Unix Pipe?
In Unix, apipe is a unidirectional, stream communication abstraction.
One process writes to the ``write end'' of the pipe,
and a second process reads from the ``read end'' of the pipe.
Its very useful utility and I have used in the scripts .
2. What is import/export ?
The Oracle export (EXP) and import (IMP) utilities are used to perform logical database backup and recovery.
They are also used to move Oracle data from one machine,
database or schema to another.
3. How fast a table is imported?
If you need to monitor how fast rows are imported from a running import job, try following sql :
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-ddhh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
C. Migration Scripts Save your export parameter file as exp_ALL.par
Save your import parameter file as imp_ALL.par
Save the following scripts as remote_exp_imp_all.ksh
Run the scripts from unix prompt as
remote_exp_imp_all.ksh > remote_exp_imp_all.out 2>&1 &
--------------------------------Cut the scripts ----------------------------------------------
#!/bin/ksh
## Assumption
# REMOTE SERVER is server on which existing production database is running
# LOCAL SERVER is new server on which database will be imported.
# In this case remote server is saqhdp01 and database name is C4PR
# All scripts are executed from LOCAL SERVER.
# Only export parameter file is required on REMOTE SERVER
# exp_ALL.par is export parameter file
# imp_ALL.par is import parameter file
set -xv . $HOME/C4PR.env
# C4PR.env sets Database environment TIME_START=`date +%T` touch start_ALL R_SERVER=saqhdp01
# Remote database server REMOTE_HOME=/u333/app
# Location on remote server L_PIPE=$HOME/upgrade/imp_pipe_ALL
# Local Unix Pipe R_PIPE=$REMOTE_HOME/upgrade/exp_pipe_ALL
# Remote Unix Pipe
# Create the local and remote pipes and change the permissions
# Remove the files prior rsh $R_SERVER "/bin/rm $R_PIPE" /usr/bin/rm $L_PIPE rsh $R_SERVER "/etc/mknod $R_PIPE p;
#chmod 777 $R_PIPE" /usr/sbin/mknod $L_PIPE p
# Start the import from the pipe in the background
#imp parfile=$HOME/upgrade/imp_ALL.par > $HOME/upgrade/imp_ALL.out 2>&1 &
# Start the export on the remote server in the background
#rsh $R_SERVER ". /u333/app/upgrade/C4PR.env;
exp parfile=/u333/app/upgrade/exp_ALL.par > /u333/app/upgrade/exp_ALL.out 2>&1" > ALL.out 2>&1 &
# cat (concatenate) the export pipe to the import pipe on the remote server
#rsh $R_SERVER "cat < $R_PIPE " | cat > $L_PIPE TIME_END=`date +%T` touch $HOME/upgrade/end_ALL
# Remove the local and remote pipes
rsh $R_SERVER "rm $R_PIPE"
rm $L_PIPE
mailx -s "Start/End time for piped export/import " <
D. What I did to finish in 5 Hours In order to achieve it, I performed various iterations.
At the end of each iteration results were analyzed and issues (problematic area) were resolved.
Also there were support from internal Engineering, Operations, DBA and management.
Following points were considered in final iterations .
• Gather the statistics from various iterations •
Create a blank database on RAID-1 file systems.
Since import is write intensive operations, RAID-1 takes at least 30% less time.
• Distributes the data files based on I/O pattern. STATSCPACK results are helpful
• Validate the parameters
disk_asynch_io = false
db_writer_processes = (No of cpu/2 )
_system_trig_enabled=FALSE
• Used a large buffer size. I used 100000000
• Created rollback segments of bigger size (initial 1 GB and next 100 MB ).
• Increased the online Redo log size . 200MB in my case .
• Drop the snapshot logs before export
• Disable the database triggers before export
• Set database in noarchivelog mode
• Using the above script , export/import the database structure only without indexes, constraints
• Fix the invalid objects and compare the objects
• With help of above scripts , Prepare unix-pipe scripts to run in parallel at user level .
Divide the users in multiple groups based on their timing so that all can finish around same time .
In our case the biggest table was MTL_MATERIAL_TRANSACTION of INV user with 14 Million rows and it was taking 3.5 hours.
So I divided the group in such way that all group should finish in 4 hours.
I used 4 set of unix-pipe scripts .
• Also wrote scripts to build the index parallel to import. \
I wrote the scripts which was checking the tables from imported logs on regular intervals (10 minutes)
and starts rebuilding the index automatically on those tables which were imported in last 10 minutes.
This way index rebuild was also completing on tables which were imported.
The remaining indexes were only on those tables which were imported in last 10 minutes.
This script reduced the index rebuild time .
• Finally compare the objects
E. Contact Information Send your questions and feedback atapp
A. Introduction...............................
B. Required utilities.........................
1. What is Unix Pipe?.........................
2. What is import/export ?....................
3. How fast a table is imported?..............
C. Migration Scripts..........................
D. What I did to finish in 5 Hours............
E. Contact Information........................
A. Introduction Migration is the process of transforming from one version to another.
Export/Import is only available utility to migrate the database across the platform.
Migration of production database across the platform using export/import needs downtime and this effort becomes critical for those companies
whose production database is 24x7 and can’t afford long duration of production downtime.
The aim of this document is to share the technical knowledge of using export/import with Unix pipe,
which needs minimal downtime.
Before finalizing this scripts I tested all combinations of export/import.
The results are based on my experience, which may vary and depends upon server capacity.
It took a little time to fix the scripts but we migrated the production database of 160 GB in 5 hours.
B. Required utilities Migration scripts need following three utilities.
Unix pipe
Oracle export (exp)
Oracle import (imp)
1. What is Unix Pipe?
In Unix, apipe is a unidirectional, stream communication abstraction.
One process writes to the ``write end'' of the pipe,
and a second process reads from the ``read end'' of the pipe.
Its very useful utility and I have used in the scripts .
2. What is import/export ?
The Oracle export (EXP) and import (IMP) utilities are used to perform logical database backup and recovery.
They are also used to move Oracle data from one machine,
database or schema to another.
3. How fast a table is imported?
If you need to monitor how fast rows are imported from a running import job, try following sql :
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-ddhh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
C. Migration Scripts Save your export parameter file as exp_ALL.par
Save your import parameter file as imp_ALL.par
Save the following scripts as remote_exp_imp_all.ksh
Run the scripts from unix prompt as
remote_exp_imp_all.ksh > remote_exp_imp_all.out 2>&1 &
--------------------------------Cut the scripts ----------------------------------------------
#!/bin/ksh
## Assumption
# REMOTE SERVER is server on which existing production database is running
# LOCAL SERVER is new server on which database will be imported.
# In this case remote server is saqhdp01 and database name is C4PR
# All scripts are executed from LOCAL SERVER.
# Only export parameter file is required on REMOTE SERVER
# exp_ALL.par is export parameter file
# imp_ALL.par is import parameter file
set -xv . $HOME/C4PR.env
# C4PR.env sets Database environment TIME_START=`date +%T` touch start_ALL R_SERVER=saqhdp01
# Remote database server REMOTE_HOME=/u333/app
# Location on remote server L_PIPE=$HOME/upgrade/imp_pipe_ALL
# Local Unix Pipe R_PIPE=$REMOTE_HOME/upgrade/exp_pipe_ALL
# Remote Unix Pipe
# Create the local and remote pipes and change the permissions
# Remove the files prior rsh $R_SERVER "/bin/rm $R_PIPE" /usr/bin/rm $L_PIPE rsh $R_SERVER "/etc/mknod $R_PIPE p;
#chmod 777 $R_PIPE" /usr/sbin/mknod $L_PIPE p
# Start the import from the pipe in the background
#imp parfile=$HOME/upgrade/imp_ALL.par > $HOME/upgrade/imp_ALL.out 2>&1 &
# Start the export on the remote server in the background
#rsh $R_SERVER ". /u333/app/upgrade/C4PR.env;
exp parfile=/u333/app/upgrade/exp_ALL.par > /u333/app/upgrade/exp_ALL.out 2>&1" > ALL.out 2>&1 &
# cat (concatenate) the export pipe to the import pipe on the remote server
#rsh $R_SERVER "cat < $R_PIPE " | cat > $L_PIPE TIME_END=`date +%T` touch $HOME/upgrade/end_ALL
# Remove the local and remote pipes
rsh $R_SERVER "rm $R_PIPE"
rm $L_PIPE
mailx -s "Start/End time for piped export/import " <
D. What I did to finish in 5 Hours In order to achieve it, I performed various iterations.
At the end of each iteration results were analyzed and issues (problematic area) were resolved.
Also there were support from internal Engineering, Operations, DBA and management.
Following points were considered in final iterations .
• Gather the statistics from various iterations •
Create a blank database on RAID-1 file systems.
Since import is write intensive operations, RAID-1 takes at least 30% less time.
• Distributes the data files based on I/O pattern. STATSCPACK results are helpful
• Validate the parameters
disk_asynch_io = false
db_writer_processes = (No of cpu/2 )
_system_trig_enabled=FALSE
• Used a large buffer size. I used 100000000
• Created rollback segments of bigger size (initial 1 GB and next 100 MB ).
• Increased the online Redo log size . 200MB in my case .
• Drop the snapshot logs before export
• Disable the database triggers before export
• Set database in noarchivelog mode
• Using the above script , export/import the database structure only without indexes, constraints
• Fix the invalid objects and compare the objects
• With help of above scripts , Prepare unix-pipe scripts to run in parallel at user level .
Divide the users in multiple groups based on their timing so that all can finish around same time .
In our case the biggest table was MTL_MATERIAL_TRANSACTION of INV user with 14 Million rows and it was taking 3.5 hours.
So I divided the group in such way that all group should finish in 4 hours.
I used 4 set of unix-pipe scripts .
• Also wrote scripts to build the index parallel to import. \
I wrote the scripts which was checking the tables from imported logs on regular intervals (10 minutes)
and starts rebuilding the index automatically on those tables which were imported in last 10 minutes.
This way index rebuild was also completing on tables which were imported.
The remaining indexes were only on those tables which were imported in last 10 minutes.
This script reduced the index rebuild time .
• Finally compare the objects
E. Contact Information Send your questions and feedback atapp
Oracle Export and Import Compress Scripts
Here are two scripts that export and import databases and make compressed files for Oracle. Tested on Oracle 8.0.5 and Sun 2.7 If the resulting compressed file is too big for the file system you can run the file through the Unix utility "split" to make smaller files.
#!/bin/ksh
# Export with flags (full, consistent) to a compressed file that
# is named by SID-month-day using Oracle exp
##############################################
# Main
# Configure environment
. oracle_environment.ksh # Oracle environment.
export ORACLE_SID=XXX1
DIRECTORY=/apps/home/oracle/local; export DIRECTORY
FILENAME=${DIRECTORY}/Exports/${ORACLE_SID}-`date +%d`.dmp.gz; export FILENAME
LOG=${DIRECTORY}/Exports/${ORACLE_SID}-`date +%d`.log; export LOG
PIPE=${DIRECTORY}/export_pipe.dmp; export PIPE
test -p ${PIPE} || mknod ${PIPE} p
chmod +rw ${PIPE}
cat $PIPE | /usr/local/bin/gzip > ${FILENAME} &
${ORACLE_HOME}/bin/exp log=${LOG} buffer=10485760 file=${PIPE} consistent=yes full=yes << EOF
system/password
EOF
#############################################################
#!/bin/ksh
# Oracle imp import for gzipped Oracle exp file.
##############################################
# Main
# Path of exp file.
FILENAME=$1; export FILENAME
PIPE=export_pipe.dmp; export PIPE
test -p ${PIPE} || mknod export_pipe.dmp p
chmod +rw ${PIPE}
/usr/local/bin/zcat ${FILENAME} > ${PIPE} &
${ORACLE_HOME}/bin/imp buffer=10485760 file=${PIPE} full=yes log=impzip.log << EOF
system/password
EOF
#########################################################
#!/bin/ksh
# Export with flags (full, consistent) to a compressed file that
# is named by SID-month-day using Oracle exp
##############################################
# Main
# Configure environment
. oracle_environment.ksh # Oracle environment.
export ORACLE_SID=XXX1
DIRECTORY=/apps/home/oracle/local; export DIRECTORY
FILENAME=${DIRECTORY}/Exports/${ORACLE_SID}-`date +%d`.dmp.gz; export FILENAME
LOG=${DIRECTORY}/Exports/${ORACLE_SID}-`date +%d`.log; export LOG
PIPE=${DIRECTORY}/export_pipe.dmp; export PIPE
test -p ${PIPE} || mknod ${PIPE} p
chmod +rw ${PIPE}
cat $PIPE | /usr/local/bin/gzip > ${FILENAME} &
${ORACLE_HOME}/bin/exp log=${LOG} buffer=10485760 file=${PIPE} consistent=yes full=yes << EOF
system/password
EOF
#############################################################
#!/bin/ksh
# Oracle imp import for gzipped Oracle exp file.
##############################################
# Main
# Path of exp file.
FILENAME=$1; export FILENAME
PIPE=export_pipe.dmp; export PIPE
test -p ${PIPE} || mknod export_pipe.dmp p
chmod +rw ${PIPE}
/usr/local/bin/zcat ${FILENAME} > ${PIPE} &
${ORACLE_HOME}/bin/imp buffer=10485760 file=${PIPE} full=yes log=impzip.log << EOF
system/password
EOF
#########################################################
Subscribe to:
Posts (Atom)