Category: General

Please see blog post entitled “Moving undo table spaces on a RAC with users online” for some additional background. In summary, we had to free up space on a certain ASM disk group. We had about 175 GB allocate to temp and decided to move this with users actively accessing the database.

First, take the file offline:

ALTER DATABASE TEMPFILE '+ASM_DG_01/PRODPRM/temp01.dbf' OFFLINE;

Copy the file from the old ASM disk group to the new disk group. Set oracle home to your ASM instance and then issue ASMCMD.

cp +ASM_DG_01/PRODPRM/temp01.dbf +ASM_DG_04/PRODPRM/temp01.dbf

(Note that this is a copy, not a move.)

Rename the data file in the database in sqlplus. If you have changed the oracle home to the ASM instance, remember to change back to your RAC instance’s oracle home:

ALTER DATABASE RENAME FILE '+ASM_DG_01/PRODPRM/temp01.dbf' TO '+ASM_DG_04/PRODPRM/temp01.dbf';

Bring it back online:

ALTER DATABASE TEMPFILE '+ASM_DG_04/PRODPRM/temp01.dbf' ONLINE;

Now to delete the file from the old ASM disk group. Set oracle home to your ASM instance and then issue ASMCMD.

rm +ASM_DG_01/PRODPRM/temp01.dbf

The space utilized in the ASM disk group should now be reduced.

We were running out of space on an ASM DG and had to free up space ASAP. Since out UNDO tablespaces are sized at about 200gb and were on the ASM DG that was full, we decided to move them.

This was the primary database with a physical (data guard) standby, so I needed to turn off standby file management

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';

Create a new undo file for the RAC node 01 in the new ASM disk group:

create undo tablespace UNDOTBS1A datafile '+ASM_DG_01/PRODPRM/undotbs1a.dbf' size 20G;

Alter to allocate the new undo file to RAC node 01:

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1A SCOPE=BOTH SID='PRODPRM1';

Since this is a multi-node RAC, we can take one node offline to remove all users attached to the old undo tablespace off RAC node 01:

srvctl stop instance PRODPRM1 –d PRODPRM –o immediate

To be safe, make sure the RAC node is down with:

srvctl status database –d PRODPRM

Also, check the alert log on this particular node to make sure it was a clean shutdown.

With the RAC node down, drop the data file:

drop tablespace UNDOTBS1 including contents and datafiles;

Bring up RAC node 01 and it will be using the new undo table space:

srvctl start instance PRODPRM1 –d PRODPRM

Turn on standby file management

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

While importing data via an IMPDP job we encountered the error:

Import: Release 11.2.0.4.0 - Production on Tue Mar 20 13:59:30 2018
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=dpump dumpfile=dpump_data.dmp 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . 
ORA-31693: Table data object "SCHEMA_NAME"."TABLE_NAME" failed to load/unload and is being skipped due to error:
ORA-06502: PL/SQL: numeric or value error
. . 
. . 
. . 
. . 
. . 
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Mar 20 13:59:32 2018 elapsed 0 00:00:02

Per Oracle Support, this was documented in “Bug 18665004 – ORA-31693/ORA-6502 from Datapump import (impdp) into an existing table having a LONG or LONG RAW column (Doc ID 18665004.8)”. This table did have a “LONG” column.

We were not in a position to install the fix suggested and instead decided to try the work around. We dropped the only index on the table and reran the import successfully. After the import, we recreated the index.

I was building a new standby and after I enabled log_archive_dest_state_2 on the primary, the below error message was reported in the primary’s alert_log:

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='*';
Tue Mar 20 00:20:08 2018
Errors in file /oracle/home/trace/PRODDB_tt01_438.trc:
ORA-16047: DGID mismatch between destination setting and target database
Error 16047 for archive log file 6 to 'STDBYDB'
Tue Mar 20 00:20:08 2018
Errors in file /oracle/home/trace/PRODDB_tt01_438.trc:
ORA-16047: DGID mismatch between destination setting and target database
Tue Mar 20 00:20:08 2018
Errors in file /oracle/home/trace/PRODDB_tt01_438.trc:
ORA-16047: DGID mismatch between destination setting and target database
Tue Mar 20 00:20:08 2018
Errors in file /oracle/home/trace/PRODDB_arc1_8368.trc:
ORA-16047: DGID mismatch between destination setting and target database
Tue Mar 20 00:20:08 2018
PING[ARC1]: Heartbeat failed to connect to standby 'STDBYDB'. Error is 16047.

After a few minutes of consternation, I realized that I had an incorrect parameter on the standby:

SQL> show parameter db_unique_name;
 
NAME                                 TYPE        VALUE
db_unique_name                       string      PRODDB

Basically, I had inadvertently set the db_unique_name to PRODDB on both the primary and standby database. I changed the standby as follows:

alter system set db_unique_name=STDBYDB scope=spfile sid='*';

And bounced the standby. This resolved the issue.

I received an email suggesting that I run a “Security Assessment” by using Oracle’s Database Security Assessment Tool. The tool promises to

“analyzes database configurations, users, their entitlements, security policies and identifies where sensitive data resides to uncover security risks and improve the security posture of Oracle Databases within your organization.”

The tool can be downloaded for free from support.oracle.com reference Oracle Database Security Assessment Tool (DBSAT) (Doc ID 2138254.1).

After you, agree, a zip file will be downloaded to your laptop. I choose to unzip the file and then FTP the contents to a directory on my database server. (I suggest creating a separate directory.)

The contents of the directory will be:

[snoopy_test-TSTRAC2] ls -l
total 352
-rw-r--r-- 1 oracle dba  12433 Mar 15 18:11 dbsat
-rw-r--r-- 1 oracle dba  12579 Mar 15 18:11 dbsat.bat
drwxr-xr-x 5 oracle dba     40 Mar 15 18:15 Discover
-rw-r--r-- 1 oracle dba  28216 Mar 15 18:11 sat_analysis.py
-rw-r--r-- 1 oracle dba  43181 Mar 15 18:11 sat_collector.sql
-rw-r--r-- 1 oracle dba 247465 Mar 15 18:11 sat_reporter.py
drwxr-xr-x 2 oracle dba   4096 Mar 15 18:15 xlsxwriter

chmod the files to add execute permissions.

The tool has 3 components:
Collector collects data on the system being assessed
Reporter analyzes the above output
Discoverer generates the Database Sensitive Data Assessment Report

Running the Collector
Run the collector with the below command. In my example, I ran this as sysdba with my ORACLE_HOME set to the RAC instance that I intended to execute the DBSAT tool:

./dbsat collect -n "/ as sysdba" /TSTRAC2/dean/dbsat/dbsat_collect
                 |               |                   | 
No encryption ---+               |                   |
                                 |                   | 
location for the output ---------+                   |
                                                     |
name of report file ---------------------------------+

The output will be written to a file named dbsat_collect.json

Running the Reporter
The reporter needs Python version 2.6 or later. This can be verified with

[snoopy_test-TSTRAC2] python -V
Python 2.7.5

Run the report with the below command:

./dbsat report -a -n /TSTRAC2/dean/dbsat/dbsat_collect
 
Note: 
a. Paste the above command into notepad. I used word to write this blog entry and word sometimes replaces the “-“ to a non-unix compatible character.
b. The file name from the Collector is specified without the extension

The below files will be generated:
dbsat_collect_report.txt
dbsat_collect_report.html
dbsat_collect_report.xlsx

The html file is probably the nicest format to review and will contain a large amount of information classified by level of risk.

Using the Discoverer
To run this part of the tool, you have to set up the

/TSTRAC2/dean/dbsat/Discover/conf/sample_dbsat.config
 
File which contains the information on the database connection and the location of the
 
sensitive_en.ini
 
file which contains the patterns that will be used to query database column names, column comments etc. These patterns are specified as regex and logical operators as documented in Section 1.4.3.1.2 Pattern File Configuration of the Database Security Assessment Tool User Guide.

Based on the parameters specified in the config files, the tool will then scan the database looking for column, comments etc. that match the patterns specified.

If you have a SQL performance issue, Oracle Support may request you to run the SQL Health Check script. This is a pretty cool process, so I thought I would document it.

Download the SQLHC.zip archive from the Oracle support web site. The document id at the time of writing this blog post was Doc ID 1366133.1.

Extract the zip archive. I did this on my laptop. There are three files sqldx.sql, sqlhc.sql, and sqlhcxec.sql.

Place these three files in a directory on your server.

Find the SQL id of the problem SQL. This can be accomplished via AWR, ADDM, etc. In my case, the SQL was running for greater than 5 minutes so I was able to get the SQL id quite easily.

Run the SQLHC tool as follows:
Sign on as sysdba
Issue the command as follows:
START sqlhc.sql T 3t3ypamk8wu40

the first parameter “T” indicates that my site has the SQL tunning pack.
(Valid values are Tuning(T), Diagnostics (D) or None (N)). If site has both Tuning and Diagnostics licenses then specify T (Oracle Tuning pack includes Oracle Diagnostics)

the second parameter is the sql_id of the problem SQL statement

The process will display a lot of information on the terminal and write the output to a file named sqlhc_date_time_sqlid.zip. For example:
sqlhc_20180212_180710_3t3ypamk8wu40.zip

Attach this file to your open SR with Oracle.

We had previously created a 2 node RAC physical standby database. Today, we were attempting to bring it up as a primary by issuing the commands:

startup mount;
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

In the alert log we saw the error:

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+DG_OLD_SERVER/dbname/ts_name_data_file_01.dbf'
ORA-17503: ksfdopn:2 Failed to open file +DG_OLD_SERVER/dbname/ts_name_data_file_01.dbf
ORA-15001: diskgroup "+DG_OLD_SERVER" does not exist or is not mounted

The database was attempting to lock a file with the name of the old primary’s ASM file systems. I checked in v$datafile:

 
select    a.name
from      v$datafile a
order by  a.file#
;

and all the file names were prefixed by the name of the old primary’s ASM file systems. A check in ASM on the new server revealed that the files existed in ASM prefixed with the new server’s ASM file system names. I suspect that while building the standby the below command was not issued after the recover database:

switch datafile all; 

We brought up the database in mount mode and issued commands to rename the files from the old ASM file system to the new ASM file system:

ALTER DATABASE RENAME FILE '+DG_OLD_SERVER/dbname/ts_name_data_file_01.dbf'        TO '+DG_NEW_SERVER/dbname/ts_name_data_file_01.dbf';

After all the files were renamed, I checked in v$datafile to confirm the new names. After this we were able to activate the physical standby database as a primary.

Update – February 05, 2018
I built another standby and checked the names of the data files after the recover database and the file names were correctly changed as per the DB_FILE_NAME_CONVERT parameter and hence I cannot explain what went wrong in the original build. However, there is no need to issue the “switch datafile all” command.

If you need to trace the TNSPING command, add the 2 lines below to the sqlnet.ora file:

TNSPING.TRACE_LEVEL=ADMIN
TNSPING.TRACE_DIRECTORY=/apps/opt/oracle/product/12.1.0.2/db_1/network/admin

Then run the tnsping command:

[oracle@server]$ tnsping DEANDR
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 30-JAN-2018 19:57:30
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
oracle_home/network/admin/sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server.domain.com)(PORT = 2340)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = DEANDR)))
TNS-12535: TNS:operation timed out

In this case I had set the trace files to be written to the ORACLE_HOME/network/admin directory. A file named tnsping.trc will be created and will contain information as to the details of the trace command.

I was trying to bring up a new RAC physical standby node and encountered the below error:

[racnode2-DEVDBTPN2] srvctl start database -d DEVDBTPN 
PRCR-1079 : Failed to start resource ora.devdbtpn.db
CRS-5017: The resource action "ora.devdbtpn.db start" encountered the following error: 
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
. For details refer to "(:CLSN00107:)" in "/oracle/base/diag/crs/racnode1/crs/trace/crsd_oraagent_oracle.trc".
 
CRS-2674: Start of 'ora.devdbtpn.db' on 'racnode1' failed
CRS-2632: There are no more servers to try to place resource 'ora.devdbtpn.db' on that would satisfy its placement policy

The OS information was

Linux racnode1 2.6.32-696.13.2.el6.x86_64 #1 SMP Fri Sep 22 12:32:14 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux

There were many other instances running on this RAC node. The issue turned out to be a system parameter setting for the semaphores.

Edit the sysctl.conf file as follows:

vi /etc/sysctl.conf

and change this line

kernel.sem=250 32000 100 128

to this

kernel.sem=250 64000 100 256

Basically double the 2nd and 4th parameter. Then to make the changes go into effect issue the command

sysctl –p

Stop and start the RAC DB with srvctl commands.