Skip navigation.

Michael Dinh

Syndicate content Thinking Out Loud
Michael T. Dinh, Oracle DBA
Updated: 16 hours 35 min ago

Create GoldenGate 12.2 Database User

Sat, 2016-04-09 08:33

Oracle GoldenGate for Windows and UNIX 12c (12.2.0.1)

First, I am disappointed that Oracle does not go above and beyond to provide SQL scripts to create GoldenGate users for the database.

There are different set of privileges depending on the version of the database:

4.1.4.2 Oracle 11.2.0.3 or Earlier Database Privileges
4.1.4.1 Oracle 11.2.0.4 or Later Database Privileges

PDB is not being used and it’s different for PDB.

Depending on whether you want to practice the least principle privileges, ggadmin user can be create with privileges for both extract (capture) and replicat (apply).

Please don’t forget to change the password from the script since it is hard coded to be same as username :=)

cr_ggadmin_12c.sql
-- 4.1.4.1 Oracle 11.2.0.4 or Later Database Privileges
set echo on lines 200 pages 1000 trimspool on tab off
define _username='GGADMIN'
-- grant privileges for capture
create user &_username identified by &_username default tablespace ggdata;
select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='&_username';
grant create session, connect, resource, alter any table, alter system, dba, select any transaction to &_username;
-- grant privileges for replicat
grant create table, lock any table to &_username;
-- grant both capture and apply
exec dbms_goldengate_auth.grant_admin_privilege('&_username')
-- grant capture
-- exec dbms_goldengate_auth.grant_admin_privilege('&_username','capture');
-- grant apply
-- exec dbms_goldengate_auth.grant_admin_privilege('&_username','apply');

Demo:

oracle@arrow:tiger:/media/sf_working/ggs
$ sysdba @cr_ggadmin_12c.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sat Apr 9 07:06:41 2016

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

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

ARROW:(SYS@tiger):PRIMARY> define _username='GGADMIN'
ARROW:(SYS@tiger):PRIMARY> -- grant privileges for capture
ARROW:(SYS@tiger):PRIMARY> create user &_username identified by &_username default tablespace ggdata;

User created.

ARROW:(SYS@tiger):PRIMARY> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='&_username';

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------
GGDATA                         TEMP

ARROW:(SYS@tiger):PRIMARY> grant create session, connect, resource, alter any table, alter system, dba, select any transaction to &_username;

Grant succeeded.

ARROW:(SYS@tiger):PRIMARY> -- grant privileges for replicat
ARROW:(SYS@tiger):PRIMARY> grant create table, lock any table to &_username;

Grant succeeded.

ARROW:(SYS@tiger):PRIMARY> -- grant both capture and apply
ARROW:(SYS@tiger):PRIMARY> exec dbms_goldengate_auth.grant_admin_privilege('&_username')

PL/SQL procedure successfully completed.

ARROW:(SYS@tiger):PRIMARY> -- grant capture
ARROW:(SYS@tiger):PRIMARY> -- exec dbms_goldengate_auth.grant_admin_privilege('&_username','capture');
ARROW:(SYS@tiger):PRIMARY> -- grant apply
ARROW:(SYS@tiger):PRIMARY> -- exec dbms_goldengate_auth.grant_admin_privilege('&_username','apply');
ARROW:(SYS@tiger):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:tiger:/media/sf_working/ggs
$

Clone GoldenGate 12.2

Mon, 2016-04-04 03:16

This is what insomnia does to you.

Since GoldenGate installation is now using runInstaller, I would expect the same functionality as the database; hence, cloning GoldenGate Home.

oracle@arrow:pooh:/home/oracle
$ cd /u01/app/12.2.0.1/ggs01/

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs01
$ ls
bcpfmt.tpl            ddl_ora10.sql             defgen                           extract        libggnnzitp.so      marker_status.sql       role_setup.sql
bcrypt.txt            ddl_ora10upCommon.sql     deinstall                        freeBSD.txt    libggparam.so       mgr                     sequence.sql
cachefiledump         ddl_ora11.sql             demo_more_ora_create.sql         ggcmd          libggperf.so        notices.txt             server
cfgtoollogs           ddl_ora9.sql              demo_more_ora_insert.sql         ggMessage.dat  libggrepo.so        oggerr                  sqlldr.tpl
checkprm              ddl_pin.sql               demo_ora_create.sql              ggparam.dat    libicudata.so.48    OPatch                  srvm
chkpt_ora_create.sql  ddl_remove.sql            demo_ora_insert.sql              ggsci          libicudata.so.48.1  oraInst.loc             tcperrs
convchk               ddl_session1.sql          demo_ora_lob_create.sql          help.txt       libicui18n.so.48    oui                     ucharset.h
convprm               ddl_session.sql           demo_ora_misc.sql                install        libicui18n.so.48.1  params.sql              ulg.sql
db2cntl.tpl           ddl_setup.sql             demo_ora_pk_befores_create.sql   inventory      libicuuc.so.48      prvtclkm.plb            UserExitExamples
ddl_cleartrace.sql    ddl_status.sql            demo_ora_pk_befores_insert.sql   jdk            libicuuc.so.48.1    prvtlmpg.plb            usrdecs.h
ddl_create.sql        ddl_staymetadata_off.sql  demo_ora_pk_befores_updates.sql  keygen         libxerces-c.so.28   prvtlmpg_uninstall.sql  zlib.txt
ddl_ddl2file.sql      ddl_staymetadata_on.sql   diagnostics                      label.sql      libxml2.txt         remove_seq.sql
ddl_disable.sql       ddl_tracelevel.sql        dirout                           libantlr3c.so  logdump             replicat
ddl_enable.sql        ddl_trace_off.sql         dirwww                           libdb-6.1.so   marker_remove.sql   retrace
ddl_filter.sql        ddl_trace_on.sql          emsclnt                          libgglog.so    marker_setup.sql    reverse

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs01
$ nohup tar -cvpf /tmp/ggs12c_${USER}_`hostname -s`_clone.tar . > /tmp/clone_${USER}_`hostname -s`_ggs12c.log 2>&1 &
[1] 10859
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs01
$
[1]+  Done                    nohup tar -cvpf /tmp/ggs12c_${USER}_`hostname -s`_clone.tar . > /tmp/clone_${USER}_`hostname -s`_ggs12c.log 2>&1

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs01
$ cd ..
oracle@arrow:pooh:/u01/app/12.2.0.1
$ mkdir ggs02
oracle@arrow:pooh:/u01/app/12.2.0.1
$ cd ggs02/
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ ls

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ nohup tar -xvf /tmp/ggs12c_oracle_arrow_clone.tar > /tmp/untar_${USER}_`hostname -s`_ggs12c.log 2>&1 &
[1] 10897
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$
[1]+  Done                    nohup tar -xvf /tmp/ggs12c_oracle_arrow_clone.tar > /tmp/untar_${USER}_`hostname -s`_ggs12c.log 2>&1

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ ll /tmp/*ggs*
-rw-r--r--. 1 oracle oinstall     84131 Apr  4 01:36 /tmp/clone_oracle_arrow_ggs12c.log
-rw-r--r--. 1 oracle oinstall 835788800 Apr  4 01:36 /tmp/ggs12c_oracle_arrow_clone.tar
-rw-r--r--. 1 oracle oinstall     84131 Apr  4 01:45 /tmp/untar_oracle_arrow_ggs12c.log

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ tail /tmp/clone_oracle_arrow_ggs12c.log
./jdk/bin/javadoc
./jdk/bin/jarsigner
./jdk/bin/pack200
./jdk/bin/rmid
./jdk/bin/jrunscript
./jdk/bin/extcheck
./jdk/bin/keytool
./jdk/LICENSE
./jdk/.manifest
./ddl_setup.sql

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ tail /tmp/untar_oracle_arrow_ggs12c.log
./jdk/bin/javadoc
./jdk/bin/jarsigner
./jdk/bin/pack200
./jdk/bin/rmid
./jdk/bin/jrunscript
./jdk/bin/extcheck
./jdk/bin/keytool
./jdk/LICENSE
./jdk/.manifest
./ddl_setup.sql

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ ls
bcpfmt.tpl            ddl_ora10.sql             defgen                           extract        libggnnzitp.so      marker_status.sql       role_setup.sql
bcrypt.txt            ddl_ora10upCommon.sql     deinstall                        freeBSD.txt    libggparam.so       mgr                     sequence.sql
cachefiledump         ddl_ora11.sql             demo_more_ora_create.sql         ggcmd          libggperf.so        notices.txt             server
cfgtoollogs           ddl_ora9.sql              demo_more_ora_insert.sql         ggMessage.dat  libggrepo.so        oggerr                  sqlldr.tpl
checkprm              ddl_pin.sql               demo_ora_create.sql              ggparam.dat    libicudata.so.48    OPatch                  srvm
chkpt_ora_create.sql  ddl_remove.sql            demo_ora_insert.sql              ggsci          libicudata.so.48.1  oraInst.loc             tcperrs
convchk               ddl_session1.sql          demo_ora_lob_create.sql          help.txt       libicui18n.so.48    oui                     ucharset.h
convprm               ddl_session.sql           demo_ora_misc.sql                install        libicui18n.so.48.1  params.sql              ulg.sql
db2cntl.tpl           ddl_setup.sql             demo_ora_pk_befores_create.sql   inventory      libicuuc.so.48      prvtclkm.plb            UserExitExamples
ddl_cleartrace.sql    ddl_status.sql            demo_ora_pk_befores_insert.sql   jdk            libicuuc.so.48.1    prvtlmpg.plb            usrdecs.h
ddl_create.sql        ddl_staymetadata_off.sql  demo_ora_pk_befores_updates.sql  keygen         libxerces-c.so.28   prvtlmpg_uninstall.sql  zlib.txt
ddl_ddl2file.sql      ddl_staymetadata_on.sql   diagnostics                      label.sql      libxml2.txt         remove_seq.sql
ddl_disable.sql       ddl_tracelevel.sql        dirout                           libantlr3c.so  logdump             replicat
ddl_enable.sql        ddl_trace_off.sql         dirwww                           libdb-6.1.so   marker_remove.sql   retrace
ddl_filter.sql        ddl_trace_on.sql          emsclnt                          libgglog.so    marker_setup.sql    reverse

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ cd oui/bin/
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin
$ ls run*
runConfig.sh  runInstaller  runInstaller.sh  runSSHSetup.sh

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin
$ ./runInstaller -clone -silent -noconfig -defaultHomeName ORACLE_HOME="/u01/app/12.2.0.1/ggs02"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4072 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-04-04_01-50-47AM. Please wait ...oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin
$ Oracle Universal Installer, Version 11.2.0.3.0 Production
Copyright (C) 1999, 2011, Oracle. All rights reserved.

You can find the log of this install session at:
 /u01/app/oraInventory/logs/cloneActions2016-04-04_01-50-47AM.log
.................................................................................................... 100% Done.



Installation in progress (Monday, April 4, 2016 1:50:56 AM PDT)
..........................................................................                                                      72% Done.
Install successful

Linking in progress (Monday, April 4, 2016 1:50:59 AM PDT)
Link successful

Setup in progress (Monday, April 4, 2016 1:50:59 AM PDT)
...........                                                     100% Done.
Setup successful

End of install phases.(Monday, April 4, 2016 1:51:20 AM PDT)
The cloning of OraHome3 was successful.
Please check '/u01/app/oraInventory/logs/cloneActions2016-04-04_01-50-47AM.log' for more details.

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin
$ grep -e '[[:upper:]]: ' /u01/app/oraInventory/logs/cloneActions2016-04-04_01-50-47AM.log|cut -d ":" -f1|sort -u
INFO

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin
$ cd ../../

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.


GGSCI (arrow.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED

GGSCI (arrow.localdomain) 2> show

Parameter settings:

SET SUBDIRS    ON
SET DEBUG      OFF

Current directory: /u01/app/12.2.0.1/ggs02

Using subdirectories for all process files

Editor:  vi

Reports (.rpt)                 /u01/app/12.2.0.1/ggs02/dirrpt -- does not yet exist
Parameters (.prm)              /u01/app/12.2.0.1/ggs02/dirprm -- does not yet exist
Replicat Checkpoints (.cpr)    /u01/app/12.2.0.1/ggs02/dirchk -- does not yet exist
Extract Checkpoints (.cpe)     /u01/app/12.2.0.1/ggs02/dirchk -- does not yet exist
Process Status (.pcs)          /u01/app/12.2.0.1/ggs02/dirpcs -- does not yet exist
SQL Scripts (.sql)             /u01/app/12.2.0.1/ggs02/dirsql -- does not yet exist
Database Definitions (.def)    /u01/app/12.2.0.1/ggs02/dirdef -- does not yet exist
Dump files (.dmp)              /u01/app/12.2.0.1/ggs02/dirdmp -- does not yet exist
Masterkey wallet files (.wlt)  /u01/app/12.2.0.1/ggs02/dirwlt -- does not yet exist
Credential store files (.crd)  /u01/app/12.2.0.1/ggs02/dircrd -- does not yet exist


GGSCI (arrow.localdomain) 3> create subdirs

Creating subdirectories under current directory /u01/app/12.2.0.1/ggs02

Parameter files                /u01/app/12.2.0.1/ggs02/dirprm: created
Report files                   /u01/app/12.2.0.1/ggs02/dirrpt: created
Checkpoint files               /u01/app/12.2.0.1/ggs02/dirchk: created
Process status files           /u01/app/12.2.0.1/ggs02/dirpcs: created
SQL script files               /u01/app/12.2.0.1/ggs02/dirsql: created
Database definitions files     /u01/app/12.2.0.1/ggs02/dirdef: created
Extract data files             /u01/app/12.2.0.1/ggs02/dirdat: created
Temporary files                /u01/app/12.2.0.1/ggs02/dirtmp: created
Credential store files         /u01/app/12.2.0.1/ggs02/dircrd: created
Masterkey wallet files         /u01/app/12.2.0.1/ggs02/dirwlt: created
Dump files                     /u01/app/12.2.0.1/ggs02/dirdmp: created


GGSCI (arrow.localdomain) 4> exit
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$

There is no clone.pl for GoldenGate.
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ locate clone.pl
/u01/app/oracle/product/11.2.0/dbhome_1/clone/bin/clone.pl
/u01/app/oracle/product/11.2.0/dbhome_1/clone/bin/prepare_clone.pl
/u01/app/oracle/product/11.2.0/dbhome_1/sysman/admin/scripts/db/dbclone/db_clone.pl
/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin/clone.pl
/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin/clone.pl.sbs
/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin/clone.pl.sbs.ouibak
/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin/prepare_clone.pl
/u01/app/oracle/product/12.2.0/dbhome_2/inventory/Templates/clone/bin/clone.pl.sbs
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$

oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ cat /u01/app/oraInventory/ContentsXML/inventory.xml
<?xml version=”1.0″ standalone=”yes” ?>
<!– Copyright (c) 1999, 2011, Oracle. All rights reserved. –>
<!– Do not modify the contents of this file by hand. –>
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>11.2.0.3.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”OraDb11g_home1″ LOC=”/u01/app/oracle/product/11.2.0/dbhome_1″ TYPE=”O” IDX=”1″/>
<HOME NAME=”OraDB12Home1″ LOC=”/u01/app/oracle/product/12.2.0/dbhome_1″ TYPE=”O” IDX=”2″/>
<HOME NAME=”OraHome1″ LOC=”/u01/app/oracle/product/12.2.0/dbhome_2″ TYPE=”O” IDX=”3″/>
<HOME NAME=”OraHome2″ LOC=”/u01/app/12.2.0.1/ggs01″ TYPE=”O” IDX=”4″/>
<HOME NAME=”OraHome3″ LOC=”/u01/app/12.2.0.1/ggs02″ TYPE=”O” IDX=”5″/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>


Silent Install GoldenGate 12.2

Mon, 2016-04-04 02:31

There are probably many blogs about installation; however, I tend to do things a little different.

oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$ cat /media/sf_working/oggcore_11gdb.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/u01/app/12.2.0.1/ggs01
START_MANAGER=
MANAGER_PORT=
DATABASE_LOCATION=/u01/app/oracle/product/11.2.0/dbhome_1
INVENTORY_LOCATION=/u01/app/oraInventory
UNIX_GROUP_NAME=oinstall

I like to see installation progress.

oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile /media/sf_working/oggcore_11gdb.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 28152 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4073 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-04-04_01-04-39AM. Please wait ...You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2016-04-04_01-04-39AM.log

Prepare in progress.
..................................................   10% Done.

Prepare successful.

Copy files in progress.
..................................................   61% Done.
..................................................   69% Done.
..................................................   77% Done.
..................................................   82% Done.
..................................................   87% Done.
..................................................   94% Done.
..................................................   95% Done.

Copy files successful.

Link binaries in progress.
..................................................   95% Done.

Link binaries successful.

Setup files in progress.
..................................................   95% Done.
..................................................   96% Done.
..................................................   97% Done.
..................................................   98% Done.
..................................................   99% Done.
..................................................   100% Done.

Setup files successful.
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2016-04-04_01-04-39AM.log' for more details.
Successfully Setup Software.


oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$ cat /u01/app/oraInventory/logs/silentInstall2016-04-04_01-04-39AM.log
silentInstall2016-04-04_01-04-39AM.log
The installation of Oracle GoldenGate Core was successful.

I like to see distinct log type: INFO|WARNING|ERROR.

oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$ grep -e '[[:upper:]]: ' /u01/app/oraInventory/logs/installActions2016-04-04_01-04-39AM.log|cut -d ":" -f1|sort -u
INFO
WARNING

I like to see what WARNING are all about and to compare with subsequent install.

oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$ grep -e '[[:upper:]]: ' /u01/app/oraInventory/logs/installActions2016-04-04_01-04-39AM.log|grep "^WARNING: "
WARNING: CVU is not enabled. No CVU based operation will be performed.
WARNING: Unable to find the namespace URI. Reason: Start of root element expected.
WARNING: Unable to find the namespace URI. Reason: Start of root element expected.
WARNING: Unable to get the value for INSTALL_OPTION. Error: null
WARNING: Unable to get the value for INSTALL_OPTION. Error: null
WARNING: Unable to get the value for INSTALL_OPTION. Error: null
WARNING: Validation disabled for the state init
WARNING: Validation disabled for the state summary
WARNING: Validation disabled for the state finish

Is WARNING bogus?

oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$ grep INSTALL_OPTION /u01/app/oraInventory/logs/installActions2016-04-04_01-04-39AM.log
WARNING: Unable to get the value for INSTALL_OPTION. Error: null
WARNING: Unable to get the value for INSTALL_OPTION. Error: null
WARNING: Unable to get the value for INSTALL_OPTION. Error: null
INFO: Setting value for the property:INSTALL_OPTION in the bean:OGGInstallSettings
 INSTALL_OPTION                                         ORA11g
 INSTALL_OPTION            ORA11g
INFO: adding the variable INSTALL_OPTION to command line args table
oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1
$

Deinstall GoldenGate 12.2

Mon, 2016-04-04 01:59

I always like to know how to remove software installation in the event I am not happy with how it was installed.

GoldenGate Home is at /u01/app/oracle/12.2/ggs01

Thinking of may be using the same directory structures as Grid Infrastructure, i.e. /u01/app/12.2.0.1/ggs01 with full release version.

oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01
$ ls -ld deinstall/
drwxr-xr-x. 2 oracle oinstall 4096 Apr  4 00:24 deinstall/

oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01
$ cd deinstall/

oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01/deinstall
$ ls
deinstall.sh

oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01/deinstall
$ ./deinstall.sh

ALERT: Ensure all the processes running from the current Oracle Home are shutdown prior to running this software uninstallation script.

Proceed with removing Oracle GoldenGate home: /u01/app/oracle/12.2/ggs01 (yes/no)? [no]
yes
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4073 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-04-04_12-48-07AM. Please wait ...Oracle Universal Installer, Version 11.2.0.3.0 Production
Copyright (C) 1999, 2011, Oracle. All rights reserved.

Starting deinstall


Deinstall in progress (Monday, April 4, 2016 12:48:12 AM PDT)
............................................................... 100% Done.

Deinstall successful

End of install phases.(Monday, April 4, 2016 12:48:33 AM PDT)
End of deinstallations
Please check '/u01/app/oraInventory/logs/silentInstall2016-04-04_12-48-07AM.log' for more details.

oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01/deinstall
$ cat /u01/app/oraInventory/logs/silentInstall2016-04-04_12-48-07AM.log
silentInstall2016-04-04_12-48-07AM.log
Starting deinstall
This deinstallation was successful
End of deinstallations
oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01/deinstall
$

Easy Way to Create pfile from spfile

Sun, 2016-04-03 10:50

I learned something new today; so excited as this will make automation so much easier.

create pfile=’/tmp/init@.ora’ from spfile;

The @ automatically substitute SID.

oracle@arrow:tiger:/tmp
$ sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 3 09:45:49 2016

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


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

ARROW:(SYS@tiger):PRIMARY> set time on
09:45:52 ARROW:(SYS@tiger):PRIMARY> !ls -l /tmp/init*
ls: cannot access /tmp/init*: No such file or directory

09:45:59 ARROW:(SYS@tiger):PRIMARY> create pfile='/tmp/init@.ora' from spfile;

File created.

09:46:04 ARROW:(SYS@tiger):PRIMARY> !ls -l /tmp/init*
-rw-r--r--. 1 oracle oinstall 1165 Apr  3 09:46 /tmp/inittiger.ora

09:46:10 ARROW:(SYS@tiger):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:tiger:/tmp
$


Tablespace Free w Fragmentation Info

Thu, 2016-03-24 13:43

I have been conversing with Liron Amitzi on twitter about his tablespace free SQL script.

He accommodate my request to add fragmentation details so I can verify against what I have.

Looks like I did it right.

You can find Liron script at this blog – Tablespaces Free Space and Stuff

oracle@arrow:tiger:/media/sf_working/sql
$ sysdba @ts_free_space.sql

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 24 12:35:23 2016

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


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


TABLESPACE_NAME                   CURR_SIZE     MAX_SIZE    FREE_SIZE    USED_SIZE     PCT_FREE PCT_FREE_TOTAL  FREE_CHUNKS LARGEST_CHUNK
------------------------------ ------------ ------------ ------------ ------------ ------------ -------------- ------------ -------------
USERS                                  1024         1024      1021.38         2.62        99.74          99.74            2       1021.13
UNDOTBS1                                 80        32767        64.75        15.25        80.94          99.95           12            37
SYSAUX                                  400        32767       218.94       181.06        54.73          99.45            1        218.94
SYSTEM                                  500        32767       231.19       268.81        46.24          99.18            3           231

ARROW:(SYS@tiger):PRIMARY> @free.sql
Enter value for 1: *

                                                              Database Freespace Summary

TABLESPACE_NAME                      BLKSZ   DFCT      CT_FRAG MB_FREE_FRAG     MB_FREE     MB_TOTAL PCT_USED MAX_MB_FREE MAX_PCT_USED
----------------------------------- ------ ------ ------------ ------------ ----------- ------------ -------- ----------- ------------
*m s SYSTEM                           8192      1            3          231         231          499    53.71      32,767          .82
*m s UNDOTBS1                         8192      1           12           37          65           79    53.16      32,767          .13
*a s SYSAUX                           8192      1            1          219         219          399    45.13      32,767          .55
*m s USERS                            8192      1            2        1,021       1,021         1023      .18       1,023          .18
                                           ------                           -----------                       -----------
sum                                             4                                 1,536                            99,324

ARROW:(SYS@tiger):PRIMARY>

free.sql

set line 150 echo off verify off
ttitle -
   center  'Database Freespace Summary'  skip 2
break   on report
COMPUTE sum of mb_used on report
COMPUTE sum of mb_free on report
COMPUTE sum of max_mb_free on report
COMPUTE sum of dfct on report
COLUMN mb_used       format 99,999,999
COLUMN mb_free       format 99,999,999
COLUMN max_mb_free   format 99,999,999
COLUMN mb_free_frag  format 99,999,999
COLUMN dfct          format 99999
COLUMN blksz         format 99999
COLUMN pct_used      format 999.99
COLUMN max_pct_used  format 999.99
SELECT
  DECODE(extent_management,'LOCAL','*',' ') ||
  DECODE(segment_space_management,'AUTO','a ','m ') ||
  DECODE(allocation_type,'SYSTEM','s ','u ') ||
  fs.tablespace_name tablespace_name, block_size blksz, dfct,
  fs.nfrag                      ct_frag,
  fs.mxfrag           / 1048576 mb_free_frag,
  fs.free_bytes       / 1048576 mb_free,
  df.avail            / 1048576 mb_total,
  (df.avail-fs.mxfrag)/df.avail*100 pct_used,
  df.max_bytes        / 1048576 max_mb_free,
  (df.avail-fs.mxfrag)/df.max_bytes*100 max_pct_used
FROM dba_tablespaces ts,
  (SELECT tablespace_name, count(*) dfct,
   SUM(decode(maxbytes,0,user_bytes,greatest(maxbytes,user_bytes))) max_bytes,
   SUM(user_bytes) avail
   FROM dba_data_files
   GROUP BY tablespace_name
  ) df,
  (SELECT tablespace_name,  nvl(sum(bytes),0) free_bytes, count(bytes) nfrag, nvl(max(bytes),0) mxfrag
   FROM dba_free_space
   GROUP BY tablespace_name
  ) fs
WHERE fs.tablespace_name = ts.tablespace_name(+)
AND fs.tablespace_name   = df.tablespace_name
AND regexp_like(fs.tablespace_name,'&1','i')
ORDER BY pct_used desc

Don’t Know cron

Sat, 2016-03-19 19:54

Learn something new every day !!!

Did you know the Date and Day from cron is OR condition and not AND?

Wanted to schedule cron to run every 3rd Friday.

This ended up running on the 19th which is Saturday

$ crontab -l|head -1
### Schedule below will run Every Friday OR Date 15-21
41 18 15-21 * 5 /bin/date > /tmp/date.out

$ date
Sat Mar 19 18:40:05 PDT 2016

$ date
Sat Mar 19 18:41:17 PDT 2016

$ ll /tmp/date.out
-rw-r--r--. 1 oracle oinstall 29 Mar 19 18:41 /tmp/date.out

$ cat /tmp/date.out
Sat Mar 19 18:41:01 PDT 2016

OOPS!

++++++++++

Shell script will need to check for day and exit if not the correct day.

$ date
Sat Mar 19 18:43:13 PDT 2016

$ crontab -l|head -1
44 18 15-21 * * /home/oracle/t.sh > /tmp/date.out

++++++++++
$ cat t.sh
#!/bin/sh -ex
# Exit if not Friday
if [[ $(date +%u) -ne 5 ]] ; then
    exit
fi
date
++++++++++

$ date
Sat Mar 19 18:44:26 PDT 2016

$ ll /tmp/date.out
-rw-r--r--. 1 oracle oinstall 0 Mar 19 18:44 /tmp/date.out

$ ./t.sh
++ date +%u
+ [[ 6 -ne 5 ]]
+ exit

Option 2: check day from cron.

$ date
Sun Mar 20 04:44:39 PDT 2016

$ crontab -l|head -1
45 04 15-21 * * [ $(date +\%u) -eq 7 ] && /home/oracle/t2.sh > /tmp/date.out

++++++++++
$ cat t2.sh
date
++++++++++

$ date
Sun Mar 20 04:45:01 PDT 2016

$ ll /tmp/date.out
-rw-r--r--. 1 oracle oinstall 29 Mar 20 04:45 /tmp/date.out

$ cat /tmp/date.out
Sun Mar 20 04:45:01 PDT 2016

Tested on:
oracle@arrow:tiger:/home/oracle
$ uname -an
Linux arrow.localdomain 3.8.13-68.3.2.el6uek.x86_64 #2 SMP Tue Jun 9 17:07:32 PDT 2015 x86_64 x86_64 x86_64 GNU/Linux

oracle@arrow:tiger:/home/oracle
$ cat /etc/oracle-release
Oracle Linux Server release 6.6
oracle@arrow:tiger:/home/oracle
$

++++++++++

Updated: Mar 26, 2016

$ crontab -l|head -1
27 20 15-25 * * /usr/bin/test `date +\%a` = Fri && /home/oracle/t2.sh > /tmp/t2.sh.log 2>&1

Both && and || logic produce identical results for the correct day.

$ date
Fri Mar 25 21:21:26 PDT 2016

$ test `date +\%a` = Fri;echo $?
0

pwd if test = 0

$ test `date +\%a` = Fri && pwd; echo $?
/home/oracle
0

$ test `date +\%a` != Fri;echo $?
1

pwd if test != 0

$ test `date +\%a` != Fri || pwd; echo $?
/home/oracle
0

Noticed the difference in return code as mention in reference: 

http://docstore.mik.ua/orelly/unix3/upt/ch25_02.htm

Using && returns 1 while using || returns 0 for incorrect day.

It’s not Monday; hence, pwd did not return values.

$ test `date +\%a` = Mon && pwd; echo $?
1

$ test `date +\%a` != Mon || pwd; echo $?
0

 

The right side of && (pwd) will only be evaluated if the left side exit status = 0.

$ test `date +\%a` = Mon;echo $?
1

pwd if test = 0

$ test `date +\%a` = Mon && pwd; echo $?
1

The right side of || (pwd) will only be evaluated if the left side exit status is != 0.

$ test `date +\%a` != Mon;echo $?
0

pwd if test != 0

$ test `date +\%a` != Mon || pwd; echo $?
0

 


Migrate to 12c using Export/Import

Thu, 2016-03-17 20:07

If you had success doing this, please share.

This took some troubleshooting and more than one pass.

Still not comfortable to guarantee target is identical to source.

Here are all the references, I have gathered if you want an adventure.

How to Perform a Full Database Export Import During Upgrade, Migrate, Copy or Move of a Database (Doc ID 286775.1)

Information On Installed Database Components and Schemas (Doc ID 472937.1)
Schemas CTXSYS MDSYS ORDSYS Are Not Exported (Doc ID 228482.1)

How to Install/Deinstall Oracle Workspace Manager (Doc ID 263428.1)
How to Determine if Workspace Manager is Being Used? (Doc ID 727765.1)

Data Pump: GRANTs On SYS Owned Objects Are Not Transferred With Data Pump And Are Missing In The Target Database (Doc ID 1911151.1)
After Full Import All Object Privileges Granted By SYS Are Lost (Doc ID 97902.1)

DataPump Export Fails With Error ORA-39095 (Doc ID 1071931.1)

++++++++++
How many components are installed?

*** dba_registry ***

ACTUAL SOURCE:
COMP_ID    COMP_NAME			       VERSION	       STATUS	    NAMESPACE	 SCHEMA
---------- ----------------------------------- --------------- ------------ ------------ ---------------
CATALOG    Oracle Database Catalog Views       10.2.0.4.0      VALID	    SERVER	 SYS
CATPROC    Oracle Database Packages and Types  10.2.0.4.0      VALID	    SERVER	 SYS
EM	   Oracle Enterprise Manager	       10.2.0.4.0      VALID	    SERVER	 SYSMAN
OWM	   Oracle Workspace Manager	       10.2.0.4.3      VALID	    SERVER	 WMSYS

ONE BAD SOURCE:
COMP_ID    COMP_NAME                           VERSION         STATUS       NAMESPACE    SCHEMA
---------- ----------------------------------- --------------- ------------ ------------ ---------------
APS        OLAP Analytic Workspace             11.2.0.4.0      VALID        SERVER       SYS
CATALOG    Oracle Database Catalog Views       11.2.0.4.0      VALID        SERVER       SYS
CATJAVA    Oracle Database Java Packages       11.2.0.4.0      VALID        SERVER       SYS
CATPROC    Oracle Database Packages and Types  11.2.0.4.0      VALID        SERVER       SYS
CONTEXT    Oracle Text                         11.2.0.4.0      VALID        SERVER       CTXSYS
EXF        Oracle Expression Filter            11.2.0.4.0      VALID        SERVER       EXFSYS
JAVAVM     JServer JAVA Virtual Machine        11.2.0.4.0      VALID        SERVER       SYS
ORDIM      Oracle Multimedia                   11.2.0.4.0      VALID        SERVER       ORDSYS
OWB        OWB                                 11.2.0.4.0      VALID        SERVER       OWBSYS
OWM        Oracle Workspace Manager            11.2.0.4.0      VALID        SERVER       WMSYS
RUL        Oracle Rules Manager                11.2.0.4.0      VALID        SERVER       EXFSYS
SDO        Spatial                             11.2.0.4.0      VALID        SERVER       MDSYS
XDB        Oracle XML Database                 11.2.0.4.0      VALID        SERVER       XDB
XML        Oracle XDK                          11.2.0.4.0      VALID        SERVER       SYS
XOQ        Oracle OLAP API                     11.2.0.4.0      VALID        SERVER       SYS

15 rows selected.

++++++++++
Export error using FLASHBACK_TIME

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Took a some time but finally found a work around other than using FLASHBACK_SCN which is hokie.

expdp_full.par

USERID="/ as sysdba"
directory=DATA_PUMP_DIR
METRICS=Y
PARALLEL=4
FULL=Y
DUMPFILE=full%U.dmp
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(systimestamp,'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS')"
EXCLUDE=statistics
exclude=schema:"IN ('WMSYS','SYSMAN')"
LOGFILE=exp_full.log

++++++++++
Create 12c non-PDB database.

whenever sqlerror exit sql.sqlcode
whenever oserror exit 1
set echo on
shu abort;
create spfile from pfile;
startup force nomount;
CREATE DATABASE
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE 
GROUP 1 size 100M,GROUP 2 size 100M,GROUP 3 size 100M
EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 257M AUTOEXTEND ON NEXT 256M MAXSIZE 30721M
SYSAUX DATAFILE SIZE 257M AUTOEXTEND ON NEXT 256M MAXSIZE 30721M
DEFAULT TABLESPACE USERS DATAFILE SIZE 257M AUTOEXTEND ON NEXT 257M MAXSIZE 30721M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 30720M
UNDO TABLESPACE UNDOTBS1 DATAFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 30720M
;
set echo off
@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catproc.sql;
@?/rdbms/admin/catblock.sql;
@?/rdbms/admin/catoctk.sql;
@?/rdbms/admin/catio.sql
@?/rdbms/admin/dbmsiotc.sql
@?/rdbms/admin/dbmspool.sql
@?/rdbms/admin/userlock.sql
@?/rdbms/admin/utlrp.sql
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
connect system/oracle
@?/sqlplus/admin/pupbld.sql
exit

++++++++++
Check for errors.
grep '^ORA-' create_db_omf.tiger.out |cut -d ":" -f1|sort -u

ORA-00942
ORA-01432
ORA-01434
ORA-01921
ORA-02289
ORA-04043
ORA-29807

oerr ora 1432

01432, 00000, "public synonym to be dropped does not exist"
// *Cause:
// *Action:

grep '^ORA-' create_db_omf.tiger.out |cut -d ":" -f2|grep -v "not exist"|sort -u

 role name 'EXP_FULL_DATABASE' conflicts with another user or role
 role name 'IMP_FULL_DATABASE' conflicts with another user or role

++++++++++
Don’t use FILESIZE – I know, there are presentation showing usage of FILESIZE.

Example:
FILESIZE=8g
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

++++++++++
DATAFILE SIZE 2097152000 is larger than MAXSIZE 1048576000.

ORA-39083: Object type TABLESPACE:"USERS" failed to create with error:
ORA-02494: invalid or missing maximum file size in MAXSIZE clause
Failing sql is:
CREATE TABLESPACE "USERS" DATAFILE SIZE 2097152000 AUTOEXTEND ON NEXT 104857600 MAXSIZE 1048576000

Related error:

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39083: Object type TABLE: failed to create with error:
ORA-01950: no privileges on tablespace 'USERS'

++++++++++
SYS triggers need to be extracted from Source and created at Target.
Remember this one – OJVM?

SQL> CREATE OR REPLACE TRIGGER "SYS"."DBMS_JAVA_DEV_TRG" before create
2 on database
3 begin
4 if (ora_dict_obj_type='JAVA')
5 then
6 raise_application_error(-20031,'Java Development Disabled');
7 end if;
8 end;
9 /
Trigger created.

SQL> ALTER TRIGGER "SYS"."DBMS_JAVA_DEV_TRG" DISABLE;

Trigger altered.
SYS@SQL>

++++++++++
Tablespaces were pre-created since datafile locations changed.  Alternatively, use REMAP_DATAFILE.

set echo off head off verify off feedb off pages 0 long 10000 longchunk 10000 trimspool on lines 2000 timing off term off
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',TRUE);
define _filename=none
COL name NEW_VALUE name
select name from v$database;
define _filename='tbs_&name..sql'
spool &_filename
select dbms_metadata.get_ddl('TABLESPACE',TABLESPACE_NAME) from dba_tablespace
;
spool off
set term on
pro
pro *** Spool Output: &_filename
pro
pro
exit

++++++++++
Import done in 2 passes, impdp_full_user.par and impdp_full.par

impdp_full_user.par

directory=DATA_PUMP_DIR
USERID="/ as sysdba"
METRICS=Y
PARALLEL=4
FULL=Y
DUMPFILE=full%U.dmp
LOGFILE=imp_user.log
INCLUDE=user,role,default_role,tablespace_quota,role_grant,profile,password_verify_function,tablespace

Want to detect as many issues as possible before actual data import.
ORA-39083: Object type TABLESPACE:"USERS" failed to create with error

ORA-39083: Object type TABLE: failed to create with error:
ORA-01950: no privileges on tablespace 'USERS'

++++++++++
grep -v ORA-31684 imp_user.log | grep -A 1 Processing

Processing object type DATABASE_EXPORT/TABLESPACE
     Completed 26 TABLESPACE objects in 1 seconds
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
     Completed 1 PASSWORD_VERIFY_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
     Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYS_USER/USER
     Completed 1 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/USER
     Completed 7796 USER objects in 20 seconds
Processing object type DATABASE_EXPORT/ROLE
     Completed 321 ROLE objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
     Completed 21442 ROLE_GRANT objects in 129 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
     Completed 7796 DEFAULT_ROLE objects in 10 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
     Completed 7790 TABLESPACE_QUOTA objects in 24 seconds

++++++++++
impdp_full.par

directory=DATA_PUMP_DIR
USERID="/ as sysdba"
METRICS=Y
PARALLEL=4
FULL=Y
DUMPFILE=full%U.dmp
LOGFILE=imp_full.log
EXCLUDE=user,role,default_role,tablespace_quota,role_grant,profile,password_verify_function,tablespace
STREAMS_CONFIGURATION=NO

++++++++++
grep -v ORA-31684 imp_full.log | grep -A 1 Processing

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
     Completed 6 PROC_SYSTEM_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
     Completed 145 SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/RESOURCE_COST
     Completed 1 RESOURCE_COST objects in 0 seconds
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
     Completed 1 TRUSTED_DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
     Completed 197 SEQUENCE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 205 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
     Completed 7 DIRECTORY objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 12 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/CONTEXT
     Completed 7 CONTEXT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
     Completed 1139 SYNONYM objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
     Completed 1014 SYNONYM objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
     Completed 180 TYPE objects in 3 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
--
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
     Completed 3 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
     Completed 1302 TABLE objects in 18 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Startup took 356 seconds
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 68094 OBJECT_GRANT objects in 91 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
     Completed 4 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
     Completed 238 CONSTRAINT objects in 86 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
     Completed 696 COMMENT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
     Completed 1 AUDIT_OBJ objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/RLS_POLICY
     Completed 68 RLS_POLICY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
     Completed 148 PACKAGE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 195 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
     Completed 42 FUNCTION objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 52 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
     Completed 16 PROCEDURE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
     Completed 42 ALTER_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
     Completed 16 ALTER_PROCEDURE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
     Completed 72 VIEW objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 155 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
     Completed 62 COMMENT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/RLS_POLICY
     Completed 4 RLS_POLICY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
     Completed 148 PACKAGE_BODY objects in 4 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
     Completed 16 TYPE_BODY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
ORA-39083: Object type INDEX: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
     Completed 68 TRIGGER objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
     Completed 3 TRIGGER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
     Completed 1 MATERIALIZED_VIEW objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/JOB
     Completed 5 JOB objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
     Completed 3 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
--
Processing object type DATABASE_EXPORT/AUDIT
     Completed 5 AUDIT objects in 0 seconds

12c Clone DB_SWONLY Oracle Home

Sun, 2016-03-06 19:42

Just some notes on 12c Oracle Home Cloning.

I am too lazy to put step by step instructions in words, and if you have questions, please ask.

-defaultHomeName
<HOME NAME=”OraHome1” LOC=”/u01/app/oracle/product/12.2.0/dbhome_2″ TYPE=”O” IDX=”3″/>

oracle@arrow:hawlkas:/home/oracle
$ . oraenv <<< db12c
ORACLE_SID = [hawlkas] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:db12c:/home/oracle
$ echo $ORACLE_HOME
/u01/app/oracle/product/12.2.0/dbhome_1
oracle@arrow:db12c:/home/oracle
$ cd $ORACLE_HOME
oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1
$ pwd
/u01/app/oracle/product/12.2.0/dbhome_1
oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1
$ nohup tar -cvpf /tmp/db12cHome_${USER}_`hostname -s`_clone.tar . > /tmp/clone_${USER}_`hostname -s`_db12cHome.log 2>&1 &
[1] 13092

++++++++++

oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1
$
[1]+  Done                    nohup tar -cvpf /tmp/db12cHome_${USER}_`hostname -s`_clone.tar . > /tmp/clone_${USER}_`hostname -s`_db12cHome.log 2>&1
oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1
$ ll /tmp/*clone*
-rw-r--r--. 1 oracle oinstall    1381582 Mar  6 16:24 /tmp/clone_oracle_arrow_db12cHome.log
-rw-r--r--. 1 oracle oinstall 6581944320 Mar  6 16:24 /tmp/db12cHome_oracle_arrow_clone.tar
oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1
$ tail /tmp/clone_oracle_arrow_db12cHome.log
./jdk/bin/xjc
./jdk/bin/javadoc
./jdk/bin/jarsigner
./jdk/bin/pack200
./jdk/bin/rmid
./jdk/bin/jrunscript
./jdk/bin/extcheck
./jdk/bin/keytool
./jdk/LICENSE
./jdk/.manifest
oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1
$ du -sh /tmp/db12cHome_oracle_arrow_clone.tar
6.2G    /tmp/db12cHome_oracle_arrow_clone.tar
oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1
$

++++++++++

oracle@arrow:test12c:/home/oracle
$ . oraenv <<< test12c
ORACLE_SID = [test12c] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:test12c:/home/oracle
$ cd $ORACLE_HOME
oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2
$ pwd
/u01/app/oracle/product/12.2.0/dbhome_2
oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2
$ ls
oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2
$ nohup tar -xvf /tmp/db12cHome_oracle_arrow_clone.tar > /tmp/untar_${USER}_`hostname -s`_db12cHome.log 2>&1 &
[1] 13182
oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2
$
[1]+  Done                    nohup tar -xvf /tmp/db12cHome_oracle_arrow_clone.tar > /tmp/untar_${USER}_`hostname -s`_db12cHome.log 2>&1
oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2
$ ll /tmp/untar_oracle_arrow_db12cHome.log
-rw-r--r--. 1 oracle oinstall 1381582 Mar  6 16:44 /tmp/untar_oracle_arrow_db12cHome.log
oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2
$ tail /tmp/untar_oracle_arrow_db12cHome.log
./jdk/bin/xjc
./jdk/bin/javadoc
./jdk/bin/jarsigner
./jdk/bin/pack200
./jdk/bin/rmid
./jdk/bin/jrunscript
./jdk/bin/extcheck
./jdk/bin/keytool
./jdk/LICENSE
./jdk/.manifest
oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2
$ ls
addnode     ccr          crs  dbs        diagnostics  hs             javavm  ldap  mgw      odbc    oracore      owm      precomp  rdbms     scheduler     sqlpatch  sysman  wwg
apex        cdata        css  dc_ocm     dmu          install        jdbc    lib   network  olap    oraInst.loc  perl     QOpatch  relnotes  slax          sqlplus   ucp     xdk
assistants  cfgtoollogs  ctx  deinstall  dv           instantclient  jdk     log   nls      OPatch  ord          plsql    R        rest      sqldeveloper  srvm      usm
bin         clone        cv   demo       has          inventory      jlib    md   

++++++++++

$ cd $ORACLE_HOME/clone/bin
oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin
$ perl clone.pl -silent -noconfig -defaultHomeName ORACLE_HOME="/u01/app/oracle/product/12.2.0/dbhome_2" ORACLE_BASE="/u01/app/oracle" OSDBA_GROUP=dba OSOPER_GROUP=dba
./runInstaller -clone -waitForCompletion  -noconfig  -defaultHomeName  "ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_2" 
"ORACLE_BASE=/u01/app/oracle" "oracle_install_OSDBA=dba" "oracle_install_OSOPER=dba" 
-silent -paramFile /u01/app/oracle/product/12.2.0/dbhome_2/clone/clone_oraparam.ini

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 16049 MB    Passed
Checking swap space: must be greater than 500 MB.   Actual 3994 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-03-06_04-58-03PM. 

Please wait ...You can find the log of this install session at:
 /u01/app/oraInventory/logs/cloneActions2016-03-06_04-58-03PM.log
..................................................   5% Done.
..................................................   10% Done.
..................................................   15% Done.
..................................................   20% Done.
..................................................   25% Done.
..................................................   30% Done.
..................................................   35% Done.
..................................................   40% Done.
..................................................   45% Done.
..................................................   50% Done.
..................................................   55% Done.
..................................................   60% Done.
..................................................   65% Done.
..................................................   70% Done.
..................................................   75% Done.
..................................................   80% Done.
..................................................   85% Done.
..........
Copy files in progress.

Copy files successful.

Link binaries in progress.

Link binaries successful.

Setup files in progress.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup successful.
The cloning of OraHome1 was successful.
Please check '/u01/app/oraInventory/logs/cloneActions2016-03-06_04-58-03PM.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
..................................................   95% Done.

As a root user, execute the following script(s):
        1. /u01/app/oracle/product/12.2.0/dbhome_2/root.sh

..................................................   100% Done.
oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin
$ su -
Password:
[root@arrow ~]# /u01/app/oracle/product/12.2.0/dbhome_2/root.sh
Check /u01/app/oracle/product/12.2.0/dbhome_2/install/root_arrow.localdomain_2016-03-06_17-03-49.log for the output of root script
[root@arrow ~]# exit
logout

oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin
$ /u01/app/oracle/product/12.2.0/dbhome_2/OPatch/opatch lspatches
22139226;Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)
21948354;Database Patch Set Update : 12.1.0.2.160119 (21948354)

OPatch succeeded.
oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin
$ /u01/app/oracle/product/12.2.0/dbhome_2/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.1.0.1.10
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2.0/dbhome_2
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0/dbhome_2/oraInst.loc
OPatch version    : 12.1.0.1.10
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.2.0/dbhome_2/cfgtoollogs/opatch/opatch2016-03-06_17-29-28PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.2.0/dbhome_2/cfgtoollogs/opatch/lsinv/lsinventory2016-03-06_17-29-28PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: localhost
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c                                                  12.1.0.2.0
There are 1 products installed in this Oracle Home.


Interim patches (2) :

Patch  22139226     : applied on Sun Mar 06 15:46:50 PST 2016
Unique Patch ID:  19729684
Patch description:  "Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)"
   Created on 4 Jan 2016, 01:41:46 hrs PST8PDT
   Bugs fixed:
     19699946, 19176885, 19623450, 22139226, 19909862, 21811517, 19223010
     21068507, 19895326, 19877336, 22118835, 22118851, 21566993, 19153980
     20408829, 21047766, 19231857, 19895362, 19855285, 20415564, 21555660
     19245191, 21047803, 20408866, 21566944

Patch  21948354     : applied on Sun Mar 06 15:35:02 PST 2016
Unique Patch ID:  19553095
Patch description:  "Database Patch Set Update : 12.1.0.2.160119 (21948354)"
   Created on 20 Dec 2015, 23:39:33 hrs PST8PDT
Sub-patch  21359755; "Database Patch Set Update : 12.1.0.2.5 (21359755)"
Sub-patch  20831110; "Database Patch Set Update : 12.1.0.2.4 (20831110)"
Sub-patch  20299023; "Database Patch Set Update : 12.1.0.2.3 (20299023)"
Sub-patch  19769480; "Database Patch Set Update : 12.1.0.2.2 (19769480)"
   Bugs fixed:
     19189525, 19075256, 19141838, 19865345, 19791273, 19280225, 18845653
     20951038, 19243521, 19248799, 21756699, 18988834, 19238590, 21281532
     20245930, 18921743, 18799063, 19134173, 19571367, 20476175, 20925795
     19018206, 20509482, 20387265, 20588502, 19149990, 18849537, 18886413
     17551063, 19183343, 19703301, 19001390, 18202441, 19189317, 19644859
     19358317, 19390567, 19279273, 19706965, 19068970, 19619732, 20348653
     18607546, 18940497, 19670108, 19649152, 18948177, 19315691, 19676905
     18964978, 19035573, 20165574, 19176326, 20413820, 20558005, 19176223
     19532017, 20134339, 19074147, 18411216, 20361671, 20425790, 18966843
     20294666, 19307662, 19371175, 19195895, 19154375, 19468991, 19174521
     19520602, 19382851, 21875360, 19326908, 19658708, 20093776, 20618595
     21787056, 17835294, 19791377, 19068610, 20048359, 20746251, 19143550
     19185876, 19627012, 20281121, 19577410, 22092979, 19001359, 19518079
     18610915, 19490948, 18674024, 18306996, 19309466, 19081128, 19915271
     20122715, 21188532, 20284155, 18791688, 20890311, 21442094, 18973548
     19303936, 19597439, 20235511, 18964939, 19430401, 19044962, 19409212
     19879746, 20657441, 19684504, 19024808, 18799993, 20877664, 19028800
     19065556, 19723336, 19077215, 19604659, 21421886, 19524384, 19048007
     18288842, 19689979, 20446883, 18952989, 16870214, 19928926, 21526048
     19180770, 19197175, 19902195, 20318889, 19730508, 19012119, 19067244
     20074391, 19512341, 19841800, 14643995, 20331945, 19587324, 19547370
     19065677, 19637186, 21225209, 20397490, 18967382, 19174430, 18674047
     19054077, 19536415, 19708632, 19289642, 20869721, 19335438, 17365043
     18856999, 19869255, 20471920, 19468347, 21620471, 16359751, 18990693
     17890099, 19439759, 19769480, 19272708, 19978542, 20101006, 21300341
     20402832, 19329654, 19873610, 21668627, 21517440, 19304354, 19052488
     20794034, 19291380, 18681056, 19896336, 19076343, 19561643, 18618122
     20440930, 18456643, 19699191, 18909599, 19487147, 18250893, 19016730
     18743542, 20347562, 16619249, 18354830, 19687159, 19174942, 20424899
     19989009, 20688221, 20441797, 19157754, 19032777, 19058490, 19399918
     18885870, 19434529, 19018447, 18417036, 20919320, 19022470, 19284031
     20474192, 20173897, 22062026, 19385656, 19501299, 17274537, 20899461
     19440586, 16887946, 19606174, 18436647, 17655240, 19023822, 19178851
     19124589, 19805359, 19597583, 19155797, 19393542, 19050649



--------------------------------------------------------------------------------

OPatch succeeded.
oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin
$
++++++++++
oracle@arrow:tiger:/u01/app/oraInventory/logs
$ ls -alrt|tail
-rw-r-----. 1 oracle oinstall     239 Mar  6 16:54 installActions2016-03-06_04-54-57PM.log
-rw-r-----. 1 oracle oinstall       0 Mar  6 16:56 oraInstall2016-03-06_04-55-18PM.err
-rw-r-----. 1 oracle oinstall     200 Mar  6 16:56 oraInstall2016-03-06_04-55-18PM.out
-rw-r-----. 1 oracle oinstall   11221 Mar  6 16:56 cloneActions2016-03-06_04-55-18PM.log
-rw-r-----. 1 oracle oinstall       0 Mar  6 16:58 oraInstall2016-03-06_04-58-03PM.err
-rw-r-----. 1 oracle oinstall      79 Mar  6 17:01 silentInstall2016-03-06_04-58-03PM.log
-rw-r-----. 1 oracle oinstall    1868 Mar  6 17:01 oraInstall2016-03-06_04-58-03PM.out
drwxrwx---. 6 oracle oinstall    4096 Mar  6 17:01 ..
-rw-r-----. 1 oracle oinstall 7338332 Mar  6 17:01 cloneActions2016-03-06_04-58-03PM.log
drwxrwx---. 3 oracle oinstall    4096 Mar  8 07:51 .

oracle@arrow:tiger:/u01/app/oraInventory/logs
$ grep -e '[[:upper:]]: ' cloneActions2016-03-06_04-58-03PM.log|cut -d ":" -f1|sort -u
INFO
WARNING

oracle@arrow:tiger:/u01/app/oraInventory/logs
$ grep -e '[[:upper:]]: ' cloneActions2016-03-06_04-58-03PM.log |grep "^WARNING: "
WARNING: You have not provided an email address. If you do not want to receive security updates, set 'DECLINE_SECURITY_UPDATES' to 'true'.
oracle@arrow:tiger:/u01/app/oraInventory/logs
$
++++++++++
oracle@arrow:test12c:/u01/app/oraInventory/ContentsXML
$ grep -i "home name" inventory.xml
<HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="1"/>
<HOME NAME="OraDB12Home1" LOC="/u01/app/oracle/product/12.2.0/dbhome_1" TYPE="O" IDX="2"/>
<HOME NAME="OraHome1" LOC="/u01/app/oracle/product/12.2.0/dbhome_2" TYPE="O" IDX="3"/>

RANT PSU Naming

Sun, 2016-03-06 17:12

Do you know what PSU there are?

oracle@arrow:db12c:/media/sf_working/12c/psu
$ ll *.zip
-rwxrwx---. 1 root vboxsf 147028181 Mar  4 20:00 p21948354_121020_Linux-x86-64.zip
-rwxrwx---. 1 root vboxsf 101720296 Mar  4 19:58 p22139226_121020_Linux-x86-64.zip

Let me give you a hint – (12.1.0.1.160119)
Now do you know what they are?

Let me give you another hint.
21948354 (12.1.0.1.160119) – 12.1.0.1 JAN2016 PSU (Database assumed here)
22139226 (12.1.0.2.160119) – OJVM PSU Patches

160119 – what date format is this?

opatch lsinventory

Patch  22139226     : applied on Wed Feb 03 11:07:55 CST 2016
Unique Patch ID:  19729684
Patch description:  "Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)"
   Created on 4 Jan 2016, 01:41:46 hrs PST8PDT
   Bugs fixed:
     19699946, 19176885, 19623450, 22139226, 19909862, 21811517, 19223010
     21068507, 19895326, 19877336, 22118835, 22118851, 21566993, 19153980
     20408829, 21047766, 19231857, 19895362, 19855285, 20415564, 21555660
     19245191, 21047803, 20408866, 21566944

Patch  21948354     : applied on Wed Feb 03 11:03:24 CST 2016
Unique Patch ID:  19553095

Looks like the whole numbering system is screwed up now.

Patch description:  "Database Patch Set Update : 12.1.0.2.160119 (21948354)"
   Created on 20 Dec 2015, 23:39:33 hrs PST8PDT
Sub-patch  21359755; "Database Patch Set Update : 12.1.0.2.5 (21359755)"
Sub-patch  20831110; "Database Patch Set Update : 12.1.0.2.4 (20831110)"
Sub-patch  20299023; "Database Patch Set Update : 12.1.0.2.3 (20299023)"
Sub-patch  19769480; "Database Patch Set Update : 12.1.0.2.2 (19769480)"

12c Silent Install software-only BUG

Sun, 2016-03-06 16:35

12.1: Oracle Database Software Only Silent Installation Fails to Create configToolAllCommands (Doc ID 2037923.1)

The BUG is in RED BOLD below.

oracle@arrow:hawklas:/media/sf_working/12c/database/response
$ cat /etc/oraInst.loc
inventory_loc=/u01/app/oraInventory
inst_group=oinstall

++++++++++

oracle@arrow:hawklas:/media/sf_working/12c/database/response
$ cat db_swonly.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.1.0
oracle.install.option=INSTALL_DB_SWONLY
# Specify the Unix group to be set for the inventory directory.
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/etc/oraInst.loc
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
oracle.install.db.BACKUPDBA_GROUP=dba
oracle.install.db.DGDBA_GROUP=dba
oracle.install.db.KMDBA_GROUP=dba
DECLINE_SECURITY_UPDATES=true
oracle.installer.autoupdates.option=SKIP_UPDATES

++++++++++

oracle@arrow:hawklas:/media/sf_working/12c/database
$ ./runInstaller -showProgress -waitforcompletion -silent -noconfig -force -responseFile /media/sf_working/12c/database/response/db_swonly.rsp

Starting Oracle Universal Installer...
Checking Temp space: must be greater than 500 MB.   Actual 29558 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4080 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-03-06_01-53-21PM. 
Please wait ...KiTTY X11 proxy: MIT-MAGIC-COOKIE-1 data did not matchYou can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2016-03-06_01-53-21PM.log

Prepare in progress.
..................................................   8% Done.

Prepare successful.

Copy files in progress.
..................................................   13% Done.
..................................................   18% Done.
..................................................   23% Done.
..................................................   28% Done.
..................................................   33% Done.
..................................................   39% Done.
..................................................   45% Done.
..................................................   50% Done.
..................................................   55% Done.
..................................................   60% Done.
..................................................   65% Done.
..................................................   70% Done.
..................................................   75% Done.

Copy files successful.

Link binaries in progress.

Link binaries successful.

Setup files in progress.
..............................
Setup files successful.
..........
Setup Inventory in progress.

Setup Inventory successful.
..................................................   80% Done.
..........
Finish Setup successful.

The installation of Oracle Database 12c was successful.

Please check '/u01/app/oraInventory/logs/silentInstall2016-03-06_01-53-21PM.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
..................................................   95% Done.

As a root user, execute the following script(s):
        1. /u01/app/oracle/product/12.2.0/dbhome_1/root.sh

..................................................   100% Done.

Successfully Setup Software.
As install user, execute the following script to complete the configuration.
        1. /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/configToolAllCommands RESPONSE_FILE=

        Note:
        1. This script must be run on the same host from where installer was run.
        2. This script needs a small password properties file for configuration assistants that require passwords (refer to install guide documentation).


oracle@arrow:hawklas:/media/sf_working/12c/database
$ su -
Password:
[root@arrow ~]# /u01/app/oracle/product/12.2.0/dbhome_1/root.sh
Check /u01/app/oracle/product/12.2.0/dbhome_1/install/root_arrow.localdomain_2016-03-06_14-09-10.log for the output of root script
[root@arrow ~]# ll /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/configToolAllCommands
ls: cannot access /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/configToolAllCommands: No such file or directory
[root@arrow ~]#

A Better Diff

Thu, 2016-03-03 22:25

I have been working on simplifying, perfecting, and comparing RMAN backup scripts.

The typical diff file1 file2 was not useful as I wanted a complete picture.

Look at what I found!

$ diff -iwyB --suppress-common-lines -W 150 rman_bkupinc.sh rman_bkuparc.sh;echo
# RMAN database incremental backup                                        |     # RMAN archivelog backup
# rman_bkupinc.sh                                                         |     # rman_bkuparc.sh
# Shell script calls bkupinc.rman at at SCRIPT_DIR location               |     # Shell script calls bkuparc.rman at at SCRIPT_DIR location
SID=${1:?"---> USAGE: $DN/$BN -ORACLE_SID -LEVEL"}                        |     SID=${1:?"---> USAGE: $DN/$BN -ORACLE_SID"}
LVL=${2:?"---> USAGE: $DN/$BN -ORACLE_SID -LEVEL"}                        <
DAY=`date '+%bW%U'`                                                       |     DAY=`date '+%aH%H'`
RMAN_LOG=$LOG_DIR/`echo $BN|cut -d'.' -f1`.$ORACLE_SID.L$2.$DAY.log       |     RMAN_LOG=$LOG_DIR/`echo $BN|cut -d'.' -f1`.$ORACLE_SID.$DAY.log
rman @${RMAN_SCRIPT} ${LVL} msglog $RMAN_LOG                              |     rman @${RMAN_SCRIPT} msglog $RMAN_LOG

$ diff -iwy -W 150 rman_bkupinc.sh rman_bkuparc.sh;echo
#!/bin/sh -ex                                                                   #!/bin/sh -ex
# Michael Dinh: Mar 03, 2016                                                    # Michael Dinh: Mar 03, 2016
# RMAN database incremental backup                                        |     # RMAN archivelog backup
# rman_bkupinc.sh                                                         |     # rman_bkuparc.sh
# Shell script calls bkupinc.rman at at SCRIPT_DIR location               |     # Shell script calls bkuparc.rman at at SCRIPT_DIR location

DN=`dirname $0`                                                                 DN=`dirname $0`
BN=`basename $0`                                                                BN=`basename $0`
SID=${1:?"---> USAGE: $DN/$BN -ORACLE_SID -LEVEL"}                        |     SID=${1:?"---> USAGE: $DN/$BN -ORACLE_SID"}
LVL=${2:?"---> USAGE: $DN/$BN -ORACLE_SID -LEVEL"}                        <

set -a                                                                          set -a
### Edit for proper location                                                    ### Edit for proper location
SCRIPT_DIR=/media/sf_working/rman                                               SCRIPT_DIR=/media/sf_working/rman
LOG_DIR=/tmp                                                                    LOG_DIR=/tmp
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin               PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
LD_LIBRARY_PATH=/lib:/usr/lib                                                   LD_LIBRARY_PATH=/lib:/usr/lib

### Edit resync_catalog.rman                                                    ### Edit resync_catalog.rman
### Uncomment if catalog is being used                                          ### Uncomment if catalog is being used
# RESYNC_CATALOG=$SCRIPT_DIR/resync_catalog.rman                                # RESYNC_CATALOG=$SCRIPT_DIR/resync_catalog.rman

ORACLE_SID=$1                                                                   ORACLE_SID=$1
ORAENV_ASK=NO                                                                   ORAENV_ASK=NO
. oraenv                                                                        . oraenv
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"                                        NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
DAY=`date '+%bW%U'`                                                       |     DAY=`date '+%aH%H'`
RMAN_SCRIPT=$SCRIPT_DIR/`echo $BN|cut -d'.' -f1|cut -c6-`.rman                  RMAN_SCRIPT=$SCRIPT_DIR/`echo $BN|cut -d'.' -f1|cut -c6-`.rman
RMAN_LOG=$LOG_DIR/`echo $BN|cut -d'.' -f1`.$ORACLE_SID.L$2.$DAY.log       |     RMAN_LOG=$LOG_DIR/`echo $BN|cut -d'.' -f1`.$ORACLE_SID.$DAY.log
TMPLOG=/tmp/`echo $BN|cut -d'.' -f1`_$ORACLE_SID.log                            TMPLOG=/tmp/`echo $BN|cut -d'.' -f1`_$ORACLE_SID.log
ERRLOG=/tmp/`echo $BN|cut -d'.' -f1`_$ORACLE_SID.err                            ERRLOG=/tmp/`echo $BN|cut -d'.' -f1`_$ORACLE_SID.err
set +a                                                                          set +a

# Lock file                                                                     # Lock file
exec 200>/tmp/$BN.lck                                                           exec 200>/tmp/$BN.lck
flock -n 200 || exit 1                                                          flock -n 200 || exit 1

# RMAN archivelog backup                                                        # RMAN archivelog backup
rman @${RMAN_SCRIPT} ${LVL} msglog $RMAN_LOG                              |     rman @${RMAN_SCRIPT} msglog $RMAN_LOG

### Uncomment if catalog is being used                                          ### Uncomment if catalog is being used
# rman @${RESYNC_CATALOG} msglog $RMAN_LOG append                               # rman @${RESYNC_CATALOG} msglog $RMAN_LOG append

cp -v $RMAN_LOG $TMPLOG                                                         cp -v $RMAN_LOG $TMPLOG
egrep -i '^rman-|^ora-|error|fail' $RMAN_LOG > $ERRLOG                          egrep -i '^rman-|^ora-|error|fail' $RMAN_LOG > $ERRLOG
exit                                                                            exit


$ ll /tmp/rman*.*
-rw-r--r--. 1 oracle oinstall     0 Mar  3 17:19 /tmp/rman_bkuparc_hawklas.err
-rw-r--r--. 1 oracle oinstall  7910 Mar  3 17:19 /tmp/rman_bkuparc_hawklas.log
-rw-r--r--. 1 oracle oinstall  7910 Mar  3 17:19 /tmp/rman_bkuparc.hawklas.ThuH17.log
-rw-r--r--. 1 oracle oinstall     0 Mar  3 17:19 /tmp/rman_bkuparc.sh.lck
-rw-r--r--. 1 oracle oinstall     0 Mar  3 19:20 /tmp/rman_bkupinc_hawklas.err
-rw-r--r--. 1 oracle oinstall 15315 Mar  3 19:20 /tmp/rman_bkupinc.hawklas.L1.MarW09.log
-rw-r--r--. 1 oracle oinstall 15315 Mar  3 19:20 /tmp/rman_bkupinc_hawklas.log
-rw-r--r--. 1 oracle oinstall     0 Mar  3 19:20 /tmp/rman_bkupinc.sh.lck

Revised RMAN Backup Size

Sat, 2016-02-27 13:25

Long, long ago, I posted https://mdinh.wordpress.com/2013/10/31/whats-rman-backup-size/ and recently discovered  the SQL was incorrect.

Current SQL does not include backup_copy as it got a little complicated to create an all inclusive test case – being sick and lazy.

oracle@arrow:hawklas:/media/sf_working/rman
$ sysdba @bkupinfo.sql
SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 27 10:51:17 2016

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


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


COMPLETION_TIME     BS_KEY TYPE        BP_KEY  BP_MB PIECES SET_COUNT PIECE# COM DEVICE_TYPE
------------------- ------ ----------- ------ ------ ------ --------- ------ --- -----------------
HANDLE
----------------------------------------------------------------------------------------------------
2016-02-24 18:43:02   1723 FULL          2058   25.6      1      1800      1 YES DISK
/oradata/backup/HAWK_3130551611_20160224_o8quog1h_1_1_s1800_p1

2016-02-24 18:43:12   1724 FULL          2059  40.88      1      1802      1 YES DISK
/oradata/backup/HAWK_3130551611_20160224_oaquog1p_1_1_s1802_p1

2016-02-24 18:43:22   1725 FULL          2060 109.32      1      1801      1 YES DISK
/oradata/backup/HAWK_3130551611_20160224_o9quog1i_1_1_s1801_p1

2016-02-24 18:43:25   1726 FULL          2061   8.48      1      1804      1 YES DISK
/oradata/backup/HAWK_3130551611_20160224_ocquog2b_1_1_s1804_p1

2016-02-24 18:43:33   1727 FULL          2062  59.28      1      1803      1 YES DISK
/oradata/backup/HAWK_3130551611_20160224_obquog2a_1_1_s1803_p1

2016-02-24 18:43:39   1728 ARCHIVELOG    2063    .02      1      1805      1 YES DISK
/oradata/backup/HAWK_3130551611_20160224_odquog2r_1_1_s1805_p1

2016-02-24 18:43:44   1729 CONTROLFILE   2064  11.23      1      1806      1 NO  DISK
/oradata/backup/HAWK_c-3130551611-20160224-0e

2016-02-27 09:57:49   1730 ARCHIVELOG    2065   3.39      1      1807      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ofquvecs_1_1_s1807_p1

2016-02-27 09:57:55   1731 ARCHIVELOG    2066  29.52      1      1808      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ogquvecs_1_1_s1808_p1

2016-02-27 09:57:55   1732 ARCHIVELOG    2067  13.55      1      1809      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ohquvecv_1_1_s1809_p1

2016-02-27 09:59:03   1733 LEVEL0        2068  25.78      1      1810      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_oiquved7_1_1_s1810_p1

2016-02-27 09:59:35   1734 LEVEL0        2069  30.08      4      1811      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ojquved7_1_1_s1811_p1

2016-02-27 09:59:35   1734 LEVEL0        2070  30.04      4      1811      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ojquved7_2_1_s1811_p2

2016-02-27 09:59:35   1734 LEVEL0        2071  30.09      4      1811      3 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ojquved7_3_1_s1811_p3

2016-02-27 09:59:35   1734 LEVEL0        2072  21.52      4      1811      4 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ojquved7_4_1_s1811_p4

2016-02-27 09:59:46   1735 LEVEL0        2073   30.3      2      1812      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_okquvefb_1_1_s1812_p1

2016-02-27 09:59:46   1735 LEVEL0        2074  11.87      2      1812      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_okquvefb_2_1_s1812_p2

2016-02-27 09:59:57   1736 LEVEL0        2075   8.51      1      1814      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_omquvego_1_1_s1814_p1

2016-02-27 10:00:02   1737 LEVEL0        2076  30.44      2      1813      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_olquveg9_1_1_s1813_p1

2016-02-27 10:00:02   1737 LEVEL0        2077  29.42      2      1813      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_olquveg9_2_1_s1813_p2

2016-02-27 10:00:09   1738 ARCHIVELOG    2078    .07      1      1815      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_onquveh9_1_1_s1815_p1

2016-02-27 10:00:14   1739 CONTROLFILE   2079  11.23      1      1816      1 NO  DISK
/oradata/backup/HAWK_c-3130551611-20160227-00

2016-02-27 10:02:49   1740 ARCHIVELOG    2080  11.42      1      1817      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_opquvem5_1_1_s1817_p1.bus

2016-02-27 10:02:53   1741 ARCHIVELOG    2081   1.77      1      1819      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_orquvemc_1_1_s1819_p1.bus

2016-02-27 10:02:57   1742 ARCHIVELOG    2082  14.07      3      1818      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_oqquvem5_1_1_s1818_p1.bus

2016-02-27 10:02:57   1742 ARCHIVELOG    2083  14.07      3      1818      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_oqquvem5_2_1_s1818_p2.bus

2016-02-27 10:02:57   1742 ARCHIVELOG    2084   5.38      3      1818      3 YES DISK
/oradata/backup/HAWK_3130551611_20160227_oqquvem5_3_1_s1818_p3.bus

2016-02-27 10:04:15   1743 LEVEL0        2085  14.15      2      1820      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_osquvemj_1_1_s1820_p1.bus

2016-02-27 10:04:15   1743 LEVEL0        2086  12.41      2      1820      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_osquvemj_2_1_s1820_p2.bus

2016-02-27 10:05:04   1744 LEVEL0        2087  14.17      4      1822      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ouquvep1_1_1_s1822_p1.bus

2016-02-27 10:05:04   1744 LEVEL0        2088  14.15      4      1822      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ouquvep1_2_1_s1822_p2.bus

2016-02-27 10:05:04   1744 LEVEL0        2089  14.02      4      1822      3 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ouquvep1_3_1_s1822_p3.bus

2016-02-27 10:05:04   1744 LEVEL0        2090    1.7      4      1822      4 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ouquvep1_4_1_s1822_p4.bus

2016-02-27 10:05:07   1745 LEVEL0        2091  14.01      9      1821      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_1_1_s1821_p1.bus

2016-02-27 10:05:07   1745 LEVEL0        2092  14.05      9      1821      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_2_1_s1821_p2.bus

2016-02-27 10:05:07   1745 LEVEL0        2093  14.01      9      1821      3 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_3_1_s1821_p3.bus

2016-02-27 10:05:07   1745 LEVEL0        2094  14.13      9      1821      4 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_4_1_s1821_p4.bus

2016-02-27 10:05:07   1745 LEVEL0        2095  14.11      9      1821      5 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_5_1_s1821_p5.bus

2016-02-27 10:05:07   1745 LEVEL0        2096  14.11      9      1821      6 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_6_1_s1821_p6.bus

2016-02-27 10:05:07   1745 LEVEL0        2097  14.05      9      1821      7 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_7_1_s1821_p7.bus

2016-02-27 10:05:07   1745 LEVEL0        2098  14.02      9      1821      8 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus

2016-02-27 10:05:07   1745 LEVEL0        2099   3.46      9      1821      9 YES DISK
/oradata/backup/HAWK_3130551611_20160227_otquvemj_9_1_s1821_p9.bus

2016-02-27 10:05:20   1746 LEVEL0        2100   8.51      1      1824      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_p0quveqo_1_1_s1824_p1.bus

2016-02-27 10:05:57   1747 LEVEL0        2101  14.17      5      1823      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ovquveqn_1_1_s1823_p1.bus

2016-02-27 10:05:57   1747 LEVEL0        2102   14.2      5      1823      2 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ovquveqn_2_1_s1823_p2.bus

2016-02-27 10:05:57   1747 LEVEL0        2103  14.09      5      1823      3 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ovquveqn_3_1_s1823_p3.bus

2016-02-27 10:05:57   1747 LEVEL0        2104  14.23      5      1823      4 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ovquveqn_4_1_s1823_p4.bus

2016-02-27 10:05:57   1747 LEVEL0        2105    5.6      5      1823      5 YES DISK
/oradata/backup/HAWK_3130551611_20160227_ovquveqn_5_1_s1823_p5.bus

2016-02-27 10:06:07   1748 ARCHIVELOG    2106   5.01      1      1825      1 YES DISK
/oradata/backup/HAWK_3130551611_20160227_p1quvese_1_1_s1825_p1.bus

2016-02-27 10:06:14   1749 CONTROLFILE   2107  11.23      1      1826      1 NO  DISK
/oradata/backup/HAWK_c-3130551611-20160227-01

                                              ------


sum                                           880.92



50 rows selected.

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Let’t take a look at backupset 1745 and backuppiece 2098 from RMAN.

Note BS Key is not the same as SET_COUNT (which is used to label backuppiece)

BS Key = 1745 while SET_COUNT = s1821

oracle@arrow:hawklas:/media/sf_working/rman
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Feb 27 10:51:22 2016

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

connected to target database: HAWK (DBID=3130551611)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
1723    B  F  A DISK        2016-FEB-24 18:43:02 1       1       YES        FULL
1724    B  F  A DISK        2016-FEB-24 18:43:12 1       1       YES        FULL
1725    B  F  A DISK        2016-FEB-24 18:43:22 1       1       YES        FULL
1726    B  F  A DISK        2016-FEB-24 18:43:25 1       1       YES        FULL
1727    B  F  A DISK        2016-FEB-24 18:43:33 1       1       YES        FULL
1728    B  A  A DISK        2016-FEB-24 18:43:39 1       1       YES        ARCHIVELOG
1729    B  F  A DISK        2016-FEB-24 18:43:44 1       1       NO         TAG20160224T184341
1730    B  A  A DISK        2016-FEB-27 09:57:49 1       1       YES        ARCHIVELOG
1731    B  A  A DISK        2016-FEB-27 09:57:55 1       1       YES        ARCHIVELOG
1732    B  A  A DISK        2016-FEB-27 09:57:55 1       1       YES        ARCHIVELOG
1733    B  0  A DISK        2016-FEB-27 09:59:03 1       1       YES        LEVEL0
1734    B  0  A DISK        2016-FEB-27 09:59:35 4       1       YES        LEVEL0
1735    B  0  A DISK        2016-FEB-27 09:59:46 2       1       YES        LEVEL0
1736    B  0  A DISK        2016-FEB-27 09:59:57 1       1       YES        LEVEL0
1737    B  0  A DISK        2016-FEB-27 10:00:02 2       1       YES        LEVEL0
1738    B  A  A DISK        2016-FEB-27 10:00:09 1       1       YES        ARCHIVELOG
1739    B  F  A DISK        2016-FEB-27 10:00:14 1       1       NO         TAG20160227T100010
1740    B  A  A DISK        2016-FEB-27 10:02:49 1       1       YES        ARCHIVELOG
1741    B  A  A DISK        2016-FEB-27 10:02:53 1       1       YES        ARCHIVELOG
1742    B  A  A DISK        2016-FEB-27 10:02:57 3       1       YES        ARCHIVELOG
1743    B  0  A DISK        2016-FEB-27 10:04:15 2       1       YES        LEVEL0
1744    B  0  A DISK        2016-FEB-27 10:05:04 4       1       YES        LEVEL0
1745    B  0  A DISK        2016-FEB-27 10:05:07 9       1       YES        LEVEL0
1746    B  0  A DISK        2016-FEB-27 10:05:20 1       1       YES        LEVEL0
1747    B  0  A DISK        2016-FEB-27 10:05:57 5       1       YES        LEVEL0
1748    B  A  A DISK        2016-FEB-27 10:06:07 1       1       YES        ARCHIVELOG
1749    B  F  A DISK        2016-FEB-27 10:06:14 1       1       NO         TAG20160227T100609

RMAN> list backupset 1745;

using target database control file instead of recovery catalog

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
1745    Incr 0  115.94M    DISK        00:02:08     2016-FEB-27 10:05:07
  List of Datafiles in backup set 1745
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  2    0  Incr 7405326    2016-FEB-27 10:02:59 /oradata/HAWKLAS/datafile/o1_mf_sysaux_c711xx75_.dbf

  Backup Set Copy #1 of backup set 1745
  Device Type Elapsed Time Completion Time      Compressed Tag
  ----------- ------------ -------------------- ---------- ---
  DISK        00:02:08     2016-FEB-27 10:05:06 YES        LEVEL0

    List of Backup Pieces for backup set 1745 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    2091    1   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_1_1_s1821_p1.bus
    2092    2   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_2_1_s1821_p2.bus
    2093    3   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_3_1_s1821_p3.bus
    2094    4   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_4_1_s1821_p4.bus
    2095    5   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_5_1_s1821_p5.bus
    2096    6   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_6_1_s1821_p6.bus
    2097    7   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_7_1_s1821_p7.bus
    2098    8   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus
    2099    9   AVAILABLE   /oradata/backup/HAWK_3130551611_20160227_otquvemj_9_1_s1821_p9.bus

RMAN> list backuppiece 2098;


List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
2098    1745    8   1   AVAILABLE   DISK        /oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus

RMAN> exit


Recovery Manager complete.

Note the size for backup piece is 15MB versus 14.02MB from SQL – good enough.

$ du -m /oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus

15      /oradata/backup/HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus
$ sysdba @bkupsize.sql
SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 27 10:52:45 2016

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


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


COMPLETION_TIME     TYPE                MB        MIN
------------------- ----------- ---------- ----------
2016-02-24 00:00:00 ARCHIVELOG         .02          0
2016-02-24 00:00:00 CONTROLFILE      11.23        .05
2016-02-24 00:00:00 FULL            243.56        .82
2016-02-27 00:00:00 ARCHIVELOG       98.25         .9
2016-02-27 00:00:00 CONTROLFILE      22.46        .13
2016-02-27 00:00:00 LEVEL0           505.4      38.52
                                ----------
sum                                 880.92

6 rows selected.


       GRP TYPE          TOTAL_MB
---------- ----------- ----------
         0 ARCHIVELOG       98.27
         0 CONTROLFILE      33.69
         0 FULL            243.56
         0 LEVEL0           505.4
         1                 880.92

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Let’s compare against file system.

oracle@arrow:hawklas:/media/sf_working/rman
$ du -m /oradata/backup/
882     /oradata/backup/

oracle@arrow:hawklas:/media/sf_working/rman
$ ll /oradata/backup/
total 902412
-rw-r-----. 1 oracle oinstall  26853376 Feb 24 18:43 HAWK_3130551611_20160224_o8quog1h_1_1_s1800_p1
-rw-r-----. 1 oracle oinstall 114638848 Feb 24 18:43 HAWK_3130551611_20160224_o9quog1i_1_1_s1801_p1
-rw-r-----. 1 oracle oinstall  42868736 Feb 24 18:43 HAWK_3130551611_20160224_oaquog1p_1_1_s1802_p1
-rw-r-----. 1 oracle oinstall  62169088 Feb 24 18:43 HAWK_3130551611_20160224_obquog2a_1_1_s1803_p1
-rw-r-----. 1 oracle oinstall   8904704 Feb 24 18:43 HAWK_3130551611_20160224_ocquog2b_1_1_s1804_p1
-rw-r-----. 1 oracle oinstall     19968 Feb 24 18:43 HAWK_3130551611_20160224_odquog2r_1_1_s1805_p1
-rw-r-----. 1 oracle oinstall   3551744 Feb 27 09:57 HAWK_3130551611_20160227_ofquvecs_1_1_s1807_p1
-rw-r-----. 1 oracle oinstall  30955520 Feb 27 09:57 HAWK_3130551611_20160227_ogquvecs_1_1_s1808_p1
-rw-r-----. 1 oracle oinstall  14206976 Feb 27 09:57 HAWK_3130551611_20160227_ohquvecv_1_1_s1809_p1
-rw-r-----. 1 oracle oinstall  27041792 Feb 27 09:59 HAWK_3130551611_20160227_oiquved7_1_1_s1810_p1
-rw-r-----. 1 oracle oinstall  31547392 Feb 27 09:58 HAWK_3130551611_20160227_ojquved7_1_1_s1811_p1
-rw-r-----. 1 oracle oinstall  31506432 Feb 27 09:58 HAWK_3130551611_20160227_ojquved7_2_1_s1811_p2
-rw-r-----. 1 oracle oinstall  31563776 Feb 27 09:59 HAWK_3130551611_20160227_ojquved7_3_1_s1811_p3
-rw-r-----. 1 oracle oinstall  22568960 Feb 27 09:59 HAWK_3130551611_20160227_ojquved7_4_1_s1811_p4
-rw-r-----. 1 oracle oinstall  31776768 Feb 27 09:59 HAWK_3130551611_20160227_okquvefb_1_1_s1812_p1
-rw-r-----. 1 oracle oinstall  12451840 Feb 27 09:59 HAWK_3130551611_20160227_okquvefb_2_1_s1812_p2
-rw-r-----. 1 oracle oinstall  31924224 Feb 27 09:59 HAWK_3130551611_20160227_olquveg9_1_1_s1813_p1
-rw-r-----. 1 oracle oinstall  30859264 Feb 27 10:00 HAWK_3130551611_20160227_olquveg9_2_1_s1813_p2
-rw-r-----. 1 oracle oinstall   8929280 Feb 27 09:59 HAWK_3130551611_20160227_omquvego_1_1_s1814_p1
-rw-r-----. 1 oracle oinstall     76288 Feb 27 10:00 HAWK_3130551611_20160227_onquveh9_1_1_s1815_p1
-rw-r-----. 1 oracle oinstall  11970560 Feb 27 10:02 HAWK_3130551611_20160227_opquvem5_1_1_s1817_p1.bus
-rw-r-----. 1 oracle oinstall  14755840 Feb 27 10:02 HAWK_3130551611_20160227_oqquvem5_1_1_s1818_p1.bus
-rw-r-----. 1 oracle oinstall  14749184 Feb 27 10:02 HAWK_3130551611_20160227_oqquvem5_2_1_s1818_p2.bus
-rw-r-----. 1 oracle oinstall   5646848 Feb 27 10:02 HAWK_3130551611_20160227_oqquvem5_3_1_s1818_p3.bus
-rw-r-----. 1 oracle oinstall   1853952 Feb 27 10:02 HAWK_3130551611_20160227_orquvemc_1_1_s1819_p1.bus
-rw-r-----. 1 oracle oinstall  14843904 Feb 27 10:03 HAWK_3130551611_20160227_osquvemj_1_1_s1820_p1.bus
-rw-r-----. 1 oracle oinstall  13025280 Feb 27 10:04 HAWK_3130551611_20160227_osquvemj_2_1_s1820_p2.bus
-rw-r-----. 1 oracle oinstall  14696448 Feb 27 10:03 HAWK_3130551611_20160227_otquvemj_1_1_s1821_p1.bus
-rw-r-----. 1 oracle oinstall  14737408 Feb 27 10:03 HAWK_3130551611_20160227_otquvemj_2_1_s1821_p2.bus
-rw-r-----. 1 oracle oinstall  14696448 Feb 27 10:03 HAWK_3130551611_20160227_otquvemj_3_1_s1821_p3.bus
-rw-r-----. 1 oracle oinstall  14827520 Feb 27 10:03 HAWK_3130551611_20160227_otquvemj_4_1_s1821_p4.bus
-rw-r-----. 1 oracle oinstall  14802944 Feb 27 10:04 HAWK_3130551611_20160227_otquvemj_5_1_s1821_p5.bus
-rw-r-----. 1 oracle oinstall  14802944 Feb 27 10:04 HAWK_3130551611_20160227_otquvemj_6_1_s1821_p6.bus
-rw-r-----. 1 oracle oinstall  14737408 Feb 27 10:04 HAWK_3130551611_20160227_otquvemj_7_1_s1821_p7.bus
-rw-r-----. 1 oracle oinstall  14704640 Feb 27 10:04 HAWK_3130551611_20160227_otquvemj_8_1_s1821_p8.bus
-rw-r-----. 1 oracle oinstall   3637248 Feb 27 10:05 HAWK_3130551611_20160227_otquvemj_9_1_s1821_p9.bus
-rw-r-----. 1 oracle oinstall  14868480 Feb 27 10:04 HAWK_3130551611_20160227_ouquvep1_1_1_s1822_p1.bus
-rw-r-----. 1 oracle oinstall  14843904 Feb 27 10:04 HAWK_3130551611_20160227_ouquvep1_2_1_s1822_p2.bus
-rw-r-----. 1 oracle oinstall  14712832 Feb 27 10:04 HAWK_3130551611_20160227_ouquvep1_3_1_s1822_p3.bus
-rw-r-----. 1 oracle oinstall   1785856 Feb 27 10:05 HAWK_3130551611_20160227_ouquvep1_4_1_s1822_p4.bus
-rw-r-----. 1 oracle oinstall  14868480 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_1_1_s1823_p1.bus
-rw-r-----. 1 oracle oinstall  14893056 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_2_1_s1823_p2.bus
-rw-r-----. 1 oracle oinstall  14786560 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_3_1_s1823_p3.bus
-rw-r-----. 1 oracle oinstall  14925824 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_4_1_s1823_p4.bus
-rw-r-----. 1 oracle oinstall   5881856 Feb 27 10:05 HAWK_3130551611_20160227_ovquveqn_5_1_s1823_p5.bus
-rw-r-----. 1 oracle oinstall   8929280 Feb 27 10:05 HAWK_3130551611_20160227_p0quveqo_1_1_s1824_p1.bus
-rw-r-----. 1 oracle oinstall   5254144 Feb 27 10:06 HAWK_3130551611_20160227_p1quvese_1_1_s1825_p1.bus
-rw-r-----. 1 oracle oinstall  11796480 Feb 24 18:43 HAWK_c-3130551611-20160224-0e
-rw-r-----. 1 oracle oinstall  11796480 Feb 27 10:00 HAWK_c-3130551611-20160227-00
-rw-r-----. 1 oracle oinstall  11796480 Feb 27 10:06 HAWK_c-3130551611-20160227-01
oracle@arrow:hawklas:/media/sf_working/rman
$
bkupinfo.sql


col BP_MB for 9,999,999.99
break on report
COMPUTE sum of BP_MB on report
set numw 6 lines 120
col handle for a100
SELECT
s.completion_time,
s.recid BS_KEY,
CASE
WHEN s.backup_type='L' THEN 'ARCHIVELOG'
WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
WHEN s.backup_type='D' AND s.incremental_level IS NULL THEN 'FULL'
END type,
p.recid BP_KEY,
round(p.bytes/1048576,2) BP_MB,
s.pieces,
s.set_count,
p.piece#,
p.compressed,
p.device_type,
p.handle
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A'
AND s.set_stamp (+) = p.set_stamp
AND s.set_count (+) = p.set_count
order by s.recid, p.piece#
;

bkupsize.sql


col MB for 9,999,999.99
col min for 9,999,999.99
col TOTAL_MB for 9,999,999.99
break on report
COMPUTE sum of MB on report
SELECT TRUNC(completion_time) completion_time, type, round(sum(MB),2) MB, round(sum(elapsed_seconds)/60,2) min
FROM (
SELECT
CASE
WHEN s.backup_type='L' THEN 'ARCHIVELOG'
WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
WHEN s.backup_type='D' AND s.incremental_level IS NULL THEN 'FULL'
END type,
TRUNC(s.completion_time) completion_time,
round(p.bytes/1048576,2) MB,
s.elapsed_seconds
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A'
AND s.set_stamp = p.set_stamp
AND s.set_count = p.set_count
-- UNION ALL
-- SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
)
GROUP BY TRUNC(completion_time), type
ORDER BY 1 asc,2,3
;
SELECT grouping(type) grp, type, round(sum(MB),2) TOTAL_MB
FROM (
SELECT
CASE
WHEN s.backup_type='L' THEN 'ARCHIVELOG'
WHEN s.controlfile_included='YES' THEN 'CONTROLFILE'
WHEN s.backup_type='D' AND s.incremental_level=0 THEN 'LEVEL0'
WHEN s.backup_type='I' AND s.incremental_level=1 THEN 'LEVEL1'
WHEN s.backup_type='D' AND s.incremental_level IS NULL THEN 'FULL'
END type,
TRUNC(s.completion_time) completion_time,
round(p.bytes/1048576,2) MB,
s.elapsed_seconds
FROM v$backup_piece p, v$backup_set s
WHERE p.status='A'
AND s.set_stamp = p.set_stamp
AND s.set_count = p.set_count
-- UNION ALL
-- SELECT 'DATAFILECOPY' type, TRUNC(completion_time), output_bytes, 0 elapsed_seconds FROM v$backup_copy_details
)
GROUP BY ROLLUP(type)
ORDER BY 1 asc,2,3
;


CVE-2015-7547 for Oracle Database

Mon, 2016-02-22 19:20

I am sure by now you have heard about the bug.

Reboot Required!

Need to update to release el6_7.7 if you are on OEL 6

glibc-2.12-1.166.el6_7.7.x86_64.rpm
glibc-common-2.12-1.166.el6_7.7.x86_64.rpm
glibc-devel-2.12-1.166.el6_7.7.i686.rpm
glibc-devel-2.12-1.166.el6_7.7.x86_64.rpm
glibc-headers-2.12-1.166.el6_7.7.x86_64.rpm
nscd-2.12-1.166.el6_7.7.x86_64.rpm
glibc-devel-2.12-1.166.el6_7.7.i686.rpm
glibc-2.12-1.166.el6_7.7.i686.rpm

http://linux.oracle.com/errata/ELSA-2016-0175.html

You can also use the following commands to verify RPMs for CVE_2015_7547

There are two dashes (- -) for cve.

$ yum list –cve=2015_7547|egrep ‘^glibc|^nscd’|sort

Skipping security plugin, other command
glibc-common.x86_64                  2.12-1.166.el6_7.7       @public_ol6_latest
glibc-devel.i686                     2.12-1.166.el6_7.7       @public_ol6_latest
glibc-devel.x86_64                   2.12-1.166.el6_7.7       @public_ol6_latest
glibc-headers.x86_64                 2.12-1.166.el6_7.7       @public_ol6_latest
glibc.i686                           2.12-1.166.el6_7.7       @public_ol6_latest
glibc-static.i686                    2.12-1.166.el6_7.7       public_ol6_latest
glibc-static.x86_64                  2.12-1.166.el6_7.7       public_ol6_latest
glibc-utils.x86_64                   2.12-1.166.el6_7.7       public_ol6_latest
glibc.x86_64                         2.12-1.166.el6_7.7       @public_ol6_latest
nscd.x86_64                          2.12-1.166.el6_7.7       public_ol6_latest

Note that it is not necessary to relink any binaries after this update.

Reference: glibc vulnerability (CVE-2015-7547) patch availability for Oracle Exadata Database Machine (Doc ID 2108582.1)

Assumption being made if relink is not required for Exadata, then it is not required for Non-Exadata as well.

Be safe and verify with Oracle support and let me know too please.

$ cat /etc/oracle-release

Oracle Linux Server release 6.6

$ rpm -qa –queryformat=”%{name}-%{version}-%{release}.%{arch}\n” | egrep ‘glibc|nscd’

glibc-devel-2.12-1.149.el6_6.9.i686
glibc-common-2.12-1.149.el6_6.9.x86_64
glibc-2.12-1.149.el6_6.9.i686
glibc-headers-2.12-1.149.el6_6.9.x86_64
glibc-devel-2.12-1.149.el6_6.9.x86_64
glibc-2.12-1.149.el6_6.9.x86_64

[root@arrow ~]# yum update glibc

Loaded plugins: refresh-packagekit, security
Setting up Update Process
public_ol6_UEKR3_latest                                                                                                               | 1.2 kB     00:00
public_ol6_UEKR3_latest/primary                                                                                                       |  22 MB     00:10
public_ol6_UEKR3_latest                                                                                                                              559/559
public_ol6_latest                                                                                                                     | 1.4 kB     00:00
public_ol6_latest/primary                                                                                                             |  55 MB     00:24
public_ol6_latest                                                                                                                                33290/33290
Resolving Dependencies
--> Running transaction check
---> Package glibc.i686 0:2.12-1.149.el6_6.9 will be updated
--> Processing Dependency: glibc = 2.12-1.149.el6_6.9 for package: glibc-common-2.12-1.149.el6_6.9.x86_64
--> Processing Dependency: glibc = 2.12-1.149.el6_6.9 for package: glibc-devel-2.12-1.149.el6_6.9.i686
--> Processing Dependency: glibc = 2.12-1.149.el6_6.9 for package: glibc-headers-2.12-1.149.el6_6.9.x86_64
--> Processing Dependency: glibc = 2.12-1.149.el6_6.9 for package: glibc-devel-2.12-1.149.el6_6.9.x86_64
---> Package glibc.x86_64 0:2.12-1.149.el6_6.9 will be updated
---> Package glibc.i686 0:2.12-1.166.el6_7.7 will be an update
---> Package glibc.x86_64 0:2.12-1.166.el6_7.7 will be an update
--> Running transaction check
---> Package glibc-common.x86_64 0:2.12-1.149.el6_6.9 will be updated
---> Package glibc-common.x86_64 0:2.12-1.166.el6_7.7 will be an update
---> Package glibc-devel.i686 0:2.12-1.149.el6_6.9 will be updated
---> Package glibc-devel.x86_64 0:2.12-1.149.el6_6.9 will be updated
---> Package glibc-devel.i686 0:2.12-1.166.el6_7.7 will be an update
---> Package glibc-devel.x86_64 0:2.12-1.166.el6_7.7 will be an update
---> Package glibc-headers.x86_64 0:2.12-1.149.el6_6.9 will be updated
---> Package glibc-headers.x86_64 0:2.12-1.166.el6_7.7 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================
 Package                              Arch                          Version                                   Repository                                Size
=============================================================================================================================================================
Updating:
 glibc                                i686                          2.12-1.166.el6_7.7                        public_ol6_latest                        4.3 M
 glibc                                x86_64                        2.12-1.166.el6_7.7                        public_ol6_latest                        3.8 M
Updating for dependencies:
 glibc-common                         x86_64                        2.12-1.166.el6_7.7                        public_ol6_latest                         14 M
 glibc-devel                          i686                          2.12-1.166.el6_7.7                        public_ol6_latest                        986 k
 glibc-devel                          x86_64                        2.12-1.166.el6_7.7                        public_ol6_latest                        986 k
 glibc-headers                        x86_64                        2.12-1.166.el6_7.7                        public_ol6_latest                        615 k

Transaction Summary
=============================================================================================================================================================
Upgrade       6 Package(s)

Total download size: 25 M
Is this ok [y/N]: y
Downloading Packages:
(1/6): glibc-2.12-1.166.el6_7.7.i686.rpm                                                                                              | 4.3 MB     00:02
(2/6): glibc-2.12-1.166.el6_7.7.x86_64.rpm                                                                                            | 3.8 MB     00:02
(3/6): glibc-common-2.12-1.166.el6_7.7.x86_64.rpm                                                                                     |  14 MB     00:05
(4/6): glibc-devel-2.12-1.166.el6_7.7.i686.rpm                                                                                        | 986 kB     00:00
(5/6): glibc-devel-2.12-1.166.el6_7.7.x86_64.rpm                                                                                      | 986 kB     00:00
(6/6): glibc-headers-2.12-1.166.el6_7.7.x86_64.rpm                                                                                    | 615 kB     00:00
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                        1.8 MB/s |  25 MB     00:13
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Updating   : glibc-common-2.12-1.166.el6_7.7.x86_64                                                                                                   1/12
  Updating   : glibc-2.12-1.166.el6_7.7.x86_64                                                                                                          2/12
  Updating   : glibc-headers-2.12-1.166.el6_7.7.x86_64                                                                                                  3/12
  Updating   : glibc-2.12-1.166.el6_7.7.i686                                                                                                            4/12
  Updating   : glibc-devel-2.12-1.166.el6_7.7.i686                                                                                                      5/12
  Updating   : glibc-devel-2.12-1.166.el6_7.7.x86_64                                                                                                    6/12
  Cleanup    : glibc-devel-2.12-1.149.el6_6.9                                                                                                           7/12
  Cleanup    : glibc-devel-2.12-1.149.el6_6.9                                                                                                           8/12
  Cleanup    : glibc-2.12-1.149.el6_6.9                                                                                                                 9/12
  Cleanup    : glibc-headers-2.12-1.149.el6_6.9.x86_64                                                                                                 10/12
  Cleanup    : glibc-2.12-1.149.el6_6.9                                                                                                                11/12
  Cleanup    : glibc-common-2.12-1.149.el6_6.9.x86_64                                                                                                  12/12
  Verifying  : glibc-2.12-1.166.el6_7.7.i686                                                                                                            1/12
  Verifying  : glibc-devel-2.12-1.166.el6_7.7.i686                                                                                                      2/12
  Verifying  : glibc-headers-2.12-1.166.el6_7.7.x86_64                                                                                                  3/12
  Verifying  : glibc-devel-2.12-1.166.el6_7.7.x86_64                                                                                                    4/12
  Verifying  : glibc-2.12-1.166.el6_7.7.x86_64                                                                                                          5/12
  Verifying  : glibc-common-2.12-1.166.el6_7.7.x86_64                                                                                                   6/12
  Verifying  : glibc-headers-2.12-1.149.el6_6.9.x86_64                                                                                                  7/12
  Verifying  : glibc-2.12-1.149.el6_6.9.x86_64                                                                                                          8/12
  Verifying  : glibc-common-2.12-1.149.el6_6.9.x86_64                                                                                                   9/12
  Verifying  : glibc-devel-2.12-1.149.el6_6.9.x86_64                                                                                                   10/12
  Verifying  : glibc-2.12-1.149.el6_6.9.i686                                                                                                           11/12
  Verifying  : glibc-devel-2.12-1.149.el6_6.9.i686                                                                                                     12/12

Updated:
  glibc.i686 0:2.12-1.166.el6_7.7                                              glibc.x86_64 0:2.12-1.166.el6_7.7

Dependency Updated:
  glibc-common.x86_64 0:2.12-1.166.el6_7.7              glibc-devel.i686 0:2.12-1.166.el6_7.7             glibc-devel.x86_64 0:2.12-1.166.el6_7.7
  glibc-headers.x86_64 0:2.12-1.166.el6_7.7

Complete!
[root@arrow ~]#
[root@arrow ~]# init 6
[root@arrow ~]#

$ rpm -qa –queryformat=”%{name}-%{version}-%{release}.%{arch}\n” | egrep ‘glibc|nscd’

glibc-devel-2.12-1.166.el6_7.7.i686
glibc-headers-2.12-1.166.el6_7.7.x86_64
glibc-2.12-1.166.el6_7.7.x86_64
glibc-devel-2.12-1.166.el6_7.7.x86_64
glibc-common-2.12-1.166.el6_7.7.x86_64
glibc-2.12-1.166.el6_7.7.i686

$ rpm -qa –queryformat=”%{name}-%{release}.%{arch}\n” | egrep ‘glibc|nscd’

oracle@arrow:hawklas:/home/oracle
glibc-devel-1.166.el6_7.7.i686
glibc-headers-1.166.el6_7.7.x86_64
glibc-1.166.el6_7.7.x86_64
glibc-devel-1.166.el6_7.7.x86_64
glibc-common-1.166.el6_7.7.x86_64
glibc-1.166.el6_7.7.i686

Create DB with minimum parameters

Sun, 2016-02-21 20:10

Just a quick post to create database with minimum parameters.

Note: for the control_files parameter to be updated, you must create spfile from pfile.

oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ ls *test*
inittest.ora  orapwtest
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ cat inittest.ora
db_name=test
db_create_file_dest='/oradata'
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 21 18:02:12 2016

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

Connected to an idle instance.

SYS@test> create spfile from pfile;

File created.

SYS@test> startup nomount;
ORACLE instance started.

Total System Global Area  229683200 bytes
Fixed Size                  2251936 bytes
Variable Size             171967328 bytes
Database Buffers           50331648 bytes
Redo Buffers                5132288 bytes
SYS@test> CREATE DATABASE
CHARACTER SET AL32UTF8
  2    3  EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 256M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
  4    5  sysaux DATAFILE SIZE 64M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
  6  LOGFILE GROUP 1 SIZE 100M,GROUP 2 SIZE 100M
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 64M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
  7    8  DEFAULT TABLESPACE users DATAFILE SIZE 64M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
  9  UNDO TABLESPACE undotbs DATAFILE SIZE 64M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
; 10

Database created.

SYS@test> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata/TEST/controlfile/o1_mf_cdnv3bjv_.ctl

SYS@test> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ strings spfiletest.ora
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.control_files='/oradata/TEST/controlfile/o1_mf_cdnv3bjv_.ctl'#Oracle managed file
*.db_create_file_dest='/oradata'
*.db_name='test'


oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 21 18:09:48 2016

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


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

ARROW:(SYS@test):PRIMARY> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfiletest.ora
ARROW:(SYS@test):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$

Many years ago, I had learned about drop database.
https://mdinh.wordpress.com/?s=drop+database

It does not do a complete job for cleanup.

What I did not know then which I now know, it does drop spfile as spfile is considered as part of the database, may be because it can get backup from RMAN just like the database?

oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ ll *test*
-rw-rw----. 1 oracle oinstall 1544 Feb 21 18:14 hc_test.dat
-rw-r--r--. 1 oracle oinstall   44 Feb 21 18:01 inittest.ora
-rw-r-----. 1 oracle oinstall 2048 Feb 21 17:54 orapwtest
-rw-r-----. 1 oracle oinstall 1536 Feb 21 18:03 spfiletest.ora
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 21 18:15:21 2016

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

Connected to an idle instance.

SYS@test> startup restrict force mount;
ORACLE instance started.

Total System Global Area  229683200 bytes
Fixed Size                  2251936 bytes
Variable Size             171967328 bytes
Database Buffers           50331648 bytes
Redo Buffers                5132288 bytes
Database mounted.
SYS@test> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@test> exit
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ ll *test*
-rw-rw----. 1 oracle oinstall 1544 Feb 21 18:17 hc_test.dat
-rw-r--r--. 1 oracle oinstall   44 Feb 21 18:01 inittest.ora
-rw-r-----. 1 oracle oinstall 2048 Feb 21 17:54 orapwtest
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ sysresv

IPC Resources for ORACLE_SID "test" :
Shared Memory
ID              KEY
No shared memory segments used
Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid "test"
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ ll /oradata/
total 56
drwxr-xr-x. 2 oracle oinstall 16384 Feb  1 11:59 backup
-rw-r--r--. 1 oracle oinstall    60 Nov  5  2014 bkup.rman
-rwxr-xr-x. 1 oracle oinstall   783 Nov  5  2014 bkup.sh
-rwxr-xr-x. 1 oracle oinstall   443 Nov  5  2014 bkup.sh.bak
drwxr-xr-x. 4 oracle oinstall  4096 Jan  9 15:08 fra
-rw-r--r--. 1 oracle oinstall   287 Nov  6  2014 fra.rman
drwxr-xr-x. 5 oracle oinstall  4096 Jan 14 21:28 HAWKLAS
drwxrwxr-x. 5 oracle oinstall  4096 Jan 14 21:28 HAWKSAN
drwxr-xr-x. 2 oracle oinstall  4096 Jan 28 11:07 keep
-rw-r--r--. 1 oracle oinstall   230 Jul 24  2014 restore_validate.rman
-rw-r--r--. 1 oracle oinstall   164 Jul 25  2014 simple.rman
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$


oracle@arrow:hawklas:/u01/app/oracle/diag
$ ll
total 8
drwxr-xr-x. 6 oracle oinstall 4096 Feb 21 17:55 rdbms
drwxr-xr-x. 3 oracle oinstall 4096 Sep 25 06:37 tnslsnr
oracle@arrow:hawklas:/u01/app/oracle/diag
$ cd rdbms/
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms
$ ll
total 16
drwxr-xr-x. 3 oracle oinstall 4096 Jan  9 16:34 hawk
drwxr-xr-x. 3 oracle oinstall 4096 Jan  9 16:31 hawklas
drwxr-xr-x. 3 oracle oinstall 4096 Jan  9 16:39 hawksan
drwxr-xr-x. 3 oracle oinstall 4096 Feb 21 18:17 test
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms
$ cd test/
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/test
$ ll
total 4
-rw-r-----.  1 oracle oinstall    0 Feb 21 18:02 i_1.mif
drwxr-xr-x. 15 oracle oinstall 4096 Feb 21 18:17 test
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/test
$ cd test/
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/test/test
$ ll
total 52
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 alert
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 cdump
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 hm
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 incident
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 incpkg
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 ir
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:19 lck
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:19 metadata
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 metadata_dgif
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 metadata_pv
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 stage
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 sweep
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 trace
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/test/test
$


Converting to Interval Paritions

Wed, 2016-02-17 17:24
Just a quick note to demonstrate how to convert date range partition into interval partition. Create test case:
ARROW:(MDINH@hawklas):PRIMARY> @test_part.sql
ARROW:(MDINH@hawklas):PRIMARY> set echo on
ARROW:(MDINH@hawklas):PRIMARY> drop table t_part purge;

Table dropped.

ARROW:(MDINH@hawklas):PRIMARY> create table t_part (
  2  col_date DATE
  3  )
  4  PARTITION BY RANGE (col_date)
  5  (
  6  PARTITION P2014 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD')),
  7  PARTITION P2015 VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')),
  8  PARTITION PMAX VALUES LESS THAN (MAXVALUE)
  9  );

Table created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2014-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2015-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2016-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> commit;

Commit complete.

ARROW:(MDINH@hawklas):PRIMARY> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'T_PART',cascade=>DBMS_STATS.AUTO_CASCADE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => NULL);

PL/SQL procedure successfully completed.
Check partitions:
ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
  2  FROM dba_tab_partitions i
  3  WHERE table_name='T_PART'
  4  ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  PMAX                          1                  0 MAXVALUE

ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
  2    table_name,
  3    partitioning_type,
  4    subpartitioning_type,
  5    partition_count,
  6    def_subpartition_count,
  7    partitioning_key_count,
  8    def_tablespace_name
  9  FROM DBA_PART_TABLES
 10  WHERE partitioning_type='RANGE'
 11  AND table_name='T_PART'
 12  ;

OWNER                TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH                T_PART               RANGE     NONE                    3                      0                      1 USERS
Failed due to PMAX partition as shown above:
ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(numtodsinterval(1,'year'));
alter table t_part set interval(numtodsinterval(1,'year'))
*
ERROR at line 1:
ORA-14759: SET INTERVAL is not legal on this table.
Split partition to remove PMAX:
ARROW:(MDINH@hawklas):PRIMARY> alter table t_part split partition PMAX AT(TO_DATE('2017-01-01', 'YYYY-MM-DD')) into (partition P2017, partition PMAX);

Table altered.

ARROW:(MDINH@hawklas):PRIMARY> alter table t_part drop partition pmax;

Table altered.
Check partition:
ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
  2  FROM dba_tab_partitions i
  3  WHERE table_name='T_PART'
  4  ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2017                         1                  0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')


ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
  2    table_name,
  3    partitioning_type,
  4    subpartitioning_type,
  5    partition_count,
  6    def_subpartition_count,
  7    partitioning_key_count,
  8    def_tablespace_name
  9  FROM DBA_PART_TABLES
 10  WHERE partitioning_type='RANGE'
 11  AND table_name='T_PART'
 12  ;

OWNER                TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH                T_PART               RANGE     NONE                    3                      0                      1 USERS



Wrong syntax:
ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(numtodsinterval(1,'year'));
alter table t_part set interval(numtodsinterval(1,'year'))
                                                  *
ERROR at line 1:
ORA-14752: Interval expression is not a constant of the correct type
Correct syntax:
ARROW:(MDINH@hawklas):PRIMARY> alter table t_part set interval(NUMTOYMINTERVAL(1,'year'));

Table altered.
Check partition:
ARROW:(MDINH@hawklas):PRIMARY> @part
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
  2  FROM dba_tab_partitions i
  3  WHERE table_name='T_PART'
  4  ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2017                         1                  0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
  2    table_name,
  3    partitioning_type,
  4    subpartitioning_type,
  5    partition_count,
  6    def_subpartition_count,
  7    partitioning_key_count,
  8    def_tablespace_name
  9  FROM DBA_PART_TABLES
 10  WHERE partitioning_type='RANGE'
 11  AND table_name='T_PART'
 12  ;

OWNER                TABLE_NAME           PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH                T_PART               RANGE     NONE              1048575                      0                      1 USERS
Add data:
ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2018-01-01','YYYY-MM-DD'));

1 row created.

ARROW:(MDINH@hawklas):PRIMARY> insert into t_part values (TO_DATE('2019-01-01','YYYY-MM-DD'));

1 row created.


ARROW:(MDINH@hawklas):PRIMARY> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'T_PART',cascade=>DBMS_STATS.AUTO_CASCADE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',estimate_percent => NULL);

PL/SQL procedure successfully completed.
Check partition:
ARROW:(MDINH@hawklas):PRIMARY> @part.sql
ARROW:(MDINH@hawklas):PRIMARY> set lines 200 pages 10000
ARROW:(MDINH@hawklas):PRIMARY> col table_owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col owner for a20
ARROW:(MDINH@hawklas):PRIMARY> col table_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col partition_name for a20
ARROW:(MDINH@hawklas):PRIMARY> col high_value for a50 wrap
ARROW:(MDINH@hawklas):PRIMARY> SELECT table_owner,table_name,interval,partition_name,num_rows,subpartition_count,high_value
2 FROM dba_tab_partitions i
3 WHERE table_name='T_PART'
4 ;

TABLE_OWNER          TABLE_NAME           INT PARTITION_NAME         NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE
-------------------- -------------------- --- -------------------- ---------- ------------------ --------------------------------------------------
MDINH                T_PART               NO  P2014                         1                  0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2015                         1                  0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               NO  P2017                         1                  0 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               YES SYS_P541                      1                  0 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')

MDINH                T_PART               YES SYS_P542                      1                  0 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                                                 MI:SS', 'NLS_CALENDAR=GREGORIAN')
ARROW:(MDINH@hawklas):PRIMARY> SELECT owner,
2 table_name,
3 partitioning_type,
4 subpartitioning_type,
5 partition_count,
6 def_subpartition_count,
7 partitioning_key_count,
8 def_tablespace_name
9 FROM DBA_PART_TABLES
10 WHERE partitioning_type='RANGE'
11 AND table_name='T_PART'
12 ;

OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------- --------- --------------- ---------------------- ---------------------- ------------------------------
MDINH T_PART RANGE NONE 1048575 0 1 USERS

ARROW:(MDINH@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawklas:/media/sf_working/sql
$

Not Another Standby Monitoring Script

Fri, 2016-01-29 21:55

I know what you are thinking. Not another standby monitoring script, right?

There’s plenty of them out there already and what makes this one any different?

You are probably correct and there’s probably nothing different about this.

Having to work with environments that are inconsistent such as manual standby, dataguard without broker, dataguard with broker,
I have been obsessed to create standby SQL which can accommodate any environment and can be run from primary or standby.

From PRIMARY: Recovery is IDLE
oracle@arrow:hawklas:/media/sf_working/dataguard
$ . oraenv <<< hawklas
ORACLE_SID = [hawklas] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawklas:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:04:05 2016

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


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

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawklas        READ WRITE           PRIMARY          ENABLED  TO STANDBY           ENABLED   hawksan

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        PRIMARY ACTIVE   ARCH              0
       1        2 hawksan                   VALID        STANDBY ACTIVE   LGWR              0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN            IDLE                                    NONE
       2 VALID        OPEN_READ-ONLY  IDLE                    NO GAP          NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       2        1 NO             886 29-JAN-2016 17:50:04         4      3.7   --- STANDBY LAG
       2        1 YES            882 29-JAN-2016 17:46:22

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

no rows selected

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    2659        1        1 LNS       LNS      WRITING            887    10153          0

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
From STANDBY: Recovery is IDLE
oracle@arrow:hawklas:/media/sf_working/dataguard
$ . oraenv <<< hawksan
ORACLE_SID = [hawklas] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawksan:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:04:11 2016

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


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

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawksan        READ ONLY            PHYSICAL STANDBY ENABLED  NOT ALLOWED          ENABLED   hawklas

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   ARCH              0
       1       32 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   RFS               0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN_READ-ONLY  IDLE                                    NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       1        1 NO             886 29-JAN-2016 17:50:04         4      3.7   --- STANDBY LAG
       1        1 YES            882 29-JAN-2016 17:46:22

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 882 thread 1;

*** v$dataguard_stats ***

NAME                      VALUE              UNIT
------------------------- ------------------ ------------------------------
transport lag             +00 00:00:00       day(2) to second(0) interval
apply lag                 +00 01:14:48       day(2) to second(0) interval

*** gv$managed_standby ***

no rows selected

ARROW:(SYS@hawksan):PHYSICAL STANDBY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
From DG Broker: SET STATE=’APPLY-ON’

Note: Apply Lag is NOT really 1 hour.

Look at the time difference between SQL*Plus sessions.

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:04:11 2016
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:07:28 2016
oracle@arrow:hawksan:/media/sf_working/dataguard
$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       1 hour(s) 17 minutes 16 seconds (computed 1 second ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> EDIT DATABASE hawksan SET STATE='APPLY-ON';
Succeeded.
DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 2 seconds ago)
  Apply Lag:       1 hour(s) 17 minutes 47 seconds (computed 2 seconds ago)
  Apply Rate:      465.00 KByte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> exit
From PRIMARY: Recovery is STILL IDLE
oracle@arrow:hawksan:/media/sf_working/dataguard
$ . oraenv <<< hawklas
ORACLE_SID = [hawksan] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawklas:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:07:20 2016

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


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

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawklas        READ WRITE           PRIMARY          ENABLED  TO STANDBY           ENABLED   hawksan

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        PRIMARY ACTIVE   ARCH              0
       1        2 hawksan                   VALID        STANDBY ACTIVE   LGWR              0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN            IDLE                                    NONE
       2 VALID        OPEN_READ-ONLY  IDLE                    NO GAP          NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       2        1 NO             886 29-JAN-2016 17:50:04         4      3.7   --- STANDBY LAG
       2        1 YES            882 29-JAN-2016 17:46:22

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

no rows selected

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    2659        1        1 LNS       LNS      WRITING            887    10402          0

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
From STANDBY: Recovery is MANAGED REAL TIME APPLY
oracle@arrow:hawklas:/media/sf_working/dataguard
$ . oraenv <<< hawksan
ORACLE_SID = [hawklas] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawksan:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:07:28 2016

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


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

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawksan        READ ONLY WITH APPLY PHYSICAL STANDBY ENABLED  NOT ALLOWED          ENABLED   hawklas

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   ARCH              0
       1       32 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   RFS               0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN_READ-ONLY  MANAGED REAL TIME APPLY                 NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       1        1 IN-MEMORY      886 29-JAN-2016 17:50:04         1 .0333333   --- STANDBY LAG
       1        1 YES            885 29-JAN-2016 17:50:02

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

NAME                      VALUE              UNIT
------------------------- ------------------ ------------------------------
transport lag             +00 00:00:00       day(2) to second(0) interval
apply lag                 +00 00:00:00       day(2) to second(0) interval

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    4463        1        1 MRP0      N/A      APPLYING_LOG       887    10409          0

ARROW:(SYS@hawksan):PHYSICAL STANDBY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawksan:/media/sf_working/dataguard
$
Let’s check again. From PRIMARY: Recovery is MANAGED REAL TIME APPLY
oracle@arrow:hawksan:/media/sf_working/dataguard
$ . oraenv <<< hawklas
ORACLE_SID = [hawksan] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawklas:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:09:14 2016

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


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

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawklas        READ WRITE           PRIMARY          ENABLED  TO STANDBY           ENABLED   hawksan

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        PRIMARY ACTIVE   ARCH              0
       1        2 hawksan                   VALID        STANDBY ACTIVE   LGWR              0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN            IDLE                                    NONE
       2 VALID        OPEN_READ-ONLY  MANAGED REAL TIME APPLY NO GAP          NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       2        1 NO             886 29-JAN-2016 17:50:04         1 .0333333   --- STANDBY LAG
       2        1 YES            885 29-JAN-2016 17:50:02

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

no rows selected

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    2659        1        1 LNS       LNS      WRITING            887    10512          0

ARROW:(SYS@hawklas):PRIMARY>

SQL can be download from my Public Google Drive.

Look all the way to the right under Menu or search page for PublicGoogleDrive


Bad to crosscheck archivelog all

Thu, 2016-01-28 16:44

Typically, it’s not a good idea to have “crosscheck archivelog all;” in backup scripts and
even worse to have “delete expired archivelog all;” since any evidence will be eradicated.

When you don’t crosscheck archivelog and the archivelog is missing, backup archivelog will fail.
When you crosscheck archivelog and archivelog is missing backup archivelog does not fail since RMAN marks archivelog as expired and ignore.

oracle@arrow:hawklas:/tmp
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 28 14:17:35 2016

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

connected to target database: HAWK (DBID=3130551611)
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
5191    1    868     X 2016-JAN-28 11:46:46
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_868_cbnw3vtd_.arc

5201    1    873     A 2016-JAN-28 14:15:44
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc

RMAN> list backup of archivelog sequence 873;
specification does not match any backup in the repository

RMAN> exit
$ mv -v /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc /tmp/
oracle@arrow:hawklas:/tmp
`/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc' -> `/tmp/o1_mf_1_873_cbo4v850_.arc'
oracle@arrow:hawklas:/tmp
$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 28 14:18:30 2016

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

connected to target database: HAWK (DBID=3130551611)
RMAN> backup archivelog all;
Starting backup at 2016-JAN-28 14:18:40
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 01/28/2016 14:18:46
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
validation succeeded for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_874_cbo4y4or_.arc RECID=5203 STAMP=902326725
Crosschecked 1 objects

validation failed for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_868_cbnw3vtd_.arc RECID=5191 STAMP=902317692
validation failed for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc RECID=5201 STAMP=902326632
Crosschecked 2 objects

RMAN> backup archivelog all;

sequence=873 is missing and no errors raised.

Starting backup at 2016-JAN-28 14:19:09
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=874 RECID=5203 STAMP=902326725
channel ORA_DISK_1: starting piece 1 at 2016-JAN-28 14:19:11
channel ORA_DISK_2: starting compressed archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=875 RECID=5204 STAMP=902326750
channel ORA_DISK_2: starting piece 1 at 2016-JAN-28 14:19:11
channel ORA_DISK_1: finished piece 1 at 2016-JAN-28 14:19:12
piece handle=/oradata/backup/HAWK_3130551611_20160128_jdqsgqev_1_1_1645_1 tag=TAG20160128T141910 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: finished piece 1 at 2016-JAN-28 14:19:14
piece handle=/oradata/backup/HAWK_3130551611_20160128_jeqsgqev_1_1_1646_1 tag=TAG20160128T141910 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03
Finished backup at 2016-JAN-28 14:19:14

Starting Control File and SPFILE Autobackup at 2016-JAN-28 14:19:14
piece handle=/oradata/backup/HAWK_c-3130551611-20160128-09 comment=NONE
Finished Control File and SPFILE Autobackup at 2016-JAN-28 14:19:21
RMAN> list backup of archivelog sequence 873;
specification does not match any backup in the repository

RMAN> exit


Recovery Manager complete.
oracle@arrow:hawklas:/tmp
$

RMAN> delete expired archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
5191    1    868     X 2016-JAN-28 11:46:46
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_868_cbnw3vtd_.arc

5201    1    873     X 2016-JAN-28 14:15:44
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_868_cbnw3vtd_.arc RECID=5191 STAMP=902317692
deleted archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_873_cbo4v850_.arc RECID=5201 STAMP=902326632
Deleted 2 EXPIRED objects
RMAN> backup archivelog all;
Starting backup at 2016-JAN-28 14:36:20
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=874 RECID=5203 STAMP=902326725
input archived log thread=1 sequence=875 RECID=5204 STAMP=902326750
channel ORA_DISK_1: starting piece 1 at 2016-JAN-28 14:36:21
channel ORA_DISK_2: starting compressed archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=876 RECID=5207 STAMP=902327780
channel ORA_DISK_2: starting piece 1 at 2016-JAN-28 14:36:22
channel ORA_DISK_1: finished piece 1 at 2016-JAN-28 14:36:23
piece handle=/oradata/backup/HAWK_3130551611_20160128_jgqsgrf5_1_1_1648_1 tag=TAG20160128T143621 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_2: finished piece 1 at 2016-JAN-28 14:36:23
piece handle=/oradata/backup/HAWK_3130551611_20160128_jhqsgrf5_1_1_1649_1 tag=TAG20160128T143621 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-JAN-28 14:36:23

Starting Control File and SPFILE Autobackup at 2016-JAN-28 14:36:23
piece handle=/oradata/backup/HAWK_c-3130551611-20160128-0a comment=NONE
Finished Control File and SPFILE Autobackup at 2016-JAN-28 14:36:30

RMAN>
Updated:

Does this mean we should never perform crosscheck archivelog all? No.

The purpose is to let archivelog backup fail and to investigate.

If investigation shows archivelog is indeed missing, then might be better to perform Level 1 or Level 0 backup.

RMAN> crosscheck archivelog all;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=149 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
validation succeeded for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_874_cbo4y4or_.arc RECID=5203 STAMP=902326725
validation succeeded for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_875_cbo4yy69_.arc RECID=5204 STAMP=902326750
validation succeeded for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_876_cbo5z4lz_.arc RECID=5207 STAMP=902327780
Crosschecked 3 objects

validation failed for archived log
archived log file name=/oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_877_cbog0qbg_.arc RECID=5209 STAMP=902336024
Crosschecked 1 objects
RMAN> list expired archivelog all;
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
5209    1    877     X 2016-JAN-28 14:36:20
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_28/o1_mf_1_877_cbog0qbg_.arc

RMAN> list backup of archivelog sequence 877;
specification does not match any backup in the repository

RMAN>

Checksum entire directory using md5sum

Wed, 2016-01-27 17:52

When you are backing up 3.5+ TB database, resulting in 600+ GB backupset, transferring to new DC, you would want to ensure there are no corruptions resulting from transfer.

Here is an example of how to perform checkum using md5sum for all the contents in the directory.

Taking the process further, split the .md5 file into multiple files for parallel processing.

oracle@arrow:hawklas:/oradata/keep
$ md5sum *MIG* > backup.md5


oracle@arrow:hawklas:/oradata/keep
$ cat backup.md5
080adb9ba716f3bf6f91efb06adc311e  HAWK_3130551611_20160127_hfqsdvfv_1_1_DB_MIG_1583_1
36bd695b6d87b25d153edc91ee79992f  HAWK_3130551611_20160127_hfqsdvfv_2_1_DB_MIG_1583_2
730c32ef5415bc9dd931d026fb42bcd7  HAWK_3130551611_20160127_hgqsdvg0_1_1_DB_MIG_1584_1
36fd7d0098bd628921ac789155f0a712  HAWK_3130551611_20160127_hgqsdvg0_2_1_DB_MIG_1584_2
072f757dfb808c96aef92555f80165eb  HAWK_3130551611_20160127_hgqsdvg0_3_1_DB_MIG_1584_3
767e06a7eea3cb72243d180a5591e2a2  HAWK_3130551611_20160127_hgqsdvg0_4_1_DB_MIG_1584_4
600ef7710995dcb1d11c0b27a6ee9971  HAWK_3130551611_20160127_hgqsdvg0_5_1_DB_MIG_1584_5
28c279a24cf47fb53b1f7840b7df7fc5  HAWK_3130551611_20160127_hgqsdvg0_6_1_DB_MIG_1584_6
e418ac58da6629f6aeec9c9f7bc47ca5  HAWK_3130551611_20160127_hgqsdvg0_7_1_DB_MIG_1584_7
3e6d8788ec1b5c2071b435c10b34b746  HAWK_3130551611_20160127_hgqsdvg0_8_1_DB_MIG_1584_8
5b1c964eabcc8bd602f6cef15482820a  HAWK_3130551611_20160127_hgqsdvg0_9_1_DB_MIG_1584_9
4655bac6d066ff47799ef8dcf423f532  HAWK_3130551611_20160127_hhqsdvg6_1_1_DB_MIG_1585_1
da4add20652a16726006f46a294cf90a  HAWK_3130551611_20160127_hhqsdvg6_2_1_DB_MIG_1585_2
a2ce4073fb16336c2f8a3cf78f1709ec  HAWK_3130551611_20160127_hhqsdvg6_3_1_DB_MIG_1585_3
301ef428887aba61aa33410d6c8b3c70  HAWK_3130551611_20160127_hhqsdvg6_4_1_DB_MIG_1585_4
47379e228a839bb4257aa141dbfd5107  HAWK_3130551611_20160127_hiqsdvgm_1_1_DB_MIG_1586_1
84e2cd2931f7272832b3c4cd3923e69d  HAWK_3130551611_20160127_hiqsdvgm_2_1_DB_MIG_1586_2
33da63364865b3b959491545905fdc9f  HAWK_3130551611_20160127_hiqsdvgm_3_1_DB_MIG_1586_3
06e04d3e05aff26a197621964fcb8617  HAWK_3130551611_20160127_hiqsdvgm_4_1_DB_MIG_1586_4
5436a855b3d287f9b6ed87ba07cefeb7  HAWK_3130551611_20160127_hiqsdvgm_5_1_DB_MIG_1586_5
0bc71d9930bf2653b6c8661dbf388989  HAWK_3130551611_20160127_hjqsdvh9_1_1_DB_MIG_1587_1
fa2447d3842b476ed365ef37c74db7d9  HAWK_3130551611_20160127_hkqsdvhd_1_1_DB_MIG_1588_1
beb4c1726c99335dff262224828925f5  HAWK_3130551611_20160127_hlqsdvhf_1_1_DB_MIG_1589_1
93aede1cc96dc286ff6c7d927d9505af  HAWK_3130551611_20160127_hmqsdvhh_1_1_DB_MIG_1590_1
eb0a7eb4cab45e5c9b053abb7c736f0d  HAWK_3130551611_20160127_hnqsdvhu_1_1_AL_MIG_1591_1
77808e8a0cb4ac766e7e4d868c7c81b4  HAWK_3130551611_20160127_hoqsdvhu_1_1_AL_MIG_1592_1
5994fe2cf07786495f99d2fd9f42b1f8  HAWK_3130551611_20160127_hpqsdvi5_1_1_AL_MIG_1593_1
784be893cd558cecf65aa51ba79b3e04  HAWK_3130551611_20160127_hpqsdvi5_2_1_AL_MIG_1593_2
5859c37d98d77174675f89e7590ed597  HAWK_3130551611_20160127_hqqsdvi6_1_1_AL_MIG_1594_1
6f2a8a68ab0e9847f8f2248de55cb51a  HAWK_3130551611_20160127_hrqsdvi9_1_1_AL_MIG_1595_1
446976ee788754e8cb48fb00a0acec92  HAWK_3130551611_20160127_hsqsdvid_1_1_AL_MIG_1596_1
73a488bc5aa0664fd80237a2b8da5ea8  HAWK_3130551611_20160127_htqsdvig_1_1_AL_MIG_1597_1
c0200bb23218859fb6a1edc3f7cba94d  HAWK_3130551611_20160127_huqsdvii_1_1_AL_MIG_1598_1
802c8f1524b7c6405d4d41a3b64f0a47  HAWK_3130551611_20160127_hvqsdvil_1_1_AL_MIG_1599_1
acf0c5b5ca6a3f9b58e7880eb093330a  HAWK_3130551611_20160127_i0qsdvil_1_1_AL_MIG_1600_1
dd1746fbaf90b1d867300286e297d2b3  HAWK_3130551611_20160127_i1qsdvin_1_1_AL_MIG_1601_1
7bb58056cac524304a88ba95a6837ac8  HAWK_3130551611_20160127_i2qsdvin_1_1_AL_MIG_1602_1
81ea52aadb6767ac3d3e2ae33acc9d64  HAWK_3130551611_20160127_i3qsdvio_1_1_AL_MIG_1603_1
8481443992c6858edbc03a481e13f579  HAWK_3130551611_20160127_i4qsdvip_1_1_AL_MIG_1604_1
539716837bd98835cf9b43b83cb60b79  HAWK_3130551611_20160127_i5qsdvj0_1_1_CF_MIG_1605_1
d2715ac45c5aa1e7dcd4c706c0a542ee  HAWK_3130551611_20160127_i6qsdvj5_1_1_CF_MIG_1606_1
9532408727adfd012728f989dd9a41ad  HAWK_3130551611_20160127_i7qsdvj8_1_1_CF_MIG_1607_1
840cd94839941643ecd1cbacc568ff9c  HAWK_3130551611_20160127_i8qsdvja_1_1_CF_MIG_1608_1


oracle@arrow:hawklas:/oradata/keep
$ md5sum -c -w backup.md5
HAWK_3130551611_20160127_hfqsdvfv_1_1_DB_MIG_1583_1: OK
HAWK_3130551611_20160127_hfqsdvfv_2_1_DB_MIG_1583_2: OK
HAWK_3130551611_20160127_hgqsdvg0_1_1_DB_MIG_1584_1: OK
HAWK_3130551611_20160127_hgqsdvg0_2_1_DB_MIG_1584_2: OK
HAWK_3130551611_20160127_hgqsdvg0_3_1_DB_MIG_1584_3: OK
HAWK_3130551611_20160127_hgqsdvg0_4_1_DB_MIG_1584_4: OK
HAWK_3130551611_20160127_hgqsdvg0_5_1_DB_MIG_1584_5: OK
HAWK_3130551611_20160127_hgqsdvg0_6_1_DB_MIG_1584_6: OK
HAWK_3130551611_20160127_hgqsdvg0_7_1_DB_MIG_1584_7: OK
HAWK_3130551611_20160127_hgqsdvg0_8_1_DB_MIG_1584_8: OK
HAWK_3130551611_20160127_hgqsdvg0_9_1_DB_MIG_1584_9: OK
HAWK_3130551611_20160127_hhqsdvg6_1_1_DB_MIG_1585_1: OK
HAWK_3130551611_20160127_hhqsdvg6_2_1_DB_MIG_1585_2: OK
HAWK_3130551611_20160127_hhqsdvg6_3_1_DB_MIG_1585_3: OK
HAWK_3130551611_20160127_hhqsdvg6_4_1_DB_MIG_1585_4: OK
HAWK_3130551611_20160127_hiqsdvgm_1_1_DB_MIG_1586_1: OK
HAWK_3130551611_20160127_hiqsdvgm_2_1_DB_MIG_1586_2: OK
HAWK_3130551611_20160127_hiqsdvgm_3_1_DB_MIG_1586_3: OK
HAWK_3130551611_20160127_hiqsdvgm_4_1_DB_MIG_1586_4: OK
HAWK_3130551611_20160127_hiqsdvgm_5_1_DB_MIG_1586_5: OK
HAWK_3130551611_20160127_hjqsdvh9_1_1_DB_MIG_1587_1: OK
HAWK_3130551611_20160127_hkqsdvhd_1_1_DB_MIG_1588_1: OK
HAWK_3130551611_20160127_hlqsdvhf_1_1_DB_MIG_1589_1: OK
HAWK_3130551611_20160127_hmqsdvhh_1_1_DB_MIG_1590_1: OK
HAWK_3130551611_20160127_hnqsdvhu_1_1_AL_MIG_1591_1: OK
HAWK_3130551611_20160127_hoqsdvhu_1_1_AL_MIG_1592_1: OK
HAWK_3130551611_20160127_hpqsdvi5_1_1_AL_MIG_1593_1: OK
HAWK_3130551611_20160127_hpqsdvi5_2_1_AL_MIG_1593_2: OK
HAWK_3130551611_20160127_hqqsdvi6_1_1_AL_MIG_1594_1: OK
HAWK_3130551611_20160127_hrqsdvi9_1_1_AL_MIG_1595_1: OK
HAWK_3130551611_20160127_hsqsdvid_1_1_AL_MIG_1596_1: OK
HAWK_3130551611_20160127_htqsdvig_1_1_AL_MIG_1597_1: OK
HAWK_3130551611_20160127_huqsdvii_1_1_AL_MIG_1598_1: OK
HAWK_3130551611_20160127_hvqsdvil_1_1_AL_MIG_1599_1: OK
HAWK_3130551611_20160127_i0qsdvil_1_1_AL_MIG_1600_1: OK
HAWK_3130551611_20160127_i1qsdvin_1_1_AL_MIG_1601_1: OK
HAWK_3130551611_20160127_i2qsdvin_1_1_AL_MIG_1602_1: OK
HAWK_3130551611_20160127_i3qsdvio_1_1_AL_MIG_1603_1: OK
HAWK_3130551611_20160127_i4qsdvip_1_1_AL_MIG_1604_1: OK
HAWK_3130551611_20160127_i5qsdvj0_1_1_CF_MIG_1605_1: OK
HAWK_3130551611_20160127_i6qsdvj5_1_1_CF_MIG_1606_1: OK
HAWK_3130551611_20160127_i7qsdvj8_1_1_CF_MIG_1607_1: OK
HAWK_3130551611_20160127_i8qsdvja_1_1_CF_MIG_1608_1: OK

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5

oracle@arrow:hawklas:/oradata/keep
$ vi backup.md5  -- create false error by modifying checksum.

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5
HAWK_3130551611_20160127_hfqsdvfv_1_1_DB_MIG_1583_1: FAILED
md5sum: WARNING: 1 of 43 computed checksums did NOT match
oracle@arrow:hawklas:/oradata/keep
$
oracle@arrow:hawklas:/oradata/keep
$ ls *MIG*|wc -l
43

oracle@arrow:hawklas:/oradata/keep
$ split -l 10 backup.md5 backup.md5 -- split file to contain 10 checksums per file.

oracle@arrow:hawklas:/oradata/keep
$ ll ba*
-rw-r--r--. 1 oracle oinstall 3698 Jan 27 12:52 backup.md5
-rw-r--r--. 1 oracle oinstall  860 Jan 27 12:56 backup.md5aa
-rw-r--r--. 1 oracle oinstall  860 Jan 27 12:56 backup.md5ab
-rw-r--r--. 1 oracle oinstall  860 Jan 27 12:56 backup.md5ac
-rw-r--r--. 1 oracle oinstall  860 Jan 27 12:56 backup.md5ad
-rw-r--r--. 1 oracle oinstall  258 Jan 27 12:56 backup.md5ae

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5aa
HAWK_3130551611_20160127_hfqsdvfv_1_1_DB_MIG_1583_1: FAILED
md5sum: WARNING: 1 of 10 computed checksums did NOT match

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5ab

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5ac

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5ad

oracle@arrow:hawklas:/oradata/keep
$ md5sum -c --quiet backup.md5ae

Why use KEEP backup?

Tue, 2016-01-26 19:30

Question which may have been asked.

For 1 time backup to migrate/clone database, KEEP backup does not affect retention policy and not backed up 1 times clause.

Imagine the consequences for deleting 1 off backup which has archivelog backup and the main scripts use not backed up 1-2 times clause which could mean no archivelog for subsequent backups.

RMAN> list archivelog all;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name HAWKLAS
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
5067    1    806     A 2016-JAN-21 07:45:48
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_21/o1_mf_1_806_cb1zcp8y_.arc

5068    1    807     A 2016-JAN-21 07:47:01
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_21/o1_mf_1_807_cb1zd4ns_.arc

5070    1    808     A 2016-JAN-21 07:47:16
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_22/o1_mf_1_808_cb4tyo1y_.arc

5071    1    809     A 2016-JAN-22 09:50:11
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_22/o1_mf_1_809_cb4tytsp_.arc

5073    1    810     A 2016-JAN-22 09:50:18
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_26/o1_mf_1_810_cbh5st68_.arc

5072    1    811     A 2016-JAN-26 07:56:40
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_26/o1_mf_1_811_cbh5sz08_.arc

5074    1    812     A 2016-JAN-26 07:56:45
        Name: /oradata/fra/HAWKLAS/archivelog/2016_01_26/o1_mf_1_812_cbh7mc9h_.arc


RMAN> backup archivelog sequence 806 KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' tag ARC_KEEP
2> ;

Starting backup at 2016-JAN-26 17:19:57
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=148 device type=DISK
backup will be obsolete on date 2016-FEB-26 17:19:57
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=806 RECID=5067 STAMP=901698422
channel ORA_DISK_1: starting piece 1 at 2016-JAN-26 17:19:58
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/26/2016 17:19:59
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes

RMAN> backup archivelog sequence 806 format '/tmp/U%' KEEP UNTIL TIME 'ADD_MONTHS(SYSDATE,1)' tag ARC_KEEP;

Starting backup at 2016-JAN-26 17:20:26
using channel ORA_DISK_1
backup will be obsolete on date 2016-FEB-26 17:20:26
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=806 RECID=5067 STAMP=901698422
channel ORA_DISK_1: starting piece 1 at 2016-JAN-26 17:20:26
channel ORA_DISK_1: finished piece 1 at 2016-JAN-26 17:20:27
piece handle=/tmp/U% tag=ARC_KEEP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-JAN-26 17:20:27

RMAN> backup archivelog sequence 806 not backed up 1 times tag ARC_BKUP;

Starting backup at 2016-JAN-26 17:21:02
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=806 RECID=5067 STAMP=901698422
channel ORA_DISK_1: starting piece 1 at 2016-JAN-26 17:21:02
channel ORA_DISK_1: finished piece 1 at 2016-JAN-26 17:21:03
piece handle=/oradata/fra/HAWKLAS/backupset/2016_01_26/o1_mf_annnn_ARC_BKUP_cbj6vyl7_.bkp tag=ARC_BKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-JAN-26 17:21:03

Starting Control File and SPFILE Autobackup at 2016-JAN-26 17:21:04
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/HAWK_c-3130551611-20160126-02 comment=NONE
Finished Control File and SPFILE Autobackup at 2016-JAN-26 17:21:07

RMAN> backup archivelog sequence 807 tag ARC_BACKUP;

Starting backup at 2016-JAN-26 17:21:30
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=807 RECID=5068 STAMP=901698436
channel ORA_DISK_1: starting piece 1 at 2016-JAN-26 17:21:30
channel ORA_DISK_1: finished piece 1 at 2016-JAN-26 17:21:31
piece handle=/oradata/fra/HAWKLAS/backupset/2016_01_26/o1_mf_annnn_ARC_BACKUP_cbj6wt9y_.bkp tag=ARC_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-JAN-26 17:21:31

Starting Control File and SPFILE Autobackup at 2016-JAN-26 17:21:31
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/HAWK_c-3130551611-20160126-03 comment=NONE
Finished Control File and SPFILE Autobackup at 2016-JAN-26 17:21:34

RMAN> backup archivelog sequence 807 not backed up 1 times;

Starting backup at 2016-JAN-26 17:21:43
using channel ORA_DISK_1
skipping archived log of thread 1 with sequence 807; already backed up
Finished backup at 2016-JAN-26 17:21:43

RMAN> list backup of archivelog all summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
1360    B  A  A DISK        2016-JAN-26 17:20:27 1       1       NO         ARC_KEEP
1361    B  A  A DISK        2016-JAN-26 17:21:02 1       1       NO         ARC_BKUP
1363    B  A  A DISK        2016-JAN-26 17:21:30 1       1       NO         ARC_BACKUP

RMAN> list backup of archivelog sequence 806;


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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
1360    27.00K     DISK        00:00:01     2016-JAN-26 17:20:27
        BP Key: 1505   Status: AVAILABLE  Compressed: NO  Tag: ARC_KEEP
        Piece Name: /tmp/U%

  List of Archived Logs in backup set 1360
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    806     6645495    2016-JAN-21 07:45:48 6645595    2016-JAN-21 07:47:01

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
1361    27.00K     DISK        00:00:00     2016-JAN-26 17:21:02
        BP Key: 1506   Status: AVAILABLE  Compressed: NO  Tag: ARC_BKUP
        Piece Name: /oradata/fra/HAWKLAS/backupset/2016_01_26/o1_mf_annnn_ARC_BKUP_cbj6vyl7_.bkp

  List of Archived Logs in backup set 1361
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    806     6645495    2016-JAN-21 07:45:48 6645595    2016-JAN-21 07:47:01

RMAN> list backup of archivelog sequence 806 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
1360    B  A  A DISK        2016-JAN-26 17:20:27 1       1       NO         ARC_KEEP
1361    B  A  A DISK        2016-JAN-26 17:21:02 1       1       NO         ARC_BKUP

RMAN>

OOPS! Did you see my errors?