ORACLE DBA

Thursday, June 11, 2009

Example of datapump utility

CONTENTS page

1. Steps to be performed in Production Database 1
2 Steps to be performed in Test/Dev Database 3
3. Exercises 5

1. Steps to be performed in Production Database
• Check the size of the schema

select sum(BYTES)/1024/1024 from dba_segments where owner='SLADMIN';
• Check type of objects and status of the objects the schema.

col OBJECT_TYPE for a18
set pages 100
col owner for a10
select owner ,object_type,status, count(*) from dba_objects
where owner='DSADMIN' group by owner, object_type, status;
• Check the total number of objects in the schema.

select count(*) from dba_objects where owner like 'DSADMIN';
• Check for constraints validity in the schema.

Select constraint_type,count(*) from dba_constraints where owner='DSADMIN'
Group by constraint_type;

• Take an export of the Database scheme

exp file=exp_DSADMIN_.dmp log=exp_DSADMIN_dev_bkup.log owner=DSADMIN compress=n buffer=20480000 consistent=y

Should Show: “export terminated successfully without warnings”
To list errors:

cat exp_DSADMIN_dev_bkup.log | grep EXP-

Note:In Oracle 10g you can use both expdp along with exp utility.

To take an export in Oracle 10g
1) Export schema
C:\Documents and Settings\201910>expdp system/manager DIRECTORY=MY_DIR dumpfile=scott2.dmp schemas=scott

Copy the file from production to the export directory of staging/dev/test using scp.

scp exp_DSADMIN_dev_02feb09.dmp oradb:/oraclus1s/exp01/dev

enter oracle@gemsora10's password for oradb

oracle@gemsora10's password:
exp_DSADMIN_EPROD_22d 100% |***********************************************| 604 MB 01:31

ls –ltr

2. Steps to be performed in Test /Dev Database

Take a backup of scheme before proceeding with the below steps.

1. Drop the tables and objects from staging before importing dmp:

Also check for constraints validity before dropping

select 'drop table DSADMIN.'||table_name||' cascade constraints;' from dba_tables where owner='DSADMIN';

Note: This will drop tables and dependent objects.

2. Drop all the remaining objects

select 'drop '||object_type||' DSADMIN.'||object_name||';' from dba_objects where owner=DSADMIN';

10g DROP objects

select 'drop table DSADMIN.'||table_name||' cascade constraints purge;' from dba_tables where owner='SLADMIN';

select 'purge table DSADMIN."'||object_name||'";' FROM dba_RECYCLEBIN;

select 'drop '||object_type||' DSADMIN.'||object_name||';' from dba_objects where owner='SLADMIN';

select count(*) from dba_objects where owner='DSADMIN';

3 After dropping, import the objects in staging from production:

imp fromuser=DSADMIN touser=DSADMIN file=exp_DSADMIN_EPROD_4feb05.dmp log=imp_DSADMIN_EPROD_4feb05.log commit=y resumable=y buffer=40000000

To perform and import using impdp utility in Oracle 10g

C:\Documents and Settings\201910>impdp system/manager dumpfile=scott3.dmp remap_schema=SCOTT:MDDATA LOGFILE=MDDATA.LOG DIRECTORY=MY_DIR

We can ignore the following error:

IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "TBL_SCHEDULE_DETAILS" TO "DSADMIN_ICENTER_ROLE""
IMP-00003: ORACLE error 1917 encountered

 Count should match with that of the Production database

select distinct status from dba_objects where owner='SLADMIN';

 Should show as VALID

If invalid, compile it using run this script.:

alter owner.object_name compile;

select 'alter '||object_type||' DSADMIN.' ||object_name||' compile;' from dba_objects where owner='DSADMIN' and status='INVALID';

Execute below script to compile all invalid objects in the database.

@?/rdbms/admin/utlrp.sql

3. Exercises

• Export schema

C:\Documents and Settings\201910>expdp system/manager DIRECTORY=MY_DIR dumpfile=scott2.dmp schemas=scott

Note: USER SCHEMA IS CREATED BY IMPORT IF IT DOES NOT EXIST

The schema will only be created if the export dump file set contains
the necessary CREATE USER metadata and you are importing with enough
privileges

• Import schema

impdp directory=MY_DIR DUMPFILE=scott2.dmp remap_schema=SCOTT:SCOTT LOGFILE=importscott.log

• Export TABLE

1) expdp DIRECTORY=MY_DIR dumpfile=EMPK_TABLE.DMP logfile=EMP_TABLE.LOG tables=scott.empk

2) C:\Documents and Settings\201910>expdp dumpfile=MY_DIR:dept_table.dmp logfile=MY_DIR:dept_table.log tables=scott.dept

Export: Release 10.1.0.2.0 - Production on Saturday, 29 December, 2007 10:26

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

Username: SCOTT
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": SCOTT/******** dumpfile=MY_DIR:dept_table.dmp logfile=MY_DIR:dept_table.log tables=scott.dept
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.703 KB 6 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
C:\EXPDP_OUT\DEPT_TABLE.DMP
Job "SCOTT"." " successfully completed at 10:26

• EXPORT METADATA_ONLY

 expdp DIRECTORY=MY_DIR dumpfile=EMPK_TABLE_M1.DMP logfile=EMP_TABLE_METADATA1.LOG tables=scott.empk CONTENT=METADATA_ONLY

• Loading objects of one schema into another schema.

1 select owner ,object_type,status, count(*) from dba_objects
2* where owner='SCOTT' group by owner, object_type, status
SQL> /

OWNER OBJECT_TYPE STATUS COUNT(*)
---------- ------------------ ------- ---------- ------------------------
SCOTT INDEX VALID 2
SCOTT TABLE VALID 7
SCOTT PACKAGE VALID 2
SCOTT TRIGGER VALID 1
SCOTT FUNCTION VALID 1
SCOTT FUNCTION INVALID 5

6 rows selected.

• Dropping existing objects in MDDATA Schema

select 'drop table MDATA.'||table_name||' cascade constraints purge;' from dba_tables where owner=' MDATA ';

select 'drop '||object_type||' SCOTT.'||object_name||';' from dba_objects where owner=' MDATA ';

• C:\Documents and Settings\201910>impdp system/manager dumpfile=scott3.dmp remap_schema=SCOTT:MDDATA LOGFILE=MDDATA.LOG DIRECTORY=MY_DIR

Import: Release 10.1.0.2.0 - Production on Saturday, 29 December, 2007 10:57

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=scott3.dmp remap_schema=SCOTT:MDDATA LOGFILE=MDDATA.LOG DIRECTORY=MY_DIR
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"MDDATA" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MDDATA"."DEPT" 5.703 KB 6 rows
. . imported "MDDATA"."DEPT_OLD" 5.687 KB 5 rows
. . imported "MDDATA"."EMPK" 7.820 KB 14 rows
. . imported "MDDATA"."ORDERS" 5.593 KB 2 rows
. . imported "MDDATA"."SALGRADE" 5.585 KB 5 rows
. . imported "MDDATA"."BONUS" 0 KB 0 rows
. . imported "MDDATA"."MY_STUFF" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER1" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER2" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER4" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER6" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 6 error(s) at 10:57

1 select owner ,object_type,status, count(*) from dba_objects
2* where owner='MDDATA' group by owner, object_type, status
SQL> /

OWNER OBJECT_TYPE STATUS COUNT(*)
---------- ------------------ ------- ----------
MDDATA INDEX VALID 2
MDDATA TABLE VALID 7
MDDATA PACKAGE VALID 2
MDDATA TRIGGER VALID 1
MDDATA FUNCTION VALID 1
MDDATA FUNCTION INVALID 5

6 rows selected.

• Exporting only tables and indexes of schema

• C:\Documents and Settings\201910>expdp DIRECTORY=MY_DIR EXCLUDE=INDEX,FUNCTION,PACKAGE,TRIGGER DUMPFILE=TABLE_ONLY1.DMP LOGFILE=TABLE_ONLY1.LOG SCHEMAS=SCOTT

Export: Release 10.1.0.2.0 - Production on Saturday, 29 December, 2007 11:26

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

Username: SCOTT
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": SCOTT/******** DIRECTORY=MY_DIR EXCLUDE=INDEX,FUNCTION,PACKAGE,TRIGGER DUMPFILE=TABLE_ONLY1.DMP LOGFILE=TABLE_ONLY1.LOG SCHEMA
SCOTT
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "SCOTT"."DEPT" 5.703 KB 6 rows
. . exported "SCOTT"."DEPT_OLD" 5.687 KB 5 rows
. . exported "SCOTT"."EMPK" 7.820 KB 14 rows
. . exported "SCOTT"."ORDERS" 5.593 KB 2 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."MY_STUFF" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
C:\EXPDP_OUT\TABLE_ONLY1.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:26

 MDDATA Schema does not have any object .

SQL> col OBJECT_TYPE for a18
SQL> set pages 100
SQL> col owner for a10
SQL> select owner ,object_type,status, count(*) from dba_objects
2 where owner='MDDATA' group by owner, object_type, status;

no rows selected

C:\Documents and Settings\201910>IMPDP DIRECTORY=MY_DIR DUMPFILE=TABLE_ONLY1.DMP REMAP_SCHEMA=SCOTT:MDDATA

Import: Release 10.1.0.2.0 - Production on Saturday, 29 December, 2007 11:47

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

Username: SCOTT
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": SCOTT/******** DIRECTORY=MY_DIR DUMPFILE=TABLE_ONLY1.DMP REMAP_SCHEMA=SCOTT:MDDATA
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"MDDATA" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MDDATA"."DEPT" 5.703 KB 6 rows
. . imported "MDDATA"."DEPT_OLD" 5.687 KB 5 rows
. . imported "MDDATA"."EMPK" 7.820 KB 14 rows
. . imported "MDDATA"."ORDERS" 5.593 KB 2 rows
. . imported "MDDATA"."SALGRADE" 5.585 KB 5 rows
. . imported "MDDATA"."BONUS" 0 KB 0 rows
. . imported "MDDATA"."MY_STUFF" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 11:47

After importing in another schema

C:\Documents and Settings\201910>EXIT

SQL>
SQL> col OBJECT_TYPE for a18
SQL> set pages 100
SQL> col owner for a10
SQL> select owner ,object_type,status, count(*) from dba_objects
2 where owner='MDDATA' group by owner, object_type, status;

OWNER OBJECT_TYPE STATUS COUNT(*)
---------- ------------------ ------- ----------
MDDATA INDEX VALID 2
MDDATA TABLE VALID 7

================

• Loading Objects of one Tablespace to another Tablespace.

You can use remap_tablespace option to import objects of one tablespace to another tablespace by giving the command

TABLESPACES ==Identifies a list of tablespaces to export.

expdp DIRECTORY=MY_DIR DUMPFILE=NEW.DMP TABLESPACES=NEW

$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp
REMAP_TABLESPACE=users:sales

The above example loads tables, stored in users tablespace, in the sales tablespace

NOTE:Parameter TABLE_EXISTS_ACTION=REPLACE to be use if imporintg in same database

impdp DIRECTORY=MY_DIR DUMPFILE=NEW.DMP REMAP_TABLESPACE=NEW:OLD TABLE_EXISTS_ACTION=REPLACE

ORACLE DBA
Abdul Mateen.

Step to restore rman backup

C:\Documents and Settings\201910>sqlplus sys/mysecretpassword as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Nov 12 14:32:28 2008

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
C:\AUG\SYSTEM.DBF
C:\AUG\UNDOTBS1.DBF
C:\AUG\SYSAUX.DBF
C:\AUG\USERS.DBF

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> create spfile from pfile;

File created.

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size 792200 bytes
Variable Size 942401912 bytes
Database Buffers 104857600 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string C:\ORACLE\PRODUCT\10.1.0\DB_1\
DATABASE\SPFILEAUG.ORA
SQL> create pfile from spfile;

File created.

SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\201910>oradim -new -sid NOV -intpwd MYSECRETPASSWORD -startmode M
Instance created.

C:\Documents and Settings\201910>sqlplus sys/mysecretpassword@NOV AS SYSDBA

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Nov 12 14:56:41 2008

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> startup nomount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size 792200 bytes
Variable Size 942401912 bytes
Database Buffers 104857600 bytes
Redo Buffers 524288 bytes
SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\201910>set ORACLE_SID=NOV

C:\Documents and Settings\201910>rman target sys/mysecretpasswd

Recovery Manager: Release 10.1.0.2.0 - Production

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01017: invalid username/password; logon denied

C:\Documents and Settings\201910>rman target sys/mysecretpassword

Recovery Manager: Release 10.1.0.2.0 - Production

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

connected to target database (not started)

RMAN> set controlfile autobackup format for device type disk to 'C:\RMAN2\RMANBKP%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of CSET command at 11/12/2008 15:07:21
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available

RMAN> shutdown immediate;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of shutdown command at 11/12/2008 15:07:38
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available

RMAN> exit

Recovery Manager complete.

C:\Documents and Settings\201910>sqlplus sys/mysecretpassword@NOV AS SYSDBA

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Nov 12 15:07:46 2008

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shut immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\201910>rman target sys/mysecretpassword

Recovery Manager: Release 10.1.0.2.0 - Production

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1048576000 bytes

Fixed Size 792200 bytes
Variable Size 942401912 bytes
Database Buffers 104857600 bytes
Redo Buffers 524288 bytes

RMAN> set controlfile autobackup format for device type disk to 'C:\RMAN2\RMANBKP%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database controlfile instead of recovery catalog

RMAN> restore controlfile from autobackup;

Starting restore at 12-NOV-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

recovery area destination: C:\oracle\flash_recovery_area
database name (or lock name space) used for search: NOV
channel ORA_DISK_1: no autobackups found in the recovery area
autobackup search outside recovery area not attempted because DBID was not set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/12/2008 15:09:13
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

RMAN> set controlfile autobackup format for device type disk to 'C:\RMAN2\RMANBKP\%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore controlfile from autobackup;

Starting restore at 12-NOV-08
using channel ORA_DISK_1

recovery area destination: C:\oracle\flash_recovery_area
database name (or lock name space) used for search: NOV
channel ORA_DISK_1: no autobackups found in the recovery area
autobackup search outside recovery area not attempted because DBID was not set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/12/2008 15:11:41
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> set dbid 4202246879;

executing command: SET DBID

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 1048576000 bytes

Fixed Size 792200 bytes
Variable Size 942401912 bytes
Database Buffers 104857600 bytes
Redo Buffers 524288 bytes

RMAN> set controlfile autobackup format for device type disk to 'C:\RMAN2\RMANBKP\%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 1048576000 bytes

Fixed Size 792200 bytes
Variable Size 942401912 bytes
Database Buffers 104857600 bytes
Redo Buffers 524288 bytes

RMAN> set controlfile autobackup format for device type disk to 'C:\RMAN2\RMANBKP\%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

Starting restore at 12-NOV-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

recovery area destination: C:\oracle\flash_recovery_area
database name (or lock name space) used for search: NOV
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20081112
channel ORA_DISK_1: autobackup found: C:\RMAN2\RMANBKP\c-4202246879-20081112-01
channel ORA_DISK_1: controlfile restore from autobackup complete
output filename=C:\NOV\CONTROL23.CTL
output filename=C:\NOV\CONTROL33.CTL
output filename=C:\NOV\CONTROL34.CTL
Finished restore at 12-NOV-08

RMAN> alter database mount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/12/2008 15:20:43
ORA-01103: database name 'AUG' in controlfile is not 'NOV'

RMAN>

Wednesday, April 15, 2009

ORA-29833 + create index

To resolve

ORA-29833: indextype does not exist
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared.

Refer metalink Note:280713.1

Thanks,
Abdulmateen.

Friday, July 18, 2008

Migration 10g database from Windows to Linux using RMAN

Database Migration From Windows to Linux Using RMAN

Alejandro Vargas Principal Support Consultant
Oracle Advanced Customer Services

INDEX

SUMMARY........................................................................................................................................................................2
The Windows Database....................................................................................................................................................3
List of Steps Needed to Complete the Migration................................................................................................................4

1. Check platform compatibility between source and target OS.........................................................................................5
2. Start the database in read only mode............................................................................................................................6
3. Check database readiness for transport from Windows to Linux...................................................................................7
4. Check if there are any external objects.........................................................................................................................8
5. Using the RMAN CONVERT DATABASE Command.....................................................................................................9
6. Copy Converted Datafiles, Generated Transport Script and Parameter File to the Linux............................................11
7. Edit init.ora for new database......................................................................................................................................13
8. Edit The Transport Script.............................................................................................................................................16
9. Execute the Transport Script.......................................................................................................................................21
10. Change database identifier........................................................................................................................................27
11. Check database integrity...........................................................................................................................................31
End of Report...............................................................................................................................................................32

Database Migration From Windows to Linux Using RMAN

SUMMARY

This document describes the procedure required to migrate a database from Windows to Linux using the RMAN Convert
Database command.

Both Windows and Linux platforms have the same endian format, which makes possible to transfer the whole database,
making the migration process very straightforward and simple.

To migrate between platforms that have a different endian format, Cross Platform Transportable Tablespaces (XTTS)
needs to be used instead.

Database Migration From Windows to Linux Using RMAN

The Windows Database

This exercise was done using a small default 10.2.0.4 database

SQL> select bytes,file_name from dba_data_files;

BYTES FILE_NAME

440401920 G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_SYSTEM_44QYBFPF_.DBF
246415360 G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_UNDOTBS1_44QYCFQW_.DBF
136314880 G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_SYSAUX_44QYCZ3D_.DBF

5242880 G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_USERS_44QYDHY7_.DBF

SQL> select name from v$controlfile;

NAME

G:\TECHNOLOGY\ORCLW\CONTROLFILE\O1_MF_44QY9SXR_.CTL
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCLW\CONTROLFILE\O1_MF_44QY9TPX_.CTL

SQL> select member from v$logfile;

MEMBER

G:\TECHNOLOGY\ORCLW\ONLINELOG\O1_MF_1_44QY9VJL_.LOG
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCLW\ONLINELOG\O1_MF_1_44QY9ZZ7_.LOG
G:\TECHNOLOGY\ORCLW\ONLINELOG\O1_MF_2_44QYB14V_.LOG
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCLW\ONLINELOG\O1_MF_2_44QYB5L1_.LOG
G:\TECHNOLOGY\ORCLW\ONLINELOG\O1_MF_3_44QYB6OY_.LOG
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCLW\ONLINELOG\O1_MF_3_44QYBC2F_.LOG

Database Migration From Windows to Linux Using RMAN

List of Steps Needed to Complete the Migration

The migration process is simple, but as it has several steps it is convenient to be familiar with them before running it.

1. Check platform compatibility between source and target OS
2. Start the database in read only mode
3. Check database readiness for transport from Windows to Linux using DBMS_TDB.CHECK_DB
4. Check if there are any external objects
5. Execute the Rman Convert database command
6. Copy converted datafiles, generated Transport Script and Parameter File to Linux
7. Edit the init.ora for the new database
8. Edit the Transport Script and Parameter File changing the windows paths to Linux Paths
9. Execute the Transport Script
10.Change the Database ID
11.Check database integrity

Database Migration From Windows to Linux Using RMAN

1. Check platform compatibility between source and target OS
You need to check the platforms to be sure they have the same endian format, also you need to save the
platform_name string to use it later as part of the convert database syntax in RMAN.

SQL> select * from V$DB_TRANSPORTABLE_PLATFORM
2 where PLATFORM_NAME='Microsoft Windows IA (32-bit)' or
3 PLATFORM_NAME like 'Linux%'
4 /

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
13 Linux x86 64-bit Little

Database Migration From Windows to Linux Using RMAN

2. Start the database in read only mode
In order to execute dbms_tdb.check_db the database must be opened on read only mode.

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

SQL> startup mount;

ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1295608 bytes
Variable Size 71305992 bytes
Database Buffers 88080384 bytes
Redo Buffers 7090176 bytes
Database mounted.

SQL> alter database open read only;

Database altered.

Database Migration From Windows to Linux Using RMAN

3. Check database readiness for transport from Windows to Linux
If the execution of dbms_tdb.check_db does not return any exceptions, that means the database is ready for
transport to the target platform.

SQL> set serveroutput on

SQL> declare
2 db_ready boolean;
3 begin
4 db_ready := dbms_tdb.check_db('Linux IA (32-bit)');
5 end;
6 /

PL/SQL procedure successfully completed.

Database Migration From Windows to Linux Using RMAN

4. Check if there are any external objects
If there is any external objects take note of them, they will need to be taken care
manually

SQL> set serveroutput on

SQL> declare
2 external boolean;
3 begin
4 /* value of external is ignored, but with SERVEROUTPUT set to ON
5 * dbms_tdb.check_external displays report of external objects
6 * on console */
7 external := dbms_tdb.check_external;
8 end;
9 /

The following directories exist in the database:
SYS.DATA_PUMP_DIR, SYS.ORACLE_OCM_CONFIG_DIR, SYS.ADMIN_DIR, SYS.WORK_DIR

PL/SQL procedure successfully completed.

Database Migration From Windows to Linux Using RMAN

5. Using the RMAN CONVERT DATABASE Command
Having executed successfully the checkup steps, the database is open in read only mode, then the convert
database command can be executed with Rman.

In this example, I’m not using the db_file_name_convert ' '' option because the
database is using oracle managed files (omf); when using omf Rman does generate and display the new file
names on the output of the convert database command.

At the end of the convert process Rman does display information about how to complete the conversion on the
target platform.

C:\Documents and Settings\avargas>Rman target sys/oracle@orclw nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Jun 9 17:26:22 2008

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

connected to target database: ORCLW (DBID=1718464921)
using target database control file instead of recovery catalog

RMAN> CONVERT DATABASE NEW DATABASE 'orcllnx'
2> transport script 'G:\TECHNOLOGY\ORCLLNX\transportscript'
3> to platform 'Linux IA (32-bit)';

Starting convert at 09-JUN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database

Database Migration From Windows to Linux Using RMAN

User SYS with SYSDBA and SYSOPER privilege found in password filechannel ORA_DISK_1: starting datafile conversioninput datafile fno=00001 name=G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_SYSTEM_44QYBFPF_.DBFconverted datafile=G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_SYSTEM_44TM3OPF_.DBF
Directory SYS.ADMIN_DIR found in the database
Directory SYS.WORK_DIR found in the database

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile conversioninput datafile fno=00002 name=G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_UNDOTBS1_44QYCFQW_.DBFconverted datafile=G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_UNDOTBS1_44TM5F98_.DBF

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile conversioninput datafile fno=00003 name=G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_SYSAUX_44QYCZ3D_.DBFconverted datafile=G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_SYSAUX_44TM6JTB_.DBF

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversioninput datafile fno=00004 name=G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_USERS_44QYDHY7_.DBFconverted datafile=G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_USERS_44TM7BD5_.DBF

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script G:\TECHNOLOGY\ORCLLNX\TRANSPORTSCRIPT on the target platform to create
database

Edit init.ora file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00JIGSKL_1_0.ORA. This
PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished backup at 09-JUN-08

Database Migration From Windows to Linux Using RMAN

6. Copy Converted Datafiles, Generated Transport Script and Parameter File to the Linux
We need to copy over the generated files to the Linux server, they include all converted datafiles, the transport
script and the generated pfile.

If needed create at this point the directories you will need on the Linux server, for dump destination and flash
recovery area i.e.:

mkdir –p /oradisk/oracle/app/admin/ORCLLNX/adump
mkdir –p /oradisk/oracle/app/admin/ORCLLNX/bdump
mkdir –p /oradisk/oracle/app/admin/ORCLLNX/cdump
mkdir –p /oradisk/oracle/app/admin/ORCLLNX/udump
mkdir –p /oradisk/database/ORCLLNX/FRA

You can use ftp to copy the required files to the Linux server.

In my test I will mount on the Linux server the directory for the new database, so I just move over the converted
files to a predefined directory.

All converted files have the string 44TM as part of their names, i.e.: O1_MF_SYSTEM_44TM3OPF_.DBF, then,
while being at the datafiles location I do execute the move command to the new destination:

G:\TECHNOLOGY\ORCLW\DATAFILE>move *44TM* ..\..\ORCLLNX
G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_SYSTEM_44TM3OPF_.DBF
G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_UNDOTBS1_44TM5F98_.DBF
G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_SYSAUX_44TM6JTB_.DBF
G:\TECHNOLOGY\ORCLW\DATAFILE\O1_MF_USERS_44TM7BD5_.DBF

Then I need to move the generated pfile also:

Database Migration From Windows to Linux Using RMAN

G:\TECHNOLOGY\ORCLW\DATAFILE>move
C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00JIGSKL_1_0.ORA
..\..\ORCLLNX\initORCLLNX.ora

Check that all required files are located on the ORCLLNX directory

G:\TECHNOLOGY\ORCLW\DATAFILE>dir ..\..\ORCLLNX
Volume in drive G is TECHNOLOGY
Volume Serial Number is 1877-B4EA

Directory of G:\TECHNOLOGY\ORCLLNX

06/09/2008 05:27 PM dir .
06/09/2008 05:27 PM dir ..
06/09/2008 05:31 PM 2,616 TRANSPORTSCRIPT
06/09/2008 05:30 PM 440,410,112 O1_MF_SYSTEM_44TM3OPF_.DBF
06/09/2008 05:31 PM 246,423,552 O1_MF_UNDOTBS1_44TM5F98_.DBF
06/09/2008 05:31 PM 146,808,832 O1_MF_SYSAUX_44TM6JTB_.DBF
06/09/2008 05:31 PM 5,251,072 O1_MF_USERS_44TM7BD5_.DBF
06/09/2008 05:31 PM 1,556 initORCLLNX.ora

6 File(s) 838,897,740 bytes
2 Dir(s) 18,968,444,928 bytes free

Database Migration From Windows to Linux Using RMAN

7. Edit init.ora for new database
The Rman convert command executed on Windows generated a parameter file that needs to be edited to be used
on the target Linux Server.

The pfile generated by Rman:

# Please change the values of the following parameters:
control_files = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\CF_D

ORCLLNX_ID-1718464921_00JIGSKL"
db_create_file_dest = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TECHNOLOGY"
db_recovery_file_dest =

"C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\flash_recovery_area"
db_recovery_file_dest_size= 2147483648
audit_file_dest = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ADUMP"
background_dump_dest = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\BDUMP"
user_dump_dest = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UDUMP"
core_dump_dest = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\CDUMP"
db_name = "ORCLLNX"

# Please review the values of the following parameters:
__shared_pool_size = 62914560
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
__db_cache_size = 88080384
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclwXDB)"

# The values of the following parameters are from source database:
processes = 150

Database Migration From Windows to Linux Using RMAN

sga_target = 167772160
db_block_size = 8192
compatible = "10.2.0.3.0"
db_file_multiblock_read_count= 16
undo_management = "AUTO"
undo_tablespace = "UNDOTBS1"
job_queue_processes = 10
open_cursors = 300
pga_aggregate_target = 16777216

The pfile edited to be used on Linux

# Please change the values of the following parameters:

control_files = '/oradisk/database/ORCLLNX/orcllnx_control1.ctl' ,

'/oradisk/database/ORCLLNX/orcllnx_control2.ctl'
db_create_file_dest = '/oradisk/database/ORCLLNX'
db_recovery_file_dest = '/oradisk/database/ORCLLNX/FRA'
db_recovery_file_dest_size= 2147483648
audit_file_dest = '/oradisk/oracle/app/admin/ORCLLNX/adump'
background_dump_dest = '/oradisk/oracle/app/admin/ORCLLNX/bdump'
user_dump_dest = '/oradisk/oracle/app/admin/ORCLLNX/udump'
core_dump_dest = '/oradisk/oracle/app/admin/ORCLLNX/cdump'
db_name = 'ORCLLNX'

# Please review the values of the following parameters:

__shared_pool_size = 62914560
__large_pool_size = 4194304
__java_pool_size = 4194304

Database Migration From Windows to Linux Using RMAN

__streams_pool_size = 0
__db_cache_size = 88080384
remote_login_passwordfile= 'EXCLUSIVE'
db_domain = ''
dispatchers = '(PROTOCOL=TCP) (SERVICE=orcllnxXDB)'

# The values of the following parameters are from source database:

processes = 150
sga_target = 167772160
db_block_size = 8192
compatible = '10.2.0.3.0'
db_file_multiblock_read_count= 16
undo_management = 'AUTO'
undo_tablespace = 'UNDOTBS1'
job_queue_processes = 10
open_cursors = 300
pga_aggregate_target = 16777216

Database Migration From Windows to Linux Using RMAN

8. Edit The Transport Script
Before running the transport script on the target Linux server we need to edit it to set the correct paths for datafiles,
controlfiles and dump directories, also we may want to change the value for tuning parameters.

The script generated by Rman:

-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00JIGSKL_1_0.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "ORCLLNX" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292

LOGFILE
GROUP 1 SIZE 50M,
GROUP 2 SIZE 50M,

Database Migration From Windows to Linux Using RMAN

GROUP 3 SIZE 50M
DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-ORCLW_I-1718464921_TSSYSTEM_
FNO-1_07JIGSKL',
'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-ORCLW_I-1718464921_TSUNDOTBS1_
FNO-2_08JIGSMD',
'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-ORCLW_I-1718464921_TSSYSAUX_
FNO-3_09JIGSNG',

'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-ORCLW_I-1718464921_TSUSERS_
FNO-4_0AJIGSOA'
CHARACTER SET AL32UTF8
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE

SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 33554431M;
-- End of tempfile additions.

set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)

Database Migration From Windows to Linux Using RMAN

prompt * or the global database name for this database. Use the
prompt * NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE
STARTUP UPGRADE
PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00JIGSKL_1_0.ORA'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00JIGSKL_1_0.ORA'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;

The script edited to be run on Linux:

STARTUP NOMOUNT
PFILE='/oradisk/oracle/app/oracle/product/10.2.0/db_1/dbs/initORCLLNX.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "ORCLLNX" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292

LOGFILE
GROUP 1 SIZE 50M,
GROUP 2 SIZE 50M,
GROUP 3 SIZE 50M

DATAFILE
'/oradisk/database/ORCLLNX/O1_MF_SYSTEM_44TM3OPF_.DBF',

Database Migration From Windows to Linux Using RMAN

'/oradisk/database/ORCLLNX/O1_MF_UNDOTBS1_44TM5F98_.DBF',
'/oradisk/database/ORCLLNX/O1_MF_SYSAUX_44TM6JTB_.DBF',
'/oradisk/database/ORCLLNX/O1_MF_USERS_44TM7BD5_.DBF'

CHARACTER SET AL32UTF8
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 33554431M;
-- End of tempfile additions.

set echo off

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt * or the global database name for this database. Use the
prompt * NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE

Database Migration From Windows to Linux Using RMAN

STARTUP UPGRADE
PFILE='/oradisk/oracle/app/oracle/product/10.2.0/db_1/dbs/initORCLLNX.ORA'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP
PFILE='/oradisk/oracle/app/oracle/product/10.2.0/db_1/dbs/initORCLLNX.ORA';
-- The following step will recompile all PL/SQL modules.
-- It may take several hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;

Database Migration From Windows to Linux Using RMAN

9. Execute the Transport Script
This is the log of the transport script execution:

avargas-pc:/oradisk/database/ORCLLNX> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 11 10:11:54 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @transport
SQL> STARTUP NOMOUNT
PFILE='/oradisk/oracle/app/oracle/product/10.2.0/db_1/dbs/initORCLLNX.ora';
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1266368 bytes
Variable Size 71306560 bytes
Database Buffers 88080384 bytes
Redo Buffers 7118848 bytes

SQL> CREATE CONTROLFILE REUSE SET DATABASE "ORCLLNX" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292

Database Migration From Windows to Linux Using RMAN

7 LOGFILE
8
9
10
GROUP 1 SIZE 50M,
GROUP 2 SIZE 50M,
GROUP 3 SIZE 50M
11 DATAFILE
12
13
14
15
16
'/oradisk/database/ORCLLNX/O1_MF_SYSTEM_44TM3OPF_.DBF',
'/oradisk/database/ORCLLNX/O1_MF_UNDOTBS1_44TM5F98_.DBF',
'/oradisk/database/ORCLLNX/O1_MF_SYSAUX_44TM6JTB_.DBF',
'/oradisk/database/ORCLLNX/O1_MF_USERS_44TM7BD5_.DBF'CHARACTER SET AL32UTF8
17 ;

Control file created.

SQL>
SQL> -- Database can now be opened zeroing the online logs.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL>
SQL> -- Commands to add tempfiles to temporary tablespaces.
SQL> -- Online tempfiles have complete space information.
SQL> -- Other tempfiles may require adjustment.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE
2 SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 33554431M;

Tablespace altered.

SQL> -- End of tempfile additions.

Database Migration From Windows to Linux Using RMAN

SQL> -SQL>
SQL> set echo off
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

* Your database has been created successfully!
* There are many things to think about for the new database. Here
* is a checklist to help you stay on track:
* 1. You may want to redefine the location of the directory objects.
* 2. You may want to change the internal database identifier (DBID)
* or the global database name for this database. Use the
* NEWDBID Utility (nid).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1266368 bytes
Variable Size 71306560 bytes
Database Buffers 88080384 bytes
Redo Buffers 7118848 bytes

Database mounted.
Database opened.

>>>>>>>>>>>>>> UTLIRP and UTLRP execution started here <<<<<<<<<<<<<<<<<<

SQL>

Database Migration From Windows to Linux Using RMAN

SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if there the database was not opened in UPGRADE mode
DOC>
DOC> If you encounter this error, execute "SHUTDOWN", "STARTUP UPGRADE" and
DOC> re-execute utlirp.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance

2 WHERE status != 'OPEN MIGRATE';

no rows selected

SQL> Rem Store object numbers of all valid PL/SQL-based functional indexes
SQL> DROP TABLE utlirp_enabled_func_indexes;

Table dropped.

SQL> CREATE TABLE utlirp_enabled_func_indexes AS
2 SELECT obj# FROM ind$
3 WHERE bitand(property, 2048) != 0 AND bitand(flags, 1024) = 0;

Table created.

>>>>>>>>>>>>>> many output lines followed <<<<<<<<<<<<<<<<<<

Database Migration From Windows to Linux Using RMAN

These are the final lines of the logfile:

PL/SQL procedure successfully completed.

SQL>
SQL> DOC
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

SQL> select COUNT(*) "OBJECTS WITH ERRORS" from obj$ where status = 3;
OBJECTS WITH ERRORS

0

SQL>
SQL>
SQL> DOC
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;

Database Migration From Windows to Linux Using RMAN

ERRORS DURING RECOMPILATION

0

SQL>
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;

PL/SQL procedure successfully completed.

SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem
===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem
===========================================================================
SQL> set feedback 6;

Database Migration From Windows to Linux Using RMAN

10. Change database identifier
To change the database identifier you need to use the NEWDBID utility “nid”. It is run from within Sqlplus having
the database mounted:

sqlplus "/ as sysdba"
startup mount
exit

To change the DBID

cd $ORACLE_HOME/bin

./nid target=/

To verify the DBID and database name

SELECT dbid, name FROM v$_database;

DBID Change, Execution Log:

avargas-pc:~/app/oracle/product/10.2.0/db_1/bin> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 11 10:25:09 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Database Migration From Windows to Linux Using RMAN

Total System Global Area 167772160 bytes

Fixed Size
Variable Size
Database Buffers
Redo Buffers
1266368 bytes71306560 bytes88080384 bytes7118848 bytes
Database mounted.
SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

avargas-pc:~/app/oracle/product/10.2.0/db_1/bin> ./nid target=/

DBNEWID: Release 10.2.0.4.0 - Production on Wed Jun 11 10:25:50 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to database ORCLLNX (DBID=1718464921)
Connected to server version 10.2.0
Control Files in database:

/oradisk/database/ORCLLNX/orcllnx_control1.ctl
/oradisk/database/ORCLLNX/orcllnx_control2.ctl

Change database ID of database ORCLLNX? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1718464921 to 1179074095

Database Migration From Windows to Linux Using RMAN

Control File /oradisk/database/ORCLLNX/orcllnx_control1.ctl - modified
Control File /oradisk/database/ORCLLNX/orcllnx_control2.ctl - modified
Datafile /oradisk/database/ORCLLNX/O1_MF_SYSTEM_44TM3OPF_.DBF - dbid changed
Datafile /oradisk/database/ORCLLNX/O1_MF_UNDOTBS1_44TM5F98_.DBF - dbid

changed
Datafile /oradisk/database/ORCLLNX/O1_MF_SYSAUX_44TM6JTB_.DBF - dbid changed
Datafile /oradisk/database/ORCLLNX/O1_MF_USERS_44TM7BD5_.DBF - dbid changed
Datafile /oradisk/database/ORCLLNX/ORCLLNX/datafile/o1_mf_temp_44yxofkr_.tmp

-dbid changed
Control File /oradisk/database/ORCLLNX/orcllnx_control1.ctl - dbid changed
Control File /oradisk/database/ORCLLNX/orcllnx_control2.ctl - dbid changed
Instance shut down
Database ID for database ORCLLNX changed to 1179074095.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Successfully changed database ID.

DBNEWID - Completed succesfully.

avargas-pc:~/app/oracle/product/10.2.0/db_1/bin> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 11 10:28:22 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Database Migration From Windows to Linux Using RMAN

Total System Global Area 167772160 bytes
Fixed Size 1266368 bytes
Variable Size 71306560 bytes
Database Buffers 88080384 bytes
Redo Buffers 7118848 bytes

Database mounted.

SQL> alter database open resetlogs;
Database altered.

Database Migration From Windows to Linux Using RMAN

11. Check database integrity
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS

SQL> select file_name from dba_data_files;

FILE_NAME

/oradisk/database/ORCLLNX/O1_MF_USERS_44TM7BD5_.DBF
/oradisk/database/ORCLLNX/O1_MF_SYSAUX_44TM6JTB_.DBF
/oradisk/database/ORCLLNX/O1_MF_UNDOTBS1_44TM5F98_.DBF
/oradisk/database/ORCLLNX/O1_MF_SYSTEM_44TM3OPF_.DBF

Checking component status after transport:

SQL> SELECT COMP_NAME,STATUS FROM DBA_REGISTRY;
COMP_NAME STATUS
Oracle XML Database VALID

Oracle Expression Filter VALID

Database Migration From Windows to Linux Using RMAN

Oracle Rules ManagerOracle Workspace ManagerOracle interMedia
Oracle Database Catalog ViewsOracle Database Packages and TypesJServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
VALID
VALID
VALID
VALID
VALID
VALID
VALID
VALID
End of Report

Migration of 10g database from Windows to Linux using RMAN

Database Migration From Windows to Linux Using RMAN
1/32

INDEX
SUMMARY........................................................................................................................................................................2
The Windows Database....................................................................................................................................................3
List of Steps Needed to Complete the Migration................................................................................................................4
1. Check platform compatibility between source and target OS.........................................................................................5
2. Start the database in read only mode............................................................................................................................6
3. Check database readiness for transport from Windows to Linux...................................................................................7
4. Check if there are any external objects.........................................................................................................................8
5. Using the RMAN CONVERT DATABASE Command.....................................................................................................9
6. Copy Converted Datafiles, Generated Transport Script and Parameter File to the Linux............................................11
7. Edit init.ora for new database......................................................................................................................................13
8. Edit The Transport Script.............................................................................................................................................16
9. Execute the Transport Script.......................................................................................................................................21
10. Change database identifier........................................................................................................................................27
11. Check database integrity...........................................................................................................................................31
End of Report...............................................................................................................................................................32

Sunday, July 6, 2008

Good document about ASM from Oracle:

http://www.oracle.com/technology/products/database/asm/pdf/asm_10gr2_bestpractices%2009-07.pdf

Thanks,
Abdulmateen.