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

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

Flashback Transaction Query

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
X1 TABLE

SQL> show user;
USER is "N"
SQL> desc flashback_transaction_query
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)

SQL> select * from flashback_transaction_query where table_name='X1';

no rows selected

SQL> select * from x1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> select * from flashback_transaction_query where table_owner='N';

no rows selected

SQL> insert into x1 select * from x1 where empno=7369;

1 row created.

SQL> select count(*) from x1;

COUNT(*)
----------
15

SQL> commit;

Commit complete.

SQL> select * from flashback_transaction_query where table_name='X1';

XID START_SCN START_TIM COMMIT_SCN COMMIT_TI LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID
---------------- ---------- --------- ---------- --------- ------------------------------ ------------ -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------- -------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
05002C0013030000 1454729 13-NOV-07 1456180 13-NOV-07 N 1 INSERT X1 N AAAMr2AAAAAAAAAAAA
delete from "N"."X1" where ROWID = 'AAAMr2AAAAAAAAAAAA';


SQL> insert into x1 select * from x1 where empno=7934;

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from x1;

COUNT(*)
----------
16

SQL> select * from flashback_transaction_query where table_name='X1';

XID START_SCN START_TIM COMMIT_SCN COMMIT_TI LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID
---------------- ---------- --------- ---------- --------- ------------------------------ ------------ -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------- -------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
05002C0013030000 1454729 13-NOV-07 1456180 13-NOV-07 N 1 INSERT X1 N AAAMr2AAAAAAAAAAAA
delete from "N"."X1" where ROWID = 'AAAMr2AAAAAAAAAAAA';

0800000014030000 1456180 13-NOV-07 1456222 13-NOV-07 N 1 INSERT X1 N AAAMr2AAAAAAAAAAAA
delete from "N"."X1" where ROWID = 'AAAMr2AAAAAAAAAAAA';


SQL> spool off

ASM to Non ASM

connected to target database: ORCL (DBID=1185757350)
ASM TO NON ASM
1.
RMAN> run
2> {
3> allocate channel c1 device type disk format='E:\backup\db_%U';
4> backup database;
5> }
released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=141 devtype=DISK
Starting backup at 08-AUG-08
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DBFILES/orcl/datafile/system.263.659715057
input datafile fno=00003 name=+DBFILES/orcl/datafile/sysaux.265.659715107
input datafile fno=00002 name=+DBFILES/orcl/datafile/undotbs1.264.659715093
input datafile fno=00005 name=+DBFILES/orcl/datafile/example.267.659715139
input datafile fno=00004 name=+DBFILES/orcl/datafile/users.266.659715133
channel c1: starting piece 1 at 08-AUG-08
channel c1: finished piece 1 at 08-AUG-08
piece handle=E:\BACKUP\DB_1LJNH6L3_1_1 tag=TAG20080808T124211 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:26
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel c1: starting piece 1 at 08-AUG-08
channel c1: finished piece 1 at 08-AUG-08
piece handle=E:\BACKUP\DB_1MJNH6NP_1_1 tag=TAG20080808T124211 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 08-AUG-08
released channel: c1
2. Shutdown Immediate
3.startup Nomount
4.Change the intialization parametres
*alter system set db_create_file_dest=' ' scope=spfile
*alter system set db_create_online_log_dest_1=' ' scope=spfile;
*alter system set control_files='E:/oracle/product/10.2.0/oradata/control01.ctl' scope=spfile;
5.(Optional)
SQL> drop diskgroup 'DBFILES' including contents;
Diskgroup dropped.
5.
run
{
shutdown immediate;
startup nomount
}
RMAN>set DBID=1185757350;
RMAN> run
2> {
3> restore controlfile from "E:\BACKUP\DB_1MJNH6NP_1_1";
4> startup mount;
5> set newname for datafile 1 to 'E:/oracle/product/10.2.0/oradata/system.dbf' ;
6> set newname for datafile 2 to 'E:/oracle/product/10.2.0/oradata/undotbs1.dbf' ;
7> set newname for datafile 3 to 'E:/oracle/product/10.2.0/oradata/sysaux.dbf' ;
8> set newname for datafile 4 to 'E:/oracle/product/10.2.0/oradata/users.dbf' ;
9> set newname for datafile 5 to 'E:/oracle/product/10.2.0/oradata/example.dbf' ;
10> restore database;
11> switch datafile all;
12> recover database until logseq 63;
13> sql"alter database mount";
14> sql"alter database open resetlogs";
15> }

Logfile created automatically in Flash recovery area for the same size and database opened with resetlogs.

General Syntax RMAN

RMAN> run
2> {
3> shutdown immediate;
4> startup mount;
5> restore database;
6> recover database until logseq 14;
7> sql"alter database open";
8> }
The command has no syntax errors
RMAN> run
2> {
3> shutdown immediate;
4> startup mount;
5> restore database;
6> recover database until logseq 14;
7> sql"alter database open resetlogs";
8> }
The command has no syntax errors
RMAN> run
2> {
3> sql"alter tablespace user offline";
4> restore tablespace users;
5> recover tablespace users until logseq 15;
6> sql"alter tablespace users online";
7> }
The command has no syntax errors
RMAN> run
2> {
3> sql"alter tablespace user offline";
4> restore tablespace users;
5> recover tablespace users until logseq 15 AUXILIARY DESTINATION 'e:\test\';
6> sql"alter tablespace users online";
7> }
The command has no syntax errors
RMAN> exit
RUN
{
SET UNTIL SCN 1000;
# Alternatives:
# SET UNTIL TIME 'Nov 15 2004 09:00:00';
# SET UNTIL SEQUENCE 9923;
RESTORE DATABASE;
RECOVER DATABASE;
}
TSPITR
>Run
{
Restore tablespace users;
RECOVER TABLESPACE users UNTIL LOGSEQ 1300 AUXILIARY DESTINATION 'c:\auxdest';
}

Transport Tablespace

1. Make the tablespace read only.
2.export tablespace using TRANSPORT_TABLESPACE option
3. copy the datafile to the destination database server.
4.transfer the dumpfile to the destination server.
5. import tablespace using TRANSPORT_TABLESPACE option.
6. Make the tablespace read write

********** On Source database **************
ALTER TABLESPACE NV READ ONLY;
select file_name from dba_data_files where tablespace_name = 'NV';

exp "sys/databaseadmin@NVDB" transport_tablespace=y tablespaces=NV
triggers=y constraints=y grants=y file=test.dmp

copy NV01.dbf to the filesystem for the destination database. (Copy all the datafiles of the NV tablespace to the destination database).

ALTER TABLESPACE NV READ WRITE;

************* On Destination database ***********

imp "sys/databaseadmin@NVDB1" transport_tablespace=y datafiles='NV01.dbf'
file=test.dmp

ALTER TABLESPACE NV READ WRITE;

select file_name from dba_data_files where tablespace_name = 'NV';

Its done ...