ORA-20446: The owner of the job is not registered

October 31, 2011 1 Comment

If you have the following error in EM:

ORA-20446: The owner of the job is not registered ORA-06512: at "SYSMAN.MGMT_JOBS", line 168 ORA-06512: at "SYSMAN.MGMT_JOBS", line 86 ORA-06512: at line 1

It is said to be a bug in  Oracle 11g .

To solve this, you should login as a sys user and run the following command:

execute MGMT_USER.MAKE_EM_USER('USERNAME');

Filed under DB Tagged with ,

ORA-28221: REPLACE not specified

October 24, 2011 Leave a comment

Very interesting situation. This error occurs, when user’s profile has limit password_verify_function, at the same time user doesn’t have ALTER USER privilege and trying to run the following statement:

ALTER USER my_user IDENTIFIED BY my_password;

* If user doesn’t have that limit in profile, he/she can change its password by this way without any problem.

* If she/he has that limit, it should have ALTER USER privilege to change password by this way without any problem.

 

There is also another way, if you are not able to change password by the previous command, but you must know old password:

ALTER USER my_user IDENTIFIED BY my_password REPLACE my_old_password;

Just one more trick in Oracle, that you should know…

Filed under DB Tagged with ,

Recreate Undo Tablespace

October 17, 2011 Leave a comment

–Identify undo tablespace name

SQL> SELECT NAME,VALUE
     FROM v$parameter WHERE name IN ('undo_management','undo_tablespace');

NAME             VALUE
--------------- ----------
undo_management  AUTO
undo_tablespace  UNDOTBS1

–Define how undo tablespace was created

SQL> SELECT dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1')
 FROM dual;


CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' SIZE 26214400
AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
 ALTER DATABASE DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' RESIZE 28377088

–Create another, substitute undo tablespace

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
      'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS02.DBF' SIZE 26214400
     AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M;

–Change parameter undo_tablespace value to newly created tablespace name. (I have started my DB by spfile)

SQL> ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2' SCOPE=spfile;

–Shutdown database

SQL> shutdown immediate;

–Start the database

SQL> startup;

–Take old undo tbs. into offline mode

SQL> ALTER TABLESPACE UNDOTBS1 OFFLINE;

–Drop undo tbs. including contents and datafiles

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Filed under DB Tagged with ,

Uninstall Oracle Manually

October 10, 2011 40 Comments

1.Uninstall all Oracle components using the Oracle Universal Installer (OUI).

2.Run regedit.exe and delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE. This contains registry entires for all Oracle products. Delete any references to Oracle services left behind in the following part of the registry:  HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Ora*   It should be obvious which ones relate to Oracle.

3.Reboot your machine.

4.Delete the “C:\Oracle” directory, or whatever directory is your ORACLE_BASE.

5.Delete the “C:\Program Files\Oracle” directory.

6.Empty the contents of your “c:\temp” directory.

7.Empty your recycle bin.

Filed under DB Tagged with

Install VirtualBox Guest Additions on Centos 5.5

October 5, 2011 Leave a comment

1. Login as a root user:

su –

2. Go to the Devices->Install Guest Additions…

Install VirtualBox Guest Additions on Centos 5.5

3. Mount Guest Additions device:

mkdir /media/VGuestAdditions
mount -r /dev/cdrom /media/VGuestAdditions

4. Install the following packages:

yum install gcc
yum install kernel-devel
yum install kernel-headers

5. Install Guest Additions:

cd /media/VGuestAdditions
./VBoxLinuxAdditions.run

6. Restart the system:

reboot

Filed under Linux Tagged with

Prevent to change SYS/SYSTEM password

October 3, 2011 Leave a comment

Problem: How to Prevent a User Granted the ALTER USER Privilege From Changing SYS/SYSTEM password ?

You should write system event and here it is:

Assuming that you are preventing HR user from altering SYS/SYSTEM user.

–Connect as a sys user and run the following:

CREATE or REPLACE TRIGGER prohibit_alter_SYSTEM_SYS_pass
         BEFORE ALTER on HR.schema
         BEGIN
              IF SYSEVENT='ALTER' and DICTIONARY_OBJ_TYPE = 'USER' and
                 (DICTIONARY_OBJ_NAME = 'SYSTEM' or DICTIONARY_OBJ_NAME = 'SYS')
              THEN
                 RAISE_APPLICATION_ERROR(-20001,
                            'You are not allowed to alter SYSTEM/SYS user.');
              END IF;
         END;

I highlighted BEFORE keyword because, on metalink there is wrongly written AFTER TRIGGER. Because, if you write AFTER trigger this actually means that trigger will arise after action will be performed and this is wrong.

More specifically, if we write AFTER trigger, as metalink advices, HR user will actually change SYS/SYSTEM user password and then see the error message!!!!

Filed under DB Tagged with