Thursday 4 July 2013 | Posted by postgresdba at 22:41 | 0 comments

Fixing [ ORA 30013 ] overgrown undo tablespace.


SQL> show parameter undo

NAME              TYPE            VALUE
----------------- ------------------------
undo_tablespace   string          UNDOTBS1
 
SQL> create undo tablespace UNDOTBS2 datafile size 1G autoextend off;
Tablespace created.

SQL> alter system set UNDO_TABLESPACE=UNDOTBS2;
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
 
SQL> ho oerr ORA 30013
30013, 00000, "undo tablespace '%s' is currently in use"
// *Cause:   the specified undo tablespace is currently used by another instance.
// *Action:  Wait for the undo tablespace to become available or  change to another name and reissue the statement.

SQL>  select owner, segment_name, tablespace_name, status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS1' and status='ONLINE' ;

OWNER  SEGMENT_NAME                   TABLESPACE_NAME   STATUS
------ ------------------------------ ----------------- ----------------
PUBLIC _SYSSMU9_3945653786$           UNDOTBS1          ONLINE            

SQL>  select substr(USERNAME,1,10) USERNAME,SID,SERIAL#,OSUSER,TERMINAL,SEGMENT_NAME
from v$transaction,dba_rollback_segs,v$session where SADDR=SES_ADDR and XIDUSN=SEGMENT_ID;

USERNAME            SID    SERIAL# OSUSER     TERMINAL   SEGMENT_NAME
------------ ---------- ---------- ---------- ---------- ----------------------
CITIDEVCOL           21      13519 db1123     UNKNOWN    _SYSSMU9_3945653786$

Please confirm the kill session from the respective user , If there are many session you ll have to wait till the active queries complete and free the RBS from the old undo tablespace.  

SQL> alter system kill session '21,13519';

Session killed.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

Tuesday 25 June 2013 | Posted by postgresdba at 21:35 | 0 comments

Oracle Database 12c Launched.




Oracle Database 12c Release 1 (12.1.0.1.0) has been launched officially.
DBA ‘s , Time to get your hands dirty.
Happy learning.
Oracle Database 12c Release 1   Download links:
Standard Edition, Standard Edition One, and Enterprise Edition
(12.1.0.1.0)
Linux x86-64
File 1, File 2  (2.3 GB)  See All
Solaris Sparc64
File 1, File 2  (2.6 GB)  See All
Solaris (x86-64)
File 1, File 2  (2.3 GB)  See All

Oracle Database 12c Documentation

http://www.oracle.com/pls/db121/homepage


Saturday 20 April 2013 | Posted by postgresdba at 18:38 | 0 comments

How does RMAN channel take the backup ?

The work of each channel, whether of type disk or System Backup Tape (SBT), is subdivided into the following distinct phases:

  1. Read Phase
    A channel reads blocks from disk into input I/O buffers.
  2. Copy Phase
    A channel copies blocks from input buffers to output buffers and performs additional processing on the blocks.
  3. Write Phase
    A channel writes the blocks from output buffers to storage media. The write phase can take either of the following mutually exclusive forms, depending on the type of backup media:

| Posted by postgresdba at 17:30 | 0 comments

Duplicate Database on Same host, Using ACTIVE DUPLICATE method.


SOURCE DB [ORCL]  => AUXILIARY DB [CLDB]
VERSION: 11.2.0.3
METHOD: ACTIVE DUPLICATE
########
PARAMETER FILE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/orcl/spfileorcl.ora

ORCL TABLESPACE and DATAFILE Mapping.

RMAN> report schema;

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    710      SYSTEM               YES     +DATA/orcl/datafile/system.258.812679447
2    580      SYSAUX               NO      +DATA/orcl/datafile/sysaux.259.812679447
3    70       UNDOTBS1             YES     +DATA/orcl/datafile/undotbs1.270.812679447
4    15       USERS                NO      +DATA/orcl/datafile/users.257.812679447

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       +DATA/orcl/tempfile/temp.264.804527935

#Archive the current logfile. In case there are no archives for TARGET DB.
RMAN> sql 'alter system archive log current';

using target database control file instead of recovery catalog
sql statement: alter system archive log current

#Create pfile.

$ sqlplus '/as sysdba'
SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/orcl/spfileorcl.ora
SQL> create pfile='initCLDB.ora' from spfile;
File created.

cd $ORACLE_HOME/dbs

##Edit the PFILE for CLDB database.
cat >initCLDB.ora
*.db_name='CLDB'
*.control_Files=('+CLONE_DATA','+CLONE_LOG')
*.db_file_name_convert=('+DATA','+CLONE_DATA')
*.log_file_name_convert=('+DATA','+CLONE_DATA','+FRA','+CLONE_LOG')
*.db_create_file_dest='+CLONE_DATA'
PRESS CTRL+D


###Add the below entry in the /etc/oratab file
CLDB:/u01/app/oracle/product/11.2.0/db_1:N

###Set the environment to CLDB, Start the database in nomount
$ . oraenv
ORACLE_SID = [ORCL] ? CLDB

$ sqlplus '/as sysdba'
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  271437824 bytes
Fixed Size                  2227456 bytes
Variable Size             213910272 bytes
Database Buffers           50331648 bytes
Redo Buffers                4968448 bytes

SQL> show parameter db_name;
NAME     TYPE        VALUE
-------- ----------- -----
db_name  string      CLDB

SQL> show parameter convert
NAME                   TYPE   VALUE
---------------------  ------ ------------------------------
db_file_name_convert   string +DATA, +CLONE_DATA
log_file_name_convert  string +DATA, +CLONE_DATA, +FRA, +CLONE_LOG

#Create the TNS ENTRY in the tnsnames.ora file
#[loc:/u01/app/oracle/product/11.2.0/db_1/network/admin]
CLDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = OEL61B)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CLDB)
    )
  )

#Register the database with the GRID LISTENER STATICALLY.
#NEED TO LOGIN AS GRID
#[Loc:/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora]
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = ORCL)
    )
      (SID_DESC =
      (GLOBAL_DBNAME = CLDB)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = CLDB)
    )
  )

#CHECK FOR THE LISTENER STATUS
lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 21-APR-2013 04:26:29

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                21-APR-2013 03:43:59
Uptime                    0 days 0 hr. 42 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/OEL61B/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OEL61B)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "CLDB" has 1 instance(s).
  Instance "CLDB", status BLOCKED, has 1 handler(s) for this service...
Service "ORCL" has 2 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully

#PLEASE NOTE: Status of the CLDB is BLOCKED , IT has to be UNKOWN for us to perform the duplicate
#RESTART the LISTENER

[grid@OEL61B admin]$ crsctl stop res ora.LISTENER.lsnr -f
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'oel61b'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'oel61b' succeeded
[grid@OEL61B admin]$ crsctl start res ora.LISTENER.lsnr
CRS-2672: Attempting to start 'ora.LISTENER.lsnr' on 'oel61b'
CRS-2676: Start of 'ora.LISTENER.lsnr' on 'oel61b' succeeded

[grid@OEL61B admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 21-APR-2013 04:28:50
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                21-APR-2013 04:28:33
Uptime                    0 days 0 hr. 0 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/OEL61B/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OEL61B)(PORT=1521)))
Services Summary...
Service "CLDB" has 1 instance(s).
  Instance "CLDB", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL_DUP" has 1 instance(s).
  Instance "ORCL_DUP", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

***STATUS changed to UNKOWN.


#TNSPING
[oracle@OEL61B admin]$ tnsping CLDB
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 21-APR-2013 04:30:54
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used paameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = OEL61B)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CLDB)))
OK (0 msec)
[oracle@OEL61B admin]$ tnsping ORCL
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 21-APR-2013 04:30:58
Copyright (c) 1997, 2011, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = OEL61B)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))
OK (0 msec)

#Create PASSWORD FILE
orapwd file=orapwCLDB entries=5
Enter password for SYS:

#Initiate Clone process.
rman target sys/oracle@ORCL
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Apr 21 04:34:02 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1332308625)

RMAN> connect auxiliary sys/oracle@CLDB
connected to auxiliary database: CLDB (not mounted)
run{
SET NEWNAME FOR DATAFILE 1 TO  "+CLONE_DATA";
SET NEWNAME FOR DATAFILE 2 TO  "+CLONE_DATA";
SET NEWNAME FOR DATAFILE 3 TO  "+CLONE_DATA";
SET NEWNAME FOR DATAFILE 4 TO  "+CLONE_DATA";
SET NEWNAME FOR TEMPFILE 1 TO  "+CLONE_DATA";
DUPLICATE TARGET DATABASE   TO CLDB   FROM ACTIVE DATABASE;
}

===RMAN O/P FOLLOWS========

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at 21-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=430 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=6 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=148 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=289 device type=DISK

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     271437824 bytes

Fixed Size                     2227456 bytes
Variable Size                213910272 bytes
Database Buffers              50331648 bytes
Redo Buffers                   4968448 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+CLONE_DATA/cldb/controlfile/current.263.813302321'', ''+CLONE_LOG/cldb/controlfile/current.267.813302325'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''ORCL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''CLDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '+CLONE_DATA/cldb/controlfile/current.264.813302325';
   restore clone controlfile to  '+CLONE_LOG/cldb/controlfile/current.268.813302325' from
 '+CLONE_DATA/cldb/controlfile/current.264.813302325';
   sql clone "alter system set  control_files =
  ''+CLONE_DATA/cldb/controlfile/current.264.813302325'', ''+CLONE_LOG/cldb/controlfile/current.268.813302325'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+CLONE_DATA/cldb/controlfile/current.263.813302321'', ''+CLONE_LOG/cldb/controlfile/current.267.813302325'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''CLDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     271437824 bytes

Fixed Size                     2227456 bytes
Variable Size                213910272 bytes
Database Buffers              50331648 bytes
Redo Buffers                   4968448 bytes

Starting backup at 21-APR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=72 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=192 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=197 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_ORCL.f tag=TAG20130421T051856 RECID=34 STAMP=813302340
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 21-APR-13

Starting restore at 21-APR-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=430 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=6 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=289 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=431 device type=DISK

channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_4: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 21-APR-13

sql statement: alter system set  control_files =   ''+CLONE_DATA/cldb/controlfile/current.264.813302325'', ''+CLONE_LOG/cldb/controlfile/current.268.813302325'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     271437824 bytes

Fixed Size                     2227456 bytes
Variable Size                213910272 bytes
Database Buffers              50331648 bytes
Redo Buffers                   4968448 bytes

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
 "+CLONE_DATA";
   set newname for datafile  2 to
 "+CLONE_DATA";
   set newname for datafile  3 to
 "+CLONE_DATA";
   set newname for datafile  4 to
 "+CLONE_DATA";
   backup as copy reuse
   datafile  1 auxiliary format
 "+CLONE_DATA"   datafile
 2 auxiliary format
 "+CLONE_DATA"   datafile
 3 auxiliary format
 "+CLONE_DATA"   datafile
 4 auxiliary format
 "+CLONE_DATA"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 21-APR-13
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/orcl/datafile/system.258.812679447
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.259.812679447
channel ORA_DISK_3: starting datafile copy
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.270.812679447
channel ORA_DISK_4: starting datafile copy
input datafile file number=00004 name=+DATA/orcl/datafile/users.257.812679447
output file name=+CLONE_DATA/cldb/datafile/users.268.813302375 tag=TAG20130421T051933
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:15
output file name=+CLONE_DATA/cldb/datafile/undotbs1.267.813302375 tag=TAG20130421T051933
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:36
output file name=+CLONE_DATA/cldb/datafile/sysaux.266.813302375 tag=TAG20130421T051933
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:02:36
output file name=+CLONE_DATA/cldb/datafile/system.265.813302373 tag=TAG20130421T051933
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:06
Finished backup at 21-APR-13

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+BKP1/orcl/archivelog/2013_04_21/thread_1_seq_3.322.813302559" auxiliary format
 "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_3_813300745.dbf"   ;
   catalog clone archivelog  "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_3_813300745.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 21-APR-13
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=3 RECID=121 STAMP=813302559
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_3_813300745.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 21-APR-13

cataloged archived log
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_3_813300745.dbf RECID=121 STAMP=813302561

datafile 1 switched to datafile copy
input datafile copy RECID=34 STAMP=813302561 file name=+CLONE_DATA/cldb/datafile/system.265.813302373
datafile 2 switched to datafile copy
input datafile copy RECID=35 STAMP=813302561 file name=+CLONE_DATA/cldb/datafile/sysaux.266.813302375
datafile 3 switched to datafile copy
input datafile copy RECID=36 STAMP=813302561 file name=+CLONE_DATA/cldb/datafile/undotbs1.267.813302375
datafile 4 switched to datafile copy
input datafile copy RECID=37 STAMP=813302561 file name=+CLONE_DATA/cldb/datafile/users.268.813302375

contents of Memory Script:
{
   set until scn  2025145;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 21-APR-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=6 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=148 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=289 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=149 device type=DISK

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_3_813300745.dbf
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_3_813300745.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-APR-13
Oracle instance started

Total System Global Area     271437824 bytes

Fixed Size                     2227456 bytes
Variable Size                213910272 bytes
Database Buffers              50331648 bytes
Redo Buffers                   4968448 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''CLDB'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''CLDB'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     271437824 bytes

Fixed Size                     2227456 bytes
Variable Size                213910272 bytes
Database Buffers              50331648 bytes
Redo Buffers                   4968448 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLDB" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '+clone_log', '+clone_data' ) SIZE 50 M  REUSE,
  GROUP   2 ( '+clone_data', '+clone_log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '+clone_data', '+clone_log' ) SIZE 50 M  REUSE,
  GROUP   4 ( '+clone_log', '+clone_data' ) SIZE 100 M  REUSE,
  GROUP   5 ( '+clone_log', '+clone_data' ) SIZE 100 M  REUSE,
  GROUP   6 ( '+clone_log', '+clone_data' ) SIZE 100 M  REUSE,
  GROUP   7 ( '+clone_data', '+clone_log' ) SIZE 100 M  REUSE,
  GROUP   8 ( '+clone_log', '+clone_data' ) SIZE 100 M  REUSE
 DATAFILE
  '+CLONE_DATA/cldb/datafile/system.265.813302373'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "+CLONE_DATA";
   switch clone tempfile all;
   catalog clone datafilecopy  "+CLONE_DATA/cldb/datafile/sysaux.266.813302375",
 "+CLONE_DATA/cldb/datafile/undotbs1.267.813302375",
 "+CLONE_DATA/cldb/datafile/users.268.813302375";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +CLONE_DATA in control file

cataloged datafile copy
datafile copy file name=+CLONE_DATA/cldb/datafile/sysaux.266.813302375 RECID=1 STAMP=813302584
cataloged datafile copy
datafile copy file name=+CLONE_DATA/cldb/datafile/undotbs1.267.813302375 RECID=2 STAMP=813302584
cataloged datafile copy
datafile copy file name=+CLONE_DATA/cldb/datafile/users.268.813302375 RECID=3 STAMP=813302584

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=813302584 file name=+CLONE_DATA/cldb/datafile/sysaux.266.813302375
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=813302584 file name=+CLONE_DATA/cldb/datafile/undotbs1.267.813302375
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=813302584 file name=+CLONE_DATA/cldb/datafile/users.268.813302375

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 21-APR-13

[oracle@OEL61B ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Apr 21 05:32:16 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CLDB (DBID=1689943220)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CLDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    710      SYSTEM               ***     +CLONE_DATA/cldb/datafile/system.265.813302373
2    580      SYSAUX               ***     +CLONE_DATA/cldb/datafile/sysaux.266.813302375
3    70       UNDOTBS1             ***     +CLONE_DATA/cldb/datafile/undotbs1.267.813302375
4    15       USERS                ***     +CLONE_DATA/cldb/datafile/users.268.813302375

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       +CLONE_DATA/cldb/tempfile/temp.277.813302691

House Keeping:

sqlplus '/as sysdba'
SQL> create pfile='initCLDB.ora.ora' from memory;
SQL> exit

$ cd $ORACLE_HOME/dbs

edit initCLDB.ora.ora

Remove all the deprecated parameters.

sqlplus '/as sysdba'
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initCLDB.ora';
ORACLE instance started.
Total System Global Area  271437824 bytes
Fixed Size                  2227456 bytes
Variable Size             213910272 bytes
Database Buffers           50331648 bytes
Redo Buffers                4968448 bytes

SQL> create spfile='+CLONE_DATA' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initCLDB.ora';
File created.

SQL> shut abort;

###Get the LOCATION OF SPFILE from ASM [LOGIN FROM ANOTHER SESSION AS GRID].
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     10228     8389                0            8389              0             N  BKP1/
MOUNTED  EXTERN  N         512   4096  1048576     10228     6638                0            6638              0             N  CLONE_DATA/
MOUNTED  EXTERN  N         512   4096  1048576     10228     8792                0            8792              0             N  CLONE_LOG/
MOUNTED  EXTERN  N         512   4096  1048576     10228     7497                0            7497              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     10228     5690                0            5690              0             N  FRA/
ASMCMD> cd +CLONE_DATA
ASMCMD> ls -lt
Type  Redund  Striped  Time             Sys  Name
                                        Y    CLDB/
ASMCMD> cd CLDB
ASMCMD> ls -lt
Type  Redund  Striped  Time             Sys  Name
                                        Y    TEMPFILE/
                                        Y    PARAMETERFILE/
                                        Y    ONLINELOG/
                                        Y    DATAFILE/
                                        Y    CONTROLFILE/
ASMCMD> cd PARAMETERFILE
ASMCMD> ls -lt
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   APR 21 05:00:00  Y    spfile.278.813303893
ASMCMD> cd ..
ASMCMD> pwd
+CLONE_DATA/CLDB
ASMCMD> mkalias +CLONE_DATA/CLDB/PARAMETERFILE/spfile.278.813303893 spfileCLDB.ora
ASMCMD> ls -lt
Type           Redund  Striped  Time             Sys  Name
                                                 Y    TEMPFILE/
                                                 Y    PARAMETERFILE/
                                                 Y    ONLINELOG/
                                                 Y    DATAFILE/
                                                 Y    CONTROLFILE/
                                                 N    spfileCLDB.ora => +CLONE_DATA/CLDB/PARAMETERFILE/spfile.278.813303893

##Edit the pfile to point to the SPFILE in ASM:
cat > initCLDB.ora
SPFILE='+CLONE_DATA/CLDB/spfileCLDB.ora'
PRESS CTRL+D

$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 21 05:49:33 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup;
ORACLE instance started.
Total System Global Area  271437824 bytes
Fixed Size                  2227456 bytes
Variable Size             213910272 bytes
Database Buffers           50331648 bytes
Redo Buffers                4968448 bytes
Database mounted.
Database opened.

SQL> show parameter spfile;

NAME   TYPE        VALUE
------ ----------- --------------------------------
spfile string      +CLONE_DATA/cldb/spfilecldb.ora

---END---

REF:
Oracle® Database Backup and Recovery Reference
11g Release 2 (11.2)
E10643-06

Subscribe to: Posts (Atom)