Remigium

Monday, 5 March 2018

Restricted session

-- at startup
startup restrict;

-- on running instance
alter system enable restricted session;

System altered.

-- to disable
alter system disable restricted session;

-- to check
select logins from v$instance; 

LOGINS
----------
RESTRICTED

Thursday, 22 February 2018

Wednesday, 31 January 2018

How to find a time for the latest DML on a table

Lately I've got such question from a co-worker. I looked around a bit and here are the findings:

ORA_ROWSCN pseudocolumn - seems to be the most versatile solution here - the maximal value of ORA_ROWSCN would definitely point the time of the last modification for any table.
VERSIONS_ENDSCN https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS01009
dba_tab_modifications - this is the table, which keeps number of DML operations since the last statistics collection. Thus it may be used to find the approx. time of a last modification assuming it is done after the latest stats collection, but it has of course a number of limitations

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.