die Seilerwerks

A level 0 database backup of my production instance takes over 8 hours, and duplication is just as long, if not longer. I’m going to do some reading and see if we can shore things up. I’ll report back.  The database is around 1.1 terabytes.  Some have told me that their databases of similar size are backed up in only a couple of hours.

My main concern is the duplication, or restore & recovery.  An 8 hour restore/recovery  basically means the loss of a full business day (depending on when it is needed).

Just a brief note to share with you that if you are performing an RMAN duplication over an existing instance, you’ll want to delete your temporary tablespace tempfiles first. Metalink Note 374934.1 can tell you the whys and wherefores. The note also seems to suggest that the problem exists solely in Oracle 10.2.0.2.

The note suggests getting the list of datafiles with “select * from dba_temp_files;“, doing a “startup force nomount” and then deleting the files via OS commands, before finally kicking off the RMAN duplicate command. I had been toying with a different method (creating a new temp2 tablespace and dropping the original with contents and datafiles), but this method seems to be cleaner. Plus it is the WORD OF METALINK.

I plan to write up more about my big migration at work, but I wanted to drop one quick note. I’m using RMAN to duplicate my 32-bit production server to a 64-bit development server (I’ll do the same thing for production migration). However, at the very end, RMAN puked this up:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12005: error during channel cleanup
ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56319]

But otherwise RMAN said the duplication was finished and the database was opened. I could log in locally as SYSDBA just fine, but logins through the listener gave me those two PL/SQL errors again, as did any attept to use PL/SQL.

Thankfully, my blogging rival hali in #oracle suggested ultirp.sql, which is one of the many provided scripts in your $ORACLE_HOME/rdbms/admin/ directory, along with its brother, utlrp.sql. From the comments of utlirp.sql:

This script can be used to invalidate and all pl/sql modules (procedures, functions, packages, types, triggers, views) in a database.

This script must be run when it is necessary to regenerate the compiled code because the PL/SQL code format is inconsistent with the Oracle executable (e.g., when migrating a 32 bit database to a 64 bit database or vice-versa).

Please note that this script does not recompile invalid objects automatically. You must restart the database and explicitly invoke utlrp.sql to recompile invalid objects.

That second paragraph described me to a tee. So I kicked it off (have to start the instance in upgrade mode):

SQL> shutdown immediate;
SQL> startup upgrade;
SQL> @utlirp

Once that finishes, it tells you to then restart in normal mode and run utlrp.sql to recompile the objects it just invalidated.

SQL> shutdown immediate;
SQL> startup;
SQL> @utlrp

and VOILA! Errors gone. Of course, we still have to do the rest of the application testing! Thanks to hali for the tip, and for agreeing to let me blog about it this time.

If you’re using RMAN to take your backups, and are running Oracle 10g, do yourself a favor and enable block change tracking now. Prior to doing so, my level 1 incremental backups were scanning my entire database (~830 GB) and took over 5 hours. With BCT, level 1 incremental backups only look at around 45 GB (according to v$rman_backup_job_details) and often take less than 30 minutes.

The one caveat is to watch for the overhead incurred, but we haven’t seen any noticeable performance hit.

Of course my level 0 still takes over 9 hours, but I’m hoping to (re)implement incremental merge and only take one level 0 to start and never look back.

Yesterday in #oracle, hali pointed out the existance of the v$rman_backup_job_details view, and has since written a nice example post about using it to monitor the I/O done by every RMAN job you’ve done.