Michael Dinh

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

Upgrade 12.2 Journey – Build Virtual 11.2.0.4 Oracle Restart

Sun, 2017-10-08 10:53

I am using oravirt vagrant boxes.

https://github.com/oravirt/vagrantfile
https://github.com/oravirt/vagrant-vbox-si-asm
https://github.com/oravirt/vagrantfile#environment-variables-that-can-be-used-to-override-defaults

Starting the VM’s
vagrant up will build the VM’s to the specification in hosts.yml and run the base provisioner
If there are extra provisioners defined you need one of the following:
setup=true vagrant up (if VM’s are not created)
setup=true vagrant provision (if the VM’s are already up)

setup=true giver=11.2.0.4 dbver=11.2.0.4 cdb=false vagrant up
setup=true vagrant up
setup=true giver=11.2.0.4 dbver=11.2.0.4 cdb=false vagrant provision

Modifications made for shared folders.

vagrant-vbox-si-asmF:\Vagrant\vagrant-vbox-si-asm\hosts.yml
synced_folders:
#- {src: swrepo, dest: /media/swrepo}
- {src: "F:\\OracleSoftware", dest: /sf_OracleSoftware}
- {src: "C:\\dinh\\Dropbox\\working", dest: /sf_working}
- {src: "F:\\OracleSoftware\\11.2.0.4_PatchSet_Linux_x64", dest: /media/swrepo}

F:\Vagrant\vagrant-vbox-si-asm\extra-provision\ansible-oracle\group_vars\vbox-si-asm
oracle_stage_remote: /sf_OracleSoftware
oracle_stage_remote: /sf_working
oracle_stage_remote: /media/swrepo

Environment shared folders.

[vagrant@db-asm-1 ~]$ ps -ef|grep pmon
oracle    1413     1  0 17:18 ?        00:00:00 ora_pmon_orclcdb
vagrant   2435  2417  0 17:19 pts/0    00:00:00 grep pmon
oracle   24615     1  0 16:47 ?        00:00:00 asm_pmon_+ASM
[vagrant@db-asm-1 ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2              47G  2.3G   43G   6% /
tmpfs                 873M   83M  790M  10% /dev/shm
vagrant               3.7T  172G  3.5T   5% /vagrant
sf_working            420G  133G  287G  32% /sf_working
media_swrepo          3.7T  172G  3.5T   5% /media/swrepo
sf_OracleSoftware     3.7T  172G  3.5T   5% /sf_OracleSoftware
/dev/mapper/vgora-lvora
                       84G   12G   68G  16% /u01

[vagrant@db-asm-1 ~]$ cd /media/swrepo/
[vagrant@db-asm-1 swrepo]$ ls -l
total 4305238
drwxrwxrwx. 1 vagrant vagrant       4096 Jul 20  2016 client
drwxrwxrwx. 1 vagrant vagrant       4096 Jul 20  2016 database
drwxrwxrwx. 1 vagrant vagrant       4096 Jul 20  2016 grid
drwxrwxrwx. 1 vagrant vagrant       4096 Oct  7 15:46 JUL2017PSU
-rwxrwxrwx. 1 vagrant vagrant 1395582860 Feb 11  2014 p13390677_112040_Linux-x86-64_1of7.zip
-rwxrwxrwx. 1 vagrant vagrant 1151304589 Feb 11  2014 p13390677_112040_Linux-x86-64_2of7.zip
-rwxrwxrwx. 1 vagrant vagrant 1205251894 Apr 25  2016 p13390677_112040_Linux-x86-64_3of7.zip
-rwxrwxrwx. 1 vagrant vagrant  656026876 May 12  2014 p13390677_112040_Linux-x86-64_4of7.zip
-rwxrwxrwx. 1 vagrant vagrant     297405 Jul 17  2016 README for 13390677.pdf
-rwxrwxrwx. 1 vagrant vagrant      82385 Jul 17  2016 View Patch Digest.pdf
[vagrant@db-asm-1 swrepo]$

[vagrant@db-asm-1 swrepo]$ cd /sf_OracleSoftware/12.2.0.1_Linux_64
[vagrant@db-asm-1 12.2.0.1_Linux_64]$ ls -l
total 6297251
-rwxrwxrwx. 1 vagrant vagrant 3453696911 Apr 12 15:44 linuxx64_12201_database.zip
-rwxrwxrwx. 1 vagrant vagrant 2994687209 Apr 12 15:43 linuxx64_12201_grid_home.zip
[vagrant@db-asm-1 12.2.0.1_Linux_64]$

Install Log for vagrant-vbox-si-asm


Playing with asmcmd

Sat, 2017-10-07 21:29

ASMCMD Disk Group Management Commands (11.2)

asmcmd -V
asmcmd version 11.2.0.4.0

asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304     10236    10148                0           10148              0             N  CRS/
MOUNTED  EXTERN  N         512   4096  4194304     20472    18612                0           18612              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  4194304     30708    30264                0           30264              0             N  FRA/

asmcmd lsdsk
Path
/dev/oracle/crs01
/dev/oracle/data01
/dev/oracle/data02
/dev/oracle/fra01
/dev/oracle/fra02
/dev/oracle/fra03

asmcmd lsattr -G DATA -l
Name                     Value
access_control.enabled   FALSE
access_control.umask     066
au_size                  4194304
cell.smart_scan_capable  FALSE
compatible.asm           11.2.0.4.0
compatible.rdbms         11.2.0.4.0
content.type             data
disk_repair_time         3.6h
idp.boundary             auto
idp.type                 dynamic
sector_size              512

asmcmd lsattr -G FRA -l
Name                     Value
access_control.enabled   FALSE
access_control.umask     066
au_size                  4194304
cell.smart_scan_capable  FALSE
compatible.asm           11.2.0.4.0
compatible.rdbms         11.2.0.4.0
content.type             data
disk_repair_time         3.6h
idp.boundary             auto
idp.type                 dynamic
sector_size              512

asmcmd setattr -G FRA compatible.asm 11.2.0.4
asmcmd setattr -G FRA compatible.rdbms 11.2.0.4

asmcmd spget
+CRS/asm/asmparameterfile/registry.253.956737501

asmcmd iostat -G DATA -t
Group_Name  Dsk_Name   Reads      Writes     Read_Time   Write_Time
DATA        DATA_0000  253212160  101387776  260.330309  13325.418821
DATA        DATA_0001  178841088  27744256   209.972386  152.957184

--io Displays information in number of I/Os, instead of bytes.
asmcmd iostat -G DATA -t --io
Group_Name  Dsk_Name   Reads  Writes  Read_Time   Write_Time
DATA        DATA_0000  16354  8570    260.792741  13331.596792
DATA        DATA_0001  11496  1650    209.973173  152.957184

asmcmd iostat -G DATA -t --io --region
Group_Name  Dsk_Name   Reads  Writes  Cold_Reads  Cold_Writes  Hot_Reads  Hot_Writes  Read_Time   Write_Time
DATA        DATA_0000  16357  8572    16315       5028         0          0           260.793019  13334.818434
DATA        DATA_0001  11497  1650    11436       1645         0          0           209.973268  152.957184

asmcmd lsof -G DATA
DB_Name  Instance_Name  Path
orclcdb  orclcdb        +data/orclcdb/controlfile/current.260.956739025
orclcdb  orclcdb        +data/orclcdb/datafile/sysaux.257.956738871
orclcdb  orclcdb        +data/orclcdb/datafile/system.256.956738871
orclcdb  orclcdb        +data/orclcdb/datafile/undotbs1.258.956738873
orclcdb  orclcdb        +data/orclcdb/datafile/users.259.956738873
orclcdb  orclcdb        +data/orclcdb/onlinelog/group_1.261.956739029
orclcdb  orclcdb        +data/orclcdb/onlinelog/group_2.262.956739055
orclcdb  orclcdb        +data/orclcdb/onlinelog/group_3.263.956739083
orclcdb  orclcdb        +data/orclcdb/tempfile/temp.264.956739127

oracle@db-asm-1:+ASM:/home/oracle
> ps -ef|grep pmon
oracle   15831     1  0 02:22 ?        00:00:00 asm_pmon_+ASM
oracle   16030     1  0 02:22 ?        00:00:00 ora_pmon_orclcdb
oracle   16330 11101  0 02:24 pts/3    00:00:00 grep pmon

oracle@db-asm-1:+ASM:/home/oracle
> . oraenv <<< orclcdb 
ORACLE_SID = [+ASM] ? The Oracle base remains unchanged with value /u01/app/oracle 

oracle@db-asm-1:orclcdb:/home/oracle 
> srvctl config database
orclcdb

oracle@db-asm-1:orclcdb:/home/oracle
> srvctl stop database -d orclcdb -o immediate

oracle@db-asm-1:orclcdb:/home/oracle
> ps -ef|grep pmon
oracle   15831     1  0 02:22 ?        00:00:00 asm_pmon_+ASM
oracle   16695 11101  0 02:25 pts/3    00:00:00 grep pmon

oracle@db-asm-1:orclcdb:/home/oracle
> . oraenv <<< +ASM
ORACLE_SID = [orclcdb] ? The Oracle base remains unchanged with value /u01/app/oracle

oracle@db-asm-1:+ASM:/home/oracle 
> asmcmd lsof -G DATA
DB_Name  Instance_Name  Path
oracle@db-asm-1:+ASM:/home/oracle
>

SQLcl comes default with all DB/Client installs 12.2

Mon, 2017-10-02 08:01

I had tweeted, “Wouldn’t it be nice for SQL*Plus have auto size for columns vs having to manually configure each? Larry must have not used Excel before?”

I learned, SQL*Plus does not have this functionality but SQLcl does.

Nice video and demo from SQL Developer Command Line SQLcl 

Here are the tweets

 

 


Mining Goldgate ggserr.log

Fri, 2017-09-29 21:33

Can you imagine me running in circles shouting, “The sky is falling, the sky is falling?”

Replicat Lag at Chkpt: 03:21:45

Here are the trail files at target – look at how fast it is being created.

ls -alrt ./dirdat/aa*|tail -20
-rw-r----- 1 ggsuser ggsuser 499999845 Sep 29 11:09 ./dirdat/aa000020827
-rw-r----- 1 ggsuser ggsuser 499999575 Sep 29 11:09 ./dirdat/aa000020828
-rw-r----- 1 ggsuser ggsuser 499999929 Sep 29 11:10 ./dirdat/aa000020829
-rw-r----- 1 ggsuser ggsuser 499999771 Sep 29 11:11 ./dirdat/aa000020830
-rw-r----- 1 ggsuser ggsuser 499999941 Sep 29 11:11 ./dirdat/aa000020831
-rw-r----- 1 ggsuser ggsuser 499999858 Sep 29 11:12 ./dirdat/aa000020832
-rw-r----- 1 ggsuser ggsuser 499999571 Sep 29 11:12 ./dirdat/aa000020833
-rw-r----- 1 ggsuser ggsuser 499999874 Sep 29 11:13 ./dirdat/aa000020834
-rw-r----- 1 ggsuser ggsuser 499999782 Sep 29 11:14 ./dirdat/aa000020835
-rw-r----- 1 ggsuser ggsuser 499999975 Sep 29 11:14 ./dirdat/aa000020836

My hypothesis: lots of data being capture at source.

After all is said and done. The ggserr.log was mined.

Gather dates for the 10 highest number of trails created by day in 2017.

SOURCE:

grep "^2017" ggserr.log.dinh|grep "p_test.prm:  Rolling over remote file"|awk '{ print $1 }'|uniq -c|sort -nrk 1|head

    224 2017-09-29
    147 2017-06-02
    105 2017-02-28
    101 2017-05-31
    100 2017-03-01
     98 2017-06-01
     97 2017-05-18
     91 2017-05-26
     89 2017-07-25
     85 2017-01-26

TARGET:


grep "^2017" ggserr.log.dinh|grep "r_test.prm:  Switching to next trail file"|awk '{ print $1 }'|uniq -c|sort -nrk 1|head

    279 2017-09-29
    183 2017-02-28
    174 2017-03-01
    148 2017-06-02
    146 2017-02-24
    137 2017-08-29
    137 2017-03-24
    133 2017-08-11
    130 2017-08-16
    128 2017-03-02

Different count between source/target for 2017-09-29 is due to data being collected at different time.


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> 

Pages