1. Database installation
1.1 download
Download from oracle's official website according to the number of bits of your operating system (take oracle 11g as an example) After that, unzip the two compressed packages into the same folder (it should be noted that it is best not to have irregular characters such as Chinese and spaces in the path name of this folder.) Note: the Oracle DataBase server is downloaded!! Don't make a mistake, download it as a client!!
1.2 installation
Open the corresponding decompression path, find the installation file "setup.exe", and double-click to install, as shown in the following figure:
Cancel "I want to accept security updates through My Oracle Support" in the figure below and click next
Note that if it is a notebook, select "desktop class" and the server will select "server class"
Customize oracle base directory (installation path) and password.
Prerequisite check. The installer will check whether the software and hardware systems meet the minimum requirements for installing this Oracle version. Just go to the next step.
Summarize some relevant selection and configuration information before installation. You can save it as a file or click finish without saving it.
After the database management software files and dbms files are installed, a database with the orcl name in front of the default instance database will be automatically created and installed.
Finally, complete the oracle installation.
2. Configuration database
2.1 creating tablespaces
Win+R enter cmd into the command line, enter the following, and log in as sysdba:
sqlplus /nolog connect / as sysdba startup
Note: ORA-01301:insufficient privileges error is encountered during conn / as sysdba Solution: there are two reasons for this problem. One is that there is no local system in the oracle user group; Second, local authentication of the operating system is not allowed.
- Add the currently logged in user to ora_ In the DBA group.
- In SQL Net file
SQLNET.AUTHENTICATION_SERVICE=(NTS)
Create tablespace
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;
Note: the path in front of the file name needs to be real. If there is no such path, you need to create it manually
2.2 creating users
Establish 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;
2.3 configure monitoring
Listener is a kind of network service based on Oracle server, which is mainly used to listen to the connection request from the client to the database server. Since it is a server-side service, it only exists on the database server, and the listener setting is also completed on the database server.
- Open Net Manager under oracle program
- Select the LISTENER, LISTENER, and add the address at the listening location. The host fills in the IP address of its own computer and port 1521
- Select "database service" in the drop-down option in the right column 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 here is different from the database SID. The global database name actually controls the uniqueness of the global database name in the same network segment through the domain name, such as the domain name controller under Windows. Oracle home directory can be left blank. Enter SID.
2.4 configure local service name (tnnames)
- The local service name is based on the network configuration of the Oracle client. Therefore, if the client needs to connect to the database server for operation, it needs to configure the client. Its dependent object can be any PC that wants to connect to the database server for operation, or the database server itself. As mentioned earlier, you can use the graphical management tool Net Manager provided by Oracle to complete the configuration of Oracle client. Select the service name and click the "+" button on the upper left to pop up the dialog box as shown below:
- Enter the Net service name, such as myoracle, and click next to enter the following dialog box:
- 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 here must be the same as the host name and port number configured by the database server-side listener. Click next, as shown below:
- Select the service name (Oracle8i or later) and enter the service name. The service name here is actually the global database name in the database server listener configuration. The former and the latter must be the same. 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 here should be a shared server. Otherwise, it is recommended to choose a dedicated server (for the introduction of a dedicated server, please refer to the relevant documents). Click next after configuration, as shown below:
- If the related services on the database server are started, you can click the test button to test the connection. Oracle tests and connects through scott/tiger by default. Since scott is an example user of Oracle, this user may not be configured for the formal business database or professional test database, so it needs to be changed to a valid user login before the test can succeed. If the test connection here is not successful, it doesn't matter. First click the finish button to end the configuration. Return to the main window of Oracle network manager (Oracle Net Manager) and save the configuration. By default, you can find the local service name configuration file in the Oracle installation directory (such as D:/oracle/ora92/network/admin/tnsnames.ora under Windows and $oracle_home / network / admin / tnsnames.ora under Linux/Unix). The configured local service name is shown as follows:
- The service name in the tree directory can be changed to a service name composed of any legal characters through the rename menu in the edit menu. Note that there must be no space character in front of the service name, otherwise the database server may not be connected.
Complete analysis of network configuration and access mode
Three configuration files listener ora ,sqlnet.ora ,tnsnames.ora is placed in the directory:
ORACLE_HOME\network\admin
- sqlnet.ora ---- nsswitch, which is similar to linux or other unix Conf file, which determines how to find the connection string in a connection. For example, our client input
sqlplus sys/oracle@orcl
If my sqlnet Ora looks like this
SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES,HOSTNAME)
Then, the client will start at tnsnames Find the record of orcl in ora file If there is no corresponding record, try to regard orcl as a host name, resolve its ip address through the network, and then connect to global on this ip_ Dbname = orcl. Of course, orcl here is not a host name. If I were like this
NAMES.DIRECTORY_PATH= (TNSNAMES)
Then the client will only be from tnsnames Ora finds the records of orcl. There are other options in parentheses, such as LDAP, which are not commonly used. 2. Tnsnames.ora ------ This file is similar to the unix hosts file. The correspondence from tnsname to host name or ip is provided only when sqlnet Similar in ora
NAMES.DIRECTORY_PATH= (TNSNAMES)
In this way, if TNSNAMES is in the order in which the client parses the connection string, it will try to use this file. PROTOCOL: the communication PROTOCOL between the client and the server. It is generally TCP. This content generally does not need to be changed. HOST: the machine name or IP address of the machine where the database listens. Generally, the database listens on the same machine as the database, so when I say that the machine where the database listens on generally refers to the machine where the database is located. Under UNIX or WINDOWS, you can obtain 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. It should be noted that no matter the machine name or IP address is used, the client must Ping the machine name of the machine where the database is listening through the ping command. Otherwise, the resolution of the machine name of the machine where the database is listening needs to be added to the hosts file. Port: the database listens on the listening Port. You can view the listener on the server side The ora file or the lnsrctl status [listener name] command at the command prompt of the machine where 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 side, after logging in with the system user, sqlplus > show parameter service_ View with the name command. ORCL: the corresponding local machine and another IP address corresponding to SALES. It also defines whether to use the primary server or shared server mode for connection.
TNSNAME entered when connecting
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
# The following is the host, port and protocol corresponding to this TNSNAME
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
# Using the dedicated server mode to connect needs to match the server mode. If not, it will be automatically adjusted according to the server mode
(SERVER = DEDICATED)
# Corresponding service_name ，SQLPLUS>; show parameter service_name; View
(SERVICE_NAME = orcl)
)
)
# The following is similar
SALES =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =dg1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sales)
)
)Note: if the database server uses MTS and the client program needs to use database link, it is best to clearly indicate that the client uses the dedicated direct connection mode, otherwise many Oracle bugs related to the distributed environment will be encountered. In general, it is better to use a direct connection to the database server, unless your number of real-time database connections is close to 1000. 3. listener. Ora ------ configuration file of listener listener process I won't say much about the listener process. I accept the remote access application to the database and transfer it to the oracle server process. Therefore, if the remote connection is not used and OEM is not required, the listener process is not necessary. Similarly, closing the listener process will not affect the existing database connection. Listener. Examples of ora files
#listener.ora Network Configuration File:
#E:\oracle\product\10.1.0\Db_2\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
# The following defines which instance the LISTENER process provides services for. Here is ORCL and its corresponding ORACLE_HOME and GLOBAL_DBNAME where GLOBAL_DBNAME is not required unless
# Use HOSTNAME for database connection
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = boway)
(ORACLE_HOME = /u01/app/oracle)
(SID_NAME = ORCL)
)
)
# The name of the listener. A database can have more than one listener
# The following is the protocol, IP, port, etc. that the listener listens to. Tcp1521 port is used here, and the host name is used
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
) The above example is the simplest, but also the most common. A listener process serves an instance(SID). Operation command of listener
ORACLE_HOME/bin/lsnrctl start
Others, such as stop,status, etc. After typing a lsnrctl, see help. The three files mentioned above can be configured through the graphical configuration tool
$ORACLE_HOME/netca Wizard form $ORACLE_HOME/netmgr
I'm used to netmgr, The profile is configured with sqlnet Ora is the way of name resolution service name is configured with tnsnames Ora file listeners is configured as listener Ora file, the listener process You can try the specific configuration, and then look at the configuration file. In this way, the overall structure has different connection modes.
Connection process
When you enter
sqlplus sys/oracle@orcl
1. Query sqlnet Ora looked at the resolution of the name and found that it was TNSNAME 2. Query tnsnames Ora file, find the orcl record, and find the host name, port and service_name 3. If there is no problem with the listener process, establish a connection with the listener process. 4 . According to different server modes, such as dedicated server mode or shared server mode, the listener takes the next action. The default is the dedicated server mode. If there is no problem, the client will connect to the server process of the database. 5 . At this time, the network connection has been established, and the historical mission of the listener process has been completed.
Several connection modes
Simple named connection: It is enabled by default and does not require client configuration. It only supports TC/IP (no SSL), and does not support advanced connections, such as failover during connection, source routing, and load balancing. The connection method is as follows:
connect hr/hr@db.us.oracle.com:1521/dba10g
Where dB us. oracle. COM is the host name. Of course, it can also be replaced by IP. 1521 is the connection port and dba10g is the service name - you can view it through show parameter service. Local naming: Client name resolution file tnsname. Is required Ora supports all Oracle Net protocols and advanced connection options. Connection method, such as: connect hr/hr@orcl Where orcl is the database instance name Directory naming: LDAP with Oracle Net name resolution loaded: Oracle Internet Directory and Microsoft Active Directory Services. Support all Oracle Net protocols and advanced connection options. Connection method, such as: 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 verification forms used in connection
sqlplus / as sysdba
This is typical operating system authentication and does not require the listener process
sqlplus sys/oracle
This connection method can only connect to the local database, and the listener process is also not required
sqlplus sys/oracle@orcl
This requires the listener process to be available. The most common is through network connection. In the above authentication methods, users using sys or other users authenticated by password files do not need the database to be available, and the operating system authentication does not need the database to be available. The database user authentication is due to the database authentication, so the database must be in the open state. 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
Enter user-name: initSID.ora Medium Remote_Login_Passwordfile Impact on authentication
Three optional values: NONE The default value indicates that the Oracle system does not use the password file, and the privileged user authenticated through the operating system has SYSORA and SYSOPER permissions. EXCLUSIVE 1. Indicates that only one database instance can use the password file 2. It is allowed to assign SYSORA and SYSOPER permissions to users other than SYS SHARED 1. Indicates that multiple database instances can use password files 2. It is not allowed to assign SYSORA and SYSOPER permissions to users other than SYS Therefore, if you want to log in as an operating system, Remote_Login_Passwordfile should be set to NONE About domain name (host name) resolution
/etc/hosts (UNIX) perhaps windows\hosts(WIN98) winnt\system32\drivers\etc\hosts (WIN2000)
The client needs to write the corresponding relationship between the IP address and host name of the database server.
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
Sometimes, after the first step is configured, the tnspin database server alias shows success, But sqlplus username/password@servicename When it doesn't work and jdbc thin link doesn't work, Don't forget to do this step on the client. The reason may be that the corresponding relationship between the server IP address and host name is not set in the DNS server. If there are both private IP and public IP on the Internet, the private IP is written in the front and the public IP is written in the back. It's best to keep a backup before editing. It's also best to use copy and paste when adding a line to avoid editing hosts space-time grid or tab character errors. For the ORACLE multi database environment under UNIX, the OS client needs to configure the following two environment variables
ORACLE_SID=appdb;export ORACLE_SID TWO_TASK=appdb;export TWO_TASK
To specify the default target database
**Possible for troubleshooting:**
1. lsnrctl status view the status of the listener process on the server side
LSNRCTL>help LSNRCTL>status LSNRCTL> services
2. View the client sqlnet.tnspin Ora and tnsname Whether the configuration of the ora file is correct, and the status of the listener process of the corresponding 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)
3. Check whether instance has been started
SQL>select instance_name,host_name,status from v$instance;
Check whether the database is open or mount ed.
SQL>select open_mode from v$database INSTANCE_NAME STATUS ------------------------------ ------------------------------------ orcl OPEN
Example of using hostname to access a database instead of tnsname Using tnsname to access the database is the default method, but it also brings some problems, that is, the client needs to configure tnsnames Of the ora file. If your database server address changes, you need to edit the client file again. Accessing the database through hostname eliminates this trouble. View database name
SQL> select name from v$database; NAME --------------------------- ORCL
You need to modify the server-side listener ora
- Listener configuration file listener ora
- Using host naming, tnsname is no longer required Local parsing of ora files
- listener.ora Network Configuration File:
d:\oracle\product\10.1.0\db_1\NETWORK\ADMIN\listener.ora
# 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))
)
) Client sqlnet Ora can remove TNSNAMES if it confirms that it will not use TNSNAMES for access
sqlnet.ora Network Configuration File: d:\oracle\product\10.1.0\db_1\NETWORK\ADMIN\sqlnet.ora
Generated by Oracle configuration tools. SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (HOSTNAME)
Tnsnames.ora files do not need to be configured, and it doesn't matter to delete them. The following is the configuration of the network and operating system. How can I resolve my host name You can connect in the following ways
sqlplus sys/oracle@orcl
In this case, the orcl server will be connected, and the listener will determine the service you want to connect to_ name
2.5 oracle connectivity issues
To eliminate the connection problem between the client and the server, first check whether the client configuration is correct (the client configuration must be consistent with the database server listening configuration), and then solve it according to the error prompt. Several common connection problems are listed below:
1.ORA-12541: TNS: no listener
Obviously, the listener on the server side is not started. In addition, check whether the client IP address or port is filled in correctly. Start listener:
lsnrctl start
2.ORA-12500: TNS: listener failed to start dedicated server process
For Windows, the Oracle instance service is not started. Start the instance service:
C:oradim –startup -sid myoracle
3.ORA-12535: TNS: operation timeout
There are many reasons for this problem, but it is mainly related to the network. To solve this problem, first check whether the network between the client and the server is unblocked. If the network is connected, check whether the firewalls at both ends block the connection.
4.ORA-12154: TNS: unable to process service name
Check whether the entered service name is consistent with the configured service name. In addition, pay attention to the generated local service name file (under Windows, such as
D:oracleora92networkadmin tnsnames.ora perhaps Linux/Unix lower/network/admin/tnsnames.ora
There must be no space before the service name in the first line of each service.
5.ORA-12514: TNS: the listening process cannot resolve the service given in the connection descriptor_ NAME
Open Net Manager, select the service name, and check whether the service name in the service tab is entered correctly. The service name must be consistent with the global database name configured by the server-side listener. Also check sqlnet Ora, for example, if you want to use a simple connection, you need to connect in names DIRECTORY_ Add EZCONNECT to the path parameter.
6.ORA-12518 TNS: listener failed to distribute client connections
There are two reasons for this error: in the shared mode, there are too few dispatchers, and in the exclusive mode, the number of processes exceeds the default maximum number of processes in the database. Solution steps: 1. show parameter process view the maximum number of processes allowed in the database 2,select count(*) from v$session; View current system processes If the number of processes is not enough, you can increase the number of processes by expanding PGA:
alter system set workarea_size_policy=auto scope=both; alter system set pga_aggregate_target=512m scope=both；
3. show parameter dispatchers view the number of scheduling processes If there are too few scheduling processes, you can execute:
alter system set dispatchers = '(protocol=tcp)(dispatchers=3)(service=oracle10xdb)';
7. Start the monitoring service under windows and prompt that the path cannot be found
Use the command or start listening in the service window to prompt that the path cannot be found, or listen for service startup exceptions. Open the registry and enter
HKEY_LOCAL_MACHINE/SYSTEM/Current ControlSet/Services/OracleOraHome92TNSListener
Check whether the ImagePath string item exists. If not, the setting value is D:oracleora92BINTNSLSNR. Change the setting value of different installation paths accordingly. This method is also applicable to Oracle instance services. The same as above, find
HKEY_LOCAL_MACHINE/SYSTEM/Current ControlSet/Services/Oracle ServiceMYORACLE
Check whether the ImagePath string item exists. If not, create a new one. The setting value is D: oracleora92 binoracle EXE MYORACLE. The above are some common problems of Oracle client connecting to server. Of course, we can't cover all connection exceptions. The key to solving problems lies in methods and ideas, not that every problem has a fixed answer.
8.TNS-12537, TNS-12560, TNS-00507 Linux Error: 29: Illegal seek error When Starting the Listener
Under linux and Unix, if the / etc/hosts file is not configured correctly, the following error messages will appear
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
Solution: Check whether the user has access to the / etc/hosts file and whether the / etc/hosts file contains
127.0.0.1 localhost.localdomain localhost
9.ORA-12505, TNS:listener does not currently know of SID given in connect descriptor The Connection descriptor used by the client was:192.168.1.1:1521:bbcd
This problem usually occurs when using JDBC to connect to the database. Note that Sid should be filled in the location of the bbcd above_ Name, the general configuration format of JDBC is
jdbc:oracle:thin:@IP/HOSTNAME:PORT:SID for example jdbc:oracle:thin:@145.**.**.**:1521:z***db2
10.Ora-12514: TNS: the listener currently does not recognize the service requested in the link descriptor
The problem is due to the missing Sid of the listener_ It is caused by the list description item. This problem is often encountered when configuring with netca. Listener An example of ora is as follows
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /opt/oracle/product/9.2.0.4) (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)) )))
11.ORA-12528: TNS:listener: all appropriate instances are blocking new connections
ORA-12528: the dynamic service is used for the service under monitoring. After the system is started, the database does not have a normal MOUNT. Therefore, this problem will occur in the dynamic mode, but not in the static mode. Therefore, the above method is to set the monitoring to static or in tnsnames Added in ora (UR=A). lisnter.ora adds the following
(SID_DESC =
(GLOBAL_DBNAME = ammicly)
(ORACLE_HOME = c:\oracle\product\10.1.0\db_1)
(SID_NAME = ammicly)
)
Or in tnsnames.ora Add the following:
(UR=A)12.ORA-01034: ORACLE not available and ORA-27101: shared memory realm does not exist
Check whether tnspin works normally and lsnrctl status is normal. Check local_ The listener parameter (pmon can only dynamically register the listener whose port is equal to 1521, otherwise pmon cannot dynamically register the listener. To enable pmon to dynamically register the listener, you need to set the local_listener parameter), set it in the following way
alter system set local_listener='(ADDRESS =(PROTOCOL=TCP)(HOST=10.201.1.134)(PORT=1521)(SID=siebtest))';
13.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 in tnsname The connection mode is set to shared in the ora configuration. In this case
open tnsname.ora, hold(server = shared) Change to (server = dedicate)；
2. It is caused by insufficient process:
select count(*) from v$session; show parameter processes show parameter sessions
Just increase the processes parameter 3,local_ The listener is not set properly. See the above for the setting method.
14.TNS-12542: TNS:address already in use
TNS-12560: TNS:protocol adapter error TNS-00512: Address already in use
Check the configuration of / etc/hosts to see if mult ip le IPS point to the same host name Refer to: http://lzysystem.iteye.com/blog/424569 http://blogold.chinaunix.net/u2/82873/showart_1830733.html http://gggwfn1982.blog.163.com/blog/static/15000427201041192213787/ http://guolr.iteye.com/blog/549692 http://blog.sina.com.cn/s/blog_4cd0d3a701000a6j.html http://pengxianfeng.i.sohu.com/blog/view/84726397.htm http://blog.sina.com.cn/s/blog_517cae3c01015dwu.html http://luoping.blog.51cto.com/534596/1062670 http://xiekeli.blogbus.com/logs/93619549.html http://www.linuxidc.com/Linux/2012-11/73694.htm
15. Start the listening service under windows and prompt that the path cannot be found
Use the command or start listening in the service window to prompt that the path cannot be found, or listen for service startup exceptions. Open the registry and enter
HKEY_LOCAL_MACHINE/SYSTEM/Current ControlSet/Services/OracleOraHome92TNSListener
Check whether the ImagePath string item exists. If not, set the value to
D:/oracle/ora92/BIN/TNSLSNR
Change the settings for different installation paths accordingly. This method is also applicable to Oracle instance services. The same as above, find
HKEY_LOCAL_MACHINE/SYSTEM/Current ControlSet/Services/Oracle ServiceMYORACLE
Check whether the ImagePath string item exists. If not, create a new one and set the value to
d:/oracle/ora92/binORACLE.EXE MYORACLE
The above are some common problems of Oracle client connecting to server. Of course, we can't cover all connection exceptions. The key to solving problems lies in methods and ideas, not that every problem has a fixed answer.
16.ORA-12638: ID retrieval failed
start -> program -> Oracle -> Configuration and Migration Tools -> Net Manager→local→Profile→Oracle Advanced security→verification→Remove from the selected method "NTS"
Just do it
3. Backup and restore database
3.1 backup database
exp export
ORACLE 11g is a new feature. When there is no data in the table, segment is not allocated to save space. Therefore, these tables are not exported when exp is exported. 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 results generated by the above statements and execute them. Execute the following command line to export the database
exp rad/rad@orcl file='E:\rad.dmp' buffer=40960000
Using the export expdp tool
First, specify the directory where the dump files and log files are located - lmm_db_bak
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;
- see
select * from dba_directories;
- export
expdp rad/rad@orcl schemas= rad dumpfile=rad-20170101.dmp directory= lmm_db_bak
- Use include to export some tables
expdp rad/rad@orcl schemas= rad dumpfile=rad-20170101.dmp directory= lmm_db_bak include=table:\"like \'CT%\'\"
Note: empty tables can be exported directly in this way
3.2 restore database
imp import
If you want to restore the complete oracle database, you can delete the current user first, then create the user and import the database
- delete user
drop user rad cascade;
- Create 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;
Import the database by executing the following command line
imp rad/rad@orcl file='E:\rad.dmp' buffer=40960000 full=y;
Import incomplete database dmp files (partial tables)
If the same table exists in your original database and dmp, importing dmp files will not import existing tables with the same name. You need to delete the table with the same name in your original library in advance.
Using the import impdb tool
impdp rad/rad@orcl schemas= rad directory= lmm_db_bak dumpfile=RAD-20170101.DMP FULL=y;
If the user you want to import already exists: 1. Export user expdp user1/pass1 directory=dumpdir dumpfile=user1.dmp 2. Import user impdp user2/pass2 directory=dumpdir dumpfile=user1.dmp REMAP_SCHEMA=user1:user2 EXCLUDE=USER full=y; If the user you want to import does not exist: 1. Export user expdp user1/pass1 directory=dumpdir dumpfile=user1.dmp 2. Import user impdp system/passsystem directory=dumpdir dumpfile=user1.dmp REMAP_SCHEMA=user1:user2 full=y; 3. user2 It will be established automatically, and its permissions and used tablespaces are the same as user1 Same, but in this case user2 Unable to log in and must be modified user2 Password for