ORA-01555: snapshot too old: rollback segment number

SCHEMA: WMPYMYISADMIN82
TABLE: BIZDOCCONTENT

ORA-31693: Table data object "WMPYMYISADMIN82"."BIZDOCCONTENT" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 15 with name "_SYSSMU15_134866855$" too small

---------------------------------------------------------------------------------------------------------------------------------
SQL> alter system set undo_retention=88888888 scope=both;

SQL> show parameter undo_retention

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
undo_retention                       integer                           88888888

select owner,table_name, column_name, nvl(retention, 0) retention, pctversion from dba_lobs where owner = 'WMPYMYISADMIN82'
and table_name='BIZDOCCONTENT';

OWNER                          TABLE_NAME                     COLUMN_NAME       RETENTION PCTVERSION
------------------------------ ------------------------------ ----------------- ---------- ----------
WMPYMYISADMIN82                BIZDOCCONTENT                  CONTENT           900

alter table WMPYMYISADMIN82.BIZDOCCONTENT modify lob (CONTENT) (pctversion 5);
alter table WMPYMYISADMIN82.BIZDOCCONTENT modify lob (CONTENT) (retention);

OWNER                          TABLE_NAME                     COLUMN_NAME        RETENTION PCTVERSION
------------------------------ ------------------------------ ------------------ ---------- ----------
WMPYMYISADMIN82                BIZDOCCONTENT                  CONTENT            88888888

----------------------------------------------------------------------------------------------------------------------------------

SCHEMA: WMPYMYISADMIN82
TABLE: UTSCHS

ORA-31693: Table data object "UNILAB"."UTSCHS" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8$" too small

---------------------------------------------------------------------------------------------------------------------------
SQL> alter system set undo_retention=88888888 scope=both;

SQL> show parameter undo_retention

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
undo_retention                       integer                           88888888


select owner,table_name, column_name, nvl(retention, 0) retention, pctversion from dba_lobs where owner = 'UNILAB'
and table_name='UTSCHS';

Automatic Archival Process in Oracle 9i

In the alert log, ...
Tue May 31 09:57:05 2016
ORACLE Instance ldwtdb - Can not allocate log, archival required



The archival process died after automatic archival is disabled.



This is only happen in oracle 9i and oracle 10g on wards, auto archival is enabled when the database is placed in archive log mode.

In the alert log, ...
Wed Jun  1 14:08:53 2016
ARCH: STARTING ARCH PROCESSES COMPLETE
Wed Jun  1 14:08:53 2016
ARC0: Becoming the 'no FAL' ARCH
ARC1 started with pid=28
ARC1: Archival started
ARC1: Becoming the heartbeat ARCH
Wed Jun  1 14:08:53 2016
ARC0: Becoming the 'no FAL' ARCHARC0: Evaluating archive   log 2 thread 1 sequence 1458
Wed Jun  1 14:08:53 2016
ARC1: Becoming the heartbeat ARCHARC1: Evaluating archive   log 2 thread 1 sequence 1458



Since, these 2 archival processes is working, archiving redolog is working pretty good.

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)