SQL Server 2008 DBA Routine Maintenance Tasks: Daily, Weekly, Monthly/Quarterly

SQL Server 2008 DBA Daily Routine Maintenance Tasks
Maintenance tasks requiring close and regular attention are commonly checked each day. DBAs who take on these tasks daily ensure system reliability, availability, performance, and security. Some of the daily routine maintenance tasks include the following:
■ Check that all required SQL Server services are running.
■ Check Daily Backup logs for success, warnings, or failures.
■ Check the Windows Event logs for errors.
■ Check the SQL Server logs for security concerns such as invalid logins.
■ Conduct full or differential backups.
■ Conduct Transaction Log backups on databases configured with the Full or Bulk-Logged recovery model.
■ Verify that SQL Server jobs did not fail.
■ Check that adequate disk space exists for all database files and transaction logs.
■ At least monitor processor, memory, or disk counters for bottlenecks.

SQL Server 2008 DBA Weekly Routine Maintenance Tasks
Maintenance procedures that require slightly less attention than daily checking are categorized in a weekly routine. The following list details these weekly tasks:
■ Conduct full or differential backups.
■ Review Maintenance Plan reports.
■ Check database integrity.
■ Shrink the database if needed.
■ Compact clustered and nonclustered tables and views by reorganizing indexes.
■ Reorganize data on the data and index pages by rebuilding indexes.
■ Update statistics on all user and system tables.
■ Delete historical data created by backups, restores, SQL Server agent, and maintenance plan operations.
■ Manually grow database or transaction log files if needed. Adjust automatic growth values if needed.
■ Remove files left over from executing maintenance plans.

SQL Server 2008 DBA Monthly or Quarterly Maintenance Tasks
Some maintenance task are managed more infrequently, such as on a monthly or quarterly basis. Do not interpret these tasks as unimportant because they don’t require daily maintenance. These tasks also require maintenance to ensure the health of their environment, but on a less regular basis because they are more self-sufficient and self-sustaining. Although the following tasks may appear mundane or simple, they should not be overlooked during maintenance.
■ Conduct a restore of the backups in a test environment.
■ Archive historical data if needed.
■ Analyze collected performance statistics and compare them to baselines.
■ Review and update maintenance documentation.
■ Review and install SQL Server patches and service packs (if available).
■ Test failover if running a cluster, database mirroring, or log shipping.
■ Validate that the backup and restore process adheres to the Service Level Agreement defined.
■ Update SQL Server build guides.
■ Update SQL Server disaster recovery documentation.
■ Update maintenance plan checklists.
■ Change Administrator passwords.
■ Change SQL Server service account passwords.

From book "Microsoft SQL Server 2008 Management and Administration".

Best Practices on Managing SQL Server 2008 Indexes

Best Practices on Managing SQL Server 2008 Indexes

■ Managing and optimizing indexes is an ongoing process because performance can suffer both with the lack of indexes and poorly implemented and managed indexes.
■ Create clustered indexes on columns that are frequently used and are lean data types. For example, the clustered index could be an identity column. However, it is not uncommon to create the clustered index on a column used in WHERE, ORDER BY, and GROUP BY clauses and in joins.
■ Nonclustered indexes are dependent on the clustered index. Be careful when disabling a clustered index because the nonclustered indexes are also automatically disabled.
■ A table can have only one clustered index. Take care to ensure that the key length of the clustered index doesn’t exceed 900 bytes.
■ Use nonclustered indexes to cover frequent or costly queries that are not covered by the clustered index. As many as 999 nonclustered indexes can be created on a table.
■ Take care to ensure the key length of the nonclustered index doesn’t exceed 900 bytes. Add columns as nonkey include columns to place additional data into the index.
■ In order to alleviate performance degradation caused by fragmentation, maintain indexes by either updating or rebuilding indexes.
■ If the tempdb is stored in a different filegroup or on a different set of physical disks, use the option to sort the index in the tempdb for a performance improvement.
■ Leverage the Enterprise Edition’s online indexing feature to eliminate downtime when removing fragmentation from indexes.
■ When deleting all indexes on a table, remember to remove the clustered index last. If the clustered index is removed first, any nonclustered indexes are unnecessarily maintained as part of the removal process.
■ Spatial indexes should be created to increase the response time of spatial queries.
■ Leverage Filtered indexes to improve performance and reduce maintenance costs on columns that have nulls.

From book "Microsoft SQL Server 2008 Management and Administration".

How To Test If A TCP Port Is Open on UNIX

Sometimes we need to check if a certain TCP port is open. The following are some ways to do it.

$ netstat -an | grep 5100
      *.5100               *.*  0      0 4000000      0 LISTEN
      *.5100               *.*  0      0 4000000      0 LISTEN
      *.5100               *.*  0      0 4000000      0 LISTEN

SUCCESS:
$ echo | telnet server_name 5100
Trying 100.10.10.10...
Connected to server_name.
Escape character is '^]'.
Connection to server_name closed by foreign host.

FAIL:
$ telnet  server_name 5100
Trying 100.10.10.10...
telnet: Unable to connect to remote host: Connection refused

SQL Server 2008 Database Engine Best Practices

According to book "Microsoft SQL Server 2008 Management and Administration":

■ Leverage the scripting utility within SQL Server Management Studio to transform administration tasks into Transact-SQL syntax.
■ Unless there is a specific need to do otherwise, it is a best practice to allow SQL Server to dynamically manage the minimum and maximum amount of memory allocated to SQL Server. However, if multiple applications are running on SQL Server, it is recommended to specify minimum and maximum values for SQL Server memory. Then the application cannot starve SQL Server by depriving it of memory.
■ The preferred authentication mode is Windows Authentication over SQL Server Authentication because it provides a more robust authorization mechanism.
■ Use Change Tracking to ensure accountability and compliance on databases and database objects.
■ Leverage the compression technologies to reduce storage utilization.
■ Configuring SQL auditing is recommended to capture both failed and successful logins.
■ Do not set the database to automatically shrink on a regular basis because this leads to performance degradation and excessive fragmentation over time.
■ The first Database Engine administration task after a successful SQL installation should involve tuning and configuring the server properties.
■ Configure the recovery model for each database accordingly and implement a backup and restore strategy. This should also include the system databases.
■ Database files, transaction log files, and operating system files should be located on separate volumes for performance and availability.
■ When multiple database files and transaction log files exist, organize them through the use of filegroups.
■ Create basic reports in Management Studio to better understand the SQL Server environment.
■ Automate administration tasks by using SQL Server 2008 Agent jobs.
■ Review the other break-out chapters in the book for more information on items such as hardening a SQL Server infrastructure, encryption, Policy Based Management, Resource Governor, backups, and maintenance plans.

RMAN connect target database: RMAN-00571, RMAN-00569, ORA-01031

Got the following error when connect to the target database:


RMAN> connect target sys/****@mydb;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01031: insufficient privileges

Solution:

RMAN Denies Logon to Target Database: Scenario

RMAN fails with ORA-01031 (insufficient privileges) or ORA-01017 (invalid username/password) errors when trying to connect to the target database:

% rman
Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2003, Oracle.  All rights reserved.

RMAN> CONNECT TARGET sys/mypass@inst1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01031: insufficient privileges

RMAN Denies Logon to Target Database: Diagnosis

RMAN automatically requests a connection to the target database as SYSDBA. In order to connect to the target as SYSDBA, you must do one of the following:

• Be part of the operating system DBA group with respect to the target database (that is, have the ability to connect with SYSDBA privileges to the target database without a password).
• Create a password file with the orapwd command and the initialization parameter REMOTE_LOGIN_PASSWORDFILE.
• Make sure you are connecting with the correct username and password.

If the target database does not have a password file, then the user you are logged in as must be validated with operating system authentication.

RMAN Denies Logon to Target Database: Solution

Either create a password file for the target database or add yourself to the administrator list in the operating system.

Reference: http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmtroub.htm

Ways to Check oracle sid and database name

Ways to Check oracle sid and database name

1. Get the name of the instance
select sys_context('userenv','instance_name') from dual;

2. global_name is granted to PUBLIC, so anybody can query it.
select * from global_name;
select global_name from global_name;

3. use ORA_DATABASE_NAME
select ora_database_name from dual;

4. user v$ views
select name from v$database;
select instance_name from v$instance;

ORA-00821 ORA-01034 ORA-27101, SGA_target and SPARC T series database servers

Problem: 


When migrating a database from an older Solaris database server to a new Solaris T3/T4 series database server, SGA_Target has to be increased from 350MB t0 900MB to have the database started up. Otherwise ORA-04031 error would occur. 

After spending sometime researching, the following Oracle Support article explains the issue and gives a workaround.

Reference: ORA-00821 ORA-01034 ORA-27101, SGA_target need to be increased [ID 815426.1]

Goal

Migrated a database server from a server with 8 CPU's to a server with 128 (virtual) CPU's - that is, 2 CPU's, each with 8 cores which each handled 8 threads = 2x8x8 = 128.

So Solaris (on SPARC T series) is presenting Oracle with 128 CPU's. This makes Oracle adjust it's memory demands upwards, meaning that before the databases can ran fine with 320 MB of ram (sga_target) now needs to have up around 800-900 MB of RAM?

Is there anyway to minimize the memory usage?

Fix

Calculating the sga_target should consider the following:

The calculation for min cache size = max (number of CPUs, number of processor groups) * max (granule size, 4MB)

So the SGA size will mainly depend on CPU_COUNT.

Bug 8316255 was filed for this issue and initially closed as 'Not a Bug' with the comments:

"CPU_COUNT should not be changed . This is not a bug.
Reducing CPU_COUNT has side effects due to the fact that the per cpu power of CMT may not be sufficient to sustain high load ( e.g. cache fusion messages ).
Also, some of the dependent parameters such as gc latches would be underconfigured ". 

Currently, a fix has been produced which added the notion of the raw cpu threads and effective cpu threads.
This fix is only available in the 11.2.0.3 Bundle Patch 1 for Exadata Database and will be included in 12.1.
Please refer to:
Note 8316255.8 - Bug 8316255 - Wrong discovery of number of CPUs in SPARC T series.
Note 13343057.8 - Bug 13343057 - 11.2.0.3 Bundle Patch 1 for Exadata Database

For other systems, a way of reducing memory needs would be to set a manual value for DB_CACHE_SIZE (which depends on CPU_COUNT) and keep the old value of SGA_TARGET or not using ASMM (not using SGA_TARGET).




Reference: ORA-00821 ORA-01034 ORA-27101, SGA_target need to be increased [ID 815426.1]

Who controls load balancing with multiple CPUs, Oracle or OS?

Who controls load balancing with multiple CPUs, Oracle or OS?

Answer:

Load Balancing is not done by Oracle. The Operating System manages CPU control and usage.

The only references to CPU parameters in the init.ora file for Oracle are:    
--CPU_COUNT: http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams039.htm#REFRN10023
--PARALLEL_THREADS_PER_CPU: http://docs.oracle.com/cd/E14072_01/server.112/e10820/initparams183.htm

Neither of the above parameters are involved with Load Balancing of the CPU.

Reference: How to Load Balance Oracle with Multiple CPUs [ID 158090.1]

Oracle Drop Database Command

I did not know this command before until someone mentioned to me the other day! I am used to manually dropping databases on UNIX.

Anyhow, here is some info about this command.

DROP DATABASE

The DROP DATABASE command deletes these files from operating system.
* Datafiles
* Online Redo Log Files
* Controlfiles
* SPFILE (if it exists)

The DROP DATABASE command does not delete the following files:
* init.ora (text version of the Oracle initialization file)
* password file
* entries in listener files
* entries in oratab file


If the operating system is Windows, then it does not delete the Windows service for the target database instance.

Reference: Drop Database Command Versus Remove an Oracle Database Manually [ID 443822.1]

Choosing X client for graphical installation

Choosing X client for graphical installation

from: http://ivan.kartik.sk/index.php?show_article=33

What is new in Oracle 11.2.0.2?

There are a few changes in this release:

·         Packaging: Starting with the first patch set for Oracle Database 11g Release 2 (11.2.0.2), Oracle Database patch sets are full installations of the Oracle Database software.

·         Simplified new installation: Because the release 11.2.0.2 patch set is a full installation package, if you are installing Oracle Database on a system without an existing Oracle Database installation, then you can simply install the release 11.2.0.2 patch set. You are no longer required to install the base release, and then apply the patch set.

·         Upgrade process: Beginning with the release 11.2.0.2 patch set, you have two ways to apply a patch set:  

o    Out-of-place upgrade – This is Oracle's recommended way to apply a patch set. You install the patch set into a new, separate Oracle home location. After you install the patch upgrade, you then migrate the Oracle Database from the older Oracle home. The patch set upgrade is now the same process to upgrade from one version to another. Oracle recommends that you perform an out-of-place patch set upgrade, because this patch set application option requires much less downtime, and is safer because it does not require patching an ORACLE_HOME that is already being used in production. However, you must ensure that you have sufficient free disk space to accommodate two Oracle home directories at the same time.

o    In-place upgrade – You install the patch set into an existing Oracle home location. Oracle recommends that you select this option only if you do not have sufficient free disk space to perform an out-of-place upgrade, as the upgrade removes the existing Oracle installation. This patch option requires less disk space, but requires more time, and is riskier, because if you encounter an installation failure, then you must recover the entire existing Oracle home from a backup. If you choose this more risky option, then before you begin the patch installation, complete the following tasks.

·         Direct upgrade from previous releases: You can upgrade from a previous Oracle Database release directly to the latest patch set, without having to install the base release. For example, if you want to upgrade from Oracle Database 10g Release 2, or Oracle Database 11g Release 1, then you can upgrade directly to Oracle Database 11g Release 2, patch set 2 (11.2.0.2) using an out-of-place upgrade. Oracle now supports direct upgrades to the release 11.2.0.2 patch set from any release where direct upgrade to the base release (11.2.0.1) is supported, as well as direct upgrades from 11.2.0.1 to 11.2.0.2 or 11.2.0.3.

·         New Features Available in Patch Sets: In addition to the usual set of fixes, the release 11.2.0.2 patch set contains a small number of features that complete the Database 11g Release 2 feature set. Oracle Database New Features Guide for Oracle Database 11g Release 2

Oracle SQL Statement Examples: alter database

alter database

·         backup controlfile as a text file

alter database backup controlfile to trace as '';

·         resize a data file: increase size or decrease size

ALTER DATABASE DATAFILE '' RESIZE 1200M;

·         take a datafile out of AUTOEXTEND Mode

alter database datafile '' AUTOEXTEND off;

·         turn on the autoextend feature on a datafile

alter database datafile '' autoextend on next 100m maxsize 2000m;

·         enable block change tracking

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '';

·         add logfile group

ALTER DATABASE ADD LOGFILE GROUP 1 ('') SIZE 200M;

ALTER DATABASE ADD LOGFILE GROUP 2 ('', '') SIZE 200M;

ALTER DATABASE ADD LOGFILE MEMBER '' TO GROUP 1;

·         change GLOBAL_NAME

ALTER DATABASE RENAME GLOBAL_NAME TO mydb;

·         create a new database incarnation of the database

alter database open resetlogs;

·         change archivelog mode

alter database archivelog

alter database archivelog manual

alter database noarchivelog

·         READ ONLY / READ WRITE: The following statement opens the database in read-only mode:

startup mount;

ALTER DATABASE OPEN READ ONLY;

·         The following statement opens the database in read/write mode and clears the online redo logs:

startup mount;

ALTER DATABASE OPEN READ WRITE RESETLOGS;

·         Using Parallel Recovery Processes: The following statement performs tablespace recovery using parallel recovery processes:

ALTER DATABASE RECOVER TABLESPACE tbs_03  PARALLEL;

·         Adding Redo Log File Groups: Examples The following statement adds a redo log file group with two members and identifies it with a GROUP parameter value of 3:

ALTER DATABASE

ADD LOGFILE GROUP 3

('diska:log3.log', 'diskb:log3.log') SIZE 50K;

·         The following statement adds a redo log file group containing two members to thread 5 (in a Real Application Clusters environment) and assigns it a GROUP parameter value of 4:

ALTER DATABASE 

ADD LOGFILE THREAD 5 GROUP 4 

('diska:log4.log', 'diskb:log4:log');

·         Adding Redo Log File Group Members: The following statement adds a member to the redo log file group added in the previous example:

ALTER DATABASE ADD LOGFILE MEMBER 'diskc:log3.log' TO GROUP 3;

·         Dropping Log File Members: The following statement drops one redo log file member added in the previous example:

ALTER DATABASE DROP LOGFILE MEMBER 'diskb:log3.log';

·         The following statement drops all members of the redo log file group 3:

ALTER DATABASE DROP LOGFILE GROUP 3;

·         Renaming a Log File Member: The following statement renames a redo log file member:

ALTER DATABASE RENAME FILE 'diskc:log3.log' TO 'diskb:log3.log';

·         Setting the Default Type of Tablespaces: The following statement specifies that subsequently created tablespaces be created as bigfile tablespaces by default:

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

·         Changing the Default Temporary Tablespace: The following statement makes the tbs_5 tablespace the default temporary tablespace of the database. This statement either establishes a default temporary tablespace if none was specified at create time, or replaces an existing default temporary tablespace with tbs_05:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbs_05;

·         A group of tablespaces can be defined as the default temporary tablespace by using a tablespace group. The following statement makes the tablespaces in the tablespace group tbs_group_01 the default temporary tablespaces of the database:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbs_grp_01;

·         Creating a New Data File: The following statement creates a new data file tbs_f04.dbf based on the file tbs_f03.dbf. Before creating the new data file, you must take the existing data file (or the tablespace in which it resides) offline.

ALTER DATABASE CREATE DATAFILE 'tbs_f03.dbf' AS 'tbs_f04.dbf';

·         Manipulating Temp Files: The following takes offline the temp file temp02.dbf and then renames the temp file:

ALTER DATABASE TEMPFILE 'temp02.dbf' OFFLINE;

ALTER DATABASE RENAME FILE 'temp02.dbf' TO 'temp03.dbf';

·         Changing the Global Database Name: The following statement changes the global name of the database and includes both the database name and domain:

ALTER DATABASE RENAME GLOBAL_NAME TO demo.world.example.com;

·         Enabling and Disabling Block Change Tracking: The following statement enables block change tracking and causes Oracle Database to create a block change tracking file named tracking_file and overwrite the file if it already exists:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING

USING FILE 'tracking_file' REUSE;

·         The following statement disables block change tracking and deletes the existing block change tracking file:

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

·         Resizing a Data File: The following statement attempts to change the size of data file diskb:tbs_f5.dbf:

ALTER DATABASE DATAFILE 'diskb:tbs_f5.dbf' RESIZE 10 M;

·         Clearing a Log File: The following statement clears a log file:

ALTER DATABASE CLEAR LOGFILE 'diskc:log3.log';

·         Database Recovery: The following statement performs complete recovery of the entire database, letting Oracle Database generate the name of the next archived redo log file needed:

ALTER DATABASE RECOVER AUTOMATIC DATABASE;

·         The following statement explicitly names a redo log file for Oracle Database to apply:

ALTER DATABASE RECOVER LOGFILE 'diskc:log3.log';

·         The following statement performs time-based recovery of the database:

ALTER DATABASE

RECOVER AUTOMATIC UNTIL TIME '2001-10-27:14:00:00'; 

·         Making a Consistent Database Backup: Assume that the database is open and you want to make a consistent backup of the whole database. This example shuts down the database consistently, mounts the database, makes a consistent whole database backup, and then opens the database.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

BACKUP DATABASE PLUS ARCHIVELOG;

# Now that the backup is complete, open the database.

ALTER DATABASE OPEN;

·         Mounting the Database After Restoring the Control File: This example restores the control file, mounts it, and performs recovery. Finally, the example resets the online redo log.

STARTUP FORCE NOMOUNT;

RESTORE CONTROLFILE FROM AUTOBACKUP;

ALTER DATABASE MOUNT;

# You must run the RECOVER command after restoring a control file even if no

# datafiles require recovery.

RECOVER DEVICE TYPE DISK DATABASE;

ALTER DATABASE OPEN RESETLOGS;

How to set up exclude_list files on NetBackup clients

Question: how to set up exclude_list files on NetBackup clients?

Answer: Symantec website has an excellent article to explain this: DOCUMENTATION: Additional examples for setting up exclude_list files on NetBackup clients

An exclude_list file can be created for a specific client, policy or schedule.  The exclude_list file must be created by the administrator in the /usr/openv/netbackup directory on UNIX clients.

The following options exist when creating an exclude_list file:
- Create an exclude_list file if the exclude list will apply to all backups of the client.
- Create an exclude_list. if the exclude list will apply only for a specific policy.
- Create an exclude_list.. if the exclude list will apply to only one schedule in a policy.

NetBackup will use only read the exclude_list file that most closely matches the backup policy and schedule being performed. The and used in the file name must be an exact match to the policy and schedule defined on the master server.

The following examples are meant to show different methods to format an exclude_list on a NetBackup client.  It is important to note that exclude list entries need to follow UNIX rules and conventions. 

How to properly exclude a file name by the extension:
 

The following is a valid way to handle file names by extension: 

   *.log 

   *_log
 

This will properly exclude all files and directory names that end in .log or _log. The asterisk character is needed to do the wildcard expansion.
  

The incorrect way to exclude by extension is to use: 

   .log 

   _log 
 

This will only match specific file or directory names for .log or _log. Without the asterisk character, there is no wildcard expansion done to match files ending in these entries.
 

How to properly exclude a filename that contains a specific string:
 

The following is a valid way to handle filenames that contain a specific string: 

   *.log.* 

   *_log_*

This will match any file or directory name that contains .log. or _log_. The period or underscore has no specific meaning in this example. The asterisk does the wildcard expansion.
 

How to properly exclude a specific filename or directory:
 

The following is a valid way to handle specific file or directory names. 

   log 

   logs

This will match any file or directory names called log or logs.  In this case, no asterisk is needed since there isn't any matching to be done.
 

How to use the ? character in an exclude_list entry:
 

A valid way to use the ? character in an exclude_list entry would be: 

   log.??????

This would exclude any file that begins with log. and follows with six characters. This would match log.021804 but not match log. since log. must be followed by six characters. The ? is used as a wildcard for any single character in a file or directory name. Each question mark is used to match a single character.
 

How to use the [] characters in an exclude_list entry:

      EXAMPLE 1: A valid way to use the [] characters in an exclude_list entry would be:
         log[12345]
 

This would exclude the files log1, log2, log3, log4, log5 but would not match log or log12345. The characters inside the [] are used to match any single character that is contained between the [].
 

     EXAMPLE 2: Suppose path "/path" contains several subdirectories, u01, u02, u03, u04, u10, u12, u13, u14.

     If you only want to back up /path/u01, the following exclude entry would *not* work, and all subdirectories would be backed up:
             /path/u[02-14] 

     To exclude every subdirectory except u01, two lines would be needed in the exclude_list, using the following syntax:
            /path/u0[2-4]
            /path/u1[0-4]

    An easy way to confirm before the backup whether the syntax is correct is to use an "ls" command. For the above incorrect syntax, "ls" would reveal that the syntax is incorrect:
           ls /path/u[02-14]
           No Match.

How to exclude a file or directory that contains a special character:
    A valid way to exclude files that contain special characters in the name would be:
 

\*logfile\*

This would match the file named *logfile* but would not match logfile or any filename that contains logfile. The backslash character will de-reference any of the special characters used in a NetBackup exclude_list.
  

These special characters are: 

asterisk * 

comment # 

square brackets  [ ] 

backslash \ 

question mark ?
 

 

Reference: DOCUMENTATION: Additional examples for setting up exclude_list files on NetBackup clients

Clone database from a RMAN Cold back in Oracle 10g

Clone database from a RMAN Cold back in Oracle 10g

Database environment: ·         Oracle version: 10.2.0.4se ·         RMAN version: 10.2.0.4 ·         OS: Solaris 10 ·         Target database: TargetDB ·         Auxiliary database: AuxDB ·         RMAN catalog database: RcatDB

1. Take a cold backup from the target database TargetDB (source database) and using RMAN catalog database RcatDB

2. Prepare the directory structure for the auxiliary database AuxDB (cloned database) Scenario: ·         The target database and the auxiliary database are on different database servers. ·         Need to copy the cold backups of the target database from one server to the server where the auxiliary database resides. ·         The file structure of the cold  backup should be the same as on the target database server.

3. Ensure the following two parameters are included in the pfile: ############################################ # for rman database duplication ########################################### db_file_name_convert=('/oracle_data/TargetDB', '/oracle_data/AuxDB') log_file_name_convert=('/oracle_data/TargetDB', '/oracle_data/AuxDB')

4. Add the auxiliary database to /etc/oratab: AuxDB:/fs/u02/sw_ux/oracle/product/10.2.0.4se:Y

5. Add the auxiliary database to tnsnames.ora AuxDB =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = 1521))     )     (CONNECT_DATA =       (SID = AuxDB)       (SERVER = DEDICATED)     )   )

6. Add a Oracle password file for the auxiliary database orapwd file=$ORACLE_HOME/dbs/orapwAuxDB password= entries=10

7. Start the auxiliary database in NOMOUNT mode $ . oraenv ORACLE_SID = [AuxDB] ? AuxDB The /oracle/product/10.2.0.4se/bin/orabase binary does not exist You can set ORACLE_BASE manually if it is required. $ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 25 11:26:32 2010 Copyright (c) 1982, 2007, Oracle.  All Rights Reserved. Connected to an idle instance. SQL> create spfile from pfile='/oracle/admin/AuxDB/pfile/initAuxDB.ora'; File created. SQL> startup nomount ORACLE instance started. Total System Global Area  683671552 bytes Fixed Size                  2043136 bytes Variable Size             276828928 bytes Database Buffers          398458880 bytes Redo Buffers                6340608 bytes

8. Use RMAN to clone the auxiliary database $ rman trace clone_AuxDB.log Recovery Manager: Release 10.2.0.4.0 - Production on Mon Oct 25 11:44:53 2010 Copyright (c) 1982, 2007, Oracle.  All rights reserved. RMAN> connect target sys/syspwd@TargetDB; connected to target database: TargetDB (DBID=1722637034) RMAN>  connect catalog rman/rcpwd@RcatDB; connected to recovery catalog database RMAN> connect auxiliary / connected to auxiliary database: AuxDB (not mounted) RMAN> duplicate target database to AuxDB; Finished restore at 25-OCT-10 sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUXDB" RESETLOGS NOARCHIVELOG   MAXLOGFILES     16   MAXLOGMEMBERS      2   MAXDATAFILES       30   MAXINSTANCES     1   MAXLOGHISTORY     2337  LOGFILE   GROUP 1 ( '/oracle_data/AuxDB/redo01.log', '/oracle_data/AuxDB/redo01b.log' ) SIZE 10 M  REUSE,   GROUP 2 ( '/oracle_data/AuxDB/redo02.log', '/oracle_data/AuxDB/redo02b.log' ) SIZE 10 M  REUSE,   GROUP 3 ( '/oracle_data/AuxDB/redo03.log', '/oracle_data/AuxDB/redo03b.log' ) SIZE 10 M  REUSE  DATAFILE   '/oracle_data/AuxDB/system_01.dbf'  CHARACTER SET UTF8 contents of Memory Script: {    switch clone datafile all; } executing Memory Script released channel: ORA_AUX_DISK_1 datafile 3 switched to datafile copy input datafile copy recid=1 stamp=733319493 filename=/oracle_data/AuxDB/sysaux_01.dbf datafile 12 switched to datafile copy input datafile copy recid=10 stamp=733319493 filename=/oracle_data/AuxDB/users01.dbf datafile 13 switched to datafile copy input datafile copy recid=11 stamp=733319493 filename=/oracle_data/AuxDB/undotbs01.dbf contents of Memory Script: {    recover    clone database    noredo    ,     delete archivelog    ; } executing Memory Script Starting recover at 25-OCT-10 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=104 devtype=DISK Finished recover at 25-OCT-10 contents of Memory Script: {    shutdown clone;    startup clone nomount ; } executing Memory Script database dismounted Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area     683671552 bytes Fixed Size                     2043136 bytes Variable Size                276828928 bytes Database Buffers             398458880 bytes Redo Buffers                   6340608 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUXDB" RESETLOGS NOARCHIVELOG   MAXLOGFILES     16   MAXLOGMEMBERS      2   MAXDATAFILES       30   MAXINSTANCES     1   MAXLOGHISTORY     2337  LOGFILE   GROUP  1 ( '/oracle_data/AuxDB/redo01.log', '/oracle_data/AuxDB/redo01b.log' ) SIZE 10 M  REUSE,   GROUP  2 ( '/oracle_data/AuxDB/redo02.log', '/oracle_data/AuxDB/redo02b.log' ) SIZE 10 M  REUSE,   GROUP  3 ( '/oracle_data/AuxDB/redo03.log', '/oracle_data/AuxDB/redo03b.log' ) SIZE 10 M  REUSE  DATAFILE   '/oracle_data/AuxDB/system_01.dbf'  CHARACTER SET UTF8 contents of Memory Script: {    set newname for tempfile  1 to  "/oracle_data/AuxDB/temp_01.dbf";    switch clone tempfile all;    catalog clone datafilecopy  "/oracle_data/AuxDB/sysaux_01.dbf";    catalog clone datafilecopy  "/oracle_data/AuxDB/data_01.dbf";    catalog clone datafilecopy  "/oracle_data/AuxDB/indexes_01.dbf";    catalog clone datafilecopy  "/oracle_data/AuxDB/users01.dbf";    catalog clone datafilecopy  "/oracle_data/AuxDB/undotbs01.dbf";    switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed temporary file 1 to /oracle_data/AuxDB/temp_01.dbf in control file cataloged datafile copy datafile copy filename=/oracle_data/AuxDB/sysaux_01.dbf recid=1 stamp=733319503 cataloged datafile copy datafile copy filename=/oracle_data/AuxDB/undotbs01.dbf recid=11 stamp=733319503 datafile 3 switched to datafile copy input datafile copy recid=1 stamp=733319503 filename=/oracle_data/AuxDB/sysaux_01.dbf datafile 4 switched to datafile copy input datafile copy recid=2 stamp=733319503 filename=/oracle_data/AuxDB/data_01.dbf datafile 5 switched to datafile copy input datafile copy recid=5 stamp=733319503 filename=/oracle_data/AuxDB/indexes_01.dbf datafile 8 switched to datafile copy input datafile copy recid=10 stamp=733319503 filename=/oracle_data/AuxDB/users01.dbf datafile 13 switched to datafile copy input datafile copy recid=11 stamp=733319503 filename=/oracle_data/AuxDB/undotbs01.dbf contents of Memory Script: {    Alter clone database open resetlogs; } executing Memory Script database opened Finished Duplicate Db at 25-OCT-10 RMAN> RMAN> exit Recovery Manager complete.

9. Complete.