1. Database installation
1.1 download
1.2 installation
Open the corresponding decompression path, find the installation file “setup.exe”, double-click to install, as shown below:
2. Configure the database
2.1 Creating a tablespace
Win+R Enter CMD to enter the command line, enter the following, and log in as sysdba:
sqlplus /nolog
connect / as sysdba
startup
Copy the codeOra-01301: INVALID PRIVILEGES is reported during CONN/AS SYsdba. There are two reasons for this problem. Second, local authentication of the operating system is not allowed.
- Add the current logged-in user to the orA_DBA group.
- Change the statement in the SQL.net file to
SQLNET.AUTHENTICATION_SERVICE=(NTS)
Copy the code
create tablespace TBS_CHOVA_DATA datafile 'E:\Oracle\oradata\TBS_CHOVA_DATA.dbf' size 1000 M autoextend on next 100 maxsize unlimited;
create temporary tablespace TBS_CHOVA_TEMP tempfile 'E:\Oracle\oradata\TBS_CHOVA_TEMP.dbf' size 1000 M autoextend on next 100 maxsize unlimited;
create tablespace TBS_CHOVA_IDX datafile 'E:\Oracle\oradata\TBS_CHOVA_IDX.dbf' size 200 M autoextend on next 100 maxsize unlimited;
Copy the codeNote: The path in front of the file name must exist. If it does not exist, you need to create it manually
2.2 Creating a User
Create users and assign permissions. The first smis is the user name and the second smis is the password.
create user smis identified by smis default tablespace TBS_CHOVA_DATA temporary tablespace TBS_CHOVA_TEMP;
grant connect,resource to smis;
grant dba to smis;
Copy the code2.3 Configuring Listening
Listener is a server-based network service of Oracle. It is mainly used to listen to the connection requests made by clients to the database server. Since it is a server-side service, it also exists only on the database server, where setting up the listener is done.
- Open Net Manager in Oracle
- Select the LISTENER program, LISTENER, add the address at the listening position, and fill in the IP address of the host computer, port 1521
- Select “Database Services” in the drop-down list on the right side of the window and click the Add database button. Enter the global database name in the database column that appears. Note that the global database name is different from the database SID. The global database name actually controls the uniqueness of database global names within the same network segment by domain name, just like the domain name controller in Windows. You can omit the Oracle home directory and enter SID.
2.4 Configuring the Local Service Name (Tnsnames)
- The local server name is based on the network configuration of the Oracle client. Therefore, if the client needs to connect to the database server for operation, you need to configure the local server. The attached object can be any PC that wants to connect to the database server or the database server itself. As described earlier, you can use Oracle’s built-in graphical management tool, Net Manager, to configure the Oracle client. Select the service name and click the “+” button on the upper left to pop up the following dialog box:
- Enter the Net service name, such as MyOracle, and click Next to enter the dialog box below:
- Select TCP/IP(Internet Protocol) and click Next, as shown below:
- Enter the host name and port number. Note that the host name and port number must be the same as those configured for the database server listener. Click next, as shown below:
- Select the (Oracle8i or later) service name and enter the service name. The service name here is actually the global database name in the database server-side listener configuration, and the former must be the same as the latter. The connection type is usually a dedicated server, which depends on the configuration of the database server. If a shared database server is configured, the connection type must be a shared server. Otherwise, it is recommended to select a dedicated server (refer to related documents for details about dedicated servers). After configuration, click Next, as shown below:
- If the database server related service is started, you can click the Test button to test the connection. By default, Oracle uses user Scott/Tiger to test connections. Because user Scott is an example user of Oracle, this user may not be configured for formal business databases or professional test databases. Therefore, you need to log in as a valid user to make the test successful. If the test connection is not successful, it does not matter, first click the Finish button to end the configuration. Go back to the Oracle Net Manager main window, save the configuration, The default can be found in Oracle install directory of local service configuration file (under Windows such as D: / Oracle/ora92 / network/admin/tnsnames. Ora, On Linux/Unix, $ORACLE_HOME/network/admin/ tnsnames.ora). The following figure shows the local service name:
- You can change the name of the service in the tree directory to a service name consisting of any legal characters by editing the rename menu in the menu. Note that the service name cannot start with a space character; otherwise, the database server may fail to be connected.
The network configuration and access mode are fully resolved
Ora, sqlnet. Ora, tnsnames.ora, listener.ora, sqlnet. Ora, tnsnames.ora, listener.ora, sqlnet.
ORACLE_HOME\network\admin
Copy the code- Sqlnet. ora—– works like a Linux or other Unix nsswitch.conf file that determines how to find a connection string in a connection.
For example, our client input
sqlplus sys/oracle@orcl
Copy the codeSuppose my SQLnet. Ora looks like this
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES,HOSTNAME)
Copy the codeThe client first looks for orCL records in the tnsnames.ora file. If there is no corresponding record, try to treat orCL as a host name, resolve its IP address through the network, and then connect to GLOBAL_DBNAME= orCL on the IP. Orcl is not a host name. If I look like this
NAMES.DIRECTORY_PATH= (TNSNAMES)
Copy the codeThen the client will only look up orCL records from tnsnames.ora, and there are other options in parentheses such as LDAP that are not commonly used. 2. Tnsnames.ora—— this file is similar to the hosts file in Unix and provides the tnsname to the host name or IP address, only if sqlnet.ora is similar
NAMES.DIRECTORY_PATH= (TNSNAMES)
Copy the codeIn this case, the client will attempt to use this file if TNSNAMES are in the order in which the connection string is resolved. PROTOCOL: indicates the PROTOCOL used by the client to communicate with the server. The value is usually TCP. HOST: the name or IP address of the machine on which the database is listening. Database listening is usually on the same machine as the database, so when I say the machine on which the database is listening, I usually mean the machine on which the database is listening. On UNIX or WINDOWS, you can get the machine name by using the hostname command at the command prompt of the machine where the database is listening, or the IP address by using the ipconfig(for WINDOWS) or ifconfig (for UNIX) command. Note That you must run the ping command on the client to ping the name of the machine where the database listens regardless of the machine name or IP address. Otherwise, you need to add the resolution of the name of the machine where the database listens in the hosts file. Ora file on the server or run the LNSRCTL status [listener name] command at the command prompt of the host on which the database is listening. The value of Port here must be the same as the Port on which the database is listening. SERVICE_NAME: On the server, log in as the system user and run the sqlplus> show parameter SERVICE_NAME command. ORCL: the corresponding local machine, another IP address corresponding to SALES, which also defines whether to use the master server or shared server mode for connection.
The TNSNAME entered when connecting
ORCL = (DESCRIPTION = (ADDRESS_LIST = # (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA = # SQL > select service_name, SQLPLUS> show parameter service_name; ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =dg1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = sales) ) )Copy the codeNote: If the database server uses MTS and the client needs to use database Link, it is best to specify that the client uses dedicated connection. Otherwise, you will encounter many ORACLE bugs related to distributed environments. In general, the database server is better off using direct connections, unless you have close to 1000 live database connections. Ora —— a configuration file for the listener process, which receives remote requests for access to the database and forwards them to the Oracle server process. So if you are not using a remote connection and do not need to use OEM, the listener process is not required. Similarly, shutting down the Listener process does not affect the existing database connection. An example of a listener. ora file
#listener.ora Network Configuration File: Ora # Generated by Oracle Configuration Tools. #E:\oracle\product\10.1.0\Db_2\NETWORK\ADMIN\listener Which instance does the process serve in this case ORCL, SID_LIST_LISTENER = (SID_LIST = (SID_DESC =)) GLOBAL_DBNAME = boway (ORACLE_HOME = /u01/app/oracle) (SID_NAME = ORCL)) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521)))Copy the codeThe example above is one of the simplest, but also the most common. A listener process serves an instance(SID). Listener action command
ORACLE_HOME/bin/lsnrctl start
Copy the codeOthers include stop,status, and so on. After typing an LSNRCTL, see help. All three files mentioned above can be configured using the graphical configuration tool
$ORACLE_HOME/netmgr in the form of $ORACLE_HOME/netca wizardCopy the codeProfile is configured with sqlnet.ora. Service Name is configured with tnsnames.ora files, and listeners. You can try the configuration of the listener process and then look at the configuration file. So the overall structure is connected in different ways.
The connection process
When you enter
sqlplus sys/oracle@orcl
Copy the codeSqlnet. ora = TNSNAME 2; Orcl host name, port number, and service_name 3. Establish a connection to the listener process if there is no problem with it. 4. The listener takes the next action based on the server mode, such as dedicated server mode or shared server mode. The default is dedicated server mode and the client connects to the server process of the database if there is no problem. 5. After the network connection is established, the listener process completes its historical tasks. # #
Several connection modes
Simple named Connection: Enabled by default, no client configuration is required, supports only TC/IP (without SSL), and does not support advanced connections, such as fail-over during connection, source routing, and load balancing. The connection mode is as follows:
connect hr/[email protected]:1521/dba10g
Copy the codeDb.us.oracle.com indicates the host name or IP address,1521 indicates the connection port, and dba10g indicates the service name. You can run the show parameter service command to view the information. Local naming: client name resolution file tnsname.ora is required, all Oracle Net protocols are supported, and advanced connection options are supported. The connection method is as follows: connect hr/hr@orcl, where orCL indicates the database instance name and directory name: LDAP with Oracle Net name resolution loaded: Oracle Internet Directory and Microsoft Active Directory Services. Support for all Oracle Net protocols and advanced connection options. The connection mode is as follows: connect HR /hr@orcl External naming: Use supported non-Oracle naming services, including Network Information Service (NIS) External naming, distributed Computing Environment (DCE) unit directory service (CDS)
Several forms of validation used for connection
sqlplus / as sysdba
Copy the codeThis is typical operating system authentication and does not require a listener process
sqlplus sys/oracle
Copy the codeThis connection mode can only connect to the local database and also does not require the listener process
sqlplus sys/oracle@orcl
Copy the codeThis approach requires that the listener process be available. The most common connection is through the Internet. In the preceding authentication methods, the sys user or other users authenticated by password files do not require the database to be available. The operating system does not require the database to be available. The database must be in the open state for database user authentication. Log in as a normal user
[oracle@dg1 admin]$ sqlplus sys/oracle
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 13 16:18:33 2011
Copyright (c) 1982.2005, Oracle. All rights reserved.
ERROR:
ORA- 01034:ORACLE not available
ORA- 27101:shared memory realm does not exist
Linux Error: 2: No such file or directory
Copy the codeEnter user-name: effect of Remote_Login_Passwordfile in initsid. ora on authenticationCopy the codeThree optional values: NONE The default, indicating that the Oracle system does not use password files and that privileged users authenticated by the operating system have SYSORA and SYSOPER privileges. EXCLUSIVE 1. Indicates that only one database instance can use password file 2. SYSORA and SYSOPER permissions can be assigned to users other than SYS SHARED 1. SYSORA and SYSOPER permissions are not allowed to be assigned to users other than SYS, so if you want to log in as an operating system, Remote_Login_Passwordfile should be set to NONE for domain name (host name) resolution
/etc/hosts (UNIX) or Windows \hosts(WIN98) winnt\system32\drivers\etc\hosts (WIN2000)Copy the codeThe client needs to write the mapping between the DATABASE server IP address and host name.
127.0.0.1 dg1
192.168.0.35 oracledb oracledb
192.168.0.45 tomcat tomcat
202.84.10.193 bj_db bj_db
Copy the codeIn some cases, the tnSPing database server alias is displayed successfully, but sqlplus username/password@servicename is not available, and JDBC thin Link is not available. The possible cause is that the mapping between the SERVER IP address and host name is not configured on the DNS server. If there is a private IP address and a public IP address on the Internet at the same time, the private IP address is written first and the public IP address is written last. You are advised to save a backup file before editing. When adding a line, you are advised to copy and paste it to avoid error of space or TAB characters when editing hosts. In a UNIX environment with multiple ORACLE databases, you need to configure the following environment variables on the OS client
ORACLE_SID=appdb; export ORACLE_SID TWO_TASK=appdb; export TWO_TASKCopy the codeTo specify the default target database
The usual mistakes may be used:
1. LSNRCTL status Checks the status of the listener process on the server
LSNRCTL>help
LSNRCTL>status
LSNRCTL> services
Copy the code2. Tnsping Check whether the configuration of sqlnet.ora and tnsname.ora files on the client is correct, and check the status of the listener process on the server.
[oracle@dg1 dbs]$ tnsping orcl
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 13-FEB-2011 16:48:06
Copyright (c) 1997.2005, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT =1521))) (CONNECT_DATA = (SID = orcl)))
OK (10 msec)
Copy the code3. Check whether Instance is started
SQL>select instance_name,host_name,status from v$instance;
Copy the codeCheck whether the database is open or in the mount state.
SQL>select open_mode from v$database
INSTANCE_NAME STATUS
------------------------------ ------------------------------------
orcl OPEN
Copy the codeUsing tnsname is the default way to access the database, but there is a problem that clients need to configure tnsnames.ora files. If your database server address changes, you will need to edit the client file again. Accessing the database through hostname eliminates this hassle. View database name
SQL> select name from v$database;
NAME
---------------------------
ORCL
Copy the codeThe listener.ora on the server needs to be modified
- The listener configuration file listener.ora
- The tnsname.ora file is no longer required for local parsing with host Naming
- listener.ora Network Configuration File:
D: \ oracle \ product \ 10.1.0 \ db_1 \ NETWORK \ ADMIN \ listener oraCopy the code# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
# (SID_NAME = PLSExtProc)
(SID_NAME = orcl)
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /u01/app/oracle)
# (PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
)
Copy the codeClient sqlnet.ora if it is confirmed that TNSNAME is not used for access, you can remove TNSNAMES
Sqlnet. ora Network Configuration File: d: oracle product 10.1.0 db_1 Network ADMIN sqlnet.oraCopy the codeGenerated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (HOSTNAME)
Copy the codeThe tnsnames. ora file does not need to be configured or deleted. The following is the network and operating system configuration problem, how to resolve my host name problem can be connected through the following way
sqlplus sys/oracle@orcl
Copy the codeIn this case, the orCL server is connected and the listener determines the service_name you want to connect to
2.5 Oracle Connection Failure
To troubleshoot the connection problem between the client and the server, check whether the client configuration is correct (the client configuration must be consistent with the database server listening configuration), and then troubleshoot the problem according to the error message. There are several common connection problems listed below:
Ora-12541: TNS: no listener
Obviously, the listener on the server side is not started. Also check whether the IP address or port of the client is correct. Start the listener:
lsnrctl start
Copy the codeOra-12500: TNS: Listener cannot start dedicated server process
For Windows, the Oracle instance service is not started. Start instance service:
C: oradim - startup - sid myoracleCopy the codeOra-12535: TNS: Operation timed out
There are many reasons for this problem, but most of them are related to the Internet. To resolve this problem, check whether the network between the client and server is normal. If the network is normal, check whether the firewalls on both ends block the connection.
Ora-12154: TNS: Service name cannot be processed
Check whether the entered service name is the same as the configured service name. Note also the generated local service name file (as shown in Windows
D: oracleora92networkadmin tnsnames. Ora or Linux/Unix/network/admin/tnsnames. OraCopy the codeThere must be no space before the first line of each service name.
Ora-12514: TNS: Listener process cannot resolve SERVICE_NAME given in connection descriptor
Open Net Manager, select the service name, and check that the service name entered in the service identification bar is correct. The service name must be the same as the global database name configured for the server-side listener. Also check sqlnet.ora, for example adding EZCONNECT to the names. DIRECTORY_PATH parameter if you want to connect using the easy connection method.
Ora-12518 TNS: Listener unable to distribute client connection
This error occurs for two reasons: in shared mode there are too few dispatchers and in exclusive mode the number of processes (Proces ses) exceeds the default maximum number of processes in the database. 2, select count(*) from v$session; View the number of processes in the system. If the number of processes is insufficient, you can expand the PGA to increase the number of processes.
alter system set workarea_size_policy=auto scope=both; Alter system set pga_aggregate_target=512m scope=both;Copy the codeIf the number of dispatchers is too small, run the following command:
alter system set dispatchers = '(protocol=tcp)(dispatchers=3)(service=oracle10xdb)';
Copy the code7. A message is displayed indicating that the path cannot be found when the listening service is started in Windows
Run the command or start the listener in the service window, prompting that the path cannot be found, or the listener service starts abnormally. Open the registry and enter
HKEY_LOCAL_MACHINE/SYSTEM/Current ControlSet/Services/OracleOraHome92TNSListener
Copy the codeCheck to see if the ImagePath string entry exists. If not, set it to D:oracleora92BINTNSLSNR and change it accordingly for different installation paths. This method also applies to Oracle instance services
HKEY_LOCAL_MACHINE/SYSTEM/Current ControlSet/Services/Oracle ServiceMYORACLE
Copy the codeCheck whether the ImagePath string entry exists. If not, create a new one and set the value to D :oracleora92 binoracle.exe MYORACLE. The above are some common problems with Oracle client connection to the server, but not all connection exceptions are included. The key to solve problems lies in methods and ideas, rather than every question has a fixed answer.
8.TNS-12537, TNS-12560, TNS-00507 Linux Error: 29: Illegal seek error When Starting the Listener
On Linux or Unix, the following error occurs if the /etc/hosts file is incorrectly configured
lsnrctl start
LSNRCTL for HPUX: Version 10.1.0.4.0 - Production on 01-JUL-2005 10:16:59 Copyright (c) 1991.2004, Oracle. All rights reserved.
Starting /db02/product/10.1/bin/tnslsnr: please wait...
TNS-12537: TNS:connection closed
TNS-12560: TNS:proto adapter error
TNS-00507: Connection closed HPUX Error: 29: Illegal seek
Copy the codeSolution: Check whether the user has permission to access the /etc/hosts file and whether the /etc/hosts file contains the permission
127.0.0.1 localhost. Localdomain localhostCopy the code9.ORA-12505, TNS:listener does not currently know of SID given in connect descriptor The Connection descriptor used by the client The was: 192.168.1.1:1521: BBCD
This problem usually occurs when JDBC is used to connect to the database. Note that the BBCD position should be sid_name, and the JDBC configuration format is
JDBC: oracle: thin: @ IP/HOSTNAME: PORT: SID such as JDBC: oracle: thin: @ 145. * *. * *. * * : 1521: db2 z * * *Copy the codeOra-12514: TNS: The listener cannot currently recognize the requested service in the link descriptor
This problem is caused by the absence of SID_LIST description item of listener. This problem is often encountered when netCA is used for configuration. The following is an example
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = / opt/oracle/product / 9.2.0.4) (the PROGRAM = extproc)) (SID_DESC = (GLOBAL_DBNAME = SAMPLE.COM) (ORACLE_HOME = / opt/oracle/product / 9.2.0.4) (SID_NAME = SAMPLE) )) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = tcy.com)(PORT = 1521)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) )))Copy the code11.ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Ora-12528: SQL > MOUNT tnsnames. ORA (UR=A); SQL > MOUNT tnsnames. ORA (UR=A); Lisnter. ora adds the following content
(SID_DESC = (GLOBAL_DBNAME = ammicly) (ORACLE_HOME = C: oracle product 10.1.0 db_1) (SID_NAME = ammicly)) Or add tnsnames.ora: (UR=A)Copy the codeOra-01034: ORACLE not available and ORA-27101: Shared memory does not exist
Check whether TNSPING works normally and LSNRCTL status is normal. Check the local_listener parameter. Pmon only dynamically registers listeners whose port number is 1521. Otherwise, pMON cannot dynamically register listeners
alter system set local_listener='(ADDRESS =(PROTOCOL=TCP)(HOST=10.201.1.134)(PORT=1521)(SID=siebtest))';
Copy the code13.ORA-12520 TNS:Listener count not find available handler for requested type of server
There are three possibilities: 1. Check whether the database is a dedicated server, but the connection mode is set to shared in the TNsname. ora configuration
Ora: open tnsname.ora and set (server = shared) to (server = dedicate).Copy the code2. It is caused by insufficient process:
select count(*) from v$session;
show parameter processes
show parameter sessions
Copy the code3. The local_listener parameter is set improperly. For details, see the previous section.
14.TNS-12542: TNS:address already in use
TNS-12560: TNS:protocol adapter error
TNS-00512: Address already in use
Copy the codeCheck the configuration of/etc/hosts, whether to have multiple IP refer to the same host name Reference to: lzysystem.iteye.com/blog/424569 blogold.chinaunix.net/u2/82873/sh… Gggwfn1982.blog.163.com/blog/static… Guolr.iteye.com/blog/549692 blog.sina.com.cn/s/blog_4cd0… Pengxianfeng.i.sohu.com/blog/view/8… Blog.sina.com.cn/s/blog_517c… Luoping.blog.51cto.com/534596/1062… Xiekeli.blogbus.com/logs/936195… www.linuxidc.com/Linux/2012-…
15. A message is displayed indicating that the path cannot be found when the listening service is started in Windows
Run the command or start the listener in the service window, prompting that the path cannot be found, or the listener service starts abnormally. Open the registry and enter
HKEY_LOCAL_MACHINE/SYSTEM/Current ControlSet/Services/OracleOraHome92TNSListener
Copy the codeCheck to see if the ImagePath string entry exists, and if not, set it to
D:/oracle/ora92/BIN/TNSLSNR
Copy the codeChange the installation path Settings accordingly. This method also applies to Oracle instance services
HKEY_LOCAL_MACHINE/SYSTEM/Current ControlSet/Services/Oracle ServiceMYORACLE
Copy the codeCheck to see if the ImagePath string entry exists, if not, create a new one, set to
d:/oracle/ora92/binORACLE.EXE MYORACLE
Copy the codeThe above are some common problems with Oracle client connection to the server, but not all connection exceptions are included. The key to solve problems lies in methods and ideas, rather than every question has a fixed answer.
Ora-12638: Identity retrieval failed
Start -> Programs -> Oracle -> Configuration and Migration Tools -> Net Manager→ Local → Profiles →Oracle Advanced Security → Verification → Remove "NTS" from the selected methodCopy the codeThat’s it.
3. Back up and restore the database
3.1 Backing up a Database
Exp export
ORACLE 11g is a new feature that does not allocate segment data when the table has no data, so exp does not export the table. To solve this problem, first execute the following SQL:
select 'alter table '||table_name||' allocate extent; ' from user_tables where num_rows=0 or num_rows is null;
Copy the codeCopy the results of the above statement and execute them. To export the database, run the following command line
exp rad/rad@orcl file='E:\rad.dmp' buffer=40960000
Copy the codeUse the EXPDP export tool
Start by specifying directory -lmm_db_bak where dump files and log files reside
sqlplus /nolog
connect / as sysdba
startup
create or replace directory lmm_db_bak as 'D:/lmm_db_bak';
grant read,write on directory lmm_db_bak to public;
Copy the code- To view
select * from dba_directories;
Copy the code- export
expdp rad/rad@orcl schemas= rad dumpfile=rad-20170101.dmp directory= lmm_db_bak
Copy the code- Export some tables using include
expdp rad/rad@orcl schemas= rad dumpfile=rad-20170101.dmp directory= lmm_db_bak include=table:\"like \'CT%\'\"
Copy the codeNote: This method can directly export the empty tables
3.2 Restoring the Database
Imp import
If you want to restore a complete Oracle database, delete the current user, create a user, and import the user to the database
- Delete user
drop user rad cascade;
Copy the code- Create a user
create user rad identified by rad default tablespace TBS_LLM_DATA temporary tablespace TBS_LLM_TEMP;
grant connect,resource to rad;
grant dba to rad;
Copy the codeRun the following command to import the database
imp rad/rad@orcl file='E:\rad.dmp' buffer=40960000 full=y;
Copy the codeImport incomplete database DMP file (partial table)
If you have the same table in your database and DMP, importing DMP files will not import existing tables with the same name. Need to delete their original Curry watch in advance.
Use the impDB import tool
impdp rad/rad@orcl schemas= rad directory= lmm_db_bak dumpfile=RAD-20170101.DMP FULL=y;
Copy the codeIf the user to be imported already exists: 1. Export the user expdp user1/pass1 directory=dumpdir dumpfile=user1. DMP 2. Impdp user2/pass2 directory=dumpdir dumpfile=user1. DMP REMAP_SCHEMA=user1:user2 EXCLUDE=USER full=y; If the user to be imported does not exist: 1. Export user expdp user1/pass1 directory=dumpdir dumpfile=user1. DMP 2. Impdp system/passsystem directory=dumpdir dumpfile=user1. DMP REMAP_SCHEMA=user1:user2 full=y; 3. User2 is automatically created and has the same permissions and tablespace as user1. However, you cannot log in to the system using user2 and must change the password of user2Copy the code