How to solve 10 Gb of Oracle Database Problems

1. How to configure EM to support SSL protocol

OMS, DBConsole, and Agent in 10g em all support the SSL protocol.

Specific operations:

◆ 1. if there is no environment variable ORACLE_SID, configure it first, or directly input set ORACLE_SID = xxx (in UNIX environment, use different syntax according to different shell types: setenv, export ...)

◆ 2. Type emctl secure to view all the options:

Secure oms

[] [-Reset]

Secure agent

Secure em

Secure dbconsole

[]

Secure setpwd

Secure status [oms url]

Secure lock | unlock

 

◆ 3. Configure oms as an Example

◆ 3.1 stop all oms-related services (opmnctl stopall)

◆ 3.2 knock in: emctl secure oms will let you enter the registration password, and then the system will generate the relevant certificate file. If everything goes well, the system will prompt that the configuration is successful.

◆ 3.3 restart the Service (opmnctl startall)

By default, the ports 7777 and 4889 are available. After the two ports are configured in secure mode, port 4888 supports the SSL protocol. from the emoms in the $ EM_HOMEsysmanconfig directory. the configuration information is displayed in the properties file.

◆ 3.4 check whether oms is running under the https protocol

Emctl secure status oms url

Note: The URL must be complete,

For example:

◆ 4. The Agent and DBconsole configurations are similar.

◆ 5. When the browser is started to run, IE will pop up a window for installing the security certificate, and press "OK ".

 

Ii. How to solve the problem

Taking DB Control as an example, the general steps are as follows:

◆ 1. emctl status dbconsole view the status of db control

◆ 2. Go to the $ ORACLE_HOMEhostname_sidsysmanlog directory to view related logs (such as emoms. log and emagent. log)

◆ 3. Check the configuration file emd. properties and emoms. properties.

Directory: $ ORACLE_HOMEhostname_sidsysmanconfig

◆ 4. Ensure that the listener works properly: lsnrctl status

◆ 5. 10g em uses the connection string defined in the configuration file, rather than directly accessing tnsnames. ora.

Directory: $ ORACLE_HOMEhostname_sidsysmanconfig

File: emoms. properties.

Parameter: oracle. sysman. eml. mntr. emdRepConnectDescriptor

 

3. modified the listening port and how to ensure normal operation of DB Control

◆ 1. Modify the listener. ora and tnsnames. ora files:

To add tnsnames. ora:

 

Listener_1 =

(ADDRESS = (PROTOCOL = TCP)

(HOST = xxxx) (PORT = New PORT ))

 

Then start the database:

SQL> show parameter local_listener;

SQL> alter system set local_listener = listener_1 scope = spfile;

Restart the database to make the modification take effect

◆ 2. Modify $ ORACLE_HOMEhostname_sidsysmanconfig

The emoms. properties file below:

Oracle. sysman. eml. mntr. emdRepPort = New Port

Oracle. sysman. eml. mntr. emdRepConnectDescriptor = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = xxx) (PORT = New PORT) (CONNECT_DATA = (SERVICE_NAME = xxx )))

 

◆ 3. Finally, modify $ ORACLE_HOMEhostname_sidsysmanemd.

Targets. xml:

 

◆ 4. Restart the listener and dbconsole service.

◆ 5. If the above operations are troublesome, you can use the command line tool emca:

Emca-h view all available commands

Emca-r skips the creation of the archive.

 

4. How to modify the service port of DB Control

By default, the Database Control port is 5500. You can change the port according to the instructions below.

If you want to change the oms port, you must change the following three files and then restart db control to make the change take effect:

◆ 1. Edit $ ORACLE_HOME/_/sysman/config/emoms. properties and change the following parameters:

Oracle. sysman. emSDK. svlt. ConsoleServerPort

Oracle. sysman. emSDK. svlt. ConsoleServerHTTPSPort

◆ 2. Edit $ ORACLE_HOME/_/sysman/config/emd. properties and change the following parameters:

REPOSITORY_URL

EmdWalletSrcUrl

◆ 3. Edit $ ORACLE_HOME/Solaris/j2ee/OC4J_DBConsole _/config/http-web-site.xml and change the following parameters:

Web-site port

Note: Back up data before modification.

◆ 5. How to Use "Automatic SGA Management"

As one of the new features introduced by 10 Gb, Automatic SGA Management extracts several parameters that are closely related to memory Management from the initialization parameter file, it is managed by the database itself (managed by the newly added SGA_TARGET parameter), which can reduce the DBA burden in a certain program.

As for the rationality of parameters, it is also necessary to combine AWR Report to verify.

SGA_TARGET = db_cache_size + db_nk_cache_size (n = 2, 4 ,...)

+ Db_keep_cache_size + db_recycle_cache_size +

Shared_pool_size + java_pool_size + large_pool_size + xxxx

Xxx: it is a reserved value. From the current experiment, it is basically 4 MB.

Steps:

◆ 1.

Alter system set sga_target = 300 m scope = both

Create pfile from spfile;

Shutdown immediate;

 

Modify the init. ora file and set the values of these parameters to 0:

Db_cache_size, shared_pool_size, java_pool_size, large_pool_size

 

◆ 2. Start SQLPLUS and start the database with a new pfile File

SQL> startup pfile = ....

 

Let's take a look at the adjustment results:

SQL> select name, block_size, current_size from v $ buffer_pool;

Name block_size current_size

-------------------------------------------------------------

KEEP 8192 204

SQL> Select pool, sum (bytes)/1024/1024 as "M bytes" from v $ SGASTAT

Group by pool;

Pool M bytes

-------------------------------------------------

Java pool 4

Large pool 4

Shared pool 84

205.002403

205.002403 = buffer cache + log buffer + fixed sga + all others...

 

Modify the java pool Value

SQL> alter system set java_pool_size = 20 M;

SQL> select name, block_size, current_size, prev_size from v $ buffer_pool;

Name block_size current_size prev_size

Bytes ----------------------------------------------------------------------------------------------

KEEP 8192 188 204

SQL> Select pool, sum (bytes)/1024/1024 as "M bytes" from v $ SGASTAT

Group by pool;

Pool M bytes

-------------------------------------------------

Java pool 20

Large pool 4

Shared pool 84

189.002403

We can see that the value of db_cache_size has been automatically reduced.

Change the java pool value back.

SQL> alter system set java_pool_size = 8 M;

SQL> select name, block_size, current_size, prev_size from v $ buffer_pool;

Name block_size current_size prev_size

Bytes -----------------------------------------------------------------------------------

KEEP 8192 188 204

SQL> select name, block_size, current_size, prev_size from v $ buffer_pool;

Pool M bytes

-------------------------------------------------

Java pool 20

Large pool 4

Shared pool 84

189.002403

 

This time, the value of db_cache_size has not changed, and the value of JAVA_POOL_SIZE has not changed.

 

Modify the large pool value to 16 Mb.

SQL> alter system set large_pool_size = 16 M;

System altered.

SQL> select name, block_size, current_size, prev_size from v $ buffer_pool;

NAME BLOCK_SIZE CURRENT_SIZE PREV_SIZE

---------------------------------------------------

DEFAULT 8192 176 188

SQL> Select pool, sum (bytes)/1024/1024 as "M bytes" from v $ sgastat group by pool;

Pool m bytes

----------------------

Java pool 20

Large pool 16

Shared pool 84

177.002403

 

This time, the values of db_cache_size and large_pool_size have changed.

Similarly, when shared_pool_size is increased, db_cache_size is automatically reduced.

Although the value of db_nk_cache_size will not be automatically adjusted as workload changes, we can still look at the manual modification of db_nk_block_size.

SQL> alter system set db_2k_cache_size = 4 m;

System altered.

SQL> select name, block_size, current_size, prev_size from v $ buffer_pool;

NAME BLOCK_SIZE CURRENT_SIZE PREV_SIZE

-------------------------------------------------------

DEFAULT 8192 172 176

DEFAULT 2048 4 0 <