Connect to remote database oracle11g, there are a few mistakes!

Plsql Dev in the client computer accesses the oracle11g in the virtual machine, with all the errors!

Creation time:	2017/10/14 18:44
	Cnsimo
Label:	ORACLE

Busy all afternoon, only two words describe: trouble!Installing ORACLE1GFirst of all, I already configured the windows2008+oracle11g on the virtual machine in advance, why use server2008? I have nothing else, win10 do not feel very good virtual machine, Win7 Mirror also have to download, you understand. Then is oracle11g is to go to a computer to copy over, I am a very cherish the flow of people, hey. Our teacher in order to let us more clearly distinguish between the database software installation and database configuration, so when installing oracle11g the choice is "only install the database software" (remember is the second option), and then install the character set when the UTF8, the other configuration is basically to keep the default. Well, the installation of things do not elaborate, in general, as long as the host is not a problem, detection conditions through, installation is not a problem, the following began the configuration database. First UseDCA(In Oracle's Start Menu folder, abbreviation OH), after opening a few options, select "Create a Database", and then the next step, followed by a few steps until the completion of the creation of the database, and there is no error, so there is not much to say, the only error may be to prompt a similar "no listening, unable to continue" dialog box, so go to NCA (and DCA sibling directory) to add monitoring, you just have to go the next step, the default listening port is 1521, and then back to DCA to continue the next step, until the end, it is necessary to focus on, The last page has Web Management page URL, this port number is https://hostname:port/em This form, hostname is the IP address of the machine, port is the port number, my port number is 1158 , some may not. For example, my Web Administration page: Https://server2008:1158/em, my database listener port is 1521. The Administration page is as follows:If the status in the red box is not correct after the installation, consider reloading or restarting the Oracle Services or restarting the Oracle server. Restarting the Oracle serverOperation: Open cmd to enter the following command: Sqlplus/as sysdbashutdown immediatestartupthen say the features of the Oracle configuration tools:

• DCA: Manage Databases (create, delete, etc.)
• NCA: Configuring Database Network Services (monitoring, service name, etc.)
• NET Manager: Network integrated management tools (monitoring, service name and other specific information to view changes, etc.)

There is also a tool sqlplus is also very common, using it to access the Oracle database operations as follows (CMD): If it is a normal user sqlplus Username/[email protected]/tnsname if it is a SYSDBA user, For example Syssqlplus sys/[email protected]/tnsname as sysdba you may be more aware of it when used in the back. Then there are three very important files, of which two need to know: note the path Oh!format: {database_base}\network\admin。 Changing the listener or service name information in Net Manager or NCA may map to these two files, for example, I changed the address of listen in Net Manager, In the Listener.ora file is reflected in, you can open to see, but it is best not to manually change the contents of the inside, because it is not easy to feel right can be a person pit. If you want to change it or change it in Net Manager, for example now I've changed the listen listening port from 1521 to 1522 in Net Manager, opening the Listener.ora file can see that the corresponding place has changed. tip: If you do something with net Manager when you don't know what's going on, and you forget what it is, it doesn't matter if you close the Net Manager window and you are prompted to save it, and select No to recover! It's useless to say so much, my purpose has not yet come true, how to access the database inside the virtual machine in the real machine? Let me explain.Pre-conditions：

1. What I'm saying is that if you know the computer and have a basic, quick-witted, familiar VM virtual machine, ask questions.
2. Host and virtual machine can ping each other, if not, try to change the connection mode to NAT, the host of the VMNET8 network card IPv4 set to obtain IP automatically.
3. The services of Oracle on the host are started normally.
4. Also need to know how to configure the environment variables, do not understand the automatic Baidu learning.

My machine is configured as follows:

• Virtual machine: VM12.5.7 version
• Host: Win10 Professional Edition 1703
• Plsql Developer (: https://www.allroundautomations.com/bodyplsqldevreg.html)
• Virtual machine: SERVER2008R2 Enterprise Edition, Memory 2G
• ORACLE:11G, DB instance name STD, global database name STD.CNSIMO.CN, all passwords unique: Admin2015, Character set: UTF8, Languages: English

Connection Steps

1. Download Instant client,http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html website.
2. Download the base package and Sqlplus package
3. After extracting it together, get a folder and copy it to any location, for example: D:\PLSQL Developer 12\
4. Create a new Folder network in D:\PLSQL Developer 12\instantclient_12_2 ( hereinafter referred to as {instantclient}) and create a new folder in the network admin.
5. To create a new Notepad file in {instantclient}\network\admin , the following code is copied and saved in utf-8 format, with the name changed to tnsnames.ora"suffix Txt->ora , Note that the format does not change (one more space is not possible).

tip: Please change STD.CNSIMO.CN to your global database name, and host and port to your own, and the first line of STD can be written casually. STD = (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.111.138) (PORT = 1521))) (C Onnect_data = (SERVER = SHARED) (service_name = STD. Cnsimo. CN)))

6. Open the Plsql DEV12 and find the preferences setting
7. Then, install the diagram settings in connection, instantclient the location.

         

8. A very important step, to view the value of Nls_lang in the virtual machine registry, and if none of the paths are directly looking for Nls_lang, copy this key value.
9. an important step, create a new system environment variable Nls_lang in the host, and the value is consistent with the virtual machine!

            

8. An important step, and then create a new system environment variable Tns_admin with the value {instantclient}\network\admin

            

9. Change the environment variable path and add the value {instantclient}.
10. Then cmd, run the Sqlplus command to connect to the remote database with the command: Sqlplus sys/[email protected]/std. Cnsimo. CN as SYSDBA, which represents a successful connection.

       

11. Re-open Plsql DEV, try to connect, fill in the User name password, database drop-down box if it does not appear before the first line written in Tnsnames.ora std, enter STD manually, and as a SYSDBA connection. Successful words will enter the main interface, otherwise prompt.

solve the problemOK, the general steps of the connection have been finished, but in fact many times do not really have the desire, the problem encountered a bunch, the common problem description may be as follows:

• Ora-12170:tns:connect Timeout occurred
• Ora-12514:tns: Listener currently does not recognize the requested service in the connection descriptor
• Ora-12705:cannot Access NLS data files or invalid environment specified

Solve firstThe first one., you can try to ping your remote host, may not ping, so you need to change the virtual machine and host network connection between the way;a second question, the root cause of the problem is that the service name on the remote host is not being monitored by the listener, such as when I connect through the Sqlplus STD.CNSIMO.CN to STD or any other, will report this error. Here's how to fix it:Open Net Manager, select service naming, click the plus sign to add a service, the STD shown in the list only has local meaning, it is important to the right of the service name, set as the global database name, connection type Select the shared server.                         Then close net Manager and save the configuration. Try the connection again, if it is still not connected, try restarting the Oracle database! Tip: The Internet may be found to change the virtual machine Tnsnames.ora and Listener.ora These two files, unauthorized changes may cause the problem is that the database connection is not up, Netmanager and NCA part of the function is not reactive problem . Because Oracle's content format requirements for these two files are too stringent. The header of the red box cannot have a space, the first of the blue box must have a space!                         As forone last question, check that the value of Nls_lang in the environment variable is the same as the value in the remote host registry, and remember not to hand-tap! If this is still the case, perhaps you have just configured the environment variable, cmd command box or the previous open, please re-open a cmd try again.

Connect to remote database oracle11g, there are a few mistakes!