INFO: Validating for no private interface provided
INFO: Flag oracle.install.crs.allowSingleNIC=false
SEVERE: [FATAL] [INS-41112] Specified network interface doesnt maintain connectivity across cluster nodes.
CAUSE: Installer has detected that network interface eth0 does not maintain connectivity on all cluster nodes.
ACTION: Ensure that the chosen interface has been configured across all cluster nodes.
SEVERE: [FATAL] [INS-41112] Specified network interface doesnt maintain connectivity across cluster nodes.
CAUSE: Installer has detected that network interface eth1 does not maintain connectivity on all cluster nodes.
ACTION: Ensure that the chosen interface has been configured across all cluster nodes.
INFO: Advice is ABORT
[oracle@rac1 grid]$ ./runcluvfy.sh comp nodecon -n rac1,rac2 -verbose
Verifying node connectivity
Checking node connectivity...
Checking hosts config file...
Node Name Status
------------------------------------ ------------------------
rac2 passed
rac1 passed
Verification of the hosts config file successful
Interface information for node "rac2"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.6.12 192.168.6.0 0.0.0.0 192.168.6.2 00:0C:29:03:F2:84 1500
eth1 192.168.9.12 192.168.9.0 0.0.0.0 192.168.6.2 00:0C:29:03:F2:8E 1500
Interface information for node "rac1"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.6.11 192.168.6.0 0.0.0.0 192.168.6.2 00:0C:29:8A:36:71 1500
eth1 192.168.9.11 192.168.9.0 0.0.0.0 192.168.6.2 00:0C:29:8A:36:7B 1500
Check: Node connectivity of subnet "192.168.6.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac2[192.168.6.12] rac1[192.168.6.11] yes
Result: Node connectivity passed for subnet "192.168.6.0" with node(s) rac2,rac1
Check: TCP connectivity of subnet "192.168.6.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac1:192.168.6.11 rac2:192.168.6.12 passed
Result: TCP connectivity check passed for subnet "192.168.6.0"
Check: Node connectivity of subnet "192.168.9.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac2[192.168.9.12] rac1[192.168.9.11] yes
Result: Node connectivity passed for subnet "192.168.9.0" with node(s) rac2,rac1
Check: TCP connectivity of subnet "192.168.9.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac1:192.168.9.11 rac2:192.168.9.12 passed
Result: TCP connectivity check passed for subnet "192.168.9.0"
Interfaces found on subnet "192.168.6.0" that are likely candidates for VIP are:
rac2 eth0:192.168.6.12
rac1 eth0:192.168.6.11
Interfaces found on subnet "192.168.9.0" that are likely candidates for a private interconnect are:
rac2 eth1:192.168.9.12
rac1 eth1:192.168.9.11
Checking subnet mask consistency...
Subnet mask consistency check passed for subnet "192.168.6.0".
Subnet mask consistency check passed for subnet "192.168.9.0".
Subnet mask consistency check passed.
Result: Node connectivity check passed
Verification of node connectivity was successful.
[oracle@rac1 grid]$
There is no error message for the connectivity check.
Issue disappeared after rebooting both RAC nodes.
Friday, April 25, 2014
Thursday, February 20, 2014
Using SERVERERROR Trigger to troubleshooting application error like ORA-00001
oracle@solaris:~$ sqlplus / SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 20 22:57:52 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set serveroutput on SQL> set echo on SQL> @trigger.sql SQL> CREATE TABLE servererror_log ( 2 error_datetime TIMESTAMP, 3 error_user VARCHAR2(30), 4 db_name VARCHAR2(9), 5 l_server_error number, 6 error_stack VARCHAR2(2000), 7 captured_sql VARCHAR2(1000)) 8 / Table created. SQL> SQL> CREATE OR REPLACE TRIGGER log_server_errors 2 AFTER SERVERERROR 3 ON DATABASE 4 DECLARE 5 sql_text ora_name_list_t; 6 stmt clob; 7 n number; 8 l_server_error number; 9 BEGIN 10 n := ora_sql_txt(sql_text); 11 if n > 1000 then n:= 1000; end if ; 12 FOR i IN 1..n LOOP 13 stmt := stmt || sql_text(i); 14 END LOOP; 15 16 l_server_error := server_error(1); 17 18 INSERT INTO servererror_log 19 (error_datetime, error_user, db_name,l_server_error, 20 error_stack, captured_sql) 21 VALUES 22 (systimestamp, sys.login_user, sys.database_name,l_server_error, 23 dbms_utility.format_error_stack, stmt); 24 commit; 25 END log_server_errors; 26 / Trigger created. SQL> SQL> drop table t1 purge; Table dropped. SQL> create table t1 (id number primary key); Table created. SQL> insert into t1 values (1); 1 row created. SQL> insert into t1 values (1); insert into t1 values (1) * ERROR at line 1: ORA-00001: unique constraint (OPS$ORACLE.SYS_C0011730) violated SQL> commit; Commit complete. SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS'; Session altered. SQL> select * from servererror_log; ERROR_DATETIME --------------------------------------------------------------------------- ERROR_USER DB_NAME L_SERVER_ERROR ------------------------------ --------- -------------- ERROR_STACK -------------------------------------------------------------------------------- CAPTURED_SQL -------------------------------------------------------------------------------- 20-FEB-14 10.58.14.724928 PM OPS$ORACLE ORCL 1 ORA-00001: unique constraint (OPS$ORACLE.SYS_C0011730) violated insert into t1 values (1)
Wednesday, February 12, 2014
Oracle Database 12.1 Upgrade New Features and Changes
New DBUA Interface
- DBUA is now divided into 2 panels (initially introduced with 11.2 OUI), the left panel lists all the steps executed by DBUA and the right panel executes and shows the progress of all operations listed in the left panel.
New Pre-Upgrade Information Tool
- Oracle Database 12c introduces the preupgrd.sql Pre-Upgrade Information Tool which replaces the utlu121i.sql script and earlier versions
- With the new Pre-Upgrade Information Tool, the default behavior of the prerequisite upgrade checks has been enhanced in the following ways:
- A log file, preupgrade.log, is created containing the output of the Pre-Upgrade Information Tool.
- The preupgrade_fixups.sql script is created
- The postupgrade_fixups.sql script is created
- These files are created under $ORACLE_BASE/cfgtoollogs/<SID>/preupgrade
Enhanced Upgrade Automation
- New parallel upgrade script which is a default feature in Oracle Database
- SERVER ( catalog , catproc ) component upgrade scripts get executed in parallel, for independent sub-components
- Parallel upgrade is driven by a PERL script:
$ catctl.pl
Command Line Upgrade
- In this release, the new Parallel Upgrade Utility, catctl.pl, provides parallel upgrade options that reduce downtime.
cd $ORACLE_HOME%rdbms\admin
$ORACLE_HOME\perl\bin\perl catctl.pl catupgrd.sql
Sunday, February 9, 2014
what kinds of statistics oracle optimizer relying on
Table statistics
Number of rows
Number of blocks
Average row length
Column statistics
Number of distinct values (NDV) in a column
Number of nulls in a column
Data distribution (histogram)
Extended statistics
Index statistics
Number of leaf blocks
Number of levels
Index clustering factor
System statistics
I/O performance and utilization
CPU performance and utilization
Sql server post for this week
(SFTW) SQL Server Links 07/02/14
http://www.johnsansom.com/sftw-sql-server-links-070214/
Saturday, February 8, 2014
Log mining SQL server Transaction Log
use AdventureWorks2012
go
dbcc sqlperf(logspace)
/*-----------------------------
Database Name Log Size (MB) Log Space Used (%) Status
master 2.242188 26.02352 0
tempdb 0.7421875 59.01316 0
model 0.4921875 90.77381 0
msdb 0.7421875 49.21053 0
AdventureWorks2012 0.9921875 55.56102 0
ReportServer 1.054688 44.02778 0
ReportServerTempDB 1.054688 43.84259 0
---------------------------*/s
go
select * from sys.dm_db_log_space_usage
/*-----------------------------
database_id total_log_size_in_bytes used_log_space_in_bytes used_log_space_in_percent log_space_in_bytes_since_last_backup
5 1040384 578048 55.56102 101888
---------------------------*/
go
select * into dbo.emplyee_new from HumanResources.Employee
go
select o.name, o.type,o.create_date,o.modify_date,m.definition from sys.all_objects o inner join sys.all_sql_modules m
on o.object_id=m.object_id
where name like '%dblog%'
/*---------------------------
name type create_date modify_date
fn_dblog IF 2011-11-04 21:26:15.210 2011-11-04 21:27:08.297
fn_dblog_xtp IF 2013-10-03 18:03:58.533 2013-10-03 18:03:58.567
fn_dump_dblog IF 2011-11-04 21:26:16.583 2011-11-04 21:27:08.300
fn_dump_dblog_xtp IF 2013-10-03 18:03:58.580 2013-10-03 18:03:58.597
---------------------------*/
GO
select [Current LSN], Context, [Operation],AllocUnitName from sys.fn_dblog(null,null)
-- where [Current LSN]> '0000002d:0000017b:0002'
go
select [Current LSN],[Begin Time],[Transaction Name] from sys.fn_dblog(null,null) where [Transaction Name] is not null
go
select convert(char(30),AllocUnitName) as AllocUnitName,
sum(convert(numeric, [Log Record Length])) as log_record_bytes,
sum(convert(numeric, [Log Reserve])) as log_reserve_bytes from sys.fn_dblog('45:379:1',null)
group by AllocUnitName order by log_reserve_bytes desc
/*---------------------------
AllocUnitName log_record_bytes log_reserve_bytes
------------------------------ --------------------------------------- ---------------------------------------
dbo.emplyee_new 75956 136732
NULL 1360 36966
sys.sysobjvalues.clst 1540 297
sys.sysprufiles.clst 244 209
sys.sysiscols.clst 128 74
sys.sysiscols.nc1 116 74
sys.sysidxstats.nc 164 74
sys.sysidxstats.clst 196 74
Unknown Alloc Unit 4216 0
sys.sysschobjs.clst 420 0
---------------------------*/
go
select * from fn_dblog_xtp(null,null)
go
select * from sys.database_files where type_desc='LOG'
GO
delete from dbo.emplyee_new where SickLeaveHours>20
go
BACKUP LOG [AdventureWorks2012] TO DISK = N'C:\SampleDB\AdventureWorks2012_log.bak'
WITH NOFORMAT, NOINIT,
NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
select * from fn_dump_dblog(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.ldf',
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL)
select * from fn_dump_dblog(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.bak',
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL)
go
select * from fn_dump_dblog_xtp(null,null,'DISK',1,'C:\SampleDB\AdventureWorks2012_log.bak',
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL)
go
How to clean SQL Buffer and Plan Cache
checkpoint
DBCC FreeProcCache
DBCC FreeSystemCache
DBCC FlushProcInDB()
DBCC DropCleanBuffers
Subscribe to:
Posts (Atom)