Thursday, 11 January 2018

Change of DB_UNIQUE_NAME in a database

Today I created a new database. I created with usage of Oracle Universal Installer graphic interface. I did not find the switch for advanced configuration, where I could change some parameters along the installation phase, so I was left over with this what gives the basic configuration for Server-class database option. May be I missed some tab or checkbox, does not matter.
Now the starting point is to have a 12c standalone database registered in clusterware.
  • the simple change in the spfile ends with
    SQL> alter system set db_unique_name='pizmak_node3' scope=spfile;
    alter system set db_unique_name='pizmak_node3' scope=spfile
    *
    ERROR at line 1:
    ORA-32017: failure in updating SPFILE
    ORA-65500: could not modify DB_UNIQUE_NAME, resource exists
    
  • so I found "How to change the DB_UNIQUE_NAME in a RAC database (Doc ID 1604421.1)", and remove the database from clusterware, but then
    SQL> startup mount
    ORA-01078: failure in processing system parameters
    ORA-01565: error in identifying file '+DATA/pizmak/spfilepizmak.ora'
    ORA-17503: ksfdopn:2 Failed to open file +DATA/pizmak/spfilepizmak.ora
    ORA-15056: additional error message
    ORA-17503: ksfdopn:2 Failed to open file +DATA/pizmak/spfilepizmak.ora
    ORA-15173: entry 'spfilepizmak.ora' does not exist in directory 'pizmak'
    ORA-06512: at line 4
    
    
    Apparently the oratab entry was removed with the srvctl remove database command
  • so I found "Alert Log Error ORA-17503 ORA-15173 After Deleting Database. (Doc ID 1900471.1)"
  • so I looked at $ORACLE_HOME/dbs and found pfile with SPFILE pointing to ASM location and put it aside
  • after some time I discovered true reason - initially spfile was located in +DATA/PIZMAK/ (possibly on some stage of db installation), but eventually the location was +DATA/PIZMAK/PARAMETERFILE
  • so I used the pfile in $ORACLE_BASE/admin/pizmak/pfile/, which is put there during installation
  • so I started with startup nomount pfile=my_pfile_copy - this pfile had already db_unique_name changed to the one I wanted to, no changes to db_create_file_dest (as it would point to
  • I've got info 12c is able to move files online to another location, so
    SQL> select file_id||':'||file_name from dba_data_files;
    
    FILE_ID||':'||FILE_NAME
    --------------------------------------------------------------------------------
    6:+DATA/PIZMAK/DATAFILE/users.302.965122931
    4:+DATA/PIZMAK/DATAFILE/undotbs1.303.965122931
    3:+DATA/PIZMAK/DATAFILE/sysaux.300.965122739
    1:+DATA/PIZMAK/DATAFILE/system.314.965139835
    
    alter database move datafile 1;
    alter database move datafile 3;
    alter database move datafile 4;
    alter database move datafile 6;
    
    SQL> select file_id||':'||file_name from dba_data_files;
    
    FILE_ID||':'||FILE_NAME
    --------------------------------------------------------------------------------
    6:+DATA/PIZMAK_NODE3/DATAFILE/users.303.965140667
    3:+DATA/PIZMAK_NODE3/DATAFILE/sysaux.301.965140607
    4:+DATA/PIZMAK_NODE3/DATAFILE/undotbs1.300.965140655
    1:+DATA/PIZMAK_NODE3/DATAFILE/system.314.965139835
    
  • now logs
    SQL> select member from v$logfile;
    
    MEMBER
    --------------------------------------------------------------------------------
    +DATA/PIZMAK/ONLINELOG/group_3.310.965122991
    +DATA/PIZMAK/ONLINELOG/group_3.311.965122991
    +DATA/PIZMAK/ONLINELOG/group_2.308.965122991
    +DATA/PIZMAK/ONLINELOG/group_2.309.965122991
    +DATA/PIZMAK/ONLINELOG/group_1.306.965122991
    +DATA/PIZMAK/ONLINELOG/group_1.307.965122991
    
    SQL> select GROUP#, SEQUENCE#, MEMBERS, STATUS from v$log;
    
        GROUP#  SEQUENCE#    MEMBERS STATUS
    ---------- ---------- ---------- ------------------------------------------------
             1          7          1 INACTIVE
             2          8          2 INACTIVE
             3          9          2 CURRENT
    
    
    alter database drop logfile group 1;
    alter database add logfile group 1 size 512m;
    -- here in alert log (as this was 1st log file on REDO) an error
    -- Thu Jan 11 14:41:29 2018
    -- ERROR: failed to establish dependency between database pizmak_node3 and diskgroup resource ora.REDO.dg
    
    
    alter database drop logfile group 2;
    alter database add logfile group 2 size 512m;
    -- no more errors as the structure already exists
    
    alter system switch logfile;
    alter system switch logfile;
    
    alter database drop logfile group 3;
    alter database add logfile group 3 size 512m;
    
    SQL> select member from v$logfile;
    
    MEMBER
    ------------------------------------------------------------------------------------------------------------------------
    +REDO/PIZMAK_NODE3/ONLINELOG/group_3.275.965141729
    +REDO/PIZMAK_NODE3/ONLINELOG/group_2.274.965141165
    +REDO/PIZMAK_NODE3/ONLINELOG/group_1.256.965140887
    
    
    
    I have to double members yet
  • now spfile
    create spfile='+DATA' from pfile='/oracle/m010162/init.ora.011201894952';
    -- and change in initpizmak.ora
    -- SPFILE='+DATA/pizmak_node3/parameterfile/spfile.311.965142657'
    !cp initpizmak.ora $ORACLE_HOME/dbs/
    
  • to move temporary tablespace I just created a new one and switched default temporary tablespace to it
    create temporary tablespace temp2 tempfile size 128m autoextend on next 128m maxsize 16g;
    alter database default temporary tablespace temp2;
    
    -- then as I wanted temporary tablespace named TEMP I did that once again
    create temporary tablespace temp tempfile size 128m autoextend on next 128m maxsize 16g;
    alter database default temporary tablespace temp;
    
    SQL> select file_name from dba_temp_files;
    
    FILE_NAME
    ------------------------------------------------------------------------------------------------------------------------
    +DATA/PIZMAK_NODE3/TEMPFILE/temp.312.965143967
    
    
  • to move control files I used asmcmd
    1. change control_files parameter to new location with scope spfile
    2. shutdown immediate
    3. copy or move control files to the new location in asmcmd (with cp one have to specify the name of the target file, but without instance/revision? number)
    4. start the db

Tuesday, 2 January 2018

ORA-27303: additional information: startup egid = 5003 (asmadmin), current egid = 5000 (oinstall)

I've got the following info on several occasions:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 5003 (asmadmin), current egid = 5000 (oinstall)
It seems the problem arises from wrong permissions on oracle binary files. More explanations here - https://asanga-pradeep.blogspot.dk/2012/06/ora-27303-additional-information.html.

Tuesday, 24 October 2017

Calibrating I/O

Today a colleague of mine started the short calibration code:
set serveroutput on
declare 
 l_latency number;
 l_iops    number;
 l_mbps    number;
begin
 dbms_resource_manager.calibrate_io(num_physical_disks=>6, max_latency=>12, max_iops=>l_iops, max_mbps=>l_mbps, actual_latency=>l_latency);
 dbms_output.put_line('max_iops='||l_iops||'   max_mbps='||l_mbps||'  actual_latency='||l_latency);
end;
/
... and after some 10 minutes got
Tue Oct 24 13:26:50 2017
Errors in file /opt/oracle/diag/rdbms/xxx/xxx/trace/xxx_cs01_10660.trc  (incident=60493):
ORA-04031: unable to allocate 1049112 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","ksfdcamem shared I/O read buffer")
Incident details in: /opt/oracle/diag/rdbms/xxx/xxx/incident/incdir_60493/xxx_cs01_10660_i60493.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /opt/oracle/diag/rdbms/xxx/xxx/trace/xxx_cs01_10660.trc:
ORA-04031: unable to allocate 1049112 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","ksfdcamem shared I/O read buffer")
Errors in file /opt/oracle/diag/rdbms/xxx/xxx/trace/xxx_cs01_10660.trc:
ORA-04031: unable to allocate 1049112 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","ksfdcamem shared I/O read buffer")
Tue Oct 24 13:26:51 2017
Dumping diagnostic data in directory=[cdmp_20171024132651], requested by (instance=1, osid=10660 (CS01)), summary=[incident=60493].
Tue Oct 24 13:26:51 2017
Sweep [inc][60493]: completed
Sweep [inc2][60493]: completed

While there is not much in the subject on Metalink, there exists an excellent blog article. I may add to it the following:
  • 1st idea just to flush shared pool did not help - apparently as the system was in use initially and the load taken off just prior to calibration and many allocations already took place
  • 2nd idea was to restart and that was enough - when run immediately after start, the process went smoothly.
  • after success we 've got
    Tue Oct 24 13:42:51 2017
    Shared IO Pool defaulting to 512MB. Trying to get it from Buffer Cache for process 12832.
    
    Now it seems quite obvious that even with shared pool allocating 2G after a while there is no such amount of free memory in 1M chunks - even in reserved shared pool part (shared_pool_reserved_size defaults to 5% of the shared pool total)

Friday, 6 October 2017

Problem on a SELECT through a db link

Not my adventure entirely, yet interesting.
There exists a query in the shape INSERT ... SEELECT. SELECT is done on the view. The view returns huge set of data (if called without conditions). But there is a condition, thus the whole query in normal circumstances should be executed in a fraction of a second.
But yesterday out of nothing the plan changed. It changed several times in the past, but then it was enough to inject through a sql profile the hint optimizer_features_enable('10.2.0.4') to smooth things out. So developers injected it in a hard way - to the code itself. But yesterday the plan changed again despite the hint, and the change was based on the full view generation and remote site trying to push whole that mass of data back to the local site to filter through the condition - the local database waited on TCP Socket wait event, waiting for any data, while the remote database burnt CPU trying to produce full view set (which was so huge effort that it completely choked). And any action on the 'local' side did not fix the problem.
After some time spend on struggling with the issue the final solution was as follows:
  • on the local site - optimizer_features_enable('10.2.0.4') and rule injected by sql profile
  • on remote site - optimizer_features_enable('10.2.0.4') and rule injected by sql profile to the view
It is clear that rule and optimizer_features_enable are exclusive - on the Metalink though somewhere was a note that OPAQUE_TRANSFORM may be disabled by an event or optimizer_features_enable or rule hints. My guess is the rule hint now disabled OPAQUE_TRANSFORM (which is passed by default), which allowed for the plan control on the remote site and there optimizer_features_enable('10.2.0.4') was used.

Wednesday, 9 August 2017

RMAN-20033: control file SEQUENCE# too low

Most probably the true reason for that error to occur is a race condition between 2 concurrent backup processes - for example one full backup and one archivelog backup with autobackup enabled. Thus both would synchronize with RMAN catalog and one of them may try to register controlfile being older than the last one already registered by the 2nd process.

On Metalink they suggests a weird solution for this (Rman-20033 Control File Sequence# Too Low (Doc ID 342696.1)) to recreate the controlfile or RMAN catalog - it is possible it may be needed in some scenario, but I guess the best way first is simply rerun the backup ensuring there is only one backup process running.

Tuesday, 8 August 2017

Environment variable expansion in sqlnet.ora

By and large there are not many articles on the environment variables expansion in sqlnet.ora.
May be the main reason is that in majority of cases there is no such possibility. I run a bunch of tests and for all those few cases there was no such feature enabled - simply all the entries with $ sign as leading character were treated literally as text.

The only situation when it works is if used with a WALLET_LOCATION definition. The WALLET_LOCATION definition is more complex than all the rest sqlnet.ora parameters and is built with the help of usual Oracle syntax for .ora files with parentheses. Such definition could look as follows:
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY=/opt/oracle/wallets/mywallet) ) )
But what I would emphasize here is the possibility to make a part of this definition to be build dynamically. I've tested only the file method. The only part which apparently allows for the environment variables expansion is the DIRECTORY value. Putting a variable to other attributes (like let's say METHOD) does not seem to work.
Thus one may put into the DIRECTORY path any variable defined in the environment. For example:
## as You see LOGNAME is a predefined variable, ORACLE_HOME a well known variable, 
## while $WALLET_METHOD is simply a custom one
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY=$ORACLE_HOME/$WALLET_METHOD/wallets/walletOf$LOGNAME) ) )
Further this looks as follows in strace trail:
## ORACLE_HOME is /opt/oracle/app/oracle/product/12.1.0/client_1
## WALLET_METHOD was set to FILE
## and LOGNAME is seen as rems
## errors are due to the fact I had no wallet configured at all
## wanting only to see if the expansion would take place 
[..]
stat("/opt/oracle/app/oracle/product/12.1.0/client_1/FILE/home/rems/admin/network/admin/walletOfrems/ewallet.p12", 0x7ffebccd3430) = -1 ENOENT (No such file or directory)
stat("/opt/oracle/app/oracle/product/12.1.0/client_1/FILE/home/rems/admin/network/admin/walletOfrems/cwallet.sso", 0x7ffebccd3430) = -1 ENOENT (No such file or directory)
write(1, "ERROR:\n", 7)                 = 7
write(1, "ORA-12578: TNS:wallet open faile"..., 34) = 34
[..]
With such ability we can create several wallets being chosen based on the environment context - thus with one binaries installation (and one sqlnet.ora file) we may service many users with their own wallets thus separating their responsibilities.