Oracle in World: 4/6/08

Saturday, April 12, 2008

Original Export and Import Versus Data Pump Export and Import

If you have worked with prior 10g database you possibly are familiar with exp/imp utilities of oracle database. Oracle 10g introduces a new feature called data pump export and import.Data pump export/import differs from original export/import. The difference is listed below.

1)Impdp/Expdp has self-tuning unities. Tuning parameters that were used in original Export and Import, such as BUFFER and RECORDLENGTH, are neither required nor supported by Data Pump Export and Import.

2)Data Pump represent metadata in the dump file set as XML documents rather than as DDL commands.

3)Impdp/Expdp use parallel execution rather than a single stream of execution, for improved performance.

4)In Data Pump expdp full=y and then impdp schemas=prod is same as of expdp schemas=prod and then impdp full=y where in original export/import does not always exhibit this behavior.

5)Expdp/Impdp access files on the server rather than on the client.

6)Expdp/Impdp operate on a group of files called a dump file set rather than on a single sequential dump file.

7)Sequential media, such as tapes and pipes, are not supported in oracle data pump.But in original export/import we could directly compress the dump by using pipes.

8)The Data Pump method for moving data between different database versions is different than the method used by original Export/Import.

9)When you are importing data into an existing table using either APPEND or TRUNCATE, if any row violates an active constraint, the load is discontinued and no data is loaded. This is different from original Import, which logs any rows that are in violation and continues with the load.

10)Expdp/Impdp consume more undo tablespace than original Export and Import.

11)If a table has compression enabled, Data Pump Import attempts to compress the data being loaded. Whereas, the original Import utility loaded data in such a way that if a even table had compression enabled, the data was not compressed upon import.

12)Data Pump supports character set conversion for both direct path and external tables. Most of the restrictions that exist for character set conversions in the original Import utility do not apply to Data Pump. The one case in which character set conversions are not supported under the Data Pump is when using transportable tablespaces.

13)There is no option to merge extents when you re-create tables. In original Import, this was provided by the COMPRESS parameter. Instead, extents are reallocated according to storage parameters for the target table.

Switch From DBMS_JOB to DBMS_SCHEDULAR

The previous database scheduler was DBMS_JOB. Now oracle database offers much more flexibility with the package DBMS_SCHEDULAR and typically it replaces DBMS_JOB package with enhance flexibility and comes with many more options.

In this post I will show you briefly how you can take statements created with DBMS_JOB and rewrite them using DBMS_SCHEDULER, which is the package that you use to configure and operate the Scheduler.

A)Creating a JOB:
----------------------
An example of creating a job using DBMS_JOB is the following:
SQL> desc a;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
B VARCHAR2(10)

VARIABLE jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'INSERT INTO a VALUES (1,''First'');',SYSDATE, 'SYSDATE+1/24/60');
COMMIT;
END;
/

An equivalent statement using DBMS_SCHEDULER is the following:

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'job1',
job_type => 'PLSQL_BLOCK',
job_action => 'INSERT INTO a values(1,''First'');',
start_date =>SYSDATE,
repeat_interval => 'FREQ =MINUTELY; INTERVAL = 1');
END;
/

B)Altering a Job:
-------------------------------------------
Using DBMS_JOB,

BEGIN
DBMS_JOB.WHAT(23, 'INSERT INTO a VALUES (2,''Second'');');
COMMIT;
END;
/
The jobid can be selected from query dba_jobs.

Using DBMS_SCHEDULAR,

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'JOB1',
attribute => 'job_action',
value => 'INSERT INTO a values(2,''Second'');');
END;
/

C)Removing a Job from the Job Queue
----------------------------------------------------

The following example removes a job using DBMS_JOB, where 22 is the number of the job being run:

BEGIN
DBMS_JOB.REMOVE(23);
COMMIT;
END;
/

Using DBMS_SCHEDULER, you would issue the following statement instead:

BEGIN
DBMS_SCHEDULER.DROP_JOB('job1');
END;
/

How to Create and Use OMF

OMF indicates Oracle Managed Files. With the use of Oracle-managed files the administration of an Oracle Database can be simplified. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle Database. You specify operations in terms of database objects rather than filenames.

Enable the Creation of OMFs
The following initialization parameters allow the database server to use the Oracle-managed files feature.

1)DB_CREATE_FILE_DEST: Defines the location of the default file system directory where the database creates datafiles or tempfiles when no file specification is given in the creation operation. It is also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

2)DB_CREATE_ONLINE_LOG_DEST_n:Defines the location of the default file system directory for redo log files and control file creation when no file specification is given in the creation operation. You can use this initialization parameter multiple times, where n specifies a multiplexed copy of the redo log or control file. You can specify up to five multiplexed copies.

3)DB_RECOVERY_FILE_DEST:Defines the location of the default file system directory where the database creates RMAN backups when no format option is used, archived logs when no other local destination is configured, and flashback logs. Also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

Both of these initialization parameters are dynamic, and can be set using the ALTER SYSTEM or ALTER SESSION statement.

An Example of using OMF :
1)Setting the parameter for the session:

SQL> alter session set db_create_file_dest='/oradata';
Session altered.

2)Create Tablespace using OMF:

SQL> create tablespace omf_tbs;
Tablespace created.

3)Check the data file Location:

SQL> select file_name from dba_data_files where tablespace_name='OMF_TBS';
FILE_NAME
--------------------------------------------------------------------------------
/oradata/ARJUT/datafile/o1_mf_omf_tbs_4049w4op_.dbf

Here ARJUT is the Database Name.

The dafault location for datafile is Your settings for parameter/Database Name/datafile/Unique Name.dbf

Thursday, April 10, 2008

How To Drop, Create And Recreate DB Control In 10g Database

This topic is divided into three sections.

A.Delete DB Control Objects
B.Create DB Control Objects
C.Recreate/ReConfig DB Control

A.Delete DB Control Objects:
There are various ways to delete DB Control Objects.
1).Delete DB Control Configuration Files using EMCA scripts
2).Delete DB Control Configuration Files Manually:
3).Delete DB Control Repository Objects using RepManager
4).Delete DB Control Repository Objects Manually
5).Delete DB Control Configuration Files and Repository Objects using EMCA

1).Delete DB Control Configuration Files using EMCA scripts
In 10.1g run, $emca -x sid
In 10.2g run, $emca -deconfig dbcontrol db Then enter sid as prompt and then y.

2).Delete DB Control Configuration Files Manually:

Remove the following directories from your filesystem:
$ORACLE_HOME/hostname_sid
$ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_sid

On Windows you also need to delete the DB Console service:
- run regedit
- navigate to HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services
- locate the OracleDBConsolesid entry and delete it

On, Windows XP and Windows Server 2003 you can run the following from the command line:
'sc delete service_name'

- where service_name is the DB Control service name (typically: OracleDBConsolesid)

3).Delete DB Control Repository using RepManager:
Invoke following command, RepManager hostname listener_port sid -action drop
But don't delete by RepManager. It puts the database in quiescence mode.

4).Delete DB Control Repository Objects Manually
In another post I will show it.

5).Delete DB Control Configuration Files and Repository Objects using EMCA

For 10.1,
>emca -x sid
>RepManager hostname listener_port sid -action drop

For 10.2,
>emca -deconfig dbcontrol db -repos drop

B.Create DB Control Objects
1)Create only DB Control configuration files:

on 10.1g ,>emca -r
On 10.2g, >emca -config dbcontrol db

2)Create both the DB Control Repository Objects and Configuration Files:

On 10.1g, >emca
On 10.2g, >emca -config dbcontrol db -repos create

C.Recreate/ReConfig DB Control:
1)Recreate only DB Control configuration files:
$ emca -config dbcontrol db

2)Recreate both the DB Control Repository Objects and Configuration Files:

$ emca -config dbcontrol db -repos recreate

Related Documents:
Troubleshooting Problems while Creating Repository using emca

Relocate Datafiles in Oracle Database.

Rename/relocate datafiles operation vary based on the log mode of the database. Database may be in archival mode or noarchive mode. In order to know the log mode of the database issue,
SELECT LOG_MODE FROM V$DATABASE;
If you get ARCHIVELOG the follow A).
If you get NOARCHIVELOG then follow B).
A)Archival Mode Operation
1)Determine the datafile on which you will do the operation,


SQL> select file_name,file_id from dba_data_files where tablespace_name='TEST_MOVE';

FILE_NAME       FILE_ID
------------------------------------------   -----------------
/oradata/Arju/test_move.dbf                        5

2)Take the tablespace of the specified datafile offline. Like, in case of Archivelog use,

SQL> alter database datafile 5 offline;
Database altered.

To use the form of the ALTER DATABASE statement, the database must be in ARCHIVELOG mode. This requirement prevents you from accidentally losing the datafile, since taking the datafile offline while in NOARCHIVELOG mode is likely to result in losing the file.

3)Move the datafile by using OS(cp/scp/copy) command or by using DBMS_FILE_TRANSFER package to you desired location.

SQL> !scp /oradata/Arju/test_move.dbf /oradata/Arju/arju/after_move.dbf

4)Rename the datafiles within the database.

SQL> alter database rename file '/oradata/Arju/test_move.dbf' to '/oradata/Arju/arju/after_move.dbf';
Database altered.

5)Recover the Datafile if needed.

SQL> alter database recover datafile 5;
Database altered.

6)Query the Data Dictionary to see the new Location.


SQL> select file_name,file_id from dba_data_files where tablespace_name='TEST_MOVE';

FILE_NAME                                   FILE_ID
-------------------------------------------------
/oradata/Arju/arju/after_move.dbf                 5

7)Backup the database.

Noarchival Mode operation
1)Shutdown the database.
SQL>Shut immediate;

2)Copy the desired datafile to your new location.
You can use cp/scp/copy/dd command or any GUI tool to copy datafile to new location.

3)Mount the database.
SQL>startup mount

4)Rename the datafile to update the controlfile information.
SQL> ALTER DATABASE RENAME FILE 'old_location' TO 'new_location';

5)Open the database for normal use.
SQL>Alter database open;

Related Documents
http://arjudba.blogspot.com/2008/08/how-to-resize-datafile.html

http://arjudba.blogspot.com/2008/05/drop-datafile-in-oracle.html

http://arjudba.blogspot.com/2008/05/recover-lost-datafile-without-backup.html

Copying Files Using the Oracle Database Server

We all know that with we can copy file from one location to another location using scp/cp( in unix/linux) or using copy/explorer/ctl+c(in windows). Within Oracle this can also be done to copy file from one location to another.

I will try to demonstrate it with example.

With Oracle we can achive our desired copy file task by two ways.

1)Using DBMS_FILE_TRANSFER Package.
2)Using Oracle Streams Propagation.

The 2nd Step inshallah will be shown in another of my threads about streams Replication.I here will discuss about DBMS_FILE_TRANSFER Package.

1)Using DBMS_FILE_TRANSFER Package.
---------------------------------------
Copying a File on a Local File System
-----------------------------------------
Using the COPY_FILE procedure in the DBMS_FILE_TRANSFER package we can copy a file on a local file system. With an example I will show you how you can copy a file named test.txt from directory to /export/home/Arju2

Step1:
---------
In SQL*Plus connect as a user who can grant privilege and create directory objects using SQL.

Step2:
-----------
Create a directory named SOURCE_DIR in which source file resides. Here it is /export/home/Arju

SQL> CREATE DIRECTORY SOURCE_DIR as '/export/home/oracle/Arju';

Step3:
---------
Also create a destination directory named DEST_DIR which copied file go. Here it is /export/home/Arju2.

SQL>CREATE DIRECTORY DEST_DIR as '/export/home/oracle/Arju2';

Step4:
---------
Grant Permission to the user who will do the operation.

GRANT EXECUTE ON DBMS_FILE_TRANSFER TO arju;
GRANT READ ON DIRECTORY source_dir TO arju;
GRANT WRITE ON DIRECTORY dest_dir TO arju;

Step5:
-----------
Connect as the user and execute procedure.

Example:
conn arju/a

SQL> !ls /export/home/oracle/Arju
test.txt

SQL> !mkdir /export/home/oracle/Arju2

SQL> !ls /export/home/oracle/Arju2

BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'SOURCE_DIR',
source_file_name => 'test.txt',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'test.txt');
END;
/

PL/SQL procedure successfully completed.

SQL> !ls /export/home/oracle/Arju2
test.txt

Troubleshooting DbConsole Error - OC4J Configuration issue

Error Description:
emctl start dbconsole
fails with the exception:
EM Configuration issue. /u01/app/oracle/product/10.2.0_64/db_1/_

Solution of The Problem:
First we need to determine whether the server use dynamic IP or static IP. There are two different solutions for these two.

1)If Server is configured with dynamic IP Address:
a)In the Loopback adapter configure and assign a dummy static address.
b)Then in the "hosts" file, add an entry associated with this dummy static IP address and map that to the machine name associated with the system through the system properties.
c)Configure the loopback adapter to be the primary network adapter on the system.
d)Now, any call to get the IP address of the system will return the dummy static IP associated with the loopback adapter. In call to get the hostname associated with this dummy IP address will return the real machine name, since this was associated with the IP address in the hosts file.
e)Also, any call to get the hostname will also return the machine name since the loopback adapter is the primary network adapter.

Suppose in Windows,

a). In the SYSTEM DRIVE:\WINDOWS\system32\drivers\etc\hosts file the following entry should be present for the node :

127.0.0.1 localhost hostname.domainname hostname

b). Set this environment variable to the hostname of the machine, from a command prompt:

For 10.1,
cmd> set EMHOSTNAME=

For 10.2,
cmd> set ORACLE_HOSTNAME=

c). Start the dbconsole now from the same window
cmd> set ORACLE_HOME=ORACLE_HOME of the 10G database
cmd> set ORACLE_SID=SID of the 10G database
cmd> cd %ORACLE_HOME%\bin
cmd> emctl start dbconsole

d).If the existing files/directories in the %ORACLE_HOME%\oc4j\j2ee\OC4J_DBConsole_hostname_SID and %ORACLE_HOME%\hostname_SID have the IP address instead of the hostname then recreate DBControl.

2)If Server uses static IP address

a)Check the hosts file entry:
On Windows, the HOSTS file is under $WINDOWS\system32\drivers\etc
On Unix systems hosts file is under /etc

The file should have the IP address followed by the fully qualified hostname.domain name, and then a short hostname or alias.
For Example,

192.168.1.2 hostname.domainname hostname

b)Check hostname and IP Address:

On unix, $ hostname $ifconfig -a
On Windows >ipconfig /all

>ping 192.168.1.2
>ping hostname.domainname
>ping hostname
>nslookup 192.168.1.2
>nslookup hostname.domainname
>nslookup hostname

c). Check if directories

/_
/oc4j/j2ee/OC4J_DBConsole_

exist and have correct permissions for the same user who has installed the software.

If they does not exist then recreate dbcontrol.
You can do it by,
$emca -deconfig dbcontrol db -repos drop
$emca -config dbcontrol db -repos create
I have shown how to drop , create and recreate dbcontrol in topic,

How To Drop, Create And Recreate DB Control In 10g Database

d)Ensure ORACLE_HOME and ORACLE_SID is set properly.

Related Documentation:
How to create and recreate DBcontrol

How to Start your database with non default spfile

All of you know that whenever it is invoked startup of a database then

1)First search for a spfile on it's default location named spfile$ORACLE_SID.ora

2)If not found then it searches for spfile.ora on the default location

3)If also not found then it search pfile on the default location.

But it is also not found then error arises.

We all know then we can easily startup with a pfile, like
startup pfile='Pfilename';
But there is no parameter like, startup spfile=.......;
So if my spfile is not in the default location then how I can specify the location of spfile.
The solution is ,

a)Create a blank Pfile.
b)Put the location of the non default spfile inside the blank Pfile with parameter spfile=location.
c)startup the database with the Pfile.

The example below will clear the problem.
1)Create no-default spfile.
SQL> create spfile='/export/home/oracle/myspfile.txt' from pfile;
File created.

2)Create a Pfile that will location SPfile.
SQL> !vi /export/home/oracle/pfile.txt
SPFILE=/export/home/oracle/myspfile.txt

3)Shutdown the database.
4)Start the database with the pfile.
SQL> startup pfile=/export/home/oracle/pfile.txt
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 109051944 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

5)Ensure that the database is started with no-default spfile.
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /export/home/oracle/myspfile.txt

Wednesday, April 9, 2008

What is your Oracle Database Software Release.

Oracle Corporation periodically releases new version of oracle database software. As many as five numbers may be required to fully identify a release. From oracle you can check or find oracle database version.

How to See which database version I am using:

1)Select * from v$version;
2)SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT VERSION STATUS
---------------------------------------- --------------- ---------------
NLSRTL 10.2.0.1.0 Production
Oracle Database 10g Enterprise Edition 10.2.0.1.0 Prod
PL/SQL 10.2.0.1.0 Production
TNS for Solaris: 10.2.0.1.0 Production

Release Number Format Description:
-------------------------------------
1)Major Database Release Number: (Here 10)
The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.

2)Database Maintenance Release Number:(Here 2)
The second digit represents a maintenance release level. Some new features may also be included.

3)Application Server Release Number:(Here 0)
The third digit reflects the release level of the Oracle Application Server.

4)Component-Specific Release Number: (Here 1)
The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

5)Platform-Specific Release Number : (Here 0)

The fifth digit identifies a platform-specific release. Usually this is a patch set.

How to Enable Listener Logging and Tracing.

I will try to show two different ways to enable logging and tracing of listener.ora file. These logging and tracing helps you to identify the network problems and troubleshooting connection problems in oracle.It also help to identify when a client is connected to oracle database.

Enabling Logging and Tracing in listener.ora:
1)Way 1: Putting entry in listener.ora
2)Way 2: Using Enterprise Manager.

1)Way 1: Putting entry in listener.ora
a)With an editor software edit the listener.ora file, Like on unix,
vi $ORACLE_HOME/network/admin/listener.ora
b)Put an entry of LOGGING_LISTENER for logging and TRACE_LEVEL_LISTENER for tracing.Like,

TRACE_FILE_LISTENER = LISTENERTEST.trc (The destination file for the trace file)
TRACE_DIRECTORY_LISTENER = /export/home (The destination directory for the trace file)
LOGGING_LISTENER = on
TRACE_LEVEL_LISTENER =SUPPORT

TRACE_LEVEL:It specifies the level of detail the trace facility records for the listener.The trace level value can either be a value within the range of 0 (zero) to 16 (where 0 is no tracing and 16 represents the maximum amount of tracing) or a value of off, admin, user, or support.

i))off (equivalent to 0) provides no tracing.

ii))user (equivalent to 4) traces to identify user-induced error conditions.

iii)admin (equivalent to 6) traces to identify installation-specific problems.

iv)support (equivalent to 16) provides trace information for troubleshooting information for Oracle Support Services.

c)Reload The Listener:
Like, lsnrctl reload

2)Way 2: Using Enterprise Manager.

i)Access the Oracle Net Administration page in Oracle Enterprise Manager.

ii)Select Listeners from the Administer list, and then select the Oracle home that contains the location of the configuration files.
iii)Click Go.

The Listeners page appears.

iv)Select a listener, and then click Edit.

The Edit Listeners page appears.

v)Click the Logging & Tracing tab.

vi)Specify the settings.
vii)Click OK.

Some Troubleshooting:

Format of the Listener Log Audit Trail:
---------------------------------------------
The audit trail formats text into the following fields:

Timestamp * Connect Data [* Protocol Info] * Event [* SID | Service] * Return Code

Properties of the audit trail are as follows:

-Each field is delimited by an asterisk (*).
-Protocol address information and service name or SID information appear only when a connection is attempted.
-A successful connection or command returns a code of zero.
-A failure produces a code that maps to an error message.

With the return code you can be able to see which type of error and when it occurs.

Audit Trail information can be used to view trends and user activity by first storing it in a table and then collating it into a report format.

Use the Trace Assistant to Examine Trace Files

With trcasst examine the trace file, Like

trcasst trace_file_name_here.
For example to see statistics we can use,

trcasst -s /export/home/mytracefile.trc

Related Documents
The listener supports no services
ORA-12518: TNS:listener could not hand off client connection
ORA-12541: TNS:no listener
In 10g listener log WARNING: Subscription for node down event still pending
lsnrctl start fails with TNS-12541, TNS-12560,TNS-00511, Linux Error: 111:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Convert CLOB to BLOB

Verify Physical Data Structure in Oracle.

With Oracle database it is sometime needed to check whether the physical data structure is intact or not. There are different ways to check whether physical data structure is intact or not, like to detect data block corruption.

I will try to demontrate two procedure in order to validate physical data structure.

1)With Export/Import Method.
2)With DBVERIFY Utility.

I have discussed about 1st method in other of my thread. Here I will try to explain about 2nd one.

DBVERIFY Utility
-------------------------

DBVERIFY utility can be used on offline or online databases, as well on backup files. We can use it to ensure that a backup database (or datafile) is valid before it is restored, to detect the corrupted block.

There are two command line interface of DBVERIFY utility.

A)To check disk blocks of a single datafile.
B)To check segment.

This utility is invoked by dbv command.

A)To check disk blocks of a single datafile.
--------------------------------------------------
Invoke dbv utility with the datafile name in file parameter. Like,

bash-3.00$ dbv file=/shared_disk/oradata/ARJU/data02.dbf feedback=1000

DBVERIFY: Release 10.2.0.1.0 - Production on Wed Apr 9 17:16:00 2008

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

DBVERIFY - Verification starting : FILE = /shared_disk/oradata/ARJU/data02.dbf
....................................................................

...
Page 80491 is marked corrupt
Corrupt block relative dba: 0x02013a6b (file 8, block 80491)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x02013a6b
last change scn: 0x0000.0573a224 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xa2240601
check value in block header: 0xf88
computed block checksum: 0x10
.
.
.
.

DBVERIFY - Verification complete

Total Pages Examined : 3045632
Total Pages Processed (Data) : 1620222
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 586
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1384939
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 39848
Total Pages Marked Corrupt : 37
Total Pages Influx : 0
Highest block SCN : 93175751 (0.93175751)

Type dbv help=y to view the available options.

Some parameters:
--------------------
USERID:This parameter is only necessary when the files being verified are ASM files.
FEEDBACK:Display a progress report to the terminal in form of dot (.). If feedback=100 is set then after verifying 100 blocks a dot(.) will be displayed.

B)To check segment.
--------------------------

In this mode, DBVERIFY enables you to specify either a table segment or index segment for verification.Duriing this mode, the segment is locked. If the specified segment is an index, the parent table is locked.

You can invoke dbv command to validate a segment like this,

dbv USERID=prod/prod SEGMENT_ID=2.2.890

SEGMENT_ID: The id of the segment that you want to verify. It is composed of the tablespace ID number (tsn), segment header file number (segfile), and segment header block number (segblock).

You can get this information from SYS_USER_SEGS. The required columns are TABLESPACE_ID, HEADER_FILE, and HEADER_BLOCK. SYSDBA privilege is required to query SYS_USER_SEGS.

Related Documents
-----------------------
To verify Physical data corruption as well as logical data corruption you can also use
RMAN Validation

How to Change Database Name and DBID?

Prior to introduction of DBNEWID utility it was possible to change the name of the database by manually creating a new control file but it was not possible to give new dbid to the database.

The DBID is an internal, unique identifier for a database. RMAN distinguishes databases by DBID, so you could not register a seed database and a manually copied database together in the same RMAN repository.

DBNEWID solves this. With DBNEWID utility you can change either database name or database id or both.

However, changing DBID is a serious procedure. When you change DBID previous backups, archived redo logs become invalid.

Procedure of changing DBID and Database Name:

1)Take a recoverable full database backup.
2)Mount the database.
3)With sysdba privilege, invoke nid
i) To change only DBID just invoke nid target=username/pass
ii) To change both DBID and DBNAME invoke nid target=username/pass DBNAME=new_database_name
iii)To change only DBNAME invoke nid target=username/pass DBNAME=new_database_name SETNAME=y

i)Change only DBID:
To change only DBID just enter the following command,
SQL>host nid target=arju/a
Where arju is a user having sysdba system priviege. And password of arju is a.

ii)Change both DBID and DBNAME:

To change the database name in addition to DBID enter the following command.
SQL>host nid=arju/a DBNAME=arjut
which changes the DBID to a new DBID (You can't set DBID though as your wish) and change the database name to arjut.
In this case the follow operations are performed is below.

1)The DBNEWID utility performs validations in the headers of the datafiles and
control files before attempting I/O to the files.

2)If validation is successful, then DBNEWID prompts you to confirm the operation (unless you specify a log file, in which case it does not prompt)

3)Then changes the DBID and the DBNAME for each datafile, including offline normal and read-only datafiles,

4)Shuts down the database, and then exits.

iii)Change only Database Name:
In the following example I will try to demonstrate to change the Database name.

1)SQL> select dbid, name from v$database;

DBID NAME
---------- ---------
246608360 ARJU

2)SQL> shutdown imemdiate;
startup mount;
3)SQL> host nid target=arju/a DBNAME=ARJUT setname=Y

DBNEWID: Release 10.2.0.1.0 - Production on Wed Apr 9 16:21:33 2008

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

Connected to database ARJU (DBID=246608360)

Connected to server version 10.2.0

Control Files in database:
/oradata/Arju/arju/control01.ctl
/oradata/Arju/arju/control02.ctl
/oradata/Arju/arju/control03.ctl

Change database name of database ARJU to ARJUT? (Y/[N]) => y
Instance shut down

Database name changed to ARJUT.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

4)SQL> !export ORACLE_SID=ARJUT
5)SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 113246248 bytes
Database Buffers 46137344 bytes
Redo Buffers 6369280 bytes
6)SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string arju
SQL> alter system set db_name=ARJUT scope=spfile;

System altered.

7)SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
8)SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 113246248 bytes
Database Buffers 46137344 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
9)SQL> select name,dbid from v$database;

NAME DBID
--------- ----------
ARJUT 246608360

How to Discover find DBID

Restore and Recover database to a new host

How to Restore the Controlfile from Backup.

Restore and Recover database in Noarchivelog Mode

Flashback Version Query

This is a new feature comes in Oracle 10g. With this you can also get back you present table to a previous state. Like you mistakenly deleted some rows and you have committed and now you want to get back your data.However, it depends on the availability of the undo information in the database, so if the undo information has been aged out, this approach will fail.

Here is an example for your better understanding,


SQL> select * from t;
         A
----------
         1
       100
        10

SQL> insert into t values(2);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t;
         A
----------
         1
       100
        10
         2

Flashback Table
SQL> flashback table t to timestamp systimestamp-interval '1' minute;

Flashback complete.


SQL> select * from t;
         A
----------
         1
       100
        10

Flashback Versions Query
You can also keep track of change data over time suppose when one row is upadted when one row is deleted and when one row is inserted by following query,

select versions_starttime, versions_endtime, versions_xid,versions_operation,a
from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;


SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation,a
  from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;  

Output is,

VERSIONS_STARTT VERSIONS_ENDTIME     VERSIONS_XID     V          A
--------------- -------------------- ---------------- - ----------
09-APR-08 02.47 09-APR-08 02.47.26 P 0100090035010000 I          5
.16 PM          M

09-APR-08 02.47                      0700100035010000 D          5
.26 PM

                                                               100
                                                                10
                                                                 1

SQL> insert into t values(200);
1 row created.

SQL> commit;
Commit complete.

SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation,a
  from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;  

VERSIONS_STARTT VERSIONS_ENDTIME     VERSIONS_XID     V          A
--------------- -------------------- ---------------- - ----------
09-APR-08 02.47 09-APR-08 02.47.26 P 0100090035010000 I          5
.16 PM          M

09-APR-08 02.47                      0700100035010000 D          5
.26 PM

09-APR-08 02.49                      0700140035010000 I        200
.35 PM

                                                                10
                                                                 1

                                                               100

6 rows selected.

You can find out the changes over time by ,

select a, versions_starttime, versions_endtime
from t versions between timestamp
to_date('7/3/20078 13:33:54','mm/dd/yyyy hh24:mi:ss')
and to_date('7/3/2008 13:37:55','mm/dd/yyyy hh24:mi:ss')

Flashback Transaction Query
You can check undo_sql by,
SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = '....';

Flashback Query
SQL> select * from flash;
A
----------
1
2

SQL> insert into flash values(3);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from flash as of timestamp systimestamp-interval '1' minute;
A
----------
1
2

SQL> select * from flash;
A
----------
1
2
3

In order to know more about it have a look at,
Restore Old Data from Flashback Query
Related Documents:
Restore Old Data from Flashback Query

Oracle Flashback Technology

Oracle Flashback Drop

Tuesday, April 8, 2008

Flashback Table and Flashback Drop

The FLASHBACK TABLE statement enables users to get a table to a previous point in time. It provides a fast, online solution to get back a table that has been accidentally modified or deleted by a user or application. It eliminates the DBA to perform more complicated point in time recovery operations.

We should remember the thing is you must use automatic undo management to use the Flashback Table feature. It is based on undo information stored in an undo tablespace.

Now let's go for experiment.
Flashback Drop
1)SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE

2)Drop the Table,
SQL> drop table test;

Table dropped.

3)Now see the Status,
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$SnQhAfQtUQzgRAAZ0Xbz6g==$0 TABLE

In fact when we issue drop table command the object resides on recycle bin. We can see the recycle bin objects from dba_recyclebin or simply,

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$SnQhAfQtUQzgRAAZ0Xbz6g==$0 TABLE 2008-04-09:12:35:17

4)Get the Table Back:

Invoke, flahsback command. Like,

SQL> flashback table test to before drop;

Flashback complete.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE

However if you have another table you can use rename to clause with flashback,

flashback table test to before drop rename to test2;
SQL> drop table test;

Table dropped.

SQL> flashback table test to before drop rename to test2;

Flashback complete.

SQL> desc test2;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

However you can drop table permanently without staying it in recyclebin using,

drop table test2 purge;

To drop table from recyclebin, use,

purge table_name;

Flashback Table

You can also get back you present table to a previous state. Like You deleted some row and you have committed. Now you can back your data. Here is an example for your better understanding,

SQL> select * from t;
A
----------
1
100
10

SQL> insert into t values(2);

1 row created.

SQL> commit;
Commit complete.

SQL> select * from t;
A
----------
1
100
10
2

SQL> flashback table t to timestamp systimestamp-interval '1' minute;

Flashback complete.

SQL> select * from t;

A
----------
1
100
10

Important Things:
1) objects will go to recyclebin or it will not go is based on recyclebin parameter settings.
If I set alter system set recyclebin=off then object will not go in recycle bin.

2)Dropped SYS and SYSTEM schema objects are don't go in recyclebin.

3)The un-drop feature brings the table back to its original name, but not the associated objects like indexes and triggers, which are left with the recycled names.Views and procedures defined on the table are not recompiled and remain in the invalid state. These old names must be retrieved manually and then applied to the flashed-back table.

Like,

SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'TEST')
AND ORIGINAL_NAME != 'TEST';

OBJECT_NAME ORIGINAL_N TYPE
------------------------------ ---------- --------
BIN$04LhcpdfanfgMAAAAAANPw==$0 TEST_01 INDEX
BIN$04LhcpdfanfgMAAAAAANPw==$0 TR_TEST TRIGGER

After the table is flashed-back, the indexes and triggers on the table TEST will be named as shown in the OBJECT_NAME column. From the above query, you can use the original name to rename the objects as follows:

ALTER INDEX "RECYCLE BIN NAME" RENAME TO TEST_01;
ALTER TRIGGER "RECYCLE BIN NAME" RENAME TO TR_TEST;

But exception is the bitmap indexes. When they are dropped, they are not placed in the recycle bin and so they are not retrievable. The constraint names are also not retrievable from the view. They have to be renamed from other sources.

4) To do flashback enable row movement must be enabled.

alter table test enable row movement;

Related Documents:
Restore Old Data from Flashback Query

Oracle Flashback Technology

ORA-01940: Cannot drop a user that is currently connected

Problem Description:

SQL> drop user strmadmin cascade;
drop user strmadmin cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

Solution of The Problem:

Find out the connected user sid and serial# by,


SQL> select sid, serial# from v$session where username = 'STRMADMIN';

   SID    SERIAL#
---------- ----------
   268       1268
   315       1223

Before killing session you may wish to lock the account for further connection attempts. This is extremely necessary to drop a user who automatically establish session like to drop an application user or to drop a user who perform batch jobs.

SQL> Alter user strmadmin account lock;

Now kill the connected session.
SQL> alter system kill session '268,1268';
System altered.

SQL> alter system kill session '315,1223';
System altered.

And then drop the user.
SQL> drop user strmadmin cascade;
User dropped.

Related Documents
Drop User in Oracle

Different Oracle Database Status and Active State.

Status:
1)Started: When you issue startup nomount then the status of database is nomount stage. In this case oracle database instance just read pfile/spfile parameter and allocate instance memory.

2)Mounted: When you issue startup mount then the status of database is mount stage.In this case oracle database starts an instance and mounts the database, but leaves the database closed. However you need to do in mount state to perform specific maintenance operations.(Such as archive mode and recovery)

3)Open: When you issue alter database open then the status of database is open stage.After executing this statement, any valid Oracle Database user with the CREATE SESSION system privilege can connect to the database.

4)Open Migrate: After ALTER DATABASE OPEN {UPGRADE | DOWNGRADE}

To see these status issue,
SQL>SELECT STATUS FROM V$INSTANCE;

Database Active State:
1)NORMAL: Indicates the database is in a normal state.

2)QUIESCING: Indicates that the ALTER SYSTEM QUIESCE RESTRICTED statement has been issued: no new user transactions, queries, or PL/SQL statements are processed in this instance. User transactions, queries, or PL/SQL statements issued before the ALTER SYSTEM QUIESCE RESTRICTED statement are unaffected. DBA transactions, queries, or PL/SQL statements are also unaffected.

3)QUIESCED: indicates that the ALTER SYSTEM QUIESCE RESTRICTED statement has been issued: no user transactions, queries, or PL/SQL statements are processed. DBA transactions, queries, or PL/SQL statements are unaffected. User transactions, queries, or PL/SQL statements issued after the ALTER SYSTEM QUIESCE RESTRICTED statement are not processed.

To see these status issue,
SQL>SELECT ACTIVE_STATE FROM V$INSTANCE;

Database Status:
1)SUSPENDED: The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to datafiles (file header and file data) and control files. The suspended state lets you back up a database without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state.

2)ACTIVE: Use the ALTER SYSTEM RESUME statement to resume normal database operations.

All these can be seen by issuing the following commands,

SQL> select status, active_state, database_status from v$instance;

Database Open Mode:
1)Read Only: Opening a database in read-only mode enables you to query an open database while eliminating any potential for online data content changes.
The following statement opens a database in read-only mode:

ALTER DATABASE OPEN READ ONLY;

2)Read Write: Open a database in read/write mode as follows:
ALTER DATABASE OPEN READ WRITE;
However, read/write is the default mode.

This opening mode can be shown from,

SQL>select open_mode from v$database;

How to know My Database Start with Spfile/Pfile?

There is a parameter spfile which indicate the startup parameter of the database. If database is started with pfile then the parameter spfile contains null value. If database is stated with spfile then the parameter value contains the location of the spfile.

Like, if it is started with spfile then the output will like,

SQL> select decode(value,'','Your Database started with pfile','Your Database started with spfile') from v$parameter where name='spfile';

DECODE(VALUE,'','YOURDATABASESTAR
---------------------------------
Your Database started with spfile

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/oracle/product/spfilearju.ora

If your database is started with pfile, it will show like,
SQL> select decode(value,'','Your Database started with pfile','Your Database started with spfile') from v$parameter where name='spfile';

DECODE(VALUE,'','YOURDATABASESTAR
---------------------------------
Your Database started with pfile

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

One think you need to remember that there is a parameter named spfile exist in database but no parameter pfile exist in database.

Related Documents:
----------------------------
How Spfile can be Recovered

Monday, April 7, 2008

ORA-07283/ORA-32004: obsolete and/or deprecated parameter(s) specified

Error Description:
while startup your database it raise an error like,

ORA-19905: log_archive_format must contain %s, %t and %r
Explanation of Error:
Could not startup the database using Spfile as Spfile had wrong format. Some/Al least one parameter in it is obsolete and/or deprecated.

Solution:
1)start the database in nomount state:
Try with startup nomount.

If you fail,then see section a).
If you succeed then follow section b)

a)If you fail to startup nomount stage(You will fail if static parameter is wrongly set.) like,
ORA-19905: log_archive_format must contain %s, %t and %r then,

i)Create a copy of the init.ora file. ( oracle_home/admin/$ORACLE_SID/pfile --> init.ora ) or you don't have a copy then you must have to create a new pfile.

ii)Edit the above init.ora to reflect the log_archive_format = 'arch_%r_%t_%s.arc'

iii)Shutdown immediate and Startup nomount with the above pfile.

SQL> shutdown immediate
SQL> startup nomount pfile = oracle_home/admin/$ORACLE_SID/pfile/init.ora

iv)Create spfile from pfile
SQL> create spfile from pfile ='oracle_home/admin/pfile/init.ora'

v) SQL> shutdown immediate
vi) SQL> startup

b)You succeed but with an error like,
ORA-32004: obsolete and/or deprecated parameter(s) specified

i)See alert log to see which parameter has been obsolete and/or deprecated parameter. Find the parameters. Set the parameter / reset the parameter.

Like,

SQL> alter system reset log_archive_start scope=spfile sid='*';

ii)shutdown immediate

iii)startup
2)Alter database mount.

3)Alter databse open.
If you face the error like

ORA-07283: sksaprd: invalid volume size for archive destination.
then,

SQL> alter system reset log_archive_dest scope=spfile sid='*';

Manually Creating an Oracle Database

I think ,many one of you have already have created your database by dbca. DBCA is a bery good graphical tool by which you can create your database. You can also create your database manually.
In 10g I want to show how easily you can create database.

I want to show an example of Unix environment.

1)Set your ORACLE_SID.
export ORACLE_SID=arju
2)Create the Initialization Parameter File.
touch $ORACLE_HOME/dbs/initarju.ora
3)Edit the Initialization Parameter File.
Just entry,
db_name=arju
db_create_file_dest='/oradata'
4)Connect to the Instance.
sqlplus / nolog
conn / as sysdba
5)Create database
startup nomount
create database;
6)Run Scripts to Build Data Dictionary Views
CONNECT / AS SYSDBA
@/u01/oracle/rdbms/admin/catalog.sql
@/u01/oracle/rdbms/admin/catproc.sql

7)Back Up the Database.

In this example I have created database with pfile. If you want to create database manually by spfile then create an spfile after step 5 as SQL>CREATE SPFILE FROM PFILE;

Related Documents
Create the Data Dictionary in Oracle

Database Administrator Authentication

We sometimes need such special operation on database like, startup or shutdown the database or change the archival mode of the task. To do this, two special system privileges, SYSDBA and SYSOPER are required. You must have one of these privileges granted to you, depending upon the level of authorization you require.

One thing we need to remember when you connect with SYSDBA or SYSOPER privileges, you connect with a default schema. For SYSDBA this schema is SYS and for SYSOPER the schema is PUBLIC.

There are two methods are available for authenticating database administrators.

1)Operating System(OS) Authentication.
2)Password File Authentication.

Priority of Database Administrator Authentication Methods:
------------------------------------------------------------
Remote Database Administration ->If have Secure Connection?->If yes->Want to use OS authentication?->If yes then use OS Authentication.

Remote Database Administration ->If have Secure Connection?->If yes->Want to use OS authentication?->If No then use Password file Authentication.

Remote Database Administration ->If have Secure Connection?->If No then use Password file Authentication.

Local Database Administration ->Want to use OS authentication?->If No then use Password file Authentication.

Local Database Administration ->Want to use OS authentication?->If yes then use OS Authentication.

Using Operating System Authentication
-------------------------------------------
Two special operating system groups control database administrator connections when using operating system authentication. These groups are generically referred to as OSDBA and OSOPER.
On unix is is usually referred as dba/oper and on windows as ORA_DBA/ORA_OPER.

If you are a member of the OSDBA/ORAOPER group and you specify AS SYSDBA/ AS SYSOPER when you connect to the database, then you connect to the database with the SYSDBA/SYSOPER system privilege.

If you are not a member of OSDBA/ORAOPER group then OS authentication fails.

How to Use Operating System Authentication

To enable operating system authentication of an administrative user:

1.Create an operating system account for the user.
2.Add the account to the OSDBA or OSOPER operating system defined groups.

On unix the system is

# id -a oracle
uid=100(oracle) gid=100(oinstall) groups=101(dba)
# useradd -g oinstall -G dba test
# exit
exit
bash-3.00$ su test
Password:
sh-3.00$ sqlplus / as sysdba

On windows, click on my computer>Manage>Local Users and Groups>Add user and group.

Using Password File Authentication
--------------------------------------------

1.If you don't have password file create one,
2.Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default).
3.Connect to the database as user SYS (or as another user with the administrative privileges).
4.If the user does not already exist in the database, create the user.
5.Grant the SYSDBA or SYSOPER system privilege to the user:

Here is the steps,
1.orapwd FILE=newpwdfile.pwd PASSWORD=test ENTRIES=30
2.alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile
3.SQL> conn / as sysdba
4.SQL> create user test identified by t;
5.SQL> !lsnrctl status and see the service name. Here my one is arju.arjubd.com
6.grant sysdba to test
And here is the test after step 5,

SQL> grant create session to test;

Grant succeeded.

SQL> conn test/t as sysdba
Connected.
SQL> conn test/t@neptune/arju.arju.com as sysdba
ERROR:
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant sysdba to test;

Grant succeeded.

SQL> conn test/t@neptune/arju.arju.com as sysdba
Connected.

You can check who of you have password file authentication using,

select username, SYSDBA,SYSOPER from v$pwfile_users;

Related Documents
Login to Dbconsole, Authentication failed!null Returned

UDE-00008, ORA-06550 and PLS-00905 When Using Datapump

Symptoms of the Error:
-----------------------------
UDE-00008: operation generated ORACLE error 6550
ORA-06550: line 1, column 56:
PLS-00905: object SYS.DBMS_DATAPUMP is invalid

Cause:
------------------
SYS.DBMS_DATAPUMP is inavlid.

SQL> alter package SYS.DBMS_DATAPUMP compile;
Warning: Package altered with compilation errors.

SQL> show errors

Solution:
---------------
To implement the solution, execute the following steps:

SQL> connect / as sysdba;
SQL> startup upgrade;
SQL> @catalog.sql <--------------$ORACLE_HOME/rdbms/admin SQL> @catproc.sql <--------------$ORACLE_HOME/rdbms/admin

Sunday, April 6, 2008

Export/Import DataPump Parameter QUERY Specification

I will try to demonstrate how you can use QUERY parameter in IMPDP or EXPDP. I will also try to show where quotes must be used in the WHERE clause. Incorrect usage of single or double quotes for the QUERY parameter can result in errors such as:

ORA-39001: invalid argument value
ORA-00933: SQL command not properly ended
LRM-00111: no closing quote for value ''
ORA-06502: PL/SQL: numeric or value error

1. QUERY in Parameter file.
-----------------------------------------
Using the QUERY parameter in a parameter file is the preferred method. Put double quotes around the text of the WHERE clause.
exp.par contains,

QUERY= prod.dept:"WHERE id IN (SELECT DISTINCT
id FROM prod.employees e, prod.jobs j WHERE e.job_id=j.job_id
AND UPPER(j.job_title) = 'DBA' OR e.salary >= 500)"

2. QUERY on Command line.
---------------------------------
The QUERY parameter can also be used on the command line. Again, put double quotes around the text of the WHERE clause.

Suppose in Unix,

expdp scott/tiger DIRECTORY=my_dir \
DUMPFILE=expdp.dmp LOGFILE=expdp.log TABLES=emp,dept \
QUERY=emp:\"WHERE ename LIKE \'A\%\'\"

3. QUERY in Oracle Enterprise Manager Database Console.
---------------------------------------------------------------
Login to EM>Maintenance>Utilities>Export to Files>Show Advanced Options(Step2)>QUERY>Add>...

The option in EM varies from version to version. In another version I saw it as,

Login to EM>Maintenance>Export to Export Files>Select options>Show Advanced Options>Query>Add>Table Name>Predicate Clause .............

4. Known Issues in Import/Export.
--------------------------------------
Bug 4383811 - Import Data Pump mail fail with: ORA-600[qerxtAgentOpen_911]
Bug 5767565 - Import Data Pump with REMAP_SCHEMA and NETWORK_LINK does not handle the QUERY parameter.

Related Documents:
--------------------
Data Pump Export/Import

Export/Import from Enterprise Manager

Original Export/Import

Extract DDL from Dump/ Read contents of dumpfile

I have been asked several times from different forums about the question of how we can extract ddl from dump?
In a nutshell I will try to show the procedure of how we can extract ddl from dump without importing any data.

Before proceed you need to know by which command dump was taken? Is it by exp or by expdp. Both have different procedures.I demonstrates below these two in two sections.

A) Dump was taken by exp.
B) Dump wad taken by expdp.

A) Dump was taken by exp.

1) You can simply use any viewer command and search the specified strings for the contents within it. I tried with cat, less and strings command and it worked.

Suppose in unix , strings a.dmp |grep "CREATE TABLE"
or, cat dumpfile_name |grep "YOUR SEARCH STRING"

2)you can invoke imp command with dump file name and with show=y option. The DDL command inside the dump will be shown to the screen. Like,

imp userid/password file=dumpfilename show=y

You can include log=filename with show=y option in order to save the contents inside file. Like,
imp userid/password file=dumpfilename show=y log=logfile.txt

3)A preferred method to me is to use indexfile=file.txt with imp command. It is better than show=y option. As output from indexfile=file.txt can be easily used without removing rem keyword and that script you can use easily. But output by show=y can't be used easily as double quote places in awkward places. Like you should use,
imp userid/password file=dumpfilename indexfile=file.txt

B) Dump was taken by expdp.

1)With impdp you have to use sqlfile=file.txt in which the ddl commands will be exported to file file.txt.Like,
impdp userid/password dumpfile=dumpfilename sqlfile=file.txt

Related Documents
How to get different object_type creation scripts
How to get DDL from database objects

Troubleshooting ORA-2085 "database link %s connects to %s"

When the source database initialization parameter GLOBAL_NAMES is set to true, the database link name must match the target database global name as it exists in the GLOBAL_NAME view in the data dictionary.

This error have two solutions.

A)Create database link with the name as of source database global database name.
B)Change GLOBAL_NAMES to false if it is not required.

(A)Create database link with the name as of source database global database name.
------------------------------
One thing you should remember when you do not specify the domain portion of the dblink name in the create statement, Oracle automatically qualifies the link name with the domain of the SOURCE database global name view.

Check the contents of ALL_DB_LINKS for the fully qualified link name.

To know the global name of the source database,
SQL>Select * from global_name;
TEST.WORLD.COM

Now according to output you can make database link in your destination database, like

SQL>Create public database link TEST.WORLD.COM connect to userid identified by password using 'test';

Or , you can make it by
SQL>create database link ARJU.JUPITER.ARJU.NET connect to web_key identified by web_key using '(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = jupiter)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ARJU.JUPITER.ARJU.NET)
))';

Where in the database where I like to connect after invoking lsnrctl status I got the HOST = jupiter and PORT = 1522 and SERVICE_NAME = ARJU.JUPITER.ARJU.NET.

(B)Change GLOBAL_NAMES to false if it is not required.
----------------

SQL> alter system set global_names=FALSE;
System altered.

Related Documents
How to Import data without Dumpfile-Network Mode Import with use of database link

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle

Saturday, April 12, 2008

Thursday, April 10, 2008

Wednesday, April 9, 2008

Tuesday, April 8, 2008

Monday, April 7, 2008

Sunday, April 6, 2008

Tag Cloud

Blog Archive

Which Oracle database version you use?