Oracle connection configuration and instance backup and recovery, oracle instance

Oracle connection configuration and instance backup and recovery, oracle instance

Background: It is impossible for a team to build their own local database for project development. In most cases, the database on the server is used in a unified manner. In this case, remote database connection is required. In addition, you also need to migrate the database. At this time, you need to back up and restore the database.

Starting from creating a database instance:

1. Create a database instance

Create a database instance by using the database configuration assistant and obtain the configuration information of the data instance.

SID is the name of the database instance: ZHeOracle (case-insensitive). You can also query the database instance using SQL commands:

 

After a database instance is available, you must configure a listener for the database instance so that the instance can accept the connection request sent by the client.

Ii. Server Listener Configuration

Configure the listening location and Database Service, and click the file to save the configuration.

To connect a client to a remote server, configure the service name:

Iii. Client Service naming Configuration

Enter the IP address of the remote network database to be connected.

Next, perform a connection test. If the logon fails, the default user may be locked. You can use the system user test to change the logon status.

Supplement:

The above server listener and client service names are configured on the visual interface. The following describes the non-visual Configuration:

Oracle installation path: E: \ oracle \ product \ 10.2.0 \ client_1 \ network \ ADMIN

Find the following file:

Tnsnames. ora-Client Service naming configuration file

Listener. ora --- server listener Configuration File

Note: The Global Database Name and database instance name are not the same concept!

Iv. Client Login

Currently, the server database and client are both on the local machine (127.0.0.1). Make sure the related services are enabled:

Use SQL Plus to log on:

Use the service name of the client, which is ZHe_Test (Case Insensitive)

Use plsqldev to log on:

The red part is also the service name

V. backup and recovery of database instances

Take the plsqldev tool for database backup as an example to export and restore database instances:

1. export the user database object: Tools-export user objects

 

The user ZHE has these user objects. Select Export to Export the. SQL file.

2. export user database data (such as data records): Tools-export tables

The exported file format is. dmp.

3. Restore the database

Preparations (Create Table space and user script. SQL ):

Because the backup database table may have been stored in a tablespace (for example, it was originally stored in the zhe_tbs tablespace ), if the database to be imported does not have the zhe_tbs tablespace, an error will be reported during the import operation. Here code1 is used to create the zhe_tbs tablespace (D: \ ZHe_Oracle \ ZHe_Orac is the path of the database instance to be imported, and you must have sufficient permissions ). Code creates a user and sets the user's tablespace to zhe_tbs. code5 and 6 authorize zheuser the following roles:

Tools-import tables (Note: import the user database object first, and then import the database records. An error will be reported if the sequence is incorrect !) :

Import user database objects:

Import database object data:

The database recovery has ended.