OCP Fundamental II Chapter 2 - Configuring Oracle Net on the Server

OCP: Oracle9i DBA Fundamentals II Study Guide
by Doug Stuns and Matthew Weishan

Chap. 2
Review Questions (Ansewers provided at the end)

1. Which file must be present on the Oracle server to start the Oracle listener?
A. listener.ora
B. lsnrctl.ora
C. sqlnet.ora
D. tnsnames.ora
---------------

2. What are the possible ways in which the listener may connect a user to an Oracle9i instance? (Choose all that apply.)
A. Prespawned connection
B. Redirect connection
C. Bequeath connection
D. Multipass connection
-------------------
3. What is the default name of the Oracle listener?
A. lsnrctl
B. Listen
C. sqlnet
D. tnslistener
E. None of the above
----------

4. What is the maximum number of databases a listener processes?
A. 1 database
B. 2 databases
C. 10 databases
D. 25 databases
E. None of the above
-------

5. What is the maximum number of listener.ora files that should exist on a server?
A. One
B. Two
C. Four
D. Eight
E. None of the above
----------

6. Which of the following does this phrase characterize?
“…records all events that occur on a network, even when an error does not happen.”
A. Oracle Net Manager
B. Network tracing
C. Network logging
D. None of the above
-------------

7. When automatic registration of services is used, you will not see the service listed in which of the following files?
A. sqlnet.ora
B. tnsnames.ora
C. listener.ora
D. None of the above

8. Which of the following are not default listener.ora settings? (Choose all that apply.)
A. Listener name = LISTENER
B. Port = 1521
C. Protocol = IPX
D. Protocol = TCP/IP
E. Listener name = lsnrctl
-------------

9. Which of the following is the command-line interface used to administer the listener?
A. LISTENER
B. lismgr
C. TCPCTL
D. lsnrctl
E. None of the above
---------

10. Which Oracle Net Manager icon should you choose to manage listeners?
A. Services
B. Listener Names
C. Profile
D. Listeners
E. None of the above
-------

11. Which parameter sets the number of seconds a server process waits to get a valid client request?
A. connect_waittime_listener_name
B. connect_wait_listener_name
C. timeout_listener_name
D. connect_timeout_listener_name
--------

12. Which of the following is the trace level that will produce the largest amount of information?
A. ADMIN
B. USER
C. ALL
D. SUPPORT
E. None of the above
--------

13. What is the maximum number of listeners that can be configured for a server?
A. One
B. Two
C. Four
D. Eight
E. None of the above
---------

14. There is a listener called LISTENER. Which of the following is the correct way to start this listener?
A. lsnrctl startup listener
B. lsnrctl start
C. listener start
D. listener start listener
-------

15. There is a listener called listenerA. Which of the following is the correct command to start this listener?
A. lsnrctl startup listenerA
B. lsnrctl start
C. listener start
D. listener startup
E. lsnrctl start listenerA
--------

16. Modifications have been made to the listener.ora file from Oracle Net Manager. When will these modifications take effect?
A. Immediately
B. After exiting the Oracle Net Manager
C. Upon saving the listener.ora file
D. After executing lsnrctl refresh
E. None of the above
----------

17. There is a listener called listener1 that you want to edit using the lsnrctl utility. What command would you use to target the listener as the current listener for editing?
A. set current_listener listener1
B. accept current_listener listener1
C. reload listener listener1
D. refresh listener listener1
--------------

18. Modifications have been made using the lsnrctl facility. What must be set to ON in order to make the changes permanent?
A. save_configuration
B. save_listener.ora
C. save_config_on_stop
D. configuration_save
------------

19. The administrator or DBA wants to make a backup of the listener file after making changes using lsnrctl. Which command must be implemented to make this backup from the lsnrctl facility?
A. create_backup
B. save_config_on_stop
C. save_config
D. save_backup
----------

20. What is the port number to use when you are configuring the listener for Oracle9i JVM on TCP/IP with SSL?
A. 1521
B. 2482
C. 2481
D. 1526
E. None of the above
--------------

Answers
1. A.
The listener is the process that manages incoming connection requests. The listener.ora file is used to configure the listener. The sqlnet.ora file is an optional client- and server-side file. The tnsnames.ora file is used for doing local naming resolution. There is no such file as lsnrctl.ora.

2. B, C.
The listener can handle a connection request in one of two ways: it can spawn a process and bequeath (pass) control to that process, or it can redirect the process to a dedicated process or dispatcher when using Oracle Shared Server.

3. E.
When creating a listener with the Oracle Net Manager, the Assistant recommends LISTENER as the default name. When you are starting and stopping the listener via the command line tool, the tool assumes the name of the listener is LISTENER if no listener name is supplied.

4. E.
There is no physical limit to the number of services a listener can listen for.

5. A.
Although a listener can listen for an unlimited number of services, only one listener.ora file is used. If multiple listeners are configured, there will still be only one listener.

6. B.
Network tracing is what records all events on the network even if there is no error involved. Tracing should be used sparingly and only as a last resort in the case of network problems. Logging will log only significant events such as listener startup and connection requests.

7. C.
When services are dynamically registered with the listener, their information is not present in the listener.ora file.

8. C, E.
A default listener has a name of LISTENER and listens on Port 1521 for TCP/IP connections.

9. D.
LISTENER is the default name of the Oracle listener. There is no such utility as lismgr. TCPCTL was actually an old utility used to start and stop the SQL*NET version 1 listener. The lsnrctl command is used to manage the listener.

10. D.
Become familiar with the Oracle Net Manager interface. Listeners is the correct choice. Profile is used for sqlnet.ora administration. The other choices are not valid menu options.

11. D.
When a user makes a connection request, the listener passes control to some server process or dispatcher. Once the user is attached to this process, all negotiations and interaction with the database pass through this process. If the user supplies an invalid user ID or password, the process waits for a period of time for a valid response. If the user does not contact the server process with a valid response in the allotted time, the server process terminates, and the user must contact the listener so that the listener can again spawn a process or redirect the client to an existing dispatcher. This period of time that the process waits is specified by the connect_timeout_listener_name parameter. This parameter is specified in seconds.

12. D.
The highest level of tracing available is the SUPPORT level. This is the level that would be used to trace packet traffic information.

13. E.
There is no maximum number of listeners that can be configured per server.

14. B.
The default listener name is LISTENER. Since this is the default, simply enter lsnrctl start. The name LISTENER is assumed to be the listener to start in this case.

15. E.
Oracle expects the listener to be called LISTENER by default. The name of the facility to start the listener is lsnrctl. Using lsnrctl start will start the default listener. To start a listener with another name, enter lsnrctl start listener_name.

16. E.
Anytime modifications are made to the listener file using the Oracle Net Manager, either manually or by using lsnrctl, the listener must be reloaded for the modifications to take effect. To perform this reload, get to a command line and enter lsnrctl reload. You could also stop and start the listener, which will have the same effect. Since lsnrctl reload is not one of the choices, none of the above is the correct answer.

17. A.
If you want to administer any listener besides the default listener when using lsnrctl, you must target that listener. set commands are used to change lsnrctl session settings. So, set current_listener listener1 would be the correct command.

18. C.
Changes made to the listener.ora file in the lsnrctl facility can be made permanent. To make changes permanent, set the save_ config_on_stop option to ON.

19. C.
The DBA can make a backup of the existing listener.ora file after making modifications to it using lsnrctl. The backup will be named listener.bak. This is done with the save_config option.

20. B.
Port 2482 is the port to use when you want to configure Oracle Net for HTTP and IIOP connections over TCP/IP with SSL. Port 2481 is used when TCP/IP is used for HTTP and IIOP connections.

OCP fundamental II Chapter 1 Introduction to Network Administration- Review Questions

Chapter 1 - Introduction to Network Administration
Review Questions

1. All of the following are examples of networking architectures except:
A. Client/server
B. N-tierC. Single-tier
D. Two-tie
E. All of the above are examples of network architectures.

Ans E.
All of these are examples of network connectivity configurations. Networking can be as simple as a dumb terminal connected directly to a server via a serial connection. It can also be as complex as an n-tier architecture that may involve clients, middleware, the Internet, and database servers.
----------

2. You manage one non-Oracle database and several Oracle databases. An application needs to access the non-Oracle databases as if it were part of the Oracle databases. What tool will solve this business problem? Choose the best answer.
A. Oracle Advanced Security
B. Oracle Connection Manager
C. Heterogeneous Services
D. Oracle NetE. None of the above

Ans C.
Oracle Advanced Security would not solve this application problem becauseit addresses security and not accessibility to non-Oracle databases. Oracle Net would be part of the solution, but another Oracle Network component is necessary. Connection Manager would also not be able to accommodate this requirement on its own. Heterogeneous Services is the correct answer because these services providecross- platform connectivity to non-Oracle databases.
----------

3. Which of the following is true about Oracle Net?
A. It is not an option included in the Oracle Enterprise installation.
B. It only works on TCP/IP platforms.
C. It has an open API.
D. It is never installed directly on a client workstation.

Ans.
C. Oracle Net is included in the Oracle Enterprise installation and works with a variety of protocols. It also has a client and a server component. The only statement that is true about Oracle Net is that it has an open Applications Program Interface (API), which means that third-party software can write to these specifications to interact directly with Oracle Net.
--------

4. A DBA wants to centrally administer all of the Oracle network services in a large Oracle9i installation with many network services. Which facility wouldbest provide this functionality at minimal cost?
A. Advanced Security
B. Heterogeneous Services
C. Oracle Shared Server
D. Oracle Internet Directory

Ans D.
Advanced Security, Heterogeneous Services, and Oracle Shared Server would not provide a solution to this business need because none of these address the issue of centrally managing network services. The best solution to the problem is the Oracle Internet Directory because it would facilitate centralized naming.

------
5. What are TCP/IP, DECnet, and LU6.2 all examples of?
A. Computer programming languages
B. Oracle Net connection tools
C. Networking protocols
D. Network programming languages

Ans C.
TCP/IP, DECnet, and LU6.2 are all examples of network protocols.

------

6. Which feature of Oracle Net best describes this statement: "Oracle Net supports TCP/IP and LU6.2."?
A. GUI tools integration
B. Robust tracing and diagnostic tools
C. Zero configuration on the client
D. Network transport protocol support

Ans D.
Oracle Net allows for support of multiple protocols. TCP/IP and LU6.2 are two examples of the protocols that Oracle Net supports.
-------

7. What is a solution that Oracle9i employs with Oracle Net that allows connectivity of Java Components such as Enterprise JavaBeans?
A. LU6.2
B. IPA
C. GIOP
D. Oracle Internet Director

Ans. C.
The General Inter-ORB Protocol is a protocol that supports connectivity of Java components.
--------

8. What is the standard that the Oracle Net communications stack is based on?
A. OCI
B. NPI
C. OSI
D. API

Ans C.
The Oracle Net communications stack is based on the Open Systems Interconnection (OSI) model. NPI and OCI are parts of the Oracle Net stack and API stands for Applications Program Interface.
----------

9. "Responsible for moving bits across the wire" describes which of the following OSI layers?
A. Application Layer
B. Physical Layer
C. Data Link Layer
D. Network Layer

Ans B.
The Physical Layer is responsible for sending the actual data bits acrossthe network. The other layers are all above this base layer.
-------

10. What is the default name of the process that is used to make external calls via Oracle Net?
A. externalproc
B. external
C. extproc
D. procext
Ans. C. The default name of the external procedure process is extproc. lsnrctl is a utility used to manage the listener service. External and procext are not valid responses.
----------------

11. IIOP is an example of which of the following?
A. Tools to use for Oracle Net
B. Oracle network integration utilities
C. Internet network protocol
D. Portions of the Oracle Net stack

Ans C.
IIOP is an example of an Internet network protocol.
-------

12. Connection Manager provides which of the following?
A. Multiplexing
B. Cross protocol connectivity
C. Network access control
D. All of the above

Ans D.
Connection Manager is a middleware solution that provides for multiplexing of connections, cross protocol connectivity, and network access control. All of the answers describe Connection Manager.
-------

13. Which of the following is true about the OCI layer?
A. It displays the graphical interface
B. Its datatype conversions are handled.
C. It interfaces directly with the protocol adapters.
D. It interfaces directly with the TTC layer.
E. None of the above.

Ans D.
The OCI layer is below the application layer and above the TTC layer. The call interface handles such things as cursor management and SQL execution. This information is passed on to the Two-Task Common layer. --------

14. To which of the choices below does the following statement apply? "Prevents direct communication between a client and applications inside the corporate network."
A. Proxy-based
B. Filter-based firewalls
C. Both types of firewalls
D. Neither type of firewall

Ans A.
Proxy-based firewalls prevent any direct contact between a client and applications inside a corporate firewall. Filter-based firewalls inspect the packet headers but pass the packet on without modification to the destination application. Proxy-based firewalls act more as a relay between external clients and internal applications.
-------

15. When a connection is made via a Java applet, what type of driver is utilized?
A. JDBC OCI driver
B. JDBC Thin Driver
C. ODBC driver
D. OCI driver

Ans B.
The JDBC Thin Driver would be utilized if a Java applet is used to communicatewith an Oracle database through an application server.
-------

16. A client workstation connects to a transaction server, which passes on requests to the Oracle database. This is a description of which of the following?
A. Single-tier architecture
B. Client/server architecture
C. N-tier architecture
D. None of the above

Ans C.
When you introduce middle tiers into the processing of a transaction, this is known as n-tier architecture.
--------

17. Which Oracle Net networking product can be best described as middleware?
A. Oracle Internet Directory
B. Oracle Connection Manager
C. Oracle Advanced Networking
D. Oracle Shared Server

Ans B.
The Connection Manager is a middle-tier option that provides multi-protocol interchange, connection concentration, and client access control.
----------

18. Which of the following are characteristics of complex networks?
A. Multiple protocols
B. Diverse geographic locations
C. Multiple operating systems
D. Multiple hardware platforms
E. All of the above

Ans:
E. All of these are characteristics of complex networks.
-------
19. What is the preferred method of centralized naming in an Oracle9i environment?
A. Oracle Names Server
B. Oracle Connection Manager
C. Oracle Shared Server
D. Directory Naming with Oracle Internet Directory

Ans D.
Oracle Internet Directory and Directory Naming are displacing the Oracle Names Server as the preferred method of centralized naming.
---------------

20. Which of the following is an example of the ability to group connections together?
A. Protocol Interchange
B. Network Access Control
C. Multiplexing
D. Data Integrity checking
E. None of the above

Ans: C.
Multiplexing is a characteristic of the Oracle Connection Manager that allows several incoming requests to be handled and transmitted simultaneously over a single outgoing connection. This is a scalability feature provided by Connection Manager.

Copy and Rename a database (clone)

Practise: Copy and Rename a Database
The original DB: mydb1
The new DB: oradb1

1) Copy mydb1 data files and init.ora
select file_name from dba_data_files
FILE_NAME
--------------------------------------------
/opt/app/oracle/oradata/mydb1/system01.dbf
/opt/app/oracle/oradata/mydb1/undotbs01.dbf
/opt/app/oracle/oradata/mydb1/user01.dbf

cp above files to the location(after shutdown the db):
/opt/app/oracle/oradata/oradb1/

cp the online redo file to the new location too
( do we really need to copy redo log file?)

Copy the initmydb1.ora to initoradb1.ora;
edit initoradb1.ora; change any neccessary parameters (db_name etc)
create bdump cdump udump etc directories

2) Create the script that will re-create the controlfile
sql> startup mount
sql> alter database backup controlfile to trace;

in USER_DUMP_DEST find the current trace file,
create a script (cr_ctl.sql) from it;
change 'reuse' to 'set'; specify resetlogs

3) create a password file under $ORACLE_HOME/dbs
% orapwd file=orapworadb1 password=oracle entries=5

4) Set ORACLE_SID = oradb1
run the cr_ctl.sql script

% sqlplus /nolog
% connect / as sydba
% @cr_ctl.sql
% create spfile from pfile;
% alter database open resetlogs;

5) add a temp file
alter tablespace temp add tempfile
'/opt/app/oracle/oradata/oradb1/temp01.dbf' size 30M;

Backup Types

Full
- A backup that is not incremental.
- Includes all used data blocks in the datafiles
- Full backups of control files and archived logs always include all blocks in the files

Incremental
- A backup of datafiles that includes only the blocks that have changed since a previous incremental backup.
- Require a full or incremental level 0 backup to serve as a basis

Online
- A backup of online, read/write datafiles when the database is open

Closed
- A backup of any part of the target database when it is mounted but not open
- Can be consistent or inconsistent

Consistent
- A backup taken when the database in mounted(but not open) and was not crashed or shutdown with the ABORT option prior to mounting.
- The checkpoints SCNs in the datafile headers match the header information in the control file and none of the datafiles has changes beyond its checkpoint
- Can be restored without recovery

Inconsistent
- A backup of any part of the target database when
--- It is open
--- It crashed or a SHUTDOWN ABORT was run prior to mounting.
- Requires recovery to become consistent

A level 0 incremental backup
- the base for subsequent incremental backups, copies all blocks containing data.
- The only difference between a level 0 backup and a full backup is that a full backup is never included in an incremental strategy.

Incremental backup

A level n incremental backup in which n is greater than zero backs up either:
- All blocks changed after the most recent backup at level n or lower (the default type of incremental backup, which is called a differential backup)
- All blocks changed after the most recent backup at level n-1 or lower (called a cumulative backup)

Cumulative incremental backups
- reduce the work needed for a restore by ensuring that you only need one incremental backup from any particular level.
- Cumulative backups require more space and time than differential backups, however, because they duplicate the work done by previous backups at the same level.

Concepts - Index

Oracle provides:
- B-tree indexes
- B-tree cluster indexes
- Hash cluster indexes
- Reverse key indexes
- Bitmap indexes
- Bitmap Join indexes

Performance:

However, the presence of many indexes on a table decreases the performance of updates, deletes, and inserts, because Oracle must also update the indexes associated with the table

Function-based index

- A function-based index computes the value of the function or expression and stores it in the index. You can create a function-based index as either a B-tree or a bitmap index.

Example 1

CREATE INDEX uppercase_idx ON employees (UPPER(first_name));

can facilitate processing queries such as this:SELECT * FROM employees WHERE UPPER(first_name) = 'RICHARD';

Example 2

For example, if you create the following index:CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);

then Oracle can use it when processing queries such as this:
SELECT a FROM table_1 WHERE a + b * (c - 1) <>The B-tree structure has the following advantages:
- All leaf blocks of the tree are at the same depth, so retrieval of any record from anywhere in the index takes approximately the same amount of time.
- B-tree indexes automatically stay balanced.
- All blocks of the B-tree are three-quarters full on the average.
- B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.
- Inserts, updates, and deletes are efficient, maintaining key order for fast retrieval.
- B-tree performance is good for both small and large tables and does not degrade as the size of a table grows.


Index Unique Scan
- used for returning the data from B-tree indexes.
- The optimizer chooses a unique scan when all columns of a unique (B-tree) index are specified with equality conditions.

Reverse Key Indexes

- reverses the bytes of each column indexed (except the rowid) while keeping the column order.

- Using the reverse key arrangement eliminates the ability to run an index range scanning query on the index. Because lexically adjacent keys are not stored next to each other in a reverse-key index,

- only fetch-by-key or full-index (table) scans can be performed.

Bitmap index

- Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid

- Not suitable for OLTP applications with large numbers of concurrent transactions modifying the data. Intended for decision support in data warehousing applications where users typically query the data rather than update it.

- Not suitable for columns that are primarily queried with less than or greater than comparisons. Useful for AND, OR, NOT, or equality queries.

- The advantages of using bitmap indexes are greatest for low cardinality columns: that is, columns in which the number of distinct values is small compared to the number of rows in the table.