Hi Folks,
Today I’m leaving wordpress.com as the base of my blog. I’ve moved to my own domain named beyondoracle.com. This change will provide me to use much more features of wordpress than the one provided in the wordpress.com service.
I also updated the look of the new site to a cleaner simplistic design.
So, please check it out at http://www.beyondoracle.com and I hope you like the changes and continue beeing a regular reader 
Cya
J
Posted in Uncategorized
If any of you ever lost the weblogic adminserver password or any other weblogic server (for instance your forms and reports server 11g enterprise manager password), and want to reset it, these are the steps to do it on weblogic 10.3 (11g)
# set environment variables
[oracle@app ~]$ export MWHOME=/u01/app/oracle/product/middleware
[oracle@app ~]$ export DOMAIN_HOME=$MWHOME/user_projects/domains/base_domain
[oracle@app ~]$ export CLASSPATH=$CLASSPATH:$MWHOME/wlserver_10.3/server/lib/weblogic.jar
[oracle@app ~]$ cd $DOMAIN_HOME/bin
[oracle@app bin]$ . ./setDomainEnv.sh
# creating a new default authenticator
[oracle@app bin] cd ../security
[oracle@app security] mv DefaultAuthenticatorInit.ldift oldDefaultAuthenticator
[oracle@app security] java weblogic.security.utils.AdminAccount weblogic mynewpass .
# removing boot.properties if exists
[oracle@app security] cd ../servers/AdminServer
[oracle@app AdminServer] mv data data_old
[oracle@app AdminServer] cd security/
[oracle@app security] mv boot.properties oldboot.properties
# restart the admin server with the new password
[oracle@app security] cd $DOMAIN_HOME
[oracle@app security] ./startWebLogic.sh
Hope it helps someone …
Today after I exported a schema and trying to import it to another schema (using remap_schema), I’ve hit the bug 6666291. The cause of this problem is an DataPump export file created with compression parameter set to “data_only” or “all”.
expdp “‘sys/xxxxxx as sysdba’” schemas=myschema dumpfile=dumpfilename.dmp compression=all
data is exported successfully.
impd dumpfile=dumpfilename.dmp remap_schema=myschema:otherschema
The errors I hit:
ORA-02354: error in exporting/importing data
ORA-39776: fatal Direct Path API error loading table “<table name>”
ORA-00600: internal error code, arguments: [klaprs_11], [], [], [], [], [], [],
To fix the issue, you can apply 11.1.0.7 patchset or upgrade to 11.2 release or apply oneoff Patch 6368018. Or, you can just export your data without the compression clause.
Regards
J
Posted in dba
As you probably know, database must be started before any other RMAN commands are issued. So… how to solve this issue if you have 100% that your database is open and working? Take a look at my case and my environment variables:
ORACLE_SID=dw
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1/
ORACLE_BASE=/u01/app/oracle/
[oracle@db env]$ rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Thu Oct 8 15:08:53 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN>
But I am 100% sure that my database is online
So what’s happening here? Take a look at my ORACLE_HOME… Do you find anything strange on it’s path?
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1/
You’re right… I have an extra slash “/” at the end of the path… RMAN strangely thinks my database is not online if the ORACLE_HOME ends with “/” … It works with lsnrctl, emctl and other oracle utils but with RMAN and SQLPLUS won’t work. So the error was a typo… In the bash a command like “cd /u01//app” works, but in this case RMAN and SQLPLUS got confused.
[oracle@db env]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1/
[oracle@db env]$ rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Thu Oct 8 15:19:10 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> exit
Recovery Manager complete.
[oracle@db env]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@db env]$ rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Thu Oct 8 15:19:15 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DW (DBID=3334851943)
RMAN>
Sometimes, Oracle patchset silent installation is done with a “Response File” (patchset.rsp) supplied with the patchset software. The process is documented in the README for each patchset.
What is sometimes unknown is that the same silent patchset installation can be done directly from the command prompt without response file, by using a minimum set of variables (from response file) as arguments to the OUI (i.e “runInstaller” or “setup.exe”).
For example on Linux and on patchet 10.2.0.4, following command can be used to install Oracle Database Patchset software in silent mode:
[root@app ~]# su – oracle
[oracle@app ~]$ cd /soft/db10g/patch10204/Disk1
[oracle@app Disk1]$
[oracle@app Disk1]$ ./runInstaller -silent -force -debug \
FROM_LOCATION=”/soft/db10g/patch10204/Disk1/stage/products.xml” \
ORACLE_HOME=”/opt/oracle/product/10.2.0/db_1″ \
ORACLE_HOME_NAME=”OraDb10g_home1″ \
TOPLEVEL_COMPONENT=’{“oracle.patchset.db”,”10.2.0.4.0″}’
We just need to specify the products.xml location from the patchset directory, the ORACLE_HOME path and the ORACLE_HOME name.
We can also do it on windows like this:
C:> cd c:\soft\db10g\patch10204\Disk1
C:> setup.exe -silent -force -debug FROM_LOCATION=”c:\soft\db10g\patch10204\Disk1\stage\products.xml” ORACLE_HOME=”C:\app\oracle\product\10.2.0\db_1″ ORACLE_HOME_NAME=”OraDb10g_home1″ TOPLEVEL_COMPONENT={“oracle.patchset.db”,”10.2.0.4.0″}
Hope it helps someone with problems running the patchset GUI installer…
As most of you probably know, an AMI (Amazon Machine Image) is a pre-built package of software which can be used to create or instantiate a virtual machine within the Amazon Elastic Compute Cloud (EC2).
Amazon EC2 allows anyone with few bucks on the credit card to rent computers on which to run their own applications. EC2 provides scalable deployment of applications by using a web services interface through which we can create virtual machines on which we can load any software of our choice.
Oracle released, about 7 months ago, AMI’s in the US Amazon regions (US based data centers), that come with pre-installed Oracle Linux and Oracle Database 11g. Unfortunately in Europe we didn’t have those images available and most of us had to rely on Centos or Redhat AMI’s and then download and install Oracle software and create the database.
So, it seems these last days, quietly, Oracle released Oracle AMI’s for Europe region (London based data center) . Now it’s much faster to create Oracle Instances with 11g up and running…
The AMI’s ID’s are:
32 bit:
ami-087a517c
Oracle 11.1.0.7 Enterprise Edition
oracle-corporation-eu/database-ami/32-bit/oracle_11107_EE_32Bit-image.manifest.xml
ami-0a7a517e
Oracle 11.1.0.7 Standard Edition / SE One
oracle-corporation-eu/database-ami/32-bit/oracle_11107_SE_SE1_32Bit-image.manifest.xml
64 bit:
ami-587b502c
Oracle 11.1.0.7 Standard Edition / SE One
oracle-corporation-eu/database-ami/64-bit/oracle_11107_SE_SE1_64Bit-image.manifest.xml
ami-5e7b502a
Oracle 11.1.0.7 Enterprise Edition
oracle-corporation-eu/database-ami/64-bit/oracle_11107_EE_64Bit-image.manifest.xml
We can create the type of instance that we want, depending on our processing power needs we can choose different types of instances… The most common are:
Small Instance – The small instance (default) equates to a system with 1.7 GB of memory, 1 EC2 Compute Unit (1 virtual core with 1 EC2 Compute Unit), 160 GB of instance storage, 32-bit platform
Large Instance – The large instance represents a system with 7.5 GB of memory, 4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units each), 850 GB of instance storage, 64-bit platform
Extra Large Instance – The extra large instance offers the equivalent of a system with 15 GB of memory, 8 EC2 Compute Units (4 virtual cores with 2 EC2 Compute Units each), 1690 GB of instance storage, 64-bit platform.
You have also High CPU instances that have proportionally more CPU resources than memory (RAM) and address compute-intensive applications.
Happy instantiation
An Amazon Machine Image (AMI) is a pre-built package of software which can be used to create or instantiate a virtual machine within the Amazon Elastic Compute Cloud.
An AMI forms the basic unit of deployment for services delivered using EC2 and typically includes the operating system (for example Linux, UNIX, or Windows) and additional software necessary to deliver a service.
A Quick intro… Take a look at the following issues:
Symptom : Oracle Database Configuration Assistant (DBCA) stalled while creating the database
Symptom : Oracle IAS 10G stalled during software installation with message “Please wait, this will take a moment”
Symptom : error while loading shared libraries: $ORACLE_HOME/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied
If you ever had any of the previous symptons while trying to install Oracle Software, create a instance / database with DBCA, create a listener or even run sqlplus… then you probably have SELinux mode set to “Enforcing”.
Security-Enhanced Linux (SELinux) is a Linux feature that provides a variety of security policies, including U.S. Department of Defense style mandatory access controls, through the use of Linux Security Modules (LSM) in the Linux kernel. It is not a Linux distribution, but rather a set of modifications that can be applied to Unix-like operating systems, such as Linux and BSD.
Under Oracle Enterprise Linux or Redhat Enterprise Linux, if needed we can switch SELinux from the default “Enforcing” mode that it is running in, to the “Permissive” mode by running following commands as root user:
[root@server~]# getenforce
Enforcing
[root@server~]# setenforce 0
[root@server~]# getenforce
Permissive
The previous commandes changed the default mode to “permissive” and allows SELinux to continue running, and logging denial messages, but will not deny any operations. If you would like to return to Enforcing mode just run as root:
[root@server~]# setenforce 1
[root@server~]# getenforce
Enforcing
Another way to temporarily disable (0) or enable (1) SELinux is to run one of the following commands:
[root@server~]# echo 0 > /selinux/enforce
[root@server~]# echo 1 > /selinux/enforce
The previous commands are immediate, and will remain in effect until the next reboot. If you want to make “Permissive” mode permanent you must add “enforcing=0″ to the kernel boot line that usually is at /etc/grub.conf file. For instance:
# grub.conf generated by anaconda
#
default=0
timeout=5
splashimage=(hd0,0)/boot/grub/splash.xpm.gz
hiddenmenu
title Enterprise Linux (2.6.18-92.el5PAE)
root (hd0,0)
kernel /boot/vmlinuz-2.6.18-92.el5PAE ro root=LABEL=/ enforcing=0
initrd /boot/initrd-2.6.18-92.el5PAE.img
.
.
Instead of editing grub we can configure SELinux by editing the file /etc/selinux/config and choose any of the following modes:
SELINUX=enforcing
or
SELINUX=permissive
or
SELINUX=disabled
After the next reboot the SELinux will comply to the permanent settings that we have choosen above.
Every Oracle Database must have a way of maintaining information that is used to rollback changes to the database. This information consists of records of the actions of transactions, primarily before they are committed. All these records are called undo.
When an user or application issue a rollback statement, undo records are used to undo changes that were made to the database by the transaction that were not commited.
When we have a large database, most of the long batch transactions that demand lot of undo work or purge operations will increase a lot the undo tablespace size. Depending on the size of these operations your database can have an undo tablespace from few MB to dozens GB.
To save space from an once required large undo tablespace, we may resize it to make it smaller. In my databases sometimes the size of my undo tablespace achieves 20GB.
We can resize the database datafiles with:
SQL> ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/mydb/undotbs01.dbf’ RESIZE 500M;
Most of the times when I try to resize the undo tablespace I encounter ORA-3297 error: file contains used data beyond the requested RESIZE value. This means that some undo information stills stored above the datafile size we want to set. We can check the most high used block to check the minimum size that we can resize a particular datafile. For that we can query the dba_free_space dictionary view.
Another way to set our undo tablespace to the size that we want is to create another undo tablespace, set it the default one, take offline the old and then just drop the big old tablespace.
To check your undo tablespace info issue the following statement:
SQL> SELECT name,value FROM v$parameter WHERE name IN (‘undo_management’,'undo_tablespace’);
NAME VALUE ------------------------ undo_management AUTO undo_tablespace UNDOTBS01
SQL>
SQL> CREATE UNDO TABLESPACE undotbs02
DATAFILE ‘/u01/app/oracle/oradata/mydb/undotbs02.dbf’ SIZE 1024M
REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 10240M;
SQL> ALTER SYSTEM SET undo_tablespace = ‘UNDOTBS02′;
SQL> ALTER TABLESPACE undotbs01 OFFLINE;
SQL> DROP TABLESPACE undotbs01 INCLUDING CONTENTS AND DATAFILES;
SQL>
With these steps we created a new undo tablespace, set it as the system default undo tablespace and drop the old tablespace including the datafiles.
Posted in dba | Tags: rollback, tablespace, undo
If you ever tried to start the Enterprise Manager with the command ‘emctl start dbconsole’ and you got stuck with the error:
OC4J Configuration issue. <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<YOURHOST>_<YOURDB> not found.
.
for instance:
OC4J Configuration issue. /opt/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_db.neoface.pt_ORCL not found.
.
Then the cause may be that Database Control is not configured or misconfigured… This can be verified by checking if the following directories exist in your ORACLE_HOME:
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<HOSTNAME>_<DBNAME>
<ORACLE_HOME>/<HOSTNAME>_<DBNAME>
.
If those directories do not exist, Database Control has not been configured for the database.
SOLUTION
To configure the enterprise manager database control for your database, issue the following commands as oracle user:
[oracle@neoface oracle]$ export ORACLE_HOME=<ORACLE_HOME>
[oracle@neoface oracle]$ export ORACLE_SID=<ORACLE_SID>
[oracle@neoface bin]$ cd $ORACLE_HOME/bin
[oracle@neoface bin]$ ./emca -config dbcontrol db -repos create
Now just follow the instructions on the screen… you’ll need the SYS, DBSNMP and SYSAUX passwords.
Sometimes you already have the SYSMAN, DBSNMP and SYSAUX accounts, and the previous EMCA command can fail to create the DBConsole repository the error ORA-20001 occurs.
ORA-20001 Sysman Already Exists While Running EMCA .
The Solution is simple… Drop the existing repository
[oracle@neoface oracle]$ cd $ORACLE_HOME/bin
[oracle@neoface bin]$ ./emca -deconfig dbcontrol db -repos drop
After removing the repository try to created it with the commands I explained previously. After you install the EM repositiry, the DBConsole should be started and you should be able to access the DBConsole using the URL provided at the installation screen.
(UPDATE)
If the previous drop procedure fails and by checking the logs we see an ORA-01031 insufficient privileges, this may be because we don’t have a password file created. Just go to $ORACLE_HOME/dbs and check if we have a file called pwd<sid>.ora. If not, create it…
[oracle@neoface oracle]$ cd $ORACLE_HOME/bin
[oracle@neoface bin]$ ./orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=oracle entries=5
Hope it helps!
Moving, copying or cloning a database from one server to another with different directory structures can be easily accomplished with RMAN. Imagine that you have a database on one node and you want to copy it to another node without shuting down your database and move your datafiles to a different directory structure… This will be demonstrated here by using RMAN.
ASSUMPTIONS
Source Database
Destiny Database
AT SERVER1
Logon as oracle user software owner at server1 and set your environment variables. Then open RMAN and backup the source database we want to copy /move / clone.
[oracle@neoface oracle]$ export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
[oracle@neoface oracle]$ export ORACLE_SID=neo
[oracle@neoface oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@neoface oracle]$ rman target /
RMAN> backup database plus archivelog;
The RMAN backup created the following files at /u01/backup:
cf_NEO_c-1689570411-20090106-00 (control file backup) back_NEO_675389594_736_1 back_NEO_675389780_737_1 back_NEO_675390018_738_1 back_NEO_675390293_739_1
Copy those 5 backup files to server2
[oracle@neoface oracle]$ scp /u01/backup/back_NEO* root@mynode2.com:/u01/backup/
Create an initialization file (pfile) from the current spfile. Then copy it to the server2.
[oracle@neoface oracle]$ sqlplus “/ as sysdba”
SQL> create pfile from spfile;
SQL> exit;
[oracle@neoface oracle]$ scp /opt/oracle/product/10.2.0/db_1/dbs/initneo.ora oracle@mynode2.com:/opt/oracle/product/10.2.0/db_1/dbs/initneo.ora/
AT SERVER2
Logon at server2 to do the following steps:
Switch to oracle user and create datafiles directories :
[root@mynode2 root] su – oracle
[oracle@mynode2 oracle]$ mkdir /opt/oracle/admin/neo -p
[oracle@mynode2 oracle]$ cd /opt/oracle/admin/neo
[oracle@mynode2 oracle]$ mkdir cdump udump bdump pfile
[oracle@mynode2 oracle]$ mkdir /opt/oracle/oradata/neo -p
Edit your pfile accordingly your new directory structure:
[oracle@mynode2 oracle]$ vi /opt/oracle/product/10.2.0/db_1/dbs/initneo.ora
Set environment variables and start working on RMAN:
[oracle@mynode2 oracle]$ export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
[oracle@mynode2 oracle]$ export ORACLE_SID=neo
[oracle@mynode2 oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@mynode2 oracle]$ rman target /
RMAN> startup nomount
RMAN> restore controlfile from ‘/u01/backup/cf_NEO_c-1689570411-20090106-00′;
RMAN> alter database mount ;
RMAN> exit
Now that the database is mounted, we’ll check the correct database SCN from the current log that we’ll use later to recover the database. Take note of your current SCN.
[oracle@mynode2 oracle]$ sqlplus “/ as sysdba”
SQL> select group#, first_change#, status, archived from v$log;
GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
1 336565140 ACTIVE YES
2 336415067 CURRENT NO
3 336523814 INACTIVE YES
SQL> exit;
[oracle@mynode2 oracle]$ rman target /
As we only copied to this server the backup we created at the beggining and we did not copy all the backups we had on server1 we must crosscheck the catalog against the OS files. Run the following commands at RMAN prompt :
RMAN> CROSSCHECK backup;
RMAN> CROSSCHECK copy;
RMAN> CROSSCHECK backup of database;
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;
Now let’s catalog the 4 backup pieces that we copy to this server2:
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675389594_736_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675389780_737_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675390018_738_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675390293_739_1′;
Next, as we changed the directory of our datafiles we must rename the redologs:
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/neo2/redo01.log’ to ‘/opt/oracle/oradata/neo/redo01.log’;
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/neo2/redo02.log’ to ‘/opt/oracle/oradata/neo/redo02.log’;
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/neo2/redo03.log’ to ‘/opt/oracle/oradata/neo/redo03.log’;
If you use BLOCK CHANGE TRACKING to allow fast incremental backups, and if you want to move the datafiles to different directory you must disable this feature and enabling it by specifying the new dir:
RMAN> ALTER DATABASE disable block change tracking;
RMAN> ALTER DATABASE enable block change tracking using file ‘/opt/oracle/oradata/neo/block_change_tracking.f’;
This will avoid errors like ORA-19751 and ORA-19750
Now let’s run the script that will restore our database, renaming the datafiles and recovering until the archivelog with SCN 336415067, the current one.
RMAN> run {
set newname for datafile 1 to “/opt/oracle/oradata/neo/system01.dbf”;
set newname for datafile 2 to “/opt/oracle/oradata/neo/undotbs01.dbf”;
set newname for datafile 3 to “/opt/oracle/oradata/neo/sysaux01.dbf”;
set newname for datafile 4 to “/opt/oracle/oradata/neo/data01.dbf”;
set newname for datafile 5 to “/opt/oracle/oradata/neo/index01.dbf”;
set newname for datafile 6 to “/opt/oracle/oradata/neo/users01.dbf”;
set newname for datafile 7 to “/opt/oracle/oradata/neo/streams.dbf”;
set newname for datafile 8 to “/opt/oracle/oradata/neo/data01brig.dbf”;
set newname for datafile 9 to “/opt/oracle/oradata/neo/index02.dbf”;
restore database;
switch datafile all;
recover database until scn 336415067;
}
RMAN> ALTER DATABASE open resetlogs;
I didn’t manage to avoid errors like ORA-01110 and ORA-01180 at RMAN without using the “until” clause in the “recover database” sentence instead, like most people use it, as the first instruction after the run command.
Later I’ll upload a video for you easily see it running flawlessly
Btw, HAPPY NEW YEAR!!!!
