Ubuntu under VMware.

How to install VMware tools on Ubuntu:

Click on VM\Install VMware Tools in the main menu of VMware.
The execute the following command in a terminal of you virtual Ubuntu:

cp /cdrom/*.gz /tmp/
cd /tmp
tar xvzf VM*.gz
cd vmware*
sudo ./vmware-install.pl
vmware-toolbox &

Another problem is that mouse wheel scrolling does not work on Ubuntu under VMware.

To fix it you have to edit the following file:

sudo gedit /etc/X11/xorg.conf

Where you have to have the following section:

Section "InputDevice"
Identifier "Configured Mouse"
Driver "vmmouse"
Option "CorePointer"
Option "Device" "/dev/input/mice"
Option "Protocol" "ImPS/2"
Option "Buttons" "5"
Option "ZAxisMapping" "4 5"
EndSection

Then press "Ctrl+Alt+Backspace" for restating the windows system.

Oracle Identity Management And Internet Directory.

1. The $ORACLE_HOME/(server_dbname)/sysman/recv/errors directory is filled up by err files.

$ORACLE_HOME/(server_dbname)/sysman/recv/errors directory is filled up by err files.

1. Stop the consoles:
cd $ORACLE_HOME/bin
./emctl stop iasconsole
./emctl stop dbconsole

2. Make a backup of the $ORACLE_HOME/sysman/jlib/emCORE.jar file
cd $ORACLE_HOME/sysman/jlib
cp emCORE.jar emCORE.jar.bkp

3 Replace this jar file by that one of 10.1.0.5.0 version: emCORE.jar

4. Make a copy of the $ORACLE_HOME/bin/emctl.pl file.
cd $ORACLE_HOME/bin
cp emctl.pl emctl.pl.bkp

5. Replace 10.1.0.3.0 to 10.1.0.5.0 in the $ORACLE_HOME/bin/emctl.pl file.
There are three places where this string has to be changed.

6. Start consoles.
cd $ORACLE_HOME/bin
./emctl start iasconsole
./emctl start dbconsole
./emctl status iasconsole
./emctl status dbconsole

How to rebuild a table which occupies the last extend in the data file, and then shrink this file.

We can use the following script for preparing a script for rebuilding the last table in the tablespace, its indices and analyzing all this stuff:

select comm from (
select 3,'ALTER TABLE '||owner||'.'||SEGMENT_NAME||' MOVE TABLESPACE fire_tables;' comm
from (
select owner, segment_name, segment_type, block_id
from dba_extents
where file_id =
( select ddf.file_id
from dba_data_files ddf
where ddf.file_name = '(file name)' )
order by block_id desc ) where rownum = 1
union
select 1,'exec dbms_stats.gather_table_stats(ownname=>'''||owner||''',tabname=>'''||segment_name||''',estimate_percent=>20,cascade=>true);' comm
from (
select owner, segment_name, segment_type, block_id
from dba_extents
where file_id =
( select ddf.file_id
from dba_data_files ddf
where ddf.file_name = '(file name)' )
order by block_id desc ) where rownum = 1
union
select 2,'ALTER INDEX '||idx.owner||'.'||idx.index_name||' REBUILD TABLESPACE fire_indexes online;' comm from
(select owner,SEGMENT_NAME
from (
select owner, segment_name, segment_type, block_id
from dba_extents
where file_id =
( select ddf.file_id
from dba_data_files ddf
where ddf.file_name = '(file name)' )
order by block_id desc )
where rownum = 1) tbs, dba_indexes idx
where idx.table_name=tbs.segment_name and idx.owner=tbs.owner ) order by 1 asc;

After the rebuilding the table you should figure out the block size:

select value from v$parameter where name = 'db_block_size'
/

Then you can execute the script which creates a script for maximum shrinking a required data file:

select 'alter database datafile ''' ||file_name|| ''' resize ' ||
ceil( (nvl(hwm,1)*(db block size))/1024/1024 )||'m;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents group by file_id ) b
where a.file_id = b.file_id(+) and
ceil(blocks*(db block size)/1024/1024)-
ceil((nvl(hwm,1)*(db block size))/1024/1024 ) > 0
/

Then we may increase a size of each data file to 10% if we want.

select 'alter database datafile ''' ||file_name|| ''' resize '||ceil((100+10)*ceil(bytes/1024/1024)/100)||'m;' from dba_data_files;

And then we may set up auto-extend to a size of file plus 2Gb for example:

select 'ALTER DATABASE DATAFILE '''||file_name||''' AUTOEXTEND ON NEXT 5M MAXSIZE '||to_char(bytes+2147483648)||';' from dba_data_files;

How to analyze all tables with theirs indices where statistics is equal to null.

For preparing a script for analyzing all tables with its indices where statistics is equal to null you can use this query:

select 'exec dbms_stats.gather_table_stats( ownname=>''' ||owner||''',tabname=>'''|| table_name||''', estimate_percent=>20,cascade=>true);' comm from dba_tables where last_analyzed is null and owner not in ('SYS','SYSTEM');

How to make a crontab backup.

If we want to make a crontab backup we can insert the following lines into crontab (crontab -e)

#------------------------------------------------------------------------------
# Write out crontab file daily for backup
00 18 * * * $HOME/scripts/crontab_backup.ksh > $HOME/logs/crontab_backup.log 2>&1
#------------------------------------------------------------------------------
# Cleanup files
#
12 07 * * * find $HOME/logs/* -name "*" -mtime +30 -exec rm {} \; > /dev/null 2>&1

Where the $HOME/scripts/crontab_backup.ksh script contains of the following code:

#!/bin/ksh
LOG=$HOME/logs/crontab.`date +%y%m%d_%H:%M`
crontab -l > $LOG
# EOF

How to switch a database into the archive log mode:

In order to switch a database to archivelog mode we can use the following procedure:

shutdown immediate;
startup mount exclusive
alter database archivelog;
archive log start;
startup force;

And to check what is the the archivelog mode status:

SELECT log_mode FROM gv$database;

MISCELLANEOUS

• How to add a new virtual disk into linux vmware machine.
• How to fix the "Could not open virtual machine" vmware error.
• How to make a crontab backup.
• Ubuntu under VMware.
  

ORACLE TECHNIQUES

• How to clean up the FRA from archive logs.
• How to create a flash recovery area.
• ORA-06512 on 9i SE
• How to reset an oracle parameter.
• "EXP-00008: ORACLE error 6550 encountered" error of Oracle10g.
• How to truncate all tables in schema which contain foreign key constraints.
• How to import a schema and ignore all data from tables.
• How to change the location of oraInventory.
• How to switch a database into the archive log mode.
• How to analyze all tables with theirs indices where statistics is equal to null.
• How to rebuild a table which occupies the last extend in the data file, and then shrink this file.

INSTALLATION

• Creating an oracle 9i database.
• Ora9i on CentOS4(VMware) - 2.
• Ora9i on CentOS4(VMware) - 1.

How to clean up the flash recovery area from archive logs.

Hi,

If the flash recovery area is filled up for example by archive logs you can check it using this query:

select * from V$FLASH_RECOVERY_AREA_USAGE;

To clean up FRA from archive logs you can physically delete archive logs from files system where FRA is located then execute RMAN connect to target and catalog if you use the last one and run the following script for cross checking and delete archive log files which we just physically deleted:

crosscheck archivelog all ;
delete expired archivelog all;

Thanks,
Sergey.

How to create a flash recovery area.

Hi,

To create a flash recovery area from scratch the following two commands need to be executed:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = (Size of FRA for example 10G) SCOPE=BOTH;

and

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = (Path to FRA for example '/u01/flash_recovery_area') SCOPE=BOTH;

That is it.


Thanks,
Sergey.

ORA-06512: on 9i SE

Hi,

When an Oracle 10G Standard Edition database has been installed, I have got the following error in the alert log file of this database:

ORA-00604: error occurred at recursive SQL level 1
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1
ORA-06512: at line 13

I have found that for fixing that error we need to shut down the Oracle OLAP functionality using the following commands.

ALTER TRIGGER SYS.OLAPISTARTUPTRIGGER DISABLE;
ALTER TRIGGER SYS.OLAPISHUTDOWNTRIGGER DISABLE;

Thanks,
Sergey.

How to reset an oracle parameter.

Hello,

Recently I mistakenly set up the log_archive_start=TRUE parameter on a 10g database when switched it into archive log mode. This parameter is obsolete in the 10g.

To erase this parameter from an spfile we can use the following command:
ALTER SYSTEM RESET log_archive_start SCOPE=SPFILE SID='*';

Thanks,
Sergey.

Creating an oracle 9i database.

Hi Everyone,

To create a new oracle database we need to complete the following steps:

1. As root create the oracle directory under the /u01 volume, which created in the last posting.
And change the owner to the oracle:oinstall account

[root@cent4 ~]# cd /u01/ [root@cent4 u01]# mkdir oracle [root@cent4 u01]# chown oracle:oinstall ./oracle
2. Change the current ID to oracle and create an oradata directory.
[root@cent4 ~]# su - oracle [oracle@cent4 ~]$ cd /u01/oracle [oracle@cent4 ora]$ mkdir oradata
3. In the parallel terminal window as root execute the xhost command:
[root@cent4 ~]# xhost + access control disabled, clients can connect from any host
4. And in the oracle window run the dbca command:
[oracle@cent4 oradata]$ cd $ORACLE_HOME/bin [oracle@cent4 bin]$ ./dbca
5.1. In the "Database Configuration Assistant: Welcome" just click ("Next").
5.2. In the "Database Configuration Assistant, Step 1 of 8: Operations"
choose "Create a database" and then click ("Next").
5.3. In the "Database Configuration Assistant, Step 2 of 8: Database Templates"
choose "General Purpose" and then click ("Next").
5.4. In the "Database Configuration Assistant, Step 3 of 7: Database Identification"(It is strange that there is 7 instead of 8) set up the following values for those fields:
Global Database Name: test.ssl.world
SID: test
(Obviously you can set up any name what you want in these fields.) then click (“Next”).
5.5. In the "Database Configuration Assistant, Step 4 of 7: Database Configuration Options" choose: "Dedicated Server Mode" then click ("Next").
5.6. In the "Database Configuration Assistant, Step 5 of 7: Initialization Parameters" stay all parameters as default then click (“Next”).
5.7. In the "Database Configuration Assistant, Step 6 of 7: Database Storage" replace all {ORACLE_BASE} variables to /u01/oracle for control files, Datafiles, Redo Log Groups then click ("Next").
5.8. In the "Database Configuration Assistant, Step 7 of 7: Creation Options" stay a default value "Create Database" and click ("Finish").
5.9. In the following window set up your favorite passwords for sys and system accounts. So, the database has been successfully created. Now you can startup or shutdown it using the following commands under the oracle linux account:
[oracle@cent4 ~]$ . oraenv
ORACLE_SID = [test1] ? test
[oracle@cent4 ~]$ sqlplus /nolog

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

In the next article I am planning to upgrade this database to the Oracle 10G version.

Thanks,
Sergey.

How to add a new virtual disk to a linux vmware machine.

1. Shutdown the virtual linux by: shutdown –h now
2. Click on "Edit virtual machine settings" in the "Commands" section of vmware for our virtual machine.
3. Click on "Add…" button of "Virtual Machine Settings" window.
4. Choose the "Hard Disk" and click "Next".
5. Choose the "Create a new virtual disk" option and click "Next".
6. In the "Virtual disk type" section choose SCSI and click "Next".
7. In the "Disk capacity" chose 20GB for the "Disk size" and mark "Split disk" into 2GB files. Then click "Next".
8. Click "Finish" in the next window.
9. Now we can see a new disk on the "Hardware" tab. Click "Ok".
10. Click on "Start this virtual machine" link.
11. Login and open a terminal window as root.
12. Execute: fdisk /dev/sdb
We run /dev/sdb because this is the second disk on our linux system (first one is /dev/sda).
In the fdisk utility complete the following commands:
12.1. Press "n" to add a new partition
12.2. Press "p" for a primary partition (1-4)
12.3. Enter "1" to set up the first partition.
12.4. Enter the default values for first and last cylinders.
12.5. Enter "w" for writing this information.
13. For formatting the new partition enter: mkfs -t ext3 /dev/sdb1
14. Create the /u01 directory for mounting the new disk: mkdir /u01
15. Mount this new disk to this directory: mount -t ext3 /dev/sdb1 /u01
16. Check by df –k that this disk is mounted.
[root@cent4 ~]# df -k /u01
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sdb1 20635700 77888 19509576 1% /u01
17. In the /etc/fstab file add the following entry:
/dev/sdb1 /u01 ext3 defaults 1 1
I have chosen these parameters because I have the same ones for my root partition:
/dev/VolGroup00/LogVol00 / ext3 defaults 1 1
So probably it makes sense.
That’s it. Now, if we reboot our virtual machine the /u01 volume will be mounted automatically.
In the next article we will use this volume for our new oracle database.

Thanks,
Sergey.

EXP-00008: ORACLE error 6550 encountered

Hi,

Recently I did a full export from an Oracle 10g database using a regular oracle account(not sys or system), and got the following errors:

...
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table DEF$_AQCALL
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 18:
PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1204
ORA-06512: at "SYS.DBMS_SQL", line 323
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 97
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 126
ORA-06512: at line 1
. . exporting table DEF$_AQERROR
...
. . exporting table DEF$_ERROR
EXP-00008: ORACLE error 6510 encountered
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 50
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 126
ORA-06512: at line 1
. . exporting table DEF$_LOB 0 rows exported
...

I have found that we need to grant some sys procedures to this user for getting rid of these errors.
So I logged in as sys and executed the following commands:
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO ;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO ;
And it really helped me to fix it.

Thanks,
Sergey.

How to fix the "Could not open virtual machine" vmware error.

Hello,

Today I got the following error when tried to open a vmware virtual machine:

Could not open virtual machine: C:\vm\cent4\Red Hat Enterprise Linux 4.vmx.
This virtual machine appears to be in use.

To fix this error I have found that it is enough to delete a lck file
from the "C:\vm\cent4\Red Hat Enterprise Linux 4.vmx.lck\" directory.


Thanks,
Sergey.

How to truncate all tables in a schema which contain foreign key constraints:

1. Connect as system to a database, however it is possible to perform these scripts from another account which has got enough privileges.
2. Prepare scripts for disabling and enabling foreign key constraints.
2.1. The first script includes the output of the following query:

SELECT 'ALTER TABLE'||' '||owner||'.'||table_name||' DISABLE CONSTRAINT '||constraint_name||' ;' FROM dba_constraints WHERE constraint_type = 'R' and owner='name of schema' and status = 'ENABLED';

Let's name the output of this query as disable_constraints.sql

2.2. The second script includes the output of a very similar query.

SELECT 'ALTER TABLE'||' '||owner||'.'||table_name||' ENABLE CONSTRAINT '||constraint_name||' ;' FROM dba_constraints WHERE constraint_type = 'R' and owner='name of schema' and status = 'ENABLED';

Let's name the output of this query as enable_constraints.sql

3. Prepare scripts for truncating all tables in the schema.
SELECT 'TRUNCATE TABLE '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='name of schema';

Name the output of this query as truncate_tables.sql

4. Run these scripts in the following order:
@disable_constraints.sql
@truncate_tables.sql
@enable_constraints.sql

That's it.

Thanks,
Sergey.

How to change the location of oraInventory.

If you want to change the location of oraInventory you need just put a new location in the /var/opt/oracle/oraInst.loc (on Solaris) in the inventory_loc variable and move the oraInventory directory from the old place to the new one.

Thanks,
Sergey.

How to import a schema from a dump file and ignore all data in tables.

If you need to import schema without data from a dump file you should follow the following routine:
1. Extract the DDL script of this schema from the dump file:
imp file=exp_file.dmp indexfile=ddl_script.sql userid=system/xxxxxxxx@destination_db fromuser=dmp_user touser=dest_user
Where:
exp_file.dmp – the oracle dump file.
ddl_script.sql – the DDL script which will include all DDL commands of this schema.
xxxxxxxx – system password.
destination_db – an oracle tnsnames alias of the destination database. We could omit it if we are doing import on the same computer where the database is with proper environment variables.
dmp_user – user name in the dump file.
dest_user – user name in the destination database.
2. After the ddl_script.sql file is ready you should remove the REM comments from it.
3. Connect as dest_user to the destination database and execute the ddl_script.sql script. Now we have the non-populated schema without PL/SQL code.
4. Import the dump file on the dest_user schema using the following command:
imp file= exp_file.dmp IGNORE=N userid=system/xxxxxxxx fromuser=dmp_user touser=dest_user
Using IGNORE=N flag we skip all inserts and keep having empty tables however PL/SQL code will be imported.

That’s it.

Thanks,
Sergey.