Oracle Database Migration Steps Ideas

background : as a dba,oracle database migration is often the thing to encounter, just recently I was also migrating a business system, the database is Oracle, by the way record.

Description : to make it easier to explain, the old database is called a, and the new is B. The user and password are irp/irp, and B is a completely new environment.

Step thinking :

1. Export the data file from A.

Sqlplus/nologconn/as sysdbaexp irp/irp buffer=64000 file=d:\test. DMP Log=d:\test.log Owner=irp

2. View the user default tablespace on a machine to create the same tablespace 650 when importing ) this.width=650; "Src=" http://images.csdn.net/syntaxhighlighting/ Outliningindicators/none.gif "align=" Top "/>

Sql> Select Username,default_tablespace from dba_users where username = ' IRP ';       USERNAME Default_tablespace------------------------------------------------------------ IRP IRP

3. View the table space used by the user

Sql> Select DISTINCT owner, tablespace_name from Dba_extents where owner like ' IRP '; OWNER tablespace_name------------------------------------------------------------I RP IRP

4. View the data file for the tablespace to create the appropriate size data file on B.

Sql> Select File_name,tablespace_name from Dba_data_files where tablespace_name in ' IRP '; file_name Tablespace_name------------------------------------------------- ---------------------D:\APP\ADMINISTRATOR\ORADATA\ORCL\IRP. DBF IRP If the user uses more than one table space, the query statement is as follows select File_name,tablespace_name from Dba_data_files where Tablespace_name in (' IRP1 ', ' IRP 2 ');

5. Check the table space of machine B to see if there is an IRP (this step is not required if it is a new server)

The select name from V$tablespace the where name in ' IRP ' is not found, stating that there is no such two tablespace that needs to be created.

6. Create a tablespace, user, and authorization on B

Create large file tablespace create bigfile tablespace  "IRP"  DATAFILE  ' D:\app\oracle\oradata\orcl\irp.dbf '  size 100m autoextend on next 100m maxsize unlimited logging  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;  Create a default table space or  CREATE TABLESPACE  "IRP"  DATAFILE  ' D:\app\oracle\oradata\orcl\irp.dbf '  size  100M AUTOEXTEND ON NEXT 100M MAXSIZE 10000M LOGGING EXTENT  management local;  creating the user       CREATE USER  IRP  profile   "DEFAULT"  IDENTIFIED BY  "IRP"  DEFAULT TABLESPACE  "IRP"  temporary  TABLESPACE  "TEMP"  ACCOUNT UNLOCK;  authorization       GRANT  " CONNECT  TO  IRP;       grant  "RESOURCE"  TO    "IRP";        grant unlimited tablespace to  "IRP"; 

Note: The Bigfile table space can only create one datafile data file, Smallfile table space may create up to 1024 data files

7. If the user already exists on Server B, how to delete the user.

See if the user sql> select username from dba_users where username= ' IRP ' is present;        Delete the user and all objects owned by drop user irp cascade;  see if there are any objects under this user; select  object_type,count (*)  from all_objects where owner= ' IRP '  group by  object_type        object_type            count (*)------------------------------------------------------------ ------ ----1    sequence    3      2     PROCEDURE    5       3     LOB    139       4     Package    3       5    package  body    2       6    trigger    1        7    TABLE    384        8    INDEX    426        9    function    6

At this point, if the user is connected, the drop will go wrong, you must first kill the user's session, and then drop users

Build the statement that kills the IRP user session and execute,select  ' alter system kill session  ' | | sid| | ', ' | | serial#| | "  immediate; '  from v$session where username= ' IRP '; The statement above is to construct the statement that kills the IRP user SESSION, then copy the statements, paste them into the sqlplus, To kill the IRP session. ' Altersystemkillsession ' | | sid| | ', ' | | serial#| | " IMMEDIATE; '       --------------------------------------------------------------alter  system kill session  ' 9,42043 '  immediate;      alter  system kill session  ' 10,9137 '  immediate;       alter  system kill session  ' 72,17487 '  immediate;        alter system kill session  ' 84,3280 '  immediate;        alter system kill session  ' 91,976 '  immediate;        alter system kill session  ' 100,13516 '  immediate;       alter system kill  session  ' 111,5973 '  immediate;       alter system  kill session  ' 115,4751 '  immediate;       alter system  kill session  ' 120,10356 '  immediate;       alter  system kill session  ' 211,4075 '  immediate;       alter  system kill session  ' 216,48068 '  immediate;

8. Finally import data on B

Be careful to exit sqlplus when the IMP is executed and execute directly at the command line.

IMP test/test buffer=64000 file=d:\test. DMP Log=d:\imptest.log fromuser=irp Touser=irp

This article is from the "Technical Achievement Dream" blog, please be sure to keep this source http://pizibaidu.blog.51cto.com/1361909/1854468

Oracle Database Migration Steps Ideas