c:\> C:\>sqlplus testuser/Password@//ractest-scan.ractest.local:1521/service SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 31 11:44:13 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters and Automatic Storage Management options SQL> password Changing password for TESTUSER Old password: New password: Retype new password: Password changed SQL>
Month: August 2018
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database
Oracle database instance hung due to recovery area full causing “Archival Stopped Error”.
Check alert.log with below ORA errors:
— Alert.log
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that
are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted
*************************************************************
WARNING: A file of type ARCHIVED LOG may exist in
db_recovery_file_dest that is not known to the database.
Use the RMAN command CATALOG RECOVERY AREA to re-catalog
any such files. If files cannot be cataloged, then manually
delete them using OS command. This is most likely the
result of a crash during file creation.
*************************************************************
Check recovery area view , which says a lot of free space available:
SQL> set pagesize 120 SQL> set linesize 120 SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES ----------- ------------------- ------------------------- --------------- ... .. ARCHIVED LOG 33.5 0.8 248 BACKUP PIECE 0 0 0 ... .. . 7 rows selected.
Check the database resetlog date and time:
SQL> alter session set nls_date_format='YYYYMMDD-HH24:MI:SS';
Session altered.
SQL> select CREATED,RESETLOGS_TIME,PRIOR_RESETLOGS_TIME,
CONTROLFILE_TIME,VERSION_TIME from v$database;
CREATED RESETLOGS_TIME PRIOR_RESETLOGS_T CONTROLFILE_TIME VERSION_TIME
----------------- ----------------- ----------------- ----------------- -----------------
20180817-11:05:10 20180817-11:05:43 20161009-20:55:02 20180821-15:19:21 20180817-11:05:10
CAUSE
There are archivelogs in recovery area from old databases, which were refreshed or restored with different DB ID, manually delete them, then everything is fine:
ASMCMD> ls -ltr Type Redund Striped Time Sys Name ... ... .. . Y 2018_08_14/ Y 2018_08_15/ Y 2018_08_16/ RESETLOGS DATE -----> Y 2018_08_17/ Y 2018_08_18/ Y 2018_08_19/ Y 2018_08_20/
ORA-15137: The ASM cluster is in rolling patch state
SYMPTOM
While adding a new disk into an existing diskgroup, below errors occurred:
ORA-15032: not all alterations performed ORA-15137: The ASM cluster is in rolling patch state.
INVESTIGATION
1) On both nodes, it shows the cluster in “In Rolling Patch”, and the patch levels are all the same.
SQL> SELECT SYS_CONTEXT('SYS_CLUSTER_PROPERTIES', 'CLUSTER_STATE')
FROM DUAL;
SYS_CONTEXT('SYS_CLUSTER_PROPERTIES','CLUSTER_STATE')
--------------------------------------------------------------------
In Rolling Patch
SQL> SELECT SYS_CONTEXT('SYS_CLUSTER_PROPERTIES', 'CURRENT_PATCHLVL')
FROM DUAL;
SYS_CONTEXT('SYS_CLUSTER_PROPERTIES','CURRENT_PATCHLVL')
--------------------------------------------------------------------
3628626982
$ asmcmd
ASMCMD> showclusterstate
In Rolling Patch
ASMCMD> showpatches
---------------
List of Patches
===============
26609817
26609966
26839277
27105253
27128906
27144050
27335416
27458609
27464465
27674384
ASMCMD> showversion
ASM version : 12.2.0.1.0
2) “crsctl query crs softwarepatch” shows the same results on both nodes:
$ crsctl query crs softwarepatch Oracle Clusterware patch level on node racnode1 is [3628626982]. $ crsctl query crs softwarepatch Oracle Clusterware patch level on node racnode2 is [3628626982].
3) “crsctl query crs releasepatch” shows the same results on both nodes:
— racnode1:
$crsctl query crs releasepatch Oracle Clusterware release patch level is [3628626982] and the complete list of patches [26609817 26609966 26839277 27105253 27128906 27144050 27335416 27458609 27464465 27674384 ] have been applied on the local node.
— racnode2:
$crsctl query crs releasepatch Oracle Clusterware release patch level is [3628626982] and the complete list of patches [26609817 26609966 26839277 27105253 27128906 27144050 27335416 27458609 27464465 27674384 ] have been applied on the local node.
4) kfod command shows the same results on both nodes:
— racnode1:
$ $ORACLE_HOME/bin/kfod op=patchlvl ------------------- Current Patch level =================== 3628626982 $ $ORACLE_HOME/bin/kfod op=patches --------------- List of Patches =============== 26609817 26609966 26839277 27105253 27128906 27144050 27335416 27458609 27464465 27674384
— racnode2:
$ $ORACLE_HOME/bin/kfod op=patchlvl ------------------- Current Patch level =================== 3628626982 $ $ORACLE_HOME/bin/kfod op=patches --------------- List of Patches =============== 26609817 26609966 26839277 27105253 27128906 27144050 27335416 27458609 27464465 27674384
5) lsinventory shows the same results on both nodes:
$ $ORACLE_HOME/OPatch/opatch lsinventory | grep -i desc ARU platform description:: Linux x86-64 Patch description: "Database Apr 2018 Release Update : 12.2.0.1.180417 (27674384)" Patch description: "OCW APR 2018 RELEASE UPDATE 12.2.0.1.0(180129) (27464465)" Patch description: "ACFS APR 2018 RELEASE UPDATE 12.2.0.1.0(180129) (27458609)" Patch description: "Tomcat Release Update 12.2.0.1.0(ID:171023.0830) (27144050)" Patch description: "DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913) (26839277)"
SOLUTIONS
$crsctl stop rollingpatch CRS-1161: The cluster was successfully patched to patch level [3628626982].
recheck with above commands, the cluster status is changed from “In Rolling Patch” to “Normal” now.
SQL> SELECT SYS_CONTEXT('SYS_CLUSTER_PROPERTIES', 'CLUSTER_STATE')
FROM DUAL;
SYS_CONTEXT('SYS_CLUSTER_PROPERTIES','CLUSTER_STATE')
--------------------------------------------------------------------------------
Normal
Generally speaking, For “ORA-15137: The ASM cluster is in rolling patch state” issue, the below steps can be followed one after another, until the issue is resolved:
a) stop rolling patch status one node by another:
SQL>Alter system stop rolling patch;
b) stop rolling patch for whole cluster:
$crsctl stop rollingpatch
c) In case of postpatch not complete successfully for some reason, which can also cause this issue:
— As super root user :
$GRID_HOME/crs/install/rootcrs.sh -prepatch $GRID_HOME/crs/install/rootcrs.sh -postpatch
d) For some reason, OCR is not updated with right patchlevel:
$GRID_HOME/crs/install/rootcrs.sh -prepatch $GI_HOME/bin/clscfg -patch $GRID_HOME/crs/install/rootcrs.sh -postpatch
e) For some reason, patches are available from “opatch lsinventory”, but they are missing from kfod output:
-- as super user $GRID_HOME/crs/install/rootcrs.sh -prepatch -- as grid owner, $GRID_HOME/bin/patchgen commit -pi 12345678 $GRID_HOME/bin/patchgen commit -pi 23456789 -- as super user $GRID_HOME/crs/install/rootcrs.sh -postpatch
Search AD users and group in Windows Desktop
There are changes you want to search AD with no tools on-hand like Jexplore, ApacheDS or LdapAdmin. Here is the command you can get DSQuery for simple search for user/group. 1. %SystemRoot%\SYSTEM32\rundll32.exe dsquery,OpenQueryWindow
via Search AD users and group in Windows Desktop — Ran’s Identity and Access Management blog
ORA-02085: database link DBLINK_TEST connects to TESTDB
If parameter global_names =TRUE, the database link name has to match the remote database name.
Try to query a remote table through a database link:
SQL> select count(*) from testuser.test@dblink_test; select count(*) from testuser.test@dblink_test; * ERROR at line 1: ORA-02085: database link UDBLINK_TEST connects to TESTDB
REASON
Check database parameter global_names =TRUE. If parameter global_names =TRUE, the database link name has to match the remote database name( TESTDB).
WORKAROUND
Turn off global_names in session level, then it should be ok.
SQL>alter session set global_names=false;
Session altered.
SQL>select count(*) from testuser.test@dblink_test;
COUNT(*)
----------
100
Make DBA Life Easy 