How we faster the process of converting a non-ASM single-instance database to RAC database with ASM using RCONFIG tool?

I have been given with a challenging task to convert one of our critical production databases, which is of 1 TB (Terabyte) in size, to Oracle 10g RAC with ASM storage option. Even though, there are many methods and tools available to perform this activity, I have preferred to use the RCONFIG tool.

We prepared an input XML file required for RCONFIG tool, and run the RCONFIG utility as follows:

$ cd /oracle/ora102/db_1/assistants/rconfig/sampleXMLs
$ rconfig ConverToRAC.xml

When we start the RCONFIG tool to convert the database to RAC, the RCONFIG tool initially moves all the non-ASM database files to ASM disk files, for this RCONFIG tool internally invokes RMAN utility to backup the target database to the ASM disk groups, eventually the database is converted to RAC using RCONFIG.

The conversion took almost 9 hours to complete the process, because during the conversion RMAN used only one channel per data file to backup to ASM disks. There was no chance of improving the RMAN copy process by allocating more channels in the input XML file, and also Oracle doesn’t recommend doing other changes in the input XML file.

One thing was observed during the RMAN copy that RMAN is using target database control file instead of recovery catalog, and also using the RMAN default preconfigured settings for that database.

To know the RMAN default preconfigured settings for the database:

$ export ORACLE_SID=MYPROD
$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 5 10:21:05 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: MYPROD (DBID=1131234567)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/ora102/db_1/dbs/snapcf_T24MIG1.f'; # default

Here we see that the PARALLELISM is 1 (default), that’s why the RMAN using only one channel during backing up the non-ASM datafiles to ASM Disk Groups, and were taking 9 hours to complete the backup.

We have changed the PRALLELISM count to 6 (it depends upon number of CPUs you have in the server).

Solution:

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 6;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 6 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/ora102/db_1/dbs/snapcf_T24MIG1.f'; # default

After changing the PARALLELISM count to 6, the RMAN has allocated 6 channels and the conversion process has improved greatly and reduced the downtime drastically to 4 Hours 30 minutes.

Following is the extract of rconfig.log file, this file is located under:

$ORACLE_HOME/db_1/cfgtoolslogs/rconfig
............................................................................
............................................................................
............................................................................
[17:17:16:43] Log RMAN Output=RMAN> backup as copy database to destination '+DATA_DG';
[17:17:16:53] Log RMAN Output=Starting backup at 04-AUG-09
[17:17:16:258] Log RMAN Output=using target database control file instead of recovery catalog
[17:17:16:694] Log RMAN Output=allocated channel: ORA_DISK_1
[17:17:16:698] Log RMAN Output=channel ORA_DISK_1: sid=866 devtype=DISK
[17:17:17:9] Log RMAN Output=allocated channel: ORA_DISK_2
[17:17:17:13] Log RMAN Output=channel ORA_DISK_2: sid=865 devtype=DISK
[17:17:17:324] Log RMAN Output=allocated channel: ORA_DISK_3
[17:17:17:327] Log RMAN Output=channel ORA_DISK_3: sid=864 devtype=DISK
[17:17:17:637] Log RMAN Output=allocated channel: ORA_DISK_4
[17:17:17:641] Log RMAN Output=channel ORA_DISK_4: sid=863 devtype=DISK
[17:17:17:967] Log RMAN Output=allocated channel: ORA_DISK_5
[17:17:17:971] Log RMAN Output=channel ORA_DISK_5: sid=862 devtype=DISK
[17:17:18:288] Log RMAN Output=allocated channel: ORA_DISK_6
[17:17:18:293] Log RMAN Output=channel ORA_DISK_6: sid=861 devtype=DISK
[17:17:20:416] Log RMAN Output=channel ORA_DISK_1: starting datafile copy
[17:17:20:427] Log RMAN Output=input datafile fno=00053 name=/oradata/MYPROD/users_01.dbf
[17:17:20:532] Log RMAN Output=channel ORA_DISK_2: starting datafile copy
[17:17:20:544] Log RMAN Output=input datafile fno=00021 name=/oradata/MYPROD/ users_02.dbf
[17:17:20:680] Log RMAN Output=channel ORA_DISK_3: starting datafile copy
[17:17:20:694] Log RMAN Output=input datafile fno=00022 name=/oradata/MYPROD/ users_03.dbf
[17:17:20:786] Log RMAN Output=channel ORA_DISK_4: starting datafile copy
[17:17:20:800] Log RMAN Output=input datafile fno=00023 name=/oradata/MYPROD/ users_04.dbf
[17:17:20:855] Log RMAN Output=channel ORA_DISK_5: starting datafile copy
[17:17:20:868] Log RMAN Output=input datafile fno=00024 name=/oradata/MYPROD/ users_05.dbf
[17:17:20:920] Log RMAN Output=channel ORA_DISK_6: starting datafile copy
[17:17:20:930] Log RMAN Output=input datafile fno=00011 name=/oradata/MYPROD/ users_06.dbf
............................................................................
............................................................................
............................................................................
[21:29:5:518] Log RMAN Output=Finished backup at 04-AUG-09
............................................................................
............................................................................
............................................................................

[21:39:10:723] [NetConfig.startListenerResources:5] started Listeners associated with database MYPROD
[21:39:10:723] [Step.execute:255] STEP Result=Operation Succeeded
[21:39:10:724] [Step.execute:284] Returning result:Operation Succeeded
[21:39:10:724] [RConfigEngine.execute:68] bAsyncJob=false
[21:39:10:725] [RConfigEngine.execute:77] Result= < version="1.1">

<ConvertToRAC>
    <Convert>
      <Response>
        <Result code="0" >
          Operation Succeeded
        </Result>
      </Response>
      <ReturnValue type="object">
<Oracle_Home>
         /oracle/ora102/db_1
       </Oracle_Home>
        <SIDList>
          <SID>MYPROD1<\SID>
          <SID>MYPROD2<\SID>
        <\SIDList>     </ReturnValue>
    </Convert>
  </ConvertToRAC></RConfig>

Note: For the sake of look and feel format, the above output has been trimmed neatly. You can also observer that 6 channels were being allocated, timings of backup start and end, and the success code end of the rconfig.log file.

References:

To know more about RCONFIG tool and other Metalink references on it, please take a look at the below blog post written by Mr. Syed Jaffar Hussain.

http://jaffardba.blogspot.com/2008/09/my-experience-of-converting-cross.html

Oracle 10g R2 Documentation information on RCONFIG:

http://download.oracle.com/docs/cd/B19306_01/install.102/b14205/cvrt2rac.htm#BABBAAEH

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

Should I go for Oracle 10g OCA/OCP or 11g OCA/OCP Certification?

Hello All,

Choosing whether to go for Oracle 10g OCA/OCP or 11g OCA/OCP Certification is becoming complex to the Novice or Newbie DBAs. And also, I have seen a couple of posts asking such similar certification doubts more frequently in the OTN Forums, when they are not really sure or confused.

Well, this is ever been told by everyone that going for latest version certification is good and ideal. But, what I advise is, first go for Oracle 10g OCA and OCP, then upgrade to Oracle 11g OCP.

Following are my views on why to go for Oracle 10g OCA/OCP initially rather than 11g OCA/OCP directly.

• As we all know that newer version (11g) does include older version features plus new features and bug fixes of older version issues.
• Retirement date for Oracle 10g Certification has not yet been announced by Oracle. Moreover, Oracle Database 11g: Administration I (OCA) exam is only on production i.e. regular and Oracle Database 11g: Administration II (OCP) exam is not yet gone for Production i.e. still Beta Exam.
• Oracle Database 10g is still being used as Production for the business in all most all the organizations in the globe. But very less companies are using the Oracle Database 11g for their business, as Oracle 11g is still a base release 11.1.0.6 and yet to go for standard release 11g (11.2.X.X) shortly. This means that Oracle 11g is not fully deployed or used for Production purpose yet.
• Oracle Database 10g Release (10.2) still has Oracle primary and extended (Metalink) support for few more years from now, after that Oracle 10g will also be de-supported by Oracle.
• Both versioned (10g and 11g) certifications have two exams – Administration I (OCA) and Administration II (OCP). Each exam fees of them is $125 US i.e. there is no vary.
• It’s mandatory for the OCP candidates to undergo one approved course from the Approved list of Oracle University Courses for hands on course requirement. This applies to both Oracle 10g and 11g Certification takers.
• For Oracle 10g OCP Certification holders, there is only one exam 1Z0-050 - Oracle Database 11g: New Features for Administrators given to upgrade directly to the Oracle 11g OCP. No course or hands on course requirement form is to be submitted to achieve the Oracle 11g OCP (Upgrade) Certification.
• In this way, one will have both Oracle 10g and 11g Certifications in hand, and can show the same in their resume or CV. This also fulfills the requirement where the companies are looking for the candidates those are having enough experience with Oracle 10g and 11g, and holding multiple certifications in it.

One can go for direct Oracle 11g Certification under the following circumstances.

• If you are forced, by your company or manager, to undergo Oracle 11g Course and take Oracle 11g Certification Exams, for their specific requirement on Oracle Database 11g related projects.
• When there is no Oracle 10g course listed in the training institute in your city, instead only Oracle 11g Courses are available.
• When unable to afford to take Oracle 11g Upgrade exam.
• If my above views are not Okay with you -:)

Note: Folks, above are only my views, and need not to be the same with others. So, it’s left up you to decide whether to go for Oracle 10g OCA/OCP or 11g OCA/OCP Certification. For any information or doubts, then refer the Oracle Certification Program link and navigate yourself to the options to know more about Beta Exams, Retirements, List of Exams, Upgrade your Certification, Hands on Course Requirement etc.,

Your comments are welcomed if this article information helps you.

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com

How We Resolved the Account Locked (Timed) issue?

An application user account, in one of our Oracle 10g Databases, is being locked every time. Below are our findings and solution to resolve the issue.

Details:
Oracle Database Version: 10g R2 (10.2.0.1)
Application User: APPUSR
Error: ORA-28000: the account is locked

Login as SYSDBA

SQL> conn /as sysdba

Check the APPSUSR account status.

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPUSR’;
USERNAME ACCOUNT_STATUS PROFILE
-------------------- -------------------- ---------------
APPUSR LOCKED(TIMED) DEFAULT

Here we can see the account status is LOCKED (TIMED) and the default user’s profile is DEFAULT.

Check the resource limits of DEFAULT profile.

SQL> SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';

RESOURCE_NAME RESOURCE LIMIT
-------------------------------- -------- ----------
COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD 10
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL
PASSWORD_LOCK_TIME PASSWORD UNLIMITED
PASSWORD_GRACE_TIME PASSWORD UNLIMITED

All resource limits for DEFAULT profile is set to UNLIMITED, but only for FAILED_LOGIN_ATTEPTS attribute, it’s set to some value (10). Due to this the user account keeps getting locked(timed).When we check in the Oracle Documentations, it’s stated that FAILED_LOGIN_ATTEPTS attribute for DEFAULT profile has been changed from 10.2.0.1 from UNLIMITED to 10.

What we can do is, either we may need to change the resource limit for FAILED_LOGIN_ATTEPTS attribute in DEFAULT profile, or create a new profile for that user with FAILED_LOGIN_ATTEPTS attribute value set to UNLIMITED. But for security reasons, we will not tamper the DEFAULT profile, which is not recommended too. Then let’s go for creating a new profile and assign that profile to the user.

Create a profile.

SQL> CREATE PROFILE APPUSR_DEFAULT LIMIT
2 COMPOSITE_LIMIT UNLIMITED
3 SESSIONS_PER_USER UNLIMITED
4 CPU_PER_SESSION UNLIMITED
5 CPU_PER_CALL UNLIMITED
6 LOGICAL_READS_PER_SESSION UNLIMITED
7 LOGICAL_READS_PER_CALL UNLIMITED
8 IDLE_TIME UNLIMITED
9 CONNECT_TIME UNLIMITED
10 PRIVATE_SGA UNLIMITED
11 FAILED_LOGIN_ATTEMPTS UNLIMITED
12 PASSWORD_LIFE_TIME UNLIMITED
13 PASSWORD_REUSE_TIME UNLIMITED
14 PASSWORD_REUSE_MAX UNLIMITED
15 PASSWORD_VERIFY_FUNCTION NULL
16 PASSWORD_LOCK_TIME UNLIMITED
17 PASSWORD_GRACE_TIME UNLIMITED;

Profile created.

Assign the newly created profile to the user as default profile.

SQL> ALTER USER appusr PROFILE appusr_default;

User altered.

Unlock the user account:

SQL> ALTER USER appusr ACCOUNT UNLOCK;

User altered.

Now check again the status of APPUSR user.

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPUSR’;
USERNAME ACCOUNT_STATUS PROFILE
-------------------- -------------------- ---------------
APPUSR OPEN APPUSR_DEFAULT

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com

How We Used the Oracle CSSCAN Utility and CSALTER Script to Migrate the Database Character Set?

As per the requirement from the Application Vendor, we had to change the database character set on one of the existing test databases.

CSSCAN Utility and CSALTER Script:

Database character set migration has two stages: data scanning (csscan) and data conversion (csalter.plb).

The CSSCAN Utility is used to denitrify possible database character set conversion problems and truncation of data. Data scanning identifies the amount of effort required to migrate data into the new character encoding scheme before changing the database character set. This information helps to determine the best approach for converting the database character set.

The CSALTER script is part of the Database Character Set Scanner utility. The CSALTER script is the most straightforward way to migrate a character set, but it can be used only if all of the schema data is a strict subset of the new character set. The new character set is a strict superset of the current character set if:

Each and every character in the current character set is available in the new character set.

Each and every character in the current character set has the same code point value in the new character set.

Here are the simple steps to migrate the default Database Character Set:

Database Version: Oracle 10g Database R2 (10.2.0.3)
Source Character Set: AL32UTF8
Target Character Set: WE8ISO8859P1

Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Take a full database backup:

Sometimes, incorrect data conversion can lead to data corruption, so perform a full backup of the database before attempting to migrate the data to a new character set. The database can be reconstructed later using the backup taken before this action, if something goes wrong.

Note: We have taken the cold backup of the database and also performed the full database export using Datapump utility (expdp/impdp). It’s up to you, how and what type of full backup you prefer for your database i.e. Cold/Hot Backup/RMAN. So, you should recreate the database using this backup when the conversion fails.

Start up the database:

SQL> startup;
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.

Run the Database Character Set Scanner (CSSCAN) utility.

This CSSCAN Utility executable is under the $ORACLE_HOME/bin directory.

$ csscan \"sys/password@mydb as sysdba\" full=y
Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Sat Sep 13 15:58:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Current database character set is AL32UTF8.
Enter new database character set name: > WE8ISO8859P1
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..32): 1 > 32
Enumerating tables to scan...
. process 1 scanning SYS.SOURCE$[AAAABIAABAAAAIRAAA]
. process 2 scanning SYS.SOURCE$[AAAABIAABAAAHCJAAA]
. process 3 scanning SYS.ATTRIBUTE$[AAAAC1AABAAAAURAAA]
…………………………………………………………………………….
…………………………………………………………………………….
…………………………………………………………………………….

Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
$

For any help on CSSCAN utility i.e. keyword, prompt, and description, use the following command.

$ csscan help=y

Run the CSALTER script.

Once the Database Character Set Scanning has completed successfully, the database must be opened in restricted mode, because no normal user should allow to access the database during this task is being performed. So you can run the CSALTER script as the SYS user. The location of the CSALTER Script is "$ORACLE_HOME/rdbms/admin/csalter.plb".

Shut Down the Database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start Up the Database in Restricted Mode.

SQL> startup restrict;
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.

Run the csalter.plb script

SQL> @?/rdbms/admin/csalter.plb

0 rows created.

Function created.

Function created.

Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('y') <> 'Y') then
Checking data validility...
begin converting system objects
12 rows in table SYS.WRI$_ADV_RATIONALE are converted
1 row in table MDSYS.SDO_GEOR_XMLSCHEMA_TABLE is converted
80 rows in table SYS.METASTYLESHEET are converted
17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
4 rows in table SYS.RULE$ are converted
78 rows in table MDSYS.SDO_STYLES_TABLE are converted
10576 rows in table SYS.WRH$_SQL_PLAN are converted
21 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted
19 rows in table SYS.WRI$_ADV_ACTIONS are converted
4 rows in table MDSYS.SDO_XML_SCHEMAS are converted
2308 rows in table MDSYS.SDO_COORD_OP_PARAM_VALS are converted
578 rows in table SYS.WRI$_ADV_OBJECTS are converted
789 rows in table SYS.WRH$_SQLTEXT are converted

PL/SQL procedure successfully completed.

Alter the database character set...
CSALTER operation completed, please restart database

PL/SQL procedure successfully completed.

0 rows deleted.

Function dropped.

Function dropped.

Procedure dropped.

Shut Down the Database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start Up the Database

SQL> startup;
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
SQL>

Make sure the changes:

SQL> SELECT * FROM nls_database_parameters;

References:

Character Set Migration
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/ch11charsetmig.htm

Character Set Scanner Utilities
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scanner.htm

Oracle Metalink Notes on Database Character Set Migration

Note: 225912.1 - Subject: Changing the Database Character Set - a short Overview
Note: 119164.1 - Subject: Changing Database Character Set - Valid Superset Definitions
Note: 66320.1 -Subject: Changing the Database Character Set or the Database National Character Set
Note: 123670.1 - Subject: Use Scanner Utility before Altering the Database Character Set
Note: 225938.1 - Subject: Database Character Set Health check.

Conclusion: Here I have only explained the steps I followed to convert the database character set, which is specific to our single instance database. It means that this article doesn’t consist of any other information about Database Character Set, as these details are available and discussed thoroughly in the Oracle Documentations and Oracle Metalink. If the database is a multi instance database i.e. RAC, and has other options enabled in place. I would strongly recommend to follows the Oracle Documentations and Oracle Metalink Notes. Moreover, this scenario, explained here, is specific to our requirement, and need not be the same with others. When you are not sure or in doubt, then do *NOT* do this for other character set combinations without logging a tar with Oracle Support for verification.

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/