Very insightful article on why/when firewalls throw connections https://li555li.blogspot.se/2014/09/.
In short - to prevent this one have to set SQLNET.EXPIRE_TIME to {timeout on firewall}/2-1 in minutes.
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.
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.965140887I 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
- change control_files parameter to new location with scope spfile
- shutdown immediate
- 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)
- 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.
Friday, 24 November 2017
Tuesday, 24 October 2017
Calibrating I/O
Today a colleague of mine started the short calibration code:
While there is not much in the subject on Metalink, there exists an excellent blog article. I may add to it the following:
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:
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
Subscribe to:
Posts (Atom)