Recycle bin and dropping tablespaces

Today I hit an interesting issue. First the instance have not shut down within an hour. Second I could not drop a schema. The reason seems to be an entry in the recycle bin left after some table, which was bound with a tablespace, which in turn has been dropped apparently some time ago. So no tablespace, while the entry left and dropping the schema or purging recycle bin have failed with error in recursive SQL.

The solution quite simple - creating a new tablespace with the old name, then purging the recycle bin again .

Log rotation in 11g

Till version 11g the log rotation may been easily done by lsnrctl and set log_file to new name. With advent of ADR if used it blocks this way.
Excellent article on the theme IMHO at http://msutic.blogspot.com/2009/06/truncating-rotating-flushing.html.

Su-like technology for Oracle db

From time to time I meet with the situation when it comes to perform many changes on many db schemas provided as a set of scripts.

• The simplest way to do this is to logon sequentially to chosen schema and perform there all the necessary changes. This attitude has some drawbacks:
  
  1. one needs to know a password to every schema visited
  2. one needs to perform authentication to db many times
  3. one needs to run many scripts
• first improvement is to create master script, use CONNECT <<user>> command of sqlplus - but this still do not resolve providing passwords
  
• so we may create a more powerful account, gathering all the needed privileges and use
  ALTER SESSION SET CURRENT_SCHEMA=<<user>>; instead of CONNECT - this is a step in right direction and as long as scripts use simple objects and functionalities this way addresses all the inconveniences. Another ability is to prefix all the used objects.
  
• still yet we are far from perfectness - there are some functionalities which work wrong - the simplest example here is usage of private db links. Here we may use so called proxy users. Starting from 10gR2 these are available through sqlplus. We don't need a powerful user. We return back to use CONNECT, this time slightly modified. First we need an account with CREATE SESSION privilege. We need to alter all schemas, which we plan to make available:
  ALTER USER <<user>> GRANT CONNECT THROUGH <<proxy user>> (we may revoke it with REVOKE CONNECT THROUGH). This time we connect to any so configured user through
  CONNECT <<proxy user>>[<<user>>]/<<proxy user password>>. With this addressing all the problems is easy - we need to authenticate as only one user, so even though reconnecting many times, may use sqlplus variable substitution in order to provide a password only once.

There exists the special role BECOME USER, which apparently allows for similar behavior, however it is used for internal processing bound with Data Pump technology and rather not for use for "e;end users"e;.

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.

Installation of Oracle RDBMS on Oracle Linux 6

We install usually 2 Oracle products on database environments - RDBMS itself and Grid Control agent. The RDBMS binaries installation actually brings no problems - of course one needs X installation, which may be more than cautious sysadmin would like to install. The Grid agent is other thing - there is number of additional packages to install in order to make the installation process smooth. The best way on Oracle Linux 5 was to install package oracle-validated, which depends on few other packages, which fully cover the agent needs. With Oracle Linux 6 the package, which seems to be needed for that purpose is named oracle-rdbms-server-11gR2-preinstall (R must be in uppercase). However it seems it is not enough - additionally:

• yum install libXp.so.6
• yum install libXt.so.6
• yum install libXtst.so.6

Despite that another problem is I try to install the agent in 10.2.0.5 version, so must have used -ignoreSysPrereqs option. And now failure - some errors in linking. I will switch to 11.1.0.1.

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 :-)