Michael Dinh

Subscribe to Michael Dinh feed Michael Dinh
Michael T. Dinh, Oracle DBA
Updated: 11 hours 44 min ago

Scheduler Jobs Do Not Run Automatically

Thu, 2017-09-28 21:09

After you have followed – IF: Jobs Do Not Run Automatically (Doc ID 2084527.1) – without any success,
then check to see if services have been created and are running.

RAC DB is 12.1.0.2.0 and was cloned from standby.

It just so happens, service as defined from the SQL below was not created:

select c.SERVICE
from dba_scheduler_jobs j, dba_scheduler_job_classes c
where j.JOB_CLASS=c.JOB_CLASS_NAME
and j.JOB_NAME=UPPER('&jobname')
;

To be honest, I was not able to find the issue and team mate did.

What I found very, very strange is manually running the job using exec dbms_scheduler.run_job is successful.

The manual job ran successfully without the service created and on the wrong node for where the service is defined
(the service is defined to run on node 2, while the manual run is from node 1).

Another unsolved mystery.


Dedicated Network for DataGuard

Tue, 2017-09-26 07:50

Just some notes.

Using a separate network for DataGuard in 12c RAC
https://dbamarco.wordpress.com/2015/04/15/using-a-separate-network-for-dataguard-in-12c-rac/

PUTTING DATA GUARD TRAFFIC ON DEDICATED NETWORK INTERFACE
https://ilmarkerm.eu/blog/2015/07/putting-data-guard-traffic-on-dedicated-network-interface/

How configure Multiples Public Network in a Grid Infrastructure 11g R2 (11.2) environment
https://levipereira.wordpress.com/2011/10/22/how-configure-multiples-public-network-an-grid-infrastructure-11g-r2-11-2-environment/


Grid Infrastructure 12.2.0.1 CRS_SWONLY Silent Install

Sat, 2017-09-23 18:00

Configuring Software Binaries for Oracle Grid Infrastructure for a Standalone Server

CREATE DIRECTORIES:

[root@arrow1 ~]# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),994(vboxsf)

[root@arrow1 ~]# mkdir /u01
[root@arrow1 ~]# chmod 775 /u01/
[root@arrow1 ~]# chown oracle:oinstall /u01/

[root@arrow1 ~]# vi /etc/oraInst.loc
[root@arrow1 ~]# chown oracle:oinstall /etc/oraInst.loc

[root@arrow1 ~]# cat /etc/oraInst.loc
inventory_loc=/u01/app/oraInventory
inst_group=oinstall

[root@arrow1 ~]# ll /etc/oraInst.loc
-rw-r--r-- 1 oracle oinstall 56 Sep 23 17:11 /etc/oraInst.loc
[root@arrow1 ~]# exit

[oracle@arrow1 ~]$ mkdir -p /u01/app/12.2.0.1/grid
[oracle@arrow1 ~]$ mkdir -p /u01/app/grid
[oracle@arrow1 ~]$ mkdir -p /u01/app/oraInventory

EXTRACT SOFTWARE DIRECTLY TO GRID HOME:

[oracle@arrow1 ~]$ ls -l /sf_OracleSoftware/12.2.0.1_Linux_64/
total 6297251
-rwxrwxrwx 1 vagrant vagrant 3453696911 Apr 12 09:44 linuxx64_12201_database.zip
-rwxrwxrwx 1 vagrant vagrant 2994687209 Apr 12 09:43 linuxx64_12201_grid_home.zip

[oracle@arrow1 ~]$ unzip -d /u01/app/12.2.0.1/grid -qo /sf_OracleSoftware/12.2.0.1_Linux_64/linuxx64_12201_grid_home.zip; echo $?
0

[oracle@arrow1 ~]$ ls /u01/app/12.2.0.1/grid
addnode     crs     deinstall    gpnp           inventory  lib      opmn     oui      qos       rootupgrade.sh  srvm      welcome.html
assistants  css     demo         gridSetup.sh   javavm     log      oracore  owm      racg      runcluvfy.sh    suptools  wlm
bin         cv      diagnostics  has            jdbc       md       ord      perl     rdbms     scheduler       tomcat    wwg
cdata       dbjava  dmu          hs             jdk        network  ordim    plsql    relnotes  slax            ucp       xag
cha         dbs     env.ora      install        jlib       nls      ords     precomp  rhp       sqlpatch        usm       xdk
clone       dc_ocm  evm          instantclient  ldap       OPatch   oss      QOpatch  root.sh   sqlplus         utl

[oracle@arrow1 ~]$ ls /u01/app/12.2.0.1/grid/install/response/
grid_2017-01-26_04-10-28PM.rsp  gridsetup.rsp  sample.ccf
[oracle@arrow1 ~]$

CREATE RESPONSE FILE FOR CRS_SWONLY – WITHOUT ASM:

oracle@arrow1::/u01/app/12.2.0.1/grid/install/response
$ cp -v gridsetup.rsp grid_crs_swonly.rsp
‘gridsetup.rsp’ -> ‘grid_crs_swonly.rsp’

oracle@arrow1::/u01/app/12.2.0.1/grid/install/response
$ diff -iwyB --suppress-common-lines -W 150 gridsetup.rsp grid_crs_swonly.rsp; echo
oracle.install.option=                                                    |     oracle.install.option=CRS_SWONLY
ORACLE_BASE=                                                              |     ORACLE_BASE=/u01/app/grid
oracle.install.asm.OSDBA=                                                 |     oracle.install.asm.OSDBA=dba
oracle.install.asm.OSOPER=                                                |     oracle.install.asm.OSOPER=dba
oracle.install.asm.OSASM=                                                 |     oracle.install.asm.OSASM=dba
oracle@arrow1::/u01/app/12.2.0.1/grid/install/response

RUNCLUVFY FOR HACFG:

oracle@arrow1::/u01/app/12.2.0.1/grid
$ ./runcluvfy.sh stage -pre hacfg

Verifying Physical Memory ...FAILED (PRVF-7530)
Verifying Available Physical Memory ...PASSED
Verifying Swap Size ...PASSED
Verifying Free Space: arrow1:/usr,arrow1:/var,arrow1:/etc,arrow1:/sbin,arrow1:/tmp ...PASSED
Verifying User Existence: oracle ...
  Verifying Users With Same UID: 54321 ...PASSED
Verifying User Existence: oracle ...PASSED
Verifying Group Existence: dba ...PASSED
Verifying Group Existence: oinstall ...PASSED
Verifying Group Membership: oinstall(Primary) ...PASSED
Verifying Group Membership: dba ...PASSED
Verifying Run Level ...PASSED
Verifying Hard Limit: maximum open file descriptors ...PASSED
Verifying Soft Limit: maximum open file descriptors ...PASSED
Verifying Hard Limit: maximum user processes ...PASSED
Verifying Soft Limit: maximum user processes ...PASSED
Verifying Soft Limit: maximum stack size ...PASSED
Verifying Architecture ...PASSED
Verifying OS Kernel Version ...PASSED
Verifying OS Kernel Parameter: semmsl ...PASSED
Verifying OS Kernel Parameter: semmns ...PASSED
Verifying OS Kernel Parameter: semopm ...PASSED
Verifying OS Kernel Parameter: semmni ...PASSED
Verifying OS Kernel Parameter: shmmax ...PASSED
Verifying OS Kernel Parameter: shmmni ...PASSED
Verifying OS Kernel Parameter: shmall ...PASSED
Verifying OS Kernel Parameter: file-max ...PASSED
Verifying OS Kernel Parameter: ip_local_port_range ...PASSED
Verifying OS Kernel Parameter: rmem_default ...PASSED
Verifying OS Kernel Parameter: rmem_max ...PASSED
Verifying OS Kernel Parameter: wmem_default ...PASSED
Verifying OS Kernel Parameter: wmem_max ...PASSED
Verifying OS Kernel Parameter: aio-max-nr ...PASSED
Verifying OS Kernel Parameter: panic_on_oops ...PASSED
Verifying Package: binutils-2.23.52.0.1 ...PASSED
Verifying Package: compat-libcap1-1.10 ...PASSED
Verifying Package: libgcc-4.8.2 (x86_64) ...PASSED
Verifying Package: libstdc++-4.8.2 (x86_64) ...PASSED
Verifying Package: libstdc++-devel-4.8.2 (x86_64) ...PASSED
Verifying Package: sysstat-10.1.5 ...PASSED
Verifying Package: ksh ...PASSED
Verifying Package: make-3.82 ...PASSED
Verifying Package: glibc-2.17 (x86_64) ...PASSED
Verifying Package: glibc-devel-2.17 (x86_64) ...PASSED
Verifying Package: libaio-0.3.109 (x86_64) ...PASSED
Verifying Package: libaio-devel-0.3.109 (x86_64) ...PASSED
Verifying Package: nfs-utils-1.2.3-15 ...PASSED
Verifying Package: smartmontools-6.2-4 ...PASSED
Verifying Package: net-tools-2.0-0.17 ...PASSED
Verifying Users With Same UID: 0 ...PASSED
Verifying Current Group ID ...PASSED
Verifying Root user consistency ...PASSED

Pre-check for Oracle Restart configuration was unsuccessful.


Failures were encountered during execution of CVU verification request "stage -pre hacfg".

Verifying Physical Memory ...FAILED
arrow1: PRVF-7530 : Sufficient physical memory is not available on node
        "arrow1" [Required physical memory = 8GB (8388608.0KB)]


CVU operation performed:      stage -pre hacfg
Date:                         Sep 23, 2017 5:22:21 PM
CVU home:                     /u01/app/12.2.0.1/grid/
User:                         oracle
oracle@arrow1::/u01/app/12.2.0.1/grid
$

INSTALL GRID USING gridSetup.sh

oracle@arrow1::/u01/app/12.2.0.1/grid
$ ./gridSetup.sh -silent -waitforcompletion -skipPrereqs -responseFile /u01/app/12.2.0.1/grid/install/response/grid_crs_swonly.rsp
Launching Oracle Grid Infrastructure Setup Wizard...

[WARNING] [INS-41808] Possible invalid choice for OSASM Group.
   CAUSE: The name of the group you selected for the OSASM group is commonly used to grant other system privileges (For example: asmdba, asmoper, dba, oper).
   ACTION: Oracle recommends that you designate asmadmin as the OSASM group.
[WARNING] [INS-41809] Possible invalid choice for OSDBA Group.
   CAUSE: The group name you selected as the OSDBA for ASM group is commonly used for Oracle Database administrator privileges.
   ACTION: Oracle recommends that you designate asmdba as the OSDBA for ASM group, and that the group should not be the same group as an Oracle Database OSDBA group.
[WARNING] [INS-41810] Possible invalid choice for OSOPER Group.
   CAUSE: The group name you selected as the OSOPER for ASM group is commonly used for Oracle Database administrator privileges.
   ACTION: Oracle recommends that you designate asmoper as the OSOPER for ASM group, and that the group should not be the same group as an Oracle Database OSOPER group.
[WARNING] [INS-41813] OSDBA for ASM, OSOPER for ASM, and OSASM are the same OS group.
   CAUSE: The group you selected for granting the OSDBA for ASM group for database access, and the OSOPER for ASM group for startup and shutdown of Oracle ASM, is the same group as the OSASM group, whose members have SYSASM privileges on Oracle ASM.
   ACTION: Choose different groups as the OSASM, OSDBA for ASM, and OSOPER for ASM groups.
You can find the log of this install session at:
 /u01/app/oraInventory/logs/GridSetupActions2017-09-23_05-52-14PM/gridSetupActions2017-09-23_05-52-14PM.log

As a root user, execute the following script(s):
        1. /u01/app/12.2.0.1/grid/root.sh

Execute /u01/app/12.2.0.1/grid/root.sh on the following nodes:
[arrow1]


Successfully Setup Software.
oracle@arrow1::/u01/app/12.2.0.1/grid
$

RUN root.sh

[root@arrow1 ~]# /u01/app/12.2.0.1/grid/root.sh
Check /u01/app/12.2.0.1/grid/install/root_arrow1_2017-09-23_17-54-24-226719765.log for the output of root script

[root@arrow1 ~]# cat /u01/app/12.2.0.1/grid/install/root_arrow1_2017-09-23_17-54-24-226719765.log
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/12.2.0.1/grid
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

To configure Grid Infrastructure for a Cluster or Grid Infrastructure for a Stand-Alone Server execute the following command as oracle user:
/u01/app/12.2.0.1/grid/gridSetup.sh
This command launches the Grid Infrastructure Setup Wizard. The wizard also supports silent operation, and the parameters can be passed through the response file that is available in the installation media.

[root@arrow1 ~]#

This is where Oracle has take a turn for the worse.
Instructions are not provided after running root.sh and are incorrect!

AS ROOT RUN roothas.pl

[root@arrow1 ~]# /u01/app/12.2.0.1/grid/perl/bin/perl -I /u01/app/12.2.0.1/grid/perl/lib -I /u01/app/12.2.0.1/grid/crs/install /u01/app/12.2.0.1/grid/crs/install/roothas.pl
Using configuration parameter file: /u01/app/12.2.0.1/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/arrow1/crsconfig/roothas_2017-09-23_06-11-34PM.log
2017/09/23 18:11:35 CLSRSC-363: User ignored prerequisites during installation
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node arrow1 successfully pinned.
2017/09/23 18:11:44 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'arrow1'
CRS-2673: Attempting to stop 'ora.evmd' on 'arrow1'
CRS-2677: Stop of 'ora.evmd' on 'arrow1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'arrow1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.

arrow1     2017/09/23 18:12:54     /u01/app/12.2.0.1/grid/cdata/arrow1/backup_20170923_181254.olr     0
2017/09/23 18:12:54 CLSRSC-327: Successfully configured Oracle Restart for a standalone server
[root@arrow1 ~]#

AS ORACLE runInstaller -updateNodeList

oracle@arrow1::/u01/app/12.2.0.1/grid
$ cd oui/bin/

oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$ ./runInstaller -updateNodeList ORACLE_HOME=/u01/app/12.2.0.1/grid -defaultHomeName CLUSTER_NODES= CRS=TRUE
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4080 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'UpdateNodeList' was successful.
oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin

CHECK PROCESESS

oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$ ps -ef|grep oracle
root      3796  1125  0 17:03 ?        00:00:00 sshd: oracle [priv]
oracle    3798  3796  0 17:03 ?        00:00:01 sshd: oracle@pts/2
oracle    3799  3798  0 17:03 pts/2    00:00:00 -bash
root     13572  1125  0 17:39 ?        00:00:00 sshd: oracle [priv]
oracle   13574 13572  0 17:39 ?        00:00:00 sshd: oracle@pts/0
oracle   13575 13574  0 17:39 pts/0    00:00:00 -bash
oracle   25084     1  0 18:12 ?        00:00:03 /u01/app/12.2.0.1/grid/bin/ohasd.bin reboot
oracle   25197     1  0 18:12 ?        00:00:02 /u01/app/12.2.0.1/grid/bin/oraagent.bin
oracle   25215     1  0 18:12 ?        00:00:01 /u01/app/12.2.0.1/grid/bin/evmd.bin
oracle   25269 25215  0 18:12 ?        00:00:01 /u01/app/12.2.0.1/grid/bin/evmlogger.bin -o /u01/app/12.2.0.1/grid/log/[HOSTNAME]/evmd/evmlogger.info -l /u01/app/12.2.0.1/grid/log/[HOSTNAME]/evmd/evmlogger.log
oracle   25404  3799  0 18:27 pts/2    00:00:00 ps -ef
oracle   25405  3799  0 18:27 pts/2    00:00:00 grep --color=auto oracle
oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$

CHECK INSTALL LOG

oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$ export LOG=/u01/app/oraInventory/logs/GridSetupActions2017-09-23_05-52-14PM/gridSetupActions2017-09-23_05-52-14PM.log

oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$ grep -e '[[:upper:]]: ' $LOG |cut -d ":" -f1|sort -u
   ACTION
   CAUSE
INFO
WARNING

oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$ grep -e '[[:upper:]]: ' $LOG|grep "^WARNING: "
WARNING:  [Sep 23, 2017 5:52:18 PM] Unable to find the namespace URI. Reason: Start of root element expected.
WARNING:  [Sep 23, 2017 5:52:18 PM] Unable to find the namespace URI. Reason: Start of root element expected.
WARNING:  [Sep 23, 2017 5:52:21 PM] Validation disabled for the state init
WARNING:  [Sep 23, 2017 5:52:26 PM] Validation disabled for the state flowDecider
WARNING:  [Sep 23, 2017 5:52:26 PM] Validation disabled for the state CRSNodeInformationUI
WARNING:  [Sep 23, 2017 5:52:26 PM] No Local Domain found, setting to whatever first domain is available
WARNING:  [Sep 23, 2017 5:52:26 PM] No Domain found, skipping Same Domain validation
WARNING:  [Sep 23, 2017 5:52:27 PM] [WARNING] [INS-41808] Possible invalid choice for OSASM Group.
WARNING:  [Sep 23, 2017 5:52:27 PM] [WARNING] [INS-41809] Possible invalid choice for OSDBA Group.
WARNING:  [Sep 23, 2017 5:52:27 PM] [WARNING] [INS-41810] Possible invalid choice for OSOPER Group.
WARNING:  [Sep 23, 2017 5:52:27 PM] [WARNING] [INS-41813] OSDBA for ASM, OSOPER for ASM, and OSASM are the same OS group.
WARNING:  [Sep 23, 2017 5:52:29 PM] Validation disabled for the state prereqExecutionDecider
WARNING:  [Sep 23, 2017 5:52:29 PM] Validation disabled for the state showSummary
WARNING:  [Sep 23, 2017 5:52:29 PM] Unable to find the namespace URI. Reason: /u01/app/12.2.0.1/grid/install/response/grid_2017-09-23_05-52-14PM.rsp (No such file or directory)
WARNING:  [Sep 23, 2017 5:53:50 PM] Validation disabled for the state finish
WARNING:  [Sep 23, 2017 5:53:50 PM] Could not create directory: /u01/app/oraInventory/logs/GridSetupActions2017-09-23_05-52-14PM
oracle@arrow1::/u01/app/12.2.0.1/grid/oui/bin
$

Bad Practice from Oracle – datapatch

Sat, 2017-09-23 08:18

Not too long ago, I discovered:
dbca does hard coding

Now it gets even worse.

Create TEST environment.

SOURCE ORACLE_HOME:/u01/app/oracle/product/12.1.0/db_1
TARGET ORACLE_HOME:/u01/app/oracle/product/12.1.0/db

DB was cloned from Source to Target.

datapatch failed due to different OH and hard coding from dba_directories as shown below.

SOURCE:
OPATCH_LOG_DIR /u01/app/oracle/product/12.1.0/db_1/QOpatch
OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.1.0/db_1/QOpatch
OPATCH_INST_DIR /u01/app/oracle/product/12.1.0/db_1/OPatch

TARGET:
OPATCH_LOG_DIR /u01/app/oracle/product/12.1.0/db/QOpatch
OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.1.0/db/QOpatch
OPATCH_INST_DIR /u01/app/oracle/product/12.1.0/db/OPatch

I don’t get it! Why not use relative versus absolute path?

You might ask, “Why is the target different!”

To be honest, in this specific case, it should not have been.

BUT there may be scenarios where OH is already used and new location, i.e. db_2, is required.

Looking forward to Oracle AI – sarcasm.

 


RMAN Backup from Standby w Recovery Catalog Part 2

Wed, 2017-09-20 18:15

RMAN Backup from Standby w Recovery Catalog

What! There’s a part 2?

FRA from primary was getting full since archivelog deletion was not working.

  1. Why is there a need to delete archivelog since FRA performs clean up?
  2. If FRA performs clean up then why is the destination full?
  3. Go to 1.

Reminds me of, “Who’s on first, What’s on second, I Don’t Know is on third”

Notice the old configuration.

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
Archivelog deletion.
connect target;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
list archivelog all;
delete noprompt archivelog until time 'sysdate-7/24';
list archivelog all;
Archivelog deleted.

archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31831.359.955164907 RECID=87186 STAMP=955164906
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31832.886.955166705 RECID=87189 STAMP=955166705
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31833.594.955168505 RECID=87191 STAMP=955168504
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31834.411.955170305 RECID=87193 STAMP=955170304
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31835.418.955172107 RECID=87195 STAMP=955172106
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31836.448.955173905 RECID=87197 STAMP=955173906
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31837.438.955175705 RECID=87199 STAMP=955175705
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31838.811.955176699 RECID=87201 STAMP=955176703
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31839.922.955178497 RECID=87203 STAMP=955178499
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31840.468.955180295 RECID=87205 STAMP=955180294
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31841.910.955182097 RECID=87207 STAMP=955182097
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31842.830.955183897 RECID=87209 STAMP=955183897
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31843.986.955185697 RECID=87211 STAMP=955185696
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31844.879.955186507 RECID=87212 STAMP=955186506
Deleted 14 objects
OH SH*T
RMAN> connect target;

connected to target database: QA (DBID=3476258591)

RMAN> connect catalog cat@rman

recovery catalog database Password:
connected to recovery catalog database

RMAN> list backup of archivelog from logseq=31831 until logseq=31844 thread=1 summary;

specification does not match any backup in the repository
Sign of JOY!
RMAN> list backup of archivelog from logseq=31831 until logseq=31844 thread=1 summary for db_unique_name all;


specification does not match any backup in the repository

List of Backups for database with db_unique_name QADR
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
812913  B  A  A DISK        2017-09-20:05:43:11 1       1       YES        DAILY_ARCHLOG_BKUP
812915  B  A  A DISK        2017-09-20:05:43:30 1       1       YES        DAILY_ARCHLOG_BKUP
813550  B  A  A DISK        2017-09-20:09:36:44 1       1       YES        DAILY_ARCHLOG_BKUP
813551  B  A  A DISK        2017-09-20:09:37:55 1       1       YES        DAILY_ARCHLOG_BKUP
813559  B  A  A DISK        2017-09-20:09:41:33 1       1       YES        DAILY_ARCHLOG_BKUP
813570  B  A  A DISK        2017-09-20:09:45:17 1       1       YES        DAILY_ARCHLOG_BKUP
814104  B  A  A DISK        2017-09-20:13:36:44 1       1       YES        DAILY_ARCHLOG_BKUP
814105  B  A  A DISK        2017-09-20:13:37:55 1       1       YES        DAILY_ARCHLOG_BKUP
814112  B  A  A DISK        2017-09-20:13:41:05 1       1       YES        DAILY_ARCHLOG_BKUP
814114  B  A  A DISK        2017-09-20:13:41:45 1       1       YES        DAILY_ARCHLOG_BKUP

RMAN> show all;

RMAN configuration parameters for database with db_unique_name QADR are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;

Wondering if
CONFIGURE DEFAULT DEVICE TYPE TO DISK
had anything to do with
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO ‘SBT_TAPE’ APPLIED ON ALL STANDBY
not working?

You might ask, why not just use “CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY” only.

That depends if you want accuracy or not.

Was the requirement backup to tape or to disk?

If the requirement is backup to tape, feel lucky FRA got full; otherwise, there’s a false pretense of good backup going to tape.

 


RMAN Backup from Standby w Recovery Catalog

Tue, 2017-09-19 20:05

Apologies as this is not a very clean post as there is too much info to disseminate.

Hopefully, the info may be useful at some point.

Try connect using connect target sys/password@tns vs connect connect target;

Otherwise, RMAN> resync catalog from db_unique_name all will fail or get the error below.

RMAN-06820: WARNING: failed to archive current log at primary database

When using RMAN recovery catalog, “from db_unique_name and for db_unique_name” are introduced.
Either specify the required db_unique_name or use ALL.

References:

RMAN-06613: Connect identifier for DB_UNIQUE_NAME not configured (Doc ID 1598653.1) 
List backup on Standby database returns no data in a Dataguard Configuration (Doc ID 1382885.1) 
ORA-17629 : RMAN Resync Catalog from db_unique_name all fails (Doc ID 1301769.1) 
RESYNC CATALOG FROM DB_UNIQUE_NAME ALL fails ORA-17629, ORA-17628 (Doc ID 1327156.1)

DEMO:

$ rman

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 18 15:26:30 2017

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

RMAN> connect target sys@qa

target database Password:
connected to target database: QA (DBID=147966131)

RMAN> connect catalog cat@rman

recovery catalog database Password:
connected to recovery catalog database

RMAN> resync catalog from db_unique_name all;


starting full resync of recovery catalog
full resync complete

resyncing from database with DB_UNIQUE_NAME QADR

RMAN> show all for db_unique_name all;


RMAN configuration parameters for database with db_unique_name QA are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qa/%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backups/rman/qa/%U' MAXPIECEZE 20 G;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' MAXPIECEZE 5 G;
CONFIGURE MAXSETZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESON ALGORITHM 'BAC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'QA' CONNECT IDENTIFIER  'QA';
CONFIGURE DB_UNIQUE_NAME 'QADR' CONNECT IDENTIFIER  'QADR';
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/11g/dbs/snapcf_qa.f'; # default

RMAN configuration parameters for database with db_unique_name QADR are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qadr/%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO BACKUPSET PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backups/rman/qadr/%U' MAXPIECEZE 32 G;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' MAXPIECEZE 32 G;
CONFIGURE MAXSETZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESON ALGORITHM 'BAC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'QA' CONNECT IDENTIFIER  'QA';
CONFIGURE DB_UNIQUE_NAME 'QADR' CONNECT IDENTIFIER  'QADR';
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/11g/dbs/snapcf_qa.f'; # default

RMAN> exit

ALL controlfile autobackup going to disk, will probably needs further investigation.

$ rman

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Sep 18 20:08:27 2017

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

RMAN> connect target;

connected to target database: QA (DBID=3476258591)

RMAN> connect catalog cat@rman

recovery catalog database Password:
connected to recovery catalog database

RMAN> list backup of controlfile summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
787503  B  F  A SBT_TAPE    2017-09-14:17:10:41 1       1       NO         TAG20170914T171041

RMAN> list backupset 787503;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
787503  Full    13.75M     SBT_TAPE    00:00:00     2017-09-14:17:10:41
        BP Key: 787508   Status: AVAILABLE  Compressed: NO  Tag: TAG20170914T171041
        Handle: c-3476258591-20170914-05   Media: 758343
  Control File Included: Ckp SCN: 9869799255   Ckp time: 2017-09-14:17:10:41
  SPFILE Included: Modification time: 2017-08-16:17:19:20

RMAN> list backup of controlfile summary for db_unique_name all;


List of Backups for database with db_unique_name QA
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
787503  B  F  A SBT_TAPE    2017-09-14:17:10:41 1       1       NO         TAG20170914T171041

List of Backups for database with db_unique_name QADR
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
795620  B  F  A DISK        2017-09-16:20:12:59 1       1       NO         TAG20170916T201259
796112  B  F  A DISK        2017-09-16:21:35:35 1       1       NO         TAG20170916T213535
796981  B  F  A DISK        2017-09-17:01:38:27 1       1       NO         TAG20170917T013827
798063  B  F  A DISK        2017-09-17:05:38:42 1       1       NO         TAG20170917T053841
799073  B  F  A DISK        2017-09-17:09:40:17 1       1       NO         TAG20170917T094016
800391  B  F  A DISK        2017-09-17:13:40:52 1       1       NO         TAG20170917T134052
801182  B  F  A DISK        2017-09-17:17:41:18 1       1       NO         TAG20170917T174118
801914  B  F  A DISK        2017-09-17:19:55:17 1       1       NO         TAG20170917T195517
802532  B  F  A DISK        2017-09-17:21:35:26 1       1       NO         TAG20170917T213526
803350  B  F  A DISK        2017-09-18:01:35:55 1       1       NO         TAG20170918T013555
804191  B  F  A DISK        2017-09-18:05:36:21 1       1       NO         TAG20170918T053620
805093  B  F  A DISK        2017-09-18:09:37:49 1       1       NO         TAG20170918T093749
805935  B  F  A DISK        2017-09-18:13:38:14 1       1       NO         TAG20170918T133814
807696  B  F  A DISK        2017-09-18:17:38:31 1       1       NO         TAG20170918T173831
808143  B  F  A DISK        2017-09-18:19:51:45 1       1       NO         TAG20170918T195145

RMAN> list backupset 808143;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 09/18/2017 20:10:47
RMAN-06160: no backup pieces found for backup set key: 808143

RMAN> list backupset 808143 for db_unique_name QADR;

List of Backup Set for database with db_unique_name QADR
===================

--- Notice %d is db_name and not db_unique_name which is disappointing

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
808143  Full    13.80M     DISK        00:00:00     2017-09-18:19:51:45
        BP Key: 808152   Status: AVAILABLE  Compressed: NO  Tag: TAG20170918T195145
        Piece Name: /backups/rman/qadr/QA_3476258591_c-3476258591-20170918-05.ctl
  Standby Control File Included: Ckp SCN: 9902230362   Ckp time: 2017-09-18:19:45:07
  SPFILE Included: Modification time: 2017-09-16:22:12:41

RMAN> exit

When configurations are the same for primary and standby, do this:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl' FOR DB_UNIQUE_NAME ALL

versus

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%d_%I_%F.ctl' FOR DB_UNIQUE_NAME 'QA';
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%d_%I_%F.ctl' FOR DB_UNIQUE_NAME 'QADR';
RMAN> show CONTROLFILE AUTOBACKUP FORMAT for db_unique_name all;

RMAN configuration parameters for database with db_unique_name QA are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qa/%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';

RMAN configuration parameters for database with db_unique_name QADR are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qadr/%d_%I_%F.ctl';

RMAN>

Thoughts on RMAN Backup Strategy Part 3

Mon, 2017-09-18 18:42

Thoughts on RMAN Backup Strategy Part 2

Thoughts on RMAN Backup Strategy

Linux Locking using flock

From cron:

00 03 * * 0          rman_db_backup.sh 0     > /tmp/rman_l0.log 2>&1
00 03 * * 1,2,3,4,5  rman_db_backup.sh 1     > /tmp/rman_l1.log 2>&1
30 * * * *           rman_archive_backup.sh  > /tmp/rman_arch.log 2>1

Notice from cron, DB backup starts at 3am and achivelog backup starts at 30m after the hour.

What happens when DB performs backup database plus archivelog delete input?

What happens when failed backup does not exits from shell script and continue with delete noprompt obsolete;?

Just a few thoughts:

  1. Use flock to prevent 2 backups for running at the same time.
  2. User error trapping to exit from shell script vs continuing to next step.
  3. Don’t delete archivelog from DB backup.
  4. Delete archivelog from ARC backup or schedule MW tasks.

 


Protection From Equifax Data Breach

Sun, 2017-09-17 15:00

Place fraud alert – this only needs to be done from one credit bureau and it will be forwared to all others.

To be safe, I have sucessfully placed fraud alert for TransUnion and Equifax. Was not successful with Experian.

Place credit freeze for Equifax only. Freezing all 3 bureaus will be a pain in the arse for when establishing credit.

Should the business use Equifax exclusively, then there’s a choice to unfreze credit from Equifax or take the business elsewhere.

Preference would be take business elsewhere. At this moment, credit freeze at Equifax is planned to be for life.

After placing fraud alert, I got alerted from CreditWise, a free basic credit monitoring from Capital One credit card.

Heads up: there was some activity on your TransUnion credit report.

Visit the Alerts section of CreditWise® from Capital One® to review your recent TransUnion credit report change.
To protect your privacy, we aren’t including any confidential details here.
Sign in soon—staying on top of your credit can help you defend yourself against potential fraud or identity theft.

ProtectMyID Essential, Exclusively for AAA Members – covers credit monitoring from Experian

https://www.annualcreditreport.com/
Get a free copy of your credit report every 12 months from each credit reporting company.

Every 4 month, get free credit report from 1 credit reporting company.

It’s not fool proof, but better than nothing.

At this point, looks like all bases are covered.

Good Luck to you.


Thoughts on RMAN Backup Strategy Part 2

Sun, 2017-09-17 09:33

Cron backup schedule.

00 03 * * 0          rman_db_backup.sh 0     > /tmp/rman_l0.log 2>&1
00 03 * * 1,2,3,4,5  rman_db_backup.sh 1     > /tmp/rman_l1.log 2>&1
30 * * * *           rman_archive_backup.sh  > /tmp/rman_arch.log 2>&1

FAILED: Archive RMAN backup.
It’s the dreaded archivelog does not exist.
We can simply put crosscheck archivelog all, delete noprompt expired archivelog all, and this should fix the problem, right?

WRONG! Don’t do this. I know there will be disagreement and this is my opinion.

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file +FRA/ARCHIVELOG/2017_09_10/thread_1_seq_786844.649.954296727
ORA-17503: ksfdopn:2 Failed to open file +FRA/ARCHIVELOG/2017_09_10/thread_1_seq_786844.649.954296727
ORA-15012: ASM file '+FRA/ARCHIVELOG/2017_09_10/thread_1_seq_786844.649.954296727' does not exist

Scripts did have crosscheck and I have commented out prior because error was being ignored since next backup was successful.

$ grep crosscheck *.sh
rman_archive_backup.sh:#crosscheck archivelog all;
rman_db_backup.sh:#crosscheck archivelog all;

The error happened again and next backup was successful. Why?

Archivelog backup does not exists.

RMAN> list backup of archivelog from logseq=786840 until logseq=786850 thread=1;

specification does not match any backup in the repository

RMAN> 

Archived log shown as backed up and deleted. What happened and is it safe to ignore?

SQL> select FIRST_TIME,SEQUENCE#,THREAD#,STATUS,DELETED,BACKUP_COUNT
from V$ARCHIVED_LOG 
where SEQUENCE# between 786840 and 786850;

FIRST_TIME	     SEQUENCE#	  THREAD# S DEL BACKUP_COUNT
------------------- ---------- ---------- - --- ------------
2017-09-10 02:25:12	786840		1 D YES 	   1
2017-09-10 02:25:15	786841		1 D YES 	   1
2017-09-10 02:25:18	786842		1 D YES 	   1
2017-09-10 02:25:21	786843		1 D YES 	   1
2017-09-10 02:25:24	786844		1 D YES 	   1
2017-09-10 02:25:27	786845		1 D YES 	   1
2017-09-10 02:25:30	786846		1 D YES 	   1
2017-09-10 02:25:33	786847		1 D YES 	   1
2017-09-10 02:25:36	786848		1 D YES 	   1
2017-09-10 02:25:39	786849		1 D YES 	   1
2017-09-10 02:25:42	786850		1 D YES 	   1

11 rows selected.

SQL> exit

Please ponder for a moment and will post findings next week.


Assign Pseudo Random Password for Oracle DB Users

Wed, 2017-09-13 21:35

Due to password verification function, I had to hard code some of  the requirements since I did not want to spend too much time on the matter.

ORA-20024: Password must contain at least 2 lowercase character(s)
ORA-20026: Password must contain at least 1 special character(s)
ORA-20025: Password must contain at least 2 digit(s)

DBMS_RANDOM.STRING opt IN CHAR,len IN NUMBER) RETURN VARCHAR2;

opt:
'u', 'U' - returning string in uppercase alpha characters
'l', 'L' - returning string in lowercase alpha characters
'a', 'A' - returning string in mixed case alpha characters
'x', 'X' - returning string in uppercase alpha-numeric characters
'p', 'P' - returning string in any printable characters.
Otherwise the returning string is in uppercase alpha characters.

len:
Length of the returning string

Modify DB user password:


declare
 pwd varchar2(10);
 l_sql varchar (10000);
begin
for x in (
select username from dba_users where username not in ('XS$NULL') order by 1
)
loop
 pwd := dbms_random.string('X',10);
 l_sql := 'alter user '||x.username||' identified by "ab!'||pwd||'yz90"';
 dbms_output.put_line(l_sql);
 execute immediate l_sql;
end loop;
end;
/

 

Check password modification date.


select name, ctime, ptime from sys.user$
where name in (select username from dba_users)
order by 1; 

 


Thoughts on RMAN Backup Strategy

Sun, 2017-09-10 12:56

Do you label your backupset to provide meaningful name?

rman_l0_gosctvna_1_1
rman_arc_h4scu0u7_1_1
rman_arc_h9scu4en_1_1
DBNAME_c-593812450-20170827-07.ctl

For Level 0 backup, do you perform database backup separately from archivelog and tag them differently, e.g. LEVEL0, ARCHIVELOG?

How do you tag your backup?

Do you backup controlfile to trace or backup init parameter or backup password file?

Just some thoughts after have been through hell with restore and lucky came out of hell.

Of course, there are pros and cons to everything.
With too much customization, cannot simply use RMAN configure to set it and forget it.

FYI: 12c syntax for SQL


connect target;
set echo on 
set command id to "LEVEL0";
show all;
run {
set controlfile autobackup format for device type disk to '/oradata/backup/%d_%I_%F.ctl';
allocate channel d1 device type disk format '/oradata/backup/%d_%I_%T_%U.LEVEL0' maxopenfiles 1;
backup as compressed backupset 
incremental level 0 check logical database 
#filesperset 1 tag="LEVEL0"
tag="LEVEL0"
include current controlfile
plus archivelog not backed up 1 times
#filesperset 8 tag="LEVEL0"
tag="LEVEL0"
;
alter database backup controlfile to trace as '/oradata/backup/cf_@.sql' REUSE RESETLOGS;
create pfile='/oradata/backup/init@.ora' from spfile;
create pfile from spfile;
}
report schema;
list backup summary;
exit

 


Windows Datapump Export

Thu, 2017-08-03 22:45

The purpose of the script is to perform full database export keeping 3 export copies.
If export is successful, then fullexp*.dmp will be renamed with _1.dmp suffix added to filename.
If export is unsuccessful, then code will exit, skipping rename operations.

Note: there should never be .dmp file without # suffix unless export is unsuccessful.

In hindsight, directoryName should be using variable (ORACLE_SID) versus hardcode.

SET ORACLE_SID=DB01
SET directoryName=D:\DB01\export

expdp ‘/ as sysdba’ full=y directory=DATA_PUMP_DIR dumpfile=fullexp_%ORACLE_SID%_%COMPUTERNAME%.dmp logfile=fullexp_%ORACLE_SID%_%COMPUTERNAME%.log flashback_time=SYSTIMESTAMP REUSE_DUMPFILES=YES
IF %ERRORLEVEL% NEQ 0 GOTO ERROR

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.dmp” (DEL “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.*”)

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.dmp” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.dmp” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.dmp”)
IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.log” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.log” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.log”)

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.dmp” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.dmp” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.dmp”)
IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.log” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.log” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.log”)

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.dmp” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.dmp” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.dmp”)
IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.log” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.log” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.log”)

EXIT 0

:ERROR
EXIT 1

Results after 4 runs.

08/03/2017  07:53 PM     2,680,008,704 fullexp_DB01_CMWPHV1_1.dmp
08/03/2017  07:53 PM           161,707 fullexp_DB01_CMWPHV1_1.log
08/03/2017  07:46 PM     2,680,008,704 fullexp_DB01_CMWPHV1_2.dmp
08/03/2017  07:46 PM           161,707 fullexp_DB01_CMWPHV1_2.log
08/03/2017  07:37 PM     2,680,008,704 fullexp_DB01_CMWPHV1_3.dmp
08/03/2017  07:37 PM           161,707 fullexp_DB01_CMWPHV1_3.log

Notes on datapatch

Tue, 2017-06-27 08:47

Mike Dietrich is the God father of all Oracle upgrades.

Here are just a few reference and should read

DBUA 12c and “datapatch.pl” – things to know

DBUA misses the post-upgrade datapatch execution in Oracle 12.1.0.2. The solution is to apply the SQL changes manually after DBUA has completed the database upgrade to Oracle Database 12c:

cd $ORACLE_HOME/OPatch
./datapatch -verbose

Does DBCA execute “datapatch” in Oracle 12.2?

My notes: when it doubt check it out.

$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 on Thu Jan 19 10:35:27 2017
Copyright (c) 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_93223_2017_01_19_10_35_27/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 24315824 (Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)):
  Installed in the binary registry only
Bundle series DBBP:
  ID 161018 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    24315824 (Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016))
    24340679 (DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679))

Installing patches...
Patch installation complete.  Total patches installed: 2

Validating logfiles...
Patch 24315824 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24315824/20676808/24315824_apply_DBFS_2017Jan19_10_35_43.log (no errors)
Patch 24340679 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_DBFS_2017Jan19_10_35_47.log (no errors)
SQL Patching tool complete on Thu Jan 19 10:37:16 2017

SQL> set serveroutput on
SQL> exec dbms_qopatch.get_sqlpatch_status;

-- Logfile is provided. How convenient is that?
Patch Id : 24315824
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:14
        Description : Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24315824/20676808/24315824_apply_DBFS_2017Jan19_10_35_43.log
        Status : SUCCESS

Patch Id : 24340679
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:16
        Description : DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_DBFS_2017Jan19_10_35_47.log
        Status : SUCCESS

PL/SQL procedure successfully completed.

SQL>


$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose


SQL> set serveroutput on
SQL> exec dbms_qopatch.get_sqlpatch_status;


Patch Id : 24315824
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:14
        Description : Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24315824/20676808/24315824_apply_DBFS_2017Jan19_10_35_43.log
        Status : SUCCESS

Patch Id : 24340679
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:16
        Description : DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_DBFS_2017Jan19_10_35_47.log
        Status : SUCCESS

PL/SQL procedure successfully completed.

SQL>

12c DataGuard Validate and More

Fri, 2017-06-16 18:11
12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt) (Doc ID 1582837.1)	

Configuration
db_name=hawk
db_unique_name=hawka (primary)
db_unique_name=hawkb (standby)

Configuration - hawkdg
  Protection Mode: MaxPerformance
  Members:
    hawka - Primary database
      hawkb - Physical standby database 

Validate DataGuard Configurations.

Monitorable (Read-Only) Properties

show configuration verbose

show database verbose hawka
show database verbose hawkb

validate database verbose hawka
validate database verbose hawkb

There is no need to use on database if the instance names are unique across primary and standby environments.

You might ask, why are instance name not the  same on primary and standby?

I don’t know.

show instance verbose hawka1 on database hawka
show instance verbose hawka2 on database hawka

show instance verbose hawkb1 on database hawkb
show instance verbose hawkb2 on database hawkb

The InconsistentProperties monitorable property returns a table that shows all properties whose values contained in the broker configuration file are inconsistent with the values in the corresponding server parameter file or the runtime values.

show database hawka InconsistentProperties
show database hawkb InconsistentProperties

The InconsistentLogXptProps monitorable property returns a table that shows all properties related to redo transport services whose values are inconsistent between the broker configuration file and the runtime value.

show database hawka InconsistentLogXptProps
show database hawkb InconsistentLogXptProps

The LogXptStatus monitorable property returns a table that contains the error status of redo transport services for each of the enabled configuration members. This property pertains to the primary database, a physical standby database that ships redo data, or a far sync instance.

show database hawka LogXptStatus

The SendQEntries monitorable property returns a table that shows all log files on the primary database that were not successfully archived to one or more standby databases. This property pertains to the primary database

show database hawka SendQEntries

The RecvQEntries monitorable property returns a table indicating all log files that were received by the standby database but have not yet been applied. If no rows are returned, it implies all log files received have been applied. This property pertains to a standby database.

show database hawkb RecvQEntries

The TopWaitEvents monitorable property specifies the 5 events with the longest waiting time in the specified instance.

show instance hawkb1 TopWaitEvents

How to edit database properties for all instances.

edit instance * on database hawka set property logarchivetrace=0;

Lastly, all commands and be run from shell script with example below.

echo "***** Checking Data Guard Broker Configuration ...."
dgmgrl -echo << END
connect /
show configuration verbose
show configuration TraceLevel
show database hawklas
show database hawksan
show instance hawklas DGConnectIdentifier
show instance hawksan DGConnectIdentifier
show instance hawklas StaticConnectIdentifier
show instance hawksan StaticConnectIdentifier
show instance hawklas InconsistentProperties
show instance hawksan InconsistentProperties
show instance hawklas LogArchiveMaxProcesses
show instance hawksan LogArchiveMaxProcesses
show instance hawklas DelayMins
show instance hawksan DelayMins
show instance hawklas LogArchiveTrace
show instance hawksan LogArchiveTrace
show instance hawklas statusreport
show instance hawksan statusreport
exit
END
exit


GoldenGate 12.2 TROUBLESHOOTING REPLICAT LAG

Sun, 2017-06-11 09:16

Time Since Chkpt and Lag at Chkpt from replicat keep increasing

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
REPLICAT    RUNNING     R_NEW12C    03:49:45      06:37:47    

This occurs for due to the following reasons:

Delivering a long running transaction
Waiting on a full table scan
Blocked by another sessions
Primary extract lag or pump lag keeps increasing

Is There a Way to Make Long-running Transactions Checkpoint? (Doc ID 969684.1)

The tradeoff with GROUPTRANSOPS is with efficiency. 
The tradeoff with MAXTRANSOPS is efficiency and transaction integrity. 

There are no long running transaction from the extract and is monitored using WARNLONGTRANS 15m, CHECKINTERVAL 3m.

grep "Long Running Transaction" dirrpt/E_OLD10G.rpt

My suspicion is FTS, but how to find out?

SQL> r
  1  select
  2  -- SQL_ID,PLAN_HASH_VALUE,
  3  OBJECT_OWNER,OBJECT_NAME, min(TIMESTAMP) min_ts, max(TIMESTAMP) max_ts, count(*) ct
  4  from DBA_HIST_SQL_PLAN
  5  where operation='TABLE ACCESS'
  6  and options='FULL'
  7  and NOT REGEXP_LIKE(object_owner,'SYS|SYSTEM|DBSNMP')
  8  and TIMESTAMP > TO_DATE('01-JUN-2017','DD-MON-YYYY')
  9  group by
 10  -- SQL_ID,PLAN_HASH_VALUE,
 11  OBJECT_OWNER,OBJECT_NAME
 12  order by count(*), OBJECT_OWNER,OBJECT_NAME
 13*
 
OBJECT_OWNER         OBJECT_NAME                    MIN_TS               MAX_TS                       CT
-------------------- ------------------------------ -------------------- -------------------- ----------
XXX                  THISISAREALLYLONGTABLENAME     01-JUN-2017 00:01:00 11-JUN-2017 02:55:36        114

SQL> select index_name from dba_indexes where table_name='THISISAREALLYLONGTABLENAME';
no rows selected
SQL> 

12c How to UN Expire Password for CDB Users

Sat, 2017-06-10 15:11

Use dbms_metadata.get_ddl to extract user and replace create with alter.

oracle@medintdbl01 ~ $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jun 10 15:31:13 2017

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


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

SQL> select username, expiry_date, account_status from dba_users where username like '%MONITOR%';

USERNAME
--------------------------------------------------------------------------------
EXPIRY_DA ACCOUNT_STATUS
--------- --------------------------------
C##MONITOR
10-JUN-17 EXPIRED

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> set long 1000000 longchunksize 32000 linesize 32000 pages 0 newpage none
SQL> set heading off tab off echo off define off sqlprefix off blockterminator off timing off verify off feedb off
SQL> set sqlblanklines on embedded on trimspool on  
SQL> select dbms_metadata.get_ddl('USER','C##MONITOR') from dual;

   CREATE USER "C##MONITOR" IDENTIFIED BY VALUES 'S:***'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE

SQL> ALTER USER "C##MONITOR" IDENTIFIED BY VALUES 'S:***'
SQL> select username, expiry_date, account_status from dba_users where username like '%MONITOR%';
C##MONITOR                                                                                       07-DEC-17 OPEN
SQL> 

GoldenGate Debugging

Fri, 2017-06-09 21:40

I was working on automating debug information to submit to Oracle Support and thought I share implementation for what was requested.

OGG_GROUP_NAME is from ggsci info all (case sensitive)
./debug_gg.sh: line 3: 1: —> USAGE: /debug_gg.sh OGG_GROUP_NAME
./debug_gg.sh E_LAX

#!/bin/sh -e
DN=`dirname $0`
BN=`basename $0`
OGG_GROUP_NAME=${1:?"---> USAGE: $DN/$BN "OGG_GROUP_NAME""}
set -x
ps -o pid,uname,cmd `pgrep -f "extract.*${OGG_GROUP_NAME}"`
pstack `pgrep -f "extract.*${OGG_GROUP_NAME}"`
pstack `pgrep -f "extract.*${OGG_GROUP_NAME}"`
pstack `pgrep -f "extract.*${OGG_GROUP_NAME}"`
set +x
$GG_HOME/ggsci << EOF
info ${OGG_GROUP_NAME} detail
send ${OGG_GROUP_NAME} status
sh sleep 2m
send ${OGG_GROUP_NAME} status
sh sleep 2m
send ${OGG_GROUP_NAME} status
send ${OGG_GROUP_NAME} report
exit
EOF
echo " "
ls -alrt $GG_HOME/dirrpt/${OGG_GROUP_NAME}*.rpt|tail -3
exit

Example output from pgrep and pstack

++ pgrep -f 'extract.*E_LAX'
+ ps -o pid,uname,cmd 40882
  PID USER     CMD
40882 ggsuser  /u01/gg/12.2.0/extract PARAMFILE /u01/gg/12.2.0/dirprm/e_lax.prm REPORTFILE /u01/gg/12.2.0/dirrpt/E_LAX.rpt PROCESSID E_LAX USESUBDIRS
++ pgrep -f 'extract.*E_LAX'
+ pstack 40882

Just realized does not scale since extract is hard coded.

Next step, learn how to read pstack output – YIKES!


GoldenGate Extract RBA Not Moving LAG Increasing Appears Hung

Thu, 2017-06-08 05:55

OGG Extract RBA Not Moving And LAG Increasing And Appears Hung (Doc ID 964705.1)

Typically, when Goldengate is performing recovery:

In recovery[1] – Extract is recovering to its checkpoint in the transaction log.
In recovery[2] – Extract is recovering from its checkpoint to the end of the trail.
Recovery complete – The recovery is finished, and normal processing will resume.

Example:
Current status: In recovery[1]: Reading from data source

SEND EXTRACT

First time I have seen Processing data with empty data queue and is essentially the same process.

send e* status  
Current status: In recovery[1]: Processing data with empty data queue

send e* status
Current status: Recovery complete: Processing data with empty data queue    

The scary part is current redo vs Goldengate Checkpoint.

To prevent this, make sure there is no lag, no long running transactions before stopping extract.

select thread#,sequence# from v$log where status='CURRENT' order by 1;

THREAD#    SEQUENCE#
---------- ----------
        1     1198566
        2     1291021
        
info e*
Log Read Checkpoint  Oracle Redo Logs
                     2017-06-07 14:44:53  Thread 1, Seqno 1198492, RBA 112396
Log Read Checkpoint  Oracle Redo Logs
                     2017-06-07 14:45:33  Thread 2, Seqno 1290939, RBA 1060244424

What if integrated extract is used? Where’s my Seqno?

info e*
Log Read Checkpoint Oracle Integrated Redo Logs
2017-02-12 18:36:06
SCN 0.4928929 (4928929)

Read more about at Log sequence# and rba# of integrated extract checkpoint


NOLOGGING Bad For Recovery

Thu, 2017-05-18 22:41

What doesn’t kill you makes you stronger.
I have gone through NOLOGGING hell and not sure if I am out yet.
Disk was lost from server and database was recovered.

Oracle Standard Edition (SE2) for Microsoft Windows x64 (64-bit).

RMAN> restore database validate preview;

  List of Archived Logs in backup set 4098
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    15209   134553557  26-APR-17 134574622  26-APR-17
  1    15218   134681063  26-APR-17 134690751  27-APR-17
RMAN-05119: recovery can not be done to a consistent state.
Media recovery start SCN is 133224369
Recovery must be done beyond SCN 133235710 to clear datafile fuzziness

RMAN> spool log to recover.log
RMAN> recover database until sequence 15209;
RMAN> alter database open resetlogs;
RMAN> exit 

Bug 20315311 – RMAN-5119: recovery can not be done to a consistent state (Doc ID 20315311.8)
The content was last updated on: 17-FEB-2017

RMAN gives the following notification in the 'restore database preview' output even though a successful backup has been performed:
  RMAN-05119: recovery can not be done to a consistent state
 
Workaround
 None

SQL> select * from mytable;

select * from mytable
                      *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 915)
ORA-26040: Data block was loaded using the NOLOGGING option

RMAN> validate tablespace USERS;

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     334            263346       848640          134981624

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     295            1225517      3932160         134981561

Finished validate at 18-MAY-17

RMAN> list failure;

Database Role: PRIMARY

no failures found that match specification

RMAN> advise failure;

Database Role: PRIMARY

no failures found that match specification

RMAN> exit

RMAN> backup validate check logical database;

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     334            263346       848640          134982083
  File Name: 

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     295            1225517      3932160         134982061
  File Name: 

Finished validate at 18-MAY-17  

Basically, NOLOGGING operation occurred from backup before resetlogs recovery
and there is no datafile/database backup following NOLOGGING operations
since database backup is performed once a day.

The Gains and Pains of Nologging Operations (Doc ID 290161.1)

However, NOLOGGING is intended for configurations in which media recovery 
or the recovery of the corresponding object is not important. 
Thus, if the disk or tape or storage media fails, 
you will not be able to recover your changes from the redo because the changes were never logged.  
SQL> select max(NONLOGGED_END_CHANGE#), max(NONLOGGED_END_TIME), max(RESETLOGS_CHANGE#), max(RESETLOGS_TIME) from v$nonlogged_block;
MAX(NONLOGGED_END_CHANGE#) MAX(NONLOGGED_END_TI MAX(RESETLOGS_CHANGE#) MAX(RESETLOGS_TIME)
-------------------------- -------------------- ---------------------- --------------------
                 134534115

SQL> select count(*) from v$nonlogged_block;
  COUNT(*)
----------
        64

-- NOTICE: this matched what RMAN reported.
SQL> select file#, count(*), sum(blocks) from v$nonlogged_block group by file#;
     FILE#   COUNT(*) SUM(BLOCKS)
---------- ---------- -----------
         5         54         334
         8         10         295

SQL> select resetlogs_change#, resetlogs_time, current_scn from v$database;
RESETLOGS_CHANGE# RESETLOGS_TIME       CURRENT_SCN
----------------- -------------------- -----------
        134553558 15-MAY-2017 18:28:53   135266743

SQL> select file#, block#, NONLOGGED_START_CHANGE# from v$nonlogged_block
  2  where NONLOGGED_START_CHANGE# > (select RESETLOGS_CHANGE# from v$database)
  3  ;

no rows selected

SQL>

Truncating table and reloading data resolve SELECT issues but block corruption (Free Block) still exists.
You can read more about Playing with Oracle Free Block Corruption

SQL> truncate table mytable;

Table truncated.

SQL> select * from mytable;

no rows selected

SQL> r
  1  SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2  , greatest(e.block_id, c.block#) corr_start_block#
  3  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  4  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5  - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  6  , null description
  7  FROM dba_extents e, v$nonlogged_block c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1 10 AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 13  , greatest(f.block_id, c.block#) corr_start_block#
 14  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 15  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 16  - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 17  , 'Free Block' description
 18  FROM dba_free_space f, v$nonlogged_block c
 19  WHERE f.file_id = c.file#
 20  AND f.block_id <= c.block# + c.blocks - 1 21 AND f.block_id + f.blocks - 1 >= c.block#
 22  order by file#, corr_start_block#
 23*

                                                                                      START     END
OWNER      SEGMENT_TYPE         SEGMENT_NAME         PARTITION_NAME        FILE#     BLOCK#  BLOCK# BLOCKS_CORRUPTED DESCRIPTION
---------- -------------------- -------------------- -------------------- ------ ---------- ------- ---------------- ------------------------------
SCHEMA    TABLE                ########                                       5        219     219                1
SCHEMA    TABLE                ########                                       5        227     227                1
SCHEMA    TABLE                ########                                       5        235     235                1
SCHEMA    TABLE                ##################                             5        251     251                1
SCHEMA    TABLE                ##########                                     5        275     275                1
SCHEMA    TABLE                ############                                   5        283     283                1
SCHEMA    TABLE                #############                                  5        459     459                1
SCHEMA    TABLE                ##########                                     5        467     467                1
SCHEMA    TABLE                ################                               5        491     491                1
SCHEMA    TABLE                #############                                  5        499     499                1
SCHEMA    TABLE                ##################                             5        851     851                1
SCHEMA    TABLE                ########                                       5        875     875                1
SCHEMA    TABLE                #############                                  5        883     883                1
SCHEMA    TABLE                ################                               5        891     891                1
SCHEMA    TABLE                ##################                             5        899     903                5
SCHEMA    TABLE                ################                               5        907     907                1
SCHEMA    TABLE                ############                                   5        915     919                5
SCHEMA    TABLE                ############                                   5       1091    1091                1
SCHEMA    TABLE                ##########                                     5       1107    1107                1
SCHEMA    TABLE                ##############                                 5       1323    1327                5
SCHEMA    TABLE                ############                                   5       1331    1331                1
SCHEMA    TABLE                ##############                                 5       1347    1351                5
SCHEMA    TABLE                ############                                   5       1355    1355                1
SCHEMA    TABLE                ###########                                    5       1643    1643                1
SCHEMA    TABLE                ##########                                     5       1651    1651                1
SCHEMA    TABLE                ###########                                    5       2099    2103                5
SCHEMA    TABLE                ###############                                5       2531    2531                1
SCHEMA    TABLE                ##############                                 5       2539    2543                5
SCHEMA    TABLE                ##############                                 5       2547    2547                1
SCHEMA    TABLE                ###############                                5       2707    2707                1
SCHEMA    TABLE                ###########                                    5       2715    2715                1
SCHEMA    TABLE                ###############                                5       2875    2875                1
SCHEMA    TABLE                ##############                                 5       4211    4215                5
                                                                               5       4216    4218                3 Free Block
SCHEMA    TABLE                ###########                                    5       4611    4611                1
SCHEMA    TABLE                ########                                       5       4627    4627                1
                                                                               5       4872    4877                6 Free Block
                                                                               5       4880    4886                7 Free Block
                                                                               5       5064    5071                8 Free Block
                                                                               5       5073    5087               15 Free Block
                                                                               5       5089    5103               15 Free Block
                                                                               5       5105    5119               15 Free Block
                                                                               5       5121    5135               15 Free Block
                                                                               5       5137    5159               23 Free Block
                                                                               5       5161    5175               15 Free Block
                                                                               5       5177    5191               15 Free Block
                                                                               5       5193    5207               15 Free Block
                                                                               5       5209    5223               15 Free Block
                                                                               5       5225    5239               15 Free Block
                                                                               5       5241    5255               15 Free Block
                                                                               5       5257    5263                7 Free Block
                                                                               5       5328    5335                8 Free Block
                                                                               5       5337    5343                7 Free Block
                                                                               5     229506  229550               45 Free Block
                                                                               5     608697  608703                7 Free Block
                                                                               8    1120824 #######                5 Free Block
                                                                               8    1120872 #######                8 Free Block
                                                                               8    1274888 #######                8 Free Block
                                                                               8    1536681 #######               14 Free Block
                                                                               8    1536697 #######               11 Free Block
                                                                               8    1536713 #######                6 Free Block
                                                                               8    1791056 #######                8 Free Block
                                                                               8    2804571 #######               37 Free Block
                                                                               8    3213819 #######                5 Free Block
                                                                               8    3854212 #######              193 Free Block

65 rows selected.

Elapsed: 00:09:22.13
SQL>

How to identify all the Corrupted Objects in the Database with RMAN (Doc ID 472231.1)
In 12c the NOLOGGING blocks identified by rman validate are in new view v$nonlogged_block

set lines 200 pages 1000 tab off trimsp on timing on
col owner for a10
col segment_type for a20
col segment_name for a20
col partition_name for a20
col partition_name for a20
col description for a30
col file# for 99999
col corr_start_block# heading 'CORRUPT|START|BLOCK#' for 999999999
col corr_end_block# heading 'CORRUPT|END|BLOCK#' for 999999999
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$nonlogged_block c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$nonlogged_block c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#
;


Windows Oracle Services Using PowerShell

Wed, 2017-05-17 17:39

Lately, I have been getting feet wet with Windows.
I know GUI can be used but not very good to reproduce.

Here is now to find Stopped Oracle Windows Services and start them.

And if you want to use GUI, run Services.msc from command line.

Windows PowerShell
Copyright (C) 2012 Microsoft Corporation. All rights reserved.

PS C:\Users\oracle> hostname
minions

PS C:\Users\oracle> Get-Service -Name *oracle* | Where Status -eq "Stopped" | Format-List
Name                : Oracleagent12c1Agent
DisplayName         : Oracleagent12c1Agent
Status              : Stopped
DependentServices   : {}
ServicesDependedOn  : {}
CanPauseAndContinue : False
CanShutdown         : False
CanStop             : False
ServiceType         : Win32OwnProcess

Name                : OracleOraHome1ClrAgent
DisplayName         : OracleOraHome1ClrAgent
Status              : Stopped
DependentServices   : {}
ServicesDependedOn  : {}
CanPauseAndContinue : False
CanShutdown         : False
CanStop             : False
ServiceType         : Win32OwnProcess

Name                : OracleRemExecServiceV2
DisplayName         : OracleRemExecServiceV2
Status              : Stopped
DependentServices   : {}
ServicesDependedOn  : {}
CanPauseAndContinue : False
CanShutdown         : False
CanStop             : False
ServiceType         : Win32OwnProcess

PS C:\Users\oracle> Start-Service -name Oracleagent12c1Agent
WARNING: Waiting for service 'Oracleagent12c1Agent (Oracleagent12c1Agent)' to start...
WARNING: Waiting for service 'Oracleagent12c1Agent (Oracleagent12c1Agent)' to start...
WARNING: Waiting for service 'Oracleagent12c1Agent (Oracleagent12c1Agent)' to start...
WARNING: Waiting for service 'Oracleagent12c1Agent (Oracleagent12c1Agent)' to start...

PS C:\Users\oracle> Get-Service -name Oracleagent12c1Agent
Status   Name               DisplayName
------   ----               -----------
Start... Oracleagent12c1... Oracleagent12c1Agent

PS C:\Users\oracle> Get-Service -name Oracleagent12c1Agent
Status   Name               DisplayName
------   ----               -----------
Running  Oracleagent12c1... Oracleagent12c1Agent

PS C:\Users\oracle> Get-Service -Name *oracle* | Where Status -eq "Stopped" | Format-List
Name                : OracleOraHome1ClrAgent
DisplayName         : OracleOraHome1ClrAgent
Status              : Stopped
DependentServices   : {}
ServicesDependedOn  : {}
CanPauseAndContinue : False
CanShutdown         : False
CanStop             : False
ServiceType         : Win32OwnProcess

Name                : OracleRemExecServiceV2
DisplayName         : OracleRemExecServiceV2
Status              : Stopped
DependentServices   : {}
ServicesDependedOn  : {}
CanPauseAndContinue : False
CanShutdown         : False
CanStop             : False
ServiceType         : Win32OwnProcess

PS C:\Users\oracle> C:\app\oracle\product\agent12c\core\12.1.0.3.0\bin\emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.3.0
OMS Version       : 12.1.0.3.0
Protocol Version  : 12.1.0.1.0
Agent Home        : C:/app/oracle/product/agent12c/agent/core/12.1.0.3.0
Agent Binaries    : c:\app\oracle\product\agent12c\core\12.1.0.3.0
Agent Process ID  : 538684
Parent Process ID : 536640
Agent URL         : https://minions.local:1830/emd/main/
Repository URL    : https://cloud.local:4903/empbs/upload
Started at        : 2017-05-17 13:30:23
Started by user   : minions$
Last Reload       : (none)
Last successful upload                       : 2017-05-17 13:31:00
Last attempted upload                        : 2017-05-17 13:31:00
Total Megabytes of XML files uploaded so far : 0.03
Number of XML files pending upload           : 1
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 68.70%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2017-05-17 13:30:41
Last successful heartbeat to OMS             : 2017-05-17 13:30:41
Next scheduled heartbeat to OMS              : 2017-05-17 13:31:41

---------------------------------------------------------------
Agent is Running and Ready

PS C:\Users\oracle> Services.msc

Pages