Tuesday Nov 04, 2014

Enterprise Manager agentTZRegion

EM didn't like the switch from daylight savings to standard time - it was locked into Pacific daylight savings:

In $ORACLE_HOME/node1_sid/sysman/log/emdb.nohup
----- Mon Nov  3 19:36:01 2014::tzOffset for -07:00 is -420(min), but agent is runnning with tzOffset -480(min)


$ grep TZ $ORACLE_HOME/node1_sid/sysman/config/emd.properties
agentTZRegion=-07:00


$ grep US/Pacific $ORACLE_HOME/sysman/admin/supportedtzs.lst
US/Pacific
US/Pacific-New

$ export TZ=US/Pacific

$ $ORACLE_HOME/bin/emctl resetTZ agent
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Updating /u01/app/oracle/product/11.2.0/dbhome_1/node1_sid/sysman/config/emd.properties...
Successfully updated /u01/app/oracle/product/11.2.0/dbhome_1/node1_sid/sysman/config/emd.properties.
Login as the em repository user and run the  script:
exec mgmt_target.set_agent_tzrgn('node1:3938','US/Pacific')
and commit the changes
This can be done for example by logging into sqlplus and doing
SQL> exec mgmt_target.set_agent_tzrgn('node1:3938','US/Pacific')
SQL> commit

[also ran "emctl resetTZ agent" on other RAC node]

$ grep TZ $ORACLE_HOME/node1_sid/sysman/config/emd.properties
agentTZRegion=US/Pacific


$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 4 07:28:57 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>
SQL> alter session set current_schema = SYSMAN;

Session altered.

SQL> exec mgmt_target.set_agent_tzrgn('node1:3938','US/Pacific')

PL/SQL procedure successfully completed.

SQL> exec mgmt_target.set_agent_tzrgn('node2:3938','US/Pacific')

PL/SQL procedure successfully completed.

$ emctl stop dbconsole
$ emctl start dbconsole

Monday Sep 08, 2014

emca and ORA-12537: TNS:connection closed

Problem: I couldn't configure Enterprise Manager for a RAC database running on two SPARC T5-4 Servers

$ emca -deconfig dbcontrol db -repos drop  -cluster
$ emca -config dbcontrol db -repos create -cluster

STARTED EMCA at Sep 6, 2014 1:03:42 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database unique name: orcl2
Service name: orcl2
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/11.2.0/grid ]:
Password for SYS user: 
Password for DBSNMP user: 
Password for SYSMAN user: 40
Cluster name: ofsaa-scan
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/dbhome_1

Database instance hostname ................ Listener ORACLE_HOME ................ /u01/app/11.2.0/grid
Listener port number ................ 1521
Cluster name ................ proj-scan
Database unique name ................ orcl2
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Sep 6, 2014 1:04:28 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/orcl2/emca_2014_09_06_13_03_41.log.
Sep 6, 2014 1:04:33 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Sep 6, 2014 1:09:20 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Sep 6, 2014 1:09:22 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngineRemotely
WARNING: Error during db connection : ORA-12537: TNS:connection closed

Sep 6, 2014 1:09:33 PM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Failed to unlock all EM-related accounts
Sep 6, 2014 1:09:33 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Failed to unlock all EM-related accounts
Refer to the log file at /u01/app/oracle/cfgtoollogs/emca/orcl2/emca_2014_09_06_13_03_41.log for more details.
Could not complete the configuration. Refer to the log file at /u01/app/oracle/cfgtoollogs/emca/orcl2/emca_2014_09_06_13_03_41.log for more details.


Very strange. Everything else seemed fine.

$ srvctl status scan
$ srvctl status scan_listener
$ srvctl status listener
$ srvctl status database -d orcl2


I spent so many hours digging through log files, listener.ora, tnsnames.ora, running netca and netmgr. Nothing helped.

Two other strange things that I eventually noticed.
- sqlplus connections to the SCAN address had intermittent failures.
- Successful connections where always to one instance, never the other.

All of the failures where ORA-12537: TNS:connection closed

Eventually, I found MOS note 1069517.1, "ORA-12537 / ORA-12547 or TNS-12518 if Listener (including SCAN Listener) and Database are Owned by Different OS User". Sure enough, on one SPARC T5-4 servers, the setgid bit wasn't set on the oracle executable, but it was set on the other server.

# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
# cd $ORACLE_HOME/bin
# chmod u+s oradism nmo nmhs emtgtctl2 jssu extjob nmb oracle
# chmod g+s emtgtctl2 oracle


Friday Jan 24, 2014

Redo Log Switches

In Gathering Database Statistics in a Test Harness I said that use of the Automatic Workload Repository (AWR) is fundamental to understanding Oracle Database performance.

When I finished the scripts for the blog and ran the first test run, the AWR report was quite clear: I need to increase the size of the redo logs:

Report Summary

Top ADDM Findings by Average Active Sessions

Finding Name Avg active sessions of the task Percent active sessions of finding Task Name Begin Snap Time End Snap Time
Top SQL Statements 1.00 80.56 ADDM:1351308822_1_2046 24-Jan-14 11:36 24-Jan-14 11:42
Log File Switches 1.00 5.38 ADDM:1351308822_1_2046 24-Jan-14 11:36 24-Jan-14 11:42


Instance Activity Stats - Thread Activity

  • Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
log switches (derived) 13 126.78

Finding 2: Log File Switches
Impact is .05 active sessions, 5.38% of total activity.
-------------------------------------------------------
Log file switch operations were consuming significant database time while
waiting for checkpoint completion.
This problem can be caused by use of hot backup mode on tablespaces.  DML to
tablespaces in hot backup mode causes generation of additional redo.

...

   Recommendation 2: Database Configuration
   Estimated benefit is .05 active sessions, 5.38% of total activity.
   ------------------------------------------------------------------
   Action
      Increase the size of the log files to 1552 M to hold at least 20 minutes
      of redo information.

Original log files and groups:

SQL> select GROUP#,THREAD#,BYTES from v$log;

    GROUP#    THREAD#      BYTES
---------- ---------- ----------
     1         1        52428800
     2         1        52428800
     3         1        52428800

SQL> select MEMBER from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log

Create new ones:

SQL > ALTER DATABASE
  ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo04.log')
      SIZE 5G;

SQL> ALTER DATABASE
  ADD LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log')
      SIZE 5G;

SQL> ALTER DATABASE
  ADD LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log')
      SIZE 5G;

Drop the old one:

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

SQL> alter system checkpoint;
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

(Dropping group 2 is OK after checkpoint)


Done:

SQL> select GROUP#,THREAD#,BYTES from v$log;

    GROUP#    THREAD#       BYTES
---------- ---------- ----------
     4          1     5368709120
     5          1     5368709120
     6          1     5368709120
About

Jeff Taylor-Oracle

Search

Categories
Archives
« April 2015
SunMonTueWedThuFriSat
   
1
2
4
5
6
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
  
       
Today