41. Where Are the Settings Stored for Each Instance in Oracle?
Settings for each instance are stored in a file called Server Parameter File (SPFile). Oracle supports two types of parameter files, Text type, and Binary type. parameter files should be located in $ORACLE_HOMEdatabase directory. A parameter file should be named like "init$SID.ora", where $SID is the instance name.
42. What To Do If the Binary SPFile Is Wrong for the Default Instance?
Let's say the SPFile for the default instance is a binary file, and some settings are wrong in the SPFile, like SGA setting is bellow 20MB, how do you change a setting in the binary file? This seems to be a hard task, because the binary SPFile is not allowed to be edited manually. It needs to be updated by the server with instance started. But you can not start the instance because the SPFile has a wrong setting.
One way to solve the problem is to stop using the binary SPFile, and use a text version of the a parameter file to start the instance. Here is an example of how to use the backup copy (text version) of the parameter file for the default instance to start the instance:
>.binsqlplus
Enter user-name: SYSTEM/atoztarget AS SYSDBA
Connected to an idle instance
43. How To Check the Server Version in Oracle?
Oracle server version information is stored in a table called: PRODUCT_COMPONENT_VERSION. You can use a simple SELECT statement to view the version information like this:
>.binsqlplus
Enter user-name: SYSTEM/atoztarget AS SYSDBA
Connected to an idle instance
SQL> COL PRODUCT FORMAT A35
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION STATUS
----------------------------------- ----------- ----------
NLSRTL 10.2.0.1.0 Production
Oracle Database 10g Express Edition 10.2.0.1.0 Product
PL/SQL 10.2.0.1.0 Production
TNS for 32-bit Windows: 10.2.0.1.0 Production
44. Explain What Is SQL*Plus?
SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.
SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following:
* Format, perform calculations on, store, and print from query results
* Examine table and object definitions
* Develop and run batch scripts
* Perform database administration
You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility of SQL*Plus, or using the dynamic reporting capability of iSQL*Plus to run a script from a web page.
45. How To Start the Command-Line SQL*Plus?
f you Oracle server or client installed on your windows system, you can start the command-line SQL*Plus in two ways:
1. Click Start > All Programs > Oracle ... > Start SQL Command Line. The SQL*Plus command window will show up with a message like this:
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>
2. Click Start > Run..., enter "cmd" and click OK. A Windows command window will show up. You can then use Windows commands to start the command-line SQL*Plus as shown in the tutorial exercise below:
>cd c:oraclexeapporacleproduct10.2.0server
>.binsqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...
Copyright (c) 1982, 2005, Oracle. All rights reserved.
46. How To Get Help at the SQL Prompt?
Once SQL*Plus is started, you will get a SQL prompt like this: SQL>. This where you can enter commands for SQL*Plus to run.
To get help information at the SQL prompt, you can use the HELP command as shown in the following tutorial example:
SQL> HELP INDEX
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
...
COMPUTE LIST SET XQUERY
CONNECT PASSWORD SHOW
SQL> HELP CONNECT
CONNECT
------
47. What Information Is Needed to Connect SQL*Plus an Oracle Server?
If you want to connect your SQL*Plus session to an Oracle server, you need to know the following information about this server:
* The network hostname, or IP address, of the Oracle server.
* The network port number where the Oracle server is listening for incoming connections.
* The name of the target database instance managed by the Oracle server.
* The name of your user account predefined on in the target database instance.
* The password of your user account predefined on in the target database instance.
48. What Is a Connect Identifier?
A "connect identifier" is an identification string of a single set of connection information to a specific target database instance on a specific Oracle server.
Connect identifiers are defined and stored in a file called tnsnames.ora located in $ORACLE_HOME/network/admin/ directory. Here is one example of a "connect identifier" definition:
ggl_XE =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = www.atoztarget.com)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
The above "connect identifier" defines "TNS_XE" with the following connection information:
* The network hostname: www.atoztarget.com.
* The network port number: 1521.
* The name of the target database instance: XE.
49. How To Connect a SQL*Plus Session to an Oracle Server?
In order to connect a SQL*Plus session to an Oracle server, you need to:
1. Obtain the connection information from the Oracle server DBA.
2. Define a new "connect identifier" called "ggl_XE" in your tnsnames.org file with the given connection information.
3. Run the CONNECT command in SQL*Plus as shown in the tutorial exercise below:
>cd c:oraclexeapporacleproduct10.2.0server
>.binsqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> CONNECT ggl/retneclgg@ggl_XE;
Connected.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
---------
05-MAR-06
50. What Happens If You Use a Wrong Connect Identifier?
Of course, you will get an error, if you use a wrong connect identifier. Here is an example of how SQL*Plus react to a wrong connect identifier:
SQL> CONNECT ggl/retneclgg@WRONG;
ERROR:
ORA-12154: TNS:could not resolve the connect identifier
specified
Warning: You are no longer connected to ORACLE.
What you need to do in this case:
* Check the CONNECT command to make sure that the connect identifier is entered correctly.
* Check the tnsnames.ora file to make sure that the connect identifier is defined correctly.
* Check the tnsnames.ora file to make sure that there is no multiple definitions of the same connect identifier.
* Check your files system to see if you have multiple copies of tnsnames.ora in different Oracle home directories, because you installed multiple versions of Oracle. If you do have multiple copies, make sure your SQL*Plus session is picking up the correct copy of tnsnames.ora.
Settings for each instance are stored in a file called Server Parameter File (SPFile). Oracle supports two types of parameter files, Text type, and Binary type. parameter files should be located in $ORACLE_HOMEdatabase directory. A parameter file should be named like "init$SID.ora", where $SID is the instance name.
42. What To Do If the Binary SPFile Is Wrong for the Default Instance?
Let's say the SPFile for the default instance is a binary file, and some settings are wrong in the SPFile, like SGA setting is bellow 20MB, how do you change a setting in the binary file? This seems to be a hard task, because the binary SPFile is not allowed to be edited manually. It needs to be updated by the server with instance started. But you can not start the instance because the SPFile has a wrong setting.
One way to solve the problem is to stop using the binary SPFile, and use a text version of the a parameter file to start the instance. Here is an example of how to use the backup copy (text version) of the parameter file for the default instance to start the instance:
>.binsqlplus
Enter user-name: SYSTEM/atoztarget AS SYSDBA
Connected to an idle instance
43. How To Check the Server Version in Oracle?
Oracle server version information is stored in a table called: PRODUCT_COMPONENT_VERSION. You can use a simple SELECT statement to view the version information like this:
>.binsqlplus
Enter user-name: SYSTEM/atoztarget AS SYSDBA
Connected to an idle instance
SQL> COL PRODUCT FORMAT A35
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION STATUS
----------------------------------- ----------- ----------
NLSRTL 10.2.0.1.0 Production
Oracle Database 10g Express Edition 10.2.0.1.0 Product
PL/SQL 10.2.0.1.0 Production
TNS for 32-bit Windows: 10.2.0.1.0 Production
44. Explain What Is SQL*Plus?
SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.
SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following:
* Format, perform calculations on, store, and print from query results
* Examine table and object definitions
* Develop and run batch scripts
* Perform database administration
You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility of SQL*Plus, or using the dynamic reporting capability of iSQL*Plus to run a script from a web page.
45. How To Start the Command-Line SQL*Plus?
f you Oracle server or client installed on your windows system, you can start the command-line SQL*Plus in two ways:
1. Click Start > All Programs > Oracle ... > Start SQL Command Line. The SQL*Plus command window will show up with a message like this:
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>
2. Click Start > Run..., enter "cmd" and click OK. A Windows command window will show up. You can then use Windows commands to start the command-line SQL*Plus as shown in the tutorial exercise below:
>cd c:oraclexeapporacleproduct10.2.0server
>.binsqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...
Copyright (c) 1982, 2005, Oracle. All rights reserved.
46. How To Get Help at the SQL Prompt?
Once SQL*Plus is started, you will get a SQL prompt like this: SQL>. This where you can enter commands for SQL*Plus to run.
To get help information at the SQL prompt, you can use the HELP command as shown in the following tutorial example:
SQL> HELP INDEX
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
...
COMPUTE LIST SET XQUERY
CONNECT PASSWORD SHOW
SQL> HELP CONNECT
CONNECT
------
47. What Information Is Needed to Connect SQL*Plus an Oracle Server?
If you want to connect your SQL*Plus session to an Oracle server, you need to know the following information about this server:
* The network hostname, or IP address, of the Oracle server.
* The network port number where the Oracle server is listening for incoming connections.
* The name of the target database instance managed by the Oracle server.
* The name of your user account predefined on in the target database instance.
* The password of your user account predefined on in the target database instance.
48. What Is a Connect Identifier?
A "connect identifier" is an identification string of a single set of connection information to a specific target database instance on a specific Oracle server.
Connect identifiers are defined and stored in a file called tnsnames.ora located in $ORACLE_HOME/network/admin/ directory. Here is one example of a "connect identifier" definition:
ggl_XE =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = www.atoztarget.com)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
The above "connect identifier" defines "TNS_XE" with the following connection information:
* The network hostname: www.atoztarget.com.
* The network port number: 1521.
* The name of the target database instance: XE.
49. How To Connect a SQL*Plus Session to an Oracle Server?
In order to connect a SQL*Plus session to an Oracle server, you need to:
1. Obtain the connection information from the Oracle server DBA.
2. Define a new "connect identifier" called "ggl_XE" in your tnsnames.org file with the given connection information.
3. Run the CONNECT command in SQL*Plus as shown in the tutorial exercise below:
>cd c:oraclexeapporacleproduct10.2.0server
>.binsqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> CONNECT ggl/retneclgg@ggl_XE;
Connected.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
---------
05-MAR-06
50. What Happens If You Use a Wrong Connect Identifier?
Of course, you will get an error, if you use a wrong connect identifier. Here is an example of how SQL*Plus react to a wrong connect identifier:
SQL> CONNECT ggl/retneclgg@WRONG;
ERROR:
ORA-12154: TNS:could not resolve the connect identifier
specified
Warning: You are no longer connected to ORACLE.
What you need to do in this case:
* Check the CONNECT command to make sure that the connect identifier is entered correctly.
* Check the tnsnames.ora file to make sure that the connect identifier is defined correctly.
* Check the tnsnames.ora file to make sure that there is no multiple definitions of the same connect identifier.
* Check your files system to see if you have multiple copies of tnsnames.ora in different Oracle home directories, because you installed multiple versions of Oracle. If you do have multiple copies, make sure your SQL*Plus session is picking up the correct copy of tnsnames.ora.
No comments:
Post a Comment