Thinking Out Loud

July 29, 2013

Differences in 12c Grid Install

Filed under: 12c — mdinh @ 1:00 am

12c Grid Infrastructure Installation for Standalone  Server, there is a warning if GRID_HOME is outside of ORACLE_BASE

db12c7o

db12c7

and Root Script execution configuration

db12c9

db12c11

What difference’s did you notice?

UPDATE:  July 31, 2013

12c Grid Infrastructure Software Only Installation – Error with [INS-32022] Grid Infrastructure software for a cluster installation must not be under an Oracle base directory.

 

July 13, 2013

Absolute versus Relative Reference

Filed under: oracle — mdinh @ 6:14 pm

It’s important for software developers to understand the differences between absolute versus relative references.

Whether it’s from an Excel spreadsheet or from an oracle database. it’s an easy mistake to make.

I have created a test case where object “abso” , “refe” to represent absolute and relative reference, respectively.

Create test case:

LAX:(APPU@db01)> @test
LAX:(APPU@db01)> drop table abso purge;

Table dropped.

LAX:(APPU@db01)> create table abso (id int);

Table created.

LAX:(APPU@db01)> create or replace view abso_v as select id from appu.abso;

View created.

LAX:(APPU@db01)> create or replace trigger appu.abso_bi before insert on appu.abso for each row
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.

LAX:(APPU@db01)> create trigger appu.abso_bd before delete on appu.abso for each row
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.

LAX:(APPU@db01)> create or replace procedure abso_proc as
  2  l_id pls_integer;
  3  begin
  4  select id into l_id from appu.abso;
  5  end;
  6  /

Procedure created.

LAX:(APPU@db01)> drop table refe purge;

Table dropped.

LAX:(APPU@db01)> create table refe (id int);

Table created.

LAX:(APPU@db01)> create or replace view refe_v as select id from refe;

View created.

LAX:(APPU@db01)> create or replace trigger refe_bi before insert on refe for each row
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.

LAX:(APPU@db01)> create trigger refe_bd before delete on refe for each row
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.

LAX:(APPU@db01)> create or replace procedure refe_proc as
  2  l_id pls_integer;
  3  begin
  4  select id into l_id from refe;
  5  end;
  6  /

Procedure created.

LAX:(APPU@db01)>

Perform metadata export:

[oracle@lax:db01]/home/oracle
$ cat expdp_metadata.par
directory=DATA_PUMP_DIR
userid="/ as sysdba"
dumpfile=metadata.dmp
logfile=metadata.log
schemas=APPU
content=METADATA_ONLY
exclude=INDEX_STATISTICS,TABLE_STATISTICS
metrics=Y
[oracle@lax:db01]/home/oracle
$ expdp parfile=expdp_metadata.par

Export: Release 11.2.0.3.0 - Production on Sat Jul 13 10:28:41 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA parfile=expdp_metadata.par
Processing object type SCHEMA_EXPORT/USER
     Completed 1 USER objects in 1 seconds
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
     Completed 1 SYSTEM_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/ROLE_GRANT
     Completed 1 ROLE_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
     Completed 1 DEFAULT_ROLE objects in 2 seconds
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
     Completed 1 PROCACT_SCHEMA objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 2 TABLE objects in 2 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
     Completed 2 PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
     Completed 2 ALTER_PROCEDURE objects in 3 seconds
Processing object type SCHEMA_EXPORT/VIEW/VIEW
     Completed 2 VIEW objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 4 TRIGGER objects in 7 seconds
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /tmp/metadata.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:29:09

[oracle@lax:db01]/home/oracle
$

Perform metadata import with remap_schema – creating SQL file:

[oracle@lax:db01]/home/oracle
$ cat impdp_metadata.par
directory=DATA_PUMP_DIR
userid="/ as sysdba"
dumpfile=metadata.dmp
schemas=APPU
remap_schema=APPU:NEWAPP
logfile=metadata.log
include=PROCEDURE,TRIGGER,VIEW
sqlfile=metadata.sql
metrics=Y
[oracle@lax:db01]/home/oracle
$ impdp parfile=impdp_metadata.par

Import: Release 11.2.0.3.0 - Production on Sat Jul 13 10:36:55 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_SCHEMA_01":  /******** AS SYSDBA parfile=impdp_metadata.par
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
     Completed 2 PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
     Completed 2 ALTER_PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/VIEW/VIEW
     Completed 2 VIEW objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 4 TRIGGER objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 10:36:58

Do you see what’s wrong with this picture?

[oracle@lax:db01]/home/oracle
$ cat /tmp/metadata.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/PROCEDURE/PROCEDURE
-- CONNECT NEWAPP
CREATE procedure abso_proc as
l_id pls_integer;
begin
select id into l_id from appu.abso; <<<
end;
/
CREATE procedure refe_proc as
l_id pls_integer;
begin
select id into l_id from refe;
end;
/
-- new object type path: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

ALTER PROCEDURE "NEWAPP"."ABSO_PROC"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

 REUSE SETTINGS TIMESTAMP '2013-07-13 10:24:27'
/

ALTER PROCEDURE "NEWAPP"."REFE_PROC"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

 REUSE SETTINGS TIMESTAMP '2013-07-13 10:24:28'
/
-- new object type path: SCHEMA_EXPORT/VIEW/VIEW
CREATE  FORCE VIEW "NEWAPP"."ABSO_V" ("ID") AS
  select id from appu.abso; <<<
CREATE  FORCE VIEW "NEWAPP"."REFE_V" ("ID") AS
  select id from refe;
-- new object type path: SCHEMA_EXPORT/TABLE/TRIGGER
CREATE TRIGGER "NEWAPP"."ABSO_BD"  before delete on appu.abso for each row <<<
begin
null;
end;
/

ALTER TRIGGER "NEWAPP"."ABSO_BD" ENABLE;

ALTER TRIGGER "NEWAPP"."ABSO_BD"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'
;
CREATE TRIGGER refe_bi before insert on refe for each row
begin
null;
end;
/

ALTER TRIGGER "NEWAPP"."REFE_BI" ENABLE;

ALTER TRIGGER "NEWAPP"."REFE_BI"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'
;
CREATE TRIGGER "NEWAPP"."ABSO_BI"  before insert on appu.abso for each row <<<
begin
null;
end;
/

ALTER TRIGGER "NEWAPP"."ABSO_BI" ENABLE;

ALTER TRIGGER "NEWAPP"."ABSO_BI"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'
;
CREATE TRIGGER refe_bd before delete on refe for each row
begin
null;
end;
/

ALTER TRIGGER "NEWAPP"."REFE_BD" ENABLE;

ALTER TRIGGER "NEWAPP"."REFE_BD"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'
;
[oracle@lax:db01]/home/oracle
$

Thank goodness for sed !!!

Remove schema reference using sed:

  • sed -i ‘s/appu\.//g’ /tmp/metadata.sql
  • sed -i ‘s/\”NEWAPP\”\.//g’ /tmp/metadata.sql

Why did I remove “NEWAPP” when it’s the correct schema?

Just to show you sed function.

[oracle@lax:db01]/home/oracle
$ sed -i 's/appu\.//g' /tmp/metadata.sql
[oracle@lax:db01]/home/oracle
$ sed -i 's/\"NEWAPP\"\.//g' /tmp/metadata.sql
[oracle@lax:db01]/home/oracle
$ cat /tmp/metadata.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/PROCEDURE/PROCEDURE
-- CONNECT NEWAPP
CREATE procedure abso_proc as
l_id pls_integer;
begin
select id into l_id from abso;
end;
/
CREATE procedure refe_proc as
l_id pls_integer;
begin
select id into l_id from refe;
end;
/
-- new object type path: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ALTER PROCEDURE "ABSO_PROC"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED
PLSQL_DEBUG= FALSE PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'
REUSE SETTINGS TIMESTAMP '2013-07-13 10:24:27'
/
ALTER PROCEDURE "REFE_PROC"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED
PLSQL_DEBUG= FALSE PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'

REUSE SETTINGS TIMESTAMP '2013-07-13 10:24:28'
/
-- new object type path: SCHEMA_EXPORT/VIEW/VIEW
CREATE FORCE VIEW "ABSO_V" ("ID") AS
select id from abso;
CREATE FORCE VIEW "REFE_V" ("ID") AS
select id from refe;
-- new object type path: SCHEMA_EXPORT/TABLE/TRIGGER
CREATE TRIGGER "ABSO_BD" before delete on abso for each row
begin
null;
end;
/

ALTER TRIGGER "ABSO_BD" ENABLE;

ALTER TRIGGER "ABSO_BD"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'
;
CREATE TRIGGER refe_bi before insert on refe for each row
begin
null;
end;
/

ALTER TRIGGER "REFE_BI" ENABLE;

ALTER TRIGGER "REFE_BI"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'
;
CREATE TRIGGER "ABSO_BI" before insert on abso for each row
begin
null;
end;
/

ALTER TRIGGER "ABSO_BI" ENABLE;

ALTER TRIGGER "ABSO_BI"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'
;
CREATE TRIGGER refe_bd before delete on refe for each row
begin
null;
end;
/

ALTER TRIGGER "REFE_BD" ENABLE;

ALTER TRIGGER "REFE_BD"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'
;
[oracle@lax:db01]/home/oracle
$

July 3, 2013

Restore 12c Upgrade

Filed under: Blogroll,oracle — mdinh @ 2:09 am

Did you finished reading my post on 12c upgrade?

Power of rot, obscuring truth, what once was old restore to youth – http://en.wikipedia.org/wiki/Visionaries:_Knights_of_the_Magical_Light

Just like that – my database is back to 11.2.0.3

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ ls *restore*
db01_restore.sh
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ ./db01_restore.sh
-- Bringing down the database from the new oracle home

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 2 18:53:14 2013

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

Connected.
ORACLE instance shut down.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
You should Remove this entry from the /etc/oratab: db01:/u01/app/oracle/product/12.1.0/dbhome_1:Y
-- Bringing up the database from the source oracle home
You should Add this entry in the /etc/oratab: db01:/u01/app/oracle/product/11.2.0.3/db_1:Y
-- Removing /u01/app/oracle/cfgtoollogs/dbua/logs/Welcome_db01.txt file

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 2 18:53:17 2013

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

Connected to an idle instance.

File created.

Disconnected

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 2 18:53:18 2013

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

Connected to an idle instance.
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2230072 bytes
Variable Size             167774408 bytes
Database Buffers          360710144 bytes
Redo Buffers                3747840 bytes
Database mounted.

Flashback complete.

Database altered.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ vi /etc/oratab
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ . ~/.bash_profile
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ db01
The Oracle base remains unchanged with value /u01/app/oracle

IPC Resources for ORACLE_SID "db01" :
Shared Memory:
ID              KEY
26116100        0x00000000
26148869        0x00000000
26181638        0xc1f92260
Semaphores:
ID              KEY
2228226         0x7632ce48
Oracle Instance alive for sid "db01"
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 2 19:00:44 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

LAX:(SYS@db01)> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ lsnrctl status listener_db01

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-JUL-2013 19:00:56

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lax.localdomain)(PORT=1531)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767)))
STATUS of the LISTENER
------------------------
Alias                     listener_db01
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                02-JUL-2013 06:39:35
Uptime                    0 days 12 hr. 21 min. 22 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lax)(PORT=1531)))
Services Summary...
Service "lax_db01" has 1 instance(s).
  Instance "db01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$

Upgrade to 12c using DBUA Silent (shhh) Mode

Filed under: 12c,upgrade — mdinh @ 1:52 am

Single instance database upgrade from 11.2.0.3 to 12.1.0.1.0.

There’s plenty of install posts already out there; hence, I am not going to blog about it.

If you find a post on DBUA Silent mode, then please let me know.

Oracle Database 12c Release 1 (12.1) DBUA in Silent mode [ID 1516616.1]

First attempt failed with errors:

ORA-00823: Specified value of sga_target greater than sga_max_size
ORA-01078: failure in processing system parameters

ORA-00823 – Specified value of sga_target greater than sga_max_size Error During Upgrade Using DBUA [ID 1439671.1]

As an alternative solution SGA_MAX_SIZE can be removed as it is not mandatory while upgrade

Is Oracle lying to me? Let’s look at spfile before and after.

$ tail spfiledb01.ora.orig

*.processes=50
*.remote_logCC"-in_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_max_size=512M
*.sga_target=512M
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_tablespace='undotbs'
*.workarea_size_policy='AUTO'

$ tail spfiledb01.ora

*.pga_aggregate_target=128M
*.processes=50
*.remoCC"n&te_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_target=512M
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_tablespace='undotbs'
*.workarea_size_policy='AUTO'

[oracle@lax:db01]/home/oracle
$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/dbua -silent \
> -sid db01 \
> -oracleHome /u01/app/oracle/product/11.2.0.3/db_1 \
> -diagnosticDest /u01/app/oracle \
> -autoextendFiles \
> -recompile_invalid_objects true \
> -degree_of_parallelism 2 \
> -upgradeTimezone \
> -emConfiguration NONE \
> -keepHiddenParams \
> -gatheringStatistics \
> -createGRP true \
> -upgrade_parallelism 2

Log files for the upgrade operation are located at: /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
Performing Database Backup
16% complete
Performing Pre Upgrade
20% complete
33% complete
Performing RDBMS Upgrade
34% complete
49% complete
50% complete
Performing Post Upgrade
51% complete
53% complete
66% complete
Upgrading Timezone
83% complete
Generating Summary
Database upgrade has been completed successfully, and the database is ready to use.
100% complete
Check the log file "/u01/app/oracle/cfgtoollogs/dbua/logs/silent.log_1372773594442" for upgrade details.

DBUA performed the following actions:

Pre-Upgrade Checks

Database contains INVALID objects prior to upgrade. 
The list of invalid SYS/SYSTEM objects was written to registry$sys_inv_objs. 
The list of non-SYS/SYSTEM objects was written to registry$nonsys_inv_objs unless there were over 5000. 
Use utluiobj.sql after the upgrade to identify any new invalid objects due to the upgrade.
It is required that the recycle bin is empty prior to upgrading your database. 
Run "PURGE DBA_RECYCLEBIN" command before continuing with your upgrade.

Initialization Parameter changes

The following changes will be made in the initialization parameters:
sga_target 536870912|624951296 (old|new)
processes  50|300

Obsolete Parameters to be removed

sec_case_sensitive_logon

Timezone Upgrade

The time zone version and TIMESTAMP WITH TIME ZONE data of the Database will be upgraded from version 14 to version 18.

Recovery options

Create a new guaranteed restore point before upgrade and flashback the database in case of any upgrade failure.

Upgrade Details

The following summary lists the steps performed during the upgrade process.
Log files for all the steps, as well as this summary, are available at “/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1”.

Database Backup     -Backup.log
Pre Upgrade         -PreUpgrade.log (PreUpgradeResults.html is a lot better)
RDBMS Upgrade       -Oracle_Server.log
Post Upgrade        -PostUpgrade.log
Timezone Upgrade    -UpgradeTimezone.log
Generate Summary    -generateSummary.log (does not exist)

Let’s review the logs.

$ORACLE_BASE=/u01/app/oracle

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/logs
$ ls -l

total 64
-rw-r----- 1 oracle oinstall  3592 Jul  2 10:48 silent.log_1372773594442
-rw-r----- 1 oracle oinstall 17721 Jul  2 07:02 sqls.log
-rw-r----- 1 oracle oinstall 38554 Jul  2 07:02 trace.log

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/logs
$ head -20 silent.log_1372773594442

Upgrading the database dbName="db01"
Database contains INVALID objects prior to upgrade. The list of invalid SYS/SYSTEM objects was written to registry$sys_inv_objs. The list of non-SYS/SYSTEM objects was written to registry$nonsys_inv_objs unless there were over 5000. Use utluiobj.sql after the upgrade to identify any new invalid objects due to the upgrade.
Your recycle bin contains objects. It is required that the recycle bin is empty prior to upgrading your database. Run "PURGE DBA_RECYCLEBIN" command before continuing with your upgrade.
Supported upgrade version check succeeded.
The oratab permission check succeeded.
Oracle Home Owner Check succeeded.
Database does not use ASM storage.
Space usage summary

Space usage reason:  Upgrade

Tablespace Name:  SYSTEM
Additional space required:  159 MB
Datafile:  /oracle/oradata/db02/system_1.dbf
Auto Extensible:  Yes

Tablespace Name:  SYSAUX
Additional space required:  244 MB
Datafile:  /oracle/oradata/db02/sysaux_1.dbf
Auto Extensible:  Yes

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/logs
$ tail -20 silent.log_1372773594442

UPGRADE_PROGRESS : 48%
UPGRADE_PROGRESS : 48%
UPGRADE_PROGRESS : 48%
UPGRADE_PROGRESS : 49%
UPGRADE_PROGRESS : 49%
UPGRADE_PROGRESS : 49%
UPGRADE_PROGRESS : 50%
Performing Post Upgrade
UPGRADE_PROGRESS : 51%
UPGRADE_PROGRESS : 53%
UPGRADE_PROGRESS : 66%
Upgrading Timezone
UPGRADE_PROGRESS : 83%
Generating Summary
UPGRADE_PROGRESS : 100%
Database upgrade has been completed successfully, and the database is ready to use.

The following document describes important behavioral changes from previous database releases:
/u01/app/oracle/product/12.1.0/dbhome_1/assistants/dbua/doc/DefaultBehaviorChanges_<NLS_LANG>.html

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ ls -alrt

total 24208
-rw-r----- 1 oracle oinstall        0 Jul  2 07:02 trace.log.lck
drwxr-x--- 3 oracle oinstall     4096 Jul  2 07:02 ..
-rw-r----- 1 oracle oinstall     2595 Jul  2 07:04 upgrade.xml1
-rw-r----- 1 oracle oinstall     2256 Jul  2 07:07 upgrade.xml
-rw-r----- 1 oracle oinstall     4504 Jul  2 07:07 PreUpgradeResults.html
-rw-r----- 1 oracle oinstall        0 Jul  2 07:07 Backup.log
-rw-r----- 1 oracle oinstall     1168 Jul  2 07:07 init.ora
-rwxr-xr-x 1 oracle oinstall      173 Jul  2 07:07 createSPFile_db01.sql
-rwxr-xr-x 1 oracle oinstall      127 Jul  2 07:07 grpOpen_db01.sql
-rwxr-xr-x 1 oracle oinstall     1257 Jul  2 07:07 db01_restore.sh
-rwxr-xr-x 1 oracle oinstall       42 Jul  2 07:07 shutdown_db01.sql
-rwxr-xr-x 1 oracle oinstall       35 Jul  2 07:07 startup_db01.sql
-rw-r----- 1 oracle oinstall      486 Jul  2 07:10 PreUpgrade.log
-rw-r----- 1 oracle oinstall  8407445 Jul  2 10:16 catupgrd1.log
-rw-r----- 1 oracle oinstall 15815542 Jul  2 10:16 catupgrd0.log
-rw-r----- 1 oracle oinstall   152750 Jul  2 10:20 Oracle_Server.log
-rw-r----- 1 oracle oinstall     9703 Jul  2 10:34 PostUpgrade.log
-rw-r----- 1 oracle oinstall      364 Jul  2 10:48 UpgradeTimezone.log
-rw-r----- 1 oracle oinstall     6323 Jul  2 10:48 UpgradeResults.html
-rw-r----- 1 oracle oinstall   212540 Jul  2 10:48 trace.log
-rw-r----- 1 oracle oinstall   101483 Jul  2 10:48 sqls.log
drwxr-x--- 2 oracle oinstall     4096 Jul  2 14:38 .

-createGRP true (NF – create a guaranteed restore point when database is in archive log and flashback mode)

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat db01_restore.sh

#!/bin/sh

# -- Run this Script to Restore Oracle Database Instance db01
echo -- Bringing down the database from the new oracle home
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=db01; export ORACLE_SID
/u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/shutdown_db01.sql
echo You should Remove this entry from the /etc/oratab: db01:/u01/app/oracle/product/12.1.0/dbhome_1:Y
echo -- Bringing up the database from the source oracle home
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1; export ORACLE_HOME
ORACLE_SID=db01; export ORACLE_SID
unset LD_LIBRARY_PATH; unset LD_LIBRARY_PATH_64; unset SHLIB_PATH; unset LIB_PATH
echo You should Add this entry in the /etc/oratab: db01:/u01/app/oracle/product/11.2.0.3/db_1:Y
cd /u01/app/oracle/product/11.2.0.3/db_1
echo -- Removing /u01/app/oracle/cfgtoollogs/dbua/logs/Welcome_db01.txt file
rm -f /u01/app/oracle/cfgtoollogs/dbua/logs/Welcome_db01.txt ;
/u01/app/oracle/product/11.2.0.3/db_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/createSPFile_db01.sql
/u01/app/oracle/product/11.2.0.3/db_1/bin/sqlplus /nolog @/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/grpOpen_db01.sql

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/shutdown_db01.sql

connect / as sysdba
shutdown abort;
exit;
[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/createSPFile_db01.sql
connect / as sysdba
CREATE SPFILE='/u01/app/oracle/product/11.2.0.3/db_1/dbs/spfiledb01.ora' from pfile='/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/init.ora';
exit;

[oracle@lax:db01]/u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1
$ cat /u01/app/oracle/cfgtoollogs/dbua/lax_db01/upgrade1/grpOpen_db01.sql

connect / as sysdba
startup mount;
flashback database to restore point grp_1372774067744;
alter database open resetlogs;
exit;

Improved sysresv
$ sysresv

IPC Resources for ORACLE_SID "db01" :
Maximum shared memory segment size (shmmax): 4398046511104 bytes
Total system shared memory (shmall): 4398046511104 bytes
Total system shared memory count (shmmni): 4096
Total /dev/shm size: 1581465600 bytes, used: 278528 bytes
Shared Memory:
ID              KEY
24084484        0x450f83c4
24117253        0x00000000
Semaphores:
ID              KEY
1867778         0x0c97f794
1900547         0x0c97f795
1933316         0x0c97f796
Oracle Instance alive for sid "db01"

Don’t forget to change compatible
$ sqlplus “/ as sysdba”

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 2 14:54:29 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

LAX:(SYS@db01)> show parameter compat

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.3
noncdb_compatible                    boolean     FALSE
plsql_v2_compatibility               boolean     FALSE
LAX:(SYS@db01)> exit

July 2, 2013

RMAN restore

Filed under: 11g,Recovery,RMAN — mdinh @ 1:38 pm

Is my backup any good? Let’s find out.

Today, I needed to restore the database due to a failed upgrade to 12c and here is the process.

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jul 2 06:02:45 2013

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

connected to target database (not started)

RMAN> startup force nomount;

Oracle instance started

Total System Global Area     534462464 bytes

Fixed Size                     2230072 bytes
Variable Size                163580104 bytes
Database Buffers             364904448 bytes
Redo Buffers                   3747840 bytes

RMAN> exit

$ ls -l /oracle/backup/
total 179536
-rw-r----- 1 oracle oinstall 22029824 Jun 28 22:01 DB01_1439793859_20130628_tbodbp63_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall 12934144 Jun 28 22:01 DB01_1439793859_20130628_tcodbp6a_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  1236992 Jun 28 22:01 DB01_1439793859_20130628_tdodbp6d_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  6857728 Jun 28 22:01 DB01_1439793859_20130628_teodbp6f_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall 23293952 Jun 28 22:01 DB01_1439793859_20130628_tfodbp6i_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  1555456 Jun 28 22:01 DB01_1439793859_20130628_tgodbp6l_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  7286272 Jun 28 22:01 DB01_1439793859_20130628_thodbp6m_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  9636352 Jun 28 22:02 DB01_1439793859_20130628_tiodbp6p_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  1377280 Jun 28 22:02 DB01_1439793859_20130628_tjodbp6t_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  5707264 Jun 28 22:02 DB01_1439793859_20130628_tkodbp6u_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall 37765120 Jun 28 22:02 DB01_1439793859_20130628_tlodbp6v_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  4841472 Jun 28 22:02 DB01_1439793859_20130628_tmodbp7e_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall 20168704 Jun 28 22:02 DB01_1439793859_20130628_tnodbp7i_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  2678784 Jun 28 22:02 DB01_1439793859_20130628_toodbp8b_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  1073152 Jun 28 22:02 DB01_1439793859_20130628_tpodbp8i_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  1073152 Jun 28 22:02 DB01_1439793859_20130628_tqodbp8j_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  1073152 Jun 28 22:03 DB01_1439793859_20130628_trodbp8k_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  1073152 Jun 28 22:03 DB01_1439793859_20130628_tsodbp8l_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  1073152 Jun 28 22:03 DB01_1439793859_20130628_ttodbp8n_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  1277952 Jun 28 22:03 DB01_1439793859_20130628_tuodbp8o_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall    42496 Jun 28 22:03 DB01_1439793859_20130628_tvodbp8t_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall    98304 Jun 28 22:03 DB01_1439793859_20130628_u0odbp8u_1_1.inc0_Fri
-rw-r----- 1 oracle oinstall  9830400 Jun 28 22:03 DB01_c-1439793859-20130628-08
-rw-r----- 1 oracle oinstall  9830400 Jul  1 17:06 DB01_c-1439793859-20130701-00

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jul 2 06:03:54 2013

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

connected to target database: DB01 (not mounted)

RMAN> restore controlfile from '/oracle/backup/DB01_c-1439793859-20130628-08';

Starting restore at 02-jul-2013 06:04:17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/oracle/flashrecovery/LAX_DB01/controlfile/o1_mf_8nctts7b_.ctl
Finished restore at 02-jul-2013 06:04:21

RMAN> startup force mount;

Oracle instance started
database mounted

Total System Global Area     534462464 bytes

Fixed Size                     2230072 bytes
Variable Size                163580104 bytes
Database Buffers             364904448 bytes
Redo Buffers                   3747840 bytes

RMAN> restore database;

Starting restore at 02-jul-2013 06:04:45
Starting implicit crosscheck backup at 02-jul-2013 06:04:45
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=19 device type=DISK
Crosschecked 22 objects
Finished implicit crosscheck backup at 02-jul-2013 06:04:48

Starting implicit crosscheck copy at 02-jul-2013 06:04:48
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 02-jul-2013 06:04:48

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

List of Cataloged Files
=======================
File Name: /oracle/flashrecovery/LAX_DB01/archivelog/2013_07_01/o1_mf_1_136_8x450667_.arc

using channel ORA_DISK_1
using channel ORA_DISK_2

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 /oracle/oradata/db02/system_1.dbf
channel ORA_DISK_1: reading from backup piece /oracle/backup/DB01_1439793859_20130628_tlodbp6v_1_1.inc0_Fri
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00004 to /oracle/oradata/db02/user_data_1.dbf
channel ORA_DISK_2: reading from backup piece /oracle/backup/DB01_1439793859_20130628_tmodbp7e_1_1.inc0_Fri
channel ORA_DISK_2: piece handle=/oracle/backup/DB01_1439793859_20130628_tmodbp7e_1_1.inc0_Fri tag=INC0_FRI
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:25
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00002 to /oracle/oradata/db02/sysaux_1.dbf
channel ORA_DISK_2: reading from backup piece /oracle/backup/DB01_1439793859_20130628_tnodbp7i_1_1.inc0_Fri
channel ORA_DISK_1: piece handle=/oracle/backup/DB01_1439793859_20130628_tlodbp6v_1_1.inc0_Fri tag=INC0_FRI
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:30
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 00003 to /oracle/oradata/db02/undotbs_1.dbf
channel ORA_DISK_1: reading from backup piece /oracle/backup/DB01_1439793859_20130628_toodbp8b_1_1.inc0_Fri
channel ORA_DISK_2: piece handle=/oracle/backup/DB01_1439793859_20130628_tnodbp7i_1_1.inc0_Fri tag=INC0_FRI
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:05
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00005 to /oracle/oradata/db02/udata_1.dbf
channel ORA_DISK_2: reading from backup piece /oracle/backup/DB01_1439793859_20130628_tpodbp8i_1_1.inc0_Fri
channel ORA_DISK_2: piece handle=/oracle/backup/DB01_1439793859_20130628_tpodbp8i_1_1.inc0_Fri tag=INC0_FRI
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:25
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00006 to /oracle/oradata/db02/user_index_1.dbf
channel ORA_DISK_2: reading from backup piece /oracle/backup/DB01_1439793859_20130628_tqodbp8j_1_1.inc0_Fri
channel ORA_DISK_1: piece handle=/oracle/backup/DB01_1439793859_20130628_toodbp8b_1_1.inc0_Fri tag=INC0_FRI
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
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 00007 to /oracle/oradata/db02/user_data_2.dbf
channel ORA_DISK_1: reading from backup piece /oracle/backup/DB01_1439793859_20130628_trodbp8k_1_1.inc0_Fri
channel ORA_DISK_1: piece handle=/oracle/backup/DB01_1439793859_20130628_trodbp8k_1_1.inc0_Fri tag=INC0_FRI
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00008 to /oracle/oradata/db02/nidx_1.dbf
channel ORA_DISK_1: reading from backup piece /oracle/backup/DB01_1439793859_20130628_tsodbp8l_1_1.inc0_Fri
channel ORA_DISK_2: piece handle=/oracle/backup/DB01_1439793859_20130628_tqodbp8j_1_1.inc0_Fri tag=INC0_FRI
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:07
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00009 to /oracle/oradata/db02/nlob_1.dbf
channel ORA_DISK_2: reading from backup piece /oracle/backup/DB01_1439793859_20130628_ttodbp8n_1_1.inc0_Fri
channel ORA_DISK_1: piece handle=/oracle/backup/DB01_1439793859_20130628_tsodbp8l_1_1.inc0_Fri tag=INC0_FRI
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_2: piece handle=/oracle/backup/DB01_1439793859_20130628_ttodbp8n_1_1.inc0_Fri tag=INC0_FRI
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:07
Finished restore at 02-jul-2013 06:07:00

RMAN> list backup of archivelog all;

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
855     21.01M     DISK        00:00:03     28-jun-2013 22:01:42
        BP Key: 855   Status: AVAILABLE  Compressed: YES  Tag: INC0_FRI
        Piece Name: /oracle/backup/DB01_1439793859_20130628_tbodbp63_1_1.inc0_Fri

  List of Archived Logs in backup set 855
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    69      1263640    14-jun-2013 20:24:54 1263670    14-jun-2013 20:25:37
  1    70      1263670    14-jun-2013 20:25:37 1285630    15-jun-2013 06:03:19

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
856     12.33M     DISK        00:00:02     28-jun-2013 22:01:48
        BP Key: 856   Status: AVAILABLE  Compressed: YES  Tag: INC0_FRI
        Piece Name: /oracle/backup/DB01_1439793859_20130628_tcodbp6a_1_1.inc0_Fri

  List of Archived Logs in backup set 856
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    71      1285630    15-jun-2013 06:03:19 1311478    21-jun-2013 14:08:17
  1    72      1311478    21-jun-2013 14:08:17 1334378    22-jun-2013 10:30:56
  1    73      1334378    22-jun-2013 10:30:56 1337697    22-jun-2013 10:38:42

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
857     1.18M      DISK        00:00:01     28-jun-2013 22:01:50
        BP Key: 857   Status: AVAILABLE  Compressed: YES  Tag: INC0_FRI
        Piece Name: /oracle/backup/DB01_1439793859_20130628_tdodbp6d_1_1.inc0_Fri

  List of Archived Logs in backup set 857
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    74      1337697    22-jun-2013 10:38:42 1338000    22-jun-2013 10:45:53
  1    75      1338000    22-jun-2013 10:45:53 1338078    22-jun-2013 10:46:27
  1    76      1338078    22-jun-2013 10:46:27 1338436    22-jun-2013 10:47:21
  1    77      1338436    22-jun-2013 10:47:21 1338524    22-jun-2013 10:47:33
  1    78      1338524    22-jun-2013 10:47:33 1338551    22-jun-2013 10:48:07
  1    79      1338551    22-jun-2013 10:48:07 1338727    22-jun-2013 10:50:49
  1    80      1338727    22-jun-2013 10:50:49 1338803    22-jun-2013 10:51:18
  1    81      1338803    22-jun-2013 10:51:18 1338919    22-jun-2013 10:54:58

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
858     6.54M      DISK        00:00:01     28-jun-2013 22:01:52
        BP Key: 858   Status: AVAILABLE  Compressed: YES  Tag: INC0_FRI
        Piece Name: /oracle/backup/DB01_1439793859_20130628_teodbp6f_1_1.inc0_Fri

  List of Archived Logs in backup set 858
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    82      1338919    22-jun-2013 10:54:58 1338943    22-jun-2013 10:55:25
  1    83      1338943    22-jun-2013 10:55:25 1339111    22-jun-2013 10:58:58
  1    84      1339111    22-jun-2013 10:58:58 1339136    22-jun-2013 10:59:25
  1    85      1339136    22-jun-2013 10:59:25 1339690    22-jun-2013 11:18:52
  1    86      1339690    22-jun-2013 11:18:52 1339721    22-jun-2013 11:19:37
  1    87      1339721    22-jun-2013 11:19:37 1339880    22-jun-2013 11:22:45
  1    88      1339880    22-jun-2013 11:22:45 1339909    22-jun-2013 11:23:24
  1    89      1339909    22-jun-2013 11:23:24 1351681    22-jun-2013 17:42:30

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
859     22.21M     DISK        00:00:02     28-jun-2013 22:01:56
        BP Key: 859   Status: AVAILABLE  Compressed: YES  Tag: INC0_FRI
        Piece Name: /oracle/backup/DB01_1439793859_20130628_tfodbp6i_1_1.inc0_Fri

  List of Archived Logs in backup set 859
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    90      1351681    22-jun-2013 17:42:30 1351714    22-jun-2013 17:43:16
  1    91      1351714    22-jun-2013 17:43:16 1351826    22-jun-2013 17:44:39
  1    92      1351826    22-jun-2013 17:44:39 1351861    22-jun-2013 17:45:22
  1    93      1351861    22-jun-2013 17:45:22 1351958    22-jun-2013 17:46:02
  1    94      1351958    22-jun-2013 17:46:02 1351996    22-jun-2013 17:46:52
  1    95      1351996    22-jun-2013 17:46:52 1386594    23-jun-2013 11:14:04

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
860     1.48M      DISK        00:00:00     28-jun-2013 22:01:57
        BP Key: 860   Status: AVAILABLE  Compressed: YES  Tag: INC0_FRI
        Piece Name: /oracle/backup/DB01_1439793859_20130628_tgodbp6l_1_1.inc0_Fri

  List of Archived Logs in backup set 860
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    96      1386594    23-jun-2013 11:14:04 1386656    23-jun-2013 11:14:59
  1    97      1386656    23-jun-2013 11:14:59 1386846    23-jun-2013 11:15:52
  1    98      1386846    23-jun-2013 11:15:52 1387089    23-jun-2013 11:21:53
  1    99      1387089    23-jun-2013 11:21:53 1387121    23-jun-2013 11:22:42
  1    100     1387121    23-jun-2013 11:22:42 1390442    23-jun-2013 13:34:48
  1    101     1390442    23-jun-2013 13:34:48 1390476    23-jun-2013 13:35:25
  1    102     1390476    23-jun-2013 13:35:25 1390885    23-jun-2013 13:47:06
  1    103     1390885    23-jun-2013 13:47:06 1390921    23-jun-2013 13:47:49

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
861     6.95M      DISK        00:00:01     28-jun-2013 22:01:59
        BP Key: 861   Status: AVAILABLE  Compressed: YES  Tag: INC0_FRI
        Piece Name: /oracle/backup/DB01_1439793859_20130628_thodbp6m_1_1.inc0_Fri

  List of Archived Logs in backup set 861
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    104     1390921    23-jun-2013 13:47:49 1391006    23-jun-2013 13:48:47
  1    105     1391006    23-jun-2013 13:48:47 1391034    23-jun-2013 13:49:23
  1    106     1391034    23-jun-2013 13:49:23 1397432    23-jun-2013 16:13:40
  1    107     1397432    23-jun-2013 16:13:40 1397462    23-jun-2013 16:14:22
  1    108     1397462    23-jun-2013 16:14:22 1397656    23-jun-2013 16:17:59
  1    109     1397656    23-jun-2013 16:17:59 1397695    23-jun-2013 16:19:10
  1    110     1397695    23-jun-2013 16:19:10 1397815    23-jun-2013 16:20:15
  1    111     1397815    23-jun-2013 16:20:15 1397853    23-jun-2013 16:21:16

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
862     9.19M      DISK        00:00:02     28-jun-2013 22:02:03
        BP Key: 862   Status: AVAILABLE  Compressed: YES  Tag: INC0_FRI
        Piece Name: /oracle/backup/DB01_1439793859_20130628_tiodbp6p_1_1.inc0_Fri

  List of Archived Logs in backup set 862
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    112     1397853    23-jun-2013 16:21:16 1398226    23-jun-2013 16:35:17
  1    113     1398226    23-jun-2013 16:35:17 1398247    23-jun-2013 16:35:32
  1    114     1398247    23-jun-2013 16:35:32 1423648    24-jun-2013 08:13:39
  1    115     1423648    24-jun-2013 08:13:39 1450863    28-jun-2013 17:52:16
  1    116     1450863    28-jun-2013 17:52:16 1471023    28-jun-2013 20:55:14
  1    117     1471023    28-jun-2013 20:55:14 1471238    28-jun-2013 20:58:24
  1    118     1471238    28-jun-2013 20:58:24 1471271    28-jun-2013 20:59:17
  1    119     1471271    28-jun-2013 20:59:17 1471466    28-jun-2013 21:03:46

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
863     1.31M      DISK        00:00:00     28-jun-2013 22:02:05
        BP Key: 863   Status: AVAILABLE  Compressed: YES  Tag: INC0_FRI
        Piece Name: /oracle/backup/DB01_1439793859_20130628_tjodbp6t_1_1.inc0_Fri

  List of Archived Logs in backup set 863
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    120     1471466    28-jun-2013 21:03:46 1471508    28-jun-2013 21:05:00
  1    121     1471508    28-jun-2013 21:05:00 1471606    28-jun-2013 21:05:48
  1    122     1471606    28-jun-2013 21:05:48 1471703    28-jun-2013 21:07:03
  1    123     1471703    28-jun-2013 21:07:03 1471832    28-jun-2013 21:09:18
  1    124     1471832    28-jun-2013 21:09:18 1471874    28-jun-2013 21:10:19
  1    125     1471874    28-jun-2013 21:10:19 1471940    28-jun-2013 21:10:44
  1    126     1471940    28-jun-2013 21:10:44 1472320    28-jun-2013 21:11:27
  1    127     1472320    28-jun-2013 21:11:27 1472806    28-jun-2013 21:30:06

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
864     5.44M      DISK        00:00:00     28-jun-2013 22:02:06
        BP Key: 864   Status: AVAILABLE  Compressed: YES  Tag: INC0_FRI
        Piece Name: /oracle/backup/DB01_1439793859_20130628_tkodbp6u_1_1.inc0_Fri

  List of Archived Logs in backup set 864
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    128     1472806    28-jun-2013 21:30:06 1472858    28-jun-2013 21:30:38
  1    129     1472858    28-jun-2013 21:30:38 1472879    28-jun-2013 21:30:55
  1    130     1472879    28-jun-2013 21:30:55 1473684    28-jun-2013 21:57:32
  1    131     1473684    28-jun-2013 21:57:32 1473722    28-jun-2013 21:58:34
  1    132     1473722    28-jun-2013 21:58:34 1473801    28-jun-2013 21:59:35
  1    133     1473801    28-jun-2013 21:59:35 1474126    28-jun-2013 22:00:05
  1    134     1474126    28-jun-2013 22:00:05 1476611    28-jun-2013 22:01:37

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
875     41.00K     DISK        00:00:00     28-jun-2013 22:03:09
        BP Key: 875   Status: AVAILABLE  Compressed: YES  Tag: INC0_FRI
        Piece Name: /oracle/backup/DB01_1439793859_20130628_tvodbp8t_1_1.inc0_Fri

  List of Archived Logs in backup set 875
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    135     1476611    28-jun-2013 22:01:37 1476657    28-jun-2013 22:03:08

RMAN> recover database until sequence 136;

Starting recover at 02-jul-2013 06:08:54
using channel ORA_DISK_1
using channel ORA_DISK_2
datafile 6 not processed because file is read-only
datafile 7 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 135 is already on disk as file /oracle/flashrecovery/LAX_DB01/archivelog/2013_06_28/o1_mf_1_135_8wwtndz2_.arc
archived log file name=/oracle/flashrecovery/LAX_DB01/archivelog/2013_06_28/o1_mf_1_135_8wwtndz2_.arc thread=1 sequence=135
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-jul-2013 06:08:57

RMAN> alter database open resetlogs;

database opened

RMAN>

Blog at WordPress.com.