Re: RMAN CONVERT DATABASE

From: onedbguru <onedbguru_at_yahoo.com>
Date: Wed, 30 Mar 2011 16:51:17 -0700 (PDT)
Message-ID: <0ff3246b-5098-4e82-9891-bba4d1480890_at_w21g2000yqm.googlegroups.com>



On Mar 29, 11:26 pm, BillPedersen <wmapeder..._at_gmail.com> wrote:
> On Mar 29, 8:59 pm, John Hurley <hurleyjo..._at_yahoo.com> wrote:
>
> > Bill:
>
> > # Thoughts?
>
> > How about a straight up impdp using network link?
>
> I guess the other thing is I did not see much/any discussion on it in
> the 10g2R documentation set...
>
> Found some notes online, will review.
>
> From what I can see, I would suspect I need to have "target" database
> in the destination - one which can be "blown away" and then it is a
> matter of logging into both systems, setting up the link and then
> starting the expdp and then the impdp processes on SQLplus with the
> full=y option on the expdp.
>
> Does one need to do the same sort of PL/SQL recompile on the target as
> discussed with RMAN CONVERT process?  I did not yet see any discussion
> around that.
>
> Thanks,
>
> Bill.

Bill,

Depending on the size of your database (doesn't sound very big at the moment). With the following procedure there is no need to create a dump file. IMPDP is very smart. You can even have it create the tablespaces for you, although when moving to a completely new platform, you should do that ahead of time unless you start up the database with the parameter DB_FILE_NAME_CONVERT = '/dbs/t1/','DSA3: [oradata]','dbs/t2/ ','DSA4:[oradata]' (note they are in comma separated pairs)
and DB_CREATE_FILE_DEST = 'directory'. Oracle has gotten a lot better at helping you move stuff about.

http://download.oracle.com/docs/cd/E18283_01/server.112/e17120/omf002.htm#i1006164

If you only have one schema (or many schema owners for that matter) you can do the following:
Create database.
log in / as sysdba
[note this one connects to user SYSTEM using a SERVICE_NAME] sql> create database link from_db CONNECT TO SYSTEM IDENTIFIED BY systempassword
USING '(DESCRIPTION = (ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = {HOSTIPADDRESS})(PORT = 1521))) (CONNECT_DATA =(SERVICE_NAME = {SERVICENAME}))'; or
[note this one connects to schema owner using SID] sql> create database link from_db CONNECT TO <schema_owner> IDENTIFIED BY <password>
USING '(DESCRIPTION = (ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = {HOSTIPADDRESS})(PORT = 1521))) (CONNECT_DATA =(SID = {SIDNAME}))'; select * from dba_directories;

exit sql.



impdp DIRECTORY=<SomeDirName from query above> NETWORK_LINK=from_db schemas=<schema_owner>

Username: / as sysdba

OR

impdp DIRECTORY=<SomeDirName from query above> NETWORK_LINK=from_db schemas=<schema_ownerA>,<schema_ownerB>
remap_schema=<schema_ownerA>:<schema_ownerC> remap_tablespaces=<tablespaceA>:<tablespaceC>

SQL> select file_name from dba_data_files   2 ;

FILE_NAME


C:\ORACLEXE\ORADATA\XE\USERS.DBF
C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF
C:\ORACLEXE\ORADATA\XE\UNDO.DBF
C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF
C:\ORACLEXE\ORADATA\XE\MYTABLESPACE

SQL> alter system set db_create_file_dest='C:\ORACLEXE\ORADATA\XE';

System altered.

SQL> create tablespace abc; <<<<NOTE: NO DATAFILE DEFINITION WAS PROVIDED HERE. Tablespace created.

SQL> select file_name from dba_data_files;

FILE_NAME


C:\ORACLEXE\ORADATA\XE\USERS.DBF
C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF
C:\ORACLEXE\ORADATA\XE\UNDO.DBF
C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF
C:\ORACLEXE\ORADATA\XE\MYTABLESPACE
C:\ORACLEXE\ORADATA\XE\XE\DATAFILE\O1_MF_ABC_6S7J8KB8_.DBF

6 rows selected.

[demonstrated using OracleXE (10.2.0.1) on windows - don't have the patience to try and munge my VMS 7.3-2 system - besides not even sure 11gR2 would even install on it...] Received on Wed Mar 30 2011 - 18:51:17 CDT

Original text of this message