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!