jump to navigation

ORA-16072: a minimum of one standby database destination is required 27 August 2011

Posted by David Alejo Marcos in ASM, Exadata, Oracle 11.2, RAC, RMAN.
Tags: , , , , , , ,
2 comments

This is a quick post regarding the error on the subject. This is the second time it happens to me, so I thought I will write a bit about it.

The problem:

I am refreshing one of my UAT environments (happens to be a Full Rack Exadata) using Oracle RMAN duplicate command. Then the following happens (on both occasions).

1.- Duplicate command fails (lack of space for restoring archivelogs, or any other error). This is can be fixed quite easy.

2.- following error while trying to open the database after restore and recover has finished:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 13710
Session ID: 1250 Serial number: 5
SQL> exit

On the alert.log file we can read the following:

Wed Aug 24 13:32:48 2011
alter database open
Wed Aug 24 13:32:49 2011
LGWR: STARTING ARCH PROCESSES
Wed Aug 24 13:32:49 2011
ARC0 started with pid=49, OS id=13950
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
<strong>LGWR: Primary database is in MAXIMUM AVAILABILITY mode</strong>
<strong>LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR</strong>
<strong>LGWR: Minimum of 1 LGWR standby database required</strong>
Errors in file /apps/oracle/server/diag/rdbms/xxxx04/xxxx041/trace/xxxx041_lgwr_13465.trc:
<strong>ORA-16072: a minimum of one standby database destination is required</strong>
Errors in file /apps/oracle/server/diag/rdbms/xxxx04/xxxx041/trace/xxxx041_lgwr_13465.trc:
ORA-16072: a minimum of one standby database destination is required
LGWR (ospid: 13465): terminating the instance due to error 16072
Wed Aug 24 13:32:50 2011
ARC1 started with pid=48, OS id=13952
Wed Aug 24 13:32:50 2011
System state dump is made for local instance
System State dumped to trace file /apps/oracle/server/diag/rdbms/xxxx04/xxxx041/trace/xxxx041_diag_13137.trc
Trace dumping is performing id=[cdmp_20110824133250]
Instance terminated by LGWR, pid = 13465

The Solution:

Quite simple:

1.- Start up database in mount mode:

SQL> startup mount

ORACLE instance started.

Total System Global Area 1.7103E+10 bytes
Fixed Size                  2230472 bytes
Variable Size            4731176760 bytes
Database Buffers         1.2180E+10 bytes
Redo Buffers              189497344 bytes
Database mounted.

SQL> select open_mode, DATABASE_ROLE, guard_status, SWITCHOVER_STATUS from v$database;

OPEN_MODE            DATABASE_ROLE    GUARD_S SWITCHOVER_STATUS
-------------------- ---------------- ------- --------------------
MOUNTED              PRIMARY          NONE    NOT ALLOWED

2.- Execute the following command:

SQL> alter database set standby database to maximize performance;

Database altered.

SQL> select open_mode, DATABASE_ROLE, guard_status, SWITCHOVER_STATUS from v$database;

OPEN_MODE            DATABASE_ROLE    GUARD_S SWITCHOVER_STATUS
-------------------- ---------------- ------- --------------------
MOUNTED              PRIMARY          NONE    NOT ALLOWED

3.- Stop database:

SQL shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

4.- Start up database mount mode:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.7103E+10 bytes
Fixed Size                  2230472 bytes
Variable Size            4731176760 bytes
Database Buffers         1.2180E+10 bytes
Redo Buffers              189497344 bytes
Database mounted.

5.- Open database:

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
xxxx041

SQL>

As always, comments are welcome.

David Alejo-Marcos.

David Marcos Consulting Ltd.

Advertisements

ORA-01017: invalid username/password; logon denied after upgrade 11.2 30 June 2010

Posted by David Alejo Marcos in Oracle 11.2, Standby.
Tags: ,
4 comments

Over the last several weeks, I have been upgrading several database to 11.2 with and without broker.

I will write a step-by-step guide later and some of the problems I found.

The interesting thing about my last upgrade was an error (not surprise here, if all went smooth I will not have anything to write about) with the standby database.

The problem:

The error was quite self-explanatory:

ORACLE Instance ddddd - Archival Error. Archiver continuing.
Errors in file /opt/oracle/diag/rdbms/ssssss/dddddd/trace/ddddd_arc0_6420.trc:
ORA-01017: invalid username/password; logon denied
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
Errors in file /opt/oracle/diag/rdbms/ssssss/dddddd/trace/ddddd_arc0_6420.trc:
ORA-16191: Primary log shipping client not logged on standby
FAL[server, ARC0]: Error 16191 creating remote archivelog file 'delta'
FAL[server, ARC0]: FAL archive failed, see trace file.
Errors in file /opt/oracle/diag/rdbms/ssssss/dddddd/trace/ddddd_arc0_6420.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing

and the contents of /opt/oracle/diag/rdbms/ssssss/dddddd/trace/ddddd_arc0_6420.trc are:

ORA-01017: invalid username/password; logon denied
OCI_DBVER attribute value retrieval failed error=1017
*** 2010-06-26 08:51:58.413 4132 krsh.c
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
*** 2010-06-26 08:51:58.413 869 krsu.c

The solution:

The first thing I did was to check the value of “remote_log_passwordfile” parameter. The value was correct (exclusive).

The second step was trying to connect as sys from one box the other and it was also fine.

What I did next was to recreate the orapwd files on primary and standby, but with two extra options:

orapwd file=orapwXXXXX password=YYYYYY entries=NN ignorecase=y force=y

With the ignorecase, we are telling oracle to revert to the previous behavior (all capitals). Force will allow to overwrite the current passwordfile if it exists.

After stopping and starting the primary and standby databases, all started to work as normal.

I think the problem was related to some capitals on the passwordfile when it was first created, but this is just a guess.

As always, comments are welcome.

Two or more Threads on an Oracle single instance 9 March 2010

Posted by David Alejo Marcos in RAC, Tuning.
Tags: , ,
2 comments

Have you ever move from a RAC configuration to a single instance?, this is one of the scenarios were you may finish with more than one thread on a single instance database.

The problem is that having more that one thread is for RAC, not single instances and will give you problems like restoring the database or alerts if you have scripts to monitor standby databases being kept up to date.

The problem:

having two threads on a single instance.

Thread 2 and 3 where closed on our primary database:

SQL> select thread#, status, enabled, sequence#’, checkpoint_time from v$thread;

THREAD# STATUS ENABLED   SEQUENCE# CHECKPOIN
———- —— ——– ———- ———
1 OPEN    PUBLIC    26956 09-MAR-10
2 CLOSED PRIVATE       31 06-MAR-10
3 CLOSED PRIVATE      104 09-MAR-10

but as you can see on the following query, Oracle was still using one of them (thread 3 in our case):

SQL> select thread#, sequence#, status from v$log;

THREAD#  SEQUENCE# STATUS
———- ———- —————-
1    26947 INACTIVE
1    26948 INACTIVE
1    26949 CURRENT
1    26945 INACTIVE
1    26946 INACTIVE
3      100   INACTIVE
3      101 CURRENT
2        0     UNUSED
2        0     UNUSED

9 rows selected.

this was problematic once we tried to restore backups from that database, among other things.

The solution:

it is quite simple, we need to remove thread 2 and thread 3 from our database. The steps to follow are:

1.- disable unwanted threads:

ALTER DATABASE DISABLE THREAD 2;
ALTER DATABASE DISABLE THREAD 3;

2.- archive the thread that is in “CURRENT” or “INACTIVE” status from threads 2 or 3:

select group#, thread#, sequence#, status from v$log where status = ‘CURRENT’ and thread# in (2,3);

ALTER SYSTEM ARCHIVE LOG GROUP ????;

replace ???? with any group# returned.

3.- Drop logfile groups belonging to threads 2 and 3:

ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE DROP LOGFILE GROUP 7;
ALTER DATABASE DROP LOGFILE GROUP 8;
ALTER DATABASE DROP LOGFILE GROUP 9;

The reason:

The reason behind this problem may differ from system to system.

Some people have this problem because they copied the spfile (or init.ora) file from a different RAC system, and use it on a single instance database without removing RAC parameters.

Other people (like us) is because our primary database is RAC and the standby is single instance.

The init.ora parameter thread has a default value of 0 (zero) meaning that this instance will use any available, enabled public thread.

When we create a database, Oracle creates and enables thread 1 as public by default. This parameter is classified as RAC parameter, but we can use it on a single instance if we decide to use a different public thread that 1 or to fix this problem.

We did our switchover test from node 3. This node has instance 3 from our 3-node RAC running. As soon as we did the switchover to a single instance, Oracle started using thread 1 (default) and thread 3 (the same thread defined from the source database at the time of the switch).

At that time we should have followed the steps above OR modify our init.ora (or spfile) file and specify thread=1 for our single instance database.

From now on, we will add thread=1 on our single instance that have RAC as standby or any single instance with RAC as primary to avoid this problem in the future.

As always, comments are welcome.