Oracle Database Migration Utilities

I did not really update my blog for sometimes.
Today , I am going to share with you on Oracle database migration utilities.

First of all, there is no migration utility (Script or DBUA) to perform a cross platform migration of an Oracle Database.

Changing platforms requires the database be re-built and / or the data moved using one of the following methods:

1. Export / Import to include the use of Datapump facilities.
    (All versions support Export/Import but for Datapump 10.1.0.2 or higher is required.)
2. Transportable Tablespaces 10G or Later
3. RMAN Convert Database functions. 10G or Later
4. Streams Replication
5. Create Table As Select (CTAS)
6. Dataguard Heterogeneous Primary and Physical Standbys
7. Oracle Golden Gate


Each of techniques have their strength or weakness, before implement it, you must study and do own research.

There is Oracle paper is writting on this topic - Migration Of An Oracle Database Across OS Platforms (Generic Platform) (Doc ID 733205.1) if you have interested to

Thanks! Have a great day!

ARC0: Media recovery disabled

I have seen a lot of error messages in alert.log.
ARC0: Media recovery disabled
ARC1: Media recovery disabled

When I grep the arc processes, those processes still alive in the background.
[oracle92@aysbdbs06 bdump]$ ps -ef | grep arc |  grep LHCCPOC
oracle92  5593     1  0 Dec10 ?        00:00:01 ora_arc0_LHCCPOC
oracle92  5595     1  0 Dec10 ?        00:00:00 ora_arc1_LHCCPOC

Refer to this Oracle document, ARC0: Media Recovery Disabled (Doc ID 131119.1).
The root cause is an archive process has been started on a database which is running in NOARCHIVELOG mode. 

In my environment, I have found this parameter LOG_ARCHIVE_START is TRUE.
Even the database is in NOARCHIVELOG mode, but the archival processes will still running 
if the parameter above is TRUE.






LOG_ARCHIVE_START initialization parameters is deprecated in Release 10.1.

The key to the fix is if your intend your database to be running in ARCHIVELOG or 
NOARCHIVELOG mode. 

RESOLUTION



1.If intention is to have database in ARCHIVELOG mode: 1.1. Alter the database log mode: SQL> startup mount; SQL> alter database archivelog;

2. If intention is to have database in NOARCHIVELOG mode: 2.1. Do not start an archive process on startup: set LOG_ARCHIVE_START=FALSE (default) 2.2. Ignore message in alert.log

How to apply Oracle feature OMF in File System?

If you company is using file system instead of ASM and OMF feature is enabled in your Oracle Database. I would said it is pretty difficult to manage the storage allocation because OMF feature is based on 3 database parameters: DB_CREATE_FILE_DEST, DB_CREATE_ONLINE_LOG_DEST_n and DB_RECOVERY_FILE_DEST.
Both of these initialization parameters are dynamic, and can be set using the ALTER SYSTEM or ALTER SESSION statement.

Since OMF is define the structure for data files, however, it is also refer your parameter DB_CREATE_FILE_DEST to create data files.

Below is case study to simulate the situation. The case study is add data file into different file system (mount point).
I have tried to run this command to add datafiles into 2 mount points in the same time, but it was prompted error.



Even, I had planned to add single datafile into the existing tablespace. But, it wasn't go through.
Obviously, the command is not allow to use your own defined path to create data file







Furthermore, I have create tablespaces with only 1 datafile attached.












SQL> alter system set db_create_file_dest='/data2/oradata' scope=both;

After changed the db_create_file_dest parameter, I have added data file into tablespaces created just now.












Now, both mount points have the data files with OMF managed.
There are pros and cons in this feature which need you to consider it further.

NETCA & DBCA utilities are not working in Oracle Database 10g

To resolve the problem, perform the following actions:

1) Install the following missing (required) Linux packages:

     glibc-32bit-8.1-9

     glibc-devel-32bit-8.1-9

Note 851598.1 Linux OS Requirements Reference List for Database Server

2) Run the following command to relink the Oracle executables:

$ORACLE_HOME/bin/relink all > /tmp/relink.txt 2>&1

Note 131321.1 How to Relink Oracle Database Software on UNIX/LINUX

3) Check the output of relink.txt for errors.

Oracle document reference
While Installing Oracle Database on Linux x86-64 Linking Errors Occur and NetCA/DBCA Fail with Error: "UnsatisfiedLinkError exception loading native library: njni10" (Doc ID 308788.1)


 

ORA-28031: maximum of 148 enabled roles exceeded

I was doing the migration for a huge database from HP-UX to Linux. My strategy is to export the dump from source database and import into the target database.

Out of the sudden, Oracle prompted this error during the import.
 

In the alert log, we found the DM00 process died which was import job is failed.
 

Throughout the trace files, we can see a lot of error messages stated
ORA-28031: maximum of 148 enabled roles exceeded


Description of the Problem
When a new session is initiated Oracle will attempt to enable all the default roles of the account if their number will be less than ( (value of the MAX_ENABLED_ROLES parameter) – 2 ). The default value of MAX_ENABLED_ROLES is 150. If the account is granted (directly or recursively) more than 148 roles then the session creation will fail with ORA-28031.

SQL> show parameter max_enabled_roles
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_enabled_roles                    integer     150

Solution
Check amount of roles is tight with SYS account (or other account run the import).
SQL> SELECT * FROM dba_role_privs WHERE Grantee='SYS' AND default_role='YES';

SQL> ALTER USER SYS DEFAULT ROLE CONNECT,EXP_FULL_DATABASE,JAVAUSERPRIV,OLAP_DBA, ...

(Recommendation for normal user, alter user <username> default role none;)

Cross check if there is data pump job still running or idle.
SQL> SELECT * FROM dba_datapump_jobs;
no rows selected

<
SQL> SET lines 200
SQL> COL owner_name FORMAT a10;
SQL> COL job_name FORMAT a20
SQL> COL state FORMAT a12
SQL> COL operation LIKE state
SQL> COL job_mode LIKE state
SQL> SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;  2    3    4    5

OWNER_NAME JOB_NAME OPERATION JOB_MODE   STATE  ATTACHED_SESSIONS
---------- -------------------- ------------ ------------ ------------ ----------- ------------ ----------- ------------
SYS        SYS_IMPORT_FULL_01   IMPORT       FULL         NOT RUNNING                  0

SQL> SELECT o.status, o.object_id, o.object_type,
       o.owner||'.'||object_name "OWNER.OBJECT"
  FROM dba_objects o, dba_datapump_jobs j
 WHERE o.owner=j.owner_name AND o.object_name=j.job_name
   AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

STATUS   OBJECT_ID OBJECT_TYPE         OWNER.OBJECT
------- ---------- ------------------- --------------------------------------------------
VALID        47842 TABLE               SYS.SYS_IMPORT_FULL_01
>

If you find the orphaned data pump jobs which the job status is not running.
Please follow the Oracle document for the step.
How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)

Run again the import and see the output
$ nohup sh import.sh > output.txt &



Pretty job Done!