Move Oracle 11G database from Sun OS to AIX server

Oracle replatform to different host:
First of all, Oracle "software only" was installed with exact same version on the target AIX host. Next steps:

1. the source and target hosts must in same endian format
SQL> select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;

Below query also gives the platform name:
SQL> select PLATFORM_NAME from v$database;
PLATFORM_NAME
--------------------------------------------------------------------------------
Solaris[tm] OE (64-bit)

2. open source database in read only mode
SQL> startup mount
SQL> alter database open read only;
SQL> select open_mode from v$database;

3. verify the source database is ready

3.1 Check database if ready to replatform on target host
 SQL> set serveroutput on
 SQL> declare
 db_ready boolean;
 begin
 db_ready := dbms_tdb.check_db('AIX-Based Systems (64-bit)');
 end;
 /
3.2 if it return below messages, follow instructions. All tablespaces must be in READ/WRITE mode.

Some files in the database have not been converted to the 10.0 compatibility format. Open the database in READ/WRITE mode and alter tablespace TEST_DATA to READ/WRITE. Then open the database in READ-ONLY mode and retry.

SQL> startup
SQL> ALTER TABLESPACE TEST_DATA READ WRITE;
SQL> select tablespace_name, status from dba_tablespaces;

3.3 repeat the step 2 (to make sure source database is ready for doing the copy)

4. check if external tables, directories and BFILEs are used
SQL> set serveroutput on
SQL> declare
 external boolean;
 begin
 external := dbms_tdb.check_external;
 end;
 /

5. get scripts ready

5.1 create a copy of pfile in source database.
SQL> create pfile='/oracle/diag/rdbms/docdb/dba/initDOCDB.ora' from spfile;

5.2 run on source platform, this step creates 3 files init_XXXXX.ora, convertscript.rman and transportscript.sql

rman target /
RMAN>
CONVERT DATABASE ON TARGET PLATFORM
CONVERT SCRIPT '/oracle/diag/rdbms/docdb/dba/convertscript.rman'
TRANSPORT SCRIPT '/oracle/diag/rdbms/docdb/dba/transportscript.sql'
new database 'DOCDB'
FORMAT '/oracle/diag/rdbms/docdb/dba/%U';

6. copy all below files to target host (AIX server). This takes time for large database.

6.1 datafiles (copy them to temporary directry /oradata/DOCDB/backup/ and will convert then to desired location later)
6.2 other files to /app/oracle/diag/rdbms/docdb/dba
init_XXXXX.ora   (useless)
convertscript.rman
transportscript.sql
initDOCBD.ora

7. assume necessary folder structure exists on target host, such as /app/oracle/diag/rdbms/docdb/DOCDB/adump for audit_file_dest. Edit the initDOCDB.ora file, startup the instance in nomount.

STARTUP NOMOUNT PFILE = '/app/oracle/diag/rdbms/docdb/dba/initDOCDB.ora';

8. modify the convertscript.rman script

8.1 create dummy controlfile, find more information in convertscript.rman
(Use sql on source database to get the full list of statements:
select '''/oradata/DOCDB/backup/' || substr(file_name, instr(file_name,'/',-1)+1) ||''''
from dba_data_files; )

CREATE CONTROLFILE REUSE SET DATABASE "DOCDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata/DOCDB/redo_cntrl01/redo01_temp.log' SIZE 10M,
GROUP 2 '/oradata/DOCDB/redo_cntrl02/redo02_temp.log' SIZE 10M,
GROUP 3 '/oradata/DOCDB/redo_cntrl03/redo03_temp.log' SIZE 10M
DATAFILE
'/oradata/DOCDB/backup/system01.dbf',
'/oradata/DOCDB/backup/tools01.dbf',
'/oradata/DOCDB/backup/data_lrg.dbf',
'/oradata/DOCDB/backup/indx_med.dbf',
....  ....  ....
'/oradata/DOCDB/backup/indx_lrg.dbf',
'/oradata/DOCDB/backup/undo01.dbf',
'/oradata/DOCDB/backup/sysaux01.dbf'
CHARACTER SET WE8ISO8859P1
;

8.2 use below sql in source database to get the list of data files with the new path:
select 'DATAFILE ''/oradata/DOCDB/backup/' || substr(file_name, instr(file_name,'/',-1)+1)
 ||''' FORMAT ''/oradata/DOCDB/data01/data/' || substr(file_name, instr(file_name,'/',-1)+1) || ''''
from dba_data_files;

8.3 make sure folder "/oradata/DOCDB/data01/data" exists and has enough space on target host. It will be the new permanent location for datafiles.

9. run final rman convert script. Database must be mounted at this step.

$ rman target / nocatalog
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 17 09:29:22 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DOCDB (DBID=37305165, not open)
using target database control file instead of recovery catalog

RMAN> @convertscript.rman
========================================================================
RUN {
  CONVERT
  FROM PLATFORM 'Solaris[tm] OE (64-bit)'
  PARALLELISM 4
DATAFILE '/oradata/DOCDB/backup/system01.dbf' FORMAT '/oradata/DOCDB/data01/data/system01.dbf'
DATAFILE '/oradata/DOCDB/backup/tools01.dbf' FORMAT '/oradata/DOCDB/data01/data/tools01.dbf'
DATAFILE '/oradata/DOCDB/backup/data_lrg.dbf' FORMAT '/oradata/DOCDB/data01/data/data_lrg.dbf'
DATAFILE '/oradata/DOCDB/backup/indx_med.dbf' FORMAT '/oradata/DOCDB/data01/data/indx_med.dbf'
DATAFILE '/oradata/DOCDB/backup/indx_lrg.dbf' FORMAT '/oradata/DOCDB/data01/data/indx_lrg.dbf'
DATAFILE '/oradata/DOCDB/backup/undo01.dbf' FORMAT '/oradata/DOCDB/data01/data/undo01.dbf'
DATAFILE '/oradata/DOCDB/backup/sysaux01.dbf' FORMAT '/oradata/DOCDB/data01/data/sysaux01.dbf'
; }
.... .... ....
Finished conversion at target at 12-JUL-13
RMAN-06497: WARNING: control file is not current, control file AUTOBACKUP skipped
RMAN> **end-of-file**
========================================================================

10. Shutdown database
SQL> shutdown immediate

11. Now, the database is converted. Use latest init file to create control file
(Useful sql: select '''/oradata/DOCDB/data01/data/' || substr(file_name, instr(file_name,'/',-1)+1) || ''','
from dba_data_files; )

STARTUP NOMOUNT PFILE=/app/oracle/diag/rdbms/docdb/DOCDB/pfile/initDOCDB.ora

CREATE CONTROLFILE REUSE SET DATABASE "DOCDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 20
    MAXLOGMEMBERS 4
    MAXDATAFILES 600
    MAXINSTANCES 1
    MAXLOGHISTORY 7260
LOGFILE
  GROUP 1 ('/oradata/DOCDB/redo_cntrl01/log1a.dbf','/oradata/DOCDB/redo_cntrl01/log1b.dbf') SIZE 50M,
  GROUP 2 ('/oradata/DOCDB/redo_cntrl02/log2a.dbf','/oradata/DOCDB/redo_cntrl02/log2b.dbf') SIZE 50M,
  GROUP 3 ('/oradata/DOCDB/redo_cntrl03/log3a.dbf','/oradata/DOCDB/redo_cntrl03/log3b.dbf') SIZE 50M
DATAFILE
'/oradata/DOCDB/data01/data/system01.dbf',
'/oradata/DOCDB/data01/data/tools01.dbf',
'/oradata/DOCDB/data01/data/data_lrg.dbf',
'/oradata/DOCDB/data01/data/indx_med.dbf',
'/oradata/DOCDB/data01/data/indx_lrg.dbf',
'/oradata/DOCDB/data01/data/undo01.dbf',
'/oradata/DOCDB/data01/data/sysaux01.dbf'
CHARACTER SET WE8ISO8859P1
;

12. set undo_management=MANUAL prior "ALTER DATABASE OPEN RESETLOGS;" therwise will have recovery issue.
        SQL> shutdown immediate
        SQL> set undo_management=MANUAL in your init file

13. open database in resetlogs
        SQL> STARTUP MOUNT PFILE=/app/oracle/diag/rdbms/docdb/dba/initDOCDB.ora
        SQL> show parameter undo
        SQL> ALTER DATABASE OPEN RESETLOGS;

14. temp file won't convert by rman. add tempfile
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/DOCDB/temp01/temp01.dbf'
     SIZE 8194M REUSE AUTOEXTEND on maxsize 20g;
   
14. drop undo tablespace
SQL> DROP TABLESPACE undo;

15. create a new undo tablespace
SQL> create UNDO tablespace undo datafile '/oradata/DOCDB/data01/data/undo01.dbf' size 2000M
           reuse AUTOEXTEND ON MAXSIZE 16g;

16. shutdown the database, change UNDO_MANAGEMENT=AUTO in your init file
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE PFILE='/app/oracle/diag/rdbms/docdb/dba/initDOCDB.ora'
SQL> show parameter undo
SQL> @@ ?/rdbms/admin/utlirp.sql
SQL> select owner, count(*) from all_objects where status = 'INVALID' group by owner;

17. put back necessary parameter(s) in init.ora and recreate spfile. Such as
*.optimizer_features_enable='10.2.0.4'

18. create SPFILE.
SQL> CREATE SPFILE='/app/oracle/diag/rdbms/docdb/DOCDB/pfile/spfileDOCDB.ora'
   from pfile='/app/oracle/diag/rdbms/docdb/DOCDB/pfile/initDOCDB.ora';

19. create password file
$ orapwd file=/app/oracle/diag/rdbms/docdb/DOCDB/pfile/orapwDOCDB password=XXXXXX entries=100

20. create soft links
  cp /app/oracle/diag/rdbms/docdb/dba/initDOCDB.ora /app/oracle/diag/rdbms/docdb/DOCDB/pfile/initDOCDB.ora

  ln -s /app/oracle/diag/rdbms/docdb/DOCDB/pfile/spfileDOCDB.ora /app/oracle/product/11.2.0.3/dbs
  ln -s /app/oracle/diag/rdbms/docdb/DOCDB/pfile/initDOCDB.ora /app/oracle/product/11.2.0.3/dbs
  ln -s /app/oracle/diag/rdbms/docdb/DOCDB/pfile/orapwDOCDB /app/oracle/product/11.2.0.3/dbs

21. copy tnsnames.ora to $ORACLE_HOME/network/admin (for database links, etc)

22. run a RMAN full backup.

Convert database from WE8ISO8859P1 to AL32UTF8

Below query will return the name of character set in the database:

select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

We can't simply use "ALTER DATABASE CHARACTER SET" to go from WE8ISO8859P1 or
WE8ISO8859P15 or WE8MSWIN1252 to Unicode AL32UTF8 (or UTF8) because AL32UTF8 is not a binary superset of any of these character sets.

For 10g database, the conversion is easier because the scan tool (csscan) was installed for you during the 10g installation. I followed Metalink Doc ID 260192.1 to do the conversion on a 10g database.

1. Run $ORACLE_HOME/rdbms/admin/csminst.sql by 'sys as sysdba' to create database objects and a user for the objects.
2. Check if you have no invalid code points in the current character set. Run below line by 'sys as sysdba':
csscan FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=WE8ISO8859P1 LOG=WE8check CAPTURE=Y ARRAY=1000000 PROCESS=2
The Scan Summary Report WE8check.txt shows only "Changeless data".
3. Check which rows contain data for which the code point will change. Run csscan:
csscan FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=AL32UTF8 LOG=WE8TOUTF8 CAPTURE=Y ARRAY=1000000 PROCESS=2

Part of the WE8TOUTF8.txt is

[Data Dictionary Conversion Summary]

Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- -----------
VARCHAR2 6,067,118 1 0 0
CHAR 448 0 0 0
LONG 151,405 0 0 0
CLOB 63,023 7,385 0 0
VARRAY 20,839 0 0 0
--------------------- ---------------- ---------------- ---------------- -----------
Total 6,302,833 7,386 0 0
Total in percentage 99.883% 0.117% 0.000% 0.000%

The data dictionary can not be safely migrated using the CSALTER script

[Application Data Conversion Summary]

Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- -----------
VARCHAR2 348,264,895 84,466 102 0
CHAR 339,986,561 114 195 0
LONG 1,270 0 0 0
CLOB 0 0 0 0
VARRAY 1,525 0 0 0
--------------------- ---------------- ---------------- ---------------- -----------
Total 688,254,251 84,580 297 0
Total in percentage 99.988% 0.012% 0.000% 0.000%


- Drop snapshot
SQL> select min(snap_id) min_snapid ,max(snap_id) max_snapid from dba_hist_snapshot
SQL> execute dbms_workload_repository.drop_snapshot_range(3737, 3905);

- Need to handle foreign keys (Doc ID 1039297.6)

dump() function

When there are special characters, like newline and end-of-line, stored in a column, it is unable to see them directly. Function dump() is the best tool in this situation:

select dump(column_name) from ...

If there is a 0 returned, it hits NULL control character (ASCII character 0, eg chr(0) ).

View other users' PL/SQL procedure code or debug

The best way to grant a role or other users to read your procedure code is

SQL> grant DEBUG ON your_id.PROC_name to A_ROLE_NAME;

In this way, all users in the Role are able to view the code, but can not EXECUTE or modify the procedure's code. It only works in 10g and above.

If a user wants to debug a PL/SQL procedure of his own schema in Microsoft Visual Studio, make below grant to the user as SYS:

SQL> grant DEBUG CONNECT SESSIOM to user_id; 

This grant will address below errors:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_DEBUG_JDWP",

Oracle CJQ0 Process and J000 Process

In Oracle alert log, you may see below message:

Starting background process CJQ0
Thu Feb 03 10:38:40 2011
CJQ0 started with pid=48, OS id=15990882
......
Thu Feb 03 11:19:38 2011
Stopping background process CJQ0

CJQ0 is a coordinator job queue (CJQ0) process to run scheduled jobs defined by DBMS_JOB package. Oracle slave processes (J000 – J999) executes the scheduled jobs. The CJQ0 process will be keeping track of the schedule and starts slave processes so as to execute the scheduled jobs.

Normally, parameter job_queue_processes is not set to zero as it will disable all job queue processing and stop the CJQ0 process. It is suggested to set job_queue_processes to a value higher than the maximum number of simultaneous jobs you expect to run.

In troubleshooting, you might kill the process at OS level by using below query to identify SID and SPID values:

SQL> SELECT A1.SID, A2.SPID FROM V$SESSION A1,V$PROCESS A2
WHERE A1.PADDR = A2.ADDR AND TYPE='BACKGROUND'
AND A1.PROGRAM LIKE '%CJQ%'

To get the description on all Oracle background processes, run below query in the database:

SQL> select name, description from v$bgprocess;