Oracle Point, Oracle Life.

Recently, one of our production databases is planning to get ready for application software upgrade. For testing purpose, this production database is asked to be duplicated on remote server with real production data. To implement it, RMAN is probably the best solution for it without downtime of production database.

To implement duplication practice, the following steps we conducted in order to get it done eventually.

1. Preparing the duplicate (Auxiliary) instance on remote server

• creating oracle password file
• editing listener.ora, tnsnames.ora, and sqlnet.ora (if necessary)
• creating initialization parameter file for auxiliary instance

2. Creating full target database (production one) backup via RMAN, including

• data files
• control files
• archivelog files

3. Copying RMAN backup files to same file directory on remote server

4. duplicate database pre-creating preparation on remote server

• get original Filenames (data files and log files) from Target database
• creating file directory for duplicate database
• create symbolic links if necessary
• creating RMAN script for duplicating (customizing name and location of data files and log files )
• starting duplicating database

Above is the basic steps to get it done. And, thanks to posting Duplicate Oracle Database with RMAN on www.adadia.com, I’ve successfully implemented. During my practicing, I experienced couple of problems which caused failure of duplicating.

The biggest issue I experienced is that, unlike duplicating within same host, this approach failed to create duplicate database and recover to current point of time because RMAN can’t find necessary archive log files to apply at the end of duplicating. To overcome it, I added the following “set until time” line to RMAN duplicating script. The time I set to is minutes earlier than full RMAN backup time of target database.

set until time “to_date(’Apr 17 2010 10:00:00′,’Mon DD YYYY HH24:MI:SS’)”;

With setting of “set until time”, the process of RMAN duplicating go for incomplete recovery to previous point of time. Since archive log file is sitting with the full target database RMAN backup, this approach won’t cause any problem. 

Besides, I would like to bring forward to an efficiency fact when I’ was working on it. Generally, recovering data files from RMAN backup pieces would be inefficient because the single data file very likely cross over multiple RMAN backupset pieces. And, most importantly, creating data files from RMAN backupset pieces is kindly scan of backupset throughout. To speed this process, I copied all data files from my RMAN backup location. If you are using Oracle Suggest Backup Strategy, which is incremental daily backup, you’ll have a subfolder “datafiles”, where you could find data files for each tablespace, exactly like what you have under you home directory of instance. Simply copy them to same directory on remote server, and then RMAN duplicating will be speeding up because duplicating script will directly recover data files from those backup files. That must be super fast. Actually, I saved over 9 hours to get it done and the new duplicating process was finished within only an hour.

One more thing I would like to mention here is the potential impact of production (target) database. Because the duplicating process was conducted via RMAN with connection to both of target database and  duplicate database, you must be very careful if you want do recovery or restore outside of the duplicating script. If you made mistake there, that would hurt your target (production) database. Please remember, except for running duplicating script, don’t do anything else there.

Popularity: 7% [?]

One of our developers reported that she experienced the following errors while she was running a simple update statement to update an oracle table.

ORA-12096: error in materialized view log on “SATURN”.”SSBSECT”
ORA-01536: space quota exceeded for tablespace ‘DATADDS’

The update statement issued is as simple as followings.

update ssbsect set ssbsect_ssts_code=’A’
where ssbsect_user_id=’WSTUMGR’;

In order to figure out this problem, the following steps were conducted in specific order.

Step 1: check if the user by which that update statement run has Unlimited Tablespace privilege

Answer: yes, it has and also tablespace of target table has considerable free space

Step 2: find any dependent objects over target table.

select NAME from dba_dependencies where REFERENCED_NAME=’SSBSECT’

Answer: yes, there are some dependent objects.

Step 3: check the owner of those dependent objects

select OWNER, object_name, object_type from dba_objects where OBJECT_NAME in (select NAME from dba_dependencies where REFERENCED_NAME=’SSBSECT’);

Step 4: check if above owners have Unlimited Tablespace privilege on target tablespace

Answer: All above owners have Unlimited Tablespace privilege except for PUBLIC

Step 5: Grant Unlimited Tablespace privilege to PUBLIC

Grant unlimited tablespace to public;

The problem is solved. (Once done, I revoked Unlimited Tablespace privilege from Public and re-ran the update statement, it worked very well without error. It’s clear that  the quota has been reset.)

Note: What’s Public? A user, a role, a privilege? Refer to Oracle Metalink Note:234551.1

Further discussion: PUBLIC acts as a default role granted to every user in an Oracle database. Any authenticated database user can exercise privileges that are granted to PUBLIC. Therefore, granting unlimited tablespace privilege to public is kind of risky. (Reversely, revoking Unlimited Tablespace is also risky somehow.) The above granting is conducted based on the following two facts.

1. It’s done on development system.

2. We have tough space resource and are unable to extend target tablespace considerably.

Popularity: 7% [?]