Locating data files on standby

Problem

The db_create_file_dest parameter indicates the default directory in which an Oracle RDBMS instance creates new files according to the OMF rules.
The scenario considered is this:

• we want to add new files
• despite the default location we have also additional one built upon lesser storage and designated for archive purposes
• there is also standby configured also with OMF, and file paths differ between both instances

The problem is how to place files in the archival location on standby with least effort?

Solution 1

One simply:

• adds data files on the primary with explicit specification of the file path - the files are added on primary to the right location, on the standby to the default one (instead of the archivel one)
• lists recently added files identifiers - this may be a mundane task especially if one adds data files to already existing tablespaces or is unable to provide simple condition on tablespace_names - in general this may be done on the dba_data_files or v$datafile view.
• next one creates a script with move operation
  

  -- first part is to move data files at the OS level
  select 
    'mv '||name||' [archival path]'||substr(name, instr(name, '/', -1))||';' bash1 
  from v$datafile where file# in ([ids list]);
  
  -- second part is to rename files at the db level
  select 
    'alter database rename datafile '''||name||''' to ''[archival path]'||substr(name, instr(name, '/', -1))||''';' sql1 
  from v$datafile where file# in ([ids list]);
  

  The best way of course is to format and spool the answers of those queries to scripts (or one script)
• now one have to switch off the managed standby mode (alter database recover managed standby cancel;)
• the next step is to switch standby_file_management to MANUAL (alter system set standby_file_management='MANUAL';)
• further one have to perform the core commands - run scripts generated earlier and move files to the final location and rename the paths at the database level
• the last steps are switching to AUTO mode again (alter system set standby_file_management='AUTO';) and starting the managed replication (recover managed standby database using current logfile disconnect)

Solution 2

I suppose this one is much more clever than the first. On the standby one simply changes temporarily the default OMF path to the archival location - of course there are elements we have no influence on like db_unique_name or datafile literal within the path, but these are of small importance and we may be prepared for it, if we assume earlier such scenario. Then it is enough to add data files - the standby will automatically place them into the location of choice - of course we have to be sure, no one adds other files at the moment. After the operation it is enough to set db_crate_file_dest back to previous setting.

Mysterious problem with archivelog replication

The starting points to this short story are the following:

• we have a system with active-passive configuration
• lately one of us made a failover to another node
• the configuration actually not changed and used for some time - anyway we checked it few times and there were no errors
• the standby was recreated
• local application of archivelogs on a standby was successful

The problem was the primary database did not send the archivelogs to the standby.
The entry in V$ARCHIVE_DEST_STATUS indicated wrong unique name in the configuration. Not sure if recall correctly, but I think it was ORA-16053 listed there. We checked the configuration at least few times by few pairs of eyes and not spotted anything wrong.
There was hanging one of ARCH processes on the primary, which tried to send an archivelog from before the failover incarnation, so we suspected it may be it, but killing it did not change anything.

However even though the real cause stays mysterious, the solution has been quite obvious and strightforward. A collegue of mine configured another archive destination - we disabled number 2 and enabled number 3 - the system return to work properly.

Compatibility on active-passive configuration and ORA-600

Today we experienced some small issues on one of the production environments. And btw. the following story I would like to share with.

Situation overview

The database in question works in active-passive configuration with managed standby recovery enabled. From time to time it has some problems with memory settings. As this issue repeats for some time we are already quite experienced with it and act appropriately. And because of nature of this database traffic we are able to run some experiments. And today was also such day.

Compatible parameter

Among other adjustments and settings we've changed the compatible parameter from 11.2.0.0.0 to 11.2.0.3.0. But the change was done only on the active node.

Output

The first what we've got was the ORA-600 error.

[.. from alert.log ..]
Mon May 12 08:48:10 2014
RFS[13]: Assigned to RFS process 7418
RFS[13]: Selected log 7 for thread 1 sequence 140861 dbid -1879016249 branch 793971529
Errors in file /oracle/diag/rdbms/zabbix_node2/zabbix/trace/zabbix_rfs_7412.trc  (incident=192281):
ORA-00600: internal error code, arguments: [2730], [331], [1], [5], [140862], [140862], [512], [512], [], [], [], []

[.. from one of trace files ..]
*** ACTION NAME:() 2014-05-12 08:48:09.667
 
Identified standby redo log 5 for implicit mid-log reconnect
DDE: Problem Key 'ORA 600 [2730]' was flood controlled (0x2) (incident: 192281)
ORA-00600: internal error code, arguments: [2730], [331], [1], [5], [140862], [140862], [512], [512], [], [], [], []
Exception 600 received while writing lno 5 thread 1 seq 140862
*** 2014-05-12 08:48:10.517 4638 krsb.c
krsb_stream_write: Error 600 while attempting to write buffer
krsv_dsga: Dispatching RFS shutdown notification

Of course as all the ORA-600 quite misterious.
As I was not sure at which archivelog the problem appeared and was too lazy to look through the alert log, I simply run in SQL*Plus (nota bene 5th and 6th arguments of the ORA-600 indicate the archivelog sequence on which the problem emerges, we had already few such archivelogs, so one had to find the first occurence of the problem anyway)

recover standby database until cancel

. Now things were much more clear - at one of the archivelogs I have simply got an info, that the server can not process the archivelog with compatibility set to higher than currently set. It was enough to set the compatibility accordingly and restart the standby.

Duplicating for standby

On the start I have a target database configured with OMF and specified unique db name. On the standby side I created an instance, started it and that's it. Oh, yeah - I copied password file from primary side to be able to connect to standby and configured listener with GLOBAL_DBNAME, which allows for registering a service for use in tnsnames.ora on both hosts. Of course I specified proper entries in tnsnames.ora as well.
There are possible many configurations for this duplication - combinations of a target database, a standby database and a catalog - of course a standby one is a must, but the rest is optional and may be avoided, although it complicates the matter a little. I choose to use additionally to standby database also active target database. This avoids the trouble of applying all the redo logs from last backup.

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

First failure: the command failed with ORA-17628: Oracle error 19505 returned by remote Oracle server on primary and ORA-19505: failed to identify file "/oracle/oradata/ZABBIX/controlfile/o1_mf_855ys7j8_.ctl"
ORA-27040: file create error, unable to create file as I did not create path for controlfile (so now mkdir -p /oracle/oradata/ZABBIX/controlfile/o1_mf_855ys7j8_.ctl).

Second start and failure:

RMAN-03002: failure of Duplicate Db command at 11/13/2012 10:14:19
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-06550: line 1, column 17:
PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared
ORA-06550: line 1, column 7:

I found at http://blog.csdn.net/tianlesoftware/article/details/6232292 that it is due to not specifying nocatalog when connecting with rman. And that really helped.
Third try and this time I moved much ahead - I was wrong with assuming the online logs will be created according to the db_create_file_dest (so I should set db_create_online_log_dest_n at least to overcome this). However this time duplication is going forward - the data files are created according to the plan.
Because copy is done with backup as copy reuse datafile X auxiliary format new then no useless copying to storage local to primary and all bytes goes directly to the standby files, which is nice

To finish the standby I need now to add standby log files (though this is optional) and set log_archive_dest_state_2 to enable. The managed standby is already run by the duplicate command. One may want to switch this to recovery using current logfile.

Enabling Real Time Apply on open standby (with VPD)

Actually nothing special. One needs to stop the recovery, open standby in READ-ONLY mode and one again start the recovery.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

There is however one trick - one needs to do this rather quick. It seems (at least for me) that even one archivelog behind the most actual means the managed replication will not start, waiting apparently on some flag indicating the end of previously written log. Thus copying to standby needed logs does not mean, the standby will apply them. It seems a condition is here that archivelog on standby should be "in transition" and the same log being current on primary.

UPDATE from 2013-06-20
It seems I missed some apparent (and specific for this configuration) facts. First of all the database in question despite of having its standby as Active Data Guard has also implemented some VPD policy, which corresponds to a trigger triggered on logon.

The starting position is the following:

• primary database up and running, log_archive_dest_state_2 equals to defer
• standby database shut down, few archivelogs behind the primary, FAL server indicated

When the standby is mounted or started it tries to connect to FAL server. In my case this connection fails due to the trigger mentioned above as it throws an exception for some reason. If the standby is mounted then enabling log_archive_dest_2 (ie setting log_archive_dest_state_2 do enable) on the primary makes the primary to connect to the standby. Those connections succeed. Now no matter what I will do (open the standby in READ ONLY mode and then start the Real Time Apply or start it on the mounted standby) the managed recovery will succeed - after recovery start the ARCH processes provide not yet transferred archivelogs, the recovery process (MRP0) applies them, and then (assuming USING CURRENT LOGFILE clause) switches to communication with a primary LNS process in order to apply directly from memory not waiting for the logfile switch.
But if I would open the standby directly or do not enable log_archive_dest_2 when the standby stays in MOUNTED mode then all the connections from the primary would fail as the FAL connection from the standby to the primary. In such case there is impossible to open the communication and enable successfully the replication - the MRP0 process will start without problems, yet the archivelogs will not be transferred to the standby.
Then the only solution I've found is to shut the standby down and then mount it, ensure the communication with the primary is enabled (simple switching log_archive_dest_state_2 to defer, then to enabled again resets the connections and the messages in the standby alert log will tell if it works (no messages or error messages signal the problem)) and now (optionally) open, then start the managed recovery.

All my previous hypotheses are apparently wrong. There is no need to be at the same sequence on the primary and the standby, so the time after which the recovery is started is irrelevant in this context. The only thing important is to make connections during mount phase (as they are impossible to acquire when any database is in open mode).

Error ORA-01031/ORA-00604 on communication with standby

When I have hit the ORA-01031 error on version 10g, the cause was usually boring - badly configured connection primary<->standby, lack of password file, improper permissions on such file, etc. With 11g version the trivial error become more enigmatic. I checked connections, copied the password file from the primary and still got ORA-01031 on connection from the primary to the standby (and ORA-00604 on the connection from the standby to the primary). Finally under the link https://forums.oracle.com/forums/thread.jspa?messageID=10350931 I have found quite good explanation to my problem.
With the advent of 11g version DEFAULT profile has got some limitations - among others expired passwords. Due to this passwords go through grace time period, when "normal" user sees a message about grace time period. But automatically controlled connection to/from a standby gets lost with this behavior, while returned errors do not indicate the right solution, which is simple of course. One may

• change a password (to solve temporarily)
• or change the DEFAULT profile
• or change SYS profile to less restrictive

One must remember that after any of those operations the current logfile needs to be applied on a standby in order to see a positive change. After that automatic redo shipping started to work properly.

UPDATE on 2013-06-20
It seems that despite possible problems with passwords (which may arise with similar symptoms) in my particular case the true reason behind the ORA-1031/ORA-604 errors is the local coniguration - on the database is enabled VPD technology and an ON LOGON trigger to enforce the required context settings. The connections are successful only if done from a primary to a mounted standby. More on the subject in another article.

Speeding up a standby recovery with incremental backup

Lately I have found a description for recovering using incremental backup. Despite speeding up a standby recovery (assuming the number of archivelogs to apply is much beyond the amount of changed data blocks) this method is useful for example for recovery from lost archivelog. I found some good articles on the topic above:

1. http://dbakerber.wordpress.com/2011/12/20/incremental-recovery-of-standby-asm-and-rman/
2. http://msutic.blogspot.com/2010/11/recover-from-incrementally-updated.html - this one is especially very precise

My personal goal was to speed up a standby recovery. I did it on the 11.2.0.2 version. I assume here disk channel, but tape is the same or even simpler as one do not need to transfer backup files from the primary to the standby host assuming centralized backup facility. The receipt is as follows:

1. make an incremental backup from SCN, at which our standby stopped (or actually it is better to backup slightly more then is needed, thus SCN should be a little lower then the really needed). This could be as simple as this:
   

   BACKUP INCREMENTAL FROM SCN  60839640548 DATABASE TAG='SCN_BEFORE_60839650548';
   

   Here a warning - this has to be a disk backup. In docs (at least for 10.2) there is stated that

   RMAN does not consider the incremental backup as part of a backup strategy at the source database.

   It vanished in later versions of docs (or at least I can not find it, but after perfoming an incremental backup to tape we were not able to list it).
2. make a copy of a current controlfile for standby:
   

   # from rman
   BACKUP CURRENT CONTROLFILE FOR STANDBY;
   # or from sqlplus
   ALTER DATABASE CREATE PHYSICAL STANDBY CONTROLFILE AS '/tmp/control01.ctl';
   

3. transfer both backups to the standby - starting from here all
4. restore control files with rman

   RESTORE STANDBY CONTROLFILE FROM '[controlfile path]';
   

   or simply replace existing control files if using sqlplus

   cp [controlfile path] [CONTROL_FILES parameter value] # for every position in CONTROL_FILES parameter of course
   

5. now mount the standby instance

   STARTUP NOMOUNT
   ALTER DATABASE MOUNT STANDBY DATABASE;
   

6. set STANDBY_FILE_MANAGEMENT to MANUAL (if set to AUTO)
7. catalog all the already existing on standby datafiles to control file

   CATALOG START WITH '[datafile path]'; # for every datafile path 
   

8. add all non-existent datafiles with sqlplus or with help of command SQL of rman

   ALTER DATABASE CREATE DATAFILE [file_id]; 
   

9. now is the time for switching the database to cataloged copies of datafiles. The simplest way is to:

   SWITCH DATABASE TO COPY;
   

   but if there were some non-existent files, which had to be added in the previous step, this will do not work. The source 2 provides a way to workaround it:

   CHANGE COPY OF DATAFILE [file_id] UNCATALOG;
   

   My way is to produce a script with a call to v$datafile:

   spool switch_files.rmn
   SELECT 'SWITCH DATAFILE '||file_id||' TO COPY;' rmn1 FROM v$datafile ORDER BY file#;
   spool off
   

   then execute it and edit the spooled content to remove unnecessary entries - among those also tries to switch lately created datafile stubs (stubs are already seen by the instance, so actually no need to switch to them).
10. now almost finish - recover database with rman - NOREDO forces rman to not use archivelogs at all

    RECOVER DATABASE NOREDO;
    

11. the rest is possibly to fix some small issues with redologs, which means usually clear logfiles and to start managed standby recovery
    • it is interesting that in order to clear standby logfiles one needs to put at work the parameter log_file_name_convert
    • it seems enabling the standby recovery may be performed at any SCN - we simply switch the recovery process from incremental backup to archived logs again. Now onwards we need to apply the logs requested by standby - assuming the managed standby, the log sequences are to be found in an alert log as entries about archive gaps.

      ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
      

And now we are with automatically running standby back again :-)

Error 1017 received logging on to the standby

I have got frequently the error "Error 1017 received logging on to the standby", while trying to enable the archivelog transfer between primary and standby.
The majority of tips on the net and the entry in the alert.log would tell You:
- check Your remote_login_passwordfile - should be SHARED or EXCLUSIVE
- check Your password file - it should exist and a password for SYS should be the same.
I have done as suggested, still no progress.

The solution was actually trivial - just copy the password file from the primary to the standby. Not sure why previously it did not work - I am sure the SYS password was the same and I checked connection in both directions (i.e. PRIMARY->STANDBY, STANDBY->PRIMARY) using the same entries in tnsnames.ora as specified in the archivelog transfer configuration.

It seems, there is a huge difference between versions 10g and 11g in this case - previously it was enough to create new password file with the same password. Now it must be the same file (ie. copy from the original on the primary).
And here is a very good article on the password files in Data Guard environment.