Posted in 2017

Patching a RAC, OJVM is rolling installable!

I want to write only necessary steps which I have executed on the RAC and show the whole datapatch!
I do basically the same as opatchauto, but in a step by step manner.
Since January 2017 you can conditially rolling patch a RAC. So what I have done is, patching a 2 Node RAC with local homes with PSU Jul2016 with OJVM to the newest PSU (26636286)

See Note
RAC Rolling Install Process for the “Oracle JavaVM Component Database PSU” (OJVM PSU) Patches (Doc ID 2217053.1)

database owner
 srvctl stop home -o /u01/app/oracle/12.1.0.2 -s /tmp/status -n <node>

root
 /u01/app/grid/12.1.0.2/crs/install/rootcrs.sh -prepatch

grid owner
 runuser -l oracle -c '/u01/app/grid/12.1.0.2/OPatch/opatch apply -silent -oh /u01/app/grid/12.1.0.2 -local /u01/install/26636286/26635815/26392192'
 runuser -l oracle -c '/u01/app/grid/12.1.0.2/OPatch/opatch apply -silent -oh /u01/app/grid/12.1.0.2 -local /u01/install/26636286/26635815/26392164'
 runuser -l oracle -c '/u01/app/grid/12.1.0.2/OPatch/opatch apply -silent -oh /u01/app/grid/12.1.0.2 -local /u01/install/26636286/26635815/21436941'
 runuser -l oracle -c '/u01/app/grid/12.1.0.2/OPatch/opatch apply -silent -oh /u01/app/grid/12.1.0.2 -local /u01/install/26636286/26635815/26713565'

database owner
 runuser -l oracle -c '/u01/install/26636286/26635815/26392192/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/12.1.0.2'
 runuser -l oracle -c '/u01/app/oracle/12.1.0.2/OPatch/opatch apply -silent -oh /u01/app/oracle/12.1.0.2 -local /u01/install/26636286/26635815/26392192'
 runuser -l oracle -c '/u01/app/oracle/12.1.0.2/OPatch/opatch apply -silent -oh /u01/app/oracle/12.1.0.2 -local /u01/install/26636286/26635815/26713565'
 runuser -l oracle -c '/u01/install/26636286/26635815/26392192/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/12.1.0.2'
 runuser -l oracle -c '/u01/app/oracle/12.1.0.2/OPatch/opatch apply -silent -oh /u01/app/oracle/12.1.0.2 -local /u01/install/26636286/26635845'

root
 /u01/app/grid/12.1.0.2/rdbms/install/rootadd_rdbms.sh
 /u01/app/grid/12.1.0.2/crs/install/rootcrs.sh -postpatch

database owner
 srvctl start home -o /u01/app/oracle/12.1.0.2 -s /tmp/status -n <node>

oracle@rac1 [ cdb11 ] OPatch]$ ./datapatch -verbose -skip_upgrade_check
 SQL Patching tool version 12.1.0.2.0 Production on Tue Nov 14 13:28:43 2017
 Copyright (c) 2012, 2016, Oracle. All rights reserved.

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

Connecting to database...OK
 Note: Datapatch will only apply or rollback SQL fixes for PDBs
 that are in an open state, no patches will be applied to closed PDBs.
 Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
 (Doc ID 1585822.1)
 Bootstrapping registry and package to current versions...done
 Determining current state...done

Current state of SQL patches:
 Patch 23177536 (Database PSU 12.1.0.2.160719, Oracle JavaVM Component (JUL2016)):
 Installed in CDB$ROOT ORCL PDB$SEED only
 Patch 26635845 (Database PSU 12.1.0.2.171017, Oracle JavaVM Component (OCT2017)):
 Installed in the binary registry only
 Bundle series PSU:
 ID 171017 in the binary registry and ID 160719 in PDB CDB$ROOT, ID 160719 in PDB PDB$SEED, ID 160719 in PDB ORCL

Adding patches to installation queue and performing prereq checks...
 Installation queue:
 For the following PDBs: CDB$ROOT PDB$SEED ORCL
 The following patches will be rolled back:
 23177536 (Database PSU 12.1.0.2.160719, Oracle JavaVM Component (JUL2016))
 The following patches will be applied:
 26635845 (Database PSU 12.1.0.2.171017, Oracle JavaVM Component (OCT2017))
 26713565 (DATABASE PATCH SET UPDATE 12.1.0.2.171017)

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

Validating logfiles...
 Patch 23177536 rollback (pdb CDB$ROOT): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/23177536/20400035/23177536_rollback_CDB1_CDBROOT_2017Nov14_13_29_44.log (no errors)
 Patch 26635845 apply (pdb CDB$ROOT): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26635845/21564421/26635845_apply_CDB1_CDBROOT_2017Nov14_13_33_01.log (no errors)
 Patch 26713565 apply (pdb CDB$ROOT): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26713565/21602269/26713565_apply_CDB1_CDBROOT_2017Nov14_13_33_04.log (no errors)
 Patch 23177536 rollback (pdb PDB$SEED): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/23177536/20400035/23177536_rollback_CDB1_PDBSEED_2017Nov14_13_35_07.log (no errors)
 Patch 26635845 apply (pdb PDB$SEED): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26635845/21564421/26635845_apply_CDB1_PDBSEED_2017Nov14_13_38_18.log (no errors)
 Patch 26713565 apply (pdb PDB$SEED): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26713565/21602269/26713565_apply_CDB1_PDBSEED_2017Nov14_13_38_25.log (no errors)
 Patch 23177536 rollback (pdb ORCL): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/23177536/20400035/23177536_rollback_CDB1_ORCL_2017Nov14_13_35_07.log (no errors)
 Patch 26635845 apply (pdb ORCL): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26635845/21564421/26635845_apply_CDB1_ORCL_2017Nov14_13_38_17.log (no errors)
 Patch 26713565 apply (pdb ORCL): SUCCESS
 logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/26713565/21602269/26713565_apply_CDB1_ORCL_2017Nov14_13_38_21.log (no errors)
 SQL Patching tool complete on Tue Nov 14 13:40:32 2017
Advertisements
Posted in 2017

12.2 SE2 Upgrade

Hi all,

after long time I am finally back with something very interresting.

Upgrade to 12c(12.1 /12.2) through RMAN Duplicate using BACKUP LOCATION with NOOPEN clause (Doc ID 2022820.1)

If you check this note, you get a pretty simple upgrade scenario. The funny thing is, as described here or in the docs, it simply does not work. with -l or -n it simply failed.

oracle@orcl [ ORCL ] admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl –l /u01/app/oracle/admin/upgrade catupgrd.sql

Argument list for [catctl.pl]
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0

Invalid command line syntax in the vicinity of '–l' or '/u01/app/oracle/admin/upgrade'. Exiting.

So after being nervous I decided to call it simply from sqlplus, who cares, I can restore the db in 2 Minutes.

 SQL> @catupgrd.sql
DOC>######################################################################
DOC>######################################################################
DOC> ERROR
DOC>
DOC>
DOC> As of 12.2, customers must use the parallel upgrade utility, catctl.pl,
DOC> to invoke catupgrd.sql when upgrading the database dictionary.
DOC> Running catupgrd.sql directly from SQL*Plus is no longer supported.
DOC>
DOC> For Example:
DOC>
DOC> cd $ORACLE_HOME/rdbms/admin
DOC> catctl
DOC>
DOC> or
DOC>
DOC> cd $ORACLE_HOME/rdbms/admin
DOC> $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
DOC>
DOC> Refer to the Oracle Database Upgrade Guide for more information.
DOC>
DOC>
DOC>######################################################################
DOC>######################################################################
DOC>#

And with the second defined command it works (I haven’t started the first). Interresting is, that the default value 4 was automatically set and the log location is by default $ORACLE_HOME/cfgtoollogs.

Now after waiting some time with these components to be upgraded

Components in [ORCL]
 Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV EM MGW ODM OLS RAC WK]

I am finishing after

Grand Total Upgrade Time: [0d:0h:34m:51s]

 

 

Posted in 2017

Installing GI for Standalone

Yesterday I have installed GI for Standalone, I wanted to install my VBox with AFD, but I got this error. I don’t know, it’s sad I think.

AFD-620: AFD is not supported on this operating system version: 3.10.0-514.10.2.el7.x86_64

So I had to install asmlib and continue with. I don’t understand why I had to restart my machine, it was restarted before I started ./gridSetup.sh so it’s strange.

I had disks sdb and sdc prepared with fdisk. I don’t write about it again, it’s always the same.

[oracle@ora01 oracle]$ lsblk
NAME            MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda               8:0    0   20G  0 disk
├─sda1            8:1    0  476M  0 part /boot
└─sda2            8:2    0 15.3G  0 part
  ├─ol-root     253:0    0   14G  0 lvm  /
  └─ol-products 253:2    0 23.3G  0 lvm  /products
sdb               8:16   0   12G  0 disk
sdc               8:32   0   12G  0 disk
sdd               8:48   0   30G  0 disk
└─sdd1            8:49   0 25.7G  0 part
  ├─ol-swap     253:1    0  3.7G  0 lvm  [SWAP]
  └─ol-products 253:2    0 23.3G  0 lvm  /products
sr0              11:0    1 1024M  0 rom

Now install the rpms

[root@ora01 ~]# yum install oracleasm-support 
[root@ora01 ~]# rpm -i /media/sf_12.2.0.1/oracleasmlib-2.0.12-1.el7.x86_64.rpm

Configuring oracleasm is always the same

[root@ora01 ~]# oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done

While initializing it I forgot a package

[root@ora01 ~]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": failed
Unable to load module "oracleasm"
[root@ora01 ~]#
[root@ora01 ~]# yum install kmod-oracleasm

Initialize oracleasm!

[root@ora01 ~]# oracleasm init
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm
[root@ora01 ~]#
[root@ora01 ~]# oracleasm createdisk disk1 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@ora01 ~]# oracleasm createdisk disk2 /dev/sdc1
Writing disk header: done
Instantiating disk: done
[root@ora01 ~]#
[root@ora01 ~]#
[root@ora01 ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
[root@ora01 ~]# oracleasm listdisks
DISK1
DISK2

Now everything is prepared for the gridSetup.sh

[oracle@ora01 oracle]$ cd grid_12.2.0.1/
[oracle@ora01 grid_12.2.0.1]$ ./gridSetup.sh
Launching Oracle Grid Infrastructure Setup Wizard...

You can find the log of this install session at:
 /tmp/GridSetupActions2017-04-06_05-37-53PM/gridSetupActions2017-04-06_05-37-53PM.log
Moved the install session logs to:
 /products/oraInventory/logs/GridSetupActions2017-04-06_05-37-53PM

The root.sh exection failed of course and it wrote I need to restart the machine to continue.

Now I had to manually execute the steps when this error happens, nice thing Oracle, it’s documented and it worked without problems.

[root@ora01 oraInventory]# ./orainstRoot.sh
Changing permissions of /products/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /products/oraInventory to oinstall.
The execution of the script is complete.
[root@ora01 oraInventory]# cd ../
[root@ora01 products]# cd ora
oracle/       oraInventory/
[root@ora01 products]# cd oracle/grid_12.2.0.1/
[root@ora01 grid_12.2.0.1]# ./root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /products/oracle/grid_12.2.0.1
   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.
Using configuration parameter file: /products/oracle/grid_12.2.0.1/crs/install/crsconfig_params
The log of current session can be found at:
  /products/oracle/crsdata/ora01/crsconfig/roothas_2017-04-06_05-46-45PM.log
2017/04/06 17:46:46 CLSRSC-363: User ignored prerequisites during installation
2017/04/06 17:46:52 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'ora01'
CRS-2673: Attempting to stop 'ora.evmd' on 'ora01'
CRS-2677: Stop of 'ora.evmd' on 'ora01' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'ora01' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.

ora01     2017/04/06 17:47:42     /products/oracle/grid_12.2.0.1/cdata/ora01/backup_20170406_174742.olr     0
2017/04/06 17:47:43 CLSRSC-327: Successfully configured Oracle Restart for a standalone server

Now the last part was to generate a response file and run this type of command

[root@ora01 cfgtoollogs]# pwd
/products/oracle/grid_12.2.0.1/cfgtoollogs
[root@ora01 cfgtoollogs]# ./configToolAllCommands RESPONSE_FILE=/products/oracle/grid_12.2.0.1/cfgtoollogs/cfg.props
This script must not be run as root.
[oracle@ora01 ~]$ cat /products/oracle/grid_12.2.0.1/cfgtoollogs/cfg.props
oracle.assistants.asm|S_ASMPASSWORD=oracle
[oracle@ora01 cfgtoollogs]$ ./configToolAllCommands RESPONSE_FILE=/products/oracle/grid_12.2.0.1/cfgtoollogs/cfg.props

Finally it finished with a lot of warnings. But warnings are not problems.

After this I could start my asmca and create my ASM, it went smoothly. What I like to do on my VMs is to set these parameters, Oracle automatically sets AMM for GI to 1000MB and you cannot resize it to a lower value, it’s hardcoded. So you have to use ASMM

[oracle@ora01 trace]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Thu Apr 6 18:06:42 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter memor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 1076M
memory_target                        big integer 1076M
SQL> show parameter spf

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/ASM/ASMPARAMETERFILE/reg
                                                 istry.253.940615485
SQL> alter system set sga_target=300m scope=spfile ;

System altered.

SQL> alter system set memory_max_target=0 scope=spfile ;

System altered.

SQL> alter system set memory_target=0 scope=spfile ;

System altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@ora01 trace]$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'ora01'
CRS-2673: Attempting to stop 'ora.evmd' on 'ora01'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'ora01'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'ora01'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'ora01'
CRS-2677: Stop of 'ora.FRA.dg' on 'ora01' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'ora01' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'ora01'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'ora01' succeeded
CRS-2677: Stop of 'ora.evmd' on 'ora01' succeeded
CRS-2677: Stop of 'ora.asm' on 'ora01' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'ora01'
CRS-2677: Stop of 'ora.cssd' on 'ora01' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'ora01' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[oracle@ora01 trace]$ crsctl start has
CRS-4123: Oracle High Availability Services has been started.
[oracle@ora01 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Thu Apr 6 18:18:23 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show sga

Total System Global Area  314572800 bytes
Fixed Size                  8620224 bytes
Variable Size             280786752 bytes
ASM Cache                  25165824 bytes

 

Posted in 2017

dNFS Oracle 12.2

First let’s check my disks.

[root@nfs01 ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 20G 0 disk
├─sda1 8:1 0 500M 0 part /boot
└─sda2 8:2 0 19.5G 0 part
├─ol-root 252:0 0 17.5G 0 lvm /
└─ol-swap 252:1 0 2G 0 lvm [SWAP]
sdb 8:16 0 30G 0 disk
└─sdb1 8:17 0 30G 0 part
└─vg_oracle-product 252:2 0 20G 0 lvm /u01
sdc 8:32 0 50G 0 disk
sr0 11:0 1 1024M 0 rom

As we see my sdc is not used, so we can partition it

[root@nfs01 ~]# fdisk /dev/sdc
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0x592b4f7f.

Command (m for help): n
Partition type:
p primary (0 primary, 0 extended, 4 free)
e extended
Select (default p): p
Partition number (1-4, default 1): 1
First sector (2048-104857599, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-104857599, default 104857599):
Using default value 104857599
Partition 1 of type Linux and of size 50 GiB is set

Command (m for help): t
Selected partition 1
Hex code (type L to list all codes): 8e
Changed type of partition 'Linux' to 'Linux LVM'

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

Make a filesystem on this device

[root@nfs01 ~]# mkfs.xfs /dev/sdc1
meta-data=/dev/sdc1 isize=256 agcount=4, agsize=3276736 blks
= sectsz=512 attr=2, projid32bit=1
= crc=0 finobt=0, sparse=0
data = bsize=4096 blocks=13106944, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=0
log =internal log bsize=4096 blocks=6399, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0

Small preparations
[root@nfs01 ~]# mkdir /oraclenfs
[root@nfs01 ~]# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),982(vboxsf)
[root@nfs01 ~]# cat /etc/fstab
/dev/mapper/ol-root / xfs defaults 0 0
UUID=52f425c5-8649-49e7-a899-b68ff9d75451 /boot xfs defaults 0 0
/dev/mapper/ol-swap swap swap defaults 0 0
/dev/mapper/vg_oracle-product /u01 xfs defaults 0 0
/dev/sdc1 /oraclenfs xfs defaults 0 0
[root@nfs01 ~]# mount -a
[root@nfs01 ~]#
[root@nfs01 ~]#
[root@nfs01 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/ol-root 18G 8.6G 8.9G 50% /
devtmpfs 987M 0 987M 0% /dev
tmpfs 1002M 84K 1002M 1% /dev/shm
tmpfs 1002M 8.9M 993M 1% /run
tmpfs 1002M 0 1002M 0% /sys/fs/cgroup
/dev/mapper/vg_oracle-product 20G 4.2G 16G 21% /u01
/dev/sda1 497M 287M 211M 58% /boot
12.2.0.1 4.8T 2.3T 2.6T 47% /media/sf_12.2.0.1
tmpfs 201M 16K 201M 1% /run/user/42
tmpfs 201M 0 201M 0% /run/user/0
/dev/sdc1 50G 33M 50G 1% /oraclenfs
[root@nfs01 ~]# chown 54321:54321 /oraclenfs/
[root@nfs01 ~]# cat /etc/exports
/oraclenfs *(rw,sync,all_squash,insecure,anonuid=54321,anongid=54321)
[root@nfs01 ~]# chkconfig --level 345 nfs on
Note: Forwarding request to 'systemctl enable nfs.service'.
Created symlink from /etc/systemd/system/multi-user.target.wants/nfs-server.service to /usr/lib/systemd/system/nfs-server.service.
[root@nfs01 ~]# service nfs start
Redirecting to /bin/systemctl start nfs.service
[root@nfs01 ~]# systemctl status nfs
● nfs-server.service - NFS server and services
Loaded: loaded (/usr/lib/systemd/system/nfs-server.service; enabled; vendor preset: disabled)
Active: active (exited) since Tue 2017-03-07 13:49:12 CET; 9s ago
Process: 2097 ExecStart=/usr/sbin/rpc.nfsd $RPCNFSDARGS (code=exited, status=0/SUCCESS)
Process: 2096 ExecStartPre=/usr/sbin/exportfs -r (code=exited, status=0/SUCCESS)
Main PID: 2097 (code=exited, status=0/SUCCESS)
CGroup: /system.slice/nfs-server.service

Mar 07 13:49:12 nfs01.example.com systemd[1]: Starting NFS server and servic....
Mar 07 13:49:12 nfs01.example.com systemd[1]: Started NFS server and services.
Hint: Some lines were ellipsized, use -l to show in full.
[root@ora01 ~]# mkdir -p /dnfs/oradata
[root@ora01 ~]# chown -R oracle:dba /dnfs/oradata/
[root@ora01 oradata]$ grep nfs /etc/fstab
192.168.56.102:/oraclenfs /dnfs/oradata nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600 0 0
[oracle@ora01 oradata]$ cat /u01/app/oracle/product/12.2.0.1/dbs/oranfstab
server: 192.168.56.102
path: 192.168.56.102
local: 192.168.56.101
export: /oraclenfs mount: /dnfs/oradata

Relink the Oracle Home!
[oracle@ora01 ~]$ . oraenv
ORACLE_SID = [oracle] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.2.0.1
The Oracle base has been set to /u01/app/oracle
[oracle@ora01 ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@ora01 lib]$ make -f /u01/app/oracle/product/12.2.0.1/rdbms/lib/ins_rdbms.mk dnfs_on ORACLE_HOME=/u01/app/oracle/product/12.2.0.1

Now you can create a database with dbca, don’t forget to set filesystem_options=setall and you use dnfs. In the alert.log will be a message under this text displayed, this means you have it done successfully.

[oracle@ora01 trace]$ grep Direct alert_cdb1.log
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 4.0
Direct NFS: channel id [0] path [192.168.56.102] to filer [192.168.56.102] via local [] is UP

Posted in 2017

Writing data to textfiles – UTL_FILE

DECLARE
 dir_exists NUMBER;
 dir_name VARCHAR2(50);
 file_name VARCHAR2(50);
 v_exists BOOLEAN;
 v_file_length NUMBER;
 v_blk_size BINARY_INTEGER;
 file_h UTL_FILE.file_type;
BEGIN
 dir_name := 'DATA_PUMP_DIR';
 -- get file_name
 select dbms_random.string('A',5)||'_'|| sys_context('userenv','db_name') || '.txt'
 into file_name
 from dual;

 -- Check if file exists and drop if it is
 UTL_FILE.FGETATTR (dir_name, file_name, v_exists, v_file_length, v_blk_size);
 IF v_exists THEN
   DBMS_OUTPUT.PUT_LINE('PURGING FILE: '||file_name) ;
   UTL_FILE.fremove(dir_name, file_name);
 END IF;

 -- open the file
 file_h := UTL_FILE.fopen(dir_name, file_name, 'W', 32767);

 -- get and write the data
FOR rec in (select username||';'||password as data from dba_users ) LOOP
 UTL_FILE.put_line (file_h, rec.data);
END LOOP;
 UTL_FILE.fclose (file_h);

END;
/
Posted in 2017

Snapshot Standby Sandbox

SQL@orcl_stby> alter database recover managed standby database cancel ;

Database altered.

SQL@orcl_stby> alter database convert to snapshot standby ;

Database altered.

SQL@orcl_stby> alter database open ;

Database altered.

SQL@orcl_stby> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/db1/orcl/system01.dbf
/u02/app/oracle/db1/orcl/sysaux01.dbf
/u02/app/oracle/db1/orcl/undotbs01.dbf
/u02/app/oracle/db1/orcl/users01.dbf

SQL@orcl_stby> create tablespace TEST datafile '/u02/app/oracle/db1/orcl/test.dbf' size 100m ;

Tablespace created.

SQL@orcl_stby> alter user psorger default tablespace TEST quota unlimited on test ;

User altered.

SQL@orcl_stby> connect psorger/psorger
Connected.
SQL@orcl_stby> create table TEST (ID number PRIMARY KEY, text clob) ;

Table created.

SQL@orcl_stby> insert into test select rownum, dbms_random.string('c',6000) from dual connect by level<=1000 ;

1000 rows created.

SQL@orcl_stby> commit ;

Commit complete.

SQL@orcl_stby> select index_name from user_indexes ;    

INDEX_NAME
------------------------------
SYS_C004727
SYS_IL0000070440C00002$$

SQL@orcl_stby> alter index SYS_C004727 rename to PK_ID_TEST ;

Index altered.

SQL@orcl_stby> select index_name from user_indexes ;

INDEX_NAME
------------------------------
SYS_IL0000070440C00002$$
PK_ID_TEST

SQL@orcl_stby> delete from test where rownum<=500 ;

500 rows deleted.

SQL@orcl_stby> commit ;

Commit complete.

SQL@orcl_stby> connect / as sysdba
Connected.
SQL@orcl_stby> create table psorger.test_interim as select * from psorger.test where rownum<=0 
  2  ;

Table created.

SQL@orcl_stby> BEGIN
  2  dbms_redefinition.can_redef_table('psorger','test',DBMS_REDEFINITION.CONS_USE_PK);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL@orcl_stby> 
SQL@orcl_stby> BEGIN
  2  DBMS_REDEFINITION.START_REDEF_TABLE('psorger','test','test_interim',dbms_redefinition.cons_use_pk);
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1498
ORA-06512: at line 2


SQL@orcl_stby> desc test_interim
ERROR:
ORA-04043: object test_interim does not exist


SQL@orcl_stby> desc psorger.test_interim
 Name								   Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ID									    NUMBER
 TEXT									    CLOB

SQL@orcl_stby> desc psorger.test       
 Name								   Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ID								   NOT NULL NUMBER
 TEXT									    CLOB

SQL@orcl_stby> drop table psorger.test_interim ;

Table dropped.

SQL@orcl_stby> create table psorger.test_interim (id number primary key, text clob) ;

Table created.

SQL@orcl_stby> BEGIN
  2  DBMS_REDEFINITION.START_REDEF_TABLE('psorger','test','test_interim',dbms_redefinition.cons_use_pk);
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1498
ORA-06512: at line 2

SQL@orcl_stby> begin
  2  dbms_redefinition.start_redef_table(uname=>'PSORGER',orig_table=>'TEST',int_table=>'TEST_INTERIM',options_flag=>dbms_redefinition.cons_use_pk) ;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL@orcl_stby> insert into psorger.test values (1,'KIK') ;

1 row created.

SQL@orcl_stby> commit ;

Commit complete.

SQL@orcl_stby> select count(*) from psorger.test ;

  COUNT(*)
----------
       501

1 row selected.

SQL@orcl_stby> select count(*) from psorger.test_interim ;

  COUNT(*)
----------
       500

1 row selected.

SQL@orcl_stby> DECLARE
  2  errors pls_integer ;
  3  BEGIN
  4  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('PSORGER','TEST','TEST_INTERIM',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, errors) ;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL@orcl_stby> select object_name, base_table_name, ddl_txt from
         DBA_REDEFINITION_ERRORS;  2   

OBJECT_NAME		       BASE_TABLE_NAME
------------------------------ ------------------------------
DDL_TXT
--------------------------------------------------------------------------------
PK_ID_TEST		       TEST
CREATE UNIQUE INDEX "PSORGER"."TMP$$_PK_ID_TEST0" ON "PSORGER"."TEST_INTERIM" ("

SYS_C004727		       TEST
ALTER TABLE "PSORGER"."TEST_INTERIM" ADD CONSTRAINT "TMP$$_SYS_C0047270" PRIMARY


2 rows selected.

SQL@orcl_stby> desc psorger.test_interim
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 ID						       NOT NULL NUMBER
 TEXT								CLOB

SQL@orcl_stby> select index_name from dba_indexes where owner='PSORGER';

INDEX_NAME
------------------------------
PK_ID_TEST
SYS_IL0000070440C00002$$
I_MLOG$_TEST
SYS_C004728
SYS_IL0000070447C00002$$

5 rows selected.

SQL@orcl_stby> exec dbms_redefinition.sync_interim_table('PSORGER','TEST','TEST_INTERIM') 

PL/SQL procedure successfully completed.

SQL@orcl_stby> select count(*) from PSORGER.TEST ;

  COUNT(*)
----------
       501

1 row selected.

SQL@orcl_stby> select count(*) from PSORGER.TEST_interim ;

  COUNT(*)
----------
       501

1 row selected.

SQL@orcl_stby> exec dbms_redefinition.finish_redef_table('PSORGER','TEST','TEST_INTERIM')

PL/SQL procedure successfully completed.

SQL@orcl_stby> select index_name from dba_indexes where owner='PSORGER';

INDEX_NAME
------------------------------
PK_ID_TEST
SYS_IL0000070440C00002$$
SYS_C004728
SYS_IL0000070447C00002$$

4 rows selected.

SQL@orcl_stby> select segment_name, bytes/1024/1024 mb from dba_segments where owner='PSORGER';

SEGMENT_NAME										  MB
--------------------------------------------------------------------------------- ----------
SYS_LOB0000070447C00002$$								   5
SYS_LOB0000070440C00002$$								   9
SYS_C004728									       .0625
SYS_IL0000070447C00002$$							       .0625
PK_ID_TEST									       .0625
SYS_IL0000070440C00002$$								.125
TEST										       .0625
TEST_INTERIM										.125

8 rows selected.


SQL@orcl_stby> drop table psorger.TEST_INTERIM purge ;

Table dropped.

SQL@orcl_stby> select index_name from dba_indexes where owner='PSORGER';

INDEX_NAME
------------------------------
SYS_C004728
SYS_IL0000070447C00002$$

2 rows selected.

SQL@orcl_stby>

SQL@orcl_stby> SELECT 
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;  2    3    4  

PERCENT_FULL
------------
	 5.8

	 
SQL@orcl_stby> select * from v$recovery_file_dest ;

NAME
------------------------------------------------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/u02/app/oracle/arch1/recovery
 3221225472  186630144		       0	       5

SQL@orcl_stby> !ls -l /u02/app/oracle/arch1/recovery/ORCL/flashback
total 153616
-rw-r-----. 1 oracle dba 78651392 Apr 29 12:28 o1_mf_bn1dh6tq_.flb
-rw-r-----. 1 oracle dba 78651392 Apr 29 12:26 o1_mf_bn1dh9qs_.flb

SQL@orcl_stby>

SQL@orcl_stby> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL@orcl_stby> startup mount
ORACLE instance started.

Total System Global Area  367439872 bytes
Fixed Size		    2253344 bytes
Variable Size		  117444064 bytes
Database Buffers	  243269632 bytes
Redo Buffers		    4472832 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode


SQL@orcl_stby> !
Classic layout determined.

+++   MSH - Version 2014.06   +++

cFollowing aliases were set:
cdscr  - change to scriptdir
cdscrl  - change to local scriptdir
cdlog  - change to logdir
oram   - set environment for ORACLE_SID
         interactive or with the ORACLE_SID as parameter
cdoh   - change to ORACLE_HOME of current DB
cdnet  - change to directory ../network/admin of current DB
cdXX   - change to dedicated directories, XX stands for:
         bd(bdump),ud(udump),aud(audit_file_dest),dbs(ORAHOME/dbs)
cddat  - change to parent datafile directory of current DB
cdarc  - change to archive directory of current DB
talert - continous view on alert-Log of current DB
valert - open alert-Log of current DB (view)
vinit  - edit init.ora/spfile.ora of current DB
s+     - command: sqlplus '/ as sysdba' (inkl. Status)
         remote connection via SQL*NET: s+ <ORACLE_SID>
syspw  - set environment variable SYSPW
vicron - editing crontab, with backupfile
do_sql   - SQL Shellfunc / as sysdba, do_sql "select...;" pipe-able
amon     - Oracle Performance Tool AMON
s++      - MySQLPLUS - Comfortable SQL-Plus
rman++   - Comfortable rman
dgmgrl++ - Comfortable dgmgrl
asmcmd++ - Comfortable ascmd
adrci++  - Comfortable adrci
dCurrent ORACLE_SID is orcl_stby.
oracle@test$ cddbs
current directory: /u01/app/oracle/product/11.2.0.4/dbs
oracle@test$ rm lkORCL # lebo mam standby s tym istym dbnamemom na servri a vytvara mi vzdy ten isty lock file - workaround
oracle@test$ exit
exit

SQL@orcl_stby> alter database mount standby database ;
alter database mount standby database
*
ERROR at line 1:
ORA-01665: control file is not a standby control file


SQL@orcl_stby> alter database mount ;

Database altered.

SQL@orcl_stby> alter database convert to physical standby ;

Database altered.

SQL@orcl_stby> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL@orcl_stby> startup mount
ORACLE instance started.

Total System Global Area  367439872 bytes
Fixed Size		    2253344 bytes
Variable Size		  117444064 bytes
Database Buffers	  243269632 bytes
Redo Buffers		    4472832 bytes
Database mounted.
SQL@orcl_stby> alter database recover managed standby database using current logfile disconnect ;

Database altered.

oracle@test$ oram
oram runs with /etc/oratab
SID-SETUP
Choose SID:
NR   SID      RUNNING  # COMMENT
 1 - orcl_stby <*>      
 2 - orcl     <*>      

Enter number or SID (type 'end' to exit):
ORACLE_SID =  ? 1
ORACLE_SID:  orcl_stby
ORACLE_HOME: /u01/app/oracle/product/11.2.0.4
oracle@test$ oram 2
oram runs with /etc/oratab
Temporary init.ora /tmp/pfileorcl_oracle.tmp created
ORACLE_SID:  orcl
ORACLE_HOME: /u01/app/oracle/product/11.2.0.4
oracle@test$ s+
SQL@orcl> select table_name from dba_tables where owner='PSORGER';

no rows selected

SQL@orcl> 

Posted in 2017

Call me Expert

Yeees, I got my Expert level recently on community.oracle.com ..
I would like to thank everyone who believes in me, it was a hard path to get 10 correct answers and 10 Helpful 😀 with this mission I finally moved to the Expert level. Thanks again