Changing the CHARACTERSET of database from WE8MSWIN1252 to AL32UTF8

These are the steps followed for changing the database characterset from WE8MSWIN1252 to AL32UTF8

I have to create a new DB with correct CHARACTERSET(AL32UTF8) & import the database dump to it, So in order to avoid losing data we have performed the below steps

We have followed document Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) [ID 260192.1] for it============================
START OF SCREENSHOT
============================
SQL> set TERMOUT ON
SQL> set ECHO ON
SQL> spool csminst.log
SQL> drop user csmig cascade;
drop user csmig cascade
          *
ERROR at line 1:
ORA-01918: user 'CSMIG' does not exist

SQL> @?/rdbms/admin/csminst.sql
SQL> rem
SQL> rem $Header: csmig/sql/csminst.sql /main/29 2008/09/09 00:47:40 ssubrama Exp $ csminst.sql
SQL> rem
SQL> rem Copyright (c) 1988, 2008, Oracle. All rights reserved.
SQL> rem
SQL> rem NAME
SQL> rem   csminst.sql
SQL> rem DESCRIPTION
SQL> rem   Create tables for Database Character Set Migration Utility
SQL> rem NOTE
SQL> rem   This script must be run while connected as SYS
SQL> rem MODIFIED
SQL> rem   ssubrama     09/04/08 - bug 7047837 fix create user
SQL> rem   nli       07/25/08 - fix bug 7256242, add the ORADATA user
SQL> rem   ssubrama     06/09/08 - bug 7047837 cleanup csmig schema for security
SQL> rem   xpeng        10/18/07 - fix bug 6460895
SQL> rem   ssubrama     01/09/07 - bug 5738695 add copyright information
SQL> rem   nli       08/02/06 - bug 5372557: CSX support. Add a CNVTYPE column into CSMIG.CSM$COLUMNS
SQL> rem   ywu       07/14/04 - up version
SQL> rem   fayang       04/23/04 - add column UNNESTED in CSM$TABLES
SQL> rem   fayang       04/06/04 - add SCNCOL# in EXTABLES and add a view for EXTABLES
SQL> rem   ywu       02/19/04 - fix bug 3434808, delete hard code passwd.
SQL> rem   ywu       07/29/03 - grant privilege to system for external table
SQL> rem   ywu       05/30/03 - add a table for data dictionary
SQL> rem   ywu       05/09/03 - add led parameter to csm$errors
SQL> rem   ywu       10/22/02 - add another error for codepoint exceed
SQL> rem   ywu       08/30/02 - up version
SQL> rem   ywu       07/01/02 - add size information
SQL> rem   ywu       07/02/02 - add resumable
SQL> rem   plinsley     01/24/02 - add include/exclude
SQL> rem   plinsley     04/02/01 - up version
SQL> rem   plinsley     03/23/01 - #1509940
SQL> rem   plinsley     03/26/01 - update views
SQL> rem   plinsley     12/15/00 - remove order by from view
SQL> rem   plinsley     11/03/00 - Add converter process column
SQL> rem   plinsley     10/04/00 - split tables that cross files
SQL> rem   plinsley     09/21/00 - Long ROWIDs in id
SQL> rem   plinsley     08/09/00 - Adding constraint dependency handling
SQL> rem   mtozawa      06/29/00 - add csmv$ views
SQL> rem   mtozawa      06/27/00 - add browid to csm$columns
SQL> rem   mtozawa      06/02/00 - specify the storage clause for csm$errors
SQL> rem   mtozawa      05/26/00 - bug1314547:optimize split mechanism, add blocks
SQL> rem   mtozawa      05/19/00 - Change table names from SCN$* to CSM$*
SQL> rem   mtozawa      03/08/00 - add SPLIT support
SQL> rem   mtozawa      12/21/99 - add property column to SCN$TABLES for IOT
SQL> rem   mtozawa      11/05/99 - remove storage params from SCN$ERRORS
SQL> rem   mtozawa      11/04/99 - add maxsiz to SCN$COLUMNS
SQL> rem   mtozawa      09/26/99 - Creation
SQL> rem
SQL>
SQL>
SQL> rem *****************************************************************
SQL> rem  The user CSMIG owns tables and procedures of Database Scanner
SQL> rem *****************************************************************
SQL> WHENEVER SQLERROR EXIT
SQL>
SQL> create user csmig identified by csmig password expire account lock
  2  /
User created.
SQL>
SQL> WHENEVER SQLERROR CONTINUE
SQL>
SQL> grant select on sys.obj$ to csmig
  2  /
Grant succeeded.
SQL> grant select on sys.col$ to csmig
  2  /
Grant succeeded.
SQL> grant select on sys.icol$ to csmig
  2  /
Grant succeeded.
SQL> grant select on sys.ind$ to csmig
  2  /
Grant succeeded.
SQL> grant select on sys.cdef$ to csmig
  2  /
Grant succeeded.
SQL> grant select on sys.con$ to csmig
  2  /
Grant succeeded.
SQL> grant select on sys.trigger$ to csmig
  2  /
Grant succeeded.
SQL> rem *****************************************************************
SQL> rem  DBA MUST ASSIGN PROPER TABLESPACE TO CSMIG
SQL> rem *****************************************************************
SQL> alter user csmig default tablespace SYSTEM quota unlimited on SYSTEM
  2  /
User altered.
SQL>
SQL> rem *****************************************************************
SQL> rem  Add version information for csm$* schema
SQL> rem  The schema version must be bumped up as csm$* schema get updated.
SQL> rem  VERSION HISTORY:
SQL> rem    1 ... 8.1.7
SQL> rem *****************************************************************
SQL> insert into sys.props$
  2  select 'NLS_CSMIG_SCHEMA_VERSION', 'x',
  3         'Character set migration utiltiy schema version #'
  4    from dual
  5   where not exists
  6         (select 'x' from sys.props$ where name = 'NLS_CSMIG_SCHEMA_VERSION')
  7  /
1 row created.
SQL> update sys.props$ set value$ = 5 where name = 'NLS_CSMIG_SCHEMA_VERSION'
  2  /
1 row updated.
SQL> rem *****************************************************************
SQL> rem  Database Scanner leaves the last scan parameters in CSM$PARAMETERS
SQL> rem  Each background process will read scan parameters from here.
SQL> rem *****************************************************************
SQL> create table csmig.csm$parameters
  2  ( name      varchar2(30) not null,                         /* paraneter name */
  3    value     varchar2(80) not null                        /* parameter value */
  4  )
  5  /
Table created.
SQL> drop public synonym csm$parameters
  2  /
drop public synonym csm$parameters
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$parameters for csmig.csm$parameters
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  Database Scanner saves the query string in CSM$QUERY
SQL> rem *****************************************************************
SQL> create table csmig.csm$query
  2  (
  3    value    clob not null                       /* query value */
  4  )
  5  /
Table created.
SQL> drop public synonym csm$query
  2  /
drop public synonym csm$query
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$query for csmig.csm$query
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  Database Scanner enumerate all tables need to be scanned
SQL> rem  Each background process will pick up a row from here for table to scan
SQL> rem *****************************************************************
SQL> create table csmig.csm$tables
  2  ( usr#      number not null,                   /* user id of the table owner */
  3    obj#      number not null,                       /* object id of the table */
  4    minrowid  rowid,          /* Minimum rowid of the split range of the table */
  5    maxrowid  rowid,          /* Maximum rowid of the split range of the table */
  6    property  number,                                        /* table property */
  7    blocks    number,                   /* number of blocks used by this table */
  8    files     number,                    /* number of files used by this table */
  9    who       number,              /* internal thread id who scanned the table */
 10    whoconv   number,            /* internal thread id who converted the table */
 11    lngconv   number,             /* internal thread id who converted long col */
 12    scnstart  date,                                 /* time table scan started */
 13    scnend    date,                               /* time table scan completed */
 14    scncols   number,                       /* number of columns to be scanned */
 15    scnrows   number,                                /* number of rows scanned */
 16    cnvstart  date,                              /* time table convert started */
 17    cnvend    date,                            /* time table convert completed */
 18    lngstart  date,                         /* time table convert long started */
 19    lngend    date,                       /* time table convert long completed */
 20    cnvcols   number,                     /* number of columns to be converted */
 21    cnvrows   number,                        /* number of rows to be converted */
 22    lngrows   number,               /* number of rows  of long to be converted */
 23    addsize   number,
 24    lastupd   rowid,                                      /* ROWID lastupdated */
 25    pstcvrows number,                      /* how many rows have been converted */
 26    lastupdlg rowid,                                      /* ROWID lastupdated */
 27    pstcvrowslg  number,                   /* how many rows have been converted */
 28    unnested  number                       /* if this table is unnested or not */
 29  )
 30  /
Table created.
SQL> drop public synonym csm$tables
  2  /
drop public synonym csm$tables
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$tables for csmig.csm$tables
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$COLUMNS contains statistic information of column data
SQL> rem *****************************************************************
SQL> create table csmig.csm$columns
  2  ( usr#       number not null,                  /* user id of the table owner */
  3    obj#       number not null,                      /* object id of the table */
  4    browid     rowid,                        /* rowid of the row in csm$tables */
  5    col#       number not null,                                   /* column id */
  6    intcol#    number not null,                /* internal column id (for ADT) */
  7    dty#       number not null,                            /* column data type */
  8    frm#       number not null,                          /* character set form */
  9    numrows    number not null,                /* number of rows in this table */
 10    nulcnt     number not null,                    /* number of null cell data */
 11    cnvcnt     number not null,    /* number of cell data that need to convert */
 12    cnvtype    number default 0 not null,                      /* convert type */
 13                 /* 1 = data in data dictionary, and can be converted by csconv*/
 14    errcnt     number not null,      /* number of cell data that has exception */
 15    sizerr     number not null, /* number of cell data that exceed column size */
 16    cnverr     number not null, /* number of cell data that undergo lossy conv.*/
 17    maxsiz     number not null,               /* max post conversion data size */
 18    chrsiz     number not null,            /* truncation due to char semantics */
 19    cnvsuc     number,                         /* cells converted successfully */
 20    cnvtrn     number,                      /* cells converted with truncation */
 21    cnvlos     number,                    /* cells converted with lossy result */
 22    cnvfai     number                               /* cells failed to convert */
 23  )
 24  /
Table created.
SQL> drop public synonym csm$columns
  2  /
drop public synonym csm$columns
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$columns for csmig.csm$columns
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$EXTABLES contains exception tables
SQL> rem *****************************************************************
SQL> create table csmig.csm$extables
  2  ( usr#       number not null,                  /* user id of the table owner */
  3    obj#       number not null,                      /* object id of the table */
  4    col#       number,                                            /* column id */
  5    intcol#    number,                         /* internal column id (for ADT) */
  6    dty#       number,                                     /* column data type */
  7    frm#       number,                                   /* character set form */
  8    property   number default 0 not null,                   /* property of row */
  9    scncol#    number                                /* column id to be scaned */
 10  )
 11  /
Table created.
SQL> drop public synonym csm$extables
  2  /
drop public synonym csm$extables
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$extables for csmig.csm$extables
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$ERRORS contains individual exception information
SQL> rem *****************************************************************
SQL> create table csmig.csm$errors
  2  ( err#       number not null,                              /* exception type */
  3    usr#       number not null,            /* user id of the object/data owner */
  4    obj#       number not null,                                  /* object id  */
  5    col#       number,                                 /* column id / position */
  6    intcol#    number,                         /* internal column id (for ADT) */
  7    typ#       number,                       /* column data type / object type */
  8    frm#       number,                                   /* character set form */
  9    cnvsize    number,                            /* post conversion data size */
 10    id$        varchar2(1000),              /* rowid / name to identify object */
 11    csidleds   number,                      /* number of charset id from led   */
 12    csidled1   number,                      /* first charset id from led       */
 13    csidled2   number,                      /* second charset id from led      */
 14    csidled3   number,                      /* third charset id from led       */
 15    langidleds number,                      /* number of language id from led  */
 16    langidled1 number,                      /* first language id from led      */
 17    langidled2 number,                      /* second language id from led     */
 18    langidled3 number                        /* third language id from led     */
 19  )
 20  pctfree 0 pctused 99
 21  storage(next 100K maxextents unlimited pctincrease 0)
 22  /
Table created.
SQL> drop public synonym csm$errors
  2  /
drop public synonym csm$errors
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$errors for csmig.csm$errors
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$LANGID contains summary information
SQL> rem *****************************************************************
SQL> create table csmig.csm$langid
  2  ( obj#       number not null,                                  /* object id  */
  3    langid     number,                                 /* language id from led */
  4    count      number
  5  )
  6  /
Table created.
SQL> drop public synonym csm$langid
  2  /
drop public synonym csm$langid
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$langid for csmig.csm$langid
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$CHARSETID contains summary information
SQL> rem *****************************************************************
SQL> create table csmig.csm$charsetid
  2  ( obj#       number not null,                                  /* object id  */
  3    csid       number,                                 /* language id from led */
  4    count      number
  5  )
  6  /
Table created.
SQL> drop public synonym csm$charsetid
  2  /
drop public synonym csm$charsetid
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$charsetid for csmig.csm$charsetid
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$INDEXES lists indexes to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$indexes
  2  ( obj#       number not null                       /* object id of the index */
  3  )
  4  /
Table created.
SQL> drop public synonym csm$indexes
  2  /
drop public synonym csm$indexes
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$indexes for csmig.csm$indexes
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$CONSTRAINTS lists constraints to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$constraints
  2  (
  3    rid        number not null,                   /* root constraint id         */
  4    lvl        number,                            /* constraint level           */
  5    con#       number not null                    /* internal constraint number */
  6  )
  7  /
Table created.
SQL> drop public synonym csm$constraints
  2  /
drop public synonym csm$constraints
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$constraints for csmig.csm$constraints
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$TRIGGERS lists triggers to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$triggers
  2  ( obj#       number not null                     /* object id of the trigger */
  3  )
  4  /
Table created.
SQL> drop public synonym csm$triggers
  2  /
drop public synonym csm$triggers
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$triggers for csmig.csm$triggers
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSM$DICTUSERS lists triggers to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$dictusers
  2  ( user#       number not null,                /* usre id for all data dictionary */
  3    username    varchar2(30)
  4  )
  5  /
Table created.
SQL> drop public synonym csm$dictusers
  2  /
drop public synonym csm$dictusers
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$dictusers for csmig.csm$dictusers
  2  /
Synonym created.
SQL> insert into csmig.csm$dictusers
  2    select distinct u.user_id, u.username from all_users u, sys.ku_noexp_view k
  3    where (k.OBJ_TYPE='USER' and k.name=u.username) or (u.username in ('SYSTEM', 'ORDDATA'))
  4  /
17 rows created.
SQL> rem
SQL> rem  define CSMV$ views
SQL> rem
SQL> rem *****************************************************************
SQL> rem  CSMV$TABLES lists tables (to be) scanned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$tables
  2        (owner_id, owner_name, table_id, table_name, MIN_ROWID, MAX_ROWID,
  3         BLOCKS, SCAN_COLUMNS, SCAN_ROWS, SCAN_START, SCAN_END)
  4      as
  5  select c.usr#, u.username, c.obj#, o.name,
  6         rowidtochar(c.minrowid), rowidtochar(c.maxrowid),
  7         c.blocks, c.scncols, c.scnrows,
  8         to_char(c.scnstart,'hh24:mi:ss'), to_char(c.scnend,'hh24:mi:ss')
  9    from csm$tables c, all_users u, sys.obj$ o
 10   where c.usr#=u.user_id and c.obj#=o.obj#
 11  /
View created.
SQL> drop public synonym csmv$tables
  2  /
drop public synonym csmv$tables
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$tables for csmig.csmv$tables
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSMV$COLUMNS lists columns scanned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$columns
  2       (owner_id, owner_name, table_id, table_name, column_id, column_intid,
  3        column_name, column_type, total_rows, null_rows, conv_rows, error_rows,
  4        exceed_size_rows, data_loss_rows, cs_exceed_size_rows, max_post_convert_size)
  5      as
  6  select c.usr#, u.username, c.obj#, o.name, c.col#, c.intcol#, co.name,
  7         decode(c.frm#, 2, 'N', '') ||
  8         decode(c.dty#, 1, 'VARCHAR2', 8, 'LONG', 96, 'CHAR', 112, 'CLOB',''),
  9         c.numrows, c.nulcnt, c.cnvcnt, c.errcnt, c.sizerr, c.cnverr, c.chrsiz, c.maxsiz
 10    from csm$columns c, all_users u, sys.obj$ o, sys.col$ co
 11   where c.usr#=u.user_id and c.obj#=o.obj# and c.obj#=co.obj#
 12     and c.col#=co.col# and c.intcol#=co.intcol#
 13  /
View created.
SQL> drop public synonym csmv$columns
  2  /
drop public synonym csmv$columns
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$columns for csmig.csmv$columns
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSMV$ERRORS lists exceptional data cell information
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$errors
  2        (owner_id, owner_name, table_id, table_name,
  3         column_id, column_intid, column_name, data_rowid,
  4         column_type, error_type)
  5      as
  6  select e.usr#, u.username, e.obj#, o.name,
  7         e.col#, e.intcol#, c.name, e.id$,
  8         decode(e.frm#, 2, 'N', '') ||
  9         decode(e.typ#, 1, 'VARCHAR2', 8, 'LONG', 96, 'CHAR', 112, 'CLOB'),
 10         decode(e.err#, 0, 'CONVERTIBLE', 1, 'EXCEED_SIZE', 2, 'DATA_LOSS',
 11                        3, 'CS_EXCEED_SIZE')
 12    from csm$errors e, all_users u, sys.obj$ o, sys.col$ c
 13   where e.usr#=u.user_id and e.obj#=o.obj#
 14     and e.obj#=c.obj# and e.col#=e.col# and e.intcol#=c.intcol#
 15  /
View created.
SQL> drop public synonym csmv$errors
  2  /
drop public synonym csmv$errors
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$errors for csmig.csmv$errors
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSMV$INDEXES lists all indexes to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$indexes
  2        (index_owner_id, index_owner_name, index_id, index_name,
  3         index_status#, index_status,
  4         table_owner_id, table_owner_name, table_id, table_name,
  5         column_id, column_intid, column_name)
  6      as
  7  select iu.user_id, iu.username, io.obj#, io.name, id.flags,
  8         decode(bitand(id.flags,1), 1, 'UNUSABLE', 'VALID'),
  9         bu.user_id, bu.username, bo.obj#, bo.name,
 10         cl.col#, cl.intcol#, cl.name
 11    from csm$indexes ci, sys.icol$ ic, sys.ind$ id, all_users iu,
 12         sys.obj$ io, all_users bu, sys.obj$ bo, sys.col$ cl
 13   where ci.obj#=ic.obj# and ci.obj#=id.obj#
 14     and ci.obj#=io.obj# and io.owner#=iu.user_id
 15     and ic.bo# =bo.obj# and bo.owner#=bu.user_id
 16     and ic.bo#=cl.obj# and ic.col#=cl.col# and ic.intcol#=cl.intcol#
 17  /
View created.
SQL> drop public synonym csmv$indexes
  2  /
drop public synonym csmv$indexes
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$indexes for csmig.csmv$indexes
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSMV$CONSTRAINTS lists all constraints to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$constraints
  2        (owner_id, owner_name, constraint_id, constraint_name,
  3         constraint_type#, constraint_type, table_id, table_name,
  4         constraint_rid, constraint_level)
  5      as
  6  select c.owner#, u.username, c.con#, c.name, cd.type#,
  7         decode(cd.type#, 1, 'CHECK', 2, 'PRIMARY_KEY', 3, 'UNIQUE',
  8                          4, 'REFERENTIAL', 'UNKNOWN'),
  9         o.obj#, o.name, cc.rid, cc.lvl
 10    from csm$constraints cc, sys.cdef$ cd, sys.con$ c, all_users u, sys.obj$ o
 11   where cc.con#=cd.con# and cc.con#=c.con#
 12     and c.owner#=u.user_id and cd.obj#=o.obj#
 13  /
View created.
SQL> drop public synonym csmv$constraints
  2  /
drop public synonym csmv$constraints
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$constraints for csmig.csmv$constraints
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSMV$TRIGGERS lists all triggers to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$triggers
  2        (trigger_owner_id, trigger_owner_name, trigger_id, trigger_name,
  3         table_owner_id, table_owner_name, table_id, table_name)
  4      as
  5  select ru.user_id, ru.username, tr.obj#, ro.name, bu.user_id,
  6         bu.username, tr.baseobject, bo.name
  7    from csm$triggers ct, sys.trigger$ tr, all_users ru, sys.obj$ ro,
  8         all_users bu, sys.obj$ bo
  9   where ct.obj#=tr.obj# and ct.obj#=ro.obj# and ro.owner#=ru.user_id
 10     and tr.baseobject=bo.obj# and bu.user_id=bo.owner#
 11  /
View created.
SQL> drop public synonym csmv$triggers
  2  /
drop public synonym csmv$triggers
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$triggers for csmig.csmv$triggers
  2  /
Synonym created.
SQL> rem *****************************************************************
SQL> rem  CSMV$EXTABLES lists all distinct objects to be scaned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$extables
  2        (obj#, usr#, property)
  3      as
  4  select     distinct(obj#), usr#, property
  5    from csm$extables where property=0;
View created.
SQL> /
View created.
SQL> rem *****************************************************************
SQL> rem  SYS.CSMV$KTFBUE wraps the fixed table sys.x$ktfbue in data
SQL> rem  dictionary for users only with DBA privilege to access
SQL> rem *****************************************************************
SQL> create or replace view sys.csmv$ktfbue as select * from sys.x$ktfbue;
View created.
SQL> /
View created.
SQL> grant select on sys.csmv$ktfbue to dba;
Grant succeeded.
SQL> /
Grant succeeded.
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@localhost ~]$ echo $ORACLE_SID
labdb
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 23 19:04:02 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> conn csmig/csmig
ERROR:
ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.
SQL> show user
USER is ""
SQL> conn / as sysdba
Connected.
SQL> alter account csmig unlock;
alter account csmig unlock
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

SQL> alter user csmig account unlock;
User altered.
SQL> conn csmig/csmig
ERROR:
ORA-28001: the password has expired

Changing password for csmig
New password:
Retype new password:
ERROR:
ORA-01045: user CSMIG lacks CREATE SESSION privilege; logon denied

Password changed
Warning: You are no longer connected to ORACLE.
SQL> show user
USER is ""
SQL> conn / as sysdba
Connected.
SQL> alter user csmig identified by oracle;
User altered.
SQL> conn csmig/oracle
ERROR:
ORA-01045: user CSMIG lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> exit\
SP2-0584: EXIT variable "\" was non-numeric
Usage: { EXIT | QUIT }  [ SUCCESS | FAILURE | WARNING | n |
       <variable> | :<bindvariable> ]  [ COMMIT | ROLLBACK ]
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ which csscaner
/usr/bin/which: no csscaner in (/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/oracle11201/bin/)
[oracle@localhost ~]$ which csscan
/u01/app/oracle/oracle11201/bin/csscan
[oracle@localhost ~]$ csscan TABLE='(SYS.SQL_VERSION$)' FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000
csscan: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory
[oracle@localhost ~]$ csscan
csscan: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory
[oracle@localhost ~]$ ldd `which sysresv`
        linux-gate.so.1 =>  (0x00696000)
        libclntsh.so.11.1 => /u01/app/oracle/oracle11201/lib/libclntsh.so.11.1 (0x00697000)
        libnnz11.so => /u01/app/oracle/oracle11201/lib/libnnz11.so (0x00110000)
        libdl.so.2 => /lib/libdl.so.2 (0x0035d000)
        libm.so.6 => /lib/libm.so.6 (0x00361000)
        libpthread.so.0 => /lib/libpthread.so.0 (0x00388000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x003a0000)
        libc.so.6 => /lib/libc.so.6 (0x003b7000)
        libaio.so.1 => /usr/lib/libaio.so.1 (0x00508000)
        /lib/ld-linux.so.2 (0x00590000)
[oracle@localhost ~]$ echo $ORACLE_HOME
/u01/app/oracle/oracle11201
[oracle@localhost ~]$ cat /etc/oratab
#

# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM:/u01/app/grid11201:N
labdb:/u01/app/oracle/oracle11201:N
[oracle@localhost ~]$ echo $PATH
/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/oracle11201/bin/
[oracle@localhost ~]$ echo $LD_LIBRARY_PATH
[oracle@localhost ~]$ LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib; export LD_LIBRARY_PATH
[oracle@localhost ~]$ echo $LD_LIBRARY_PATH
/u01/app/oracle/oracle11201/lib:/usr/lib
[oracle@localhost ~]$ echo $CLASSPATH
[oracle@localhost ~]$ csscan

Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Apr 23 20:00:46 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username:
Password:

ORA-01017: invalid username/password; logon denied
Scanner terminated unsuccessfully.
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ csscan TABLE='(SYS.SQL_VERSION$)' FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000

Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Apr 23 20:01:51 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Enumerating table to scan...
. process 1 scanning SYS.SQL_VERSION$[AAAACUAABAAAASYAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
[oracle@localhost ~]$ ls -ltr
total 80
drwxr-x--- 3 oracle oinstall  4096 Feb 25 19:55 oradiag_oracle
drwxr-xr-x 2 oracle oinstall  4096 Mar 21 00:02 backup
-rw-r--r-- 1 oracle oinstall 17710 Mar 29 18:38 dgdiag_phystby_labdb_Mar29_0638.out
-rw-r--r-- 1 oracle oinstall    69 Apr 22 08:42 afiedt.buf
-rw-r--r-- 1 oracle oinstall 25279 Apr 23 19:02 csminst.log
-rw-r--r-- 1 oracle oinstall   270 Apr 23 20:00 scan.out
-rw-r--r-- 1 oracle oinstall  7717 Apr 23 20:02 instchkc.txt
-rw-r--r-- 1 oracle oinstall   582 Apr 23 20:02 instchkc.out
-rw-r--r-- 1 oracle oinstall  1357 Apr 23 20:02 instchkc.err
[oracle@localhost ~]$ view instchkc.err

12.d) When using (full) export/import to go to a new/other AL32UTF8 database and there was NO "Truncation" data, only

"Convertible" and "Changeless" in the csscan done in point 4: expdp full=Y directory=TEST_DIR dumpfile=Full_labdb.dmp logfile=expdpFull.log
impdp full=Y directory=TEST_DIR dumpfile=Full_labdb.dmp logfile=impdpDB_testDB.log table_exists_action=replace

Disaster recovery using RMAN(losing both spfile & control file)

Hi ,
Here i had simulated a situation by manually deleting the spfile & control files from ASM location(we need to shutdown database in order to be able to delete control files)

Before this we need to check for the RMAN backup details.(backup sets)

The steps i followed are as below
========================


[oracle@localhost ~]$ ps -fe|grep pmon
grid      4157     1  0 16:18 ?        00:00:00 asm_pmon_+ASM
oracle    4405     1  0 16:25 ?        00:00:01 ora_pmon_LABDBSBY
oracle   13004 12464  0 20:46 pts/5    00:00:00 grep pmon
[oracle@localhost ~]$ export ORACLE_SID=labdb
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 16 20:51:16 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> set dbid=1788478824

executing command: SET DBID

RMAN> startup force nomount

startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/labdb/spfilelabdb.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/labdb/spfilelabdb.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/labdb/spfilelabdb.ora
ORA-17503: ksfdopn:2 Failed to open file +DATA/labdb/spfilelabdb.ora
ORA-15173: entry 'spfilelabdb.ora' does not exist in directory 'labdb'
ORA-06512: at line 4

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159019008 bytes

Fixed Size                     1335192 bytes
Variable Size                 75497576 bytes
Database Buffers              79691776 bytes
Redo Buffers                   2494464 bytes

RMAN> restore spfile from autobackup;

Starting restore at 16-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120416
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120415
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120414
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120413
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120412
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120411
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120410
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/16/2012 21:11:30
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN> restore spfile to '+DATA/LABDB/spfilelabdb.ora' from '+DATA/labdb/autobackup/2012_04_16/s_780771691.600.780771707';

Starting restore at 16-APR-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP +DATA/labdb/autobackup/2012_04_16/s_780771691.600.780771707
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-APR-12

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup force nomount;

Oracle instance started

Total System Global Area     803500032 bytes

Fixed Size                     1339516 bytes
Variable Size                222302084 bytes
Database Buffers             574619648 bytes
Redo Buffers                   5238784 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 16-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

recovery area destination: +DATA
database name (or database unique name) used for search: LABDB
channel ORA_DISK_1: AUTOBACKUP +data/LABDB/AUTOBACKUP/2012_04_16/s_780771691.600.780771707 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120416
channel ORA_DISK_1: restoring control file from AUTOBACKUP +data/LABDB/AUTOBACKUP/2012_04_16/s_780771691.600.780771707
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATA/labdb/controlfile/current.260.780787841
Finished restore at 16-APR-12

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 16-APR-12
Starting implicit crosscheck backup at 16-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 16-APR-12

Starting implicit crosscheck copy at 16-APR-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-APR-12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +data/LABDB/AUTOBACKUP/2012_04_16/s_780771691.600.780771707
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_211.602.780771917
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_212.603.780772319
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_213.606.780772735
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_214.610.780773129
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_215.612.780774801
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_216.613.780774867
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_217.616.780775293
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_218.617.780775679
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_219.620.780776059
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_220.622.780776441
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_221.623.780776827
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_222.625.780777205
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_223.628.780777587
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_224.630.780777965
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_225.631.780778341
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_226.634.780778727
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_227.635.780779109
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_228.638.780779499
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_229.640.780779879
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_230.641.780780261
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_231.643.780780649
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_232.646.780781035
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_233.647.780781419
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_234.650.780781805
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_235.652.780782187
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_236.653.780782567
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_237.655.780782953
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_238.658.780783337
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_239.659.780783729
File Name: +data/LABDB/ARCHIVELOG/2012_04_16/thread_1_seq_240.662.780784119

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/labdb/datafile/system.256.776203549
channel ORA_DISK_1: restoring datafile 00002 to +DATA/labdb/datafile/sysaux.257.776203551
channel ORA_DISK_1: restoring datafile 00003 to +DATA/labdb/datafile/undotbs1.258.776203551
channel ORA_DISK_1: restoring datafile 00004 to +DATA/labdb/datafile/users.259.776203551
channel ORA_DISK_1: reading from backup piece +DATA/labdb/backupset/2012_04_16/nnndf0_tag20120416t165621_0.597.780771385
channel ORA_DISK_1: piece handle=+DATA/labdb/backupset/2012_04_16/nnndf0_tag20120416t165621_0.597.780771385 tag=TAG20120416T165621
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:59
Finished restore at 16-APR-12

RMAN> recover database;

Starting recover at 16-APR-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 210 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_210.598.780771501
archived log for thread 1 with sequence 211 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_211.602.780771917
archived log for thread 1 with sequence 212 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_212.603.780772319
archived log for thread 1 with sequence 213 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_213.606.780772735
archived log for thread 1 with sequence 214 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_214.610.780773129
archived log for thread 1 with sequence 215 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_215.612.780774801
archived log for thread 1 with sequence 216 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_216.613.780774867
archived log for thread 1 with sequence 217 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_217.616.780775293
archived log for thread 1 with sequence 218 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_218.617.780775679
archived log for thread 1 with sequence 219 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_219.620.780776059
archived log for thread 1 with sequence 220 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_220.622.780776441
archived log for thread 1 with sequence 221 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_221.623.780776827
archived log for thread 1 with sequence 222 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_222.625.780777205
archived log for thread 1 with sequence 223 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_223.628.780777587
archived log for thread 1 with sequence 224 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_224.630.780777965
archived log for thread 1 with sequence 225 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_225.631.780778341
archived log for thread 1 with sequence 226 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_226.634.780778727
archived log for thread 1 with sequence 227 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_227.635.780779109
archived log for thread 1 with sequence 228 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_228.638.780779499
archived log for thread 1 with sequence 229 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_229.640.780779879
archived log for thread 1 with sequence 230 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_230.641.780780261
archived log for thread 1 with sequence 231 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_231.643.780780649
archived log for thread 1 with sequence 232 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_232.646.780781035
archived log for thread 1 with sequence 233 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_233.647.780781419
archived log for thread 1 with sequence 234 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_234.650.780781805
archived log for thread 1 with sequence 235 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_235.652.780782187
archived log for thread 1 with sequence 236 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_236.653.780782567
archived log for thread 1 with sequence 237 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_237.655.780782953
archived log for thread 1 with sequence 238 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_238.658.780783337
archived log for thread 1 with sequence 239 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_239.659.780783729
archived log for thread 1 with sequence 240 is already on disk as file +DATA/labdb/archivelog/2012_04_16/thread_1_seq_240.662.780784119
archived log for thread 1 with sequence 241 is already on disk as file +DATA/labdb/onlinelog/group_3.261.779847493
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_210.598.780771501 thread=1 sequence=210
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_211.602.780771917 thread=1 sequence=211
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_212.603.780772319 thread=1 sequence=212
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_213.606.780772735 thread=1 sequence=213
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_214.610.780773129 thread=1 sequence=214
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_215.612.780774801 thread=1 sequence=215
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_216.613.780774867 thread=1 sequence=216
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_217.616.780775293 thread=1 sequence=217
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_218.617.780775679 thread=1 sequence=218
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_219.620.780776059 thread=1 sequence=219
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_220.622.780776441 thread=1 sequence=220
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_221.623.780776827 thread=1 sequence=221
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_222.625.780777205 thread=1 sequence=222
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_223.628.780777587 thread=1 sequence=223
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_224.630.780777965 thread=1 sequence=224
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_225.631.780778341 thread=1 sequence=225
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_226.634.780778727 thread=1 sequence=226
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_227.635.780779109 thread=1 sequence=227
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_228.638.780779499 thread=1 sequence=228
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_229.640.780779879 thread=1 sequence=229
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_230.641.780780261 thread=1 sequence=230
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_231.643.780780649 thread=1 sequence=231
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_232.646.780781035 thread=1 sequence=232
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_233.647.780781419 thread=1 sequence=233
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_234.650.780781805 thread=1 sequence=234
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_235.652.780782187 thread=1 sequence=235
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_236.653.780782567 thread=1 sequence=236
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_237.655.780782953 thread=1 sequence=237
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_238.658.780783337 thread=1 sequence=238
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_239.659.780783729 thread=1 sequence=239
archived log file name=+DATA/labdb/archivelog/2012_04_16/thread_1_seq_240.662.780784119 thread=1 sequence=240
archived log file name=+DATA/labdb/onlinelog/group_3.261.779847493 thread=1 sequence=241
media recovery complete, elapsed time: 00:00:32
Finished recover at 16-APR-12

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/16/2012 21:54:02
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

database opened

RMAN>

Creating single instance standby from 2 node RAC in AIX 6.1

Step1: Create the requires pfiles for both primary & standby

Sample primary Init file
===============================
T24TRAIN1.__db_cache_size=1342177280
T24TRAIN2.__db_cache_size=1358954496
T24TRAIN1.__java_pool_size=16777216
T24TRAIN2.__java_pool_size=16777216
T24TRAIN1.__large_pool_size=16777216
T24TRAIN2.__large_pool_size=16777216
T24TRAIN1.__pga_aggregate_target=1677721600
T24TRAIN2.__pga_aggregate_target=1677721600
T24TRAIN1.__sga_target=2516582400
T24TRAIN2.__sga_target=2516582400
T24TRAIN1.__shared_io_pool_size=0
T24TRAIN2.__shared_io_pool_size=0
T24TRAIN1.__shared_pool_size=1107296256
T24TRAIN2.__shared_pool_size=1090519040
T24TRAIN1.__streams_pool_size=0
T24TRAIN2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/T24TRAIN/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/t24train/controlfile/current.273.778262941','+DATA/t24train/controlfile/current.272.778262941'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_recovery_file_dest_size=319815680000
*.db_recovery_file_dest='+ARCHIVE'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=T24TRAINXDB)'
T24TRAIN1.instance_number=1
T24TRAIN2.instance_number=2
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=4194304000
*.open_cursors=300
*.processes=5000
*.remote_listener='t24db_scan.cbe.local:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=5505
T24TRAIN2.thread=2
T24TRAIN1.thread=1
T24TRAIN2.undo_tablespace='UNDOTBS2'
T24TRAIN1.undo_tablespace='UNDOTBS1'
#parameters spcific to Standby database
*.db_name='T24TRAIN'
*.DB_UNIQUE_NAME='T24TRAIN'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(T24TRAIN,T24TRAINSBY)'
*.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=T24TRAIN'
*.LOG_ARCHIVE_DEST_2='SERVICE=T24TRAINSBY ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=T24TRAINSBY'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=8
*.FAL_SERVER=T24TRAINSBY
*.DB_FILE_NAME_CONVERT='+DATA/T24TRAINSBY/,'+DATA/T24TRAIN/'
*.LOG_FILE_NAME_CONVERT='+DATA/T24TRAINSBY/','+DATA/T24TRAIN/'
*.STANDBY_FILE_MANAGEMENT=AUTO

Sample standby INIT file
===================
audit_file_dest='/u01/app/oracle/admin/T24TRAINSBY/adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='+DATA/T24TRAINSBY/CONTROLFILE/control01.ctl','+DATA/T24TRAINSBY/CONTROLFILE/control02.ctl'
db_block_size=8192
db_create_file_dest='+DATA'
db_domain=''
db_recovery_file_dest_size=319815680000
db_recovery_file_dest='+ARCHIVE'
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=T24TRAINXDB)'
log_archive_format='%t_%s_%r.dbf'
memory_target=4194304000
open_cursors=300
processes=5000
remote_login_passwordfile='exclusive'
sessions=5505
#parameters spcific to Standby database
db_name='T24TRAIN'
DB_UNIQUE_NAME='T24TRAINSBY'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(T24TRAIN,T24TRAINSBY)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=T24TRAINSBY'
LOG_ARCHIVE_DEST_2='SERVICE=T24TRAIN ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=T24TRAIN'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=8
FAL_SERVER=T24TRAIN
DB_FILE_NAME_CONVERT='+DATA/T24TRAIN','+DATA/T24TRAINSBY/'
LOG_FILE_NAME_CONVERT='+DATA/T24TRAIN','+DATA/T24TRAINSBY/'
STANDBY_FILE_MANAGEMENT=AUTO

Step2: Start primary with the modified pfile if it is ASM stored pfile

SQL> shutdown immediate
SQL>exit
SQL>export ORACLE_SID=<primary_db_instance_name>
SQL>sqlplus / as sysdba
SQL>startup nomount pfile='/tmp/<modified_pfile>.ora'
SQL>create spfile='<ASM_PATH>' from pfile='/tmp/<modified_pfile>.ora'
SQL> exit

<convert pfile to spfile on ASM storage>
go to $ORACLE_HOME/dbs/  find the corresponding init.ora file for primary DB, crosscheck the contents with correct ASM path for spfile or modify it.

Step3:
Create necessary standby redo log files. e.g. in our env we have 2 threads each with 2 groups , so we added 2 standby redo log threads each with 3 groups

First create necessary ASM directories to hold the standby logs
e.g. +DATA/T24TRAIN/STANDBYLOG

then make standby file management manual on both nodes

alter system set standby_file_management=manual scope=both sid='*';

alter database add standby logfile thread 1 group 15 '+DATA/T24TRAIN/STANDBYLOG/standby_group_15.log' size 500M;
alter database add standby logfile thread 1 group 16 '+DATA/T24TRAIN/STANDBYLOG/standby_group_16.log' size 500M;
alter database add standby logfile thread 1 group 17 '+DATA/T24TRAIN/STANDBYLOG/standby_group_17.log' size 500M;
alter database add standby logfile thread 2 group 18 '+DATA/T24TRAIN/STANDBYLOG/standby_group_18.log' size 500M;
alter database add standby logfile thread 2 group 19 '+DATA/T24TRAIN/STANDBYLOG/standby_group_19.log' size 500M;
alter database add standby logfile thread 2 group 20 '+DATA/T24TRAIN/STANDBYLOG/standby_group_20.log' size 500M;

again make standby file management auto

alter system set standby_file_management=auto scope=both sid='*';

Step4: make appropriate tnsnames.ora entries on both RAC primary nodes & standby server


Step5: Make sure fal_server parameter can be connected from either side , this is important as it is used for redolog transfer

e.g. in primary FAL_SERVER=t24trainsby
so from primary server
prompt> sqlplus sys/password@t24trainsby as sysdba <should be able to connect>
          
in standby FAL_SERVER=t24train
prompt> sqlplus sys/password@t24train as sysdba<should be able to connect>

important: for our particular case we had used SCAN for primary so make sure SCAN also should be able to resolve from standby site.

Step6: make backup of primary site

 create required direcory to hold backup in local file system

mkdir /u01/backup/

rman target /

run

{

     sql "alter system switch logfile";

     allocate channel ch1 type disk format '/u01/backup/Primary_bkp_for_stndby_%U';

     backup database;

     backup current controlfile for standby;

     sql "alter system archive log current";

}

scp the backup pieces to standby server
scp /u01/backup/Primary_bkp_for_stndby* mailto:oracle@%3Cstandby_server%3E:/u01/backup/

Step7: Also make password file common on both RAC nodes & standby server
e.g.
[login to primary & standby DBs & make sec_case_sensitive_logins=FALSE]
unix_prompt> cd $ORACLE_HOME/dbs
           > <delete or backup any existing passwd files>
           > orapw file=<> entries=5 ignorecase=y password=<> 

Step8:Start the standby DB in nomount state using the modified pfile & create spfile from it.

prompt> export ORACLE_SID=t24trainsby
prompt> sqlplus / as sysdba
 sql> startup nomount pfile='<PATH>'
 sql> create spfile='<ASM_PATH>' from pfile='<PATH>'
 sql> exit

Step 8: from stanby server 

$ rman target sys@T24TRAIN1 auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 26 08:41:48 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
target database Password:
connected to target database: T24TRAIN (DBID=3199606683)
connected to auxiliary database: T24TRAIN (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;
Starting Duplicate Db at 26-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=5293 device type=DISK
contents of Memory Script:
{
   restore clone standby controlfile;
}
executing Memory Script
Starting restore at 26-MAR-12
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/Primary_bkp_for_stndby_03n6hill_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/backup/Primary_bkp_for_stndby_03n6hill_1_1 tag=TAG20120322T191253
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/t24trainsby/controlfile/control01.ctl
output file name=+DATA/t24trainsby/controlfile/control02.ctl
Finished restore at 26-MAR-12
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
   set newname for tempfile  1 to
 "+data";
   switch clone tempfile all;
   set newname for datafile  1 to
 "+data";
   set newname for datafile  2 to
 "+data";
   set newname for datafile  3 to
 "+data";
   set newname for datafile  4 to
 "+data";
   set newname for datafile  5 to
 "+data";
   set newname for datafile  6 to
 "+data";
   set newname for datafile  7 to
 "+data";
   restore
   clone database
   ;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +data in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 26-MAR-12
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +data
channel ORA_AUX_DISK_1: restoring datafile 00002 to +data
channel ORA_AUX_DISK_1: restoring datafile 00003 to +data
channel ORA_AUX_DISK_1: restoring datafile 00004 to +data
channel ORA_AUX_DISK_1: restoring datafile 00005 to +data
channel ORA_AUX_DISK_1: restoring datafile 00006 to +data
channel ORA_AUX_DISK_1: restoring datafile 00007 to +data
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/Primary_bkp_for_stndby_01n6hil2_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/backup/Primary_bkp_for_stndby_01n6hil2_1_1 tag=TAG20120322T191233
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 26-MAR-12
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=778927491 file name=+DATA/t24trainsby/datafile/system.260.778927447
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=778927491 file name=+DATA/t24trainsby/datafile/sysaux.261.778927447
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=778927491 file name=+DATA/t24trainsby/datafile/undotbs1.262.778927447
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=778927491 file name=+DATA/t24trainsby/datafile/users.264.778927447
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=778927491 file name=+DATA/t24trainsby/datafile/undotbs2.263.778927447
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=778927491 file name=+DATA/t24trainsby/datafile/t24distdata.258.778927447
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=778927491 file name=+DATA/t24trainsby/datafile/t24distindex.259.778927447
Finished Duplicate Db at 26-MAR-12
RMAN> exit
 

Issues in Installation of 2 node RAC on AIX 6.1

The main issues faced are only discussed here

Issues1: While running root.sh script it fails with "Error: Connection Timeout"
reason:   due to incorrect ULIMIT setting for users in AIX.

Issue2:  Accidentally override the System disks for ASM.
reason: before using a RAW disk for ASM make sure it has correct reserve_policy & ownership changed to grid:oinstall & permission correctly given

Issue3: While Database creation ASM diskgroups doesn't show up.
reason: the file permission of $GRID_HOME/bin/oracle should be 6571 so as root user change permission of the executable
root> chmod 6571 $GRID_HOME/bin/oracle

Issue4: After database creation users not able to connect by tns entry.
reason: Change the permission of $ORACLE_HOME/bin/oracle to 6571 as root user
root> chmod 6571 $ORACLE_HOME/bin/oracle

Creating a Duplicate Database in using RMAN in 10g(windows platform), Same server with different file structure

Hi,
Please find the below steps for creating a duplicate DB in 10g

• Step1(take proper backup & list backups ensure control file auto backup on)

        Take RMAN backup from primary database(please refer the attachment)

        List all the backups from RMAN

        Now see if any backups are taken in different location(my case i put all the backups in "D:\rman_backup") ,
        My case i moved controlfile autobackup from 'C:\oracle\product\10.2.0\db_1\flash_recovery_area\ORCL\AUTOBACKUP\2012_01_12\O1_MF_S_772414891_7JY8LSN2_' to 'D:\rman_backup\'

        Catalog any of the moved backuppiece in RMAN


RMAN> catalog backuppiece 'D:\rman_backup\O1_MF_S_772414891_7JY8LSN2_.BKP';

cataloged backuppiece
backup piece handle=D:\RMAN_BACKUP\O1_MF_S_772414891_7JY8LSN2_.BKP recid=57 stamp=772416237

• Prepare a proper init file for the new database , please find the below file i used(note log_file_name_convert & db_file_name_convert parameters)

        Content of initDUP.ora

*.audit_file_dest='D:\dup\adump'

*.background_dump_dest='D:\dup\bdump'

*.compatible='10.2.0.1.0'

*.control_files='D:\dup\control01.ctl','D:\dup\control02.ctl','D:\dup\control03.ctl'

*.core_dump_dest='D:\dup\cdump'

*.cursor_sharing='SIMILAR'

*.db_block_size=8192

*.db_create_file_dest='D:\dup\'

*.db_create_online_log_dest_1='D:\dup\redolog\'

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='C:\oracle\product\10.2.0\oradata\orcl\','D:\dup\','C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\','D:\dup\','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORCL\DATAFILE\','D:\dup\'

*.log_file_name_convert='C:\oracle\product\10.2.0\oradata\orcl\','D:\dup\redolog\','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDOLOG\ORCL\ONLINELOG\','D:\dup\redolog\'

*.db_name='orcldup'

*.db_recovery_file_dest='D:\dup\flash_recovery_area\'

*.db_recovery_file_dest_size=6442450944

*.dg_broker_start=TRUE

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.global_names=TRUE

*.job_queue_processes=10

*.log_archive_dest_1='location=D:\dup\archive valid_for=(all_logfiles,all_roles)'

*.nls_date_format='YYYY/MM/DD HH24:MI:SS'

*.nls_time_format='YYYY/MM/DD HH24:MI:SS'

*.open_cursors=300

*.optimizer_mode='all_rows'

*.pga_aggregate_target=96468992

*.processes=150

*.query_rewrite_integrity='TRUSTED'

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=290455552

*.sql_trace=TRUE

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='D:\dup\udump'

         Create a new instance in windows using

  

C:\Windows\system32>oradim -new -sid orcldup

Instance created.

     Start the instance with the pfile

now exit from sqlplus and go to RMAN for restoration, connect to both target & auxiliary as in the screenshots

Now login & check you should have your duplicate database ready..... enjoy...:)