Insert Multi-million Rows into a Table

To load large volume of data into a table quickly, we shall turn off the logging on the table to reduce REDO log files. Below steps worked well:

SQL> alter table table_A nologging;
SQL> alter index table_A_ind nologging;

SQL> INSERT /*+ append */ INTO table_A
           SELECT /*+ parallel(T 4) */ col1, col2, clo3

              FROM table_another_T T

           WHERE  ......

SQL> commit;

-- option: to turn logging back on
-- SQL> alter table table_A logging;
-- SQL> alter index table_A_ind logging;

Before run this, make sure enough space is allocated to the tablespace where table_A resides.

======================================================
If data are loaded by creating the table (CTAS), the below code will do it:

SQL> CREATE TABLE table_A TABLESPACE tablesapce_name
           NOLOGGING
           as  SELECT /*+ parallel(T 4) */ * FROM table_another_T T;

Table table_A can be alerted to LOGGING if needed, after table creation.

======================================================
I do not think UPDATE statement can reduce REDO log by changing to NOLOGGING option on tables. One option is to turn off archivelog mode in the database.

======================================================
For database with mining log data, such as Oracle Streans (CDC) or Data Gard, logging may be required.  Two statements can check the status on database level and tablespace level:

SQL> SELECT force_logging FROM v$database;
SQL> SELECT tablespace_name, force_logging FROM dba_tablespaces; 

The default setup is "NO". They can be turned on or off by below statements:

SQL> ALTER DATABASE force logging;
SQL> ALTER TABLESPACE users FORCE LOGGING;

SQL> ALTER DATABASE NO force logging;
SQL> ALTER TABLESPACE users NO FORCE LOGGING; 

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.