Oracle, Linux, AWS, Azure, GCP
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');
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…
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;
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.
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!!!!
September 26, 2011 1 Comment
Error Description: sqlplus: error while loading shared libraries: $ORACLE_HOME/lib/libclntsh.so.11.1: cannot restore segment prot after reloc: Permission denied
One of the way to solve this error is to disable SELinux(as some people advice on the forums). But it is related to the security risks.
So I decided to use Linux provided solution, which is safer:
–After I ran the following command:
# sqlplus / as sysdba
There appeard our error and some tip on the screen, indicating the solution of this error, I want to share it with you.
The brief summery is to run the following command:
$ chcon -t textrel_shlib_t '/u0/app/oracle/product/11.2.0/db_1/lib/libcntsh.so.11.1'
Note: My $ORACLE_HOME=/u0/app/oracle/product/11.2.0/db_1
The whole text in the message from Linux is the following , I think it is very interesting:
Summary SELinux is preventing sqlplus from loading /u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1 which requires text relocation. Detailed Description The sqlplus application attempted to load /u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1 which requires text relocation. This is a potential security problem. Most libraries do not need this permission. Libraries are sometimes coded incorrectly and request this permission. The SELinux Memory Protection Tests web page explains how to remove this requirement. You can configure SELinux temporarily to allow /u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1 to use relocation as a workaround, until the library is fixed. Please file a bug report against this package. Allowing Access If you trust /u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1 to run correctly, you can change the file context to textrel_shlib_t. "chcon -t textrel_shlib_t '/u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1'" You must also change the default file context files on the system in order to preserve them even on a full relabel. "semanage fcontext -a -t textrel_shlib_t '/u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1'" The following command will allow this access: chcon -t textrel_shlib_t '/u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1' ...
September 5, 2011 Leave a comment
Error : Failed to auto-start Oracle Net Listener using /ade/vikrkuma_new/oracle/bin/tnslsnr
Cause: This is due to a hard coded path in the dbstart script.
Solution:
In $ORACLE_HOME/bin/dbstart script replace
ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle
with
ORACLE_HOME_LISTNER=$ORACLE_HOME
dbstart script shold now start the listener as expected.August 8, 2011 Leave a comment
In my previous posts I discussed Oracle Advanced Replication…But this method is error-prone.
I do not recommend it to use, because it works fine only for DML operations on less than 100 transactions and there is other limitations also, if you wonder to better understand how can it be configured click here.
Because of that, I started to find another method of replication…and here it is.
So let’s start…
Database 1—–ORCL
Database 2—–TEST
First of all, you should activate archive logging for each of the databases, if you don’t remember how to do this click here.
Step 1: Create stream administration user in both databases.
———————————-ORCL———————————-
SQL> connect sys/pass@orcl as sysdba
SQL> create user streamadmin identified by streamadmin default tablespace users;
———————————-TEST———————————-
SQL> connect sys/pass@test as sysdba
SQL> create user streamadmin identified by streamadmin default tablespace users;
Step 2: Grant required privileges to the user streamadmin.
———————————-ORCL———————————-
SQL> grant dba,select_catalog_role to streamadmin;
SQL> exec dbms_streams_auth.grant_admin_privilege('streamadmin',true);
———————————-TEST———————————-
SQL> grant dba,select_catalog_role to streamadmin;
SQL> exec dbms_streams_auth.grant_admin_privilege('streamadmin',true);
Step 3: Check database parameters required for setting up stream replication.
In this section everyone writes to set GLOBAL_NAMES parameter to TRUE, but I am not using it.
SQL> show parameter job_queue_processes NAME TYPE VALUE -------------------- ------- ------ job_queue_processes integer 10 SQL> show parameter db_recovery_file_dest_size NAME TYPE VALUE -------------------------- ----------- ----- db_recovery_file_dest_size big integer 2G SQL> show parameter db_recovery_file_dest NAME TYPE VALUE --------------------- ------ ------------------------------------------------ db_recovery_file_dest string /u0/app/oracle/product/10.2.0/flash_recovery_area
Step 5: Enable supplemental logging on the COUNTRIES table on both of the databases.
———————————-ORCL———————————-
SQL> alter table hr.countries add supplemental log data (all) columns;
———————————-TEST———————————-
SQL> alter table hr.countries add supplemental log data (all) columns;
Step 6: Create Database Links between the stream administrator users in each of the databases.
———————————-ORCL———————————-
SQL> connect streamadmin/streamadmin@ORCL
SQL> create database link test connect to streamadmin identified by streamadmin using 'test';
———————————-TEST———————————-
SQL> connect streamadmin/streamadmin@TEST
SQL> create database link orcl connect to streamadmin identified by streamadmin using 'orcl';
Step 7: Create Stream Queues under the streamadmin user to apply and capture the database changes that should be replicated.
———————————-ORCL———————————-
SQL> show user User is "streamadmin"
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'apply_q', queue_name => 'apply_q', queue_user => 'streamadmin');
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'capture_q',queue_name => 'capture_q',queue_user => 'streamadmin');
———————————-TEST———————————-
SQL> show user User is "streamadmin"
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'apply_q', queue_name => 'apply_q', queue_user => 'streamadmin');
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'capture_q',queue_name => 'capture_q',queue_user => 'streamadmin');
step 8: Setup data capture on both of the databases:
———————————-ORCL———————————-
SQL> BEGIN dbms_streams_adm.add_table_rules (table_name => 'HR.COUNTRIES' ,streams_type => 'CAPTURE' ,streams_name => 'CAPTURE_STREAM' ,queue_name => 'CAPTURE_Q' ,include_dml => TRUE ,include_ddl => TRUE ,inclusion_rule => TRUE ); END;
/
———————————-TEST———————————-
BEGIN dbms_streams_adm.add_table_rules (table_name => 'HR.COUNTRIES' ,streams_type => 'CAPTURE' ,streams_name => 'CAPTURE_STREAM' ,queue_name => 'CAPTURE_Q' ,include_dml => TRUE ,include_ddl => TRUE ,inclusion_rule => TRUE ); END; /
Step 9: Setup data apply on both of the databases:
———————————-ORCL———————————-
BEGIN DBMS_STREAMS_ADM.add_table_rules ( TABLE_NAME => 'HR.COUNTRIES', STREAMS_TYPE => 'APPLY', STREAMS_NAME => 'APPLY_STREAM', QUEUE_NAME => 'APPLY_Q', INCLUDE_DML => TRUE, INCLUDE_DDL => TRUE, SOURCE_DATABASE => 'TEST' ); END; /
———————————-TEST———————————-
BEGIN DBMS_STREAMS_ADM.add_table_rules ( TABLE_NAME => 'HR.COUNTRIES', STREAMS_TYPE => 'APPLY', STREAMS_NAME => 'APPLY_STREAM', QUEUE_NAME => 'APPLY_Q', INCLUDE_DML => TRUE, INCLUDE_DDL => TRUE, SOURCE_DATABASE => 'ORCL' ); END; /
Step 10: Setup propagation process on both of the databases:
It is basically setting up relationship between the capture processes on one database and apply process on the other database.
———————————-ORCL———————————-
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES( TABLE_NAME => 'HR.COUNTRIES', STREAMS_NAME => 'ORCL_TO_TEST', SOURCE_QUEUE_NAME =>'CAPTURE_Q', DESTINATION_QUEUE_NAME => 'APPLY_Q@TEST', INCLUDE_DML => TRUE, INCLUDE_DDL => TRUE, SOURCE_DATABASE => 'ORCL'); END; /
———————————-TEST———————————-
BEGIN DBMS_STREAMS_ADM.add_table_propagation_rules( TABLE_NAME => 'HR.COUNTRIES', STREAMS_NAME => 'TEST_TO_ORCL', SOURCE_QUEUE_NAME =>'CAPTURE_Q', DESTINATION_QUEUE_NAME => 'APPLY_Q@ORCL', INCLUDE_DML => TRUE, INCLUDE_DDL => TRUE, SOURCE_DATABASE => 'TEST' ); END; /
Step 11: Setup schema instantiation SCN on both of the databases.
———————————-ORCL———————————-
DECLARE iscn NUMBER; BEGIN iscn := dbms_flashback.get_system_change_number(); dbms_apply_adm.set_table_instantiation_scn@TEST( source_object_name => 'HR.COUNTRIES' ,source_database_name => 'ORCL' ,instantiation_scn => ISCN); END; /
———————————-TEST———————————-
DECLARE iscn NUMBER; BEGIN iscn := dbms_flashback.get_system_change_number(); dbms_apply_adm.set_table_instantiation_scn@ORCL( source_object_name => 'HR.COUNTRIES' ,source_database_name => 'TEST' ,instantiation_scn => ISCN); END; /
Step 12: Start capture and apply process:
DISABLE_ON_ERROR parameter values:
‘N’ streaming process will be continued even when it encounters errors.
The default value is ‘Y’ which stops process automatically on the first error encountered.
———————————-ORCL———————————-
BEGIN dbms_apply_adm.set_parameter( apply_name => 'APPLY_STREAM' ,parameter => 'DISABLE_ON_ERROR' ,value => 'N'); dbms_apply_adm.start_apply(apply_name=> 'APPLY_STREAM'); dbms_capture_adm.start_capture(capture_name=> 'CAPTURE_STREAM'); END; /
———————————-TEST———————————-
BEGIN dbms_apply_adm.set_parameter( apply_name => 'APPLY_STREAM' ,parameter => 'DISABLE_ON_ERROR' ,value => 'N'); dbms_apply_adm.start_apply(apply_name=> 'APPLY_STREAM'); dbms_capture_adm.start_capture(capture_name=> 'CAPTURE_STREAM'); END; /
If any error happens the following query shows it:
select * from dba_apply_error;
Let’s test it…
———————————-ORCL———————————-
SQL> connect hr/hr@orcl
SQL> select * from countries;
COUNTRY_ID COUNTRY_NAME REGION_ID
---------- --------------- ----------
AR Argentina 2 SQL> insert into contries values('AA','Aaaa',1);
SQL> commit; SQL> select * from countries; COUNTRY_ID COUNTRY_NAME REGION_ID ---------- ------------- ---------- AA Aaaa 1 AR Argentina 2
———————————-TEST———————————-
SQL> connect hr/hr@test SQL> select * from countries;
COUNTRY_ID COUNTRY_NAME REGION_ID
---------- ------------- ----------
AA Aaaa 1
AR Argentina 2
August 1, 2011 Leave a comment
1. Logon as a root user:
> su Password:
2. Create Oracle software owner’s home directory.
cd /u0 mkdir –p app/oracle
3. Create necessary groups and Oracle user.
groupadd oinstall groupadd dba useradd –s /bin/ksh –d /u0/app/oracle –g oinstall –G dba oracle
4 Change the owner of the app directory.
chown –R oracle:oinstall app
5 Reset oracle’s password.
passwd oracle
6 Add the following parameters to /etc/system
set shmsys:shminfo_shmmax = 4294967295 set shmsys:shminfo_shmmin = 1 set shmsys:shminfo_shmmni = 100 set shmsys:shminfo_shmseg = 10 set semsys:seminfo_semmni = 100 set semsys:seminfo_semmns = 1024 set semsys:seminfo_semmsl = 256 set md:mirrored_root_flag=1 set noexec_user_stack=1
7. reboot server
8. Download the installation file from here. Extract and run ./runInstaller as a oracle user.
9. create .profile file in Oracle’s home directory ‘/u0/app/oracle ’ and write the following lines:
umask 022 stty istrip ORACLE_BASE=/u0/app/oracle ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1 PATH=$ORACLE_HOME/bin:/usr/ccs/bin:/usr/bin:/etc:/usr/openwin/bin:/usr/local/bin:$ORACLE_BASE export ORACLE_BASE ORACLE_HOME PATH
July 25, 2011 6 Comments
Installing SQL Developer on Centos 5 is a little bit different from Installing SQL Developer on Open Suse.
1. Download SQL Developer installation from Oracle SQL Developer RPM for Linux.
2. Install that rpm package:
> su Password: # rpm -Uhv sqldeveloper-3.0.04.34-1.noarch.rpm
3. Download JDK from here. You need Self Extracting Installer…not RPM Installer
Go to the directory, where you have downloaded JDK(Note I’ve downloaded file for 64bit…Indicate the correct filename):
> su Password:
Make it executable(indicate correct filename,which you’ve downloaded):
#chmod a+x jdk-6u26-linux-x64.bin
Run that file:
# ./jdk-6u26-linux-x64.bin
There will appear folder jdk1.6.0_26. Copy that folder to the desired location, you will need it.
4. Open the .bash_profile(located in $HOME directory) and add/edit the following entries:
JAVA_HOME=/usr/java/jdk1.6.0_26/ PATH=$PATH:/opt/sqldeveloper/ export PATH export JAVA_HOME
5. Run SQL Developer:
# sqldeveloper
DBA Knowledge Base 