Oracle database Import, export (remote, 10g, 11g)

1 Viewing version information for Oracle

(1) Connect to the database with the client, execute SELECT * from V$instance
View version Items

(2) SELECT * FROM Product_component_version

(3) or Query V$version View component-level information

1.1. Oracle parameter Settings 11g data pump export, import

Enter the CMD interface and use Sqlplus to connect to the database.

Fig. 1 Database connection Operations

Connection syntax: Sqlplus system/[email protected]

Parameter description

Parameters	Description	Note
Sqlplus	Syntax commands
System	Database administrator user Name
Oracle2013	System User Password
Orcl	Database connection Identifier	The database installation directory can be found in the Tnsnames.ora file oracle11g Directory: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN

One, the service name corresponding to the client creation and the service side
Method 1:
Modify the Tnsnames.ora file
To join the remote server naming:

SQL code
TEST_ORCL =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.110) (PORT = 1521))
)
(Connect_data =
(service_name = ORCL)
)
)

Method 2:

On the Oracle client, open Net Manager.

Create a service naming TEST_ORCL, host IP: 192.168.1.110, service name ORCL, Port 1521

Fig. 2 successfully connected to the database

environment variable settings (performed in Sqlplus)

Create or replace directory <dir_name> as ' C:\tmpdir ';

Parameter description

Parameters	Description	Note
Dir_name	Path name
C:\tmpdir	File path	It can also be another random directory, enclosed in single quotes.

Fig. 3 environment variable settings

1.2. Data backup

Backup script: EXPDP system/[email protected] Directory=file_path dumpfile=aradmin.dat logfile=aradmin.log Schemas=ARADMIN

Parameter description

Parameters	Description	Note
Expdp	Syntax commands
System	Database administrator user Name
Oracle2013	System User Password
Orcl	Database connection Identifier	The database installation directory can be found in the Tnsnames.ora file oracle11g Directory: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
Directory	File directory Name	Export the storage directory for the database file
DumpFile	Database file name	Export file names for database files
LogFile	Database log file name	Log file name of the exported database
Schemas	Database user

Operation:

Fig. 4 Database backup operations

Successfully exported.

Legend 5 Successful Export

1.3. Data recovery 1.3.1. Delete aradmin User

1. Connect to the database

Sqlplus System/[email protected]

Fig. 6 Connecting the database

2. Delete Aradmin users in the target database

Drop user aradmin cascade;

Legend 7 successfully deleted Aradmin users in the target database

1.3.2. Re-creating the Aradmin user

1. Connect to the database

Sqlplus System/[email protected]

Fig. 8 Connecting the database

2. Create Aradmin Users

Create user aradmin identified by ar#admin# default tablespace arsystem temporary tablespace artmpspc Quota Unlimited on a Rsystem;

Figure 9 Creating a Aradmin user

3. Granting database Permissions

Grant alter Session,create cluster,create database link,create sequence,create session,create synonym,create table, Create View,create procedure,create trigger,query rewrite to Aradmin;

Figure 10 Assigning database permissions

1.3.3. Database Import

Import command: IMPDP system/[email protected] Directory=file_path dumpfile= ARADMIN20130606. DAT logfile= ARADMIN20130614.log Schemas=aradmin

Fig. 11 Database Import

Import complete

2.exp local export with imp local import 10g

Exp Command:
1 exp username/[email protected] file=d:test.dmp full=y
2 exp username/[ Email protected] file=d:test.dmp owner= (ly)
3 exp username/[email protected] file= d:test.dmp tables= (GRID1,GRID2)
1 One is to export test (the Oracle service name database corresponding to a database) as a whole
2 export all tables that belong to user ly
3 to grid1 the table, and export the
D:test.dmp as the exported file address

IMP command:
1 imp system/[email protected] file=d:test.dmp
2 imp system/[email protected] full=y file=d:test.dmp ignore=y
3 imp system/[email protected] file=d:test.dmp tables= (grid1) ignore=y indicates that if a table already exists in the imported database, it is ignored to import that table
3 means only import grid1 this table

Before importing the export, test whether the corresponding database is pass: tnsping test, and the same test is the service name.
All commands can be executed under CMD

Remote Operational database with EXP/IMP

Remote operation of the Oracle database, assuming that the database is on 192.168.1.110, as follows:
One, the service name corresponding to the client creation and the service side
Method 1:
Modify the Tnsnames.ora file
To join the remote server naming:

SQL code
TEST_ORCL =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.110) (PORT = 1521))
)
(Connect_data =
(service_name = ORCL)
)
)

Method 2:

On the Oracle client, open Net Manager.

Create a service naming TEST_ORCL, host IP: 192.168.1.110, service name ORCL, Port 1521

Second, test whether the remote server is unblocked

After entering CMD, execute the command: tnsping TEST_ORCL.

Third, remote operation database
Export:

SQL code
1:exp username/[email protected]_ORCL File=bak_filepath
2:exp username/[email protected]_ORCL full=y File=bak_filepath
Username user name, password password, TEST_ORCL customer service name, Bak_filepath backup file storage path

Import:

SQL code
1:imp username/[email protected]_ORCL File=bak_filepath full=y
2:imp username/[email protected]_orcl/database_name File=bak_filepath full=y
3:imp username/[email protected]_ORCL file=bak_filepath fromuser=fromadmin touser=toadmin

Username user name, password password, TEST_ORCL customer service name, Bak_filepath backup file storage path
Fromadmin the user name of the backup data, toadmin restore the user name of the database. database_name Restore to that database

Full=y exports the database as a whole, including the table structure.

Oracle database Import, export (remote, 10g, 11g)