When you're trying to install packages with Perl Package Manager (ppm) in a Windows environment a proxy will block the connection to it's repository.
Avoid this by setting the environmental variable HTTP_PROXY like this:
set http_proxy=http://username:password@proxy.net:8080
Monday, March 22, 2010
checking sessions to an Oracle database
Checking the maximum number of sessions:
SQL> select name, value from v$parameter where name='sessions';
Checking the number of currently active sessions:
SQL> select count(*) from v$session;
SQL> select name, value from v$parameter where name='sessions';
Checking the number of currently active sessions:
SQL> select count(*) from v$session;
Looking for blocking sessions:
SQL> select blocking_session from v$session where blocking_session is not NULL;
Looking for idle sessions (more than 24 hours in this example):
SQL> select username from v$session where username is not NULL and status='inactive' and last_call_et/60/60>=24;
Please note that you'll need to be connected as sysdba!
Useful links:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:209412348074#tom26247484939076
Saturday, March 13, 2010
connecting to a database with sqlplus
Here are some examples:
sqlplus user/password@alias
Here the alias has to be resolved into the full name. This can be done with the tnsnames.ora file or by contacting an LDAP directory. Alternative is to provide all the connect details in line.
sqlplus sys/oracle@orcl as sysdba
While this is handy it's also a potential security risk. In Linux/Unix it might show up in show processes. Before 10g you could use quotes like sqlplus "/ as sysdba", now you safely login with sqlplus / as sysdba
sqlplus /nologon
This prevents the immediate login prompt. Afterwards you connect from the SQL prompt:
SQL> connect sys/oracle@orcl as sysdba
Please note that it's not always necessary to specify the sid.
sqlplus user/password@alias
Here the alias has to be resolved into the full name. This can be done with the tnsnames.ora file or by contacting an LDAP directory. Alternative is to provide all the connect details in line.
sqlplus sys/oracle@orcl as sysdba
While this is handy it's also a potential security risk. In Linux/Unix it might show up in show processes. Before 10g you could use quotes like sqlplus "/ as sysdba", now you safely login with sqlplus / as sysdba
sqlplus /nologon
This prevents the immediate login prompt. Afterwards you connect from the SQL prompt:
SQL> connect sys/oracle@orcl as sysdba
Please note that it's not always necessary to specify the sid.
Tuesday, March 9, 2010
listener.ora – tnsnames.ora – alert log
listener.ora
The listener is a server-side process that listens for database connection requests from user processes and launches dedicated server processes to establish sessions. The sessions become the connections between the user process and the database unless shared servers are in use, in which case a dispatcher process is used to share time to shared server processes.
With local naming the user supplies an Oracle Net service alias for the connect string and this is resolved by a local file into the full network address. This local file is the tnsnames.ora file. There's no need for any relationship between the alias, service name and the instance name - it's recommended to keep them the same! The Service Naming branch of the Net Manager creates or edits the Local Naming tnsnames.ora file that resides in ORACLE_HOME/network/admin directory.
You can configure several listeners in the listener.ora file but they must all have different names and addresses. Under Windows the listener will run as a Windows service, but there's no need to create the service explicitly; it'll be created implicitly the first time the listener is started.
A listener finds out about instances by the process of "registration". For static registration, you hard-code a list of instances in the listener.ora file. Dynamic registration means that the instance itself, at startup time, locates a listener and registers with it.
You can start and stop listeners through Database Control, but there's also a command-line utility lsnrctl.
The listener is a server-side process that listens for database connection requests from user processes and launches dedicated server processes to establish sessions. The sessions become the connections between the user process and the database unless shared servers are in use, in which case a dispatcher process is used to share time to shared server processes.
With local naming the user supplies an Oracle Net service alias for the connect string and this is resolved by a local file into the full network address. This local file is the tnsnames.ora file. There's no need for any relationship between the alias, service name and the instance name - it's recommended to keep them the same! The Service Naming branch of the Net Manager creates or edits the Local Naming tnsnames.ora file that resides in ORACLE_HOME/network/admin directory.
You can configure several listeners in the listener.ora file but they must all have different names and addresses. Under Windows the listener will run as a Windows service, but there's no need to create the service explicitly; it'll be created implicitly the first time the listener is started.
A listener finds out about instances by the process of "registration". For static registration, you hard-code a list of instances in the listener.ora file. Dynamic registration means that the instance itself, at startup time, locates a listener and registers with it.
You can start and stop listeners through Database Control, but there's also a command-line utility lsnrctl.
The tnsnames.ora file is a client-side file used for name resolution. It is used by user processes to locate database listeners. It may also be used the instance itself, to locate a listener with which to register.
TNS stands for Transparent Network Substrate
The heart of Oracle Net, a proprietary layered protocol running on top of whatever underlying network transport protocol you choose to use - probably TCP/IP.
The Oracle Net files by default exist in the directory ORACLE_HOME/network/admin. It's possible to relocate them with an environment variable: TNSADMIN. Mainly on systems that have several Oracle Homes. Example on Windows:
set TNSADMIN=c:\oracle\net
alert_.log
The standard Oracle alert log location defined by the initialization parameter DIAGNOSTIC_DEST or USER_DUMP_DEST.
The alert log file, located in the directory specified by the initialization parameter BACKGROUND_DUMP_DEST, contains the most significant routine status messages as well as critical error conditions. When the database is started up or shut down, a message is recorded in the alert log, along with a list of initialization parameters that are different from their default values. In addition, any ALTER DATABASE or ALTER SYSTEM commands issued by the DBA are recorded. Operations involving tablespaces and their datafiles are also recorded here. All error/critical conditions are recorded. The alert log file can be deleted or renamed at any time; it's re-created the next time an alert log message is generated. The trace files for the Oracle instance background processes are also located in BACKGROUND_DUMP_DEST. Trace files are also created for individual user sessions or connections to the database; these are located in the directory specified by the initialization parameter USER_DUMP_DEST. As of Oracle Database 11g Release 1, the diagnostics for an instance are centralized in a single directory specified by the initialization parameter DIAGNOSTIC_DEST.
The DBA will often set up a daily batch job to rename and archive the alert log on a daily basis.
The alert log is a continuous record of critical operations applied to the instance and the database. Its location is determined by the instance parameter BACKGROUND_DUMP_DEST, and its name is alert_SID.log where SID is the name of the instance. The alert log entry for a startup shows all the non-default initialization parameters. Locate the file with this command:
SQL> select value from v$parameter where name='background_dump_dest';
TNS stands for Transparent Network Substrate
The heart of Oracle Net, a proprietary layered protocol running on top of whatever underlying network transport protocol you choose to use - probably TCP/IP.
The Oracle Net files by default exist in the directory ORACLE_HOME/network/admin. It's possible to relocate them with an environment variable: TNSADMIN. Mainly on systems that have several Oracle Homes. Example on Windows:
set TNSADMIN=c:\oracle\net
alert_
The standard Oracle alert log location defined by the initialization parameter DIAGNOSTIC_DEST or USER_DUMP_DEST.
The alert log file, located in the directory specified by the initialization parameter BACKGROUND_DUMP_DEST, contains the most significant routine status messages as well as critical error conditions. When the database is started up or shut down, a message is recorded in the alert log, along with a list of initialization parameters that are different from their default values. In addition, any ALTER DATABASE or ALTER SYSTEM commands issued by the DBA are recorded. Operations involving tablespaces and their datafiles are also recorded here. All error/critical conditions are recorded. The alert log file can be deleted or renamed at any time; it's re-created the next time an alert log message is generated. The trace files for the Oracle instance background processes are also located in BACKGROUND_DUMP_DEST. Trace files are also created for individual user sessions or connections to the database; these are located in the directory specified by the initialization parameter USER_DUMP_DEST. As of Oracle Database 11g Release 1, the diagnostics for an instance are centralized in a single directory specified by the initialization parameter DIAGNOSTIC_DEST.
The DBA will often set up a daily batch job to rename and archive the alert log on a daily basis.
The alert log is a continuous record of critical operations applied to the instance and the database. Its location is determined by the instance parameter BACKGROUND_DUMP_DEST, and its name is alert_SID.log where SID is the name of the instance. The alert log entry for a startup shows all the non-default initialization parameters. Locate the file with this command:
SQL> select value from v$parameter where name='background_dump_dest';
Thursday, March 4, 2010
starting up an instance in different states
NOMOUNT
startup nomount
Start the instance without mounting a database. This does not allow access to the database and usually would be done only for database creation or the re-creation of control files. The database instance has been started using initialization file, processes are started and memory is allocated.
MOUNT
alter database mount
Start the instance and mount the database, but leave it closed. This state allows for certain DBA activities, but does not allow general access to the database. The instance is started and the control file is opened, read but it's contents are not validated.
OPEN
alter database open
startup force restrict
Start the instance, and mount and open the database. Normal database operation means that an instance is started and the database is mounted and open. This mode allows any valid user to connect to the database and perform data access operations. This can be done in unrestricted mode, allowing access to all users, or in restricted mode, allowing access for database administrators only. Normal database operation means that an instance is started and the database is mounted and open. This mode allows any valid user to connect to the database and perform data access operations. If the database is opened then control file is validated against the physical structure of database. The database is verified that all of it's file are in consistent state. If any of the file is not in consistent state, we may need some sort of recovery.
Beginning with Oracle Database 11g Release 2, the preferred (and platform-independent) method of configuring automatic startup of a database is Oracle Restart. Oracle Restart improves the availability of your Oracle database. When you install Oracle Restart, various Oracle components can be automatically restarted after a hardware or software failure or whenever your database host computer restarts.
References:
ip tcp adjust-mss command
While a host will know the MTU of its own interface and possibly that of its peers (from initial handshakes), it will not initially know the lowest MTU in a chain of links to any other peers.
To get around this issue, IP allows fragmentation: dividing the datagram into pieces, each small enough to pass over the single link that is being fragmented for, using the MTU parameter configured for that interface.
RFC 1191 (IPv4) and RFC 1981 (IPv6) describe "Path MTU discovery", a technique for determining the path MTU between two IP hosts. It works by setting the DF (Don't Fragment) option in the IP headers of outgoing packets. Any device along the path whose MTU is smaller than the packet will drop such packets and send back an ICMP "Destination Unreachable (Datagram Too Big)" message containing its MTU. This information allows the source host to reduce its assumed path MTU appropriately. The process repeats until the MTU becomes small enough to traverse the entire path without fragmentation.
Unfortunately, increasing numbers of networks drop ICMP traffic (e.g. to prevent denial-of-service attacks), which prevents path MTU discovery from working. One often detects such blocking in the cases where a connection works for low-volume data but hangs as soon as a host sends a large block of data at a time
Most Ethernet LANs use an MTU of 1500 bytes (modern LANs can use Jumbo frames, allowing for an MTU up to 9000 bytes); however, border protocols like PPPoE will reduce this.
he difference between the MTU seen by end-nodes (e.g. 1500) and the path MTU causes path MTU discovery to come into effect, with the possible result of making some sites behind badly-configured firewalls unreachable. One can possibly work around this, depending on which part of the network one controls; for example one can change the MSS (maximum segment size) in the initial packet that sets up the TCP connection at one's firewall.
Sometimes the demands of efficiency encourage artificially declaring a reduced MTU in software below the true maximum possible length supported - for example: where an ATM (Asynchronous Transfer Mode) network carries IP traffic. Some providers, particularly those with a telephony background, use ATM on their internal backbone network.
ATM operates at optimum efficiency when packet length is a multiple of 48 bytes. This is because ATM is sent as a stream of fixed-length packets (known as 'cells'), each of which can carry a payload of 48 bytes of user data with 5 bytes of overhead for a total cost of 53 bytes per cell. Artificially declaring a reduced MTU in software maximises protocol efficiency at the ATM layer by making the ATM AAL5 total payload length a multiple of 48 bytes whenever possible.
RFC 2516 prescribes a maximum MTU for PPPoE/DSL connections of 1492: a PPPoE header of 6 bytes, leaving enough room for a 1488 byte payload, or 31 full ATM cells.
The TCP MSS Adjustment feature enables the configuration of the maximum segment size (MSS) for transient packets that traverse a router, specifically TCP segments in the SYN bit set, when PPP over Ethernet (PPPoE) is being used in the network. PPPoE truncates the Ethernet maximum transmission unit (MTU) 1492, and if the effective MTU on the hosts (PCs) is not changed, the router in between the host and the server can terminate the TCP sessions. The ip tcp adjust-mss command specifies the MSS value on the intermediate router of the SYN packets to avoid truncation.
When a host (usually a PC) initiates a TCP session with a server, it negotiates the IP segment size by using the MSS option field in the TCP SYN packet. The value of the MSS field is determined by the maximum transmission unit (MTU) configuration on the host. The default MSS value for a PC is 1500 bytes.
The PPP over Ethernet (PPPoE) standard supports a MTU of only 1492 bytes. The disparity between the host and PPPoE MTU size can cause the router in between the host and the server to drop 1500-byte packets and terminate TCP sessions over the PPPoE network. Even if the path MTU (which detects the correct MTU across the path) is enabled on the host, sessions may be dropped because system administrators sometimes disable the ICMP error messages that must be relayed from the host in order for path MTU to work. The ip tcp adjust-mss command helps prevent TCP sessions from being dropped by adjusting the MSS value of the TCP SYN packets. The ip tcp adjust-mss command is effective only for TCP connections passing through the router. In most cases, the optimum value for the max-segment-size argument is 1452 bytes. This value plus the 20-byte IP header, the 20-byte TCP header, and the 8-byte PPPoE header add up to a 1500-byte packet that matches the MTU size for the Ethernet link.
If you are configuring the ip mtu command on the same interface as the ip tcp adjust-mss command, it is recommended that you use the following commands and values:
ip tcp adjust-mss 1452
ip mtu 1492
Example:
XXXNR>enable
XXXNR#conf t
Enter configuration commands, one per line. End with CNTL/Z.
XXXNR(config)#interface vlan 104
XXXNR(config-if)#ip tcp adjust-mss 1250
XXXNR(config-if)#end
XXXNR#copy run start
References:
http://www.cisco.com/en/US/docs/ios/12_2t/12_2t4/feature/guide/ft_admss.html
http://en.wikipedia.org/wiki/Maximum_transmission_unit
http://help.expedient.net/broadband/mtu_ping_test.shtml
> MTU Ping Test
To get around this issue, IP allows fragmentation: dividing the datagram into pieces, each small enough to pass over the single link that is being fragmented for, using the MTU parameter configured for that interface.
RFC 1191 (IPv4) and RFC 1981 (IPv6) describe "Path MTU discovery", a technique for determining the path MTU between two IP hosts. It works by setting the DF (Don't Fragment) option in the IP headers of outgoing packets. Any device along the path whose MTU is smaller than the packet will drop such packets and send back an ICMP "Destination Unreachable (Datagram Too Big)" message containing its MTU. This information allows the source host to reduce its assumed path MTU appropriately. The process repeats until the MTU becomes small enough to traverse the entire path without fragmentation.
Unfortunately, increasing numbers of networks drop ICMP traffic (e.g. to prevent denial-of-service attacks), which prevents path MTU discovery from working. One often detects such blocking in the cases where a connection works for low-volume data but hangs as soon as a host sends a large block of data at a time
Most Ethernet LANs use an MTU of 1500 bytes (modern LANs can use Jumbo frames, allowing for an MTU up to 9000 bytes); however, border protocols like PPPoE will reduce this.
he difference between the MTU seen by end-nodes (e.g. 1500) and the path MTU causes path MTU discovery to come into effect, with the possible result of making some sites behind badly-configured firewalls unreachable. One can possibly work around this, depending on which part of the network one controls; for example one can change the MSS (maximum segment size) in the initial packet that sets up the TCP connection at one's firewall.
Sometimes the demands of efficiency encourage artificially declaring a reduced MTU in software below the true maximum possible length supported - for example: where an ATM (Asynchronous Transfer Mode) network carries IP traffic. Some providers, particularly those with a telephony background, use ATM on their internal backbone network.
ATM operates at optimum efficiency when packet length is a multiple of 48 bytes. This is because ATM is sent as a stream of fixed-length packets (known as 'cells'), each of which can carry a payload of 48 bytes of user data with 5 bytes of overhead for a total cost of 53 bytes per cell. Artificially declaring a reduced MTU in software maximises protocol efficiency at the ATM layer by making the ATM AAL5 total payload length a multiple of 48 bytes whenever possible.
RFC 2516 prescribes a maximum MTU for PPPoE/DSL connections of 1492: a PPPoE header of 6 bytes, leaving enough room for a 1488 byte payload, or 31 full ATM cells.
The TCP MSS Adjustment feature enables the configuration of the maximum segment size (MSS) for transient packets that traverse a router, specifically TCP segments in the SYN bit set, when PPP over Ethernet (PPPoE) is being used in the network. PPPoE truncates the Ethernet maximum transmission unit (MTU) 1492, and if the effective MTU on the hosts (PCs) is not changed, the router in between the host and the server can terminate the TCP sessions. The ip tcp adjust-mss command specifies the MSS value on the intermediate router of the SYN packets to avoid truncation.
When a host (usually a PC) initiates a TCP session with a server, it negotiates the IP segment size by using the MSS option field in the TCP SYN packet. The value of the MSS field is determined by the maximum transmission unit (MTU) configuration on the host. The default MSS value for a PC is 1500 bytes.
The PPP over Ethernet (PPPoE) standard supports a MTU of only 1492 bytes. The disparity between the host and PPPoE MTU size can cause the router in between the host and the server to drop 1500-byte packets and terminate TCP sessions over the PPPoE network. Even if the path MTU (which detects the correct MTU across the path) is enabled on the host, sessions may be dropped because system administrators sometimes disable the ICMP error messages that must be relayed from the host in order for path MTU to work. The ip tcp adjust-mss command helps prevent TCP sessions from being dropped by adjusting the MSS value of the TCP SYN packets. The ip tcp adjust-mss command is effective only for TCP connections passing through the router. In most cases, the optimum value for the max-segment-size argument is 1452 bytes. This value plus the 20-byte IP header, the 20-byte TCP header, and the 8-byte PPPoE header add up to a 1500-byte packet that matches the MTU size for the Ethernet link.
If you are configuring the ip mtu command on the same interface as the ip tcp adjust-mss command, it is recommended that you use the following commands and values:
ip tcp adjust-mss 1452
ip mtu 1492
Example:
XXXNR>enable
XXXNR#conf t
Enter configuration commands, one per line. End with CNTL/Z.
XXXNR(config)#interface vlan 104
XXXNR(config-if)#ip tcp adjust-mss 1250
XXXNR(config-if)#end
XXXNR#copy run start
References:
http://www.cisco.com/en/US/docs/ios/12_2t/12_2t4/feature/guide/ft_admss.html
http://en.wikipedia.org/wiki/Maximum_transmission_unit
http://help.expedient.net/broadband/mtu_ping_test.shtml
> MTU Ping Test
Tuesday, March 2, 2010
client-identifier command
Router(dhcp-config)#client-identifier unique-identifier
Specifies the unique identifier for DHCP clients. This command is used for DHCP requests. DHCP clients require client identifiers. The unique identification of the client is specified in dotted hexadecimal notation, for example, 01b7.0813.8811.66, where 01 represents the Ethernet media type. Every request sent from a DHCP client to DHCP servers contains a hardware type and a client hardware address. For Ethernet and 802.11 wireless clients, the hardware type is always 01. The client hardware address is simply the MAC address of the client's network (Ethernet or Wireless) interface. Every request sent from a DHCP client to DHCP servers may optionally also contain a DHCP Client Identifier option. This is an arbitrary value that may be used to identify a client instead of the hardware type and client hardware address. Traditionally, the DHCP Client Identifier (when present) has been set to a value equal to the hardware type followed by the client hardware address. (For example, 01 00 01 02 a0 bc d3.)
Example:
Router#conf t
Enter configuration commands, one per line. End with CNTL/Z.
Router(config)#ip dhcp pool AccessPoint1
Router(config)#client identifier 01ab.cdef.ghij.kl
Router(config)#client-name AccessPoint1
References:
http://www.cisco.com/en/US/docs/ios/12_2/ip/configuration/guide/1cfdhcp.html#wp1001108
Specifies the unique identifier for DHCP clients. This command is used for DHCP requests. DHCP clients require client identifiers. The unique identification of the client is specified in dotted hexadecimal notation, for example, 01b7.0813.8811.66, where 01 represents the Ethernet media type. Every request sent from a DHCP client to DHCP servers contains a hardware type and a client hardware address. For Ethernet and 802.11 wireless clients, the hardware type is always 01. The client hardware address is simply the MAC address of the client's network (Ethernet or Wireless) interface. Every request sent from a DHCP client to DHCP servers may optionally also contain a DHCP Client Identifier option. This is an arbitrary value that may be used to identify a client instead of the hardware type and client hardware address. Traditionally, the DHCP Client Identifier (when present) has been set to a value equal to the hardware type followed by the client hardware address. (For example, 01 00 01 02 a0 bc d3.)
Example:
Router#conf t
Enter configuration commands, one per line. End with CNTL/Z.
Router(config)#ip dhcp pool AccessPoint1
Router(config)#client identifier 01ab.cdef.ghij.kl
Router(config)#client-name AccessPoint1
References:
http://www.cisco.com/en/US/docs/ios/12_2/ip/configuration/guide/1cfdhcp.html#wp1001108
Subscribe to:
Posts (Atom)