March 2005

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

March 2005
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Moving a Database Across OS Platforms

You’re invited to submit your Oracle-related questions to us at askdbspecialists03@dbspecialists.com. Include your name and telephone number in your email. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only).

This month’s question comes to us from Mark in San Jose, CA: We need to replace our database server hardware, and we are considering moving to a different operating system platform as well. Will we be able to move our existing database to the new server by restoring a hot or cold backup? We want to minimize down time.

Roger Schrag of the Database Specialists team responds: A hot or cold backup of an Oracle database may only be restored onto a server where the OS platform and Oracle software version match the original database server exactly. In other words, if your current database is running on HP-UX, you cannot restore a backup onto a server running Linux and expect it to work.

For databases on Oracle 9i and earlier, one supported way to move the database to a different OS platform is by using the export and import utilities. While this method does let you move an Oracle database across platforms and even across Oracle versions, it can be very slow and usually requires a lot of down time.

Oracle 10g includes enhancements to the transportable tablespace feature that allow you to transport tablespaces across OS platforms. This feature allows you to move a large database to a new operating system with much less down time than would be required for the export/import method.

The high-level steps are as follows:

  • Create a database on the new server with the same character set as the existing database. Create the application users, but not the application tablespaces.
  • Switch the application tablespaces on the existing database to read only.
  • Use the export utility on the existing server to write a file containing all of the meta-data for the schema objects in the application tablespaces. (This is fast because only the object definitions are written to the export file—not the contents of tables.)
  • Depending on the source and target operating systems, you might need to use RMAN on the existing server to convert the data files to the format used on the new server.
  • Copy the export file and data files to the new server.
  • Use the import utility on the new server to read the export file. The object definitions will be added to the data dictionary and the transported tablespaces will be incorporated into the new database.
  • Switch the application tablespaces on the new database to read-write mode.

For more information about transportable tablespaces and transporting tablespaces across operating system platforms in Oracle 10g, see Metalink documents 243304.1 and 291024.1.

NoCOUG Conference Recap and Resources

If you missed last month’s Northern California Oracle Users Group (NoCOUG) meeting, you can still benefit from all the knowledge shared at the full-day conference. Many of the speakers have provided their presentations for viewing and download. In addition, Roger Schrag of Database Specialists held a standing-room-only session on performance tuning. Here’s a short list of what’s available on NoCOUG’s website: Oracle DBA Scripts, Articles, Tools, and More

  • Speeding Up Queries with Semi-Joins and Anti-Joins: How Oracle Evaluates EXISTS, NOT EXISTS, IN, and NOT IN by Roger Schrag
  • Oracle 10g Backup and Recovery New Features by Daniel Liu
  • A Structured Approach to Database Administration Using the Principles of ITSM and ITIL by Iggy Fernandez
  • Essentials of Real Application Clusters by David Austin
  • Minimizing Risks Though Deployment Standardization by Sudip Datta
  • Oracle10g for Data Warehousing by Hermann Baer

 

If you’re looking to add to your library of Oracle-related information, check out Oracle-Base. It’s packed with tons of information, including dozens of DBA scripts, articles on Oracle10g, 9i, and 8i, industry news, tools and utilities, and more. It’s also a great place to find out more about Oracle on Linux. You’ll find Oracle-Base at http://www.oracle-base.com. Here is a quick sampling of a few main topics and sub-topics:

Oracle Backup and Recovery Using RMAN

If you are looking for an introductory article on using RMAN for Oracle backup and recovery, check out the resources on orafusion.com. They’ve started a three-part series on the subject: Part one explains how to script RMAN backups, and part two discusses some recovery scenarios in detail. The focus is on Windows servers, but much of the information applies to other platforms as well. In addition, you’ll find the following and more in the Articles section of the website at http://www.orafusion.com/resources.htm:

  • Oracle Fine Grained Access Control: Implementing row-level security in Oracle.
  • Oracle SQL Aggregation Extensions: An introduction to “cube” and “rollup” extensions of the “group by” clause.
  • Oracle Generic Heterogeneous Services: A quickstart guide on connecting to non-Oracle databases from within Oracle.
  • Java Stored Functions in Oracle: Write, compile and invoke a Java stored module in Oracle.

 

 

DBA Scripts

  • Monitoring
  • Security
  • Resource Management
  • Constraints
  • Script Creation

Oracle 9i Articles

  • Oracle9i Installation
  • Oracle9i XML
  • Oracle9i Web
  • Oracle9i Application Server

Oracle 10g

  • Oracle Database 10g: New Features For Administrators OCP Exam
  • Oracle Database 10g Installations
  • Oracle Application Server 10g

Leave a Reply

Your email address will not be published. Required fields are marked *