Sometimes for example when you are updating database you might need to check that sequences last used value does not change. This can be done running following SQL before and after update and then comparing the result sets (this show only given schema sequences):
SQL> spool test_schema_name_sequences_last_num_before.txt
SQL> set pages 120
SQL> set lines 120
SQL> select SEQUENCE_OWNER,SEQUENCE_NAME,LAST_NUMBER from dba_sequences where SEQUENCE_OWNER = '<TEST_SCHEMA_NAME'>) order by 2;
SQL> spool off;
# Again after changes:
SQL> spool test_schema_name_sequences_last_num_after.txt
SQL> set pages 120
SQL> set lines 120
SQL>
select SEQUENCE_OWNER,SEQUENCE_NAME,LAST_NUMBER from dba_sequences
where SEQUENCE_OWNER = '<TEST_SCHEMA_NAME'>) order by 2;
SQL> spool off;
NOTE! Remember that last_number column value is aware of cache. If you have cache 20 last_number is increased 20 every time next cache set is taken. And because of this you can't depend only this column when cache is used you might need also check current_value from sequence. If cache is not used at all then last_number value is increased only when next value is taken from sequence and last_number is same as current_value.
If you want to get schema sequences creation clauses out of the database you can do it with following SQL:
SQL> spool test_schema_name_sequences_ddl.txt
SQL> set pages 120
SQL> set lines 120
SQL> select dbms_metadata.get_ddl('SEQUENCE',ds.sequence_name,ds.sequence_owner) from dba_sequences ds where ds.sequence_owner='<TEST_SCHEMA_NAME>';
SQL> spool off;
This blog contains useful and interesting issues/problems about different sql and nosql databases. Mainly Oracle.
Friday, June 28, 2013
Thursday, June 27, 2013
Oracle Statspack usage
Statspack is tool for performance monitoring and reporting.
New versions of Oracle (10 and 11) provides AWR (Automatic Workload Repository) reports with more detailed statistics than statspack.
But sometimes you might still need statspack for example with Oracle SE version databases.
Here is how you can get statspack report out of your database (First create tablespace for statspack before start install):
1. Install statspack (this creates PERFSTAT schema and this asks you to give tablespace name for statspack)(Run as oracle user (and SYS)):
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spcreate.sql
2. Take snapshots for statspack report (you need at least 2 snapshots to generate report):
sqlplus perfstat/<password>
exec statspack.snap;
You can also give detail levels for snapshots (Default level is 5). Levels vary between Oracle versions.
Oracle 11.2 gives following levels:
SQL> select * from stats$level_description;
SNAP_LEVEL
----------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
0
This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information
5
This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels
6
This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels
7
This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels
10
This level includes capturing Child Latch statistics, along with all data captured by lower levels
----------------------------------
Take snapshot with certain level:
sqlplus perfstat/<password>
exec statspack.snap(i_snap_level=>10);
It is also possible to collect snapshots automatically via dbms_jobs (spauto.sql script) or with your own cron script.
3. Generate statspack report (This list available snapshots and asks you to give begin and end snapshot for report):
sqlplus perfstat/<password>
@?/rdbms/admin/spreport
You can also check available snapshots from here:
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
If you need a help for statspack report analyzing check these:
statspackanalyzer.com
http://filebank.orapub.com/cgi-bin/quickUOWTBA.cgi
4. Purge statspack snapshots (You can purge old or all statspack snapshots from database):
Purge 10 days older snapshots:
sqlplus perfstat/<password>
exec statspack.purge(sysdate-10);
5. If you want to remove statspack schema and data from your database you can do it this way (Run as oracle user (and SYS)):
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql
New versions of Oracle (10 and 11) provides AWR (Automatic Workload Repository) reports with more detailed statistics than statspack.
But sometimes you might still need statspack for example with Oracle SE version databases.
Here is how you can get statspack report out of your database (First create tablespace for statspack before start install):
1. Install statspack (this creates PERFSTAT schema and this asks you to give tablespace name for statspack)(Run as oracle user (and SYS)):
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spcreate.sql
2. Take snapshots for statspack report (you need at least 2 snapshots to generate report):
sqlplus perfstat/<password>
exec statspack.snap;
You can also give detail levels for snapshots (Default level is 5). Levels vary between Oracle versions.
Oracle 11.2 gives following levels:
SQL> select * from stats$level_description;
SNAP_LEVEL
----------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
0
This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information
5
This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels
6
This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels
7
This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels
10
This level includes capturing Child Latch statistics, along with all data captured by lower levels
----------------------------------
Take snapshot with certain level:
sqlplus perfstat/<password>
exec statspack.snap(i_snap_level=>10);
It is also possible to collect snapshots automatically via dbms_jobs (spauto.sql script) or with your own cron script.
3. Generate statspack report (This list available snapshots and asks you to give begin and end snapshot for report):
sqlplus perfstat/<password>
@?/rdbms/admin/spreport
You can also check available snapshots from here:
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
If you need a help for statspack report analyzing check these:
statspackanalyzer.com
http://filebank.orapub.com/cgi-bin/quickUOWTBA.cgi
4. Purge statspack snapshots (You can purge old or all statspack snapshots from database):
Purge 10 days older snapshots:
sqlplus perfstat/<password>
exec statspack.purge(sysdate-10);
5. If you want to remove statspack schema and data from your database you can do it this way (Run as oracle user (and SYS)):
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql
Oracle Windows OPatch Prerequisite check "CheckActiveFilesAndExecutables" (Following files are active) failed.
When you are running Oracle OPatch patches in Windows environment you might get following error:
Running prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:
Following files are active :
C:\oracle\product\10.2.0\db_1\bin\oci.dll
.
.
.
Patching guide says that before patching you should stop all Oracle services along with the "Distributed Transaction Coordinator" service. But sometimes this is not enough and you get "ActiveFiles" error when trying to run OPatch.
Usually problem is Windows Management Instrumentation (WMI) service. Restart this service from Services tool or you can also end process "WMIPrvSE.exe" from Task Manager (it will restart automatically). Then try to run OPatch again.
From following MOS (My Oracle Support) document you can find more help to this error:
Files in Use errors, when applying patches on Windows [ID 418479.1]
There is also Process Explorer tool which you can use to check which process is using certain ddl's:
http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx
Running prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:
Following files are active :
C:\oracle\product\10.2.0\db_1\bin\oci.dll
.
.
.
Patching guide says that before patching you should stop all Oracle services along with the "Distributed Transaction Coordinator" service. But sometimes this is not enough and you get "ActiveFiles" error when trying to run OPatch.
Usually problem is Windows Management Instrumentation (WMI) service. Restart this service from Services tool or you can also end process "WMIPrvSE.exe" from Task Manager (it will restart automatically). Then try to run OPatch again.
From following MOS (My Oracle Support) document you can find more help to this error:
Files in Use errors, when applying patches on Windows [ID 418479.1]
There is also Process Explorer tool which you can use to check which process is using certain ddl's:
http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx
Wednesday, June 5, 2013
MySQL flush logs; gives: ERROR 1105 (HY000): Unknown error
If you trying to run "flush logs;" from mysql or with mysqladmin you'll get following error message:
ERROR 1105 (HY000): Unknown error
There are bugs that might affect like this but usually first thing to do with this is check log paths permissions.
1. First check what log directories your MySQL is using:
mysql> show global variables like '%log%';
You can also use more specific checks like:
This show error log:
mysql> show global variables like '%err%';
or for general log:
mysql> show global variables like '%general%';
or for slow log:
mysql> show global variables like '%slow%';
For example:
mysql> show global variables like '%err%';
+--------------------+----------------------+
| Variable_name | Value |
+--------------------+----------------------+
| error_count | 0 |
| log_error | /data/mysql/logs/mysqld_error.log |
| max_connect_errors | 10 |
| max_error_count | 64 |
| slave_skip_errors | OFF |
+--------------------+----------------------+
5 rows in set (0.00 sec)
2. After you know log paths check that mysql user (or user that run mysqld if it is other than mysql.) have permissions to write in those directories (remember to check all log paths you are using).
ls -latr /data00/mysql/
drwxr-xr-x 14 mysql mysql 4096 Mar 26 06:46 logs
If there is not enough permissions then add them and the error is gone. If permissions are correct then you are probably hitting the bug.
ERROR 1105 (HY000): Unknown error
There are bugs that might affect like this but usually first thing to do with this is check log paths permissions.
1. First check what log directories your MySQL is using:
mysql> show global variables like '%log%';
You can also use more specific checks like:
This show error log:
mysql> show global variables like '%err%';
or for general log:
mysql> show global variables like '%general%';
or for slow log:
mysql> show global variables like '%slow%';
For example:
mysql> show global variables like '%err%';
+--------------------+----------------------+
| Variable_name | Value |
+--------------------+----------------------+
| error_count | 0 |
| log_error | /data/mysql/logs/mysqld_error.log |
| max_connect_errors | 10 |
| max_error_count | 64 |
| slave_skip_errors | OFF |
+--------------------+----------------------+
5 rows in set (0.00 sec)
2. After you know log paths check that mysql user (or user that run mysqld if it is other than mysql.) have permissions to write in those directories (remember to check all log paths you are using).
ls -latr /data00/mysql/
drwxr-xr-x 14 mysql mysql 4096 Mar 26 06:46 logs
If there is not enough permissions then add them and the error is gone. If permissions are correct then you are probably hitting the bug.
Monday, June 3, 2013
Oracle Cluster Verification Utility (cluvfy) tracing.
If you get errors from Cluster Verification Utility (cluvfy) then you might need to get trace log out of it.
For example if you are creating Service Request about cluvfy into My Oracle Support you might add trace log into SR.
How to get trace log from cluvfy (run these as oracle user) :
1. Create directory for log:
mkdir /tmp/cvutrace
2. Set ENV variables:
export CV_TRACELOC=/tmp/cvutrace
export SRVM_TRACE=true
export SRVM_TRACE_LEVEL=1
3. Run wanted cluvfy check:
runcluvfy.sh .....
For example:
/data/install/grid/runcluvfy.sh stage -pre crsinst -n node1,node2 -r 11gR2 -verbose
After the cluvfy run is completed you'll have its trace log in the directory you created.
Copy it and add into the SR.
More info from MOS (My Oracle Support):
MOS ID 986822.1
For example if you are creating Service Request about cluvfy into My Oracle Support you might add trace log into SR.
How to get trace log from cluvfy (run these as oracle user) :
1. Create directory for log:
mkdir /tmp/cvutrace
2. Set ENV variables:
export CV_TRACELOC=/tmp/cvutrace
export SRVM_TRACE=true
export SRVM_TRACE_LEVEL=1
3. Run wanted cluvfy check:
runcluvfy.sh .....
For example:
/data/install/grid/runcluvfy.sh stage -pre crsinst -n node1,node2 -r 11gR2 -verbose
After the cluvfy run is completed you'll have its trace log in the directory you created.
Copy it and add into the SR.
More info from MOS (My Oracle Support):
MOS ID 986822.1
Wednesday, May 29, 2013
Oracle shared server processes generates huge trace files in diag > rdbms > trace directory
There is Oracle bug that can cause shared server processes to generate huge trace files in the diag > rdbms > trace directory.
If you are using RAC this problem usually appears only in one node at the time.
Inside trace files you can find following errors (reparse is main thing to watch):
-----------
kksfbc: entering reparse diagnosis mode for xsc:0x7381dcbd0
...
and
Check cursor failed ctx=(nil) xscflg=110632 ctxflg=0 flg=102
kksSearchChildList: no suitable child found (hash_match=0)
...
and
Current Cursor Sharing Diagnostics Nodes:
Child Node: 2 ID=40 reason=Bind mismatch(8) size=4x4
...
-----------
This bug itself is not fatal if you have enough free space in disk where diag directory is.
But if that disk run out of free space then alert log will start to log errors about the trace file writing.
And if the diag directory is in the default place there will be probably other log and files in the same disk which will cause you more problems when the disk is full. For example listener log.
There is fix for this bug in MOS (My Oracle Support) and also more information (MOS ID):
10373013.8 (you can check patches from Patch & Updates > search 10373013)
NOTE! The bug is already fixed in 11.2.0.3 and 12.1 versions. And some patch updates of 11.2.0.2 . Quick workaround for this problem is empty trace files for example this way in Linux:
cat /dev/null > trace_file_name.trc . If you just delete the file it will not free used space until you restart database. But above emptying will free space immediately. But this is just workaround and error can hit again later. So if you get these often it is better to use bug fix or upgrade the database into new version.
If you are using RAC this problem usually appears only in one node at the time.
Inside trace files you can find following errors (reparse is main thing to watch):
-----------
kksfbc: entering reparse diagnosis mode for xsc:0x7381dcbd0
...
and
Check cursor failed ctx=(nil) xscflg=110632 ctxflg=0 flg=102
kksSearchChildList: no suitable child found (hash_match=0)
...
and
Current Cursor Sharing Diagnostics Nodes:
Child Node: 2 ID=40 reason=Bind mismatch(8) size=4x4
...
-----------
This bug itself is not fatal if you have enough free space in disk where diag directory is.
But if that disk run out of free space then alert log will start to log errors about the trace file writing.
And if the diag directory is in the default place there will be probably other log and files in the same disk which will cause you more problems when the disk is full. For example listener log.
There is fix for this bug in MOS (My Oracle Support) and also more information (MOS ID):
10373013.8 (you can check patches from Patch & Updates > search 10373013)
NOTE! The bug is already fixed in 11.2.0.3 and 12.1 versions. And some patch updates of 11.2.0.2 . Quick workaround for this problem is empty trace files for example this way in Linux:
cat /dev/null > trace_file_name.trc . If you just delete the file it will not free used space until you restart database. But above emptying will free space immediately. But this is just workaround and error can hit again later. So if you get these often it is better to use bug fix or upgrade the database into new version.
Monday, May 13, 2013
IOPS check from Oracle 11 server
If you want to check Oracle database server IOPS (Input/Output Operations Per Second) usage you can do it with following SQL:
SQL> break on report
SQL> compute sum label TOTAL_IOPS: of IOPS on report
SQL> select METRIC_NAME,avg(AVERAGE) as "IOPS"
from dba_hist_sysmetric_summary
where METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec')
group by METRIC_NAME;
For example:
SQL> break on report
SQL> compute sum label TOTAL_IOPS: of IOPS on report
SQL> select METRIC_NAME,avg(AVERAGE) as "IOPS"
2 from dba_hist_sysmetric_summary
3 where METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec')
4 group by METRIC_NAME;
METRIC_NAME IOPS
---------------------------------------------------------------- ----------
Physical Write Total IO Requests Per Sec 32.8448519
Physical Read Total IO Requests Per Sec 13.3675773
----------
TOTAL_IOPS: 46.2124292
NOTE! DBA_HIST_SYSMETRIC_SUMMARY contains snapshots of V$SYSMETRIC_SUMMARY view. V$SYSMETRIC_SUMMARY displays a summary of all system Metric values for the long-duration system metrics. The average, maximum value, minimum value, and the value of one standard deviation for the last hour are displayed for each metric item. And because of this if you are using V$SYSMETRIC_SUMMARY instead of DBA_HIST_SYSMETRIC_SUMMARY you will get different results .
SQL> break on report
SQL> compute sum label TOTAL_IOPS: of IOPS on report
SQL> select METRIC_NAME,avg(AVERAGE) as "IOPS"
from dba_hist_sysmetric_summary
where METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec')
group by METRIC_NAME;
For example:
SQL> break on report
SQL> compute sum label TOTAL_IOPS: of IOPS on report
SQL> select METRIC_NAME,avg(AVERAGE) as "IOPS"
2 from dba_hist_sysmetric_summary
3 where METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec')
4 group by METRIC_NAME;
METRIC_NAME IOPS
---------------------------------------------------------------- ----------
Physical Write Total IO Requests Per Sec 32.8448519
Physical Read Total IO Requests Per Sec 13.3675773
----------
TOTAL_IOPS: 46.2124292
NOTE! DBA_HIST_SYSMETRIC_SUMMARY contains snapshots of V$SYSMETRIC_SUMMARY view. V$SYSMETRIC_SUMMARY displays a summary of all system Metric values for the long-duration system metrics. The average, maximum value, minimum value, and the value of one standard deviation for the last hour are displayed for each metric item. And because of this if you are using V$SYSMETRIC_SUMMARY instead of DBA_HIST_SYSMETRIC_SUMMARY you will get different results .
Subscribe to:
Posts (Atom)