DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, August 21, 2018

EM 13.2: opatch lsinventory Command Fails With Error "RawInventory gets a null Vector of one-off entries



1. Error  Message:-


I faced with that error message while I was trying to apply 13.2.0.0.0 patch 

[oracle@TESTOEM]$export ORACLE_HOME=/u01/app/oracle/product/agent13c/agent_13.2.0.0.0
[oracle@TESTOEM]$export PATH=$PATH:/u01/app/oracle/product/agent13c/agent_13.2.0.0.0/OPatch/

[oracle@TESTOEM]$ opatch lsinventory
Oracle Interim Patch Installer version 13.8.0.0.0
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/agent13c/agent_13.2.0.0.0
Central Inventory : /etc/oraInventory
   from           : /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/oraInst.loc
OPatch version    : 13.8.0.0.0
OUI version       : 13.8.0.0.0
Logfilelocation : /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/cfgtoollogs/opatch/opatch2018-08-20_21-56-48PM_1.log


OPatch detects the Middleware Home as "/u01/app/oracle/product/agent13c"

LsInventorySession failed: RawInventory gets a null Vector of one-off entries

OPatch failed with error code 73

Lets check log file:-

[oracle@TESTOEM]$cat /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/cfgtoollogs/opatch/opatch2018-08-20_21-56-48PM_1.log
[Aug20,20189:56:50PM]OPatchinvokedasfollows:'lsinventory-invPtrLoc /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/oraInst.loc '
[Aug 20, 2018 9:56:50 PM]    OUI-67077:
                             Oracle Home       : /u01/app/oracle/product/agent13c/agent_13.2.0.0.0
                             Central Inventory : /etc/oraInventory
                                from           : /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/oraInst.loc
                             OPatch version    : 13.8.0.0.0
                             OUI version       : 13.8.0.0.0
                             OUI location      : /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/oui
Logfilelocation: /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/cfgtoollogs/opatch/opatch2018-08-20_21-56-48PM_1.log
[Aug20,20189:56:50PM]Patchhistoryfile: /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/cfgtoollogs/opatch/opatch_history.txt
[Aug20,20189:56:50PM]OPatchdetectstheMiddlewareHomeas "/u01/app/oracle/product/agent13c"
[Aug 20, 2018 9:56:50 PM]    Starting LsInventorySession at Mon Aug 20 21:56:50 BRT 2018
[Aug 20, 2018 9:56:51 PM]    [OPSR-MEMORY-1] : after installInventory.getAllCompsVect() call : 121 (MB)
[Aug 20, 2018 9:56:51 PM]    OUI-67073:LsInventorySession failed: RawInventory gets a null Vector of one-off entries
[Aug 20, 2018 9:56:51 PM]    Finishing LsInventorySession at Mon Aug 20 21:56:51 BRT 2018
[Aug 20, 2018 9:56:51 PM]    Stack Description: java.lang.RuntimeException: RawInventory gets a null Vector of one-off entries
[Aug20,20189:56:51PM]StackTrace:oracle.opatch.ipm.RawInventory.loadRawInventory(RawInventory.java:234)
[Aug20,20189:56:51PM]StackTrace:oracle.opatch.ipm.RawInventory.init(RawInventory.java:171)
[Aug20,20189:56:51PM]StackTrace:oracle.opatch.ipm.RawInventory.getInstance(RawInventory.java:87)
[Aug20,20189:56:51PM]StackTrace:oracle.opatch.ipm.IPMReadServices.getRawInventory(IPMReadServices.java:149)
[Aug20,20189:56:51PM]StackTrace:oracle.opatch.ipm.IPMReadServices.loadInventory(IPMReadServices.java:1031)
[Aug20,20189:56:51PM]StackTrace:oracle.opatch.LsInventorySession.process(LsInventorySession.java:317)
[Aug20,20189:56:51PM]StackTrace:oracle.opatch.OPatchSession.main(OPatchSession.java:2115)
[Aug 20, 2018 9:56:51 PM] StackTrace: oracle.opatch.OPatch.main(OPatch.java:640)

2. Error  Message:-

[oracle@TESTOEM]$cd 28070111
[oracle@TESTOEM]
[oracle@TESTOEM]$opatch napply
Oracle Interim Patch Installer version 13.8.0.0.0
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/agent13c/agent_13.2.0.0.0
Central Inventory : /etc/oraInventory
   from           : /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/oraInst.loc
OPatch version    : 13.8.0.0.0
OUI version       : 13.8.0.0.0
Logfilelocation: /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/cfgtoollogs/opatch/opatch2018-08-20_22-08-19PM_1.log


OPatch detects the Middleware Home as "/u01/app/oracle/product/agent13c"

UtilSession failed: RawInventory gets a null Vector of one-off entries
Logfilelocation: /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/cfgtoollogs/opatch/opatch2018-08-20_22-08-19PM_1.log

OPatch failed with error code 73

SOLUTION:-  So solution avaliable at note (Doc ID 2247800.1)

Download oracle.eons.client.zip from this Document attachment.
On the agent host Extract it  <AGENT_BASE_DIR>/agent_13.2.0.0.0/inventory/Components/
Ensure you have drwxr----- permissions for oracle.eons.client directory.
Restart the agent
Retry the plugin deploy or opatch operations, which should now work fine


[oracle@TESTOEM]$cd /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/inventory/Components
[oracle@TESTOEM]$ls -lrt oracle.eons.client
ls: cannot access oracle.eons.client: No such file or directory
[oracle@TESTOEM]$ls
oracle.apache.commons.cli       oracle.jdk                    oracle.sysman.agent.ic
oracle.apache.commons.compress  oracle.jre                    oracle.sysman.common
oracle.bali.ice                 oracle.jrf.dms                oracle.sysman.darda
oracle.bali.jewt                oracle.jrf.dms.common         oracle.sysman.db.agent.plugin
oracle.bali.share               oracle.jrf.j2ee               oracle.sysman.db.discovery.plugin
oracle.buildtools.rsf           oracle.jse.dms                oracle.sysman.emagent.installer
oracle.glcm.comdev              oracle.ldap.rsf               oracle.sysman.emas.discovery.plugin
oracle.glcm.dependency          oracle.nginst.common          oracle.sysman.oh.agent.plugin
oracle.glcm.encryption          oracle.nginst.core            oracle.sysman.oh.discovery.plugin
oracle.glcm.logging             oracle.nginst.osp.compstager  oracle.sysman.sbin
oracle.glcm.opatchauto.core     oracle.nginst.thirdparty      oracle.sysman.si.discovery.plugin
oracle.glcm.opatch.common.api   oracle.ons                    oracle.sysman.top.agent
oracle.glcm.oplan.core          oracle.opss.core              oracle.sysman.xa.discovery.plugin
oracle.glcm.osys.core           oracle.osdt.core              oracle.wsm.common
oracle.glcm.wizard              oracle.perlint                oracle.xdk.jrf
oracle.glcm.xmldh               oracle.perlint.expat          oracle.xdk.jrf.fmw
oracle.help.ohj                 oracle.perlint.modules        oracle.xdk.jrf.jaxp
oracle.help.share               oracle.pki                    oracle.xdk.jrf.xmlparserv2
oracle.http_client              oracle.rsa.crypto
oracle.jcraft.jsch              oracle.swd.opatch
[oracle@TESTOEM]$


[oracle@TESTOEM]$cd /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/inventory/Components
[oracle@TESTOEM]$unzip oracle.eons.client.zip
Archive:  oracle.eons.client.zip
   creating: oracle.eons.client/
   creating: oracle.eons.client/11.2.0.2.0/
  inflating: oracle.eons.client/11.2.0.2.0/compDef.xml
   creating: oracle.eons.client/11.2.0.2.0/resources/
  inflating: oracle.eons.client/11.2.0.2.0/resources/CompID.properties
  
[oracle@TESTOEM]$export ORACLE_HOME=/wwi/wwdb/db/oracle/product/agent13c/agent_13.2.0.0.0
[oracle@TESTOEM]$export PATH=$PATH:/wwi/wwdb/db/oracle/product/agent13c/agent_13.2.0.0.0/OPatch
[oracle@TESTOEM]$opatch lsinventory
Oracle Interim Patch Installer version 13.8.0.0.0
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /wwi/wwdb/db/oracle/product/agent13c/agent_13.2.0.0.0
Central Inventory : /etc/oraInventory
   from           : /wwi/wwdb/db/oracle/product/agent13c/agent_13.2.0.0.0/oraInst.loc
OPatch version    : 13.8.0.0.0
OUI version       : 13.8.0.0.0
Logfilelocation : /wwi/wwdb/db/oracle/product/agent13c/agent_13.2.0.0.0/cfgtoollogs/opatch/opatch2018-08-20_22-46-53PM_1.log


OPatch detects the Middleware Home as "/wwi/wwdb/db/oracle/product/agent13c"

LsinventoryOutputfilelocation : /wwi/wwdb/db/oracle/product/agent13c/agent_13.2.0.0.0/cfgtoollogs/opatch/lsinv/lsinventory2018-08-20_22-46-53PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: crm.hpseb.com
ARU platform id: 226
ARU platform description:: Linux_AMD64

Interim patches (1) :

Patch  24470104     : applied on Tue Aug 14 23:19:21 BRT 2018
Unique Patch ID:  -1471524899
   Created on 18 Aug 2016, 05:54:59 hrs PST8PDT
   Bugs fixed:
     24011099, 19030178
-------------------------------------------------------------------------------
[oracle@[oracle@TESTOEM]$ opatch apply
Oracle Interim Patch Installer version 13.8.0.0.0
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/agent13c/agent_13.2.0.0.0
Central Inventory : /etc/oraInventory
   from           : /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/oraInst.loc
OPatch version    : 13.8.0.0.0
OUI version       : 13.8.0.0.0
Logfilelocation: /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/cfgtoollogs/opatch/28033677_Aug_20_2018_22_49_53/apply2018-08-20_22-49-50PM_1.log


OPatch detects the Middleware Home as "/wwi/wwdb/db/oracle/product/agent13c"

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   28033677

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '28033677' to OH '/u01/app/oracle/product/agent13c/agent_13.2.0.0.0'

Patching component oracle.sysman.top.agent, 13.2.0.0.0...
Patch 28033677 successfully applied.
Logfilelocation: /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/cfgtoollogs/opatch/28033677_Aug_20_2018_22_49_53/apply2018-08-20_22-49-50PM_1.log

OPatch succeeded.
[oracle@[oracle@TESTOEM]$ opatch lsinventory
Oracle Interim Patch Installer version 13.8.0.0.0
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/agent13c/agent_13.2.0.0.0
Central Inventory : /etc/oraInventory
   from           : /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/oraInst.loc
OPatch version    : 13.8.0.0.0
OUI version       : 13.8.0.0.0
Logfilelocation: /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/cfgtoollogs/opatch/opatch2018-08-20_22-50-10PM _1.log


OPatch detects the Middleware Home as "/wwi/wwdb/db/oracle/product/agent13c"

LsinventoryOutputfilelocation: /u01/app/oracle/product/agent13c/agent_13.2.0.0.0/cfgtoollogs/opatch/lsinv/lsinventory2018-08-20_22-50-10PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: crm.hpseb.com
ARU platform id: 226
ARU platform description:: Linux_AMD64


Interim patches (4) :

Patch  28033677     : applied on Mon Aug 20 22:49:58 BRT 2018
Unique Patch ID:  22252118
   Created on 11 Jun 2018, 23:14:05 hrs PST8PDT
   Bugs fixed:
     28033677

Patch  28070111     : applied on Mon Aug 20 22:49:38 BRT 2018
Unique Patch ID:  22266020
Patch description:  "EM-AGENT Bundle Patch 13.2.0.0.180630"
   Created on 18 Jun 2018, 08:52:39 hrs PST8PDT
   Bugs fixed:
     28070111, 27830172, 27958551, 27116149, 27599957, 27926151, 27116631
     27686784, 27800014, 27653690, 27723689, 27588907, 27473349, 27136454
     22877528, 27585167, 27642807, 27394028, 27471627, 27418842, 27101290
     27194429, 21967024, 27446771, 27305941, 25738670, 27049704, 26665900
     23168590, 27126169, 26661721, 27319514, 26798314, 27160866, 27119470
     25265547, 24940113, 25735685, 25818326, 27031053, 25931499, 26626166
     24437923, 26235110, 26726096, 26445309, 26768840, 24661253, 26649994
     26723536, 26368549, 25298541, 25759841, 25750667, 26448382, 26584413
     26420071, 24751845, 26269529, 26367400, 26183443, 25206274, 25535160
     26302989, 26121439, 24798029, 25786643, 25632008, 26113746, 23756848
     26084422, 25704980, 25603661, 25829879, 25950674, 25136887, 25924717
     25070243, 25907307, 25922043, 25607480, 25740081, 25450062, 25460315
     25656802, 25337437, 25484249, 23638365, 25580746, 25718287, 25414194
     23478148, 25297713, 25342677, 25125856, 25113676, 24598039, 25291403
     25214037, 25105153, 25105107, 25185188, 24672237, 24653896, 24934732
     23268700, 25105555, 24415605, 24303614, 24697787, 24588124, 24456370
     24447221, 24841013, 24923633, 22504387

Patch  25610702     : applied on Mon Aug 20 22:49:31 BRT 2018
Unique Patch ID:  22266020
   Created on 28 Feb 2017, 09:35:34 hrs PST8PDT
   Bugs fixed:
     25610702

Patch  24470104     : applied on Tue Aug 14 23:19:21 BRT 2018
Unique Patch ID:  -1471524899
   Created on 18 Aug 2016, 05:54:59 hrs PST8PDT
   Bugs fixed:
     24011099, 19030178

--------------------------------------------------------------------------------
OPatch succeeded.

Monday, August 20, 2018

RMAN errors and their Solution

Backup Fails with Invalid RECID Error: RMAN-20035, RMAN-6038
When you attempt a backup and receive the following error messages:
RMAN-3014: Implicit resync of recovery catalog failed
RMAN-6038: Recovery catalog package detected an error
RMAN-20035: Invalid high RECID error
It indicates the control file and the recovery catalog is not synchronized. RMAN detects that the control file currently in use is older than the control file previously used to resynchronize.

Cause:
This due to any of the scenario you are restore a backup controlfile through a non-oracle mechanism and then open the database through Resetlogs option or making a copy of control file through operating system utility and trying the restore on new system through RMAN. You do not use catalog so RMAN does not get any information regarding this process.  The recovery catalog indicates that the highest RECID is 100, but the control file indicates that the highest RECID is 90. The control
file RECID should always be greater than or equal to the recovery catalog RECID, so RMAN issues RMAN-20035

Solution:
Login with sysdba:
sys/oracle@orcl3
Mount the database
SQL> shutdown immediate;
SQL> startup mount;
1.      Perform cancel based recovery to open the database with Resetlogs option.
Alter Database Recover Database until Cancel Using Backup Controlfile;
Alter Database Recover Cancel;
2.      Then connect to the rman and open the database using Resetlogs option
CONNECT rsys/oracle@target catalog catalog/catalog@rman
RMAN> Alter database open resetlogs;
3.      Do not forget to take the fresh backup after resetlogs option;

Backup Fails Because of Control File Enqueue: ORA-00230
In this scenario, a backup job fails because RMAN cannot make a snapshot control file. The message stack is as follows:
RMAN-00571: =======================================
RMAN-00569: ====== ERROR MESSAGE STACK FOLLOWS =========
RMAN-00571: =======================================
RMAN-03002: failure of backup command at 08/20/2018 06:48:44

ORA-00230: operation disallowed: snapshot control file enqueue unavailable
Cause:

When RMAN needs to back up or resynchronize from the control file, it first creates a snapshot or consistent image of the control file. If one RMAN job is already backing up the control file while another needs to create a new snapshot control file, then you may see the following message:
Waiting for snapshot control file enqueue
Under normal circumstances, a job that must wait for the control file enqueue waits for a brief interval and then successfully obtains the enqueue. RMAN makes up to five attempts to get the enqueue and then fails the job. The conflict is usually caused when two jobs are both backing up the control file, and the job that first starts backing up the control file waits for service from the media manager.

Solution:
To determine which job is holding the conflicting enqueue:
1.      Start a new SQL*Plus session and login with the sysdba:
2.      Execute the query to check the waiting cause:
SELECT s.SID, USERNAME AS "User", PROGRAM, MODULE,
ACTION, LOGON_TIME "Logon", l.*
FROM V$SESSION s, V$ENQUEUE_LOCK l
WHERE l.SID = s.SID
AND l.TYPE = 'CF'
AND l.ID1 = 0 AND l.ID2 = 2;
The output is look like as: (if there is lock)
SID User Program              Module                    Action           Logon
--- ---- -------------------- ------------------- ---------------- ---------
4 SYS rman@rman (TNS V1-V3) backup full datafile: c10000210 STARTED 19-AUG-18
This situation generally came across when a job is writing to a tape drive, but the tape drive is waiting for new tape to be inserted. In the mean time if you start new job then you will probably receive the enqueue message because the first job cannot complete until the new tape is loaded.
After you have determined which job is creating the enqueue, you can do one of the following:
§         Wait until the job holding the enqueue completes
§         Cancel the current job and restart it after the job holding the enqueue completes
§         Cancel the job creating the enqueue


RMAN Does Not Recognize Character Set Name: PLS-00553
While working with Sadhan I configure the rman backup server and start taking different production DB backup. In one fine morning one of our database break down needs media recovery. I connected the rman to target database and try to restore the database but I receive the following error while using the “restore database” command.
RMAN-03002: failure during compilation of command
RMAN-03013: command type: send
RMAN-06003: ORACLE error from target database: ORA-06550: line 1, column 51:
PLS-00553: character set name is not recognized
Cause:
After searching about this error I found RMAN failed to compile the SEND statement because the NS_LANG of the target database differed from the NS_LANG of the recovery catalog.  In the environment or shell script, set and export NS_LANG to the correct value for the recovery catalog so that RMAN receives the correct value.
Solution:
1. Query the target database to determine NLS_CHARACTERSET parameter.
SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
VALUE
-------------
AR8MSWIN1256
Try same Query with the rman catalog database I found the database character set is same as the production database here also.
2. Set the character environment variable in the rman catalog system same as the server.
% setenv NLS_LANG american_america.we8dec
% setenv NLS_DATE_FORMAT "MON DD YYYY HH24:MI:SS"
In windows you can go through the registry to setup environment variable:
HKEY_LOCAL_MACHINE –> SOFTWARE –> ORACLE –> NLS_LANG
also check from the below path:
My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)
Note: If the connection is made through a listener, then the listener must be started with the correct Globalization Support settings. Otherwise, the spawned connections inherit the incorrect Globalization Support settings from the listener.

Database Duplication Fails with error: RMAN-06053, RMAN-06025
When I try to duplicate database first time using RMAN the duplicate command is failed with following error:
RMAN-03002: failure of Duplicate Db command at 07/28/2010 23:11:44
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 12 lowscn 487272 found to restore

Cause:
RMAN unable to start archive log restore to default destination. The problem is that rman is not able to apply the entire archive log needed for complete recovery. For example, if you only backed up logs through sequence 15, but the most recent archived log is sequence 16, then DUPLICATE fails.

Solution:
When creating the duplication script either, use the SET UNTIL command to specify a log sequence number for incomplete recovery or include NOFILENAMECHECK with the duplicate command. For example, to terminate recovery after applying log sequence 15, enter:
RUN
{
  SET UNTIL SEQUENCE 16 THREAD 1; #recovers up to but not including log 16
  DUPLICATE TARGET DATABASE TO 'dupdb';
}
-or- add NOFILENAMECHECK with duplicate database command:
DUPLICATE TARGET DATABASE TO "clone" NOFILENAMECHECK;
For Example:
RMAN> DUPLICATE TARGET DATABASE TO "clone";
When NOFILENAMECHECK is used with the DUPLICATE command, RMAN does not validate the filenames during restoration.
Note: If the primary database and the standby database are on the same host, The DUPLICATE NOFILENAMECHECK option should not be used.

Write error on file: ORA-19502:
While trying to take the backup through the RMAN some times we are facing the below error.
RMAN> backup database plus archivelog;
channel ORA_DISK_1: starting piece 1 at 20-AUG-18
RMAN-00571: ========================================
RMAN-00569: ========= ERROR MESSAGE STACK FOLLOWS =========
RMAN-00571: =========================================
RMAN-03002: failure of backup command at 20/08/2012 07:05:04
ORA-19502: write error on file "4crcv3st_1_1", blockno 5354 (blocksize=8192)
Solution:
As the name it is clear. It is a write error on output file. Check the file then try again.
Check the location parameter default setting by issuing a command like:
Configure channel 1 device type disk format 'H:\oraback\backup_%U';
It is also possible the backup will be attempting to write to a larger disk space than is available; there is not enough disk space on the drive to complete the backup.

Failed to identify file in RMAN: ORA-19505
After configuring the target database with the RMAN first time. I try to take database backup through RMAN and found the following error:
RMAN-00571: ============================================
RMAN-00569: ============= ERROR MESSAGE STACK FOLLOWS ========
RMAN-00571: ============================================
RMAN-03009: failure of backup command on ch00 channel at 20/08/2018 07:01:03
ORA-19505: failed to identify file “/oraback/arch/1_1100721_664058960.dbf”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
In this case nothing happened with your backup command. If you read the error, it is saying particular archive logfile is not available. This may be due to file deleted at OS level due to some reason. In this situation, do the following
1. Run crosscheck command against archivelogs
RMAN>Crosscheck archivelog all;
2. If you find any archives marked as EXPIRED, then delete those expired archives
RMAN>Delete expired archivelog all;
This will prompt you YES or NO. If you don’t want a prompt
RMAN>Delete noprompt archivelog all;
3. Then take a fresh archivelog backup
RMAN>Backup archivelog all;

RMAN-06172: no autobackup found tips

Recently I got the issue from one of the user. He is getting the RMAN-06172 error with the restore spfile command in case of disaster recovery. He is using oracle 10g on windows 2003. He states he has not tested his backup before

RMAN> restore spfile from autobackup;

RMAN-03002: failure of restore command at 20/08/2018 07:32:11

RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

Cause:
A restore could not proceed because no autobackup was found or the specified handle is not a valid copy or backup piece.
1.      In case of restore from autobackup, it may be the case that a backup exists, but it does not find the location of backup due to not specify the real backup location.
2.      In case of restore from backup, it may be the backup is not a backup piece or controlfile copy. It may be that it does not exist.
Solution:
The above error occurs because RMAN does not find the control file or spfile location. Normally Oracle looks the backup by default in the ORACLE_HOME/dbs directory if you are not proving the destination with the spfile restore command. You need to change your RMAN command to point to the real spfile location with the restore command.
RMAN> Restore spfile from autobackup db_recovery_file_dest='D:\oracle\flash_recovery_area' db_name=’ORCL3’;
In the case when FRA is not configured and autobackup control file location is not set
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
RMAN> Configure controlfile autobackup on;
The backupset and autobackup controlfile is created in default windows location: "C:\windows\system32\", with DBID. For Example:
C:\WINDOWS\SYSTEM32\C-1681257132-20130102-02 comment=NONE
RMAN-06207, RMAN-06208, RMAN-06210 errors
Sometimes we are facing errors from RMAN for Oracle backup as following.
RMAN-06207: WARNING: 2 objects could not be deleted for DISK channel(s) due to mismatched status.
RMAN-06208: Use CROSSCHECK command to fix status
Solution:
To solve  RMAN-06207, RMAN-06208, RMAN-06210 errors perform crosscheck command execution from rman prompt. This command will update RMAN’s metadata and catalog.
RMAN>crosscheck archivelog all;
If this command error doesn’t resolved then delete command can be helpful in this situation.
RMAN>DELETE FORCE OBSOLETE;
If above all commands do not working then kindly check from where are you executing above commands.
Because standby archives doesn’t register in primary database. For this situation execute above commands in standby and primary both with “crosscheck archivelog all” command also.

RMAN-05542: Only UNTIL TIME can be used with DUPLICATE without TARGET and CATALOG connections

While trying to use RMAN DUPLICATE DATABASE command without connecting to target or catalog database, we are getting the following error:

DUPLICATE DATABASE to TEST1
until SCN 3587956
SPFILE
SET CONTROL_FILES='D:\ORACLE\oradata\RAM\control01.ctl'
set db_file_name_convert='D:\ORACLE\ORADATA\RAM','D:\ORACLE\ORADATA\TEST'
set log_file_name_convert='D:\ORACLE\ORADATA\RAM ','D:\ORACLE\ORADATA\TEST'
backup location 'H:\ORABACK\RAM';
Cause: Only UNTIL TIME can be used with DUPLICATE without TARGET and CATALOG connections or the situation if you specify UNTIL clause but not putting the time based.
Action: Retry the command with an UNTIL TIME clause or without an UNTIL clause.
Solution: When we change UNTIL SCN to UNTIL TIME with specified parameter details, duplication process was successful.
DUPLICATE DATABASE to TEST1
UNTIL TIME "To_Date('20-AUG-2018 07:45:28','DD-MON-YYYY HH24:MI:SS')"
SPFILE
SET CONTROL_FILES='D:\ORACLE\oradata\RAM\control01.ctl'
set db_file_name_convert='D:\ORACLE\ORADATA\RAM','D:\ORACLE\ORADATA\TEST'
set log_file_name_convert='D:\ORACLE\ORADATA\RAM ','D:\ORACLE\ORADATA\TEST'
backup location 'H:\ORABACK\RAM';

Automatic Startup and Shutdown the Oracle Database and Listener on Linux

Automatic Shutdown and Startup of Oracle Database and Listener on Linux

We need to edit the /etc/oratab file and make sure that the databases are set to Y.

ora11g:/u01/app/oracle/product/11.2.0/dbhome_1:Y
ora12c:/u01/app/oracle/product/12.1.0/dbhome_1:Y

Next we will create a script to take advantage of the dbshut and dbstart commands when you are shutting down or rebooting the server.

vi /etc/init.d/dbora


#!/bin/sh
# chkconfig: 345 99 10
# description: Service to start and stop Oracle Database and Listener
#
# processname: oracle
# config: /etc/oratab
# pidfile: /var/run/oracle.pid

# Source function library.
. /etc/init.d/functions

RETVAL=0
ORA_OWNER="oracle"
ORA_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"

# See how we were called.

prog="oracle"

start() {
echo -n $"Starting $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/dbora

return $RETVAL
}

stop() {
echo -n $"Stopping $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -r /var/lock/subsys/dbora

return $RETVAL
}

restart() {
stop
start
}

case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
echo $"Usage: $0 {start|stop|restart}"
exit 1
esac

exit $?

Now change permissions and make the service active.

[root@oracle ~]# chgrp  dba /etc/init.d/dbora
[root@oracle ~]# chmod 750 /etc/init.d/dbora
[root@oracle ~]# chkconfig --level 345 dbora on

BLACKOUT FOR MULTIPLE TARGETS ON EM13C

BLACKOUT FOR MULTIPLE TARGETS ON EM13C


The notifications emails of a “planned” downtime is one of the annoying things of monitoring systems. You forget creating blackout and you start a maintenance work, and you get lots of notifications mails. Most of the time, the target which goes down, also affect other targets so it will multiple the number of unwanted notifications mails. Good thing is, it’s very easy to create blackouts on EM13c. We can do it through web console, emctl or emcli tools. A one-liner is enough for it:
emctl start blackout BlackOutName -nodeLevel  
The above code will create a blackout for the all targets on the server. We can achieve the same thing by EMCLI:
emcli create_blackout -name="BlackOutName" -reason="you know we need to do it" -add_targets=
"myserver:host" -propagate_targets -schedule="duration:-1" # indefinite
If we use emcli, we have more options such as creating repeating blackouts, entering a reason for blackout, enabling blackout for a group of targets (which resides on different hosts).
What if we need to create blackout for multiple targets. As I mentioned, EMCLI can be used to create blackout for groups. We can create groups on EM13c, and instead of passing names of all targets in a group, we can give the group name:
# # Sample EMCLI script file to create blackout for multiple targets # # check number of arguments if len(sys.argv) <> 2: print "Usage to start a blackout: emcli @multiblackout.py targets.csv blackout_name" print "Usage to stop a blackout: emcli @multiblackout.py stop blackout_name" exit() blackout_name = sys.argv[1].upper() # login to OMS login( username="SYSMAN", password="yoursupersecretpassword" ) if sys.argv[0].lower() == "stop": stop_blackout( name= blackout_name ) # comment below line to keep old blackouts delete_blackout( name = blackout_name ) print "%s blackout stopped and deleted." % blackout_name exit() # open file for reading f = open( sys.argv[0], 'r' ) # variable to keep all targets alltargets = "" # loop for each line of the file for target in f: # build alltargets string alltargets += target.replace("\n","") + ";" create_blackout( name = blackout_name, add_targets=alltargets, reason=blackout_name,
schedule="duration:-1" )


The script accepts two parameters. First parameter is the path of the file containing the targets, the second parameter is the name of the blackout. The targets file should be something like this:

DBNAME1:oracle_database DBNAME2:oracle_database MYHOST5:host

After you create a blackout, you can stop (and delete) the blackout by running it again, but this time you need to enter “stop” as the file name:

./emcli @multiblackout.py /home/oracle/mytargets.txt TESTBLACKOUT   
./emcli @multiblackout.py stop TESTBLACKOUT 


I’m sharing a simple version so you can write your own (and better) script.

Goldengate: REPLICAT ABENDS OGG-00423 Could not find definition for demo.xxxx

REPLICAT ABENDS OGG-00423 Could not find definition for demo.xxxx

This is a case-sensitivity issue. The object name in defgen file is treated as case-insensitive name and could not find the table definition.

Remove the asterisk key from the REPLICAT parameter file, eg:

MAP demo.*,  TARGET demo.*,

Instead of asterisk key and define the table name from the REPLICAT parameter file.

MAP demo.table1,  TARGET demo.table1,

Goldengate: ./ggsci: error while loading shared libraries: libclntsh.so.12.1: cannot open shared object file: No such file or directory

Error while loading shared libraries: libclntsh.so.12.1: cannot open shared object file: No such file or directory

[oracle@ggsource.doyensys.com]$ ./ggsci
./ggsci: error while loading shared libraries: libclntsh.so.12.1: cannot open shared object file: No such file or directory


Go to the GoldenGate installation directory "/u01/gghome" and execute ldd ggsci command.
This command will help us to list out the missing shared library files.

[oracle@ggsource.doyensys.com]$ ldd ggsci
        linux-vdso.so.1 =>  (0x00007fffac7cb000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f3fedbd9000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f3fed9d4000)
        libgglog.so => /u01/gghome./libgglog.so (0x00007f3fed5b0000)
        libggrepo.so => /u01/gghome./libggrepo.so (0x00007f3fed33e000)
        libdb-6.1.so => /u01/gghome./libdb-6.1.so (0x00007f3fecf59000)
        libggperf.so => /u01/gghome./libggperf.so (0x00007f3fecd29000)
        libggparam.so => /u01/gghome./libggparam.so (0x00007f3febc1f000)
        libicui18n.so.48 => /u01/gghome./libicui18n.so.48 (0x00007f3feb82f000)
        libicuuc.so.48 => /u01/gghome./libicuuc.so.48 (0x00007f3feb4ae000)
        libicudata.so.48 => /u01/gghome./libicudata.so.48 (0x00007f3fe9ce9000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f3fe9acc000)
        libxerces-c.so.28 => /u01/gghome./libxerces-c.so.28 (0x00007f3fe9506000)
        libantlr3c.so => /u01/gghome./libantlr3c.so (0x00007f3fe92ed000)
        libnnz12.so => not found
        libclntsh.so.12.1 => not found
        libons.so => not found
        libclntshcore.so.12.1 => not found
        libggnnzitp.so => /u01/gghome./libggnnzitp.so (0x00007f3fe880d000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f3fe850b000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f3fe8148000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f3feddea000)
        libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f3fe7e3f000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f3fe7c28000)




Now check the missing library files and create the soft link for those library's.

[oracle@ggsource.doyensys.com]$ cd $ORACLE_HOME/lib/
[oracle@ggsource.doyensys.com lib]$ ls -tlr libnnz12.so
-rwxr-xr-x 1 oracle oinstall 6318657 Aug  1 02:08 libnnz12.so
[oracle@Node 1 lib]$ cd -

[oracle@ggsource.doyensys.com]$ ln -s $ORACLE_HOME/lib/libnnz12.so libnnz12.so
[oracle@ggsource.doyensys.com]$ pwd
/u01/gghome

[oracle@ggsource.doyensys.com]$ ln -s $ORACLE_HOME/lib/libclntsh.so.12.1 libclntsh.so.12.1
[oracle@ggsource.doyensys.com]$ ln -s $ORACLE_HOME/lib/libons.so libons.so
[oracle@ggsource.doyensys.com]$ ln -s $ORACLE_HOME/lib/libclntshcore.so.12.1 libclntshcore.so.12.1

Now invoke the GoldenGate ggsci
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2018 02:56:48
Operating system character set identified as UTF-8.

Goldengate: ERROR OGG-08221 - Cannot Register Or Unregister EXTRACT In Oracle GoldenGate

OGG-08221 - Cannot Register Or Unregister EXTRACT In Oracle GoldenGate:

Supplemental logging wasn't enable at the database level. Enable DB supplemental logging.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

GGSCI (ggsource.doyensys.com) 2> dblogin userid gguser@ggsource, password *****
Successfully logged into database.

GGSCI (ggsource.doyensys.com) 3> register extract EXT1 database

2018-08-11 05:20:33 INFO OGG-02003 Extract EXT1 successfully registered with database at SCN 156672.

Goldengate: ERROR: Unable to connect to database using user ggate. Please check privileges. allocating error handle.

Unable To Connect To Database Using User <goldengate user>. Please Check Privileges.

GGSCI (ggsource.doyensys.com) 5> dblogin  USERID gguser, PASSWORD xxxxxxxx
ERROR: Unable to connect to database using user ggate. Please check privileges.
allocating error handle.

Golden gate issue, I am able to connect to sqlplus via this password but using ggsci shell we cannot login.

1. Please check the below environment variables settings

ORACLE_HOME
ORACLE_SID
LD_LIBRARY_PATH
PATH

2. Once all the above settings is made correctly,

Exit all ggsci sessions,
Start a new ggsci session
restart the OGG mgr process.

3. Login again

dblogin  USERID ggate, PASSWORD xxxxxxxx

Saturday, August 18, 2018

Configuring JBoss ON as a Windows Service

The rhq-server.bat script has an installation option that installs the script as a Windows service.

1) Set the environment variable to run the Windows service as.

Every Windows service has to run as some system user. There are two environment variables in the rhq-server.bat script that set the user to use:

RHQ_SERVER_RUN_AS sets any Windows user to be the JBoss ON server user. The username given here must be in the standard Windows format, DOMAIN\user, such as EXAMPLEDOMAIN\janderson.

RHQ_SERVER_RUN_AS_ME sets the server to run as whoever the current user is. This overrides the RHQ_SERVER_RUN_AS, if both as set.

If neither environment variable is set, then the JBoss ON server runs as the system account.

Run the rhq-server.bat script with the install option to set up the service. This prompts for the password of whatever user account is used for the JBoss ON service.

serverRoot\bin\rhq-server.bat install

How to disable NUMA in Red Hat Enterprise Linux system

Environment:
Red Hat Enterprise Linux 4
Red Hat Enterprise Linux 5
Red Hat Enterprise Linux 6
Red Hat Enterprise Linux 7

Issue:

How to disable NUMA in Red Hat Enterprise Linux system

Resolution:

Adding the "numa=off" to kernel command line in boot loader configuration and rebooting the system will disable NUMA

Examples:

For RHEL 4, RHEL 5, RHEL 6 (/boot/grub/grub.conf)

title Red Hat Enterprise Linux AS (2.6.9-55.EL)
            root (hd0,0)
            kernel /vmlinuz-2.6.9-55.EL ro root=/dev/VolGroup00/LogVol00 rhgb quiet numa=off
            initrd /initrd-2.6.9-55.EL.img

For RHEL 7 (/etc/default/grub)

GRUB_CMDLINE_LINUX="rd.lvm.lv=rhel_vm-210/root rd.lvm.lv=rhel_vm-210/swap vconsole.font=latarcyrheb-sun16 crashkernel=auto  vconsole.keymap=us rhgb quiet numa=off"

Note, on RHEL 7 grub config has to be rebuilt for changes to take effect:

~]# grub2-mkconfig -o /etc/grub2.cfg
ORA-12060: During Mview Creation

Failing in creating materialized view between two databases with different characterset

Source Database:
SQL> select * from nls_database_parameters@link1 where PARAMETER='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET WE8MSWIN1252

Target Database:
SQL> select * from nls_database_parameters where PARAMETER='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8


Creating the Materialized View:

Create materialized view SCOTT.ttab1
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "MVTBS"
PARALLEL 4
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
START WITH sysdate+0 NEXT SYSDATE+6/24
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS select * from SCOTT.stab1@link1;


Received Error with ORA-01555, after sometime while creating the materialized view and the materialized view creation got failed.

To use the PREBUILT Table option, take an export backup of that table in the source database and import it in target database and then tried to recreate the mview with PREBUILT TABLE option:

create materialized view SCOTT.ttab1 ON PREBUILT TABLE
refresh fast with primary key for update
as
select col1,col2 from SCOTT.stab1@link1;

Which failed with:
Col1,
*
ERROR at line 6:
ORA-12060: shape of prebuilt table does not match definition query


Use "WITH REDUCED PRECISION" for col mis-match.

create materialized view SCOTT.ttab1 ON PREBUILT TABLE WITH REDUCED PRECISION
refresh fast with primary key for update
as
select col1,col2 from SCOTT.stab1@link1;

Failed Again:

create materialized view SCOTT.ttab1 ON PREBUILT TABLE WITH REDUCED PRECISION
*
ERROR at line 1:
ORA-12060: shape of prebuilt table does not match definition query

Remove "with primary key for update"

create materialized view SCOTT.ttab1 ON PREBUILT TABLE WITH REDUCED PRECISION
refresh fast on demand
as
select col1,col2 from SCOTT.stab1@link1;

Materialized view created.

ORA-01207: file is more recent than control file – old control file

Error Message:

SQL> alter database open;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/oradb/app/oracle/oradata/ORCL/system01.dbf’
ORA-01207: file is more recent than control file – old control file

SOLUTION :

1) Dump controlfile to trace
SQL> alter database backup controlfile to trace as '/tmp/control_reco.sql';

2) startup database nomount
SQL> startup nomount

3) create the control file
Copy the script to create control file from trace file
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradb/app/oracle/oradata/ORCL/redo01.log'  SIZE 50M,
GROUP 2 '/oradb/app/oracle/oradata/ORCL/redo02.log'  SIZE 50M,
GROUP 3 '/oradb/app/oracle/oradata/ORCL/redo03.log'  SIZE 50M
DATAFILE
'/app/oracle/oradata/rac10g/system01.dbf',
'/app/oracle/oradata/rac10g/undotbs01.dbf',
'/app/oracle/oradata/rac10g/sysaux01.dbf',
'/app/oracle/oradata/rac10g/users01.dbf'
CHARACTER SET AL32UTF8
;

Control file created.

4) recover database using backup controlfile
SQL> recover database using backup controlfile;
ORA-00279: change 460683 generated at 08/18/2018 13:36:21 needed for thread 1
ORA-00289: suggestion :
/oradb/app/oracle/flash_recovery_area/ORCL/archivelog/2018_08_17/ORCL_mf_1_33_%u_.arc
ORA-00280: change 460683 for thread 1 is in sequence #33

Give the CURRENT redo log file to apply (get from v$logfile)
SQL> set lines 150
SQL> col "REDOLOG_FILE_NAME" for a70
SQL> SELECT a.GROUP#, a.ARCHIVED, a.STATUS, b.MEMBER AS "REDOLOG_FILE_NAME" FROM v$log a JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;

    GROUP# ARC STATUS           REDOLOG_FILE_NAME
---------- --- ---------------- ----------------------------------------------------------------------
         1 NO  CURRENT          /oradb/app/oracle/oradata/ORCL/redo01.log
         2 NO  INACTIVE         /oradb/app/oracle/oradata/ORCL/redo02.log
         3 NO  INACTIVE         /oradb/app/oracle/oradata/ORCL/redo03.log

Here, group 1 is CURRENT. So use it for the recovery

/oradb/app/oracle/oradata/ORCL/redo01.log
Log applied.
Media recovery complete.

5) open the database

SQL> alter database open resetlogs;
Database altered.

Thursday, August 16, 2018

TABLESPACE IN MEMORY OPTION

Managing Tablespace Setting Default InMemory Option

The Inmemory column store parameters setted for tablespace default means all tables and materialized views in that tablespace will use it.

Step .1 Check the PDB name

SQL> show con_name

CON_NAME
------------------------------
PRAM

Step .2 Check the other datafile desnation for the particulat PDB.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ram/system01.dbf
/u01/app/oracle/oradata/ram/sysaux01.dbf
/u01/app/oracle/oradata/ram/undotbs01.dbf
/u01/app/oracle/oradata/ram/user01.dbf
/u01/app/oracle/oradata/ram/user02.dbf

Step 3. Create a Tablespace with using Default InMemory Parameter.

SQL> create tablespace tbs_im
  2  datafile '/u02/app/oracle/oradata/ram/imdata01.dbf' size 100m
  3  default inmemory;

Tablespace created.

Step 4. Check the Tablespace InMemory Features.
SQL>  select tablespace_name,def_inmemory,
  2  def_inmemory_priority,
  3  def_inmemory_distribute,
  4  def_inmemory_compression,
  5  def_inmemory_duplicate
  6  from dba_tablespaces
  7  order by 1;

TABLESPACE_NAME  DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
------------------------------ ------------------------ -------------- ---------------------------- -------------------- ------------------------------
RAMBS                          DISABLED
SYSAUX                         DISABLED
SYSTEM                         DISABLED
TBS_IM                         ENABLED     NONE     AUTO       FOR QUERY LOW     NO DUPLICATE
TEMP                           DISABLED
UNDOTBS1                       DISABLED
USERTBS2                       DISABLED

7 rows selected.

Step 5. Change the InMemory Memcompress for Capacity High
SQL> alter tablespace tbs_im
  2  default inmemory memcompress
  3  for capacity high;

Tablespace altered.

Step 6. Check the Tablespace InMemory Features.
SQL> select tablespace_name,def_inmemory,
  2  def_inmemory_priority,
  3  def_inmemory_distribute,
  4  def_inmemory_compression,
  5  def_inmemory_duplicate
  6  from dba_tablespaces
  7  order by 1;

TABLESPACE_NAME  DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
------------------------------ ------------------------ -------------- ---------------------------- -------------------- ------------------------------
RAMBS                          DISABLED
SYSAUX                         DISABLED
SYSTEM                         DISABLED
TBS_IM                         ENABLED  NONE     AUTO     FOR CAPACITY HIGH NO DUPLICATE
TEMP                           DISABLED
UNDOTBS1                       DISABLED
USERTBS2                       DISABLED

7 rows selected.

Step 7. Create a table for Default InMemory Tablespace.
SQL> create table u1.test_tab_im
  2  (id number(5), name varchar2(10),
  3  salary number(8))tablespace tbs_im;

Table created.

Step 8. Check the table InMemory Option
SQL> column table_name format a11
SQL> column column_name format a10
SQL> select table_name, segment_column_id,
  2  column_name, inmemory_compression
  3  from v$im_column_level
  4  where owner='U1' and
  5  table_name='TEST_TAB_IM'
  6  order by 2;

TABLE_NAME SEGMENT_COLUMN_ID COLUMN_NAM INMEMORY_COMPRESSION
---------- ----------------- ------------------------------ ----------------------- ------------------------------------------
TEST_TAB_IM                              1 ID                 DEFAULT
TEST_TAB_IM                              2 NAME               DEFAULT
TEST_TAB_IM                              3 SALARY             DEFAULT


Step 9. Modify the Tablespace to No InMemory Options.
SQL> alter tablespace tbs_im default no inmemory;

Tablespace altered.


Step 10. Check the Tablespace InMemory Features.
SQL> select tablespace_name,def_inmemory,
  2  def_inmemory_priority,
  3  def_inmemory_distribute,
  4  def_inmemory_compression,
  5  def_inmemory_duplicate
  6  from dba_tablespaces
  7  order by 1;

TABLESPACE_NAME  DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
------------------------------ ------------------------ -------------- ---------------------------- -------------------- ------------------------------
RAMBS                          DISABLED
SYSAUX                         DISABLED
SYSTEM                         DISABLED
TBS_IM                         DISABLED
TEMP                           DISABLED
UNDOTBS1                       DISABLED
USERTBS2                       DISABLED

7 rows selected.

Monday, August 13, 2018

ORAPWD utility in Oracle 12c

Create Password files for remote authentication enforces complexity rules
for the provided password.
     SYSDBA is a System Privilege.
     Normally,we connect to SQL*Plus using sqlplus user/password or sqlplus sys/password as sysdba. 
The password characteristics
  •     password must contain at least 8 charcters
  •     do not contain double quotes
  •     contain at least 1 letter
  •     contain at least 1 digit.
  •     contain at least 1 special charactere.
  •     don't use username and reversed username.

[ramkumar ~]$ orapwd help=y
Usage: orapwd describe file=orapwfilename
  where
file     Name of password file (required),
password     Whether to overwrite existing file (optional)
asm     
Indicates that the password to be stored in Automatic Storage Management (ASM) disk group is an ASM password. (optional),
dbuniquename
unique database name used to identify database
password files residing in ASM diskgroup only.
Ignored when asm option is specified (optional),
format     
use format=12 for new 12c features like SYSBACKUP, SYSDG and SYSKM support, longer identifiers, SHA2 Verifiers etc.
use format=12.2 for 12.2 features like enforcing user profile (password limits and password complexity) and account status for administrative users.
If not specified, format=12.2 is default (optional),
delete     
drops a password file. Must specify 'asm', 'dbuniquename' or 'file'. 
If 'file' is specified, the file must be located on an ASM diskgroup (optional)
input_file     name of input password file, from where old user entries will be migrated (optional),
sys     specifies if SYS user is password or externally authenticated.
For external SYS, also specifies external name.
SYS={y/password} specifies if SYS user password needs to be changed when used with input_file,
sysbackup     creates SYSBACKUP entry (optional).
Specifies if SYSBACKUP user is password or externally authenticated.
For external SYSBACKUP, also specifies external name.
Ignored, if input_file is specified,
sysdg     creates SYSDG entry (optional).
Specifies if SYSDG user is password or externally authenticated.
For external SYSDG, also specifies external name.
Ignored, if input_file is specified,
syskm     creates SYSKM entry (optional).
Specifies if SYSKM user is password or externally authenticated.
For external SYSKM, also specifies external name.
Ignored, if input_file is specified,
describe     describes the properties of specified password file (required).
 *   There must be no spaces around the equal-to (=) character.

Description existing Orapwd file
[ramkumar ~]$ orapwd describe file=$ORACLE_HOME/dbs/orapwcdb1
Password file Description : format=12.2

Delete the existin password file.
[ramkumar ~]$ orapwd delete=y dbuniquename=orabase file=orapwcdb1.ora password="oracle"

Create the new password file
[ramkumar ~]$ orapwd file=orapwcdb1.ora password=ramkumar12c! entries=20

Rewrite the password file
[ramkumar ~]$ orapwd file=orapwcdb1.ora password=ramkumar12C! entries=20 ignorecase=y force=y

Container user sys password
[ramkumar ~]$ orapwd file=orapwacs.ora password=ramkumar@12C entries=20 sys=c##ramkumar force=y

Location of the password file
[ramkumar ~]$ pwd
/u02/app/oracle/product/12.2.0.1/db_1/dbs

Existing file check
[ramkumar ~]$ echo *cdb1.ora
initcdb1.ora orapwcdb1.ora spfilecdb1.ora

To check the available user using password
SQL> select username, sysdba, con_id from v$pwfile_users;
USERNAME      SYSDB     CON_ID
-------------------  ----- ----------
SYS                  TRUE           0
C##RAMKUMAR         TRUE           0