Oracle 11.2 silent installation on opensolaris

Even it is not certified but after appearance of Oracle 11.2 for Solaris (x86-64)
I decided to give a try for silent installation of Oracle on opensolaris

I downloaded Solaris iso and installed it as 64-bit on one of virtual
machines with 1Gb of RAM. Installation of OS went without problem
and shortly I was enjoying a great look of OpenSolaris.

I stopped myself admiring good looking interface (interesting, do they sell
OpenSolaris alike doughnuts somewhere in SUN office :) ? )
and connected through black and white ssh terminal
to execute silent installation.

By the way I got response from Support Oracle
that SHOW_INSTALL_PROGRESS_PAGE parameter is not used
in 11.2 installation anymore and I won't see that very useful
progress bar during installation.

Based on silent installation that I executed already on Linux for 11.2 I started the following:

./runInstaller -silent \
-responseFile /export/home/oracle/install/database/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
oracle.install.db.InstallEdition=EE \
security_updates_via_myoraclesupport=false \
decline_security_updates=true \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=oper \
ORACLE_BASE="/u01/app/oracle" \
ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"

Even I had enough space on disk I got error

SUMMARY:
     - You may not have enough space on your drive for a successful install

After check of prerequisites for software installation I found that
size of temp directory should be more than 1Gb and after variables
assingment and directory creation (~/tmp) I started installation again:

export TMP=~/tmp
export TMPDIR=~/tmp
./runInstaller ...

It started and went till the end of the installation asking to
execute root.sh (which was done) but when I tried to connect to
sqlplus under sysdba I got following:

ORA-12560: TNS:protocol adapter error

Might be it's just because of ORACLE_HOME and ORACLE_SID not set?
Ok, I set them (ORACLE_SID to not existed) but got the same error.
Since it should be direct attach to oracle (not through TNS) I decided
to check oracle executable and found that it was 0 bytes.

Further investigation and journey through installation logs showed that
problem appeared during compilation of irman and ioracle:

ld: fatal: library -lcrypto: not found

Internet search discovered a blog post
"Installing the new Oracle 11g on OpenSolaris"
where that problem had been resolved through creation of link:

ln -s /lib/amd64/libcrypto.so /usr/sfw/lib/amd64

Great! I created the link and started silent installation again.
It went without errors but when I checked
oracle binary at the end, it was 0 bytes length again! Why?!

Back to installer logs and there was another
error during compilation of ioracle:

ld: fatal: mmap anon failed: Resource temporarily unavailable

My searches did not give me much information but something
was telling me that it either lack of RAM(1Gb) or small size
of swap (512Mb) and periodic failure of
manual compilation for ioracle almost proved it.

I changed size of swap device first because vmstat was showing
about 60 processes in wait state and bounced the server:

root@srv4:~# zfs get volsize rpool/swap
NAME        PROPERTY  VALUE    SOURCE
rpool/swap  volsize   512M     -
root@srv4:~# zfs set volsize=2G rpool/swap
root@srv4:~# zfs get volsize rpool/swap
NAME        PROPERTY  VALUE    SOURCE
rpool/swap  volsize   2G       -

After that change I removed installed software, deleted oracle home
entry from inventory.xml and started installation again.
That time oracle binary was compiled successfully
and I was able to login to sqlplus and created database
using dbca without problems:

$ ./dbca -silent -createDatabase -gdbName dev -templateName General_Purpose.dbc \
> -emConfiguration none -datafileDestination /u01/oradata \
> -sysPassword ... -systemPassword ... -storageType FS -initParams filesystemio_options=setall
...
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/dev/dev.log" for further details.

Another thing is to mention that to start OUI I had to add following packages:
SUNWarc SUNWctpls SUNWhea SUNWmfrun SUNWscp SUNWswmt
which allowed me to avoid errors:

Exception in thread "main" java.lang.UnsatisfiedLinkError:
/tmp/OraInstall2009-12-18_01-31-58PM/jdk/jre/lib/amd64/motif21/libmawt.so:
ld.so.1: java: fatal: libXm.so.4: open failed:
No such file or directory

Have a good day and Merry Christmas!

Exadata Simulation test

As you know, 11gR2 came with new feature that you can simulate performance effects
from Exadata Storage Server on your workload.

Just to check that functionality I installed Oracle 11g Release 2 on my local VM
(Oracle EL 5.2 32bit) and created test database called o1102

I created a user, a table with 5000000 entries, tuning set after that and executed analysis
using the script provided in $ORACLE_HOME/rdbms/admin

That's what I got:

create table t1 (c1 int, c2 varchar2(100), c3 date);

insert into t1 select rownum, dbms_random.string('X', 100),
sysdate + dbms_random.value(-100, 100) from dual
connect by level <= 5000000;

commit;
SQL> select count(*) from t1
  2  where t1.c2 like '___AB%'
  3  union all
  4  select count(*) from t1
  5  where t1.c2 like '___AC%'
  6  /

  COUNT(*)
----------
      3789
      3803

SQL> select sql_id, sql_text from v$sqlarea where sql_text like 'select count(*) from t1%';

SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
g64qhr1vp8zwr select count(*) from t1 where t1.c2 like '___AB%' union all select count(*) from
               t1 where t1.c2 like '___AC%'

5d28bd9s5pc21 select count(*) from t1, t1 t2 where t1.c2 like '___AB%' or t2.c2 like '___AC%'



SQL> var
variable   refcur
datatype   REFCURSOR



SQL> exec DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'my_sts1', description  => 'to test exadata');

PL/SQL procedure successfully completed.



SQL> exec open :refcur for SELECT value(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''g64qhr1vp8zwr''')) p;

PL/SQL procedure successfully completed.

SQL> exec DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_sts1', populate_cursor => :refcur, load_option => 'INSERT')

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> @$ORACLE_HOME/rdbms/admin/tcellsim.sql

10 Most active SQL tuning sets
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NAME                           OWNER                           SQL_COUNT DESCP
------------------------------ ------------------------------ ---------- --------------------
my_sts1                        AG                                      1 to test exadata

Specify the name and owner of SQL tuning set to use
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for sts_name: my_sts1
Enter value for sts_owner: AG

 >> SQL tuning set specified: my_sts1 owned by AG

Run Cell simulation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>> 1. create a spa analysis task to test cell simulation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 >> Name of SPA analysis task: TASK_16

>> 2. Test execute statements with cell simulatin DISABLED
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>> 3. Test execute statements with cell simulation ENABLED
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>> 4. Compare peformance and generate analysis report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

General Information
---------------------------------------------------------------------------------------------

 Task Information:                              Workload Information:
 ---------------------------------------------  ---------------------------------------------
  Task Name    : TASK_16                         SQL Tuning Set Name        : my_sts1
  Task Owner   : AG                              SQL Tuning Set Owner       : AG
  Description  :                                 Total SQL Statement Count  : 1

Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name  : EXEC_12                Started             : 09/21/2009 16:12:00
  Execution Type  : COMPARE PERFORMANCE    Last Updated        : 09/21/2009 16:12:00

  Description     :                        Global Time Limit   : UNLIMITED
  Scope           : COMPREHENSIVE          Per-SQL Time Limit  : UNUSED
  Status          : COMPLETED              Number of Errors    : 0

Analysis Information:
---------------------------------------------------------------------------------------------
 Before Change Execution:                          After Change Execution:
 ---------------------------------------------     ---------------------------------------------
  Execution Name      : cell_simulation_DISABLED    Execution Name      : cell_simulation_ENABLED
  Execution Type      : TEST EXECUTE                Execution Type      : TEST EXECUTE
  Scope               : COMPREHENSIVE               Scope               : COMPREHENSIVE
  Status              : COMPLETED                   Status              : COMPLETED
  Started             : 09/21/2009 16:11:05         Started             : 09/21/2009 16:11:43

  Last Updated        : 09/21/2009 16:11:43         Last Updated        : 09/21/2009 16:11:59
  Global Time Limit   : UNLIMITED                   Global Time Limit   : UNLIMITED
  Per-SQL Time Limit  : UNUSED                      Per-SQL Time Limit  : UNUSED
  Number of Errors    : 0                           Number of Errors    : 0

 ---------------------------------------------
 Comparison Metric: IO_INTERCONNECT_BYTES
 ------------------
 Workload Impact Threshold: 1%
 --------------------------
 SQL Impact Threshold: 1%
 ----------------------


Report Summary
---------------------------------------------------------------------------------------------

Projected Workload Change Impact:
-------------------------------------------
 Overall Impact      :  0%
 Improvement Impact  :  0%
 Regression Impact   :  0%

SQL Statement Count
-------------------------------------------
 SQL Category  SQL Count  Plan Change Count
 Overall               1                  0

 Unchanged             1                  0

Top 1 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
|           |               | Impact on | Execution | Metric     | Metric     | Impact |
| object_id | sql_id        | Workload  | Frequency | Before     | After      | on SQL |
----------------------------------------------------------------------------------------
|         6 | g64qhr1vp8zwr |     -.01% |         1 | 1120731136 | 1120845824 |  -.01% |
----------------------------------------------------------------------------------------


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

It seems that there is no performance improvement for the query
that I executed but at least functionality was tested and might be it will show
something more encouraging to another queries.

Have a good day!

Silent installation of Oracle Scheduler Agent

One of the Oracle 11g New Features is Remote External Jobs
To be able execute external jobs on remote host Oracle Scheduler Agent should be installed.
It is coming as part of Oracle Database Gateways 11g installation and can be installed in silent mode.

Giving that, I decided to install the agent using silent mode: created response file under
temporary location and left only entries with defined parameters:

$ cat /tmp/schagent.rsp
RESPONSEFILE_VERSION=2.2.1.0.0
UNIX_GROUP_NAME="oinstall"
FROM_LOCATION="/u03/install/11gateways/gateways/stage/products.xml
ORACLE_HOME="/u01/app/oracle/product/11.1.0/scheduler"
ORACLE_HOME_NAME="OraScheduler_11"
SHOW_WELCOME_PAGE=false
SHOW_CUSTOM_TREE_PAGE=false
SHOW_SUMMARY_PAGE=false
SHOW_INSTALL_PROGRESS_PAGE=false
SHOW_CONFIG_TOOL_PAGE=false
SHOW_ROOTSH_CONFIRMATION=true
SHOW_END_SESSION_PAGE=false
SHOW_EXIT_CONFIRMATION=true
NEXT_SESSION=false
NEXT_SESSION_ON_FAIL=true
TOPLEVEL_COMPONENT={"oracle.rdbms.scheduler","11.1.0.6.0"}
SHOW_SPLASH_SCREEN=false
COMPONENT_LANGUAGES={"en"}
INSTALL_TYPE="Typical"
oracle.rdbms.scheduler:DEPENDENCY_LIST={"oracle.jdk:1.5.0.1.1"}
s_hostName="srv2"
s_port="1888"

$ ./runInstaller -silent -responseFile /tmp/schagent.rsp -noconfig
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB.   Actual 2700 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 1983 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-07-06_08-57-43PM. Please wait ...
[oracle@srv2 gateways]$ Oracle Universal Installer, Version 11.1.0.6.0 Production
Copyright (C) 1999, 2007, Oracle. All rights reserved.

You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2009-07-06_08-57-43PM.log
............................................................... 100% Done.

SEVERE:OUI-10133:Invalid staging area. There are no top level components for Linux available for installation in this staging area.

It failed even all variables that I defined in response file were shown in log file of installation.

I think I already I saw it for some of silent installations and resolution was to put
original response file for installer and add parameters for ORACLE_HOME,
ORACLE_HOME_NAME (all others seem to be unnecessary):

$ ./runInstaller -silent -responseFile /u03/install/11gateways/gateways/response/schagent.rsp \
> ORACLE_HOME="/u01/app/oracle/product/11.1.0/scheduler" \
> ORACLE_HOME_NAME="OraScheduler_11" -noconfig
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB.   Actual 2700 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 1983 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-07-06_09-02-46PM. Please wait ...
[oracle@srv2 gateways]$ Oracle Universal Installer, Version 11.1.0.6.0 Production
Copyright (C) 1999, 2007, Oracle. All rights reserved.

You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2009-07-06_09-02-46PM.log
.................................................................................................... 100% Done.


Loading Product Information
...................................................................................................................... 100% Done.


Analyzing dependencies
.......................................................................................................... 100% Done.


-----------------------------------------------------------------------------
Summary
Global Settings
    Source: /u03/install/11gateways/gateways/stage/products.xml
    Oracle Home: /u01/app/oracle/product/11.1.0/scheduler (OraScheduler_11)
    Installation Type: Typical
Product Languages
   English
Space Requirements
   / Required 177MB (includes 47MB temporary) : Available 2.51GB
New Installations (2 products)
   Oracle Scheduler Agent 11.1.0.6.0
   Sun JDK 1.5.0.1.1
-----------------------------------------------------------------------------


Installation in progress (Mon Jul 06 21:03:01 EST 2009)
...........................................................................................                                     91% Done.
Install successful

Linking in progress (Mon Jul 06 21:03:27 EST 2009)
Link successful

Setup in progress (Mon Jul 06 21:03:27 EST 2009)
..                                                              100% Done.
Setup successful

End of install phases.(Mon Jul 06 21:03:28 EST 2009)
WARNING:
The following configuration scripts need to be executed as the "root" user.
#!/bin/sh
#Root script to run
/u01/app/oracle/product/11.1.0/scheduler/root.sh
To execute the configuration scripts:
    1. Open a terminal window
    2. Log in as "root"
    3. Run the scripts

The installation of Oracle Scheduler Agent was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2009-07-06_09-02-46PM.log' for more details.

[oracle@srv2 gateways]$ su -
Password:
[root@srv2 ~]# /u01/app/oracle/product/11.1.0/scheduler/root.sh
[root@srv2 ~]#

The agent had been installed and it had to be configured to communicate with database
which will execute remote external jobs.

Have a good day!

duplicate target database from active database

It is always educational to get errors, problems during testing or creation of new systems
especially when you are learning new features or investigating how different configurations
behave and real lfie cases sometimes are not the same as it described in documentation.

That's why I prefer not to think about configuration of test databases on VM religiously
but to create them in minimal configuration and change it later when errors or problems
are being caught.

It helps to get problems and since they are test servers they can be easily broken, recreated
but experience gained and it can be useful for production support of databases.

Recently testing Data Guard switchover for heterogenous databases (Linux: 32-bit <-> 64-bit)
I created several standby databases on virtual machines where primary is on Linux 32-bit
but one of standby databases is on Linux 64-bit.

So I decided to test one of oracle 11g new features
"duplicate target database ... from active database"
to create standby database on Linux 64-bit from primary which is on 32-bit OS

The prerequisite for that RMAN command is to connect to target(primary) and auxiliary
(future standby) through SQL*Net and static configuration have been added to listener.ora file
on standby host:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=tstp)
      (GLOBAL_DBNAME=tstp)
      (ORACLE_HOME=/u03/app/oracle/product/11.1.0/db_1)
    )
  )

where tstp - database name for standby database (the same is for primary)

The primary database was created using dbca in silent mode

dbca -silent                      \
-createDatabase                   \
-templateName General_Purpose.dbc \
-gdbName tstp                     \
-sid tstp                         \
-SysPassword sys                  \
-SystemPassword sys               \
-emConfiguration NONE             \
-datafileDestination /u01/oradata \
-storageType FS                   \
-characterSet WE8ISO8859P15       \
-memoryPercentage 40

and was changed to archivelog mode. I created password file for standby instance
and started it in nomount mode as it required and checked connection to both instance
under sysdba:

rman target sys/sys@tstp auxiliary sys/sys@tsts
Recovery Manager: Release 11.1.0.7.0 - Production on Wed Jun 10 01:58:13 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TSTP (DBID=532361881)
connected to auxiliary database: TSTP (not mounted)

But unfortunately duplicate command failed due to errors:

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 10-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=170 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   file  '/u01/app/oracle/product/11.1.0/db_1/dbs/orapwtstp' auxiliary format
 '/u03/app/oracle/product/11.1.0/db_1/dbs/orapwtstp'   ;
}
executing Memory Script

Starting backup at 10-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/10/2009 01:58:36
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/10/2009 01:58:36
ORA-17627: ORA-12577: Message 12577 not found;  product=RDBMS; facility=ORA

I could not find description of the error ORA-12577 but got description for 17627

oerr ora 17627
17627, 00000, "%s"
// *Cause:  An error returned by OCI while sending/receiving message from
//          remote instance
// *Action: Look at error message and take appropriate action or contact
//          Oracle Support Services for further assistance

All my further attempts somehow to get more meaning of the error failed
and I decided to create standby at the same host as primary.

Preparation steps were the same as for 64-bit server but SID of standby was different - tsts
I checked connection to both instances under sysdba and executed duplicate command.

It was completed without problem and standby database from active database had been created:

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 10-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK
contents of Memory Script:
{
   backup as copy reuse
   file  '/u01/app/oracle/product/11.1.0/db_1/dbs/orapwtstp' auxiliary format
 '/u01/app/oracle/product/11.1.0/db_1/dbs/orapwtsts'   ;
}
executing Memory Script
...
contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/oradata/tsts/control01.ctl';
   sql clone 'alter database mount standby database';
}
executing Memory Script
...
Finished backup at 10-JUN-09

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script
...
Finished backup at 10-JUN-09

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
...
Finished Duplicate Db at 10-JUN-09

RMAN> exit

So now I have a couple of things to check and test to understand if "duplicate target database
for standby from active database" can be applied to create standby database on Linux 64-bit
from primary on Linux 32-bit:
1. Creation of standby database from active database on different from primary server but
with the same OS (Linux 32-bit)
2. Try to create standby on 64-bit from primary on 32-bit after all latest Data Guard patches applied
3. Trace RMAN sessions that are failing with ORA- errors during duplicate operation


Have a good day!

READ-ONLY standby database in Oracle 11g Data Guard

Oracle 11g came with new feature called active data guard which allows to apply redo
while database is opened for read-only access. This feature is not free and license should
be purchased, that's why I was wondering how it can be managed in dgmgrl command line
to open standby database just in read-only mode w/o active recovery and how to return it back.

I created Data Guard configuration with Oracle 11.1.0.7 on EL 5.2 32-bit

DGMGRL> show configuration verbose

Configuration
  Name:                bee_dg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    bee    - Primary database
    beesby - Physical standby database

Fast-Start Failover: DISABLED

Current status for "bee_dg":
SUCCESS

And using dgmgrl commands changed state of standby database to read-only:

DGMGRL> show database beesby

Database
  Name:            beesby
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  APPLY-ON
  Instance(s):
    newsby

Current status for "beesby":
SUCCESS

DGMGRL> edit database beesby set state = read-only;
Succeeded.
DGMGRL> show database beesby

Database
  Name:            beesby
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  READ-ONLY
  Instance(s):
    newsby

Current status for "beesby":
SUCCESS

In alert log the following entries appeared:

...
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
...
ALTER DATABASE OPEN READ ONLY
...

So operation to change standby database to read only state finished okay
but what would happen if to change status to apply-on back.
(Oracle 11g documentation has lack of states to change in DG
"EDIT DATABASE (state)" : OFFLINE, ONLINE, READ-ONLY)

I changed state back to APPLY-ON and that's what I got in alert log:

DGMGRL> edit database beesby set state = apply-on;
Succeeded.

...
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
...

Data Guard changed standby database state to active data guard becuase it was not bounced
to mount mode with further start of managed recovery but simply automatic recovery had been
started.
I tried different show commands to get information about active data guard usage
but did not find any that reflects real state of standby database only queries from
v$managed_standby and v$database helped

newsby> select (select process from v$managed_standby where process like 'MRP%'), (select open_mode from v$database) from dual;

(SELECTPR (SELECTOPE
--------- ----------
MRP0      READ ONLY

they exactly told me about active data guard option

But how to put standby database back to mount mode together with managed recovery?
One of the states that can be set is OFFLINE, so I decided to execute a couple of
"edit database" commands to change state to OFFLINE and then to APPLY-ON

DGMGRL> edit database beesby set state = offline;
Operation requires shutdown of instance "newsby" on database "beesby"
Shutting down instance "newsby"...
Database closed.
Database dismounted.
ORACLE instance shut down.

alert.log:
...
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
...
Shutting down instance (immediate)
...

Unfortunately all my further attempts to startup standby instance in mount mode using dgmgrl
command "edit database beesby set state = apply-on;" were not successful even I set up static listener configuration and was able to connect through tns name to idle standby instance

DGMGRL> edit database beesby set state = apply-on;
Error: ORA-01034: ORACLE not available

Failed.

Only when I set environment variables for standby database I started instance
simply using "startup mount" in dgmgrl

DGMGRL> edit database beesby set state = apply-on;
Error:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

DGMGRL> startup mount
ORACLE instance started.
Database mounted.

select statements showed me the status that I wanted - mounted with managed recovery

newsby> select (select process from v$managed_standby where process like 'MRP%'), (select open_mode from v$database) from dual;

(SELECTPR (SELECTOPE
--------- ----------
MRP0      MOUNTED

In addition I checked v$option trying to find information about either "Active Data Guard"
or "Real-time Query" options but did not see any of them.
Also it is not clear how to avoid installation of "Active Data Guard" option or how to disable it
if it is not purchased


Have a good day!

Changing SID for standby database in Data Guard configuration

There was a question at oracle-l forum regarding possibility to use different SID
for primary and standby databases. It should be possible and it is possible
since you can create standby database on the same host as primary.

Another thing that expressed my interest is Data Guard reaction for standby's SID change

I have created DG configuration with 2 standby databases
(11.1.0.7 on CentOS 64-bit as primary and 11.1.0.7 on OEL5 32-bit for standby databases)

DGMGRL> show configuration verbose

Configuration
 Name:                bee
 Enabled:             YES
 Protection Mode:     MaxPerformance
 Databases:
   bee64  - Primary database
   bee    - Physical standby database
          - Fast-Start Failover target
   beesby - Physical standby database

Fast-Start Failover: ENABLED
 Threshold:           30 seconds
 Target:              bee
 Observer:            oel1
 Lag Limit:           2000000000 seconds
 Shutdown Primary:    TRUE
 Auto-reinstate:      TRUE

Current status for "bee":
SUCCESS

And decided to change SID for beesby database from beesby to newsby
as long as that is not failover target database:

1. shutdown immediate
2. add new entry to /etc/oratab
3. copy spfile...ora, orapw... files to new ones changing SID name
4. change environment to set new SID
   
5. startup mount

It was mounted successfully under new SID with the same database and unique names

Periodically running "show configuration verbose" command
I got the following list of messages in dgmgrl:

Warning: ORA-16610: command "Broker automatic health check" in progress
Warning: ORA-16610: command "Instance being added to database" in progress
Warning: ORA-16610: command "Broker automatic health check" in progress
Warning: ORA-16607: one or more databases have failed

Apparently, failed one is beesby:

DGMGRL> show database verbose beesby

Database
 Name:            beesby
 Role:            PHYSICAL STANDBY
 Enabled:         YES
 Intended State:  APPLY-ON
 Instance(s):
   beesby (apply instance)
   newsby

 Properties:
...
Current status for "beesby":
Error: ORA-16766: Redo Apply is stopped

As you can see there are 2 instance names appeared for the database
and even state is APPLY-ON already I enabled it but with new apply instance:

DGMGRL> EDIT DATABASE beesby SET STATE = 'APPLY-ON' WITH APPLY INSTANCE = 'newsby';
Succeeded.

Execution was successful and after warning message

Warning: ORA-16610: command "EDIT DATABASE beesby SET PROPERTY" in progress

I got final SUCCESS status for standby database and for Data Guard on the whole

DGMGRL> show database verbose beesby

Database
 Name:            beesby
 Role:            PHYSICAL STANDBY
 Enabled:         YES
 Intended State:  APPLY-ON
 Instance(s):
   beesby
   newsby (apply instance)

...
Current status for "beesby":
SUCCESS

DGMGRL> show configuration verbose

Configuration
 Name:                bee
 Enabled:             YES
 Protection Mode:     MaxPerformance
 Databases:
   bee64  - Primary database
   bee    - Physical standby database
          - Fast-Start Failover target
   beesby - Physical standby database

Fast-Start Failover: ENABLED
 Threshold:           30 seconds
 Target:              bee
 Observer:            oel1
 Lag Limit:           2000000000 seconds
 Shutdown Primary:    TRUE
 Auto-reinstate:      TRUE

Current status for "bee":
SUCCESS

As you can see the old instance name left there but it can be easily removed:

DGMGRL> REMOVE INSTANCE beesby ON DATABASE beesby;
Removed instance "beesby" from database "beesby"

Now the instance name gone and configuration is in SUCCESS status

DGMGRL> show database verbose beesby

Database
 Name:            beesby
 Role:            PHYSICAL STANDBY
 Enabled:         YES
 Intended State:  APPLY-ON
 Instance(s):
   newsby

 Properties:
...
Current status for "beesby":
SUCCESS

Have a good day!

Fast-Start Failover on Oracle Error Conditions

Using Oracle 11g Data Guard configuration you can initiate Fast-Start Failover
based on different Oracle errors - Configure Conditions for Fast-start Failover

I decided to test it and created DG configuration using not only documentation
but running "ENABLE FAST_START FAILOVER" command, getting errors :) ,
looking into drc...log files (it is 11g, they are under trace directory),
correcting problems like: standby redo log files, flashback mode, increasing
Fast-Start Failover Lag Limit and finally got SUCCESS status for
"SHOW CONFIGURATION VERBOSE":

DGMGRL> show configuration verbose

Configuration
  Name:                bee
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    bee    - Primary database
    beesby - Physical standby database
           - Fast-Start Failover target

Fast-Start Failover: ENABLED
  Threshold:           30 seconds
  Target:              beesby
  Observer:            oel1.pythian
  Lag Limit:           30 seconds
  Shutdown Primary:    TRUE
  Auto-reinstate:      TRUE

Current status for "bee":
SUCCESS

To see conditions which are configured to initiate FSF you need to execute
another command (I usually run help command and do copy/paste
of commands that I need):

DGMGRL> SHOW FAST_START FAILOVER;

Fast-Start Failover: ENABLED
  Threshold:           30 seconds
  Target:              beesby
  Observer:            oel1.pythian
  Lag Limit:           30 seconds
  Shutdown Primary:    TRUE
  Auto-reinstate:      TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    (none)

There were predefined logically named health conditions for Fast-Start Failover
and no Oracle Error Conditions.

Using help command and provided syntax I set FSF for ORA-00001 error (!)
that means failover should be initiated after violation of unique constraint
(That's terrific condition!)

DGMGRL> ENABLE FAST_START FAILOVER CONDITION 1;
 Succeeded.

alert.log also mentioned it but in decoded manner:

OS Pid: 2964 executed alter system set events '1 trace name failover level 1'

where PID=2964 belonged to DMON process. The error now is on conditions list:

DGMGRL> SHOW FAST_START FAILOVER;

Fast-Start Failover: ENABLED
  Threshold:           30 seconds
  Target:              beesby
  Observer:            oel1.pythian
  Lag Limit:           30 seconds
  Shutdown Primary:    TRUE
  Auto-reinstate:      TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    ORA-00001: unique constraint (%s.%s) violated

The only thing left wass to raise the error - I created the table with PK and tried to insert
duplicated value, got the error:

SQL> insert into t2 values(1);
insert into t2 values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (AG.SYS_C009503) violated

And failover was started:
alert.log on primary

...
OS Pid: 2964 executed alter system set events '1 trace name failover level 1'
Wed May 27 19:30:58 2009
Foreground: user-configured ORA-1 requesting FSFO
Wed May 27 19:31:03 2009
A user-configurable Fast-Start Failover condition was detected. The primary is shutting down due to ORA-00001: unique constraint (%s.%s) violated.
Database bee will not be automatically reinstated.
USER (ospid: 3587): terminating the instance
Instance terminated by USER, pid = 3587
...

alert.log on standby

...
Attempting Fast-Start Failover due to detection of condition ORA-00001: unique constraint (%s.%s) violated.
Database bee will not be automatically reinstated.
...
Failover succeeded. Primary database is now beesby.
...

observer output

...
19:31:05.81  Wednesday, May 27, 2009
Initiating Fast-Start Failover to database "beesby"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "beesby"
19:31:19.15  Wednesday, May 27, 2009
...

Wonderful, failover was done and now I had to reinstate the standby

And that was completed but through several steps (I did not use static configuration of global database name
in listener - that might was the cause of failure to accomplish it at once - I need to check it):

1. startup new standby database in mount mode (bee)
2. REINSTATE DATABASE bee (from dgmgrl connected as sys/sys from beesby environment)
3. startup standby database in mount mode (bee)
4. REINSTATE DATABASE bee (from dgmgrl connected as sys/sys from beesby environment)

=== 1 ===
SQL> startup mount
 ...
Database mounted.

=== 2 ===
 [oracle@oel1 beesby ~]$ dgmgrl sys/sys
DGMGRL for Linux: Version 11.1.0.7.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> REINSTATE DATABASE bee
 Reinstating database "bee", please wait...
Operation requires shutdown of instance "bee" on database "bee"
Shutting down instance "bee"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "bee" on database "bee"
Starting instance "bee"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "bee"
You must start instance "bee" manually
Reinstatement of database "bee" failed
DGMGRL> exit

=== 3 ===
 SQL> startup mount
 ...
Database mounted.

===4 ===
 [oracle@oel1 beesby ~]$ dgmgrl sys/sys
DGMGRL for Linux: Version 11.1.0.7.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> REINSTATE DATABASE bee
 Reinstating database "bee", please wait...
Reinstatement of database "bee" succeeded


DGMGRL> show configuration verbose

Configuration
  Name:                bee
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    beesby - Primary database
    bee    - Physical standby database
           - Fast-Start Failover target

Fast-Start Failover: ENABLED
  Threshold:           30 seconds
  Target:              bee
  Observer:            oel1.pythian
  Lag Limit:           30 seconds
  Shutdown Primary:    TRUE
  Auto-reinstate:      TRUE

Current status for "bee":
SUCCESS

Have a good day!

TNS names parsing in Data Guard configurations

Building DG configurations for Oracle 10g and 11g databases
I noticed that 11g does not do transformation of tns name
provided at stage of DG creation with regard to Oracle 10g

That might be useful if you need to tune some parameters
of SQL*Net - SDU, RECV_BUF_SIZE, SEND_BUF_SIZE

But some problems can be hidden if full string of connection
is not shown but dr....log can shed some light on them.


Have a good day!

Top 10 topics for Data Guard on Metalink by usage

Metalink Knowledge Base Data Guard top 10 notes by usage

1. 10.2 Data Guard Physical Standby Switchover(751600.1)
   
2. Data Guard Support for Heterogeneous Primary and Standby Systems in Same Data Guard Configuration (413484.1)
   
3. Applying Patchset with a 10g Physical Standby in Place (278641.1)
   
4. The Gains and Pains of Nologging Operations (290161.1)
   
5. Steps To Create Physical Standby Database (736863.1)
   
6. Oracle10g: Data Guard Switchover and Failover Best Practices (387266.1)
   
7. MAA - Creating a RAC Physical Standby for a RAC Primary (380449.1)
   
8. Script to Collect Data Guard Primary Site Diagnostic Information (241374.1)
   
9. MAA - Data Guard Redo Transport and Network Best Practices (387174.1)
   
10. Creating a 10gr2 Data Guard Physical Standby database with Real-Time apply (343424.1)
    

As you can see the 2nd place is for Heterogeneous standbies and also MAA
best practices are definitely on the edge of usage.
I think it shows that standby and Data Guard configuration are used not only to
increase availability of systems but also to migrate from one platform to another
as for example it is possible to migrate from Windows 32-bit to Linux 64-bit
with Oracle 11g and minimal downtime.

Have a good day!

silent installation of agent 10.2.0.5

I recently installed agents 10.2.0.5 on SunOS (SPARC) 5.9 and Linux x86-64
silently and noted the same problem during secure command - the screen
had being erased if password was not sent as a parameter
in emctl secure agent command.

If a password is there - everything is fine, agent was secured (there is less
information about completion in output for that version of agent)

Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
Agent is already stopped...   Done.
Securing agent...   Started.
Securing agent...   Successful.

But I have to mention that I spent several hours trying
to install agent silently (on Solaris 9) and it was failing:

./runInstaller -silent -responseFile /downloads/agent10.2.0.5/solaris/response/additional_agent.rsp \
FROM_LOCATION="/downloads/agent10.2.0.5/solaris/agent/stage/products.xml" \
BASE_DIR="/opt/app/oracle/agent10g" \
SHOW_ROOTSH_CONFIRMATION=true \
b_doAgentConfig=false \
SHOW_ROOTSH_CONFIRMATION=true \
b_doAgentConfig=false \
METALINK_USERNAME=""
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be 5.8, 5.9 or 5.10.    Actual 5.9
                                     Passed

Checking Temp space: must be greater than 250 MB.   Actual 14145 MB    Passed
Checking swap space: must be greater than 500 MB.   Actual 16220 MB    Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer
from /tmp/OraInstall2009-04-30_01-11-35AM.
Please wait ...
Please specify a Unique Oracle Base directory, 
or remove previously installed products and their directories. 
Directory name should contain only valid alphanumeric characters, 
including '_', or '.' characters.

But the directory /opt/app/oracle/agent10g did not even exist there
and there was no entry about that oracle home neither in inventory.xml
nor in oratab

Resolution was simple - I created a copy of original response file,
removed comments and empty rows, changed parameters accordingly
the environment and it worked!. The installation had been completed,
root.sh had been executed(new requirement came with 10.2.0.5)
and everything was completed.

/tmp/additional_agent.rsp

RESPONSEFILE_VERSION=2.2.1.0.0
UNIX_GROUP_NAME="oinstall"
FROM_LOCATION="../stage/products.xml"
BASEDIR="/opt/app/oracle"
b_upgrade=false
SHOW_WELCOME_PAGE=false
SHOW_NODE_SELECTION_PAGE=false
SHOW_CUSTOM_TREE_PAGE=false
SHOW_COMPONENT_LOCATIONS_PAGE=false
SHOW_SUMMARY_PAGE=false
SHOW_INSTALL_PROGRESS_PAGE=false
SHOW_REQUIRED_CONFIG_TOOL_PAGE=false
SHOW_CONFIG_TOOL_PAGE=false
SHOW_RELEASE_NOTES=false
SHOW_ROOTSH_CONFIRMATION=false
SHOW_END_SESSION_PAGE=false
SHOW_EXIT_CONFIRMATION=false
NEXT_SESSION=false
NEXT_SESSION_ON_FAIL=false
DEINSTALL_LIST={"oracle.sysman.top.agent","10.2.0.5.0"}
SHOW_DEINSTALL_CONFIRMATION=false
SHOW_DEINSTALL_PROGRESS=false
b_silentInstall=true
b_doAgentConfig=false
SHOW_XML_PREREQ_PAGE=false
SHOW_END_OF_INSTALL_MSGS=false
ACCEPT_LICENSE_AGREEMENT=true
TOPLEVEL_COMPONENT={"oracle.sysman.top.agent","10.2.0.5.0"}
SHOW_SPLASH_SCREEN=false
SELECTED_LANGUAGES={"en"}
COMPONENT_LANGUAGES={"en"}
DECLINE_SECURITY_UPDATES=true
METALINK_USERNAME=""
sl_OMSConnectInfo={"grid","4889"}

./runInstaller -silent -responseFile /tmp/additional_agent.rsp

Worth to mention that port should be http for OMS connection
even if OMS is secured (1159 by default)

I hope your installation will go smooth and quick

Have a good day!

Heterogenous standby Windows 32-bit -> Linux 32-bit in Oracle 11g

Based on Metalink Note 413484.1 physical Standby configuration
is supported for mixed platforms.

The configuration that attracted my attention is cascaded physical standby that
can be configured between Windows 32-bit -> Linux 32-bit and Linux 64-bit.

It can be considered as minimal downtime migration from Windows to Linux
using switchover operations.

To setup configuration I started with standby for Windows 32 and Linux 32
and created primary database 11.1.0.7 on Windows and
installed APEX 3.2 there as well as put the same version of RDBMS at Linux side.

To duplicate the database RMAN have to be used but because of differences
between windows and linux like paths "duplicate" command can't be used.
So I took full backup of the database including backup of current
controlfile for standby and copied it over to standby server.

backup as compressed backupset tag 'FOR_STBY' (database)
(current controlfile for standby);

I took the following steps to restore database at standby:
startup nomount;

restore standby controlfile from '/u02/fra/O1_MF_NCNNF_FOR_STBY_4Z0O8NPK_.BKP';

mount database;

catalog start with '/u02/fra';

run {
set newname for datafile 1 to new;
set newname for datafile 2 to new;
set newname for datafile 3 to new;
set newname for datafile 4 to new;
set newname for datafile 5 to new;
set newname for datafile 6 to new;
set newname for tempfile 1 to new;
restore database;
switch datafile all;
switch tempfile all;
}

I started DG broker at primary and standby databases,
added standby log files on standby and created DG configuration:

create configuration dev_dg as primary database is dev
connect identifier is '//192.168.1.3/dev';

add database dev_srv2 as connect identifier
is '//192.168.1.7/dev_srv2' maintained as physical;

enable configuration;

The configuration showed SUCCESS but I checked log files and see
that there are windows style files left in controlfile.

SQL> select member from v$logfile;

MEMBER
-----------------------------------------------
D:\APP\GORYUNOV\ORADATA\DEV\REDO03.LOG
D:\APP\GORYUNOV\ORADATA\DEV\REDO02.LOG
D:\APP\GORYUNOV\ORADATA\DEV\REDO01.LOG
/u02/oradata/DEV_SRV2/onlinelog/o1_mf_5_4z0sofxh_.log
/u02/fra/DEV_SRV2/onlinelog/o1_mf_5_4z0sogml_.log
/u02/oradata/DEV_SRV2/onlinelog/o1_mf_6_4z0soyrl_.log
/u02/fra/DEV_SRV2/onlinelog/o1_mf_6_4z0sozjh_.log

I was able to drop only one group (after cancelling recovery
and changing standby_file_management to manual), others
were not dropped since they needed for crash recovery

When all preparations were done (IP addresses and name to hosts files,
addition of dev_DGMGRL, dev_srv2_DGMGRL service names to listener.ora)
I executed switchover to standby database on Linux
and it was completed successfully:

DGMGRL> switchover to dev_srv2;
Performing switchover NOW, please wait...
New primary database "dev_srv2" is opening...
Operation requires shutdown of instance "dev" on database "dev"
Shutting down instance "dev"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "dev" on database "dev"
Starting instance "dev"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "dev_srv2"

Check of log files in new primary dev_srv2 showed new names:

SQL> select member from v$logfile;
MEMBER
---------------------------------------------------
/u01/app/oracle/product/11.1.0/db_1/dbs/D:APPGORYUNOVORADATADEVREDO03.LOG
/u01/app/oracle/product/11.1.0/db_1/dbs/D:APPGORYUNOVORADATADEVREDO02.LOG
/u02/oradata/DEV_SRV2/onlinelog/o1_mf_5_4z0sofxh_.log
/u02/fra/DEV_SRV2/onlinelog/o1_mf_5_4z0sogml_.log
/u02/oradata/DEV_SRV2/onlinelog/o1_mf_6_4z0soyrl_.log
/u02/fra/DEV_SRV2/onlinelog/o1_mf_6_4z0sozjh_.log

but I easily added new OMF logfiles and dropped files from dbs directory.


After switchover I was able to connect to APEX through standby IP address

And now I need switchover it back and create standby database on
Linux 64-bit
(to be continued...)


Have a good day!

LargeRepository option in EM Grid Control 10.2.0.5

If you have lots of targets, many jobs, getting plenty of alerts in
Enterprise Manager Grid Control you possibly already set
LargeRepository to true in emoms.properties
to speed up navigation of Console.

And of course you already downloaded and upgraded it to 10.2.0.5
which is available for Linux 64-bit as well.

The upgrade and configuration of repository would go well
but you get error 404 after login to Grid Control Console

404 Not Found
Resource /em/home.jsp/page/internalError not found on this server

There is note on Metalink that mentions that problem and
workaround is very simple and straightforward - set LargeRepository to false.

Known Issues during 10.2.0.5 Upgrade

But does everybody want to change the parameter back to false?...
I hope it will be fixed soon.

Have a good day!

silent installation of Enterprise Manager 10g Grid Control Release 5

The Enterprise Manager 10g Grid Control Release 5 for Linux x86-64 is out
on OTN and also there is patch available on Metalink

I already installed 10.2.0.3 (full release) silently and wanted to upgrade
using the same way.

There are steps clearly described in documentation how to upgrade
existing version together with post-installation actions:
Installing Enterprise Manager 10g Grid Control Release 5 (10.2.0.5) Using Existing Database

So I started:
./runInstaller -noconfig -silent \
-responseFile /opt/oracle/install/gc/10205/3731593/Disk1/response/patchset.rsp \
b_softwareonly=true s_sysPassword="sys24" \
ORACLE_HOME="/home/oracle/grid/oms10g" \
oracle.iappserver.st_midtier:szl_InstanceInformation={"rep24"}

It went ok without errors and asked to execute root.sh at the end

After that following further configuration procedures
I set PERL5LIB and started upgrade of OMS repository:
export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1

$ORACLE_HOME/perl/bin/perl /home/oracle/grid/oms10g/sysman/install/ConfigureGC.pl /home/oracle/grid

It bounced database to remove running jobs (even I did pre-installation steps
for it mentioned in Oracle Enterprise Manager
Grid Control ReadMe for Linux x86-64
10g Release 5

and finally executed RepManager with -action upgrade ... options
spooling output to $OMS_HOME/sysman/log/emrepmgr.log.10.2.0.5.0

When the script had been finished I was able to start oms and it was
showing the newest verison.

Another step was an upgrade of the agent running 10.2.0.4 version

The same installation can be used for agent upgrade but
with ORACLE_HOME related to agent home:
./runInstaller -noconfig -silent \
-responseFile /opt/oracle/install/gc/10205/3731593/Disk1/response/patchset.rsp \
b_softwareonly=true ORACLE_HOME="/home/oracle/grid/agent10g"

And that upgrade went smoothly asking to run root.sh at the end.


I started agent but it could not upload files due to errors in XML files.
So I completely cleaned files there, secured the agent and tried to start
it again but it failed with errors in emagent.trc

ERROR pingManager: Did not receive valid response to ping "ERROR-Agent is blocked. Blocked reason is: Agent is out-of-sync with repository. This most likely means that the agent was reinstalled or recovered. Please contact an EM administrator to unblock the agent by performing an agent resync from the console. Please contact EM adminstrator to unblock the agent"

There is one note on Metalink exactly related to the issue
and method to fix it is to run Synchronization from Grid Control Console
Upload Agent Failing With Error
"OMS Version Not Checked Yet" ,
After Upgrading Agent to 10.2.0.5.0

Submit of synchronization button failed with another error:

Agent Operation completed with errors. For those targets that could not be saved, please go to the target's monitoring configuration page to save them. All other targets have been saved successfully. Agent has not been unblocked. Error saving target EnterpriseManager0.rac2.home_Web Cache:oracle_webcache - Skipping target {EnterpriseManager0.rac2.home_Web Cache, oracle_webcache}: Missing properties - authpwd, authuser

I manually unblocked the agent in there and it started to upload files to OMS
without errors.


Have a good day!

silent installation of 11.1.0.7 on Linux

There are Oracle installations that simplier to do in command line
and one of them is patchset installation.

To start it I used ORACLE_HOME and ORACLE_HOME_NAME
in addition to usual -silent -responseFile options

./runInstaller -silent \
-responseFile /u02/install/patch/Disk1/response/patchset.rsp \
ORACLE_HOME="/u03/app/oracle/product/11.1.0/db_1" \
ORACLE_HOME_NAME="OraDb11g_home1"


But the execution failed with error:

SEVERE:Values for the following variables could not be obtained
from the command line or response file(s):
  METALINK_USERNAME(MetaLinkUsername)
Silent install cannot continue

Well, if it is not provided why not to use just null value. For some reason
installer did not want to do it and I added METALINK_USERNAME
(together with METALINK_PASSWORD) defined to empty string:

/u02/install/patch/Disk1> ./runInstaller -silent \
-responseFile /u02/install/patch/Disk1/response/patchset.rsp \
ORACLE_HOME="/u03/app/oracle/product/11.1.0/db_1" \
ORACLE_HOME_NAME="OraDb11g_home1" \
METALINK_USERNAME="" METALINK_PASSWORD=""


And the installation went further but stopped asking me:

You may not have enough space on your drive for a successful install.
Do you still want to continue?

Funny but I tried to answer "y", "Y", "YES" but it was failing with
"-bash: n: command not found". And then I checked the log
of installation: "INFO: Installation cannot continue. To Ignore Disk Warning, please use "-ignoreDiskWarning" command line option" was there.

I added -ignoreDiskWarning and was able to install patchset without problems
and questions

./runInstaller -silent\
 -responseFile /u02/install/patch/Disk1/response/patchset.rsp \
ORACLE_HOME="/u03/app/oracle/product/11.1.0/db_1" \
ORACLE_HOME_NAME="OraDb11g_home1" \
METALINK_USERNAME="" METALINK_PASSWORD="" \
-ignoreDiskWarning

Another thing that is worth to mention that root.sh script generated
during silent installation also has parameter not to show output:

grep -i silent /u03/app/oracle/product/11.1.0/db_1/root.sh
OUI_SILENT=true
...


With TRUE value it won't show anything on a screen during execution
but change to FALSE will provide the output as usual.


Have a good day!

silent installation of Enterprise Manager Grid Control 10.2.0.3 on Linux x86-64

The first full release of Grid Control 10g rel. 2 for Linux x86-64 is 10.2.0.3
To be prepared for future release 10.2.0.5 (I hope it will come soon) on x86-64
I have decided to try silent installation of Grid Control using existing database.

A database had been created, up and running together with listener on 1521 port.

Based on contents of the response file I added variables to start silent installation

/u02/install/gc/Disk1> ./runInstaller -silent \
-responseFile /u02/install/gc/Disk1/response/em_using_existing_db.rsp \
BASEDIR="/u02/grid" INSTALLATION_NAME="EMHome_1" \
s_reposHost="rac2" \
s_reposPort="1521" \
s_reposSID="repdb" \
s_reposDBAPwd="sys24" \
s_mgmtTbsName="/u01/oradata/repdb/mgmt.dbf" \
s_ecmTbsName="/u01/oradata/repdb/mgmt_ecm_depot1.dbf" \
s_reposPwd="man24" \
s_reposPwdConfirm="man24" \
s_securePassword="man24" \
s_securePasswordConfirm="man24" \
-ignoresysprereqs use_prereq_checker=false

Later it failed with:

SEVERE:OUI-10029:You have specified a non-empty directory
to install this product. It is recommended to specify either
an empty or a non-existent directory. You may, however, choose
to ignore this message if the directory contains Operating System
generated files or subdirectories like lost+found.

But why it happened, the directory defined under BASEDIR did not even exist!

Checking log of installation I found that home for OMS
was not defined like I wanted:

INFO: Setting variable 'ORACLE_HOME'
to '/u01/app/oracle/product/10.2.0/db_1/OracleHomes/oms10g'.
Received the value from the command line.

Ok, I defined ORACLE_HOME, ORACLE_HOME_NAME
pointing to the same location where I would like to install oms
and reran the installation again:

/u02/install/gc/Disk1> ./runInstaller -silent \
-responseFile /u02/install/gc/Disk1/response/em_using_existing_db.rsp \
ORACLE_HOME="/u02/grid/oms10g" ORACLE_HOME_NAME="oms10g" \
s_reposHost="rac2" \
s_reposPort="1521" \
s_reposSID="repdb" \
s_reposDBAPwd="sys24" \
s_mgmtTbsName="/u01/oradata/repdb/mgmt.dbf" \
s_ecmTbsName="/u01/oradata/repdb/mgmt_ecm_depot1.dbf" \
s_reposPwd="man24" \
s_reposPwdConfirm="man24" \
s_securePassword="man24" \
s_securePasswordConfirm="man24" \
-ignoresysprereqs use_prereq_checker=false

But when it started I noted that OMS and agent homes were set not
to locations that I supposed to see:

OMS Home    : /u01/app/oracle/product/10.2.0/db_1/OracleHomes/oms10g
                         Space Required: 2.4GB
AGENT Home: /u01/app/oracle/product/10.2.0/db_1/OracleHomes/agent10g
                         Space Required: 0.5GB

So I cancelled the installation and checked log file:

INFO: Setting variable 'ORACLE_HOME' to '/u02/grid/oms10g'.
Received the value from the command line.
INFO: Setting variable 'ORACLE_HOME_NAME' to 'oms10g'.
Received the value from the command line.
INFO: Setting variable 's_omsHomeName' to 'oms10g'.
Received the value from the command line.
INFO: Setting variable 's_omsHome'
to '/u01/app/oracle/product/10.2.0/db_1/OracleHomes/oms10g'.
Received the value from the command line.

Even with sets of ORACLE_HOME and ORACLE_HOME_NAME
oms and agent homes were set to some default location
using the same ORACLE_HOME as for repdb database.

Detailed analysis of the log file showed existence of variables:
s_omsHome, s_omsHomeName, s_agentHome, s_agentHomeName
I removed ORACLE_HOME and added variables defined above:

/u02/install/gc/Disk1> ./runInstaller -silent \
-responseFile /u02/install/gc/Disk1/response/em_using_existing_db.rsp \
s_omsHome="/u02/grid/oms10g" s_omsHomeName="oms10g" \
s_agentHome="/u02/grid/agent10g" s_agentHomeName="agent10g" \
s_reposHost="rac2" \
s_reposPort="1521" \
s_reposSID="repdb" \
s_reposDBAPwd="sys24" \
s_mgmtTbsName="/u01/oradata/repdb/mgmt.dbf" \
s_ecmTbsName="/u01/oradata/repdb/mgmt_ecm_depot1.dbf" \
s_reposPwd="man24" \
s_reposPwdConfirm="man24" \
s_securePassword="man24" \
s_securePasswordConfirm="man24" \
-ignoresysprereqs use_prereq_checker=false

After start and during the checks installer asked me about the space:

You may not have enough space on your drive for a successful install.
Do you still want to continue?

I canceled it again and found that ORACLE_HOME was pointed to the
home of RDBMS of the database that I used for repository:

Global Settings
    Source: /u02/install/gc/Disk1/oms/Disk1/stage/products.xml
    Oracle Home: /u01/app/oracle/product/10.2.0/db_1/OracleHomes/oms10g (oms10g)
    Installation Type: Core

Ok, there is nothing difficult to add ORACLE_HOME as well,
started installation again:

/u02/install/gc/Disk1> ./runInstaller -silent \
-responseFile /u02/install/gc/Disk1/response/em_using_existing_db.rsp \
ORACLE_HOME="/u02/grid/oms10g" ORACLE_HOME_NAME="oms10g" \
s_omsHome="/u02/grid/oms10g" s_omsHomeName="oms10g" \
s_agentHome="/u02/grid/agent10g" s_agentHomeName="agent10g" \
s_reposHost="rac2" \
s_reposPort="1521" \
s_reposSID="repdb" \
s_reposDBAPwd="sys24" \
s_mgmtTbsName="/u01/oradata/repdb/mgmt.dbf" \
s_ecmTbsName="/u01/oradata/repdb/mgmt_ecm_depot1.dbf" \
s_reposPwd="man24" \
s_reposPwdConfirm="man24" \
s_securePassword="man24" \
s_securePasswordConfirm="man24" \
-ignoresysprereqs use_prereq_checker=false

Everything was going ok but failed at "OMS configuration" step.
The main log did not show much information but since it failed
during execution of configuration tools I checked log files
in the directory where they are kept - $OMS_HOME/cfgtoollogs/cfgfw

There were messages about errors during connection
to the respository database:

WARNING: oracle.sysman.top.oms:getConnection::
Connection refused:rac2::repdb:Exception=Io exception:
Invalid number format for port number
INFO: oracle.sysman.top.oms:
Failed to connect to Database for s_SysUser=SYS,
s_encrSysPwd=******, connectString=rac2::repdb,
using default for node=rac2,sid=repdb.
ConnectException=Io exception:
Invalid number format for port number
INFO: oracle.sysman.top.oms:fixNodeNames:
The repository host name specified contains invalid format
for domain extention=rac2:Fixin is not done!
...
Could not connect to SYS/(DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=rac2)(PORT=)))
(CONNECT_DATA=(SID=repdb))):
ORA-12533: TNS:illegal ADDRESS parameters
(DBD ERROR: OCIServerAttach)
...
INFO: oracle.sysman.top.oms:RepManager Create Repository Error = 50.
Please check the log file at /u02/grid/oms10g/sysman/log/
emca_repos_create11_45_57.log

"Invalid number format for port number" why is that?
The port had been defined so why it happened?

Search on Metalink revealed the note about the similar problem
Oms Config Failed with Error: Invalid number format for port number
and the workaround is simple - to remove s_reposPort from
command line and put it to the response file.

That was done (copied original file under /tmp and changed
s_reposPort to 1521) and I started installation again
(removed entries from inventory.xml and oratab,
deleted installed directories from /u02/grid):

/u02/install/gc/Disk1> ./runInstaller -silent \
-responseFile /tmp/em_using_existing_db.rsp \
ORACLE_HOME="/u02/grid/oms10g" ORACLE_HOME_NAME="oms10g" \
s_omsHome="/u02/grid/oms10g" s_omsHomeName="oms10g" \
s_agentHome="/u02/grid/agent10g" s_agentHomeName="agent10g" \
s_reposHost="rac2" \
s_reposSID="repdb" \
s_reposDBAPwd="sys24" \
s_mgmtTbsName="/u01/oradata/repdb/mgmt.dbf" \
s_ecmTbsName="/u01/oradata/repdb/mgmt_ecm_depot1.dbf" \
s_reposPwd="man24" \
s_reposPwdConfirm="man24" \
s_securePassword="man24" \
s_securePasswordConfirm="man24" \
-ignoresysprereqs use_prereq_checker=false

Now it was executed without problems, all assistances went through
and installation had been finished. I was able to connect to
Grid Control Console.

A couple of things to mention:
* 10.2.0.4 database was used for repository
but certified is 10.2.0.3 for Linux x86-64
* Linux release is CentOS 5 64-bit that's why
-ignoresysprereqs use_prereq_checker=false were used
* Got "Create Repository Error = 14" and "Create Repository Error = 12"
when not all objects of repository were cleaned from previous
installations.
To delete them:
declare
begin
for cr in (select * from dba_synonyms where table_owner = 'SYSMAN') loop
execute immediate 'drop public synonym '||cr.synonym_name;
end loop;
end;
/

drop user sysman cascade;

drop user mgmt_view cascade;

drop role MGMT_USER;

drop tablespace MGMT_TABLESPACE including contents and datafiles;

drop tablespace MGMT_ECM_DEPOT_TS including contents and datafiles;




I hope your silent installation will be successful!

Have a good day!

ORA-16401: archivelog rejected by RFS

How big is your list of "known" issues for systems that you support?

How many of them are related to bugs in Oracle
and what portion is the "expected" behaviour that covered
by existing technical/business requirements and processes?

How often do you revise the list to check of the "known" issues
can be fixed and removed from the "list"?

Every time when new job is taken first months are spent just to get
understanding what are the "known" issues/problems, how to react to them,
whom to call, what to write, etc. And since you are the new one here
you would like to show how cool and smart you are, you are trying to
fix the issues suggesting different approaches, methods. And when
time comes to do changes - you stuck with existing environment
and your strength is going away little by little and your list of "known" issues
is growing as well :(

How is the said before related to the error that is in the subject.
Well based on a description it can be ignored and there is nothing bad
to add it to ignore list or to the list of "known" issues.
Will be anything missed and hidden under that error? Who knows :)

In this case the error had periodic occurrence and after some checking
on primary side I found out that crontab job was doing backup of archive
log files at the same time time every hour and archive_lag_target was set
to 1800 (30 mins). So every second log file switch initiated by the parameter
coincided with switch initiated by RMAN backup command.

Difference of several seconds in log switches was too much for RFS to handle
quickly and was reported in alert log.

Tue Feb 24 00:05:02 2009
Primary database is in MAXIMUM PERFORMANCE mode
kcrrvslf: active RFS archival for log 1 thread 1 sequence 1117
RFS[12]: Successfully opened standby log 5: '+DATA/test/onlinelog/group_5.272.677123691'
Tue Feb 24 00:05:02 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[21]: Assigned to RFS process 27094
RFS[21]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
kcrrvslf: active RFS archival for log 2 thread 1 sequence 1118
RFS[12]: Successfully opened standby log 4: '+DATA/test/onlinelog/group_4.273.677123691'
Errors in file /u01/app/oracle/diag/rdbms/testb/TEST/trace/TEST_rfs_27094.trc:
ORA-16401: archivelog rejected by RFS


Finally I changed archive_lag_target to 2400 (40 mins)
and it helped to avoid consecutive log switches within seconds.

No errors so far in alert log and I believe that "known" issue
will be removed from the list soon.

Have a good day!

oracle recommended bundle patches

Every time when new oracle home is installed there is a wish
to apply latest patches and also some other patches to avoid
as much as possible problems/errors in the future.
I was installing new 10.2.0.4 oracle home recently and while
checking Metalink I found "bundle patches" for CRS, RAC
and for a database in general:

10.2.0.4 RAC RECOMMENDED BUNDLE #2
10..2.0.4 CRS BUNDLE2 TRACKING BUG
10.2.0.4 GENERIC RECOMMENDED BUNDLE# 3.

It is also worth doing to check bugs that appeared after the patchset
as for 10.2.0.4

10.2.0.4 Patch Set - Availability and Known Issues


Have a good day!

splitting table partitions with BLOB in streams

The last hours of the last day of the last year I spent trying to get
capture process of streams working.

Developers executed the script that prepared application tables
for new year splitting and rebuilding indexes on them.

After that capture process was aborted due to
ORA-04031 for streams pool.

The setting for stream_pool_size was 384M only but increase of
the value up to 1, 2 even 5Gb did not help either.

Capture was failing with the errors:

ORA-04031: unable to allocate ... bytes of shared memory ("streams pool","unknown object","streams pool","kol raw")
ORA-04031: unable to allocate ... bytes of shared memory ("streams pool","unknown object","streams pool","sob_kgqmrec")
ORA-04031: unable to allocate ... bytes of shared memory ("streams pool","unknown object","streams pool","kolccst obj")
ORA-04031: unable to allocate ... bytes of shared memory ("streams pool","unknown object","streams pool","sob_kgqmrec")


After research on Metalink one interesting note had been found:

STREAMS CAPTURE ERRORS OUT WITH ORA-4031 ON SPLIT PARTITION COMMAND

which exactly describes the problem that we have.
But they were able to go through all transactions for capture process
only when streams_pool_size was increased up to 16Gb!
Unfortunately there is no chance for now to allocate
so much memory for streams and there is no patch for it yet.

Might be upgrade (from 10.2.0.4) to 11g will help,
might be additional memory will be found
or Metalink will come with some ideas, will see...

Have a good day!