Powered By Blogger

Friday, May 22, 2009

File transfer using dbms_file_transfer between servers

Steps:
create directory a as 'D:\backup_log\'
CREATE DATABASE LINK "EDU.REGRESS.RDBMS.DEV.US.ORACLE.COM"
CONNECT TO NV
IDENTIFIED BY *******
USING 'EDD';
grant read,write on directory a to nv1
exec dbms_file_transfer.GET_FILE('t1','test.txt','edu','a','test.txt');

>> t1 is the directory created on the source machine.
>> test.txt is the file placed in the source directory location.
>> edu is the database link created between the source and destination database servers.
>> 'a' is the directory object created on the destination database server as shown above.
>> test.txt is the file that is to be copied to the destination server.

Reconfiguring Database console After configuring Loopback adapter on windows

To install a loopback adapter on Windows XP:
1. Open the Windows Control Panel.
2. Double-click Add Hardware to start the Add Hardware wizard.
3. In the Welcome window, click Next.
4. In the Is the hardware connected? window, select Yes, I have already connected the hardware, and click Next.
5. In the The following hardware is already installed on your computer window, in the list of installed hardware, select Add a new hardware device, and click Next.
6. In the The wizard can help you install other hardware window, select Install the hardware that I manually select from a list, and click Next.
7. From the list of hardware types, select the type of hardware you are installing window, select Network adapters, and click Next.
8. In the Select Network Adapter window, make the following selections:
· Manufacturer: Select Microsoft.
· Network Adapter: Select Microsoft Loopback Adapter.
9. Click Next.
10. In the The wizard is ready to install your hardware window, click Next.
11. In the Completing the Add Hardware Wizard window, click Finish.

Reconfiguring DB console :
d:\>emca -config dbcontrol db -repos recreate

STARTED EMCA at May 22, 2009 5:34:53 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl
Database Control is already configured for the database orcl
You have chosen to configure Database Control for managing the database orcl
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ D:\oracle\product\10.2.0\db_1

Database hostname ................ nav.in.Nv.com
Listener port number ................ 1521
Database SID ................ orcl
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
May 22, 2009 5:35:31 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollogs\emca\orcl\emca_2009-05-22_05-34-53-PM.log.
May 22, 2009 5:35:33 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
May 22, 2009 5:36:17 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
May 22, 2009 5:38:17 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
May 22, 2009 5:38:17 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
May 22, 2009 5:40:27 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
May 22, 2009 5:40:32 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
May 22, 2009 5:41:12 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
May 22, 2009 5:41:12 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>>
Enterprise Manager configuration completed successfully
FINISHED EMCA at May 22, 2009 5:41:12 PM

Monday, May 18, 2009

Export and import the schema into the same database with different name

Oracle 10.2.0
Exported "N" schema in the database "BL".
"N" Schema details.
* Schema's default tablespace is "N_data".
* Schema has its indexes in "N_index" tablespace.
* Schema has LOB objects. And its size around 8 GB.
> Exp N/Passwd file='e:\n_exp.dmp' owner=N statistics=none buffer=314572800
exported sucessfully without warnings.
Import steps :
* Try to create a user named "NV" and created a new tablespace "NV_data" and assigned as the default tablespace.
* Created the tablespace named "NV_index" for indexes and assigned Unlimited tablespace quota to user "NV".
* Granted the same roles(Connect,resource,DBA) to the newly created user.
* Tried to import
> Imp Nv/passwd file='e:\n_exp.dmp' fromuser=n touser=nv statistics=none buffer=314572800

Objects imported into the NV user, but it used 'N_data' tablespace to create the objects.
And i tried to revoke the unlimited tablespace privilege from the user "NV".
And granted unlimited tablespace privilege on the tablespace "NV_data" to "NV" user.
Import completed sucessfully. But the index was only created in the "NV_data" tablespace later i moved the indexes to Nv_index" tablespace.

Refer :

http://forums.oracle.com/forums/thread.jspa?threadID=899752&tstart=285