Shulou(Shulou.com)06/02 Report--

Migrate AA database to BB database

1. AA database to view the user's default tablespace

Select username,default_tablespace from dba_users where username='AA_Username'

2. View the role of the user

Select * from user_role_privs

3. The location of the data file of the BB database

SQL > select name from v$datafile

NAME

D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ CCOMS\ SYSTEM01.DBF

D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ CCOMS\ UNDOTBS01.DBF

D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ CCOMS\ SYSAUX01.DBF

D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ CCOMS\ USERS01.DBF

D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ CCOMS\ EXAMPLE01.DBF

4. BB database creates default tablespaces for users, and data files are automatically extended.

SQL > create tablespace BB_default_space

2 datafile'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ CCOMS\ BB_default_space.DBF'

3 size 5M autoextend on

The tablespace has been created.

5. Create a new user in BB database

SQL > create user BB_Username

2 identified by BB_Username000

3 default tablespace AA_default_space

The user has been created.

6. BB database is authorized to new users.

SQL > grant connect,resource,dba to BB_Username

Authorization successful.

7. BB database configuration listener.ora and tnsnames.ora

Configure ip so that it can be accessed from the outside

Listener.ora file:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = D:\ oracle\ product\ 10.2.0\ db_1)

(PROGRAM = extproc)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP) (HOST = BB_IP) (PORT = 1521))

)

)

Tnsnames.ora file:

BB_Username =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = BB_IP) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = BB_Username)

)

)

Modify the listener configuration file. You need to restart the listener.

Turn off the listener: lsnrctl stop

Restart the listener: lsnrctl start

8. Configure tnsnames in another database (configured to facilitate the use of plsql). Ora

BB_Username =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = BB_IP) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = BB_Username)

)

)

9. Under which tablespaces are used to query users in AA database, the corresponding tablespaces are established.

Select distinct tablespace_name from user_segments

Where tablespace_name not in ('USERS','USERS02')

10. Generate the following script and import it into the BB database with plsql

CREATE TABLESPACE DRILL_LOB_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ TEST_LOB_SPACE.DBF' size 5m autoextend on

CREATE TABLESPACE DH_LOB_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ _ LOB_SPACE DATAFILE.DBF' size 5m autoextend on

CREATE TABLESPACE TEST_NORM_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ TEST_NORM_SPACE DATAFILE.DBF' size 5m autoextend on

CREATE TABLESPACE LOG_LOB_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ LOG_LOB_SPACE DATAFILE.DBF' size 5m autoextend on

CREATE TABLESPACE MLOG_NORM_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ MLOG_NORM_SPACE.DBF' size 5m autoextend on

CREATE TABLESPACE MLOG_LOB_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ MLOG_LOB_SPACE.DBF' size 5m autoextend on

CREATE TABLESPACE LOG_NORM_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ LOG_NORM_SPACE.DBF' size 5m autoextend on

CREATE TABLESPACE HHH_NORM_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ HHH_NORM_SPACE DATAFILE.DBF' size 5m autoextend on

CREATE TABLESPACE GPE_NORM_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ HHDS_NORM_SPACE DATAFIL.DBF' size 5m autoextend on

CREATE TABLESPACE HHDS_LOB_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ HHDS_LOB_SPACE DATAFILE.DBF' size 5m autoextend on

CREATE TABLESPACE PUB_NORM_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ PUB_NORM_SPACE DATAFILE.DBF' size 5m autoextend on

CREATE TABLESPACE PUB_LOB_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ BBII_LOB_SPACE DATAFILE.DBF' size 5m autoextend on

11. Export data from AA database

Exp AA_Username/AA_Password@AA_Database file=F:\ AA_Databaseoraclebak\ AA_Database14.dump log=F:\ AA_Databaseoraclebak\ AA_Database14.log

12. Import data from BB database: no user authorization is required

Imp BB_Username/BB_Password@BB_Database FROMUSER=AA_Username TOUSER=BB_Username ignore=yes grants=no file=F:\ AA_Databaseoraclebak\ AA_Database14.dump log=F:\ AA_Databaseoraclebak\ IMP20100121BB_Username.log

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.