If your database was started in restricted mode you can encounter following error
SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 12:39:28 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter user-name: sys@ora12c as sysdba Enter password: ERROR: ORA-12526: TNS:listener: all appropriate instances are in restricted mode
To solve this problem you need to make static registration of your service in listener.ora.
In case you need to assign new name “new_host_name” for your host execute following steps
1. Modify “/etc/hosts” file to contain a fully qualified name for the server.
<IP-address> <fully-qualified-machine-name> <machine-name>
Enter following data to “/ect/hosts”
127.0.0.1 localhost.localdomain localhost 192.168.0.50 new_host_name.dbaora.com new_host_name
2. Modify “/etc/sysconfig/network” file
NETWORKING=yes HOSTNAME=new_host_name.dbaora.com
3. Reboot your machine
Have a fun 🙂
Tomasz
This article presents how to remove old kernels on OEL5 and OEL6.
Removing old kernels is pretty simple. In this article I show how to do it on OEL5 but it works in the same way on OEL6.
1. Check kernels which are installed on your system.
[root@oel5 ~]# rpm -qa | grep kernel kernel-uek-2.6.39-400.21.2.el5uek kernel-uek-2.6.39-300.26.1.el5uek kernel-headers-2.6.18-348.4.1.0.1.el5 kernel-uek-devel-2.6.39-400.21.2.el5uek kernel-uek-firmware-2.6.39-300.26.1.el5uek kernel-uek-firmware-2.6.39-400.109.1.el5uek kernel-uek-firmware-2.6.39-400.21.2.el5uek kernel-2.6.18-348.4.1.0.1.el5 kernel-debug-2.6.18-348.4.1.0.1.el5 kernel-xen-2.6.18-348.4.1.0.1.el5 kernel-2.6.18-348.el5 kernel-debug-2.6.18-348.el5 kernel-devel-2.6.18-348.4.1.0.1.el5 kernel-xen-2.6.18-348.el5 kernel-uek-devel-2.6.39-400.109.1.el5uek kernel-devel-2.6.18-348.el5 kernel-uek-devel-2.6.39-300.26.1.el5uek kernel-uek-2.6.39-400.109.1.el5uek
2. Check your current kernel which you are using
[root@oel5 ~]# uname -r 2.6.39-400.109.1.el5uek
3. Remove old kernel(s) using command: yum remove kernel-package
It’s just example for removing one kernel
[root@oel5 ~]# yum remove kernel-2.6.18-348.el5 Loaded plugins: rhnplugin, security This system is not registered with ULN. You can use up2date --register to register. ULN support will be disabled. Setting up Remove Process Resolving Dependencies There are unfinished transactions remaining. You might consider running yum-complete-transaction first to finish them. The program yum-complete-transaction is found in the yum-utils package. --> Running transaction check ---> Package kernel.x86_64 0:2.6.18-348.el5 set to be erased --> Finished Dependency Resolution Dependencies Resolved ===================================================================== Package Arch Version Repository Size ===================================================================== Removing: kernel x86_64 2.6.18-348.el5 installed 99 M Transaction Summary ===================================================================== Remove 1 Package(s) Reinstall 0 Package(s) Downgrade 0 Package(s) Is this ok [y/N]: y Downloading Packages: Running rpm_check_debug Running Transaction Test Finished Transaction Test Transaction Test Succeeded Running Transaction Erasing : kernel 1/1 Removed: kernel.x86_64 0:2.6.18-348.el5 Complete!
Have a fun 🙂
Tomasz
NULL understanding in SQL and PL/SQL is fundamental knowledge required to be a good DBA and developer and to avoid surprises.
NULL – value that is unavailable, unassigned, unknown or inapplicable.
NULL is not the same as zero or a blank space.
This article presents problems related to ORA-39726, ORA-14097 on compressed table.
In case you want to drop a column on a compressed table (partitioned or not) you can encounter following error:
ORA-39726: unsupported add/drop column operation on compressed tables
This error can occur for following type of compression:
Exception is OLTP compression – drop is working here. You can read about compress OLTP here – Table compression in 11G
It’s useful to call UNIX/Windows command directly from PL/SQL. One of method is to use Java language embedded in database.
In case you need to analyze some complex queries you can do quick visualization of the query using nice feature of sqldeveloper: Query Builder.
Just enter your query to sqldeveloper tab “Worksheet” and switch to second tab “Query Builder” so you can see visualization of your query.
You can also build your query directly in “Query Builder” by drag and drop tables from Connections Navigator display.
Have a fun 🙂
Tomasz
This article presents how you can control access path for a table.
Access path – how Oracle will read data from a single table. If a hint specifies an unavailable access path, then the optimizer ignores it.
it’s recommended to read following article:
In case you don’t have Oracle Enterprise Manager at hand you can always generate quick reports in sqldeveloper that will show activity on your database.
Oracle metrics
There are tones of metrics which are collected by database on regular bases and a lot of them are very interesting.
Metrics definition can be found in view V$METRIC. There are about 200 metrics in 11g some examples of them below
There are a lot of options which can be used in database. Not all are available in your database. It depends which version of database you are using (standard/enterprise or else) or if a given option is active/tuned off/on. You can find details in V$OPTION.
select * from v$option; PARAMETER VALUE ----------------------------------------- ----- Active Data Guard FALSE Advanced Compression TRUE Backup Encryption TRUE Basic Compression TRUE ... Spatial TRUE Streams Capture TRUE Unused Block Compression TRUE XStream TRUE 64 rows selected