Auto Start/Shutdown Oracle 11g on CEntOS

Modify /etc/oratab and Replace N with Y for enabling Oracle Startup at Boot.

TESTDB:/u01/app/oracle/product/11.2.0/db_1:N
to 
TESTDB:/u01/app/oracle/product/11.2.0/db_1:Y

Create /etc/init.d/oracle file with following codes vi /etc/init.d/oracle. Must change ORACLE_HOME, ORACLE_SID

#!/bin/sh
#
# /etc/rc.d/init.d/oracle
# Description: Starts and stops the Oracle database, listeners and Enterprise Manager
# See how we were called.

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=TESTDB

case "$1" in
start)
echo "Starting Oracle"
echo "—————————————————-" >> /var/log/oracle
date +"! %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracle
echo "—————————————————-" >> /var/log/oracle
echo -n "Starting Oracle Databases: "
su - oracle -c ${ORACLE_HOME}/bin/dbstart >> /var/log/oracle
echo "Done."
echo -n "Starting Oracle Listeners: "
su - oracle -c "${ORACLE_HOME}/bin/lsnrctl start" >> /var/log/oracle
echo "Done."
echo -n "Starting Oracle Enterprise Manager: "
su - oracle -c "${ORACLE_HOME}/bin/emctl start dbconsole" >> /var/log/oracle
echo "Done."
echo ""
echo "—————————————————-" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "—————————————————-" >> /var/log/oracle
touch /var/lock/subsys/oracle
;;
stop)
echo "Shutting Down Oracle"
echo "—————————————————-" >> /var/log/oracle
date +"! %T %a %D : Shutting Down Oracle Databases as part of system down." >> /var/log/oracle
echo "—————————————————-" >> /var/log/oracle
echo -n "Shutting Down Oracle Enterprise Manager: "
su - oracle -c "${ORACLE_HOME}/bin/emctl stop dbconsole" >> /var/log/oracle
echo "Done."
echo -n "Shutting Down Oracle Listeners: "
su - oracle -c "${ORACLE_HOME}/bin/lsnrctl stop" >> /var/log/oracle
echo "Done."
rm -f /var/lock/subsys/oracle
echo -n "Shutting Down Oracle Databases: "
su - oracle -c ${ORACLE_HOME}/bin/dbshut >> /var/log/oracle
echo "Done."
echo ""
echo "—————————————————-" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "—————————————————-" >> /var/log/oracle
;;
restart)
echo "Restarting Oracle"
echo "—————————————————-" >> /var/log/oracle
date +"! %T %a %D : Restarting Oracle Databases as part of system up." >> /var/log/oracle
echo "—————————————————-" >> /var/log/oracle
echo -n "Restarting Oracle Databases: "
su - oracle -c ${ORACLE_HOME}/bin/dbshut >> /var/log/oracle
su - oracle -c ${ORACLE_HOME}/bin/dbstart >> /var/log/oracle
echo "Done."
echo -n "Restarting Oracle Listeners: "
su - oracle -c "${ORACLE_HOME}/bin/lsnrctl stop" >> /var/log/oracle
su - oracle -c "${ORACLE_HOME}/bin/lsnrctl start" >> /var/log/oracle
echo "Done."
echo -n "Restarting Oracle Enterprise Manager: "
su - oracle -c "${ORACLE_HOME}/bin/emctl stop dbconsole" >> /var/log/oracle
su - oracle -c "${ORACLE_HOME}/bin/emctl start dbconsole" >> /var/log/oracle
echo "Done."
echo ""
echo "—————————————————-" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "—————————————————-" >> /var/log/oracle
touch /var/lock/subsys/oracle
;;
*)
echo "Usage: oracle {start|stop|restart}"
exit 1
esac

Make the script file executable. Execute following command to change permission.

chmod 755 /etc/init.d/oracle

The following commands will ensure that the databases will come up in runlevels 2,3,4 and 5

ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc2.d/S99oracle
ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc3.d/S99oracle
ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc4.d/S99oracle
ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc5.d/S99oracle

To stop the databases on reboot or restart we need the following links

ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc0.d/K01oracle
ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc6.d/K01oracle

Read More

Install svn on CEntOS 6

svn is a software versioning and revision control system distributed as free software under the Apache License. Software developers useSubversion to maintain current and historical versions of files such as source code, web pages, and documentation.

go to url http://opensource.wandisco.com/centos/6/svn-1.8/RPMS/<arch> in my case I am using x86_64 architecture so using following URL to download required packages.
http://opensource.wandisco.com/centos/6/svn-1.8/RPMS/x86_64/

Download following packages:

1. mod_dav_svn-1.8.14-1.x86_64.rpm
2. serf-1.3.7-1.x86_64.rpm
3. subversion-1.8.14-1.x86_64.rpm

Optionally following packages can be downloaded:

1. serf-debuginfo-1.3.7-1.x86_64.rpm
2. serf-devel-1.3.7-1.x86_64.rpm
3. subversion-devel-1.8.14-1.x86_64.rpm
4. subversion-gnome-1.8.14-1.x86_64.rpm
5. subversion-javahl-1.8.14-1.x86_64.rpm
6. subversion-perl-1.8.14-1.x86_64.rpm
7. subversion-python-1.8.14-1.x86_64.rpm
8. subversion-ruby-1.8.14-1.x86_64.rpm
9. subversion-tools-1.8.14-1.x86_64.rpm

Install mod_dav_svn, serf and subversion

rpm -ivh mod_dav_svn-1.8.14-1.x86_64.rpm serf-1.3.7-1.x86_64.rpm subversion-1.8.14-1.x86_64.rpm 

Modify subversion.conf file on conf.d of httpd.

vi /etc/httpd/conf.d/subversion.conf

Modify following settings 

SVNPath /opt/repo/ to SVNParentPath /opt/repo/

AuthName "SVN Repo" to AuthName "Authorization Realm"

AuthUserFile /opt/WANdisco/apache22/conf/svn.passwd AuthUserFile /etc/httpd/conf/.htpasswd

Add following settings

AuthzSVNAccessFile /etc/svnusers

Create repo

mkdir -p /opt/repo/

cd /opt/repo/

svnadmin create rapti_rp

svnadmin create bheri_rp

svnadmin create karnali_rp

chown -R apache:apache /opt/repo

Create user and group logins

htpasswd -c -m /etc/httpd/conf/.htpasswd admin_rapti

htpasswd -m /etc/httpd/conf/.htpasswd admin_bheri

htpasswd -m /etc/httpd/conf/.htpasswd admin_karnali

htpasswd -m /etc/httpd/conf/.htpasswd user_rapti

htpasswd -m /etc/httpd/conf/.htpasswd user_bheri

htpasswd -m /etc/httpd/conf/.htpasswd user_karnali

Create groups

cat > /etc/svnusers

[groups]

rapti_admin = admin_rapti

bheri_admin = admin_bheri

karnali_admin = admin_karnali

rapti_user = user_rapti

bheri_user = user_bheri

karnali_user =  user_karnali

[rapti_rp:/]

@rapti_admin = rw

@rapti_user = r

[bheri_rp:/]

@bheri_admin = rw

@bheri_user = r

[karnali_rp:/]

@karnali_admin = rw

@karnali_user = r

Restart httpd service

/etc/init.d/httpd start

We can browse SVN with the url below:

http://<IPADDRESS>/svn/<repo_name>

http://192.168.1.251/svn/rapti_rp

Now we can create trunk, tags and branches with command below

svn mkdir file:///opt/repo/bheri_rp/tags -m "adding tag directory"
svn mkdir file:///opt/repo/bheri_rp/branches -m "adding branch directory"
svn mkdir file:///opt/repo/bheri_rp/trunk -m "adding trunk directory"

You can find the directories while accessing the svn url

http://192.168.1.251/svn/bheri_rp/

Importing existing project into repo.

svn import /home/dilli/proj_bheri http://192.168.1.251/svn/bheri_rp/ -m "Initial import"

Checkout svn to current directory

svn co http://192.168.1.251/svn/bheri_rp/ .

Create hook file so that whenever commit occurs some svn command get executed.

cat > post-commit
#!/bin/sh

cd /var/www/web/bheri && /usr/bin/svn update --username username --password password --no-auth-cache >> /tmp/svn.out

Change ownership and mode of the hook file.

chmod 755 post-commit
chown apache:apache post-commit

Read More

Alter Database link

We can use ALTER DATABASE LINK to modify database link whenever we need to change password. In case of changing username and host we need to re-create database link.


Create and verify public database link with command below.
CREATE PUBLIC DATABASE LINK dblink_test
CONNECT TO dilli IDENTIFIED BY oracle
USING 'TESTDB';

SELECT property_value
FROM database_properties@dblink_test
WHERE property_name='GLOBAL_DB_NAME';

In case of password change for the user dilli we will encounter the error below.

FROM database_properties@dblink_test

                         *

ERROR at line 2:

ORA-01017: invalid username/password; logon denied

ORA-02063: preceding line from DBLINK_TEST

We can use alter command to change the password used on dblink. Execute following command as user sys.

ALTER PUBLIC DATABASE LINK dblink_test
CONNECT TO dilli IDENTIFIED BY oracle_1;

If you need to modify username or hostname then you need to re-create the dblink. Before re-creation generate DDL of the database link using DBMS_METADATA package. We will use get_ddl function to generate DDL of database link. Return value will be of datatype CLOB so set long to 1000 characters before execution of the command otherwise incomplete DDL will be generated. Execute following command as user sys.

set long 1000
SELECT dbms_metadata.get_ddl('DB_LINK','DBLINK_TEST','PUBLIC')
FROM dual;

Now we can drop the database link and create with the SQL noted right now. Make sure there is no new line in value and the value is intact in single line as below.

To modify username use command below. In our case we have switched the username RAJIV with exactly same password as user DILLI has.

DROP PUBLIC DATABASE LINK dblink_test;

CREATE PUBLIC DATABASE LINK "DBLINK_TEST"
CONNECT TO "RAJIV" IDENTIFIED BY VALUES '0677433333EA295E225C0B92B30170664D756DA1C83756F6720A0E40DF81A0B0329BC3CF628AEE972A9E262984B1E1F26E09505A801B51EBF7372A8E79990112935DC1DECB60940B2469089A2237438C87FF97092153396834506130DA8AA8ECEEF4B61A9320688675D83B8C93860821FCFB678FA6C49827C40215D683CD6E0B'
USING 'TESTDB';

To modify host, execute command below as user sys.

DROP PUBLIC DATABASE LINK dblink_test;
CREATE PUBLIC DATABASE LINK "DBLINK_TEST"
CONNECT TO "DILLI" IDENTIFIED BY VALUES '0677433333EA295E225C0B92B30170664D756DA1C83756F6720A0E40DF81A0B0329BC3CF628AEE972A9E262984B1E1F26E09505A801B51EBF7372A8E79990112935DC1DECB60940B2469089A2237438C87FF97092153396834506130DA8AA8ECEEF4B61A9320688675D83B8C93860821FCFB678FA6C49827C40215D683CD6E0B'
USING 'PRODDB';
















Read More

MySQL slave relay log corrupted

MySQL replication stopped with slave relay log corruption. Error message will be as below.

Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

Output of SHOW SLAVE STATUS\G is as below.

Following information is required to fix this issue. Carefully note the information. Any mistake may lead to unrecoverable state of replication.

Master_Log_File: mysql-bin.000021
Read_Master_Log_Pos: 347919174
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 16247045
Relay_Master_Log_File: mysql-bin.000021
Exec_Master_Log_Pos: 324385114

We need to verify binlog file status on master server. Execute following command to verify the binlog status. If there is any error message returned it means binlog is corrupted on master server. No error message returned means binlog file's status is fine. 

In our case there is no binlog corruption on master server. If there are any error then re-setup mysql replication slave.

mysqlbinlog mysql-bin.000021 > /tmp/mysql-bin.000021

Using same mysqlbinlog command we can verify relay-binlog file status on slave server. Execute command below to verify the status. In our case there is log corruption, so it can be easily fixed with reset slave and other commands below.

mysqlbinlog mysql-relay-bin.000004 > /tmp/mysql-relay-bin.000004

Use the command below to fix the issue. Use value of Exec_Master_Log_Pos, noted above for MASTER_LOG_POS and value of Relay_Master_Log_File for MASTER_LOG_FILE.

stop slave;
reset slave all;
show slave status\G
CHANGE MASTER TO MASTER_HOST='192.168.1.1',MASTER_USER='mysql_slave', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000021', MASTER_LOG_POS=324385114;
start slave;

Additionally if we need to configure delayed master then execute command below for delayed replication.

stop slave;
change master to master_delay=86400;
start slave;

Read More

Oracle ADRCI(Automatic Diagnostic Repository Command Interpreter)

 The ADR(Automatic Diagnostic Repository) is file based repository for database diagnostic data like trace files, alert log entries, incident and problem and more.
 ADRCI(Automatic Diagnostic Repository Command Interpreter) is a special command line utility to handle oracle errors and trace files. It is tool to manage diagnostic data collected. ADRCI was introduced from Oracle 11gR1. ADRCI can be used in interactive mode or within scripts. ADR data is secured at operating system level so does not requires any login.

 ADRCI has following features:

 1. View diagnostic data within the Automatic Diagnostic Repository (ADR)
 2. View Health Monitor reports.
 3. Package incident and problem information into a zip file for transmission to Oracle Support.

 Diagnostic data contains 

 1. Incident and problem descriptions
 2. Trace files
 3. Dumps
 4. Health monitor reports
 5. Alert log entries

ADR Home

An ADR Home is the root directory for all the diagnostic data.

ADR Base

ADR base is the directory that is set by oracle parameter DIAGNOSTIC_DEST.

Homepath

All ADRCI commands operate on diagnostic data in the "current" ADR homes.

Let create one incident.

Connect as user dilli, create table and find its object_id.

conn dilli

create table dlee (n number);

select object_id from user_objects where object_name='DLEE';

Now connect as user sys and update the number of column forcefully. Note: Never perform any DML on data dictionary.

connect / as sysdba

update tab$ set cols=2 where obj#=75782;

commit;

alter system flush shared_pool;

Now connect as user dilli and select the table. An error will be encountered and error ORA-03113: end-of-file on communication channel will be appeared.

conn dilli

select * from dlee;

Check alert log for the error.

adrci

show home

set home diag/rdbms/testdb/TESTDB

show alert -tail -f

CTRL+C to exit from the tail session. 

Now list the problems

show problem

List the incidents

show incident

Problem and Incident

An incident is occurrence of a problem. The same problem may have multiple incidents. 

Multiple occurrence of same problem creates multiple incident. If we execute any command 

against the corrupt table it will create multiple incident but the problem will remain same.

Get detail of particular Incident

show incident -mode detail -p "incident_id=10961"

Creation of Packages & ZIP files to send to Oracle Support

Now gather all the information using ips(Incident Packaging Service). First command below 

creates Logical Package to generate zip files execute second command.

ips create package problem 1 correlate all

ips generate package 1 in "/home/oracle" 

Now /home/oracle/ORA7445qc_20151119112533_COM_1.zip is ready to ship to oracle support.

List all the trace files and purge all trace files that are older than 2 days(2880 min)

show tracefile

purge -age 2880 -type trace

Read More

Oracle online table redefinition

Online table redefinition is a mechanism to modify table structures without affecting its availability. It requires exact same amount of free space and more space is required if we are adding any column to the table. We will use DBMS_REDEFINITION package to redefine the table.

Features:

• Add, remove, or rename columns on a table
• Converting a non-partitioned table to a partitioned table and vice versa
• Switching a heap table to an index organised and vice versa
• Modifying storage parameters
• Adding or removing parallel support
• Reorganize (defragmenting) a table
• Transform data in a table

Restrictions:

• It is not possible to redefine Materialised Views (MViews) and tables with MViews or MView Logs defined on them.
• Redefining Temporary and Clustered Tables are not possible.
• Redefining tables with BFILE, LONG or LONG RAW columns are not possible.
• Redefining tables belonging to SYS or SYSTEM are not possible.
• Redefining Object tables are not possible.
• Redefinition cannot be done in NOLOGGING mode (watch out for heavy archiving)
• Redefinition cannot be used to add or remove rows from a table

For Testing purpose we have created table TAB_CUSTOMER in DILLI schema.

Grant execute privileges on DBMS_REDEFINITION to user.

grant execute on DBMS_REDEFINITION to dilli;

grant ALTER ANY TABLE, CREATE ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE, CREATE ANY INDEX, CREATE ANY SEQUENCE, CREATE ANY TRIGGER to dilli;

Test if the table can be redefined, Specify source table here

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('DILLI', 'TAB_CUSTOMER',DBMS_REDEFINITION.CONS_USE_ROWID);

we can use following options:

DBMS_REDEFINITION.CONS_USE_ROWID 

Redefine specified table online using rowid

DBMS_REDEFINITION.CONS_USE_PK.

Redefine specified table online using Primary Key

Verify table count of table TAB_CUSTOMER and its segment size.

select count(*) from TAB_CUSTOMER;

select segment_name, bytes/power(1024,3) size_in_GB

from user_segments where segment_name='TAB_CUSTOMER';

Create interim table with new structure if required. Oracle truncates data from this table in next step so do not copy data.

create table TAB_CUSTOMER1

as select * from TAB_CUSTOMER

where 1=2;

Start the redefinition. Oracle will copy (and transform) the data from the production table to the interim table.

EXEC DBMS_REDEFINITION.start_redef_table('DILLI', 'TAB_CUSTOMER', 'TAB_CUSTOMER1',options_flag=> DBMS_REDEFINITION.CONS_USE_ROWID);

Sync intermediate changes to interim table (optional)

 EXEC dbms_redefinition.sync_interim_table('DILLI','TAB_CUSTOMER', 'TAB_CUSTOMER1');

Copy dependencies

SET SERVEROUTPUT ON

DECLARE

  l_num_errors PLS_INTEGER;

BEGIN

  DBMS_REDEFINITION.copy_table_dependents(

    uname               => 'DILLI',

    orig_table          => 'TAB_CUSTOMER',

    int_table           => 'TAB_CUSTOMER1',

    copy_indexes        => 1,             -- Default

    copy_triggers       => TRUE,          -- Default

    copy_constraints    => TRUE,          -- Default

    copy_privileges     => TRUE,          -- Default

    ignore_errors       => FALSE,         -- Default

    num_errors          => l_num_errors,

    copy_statistics     => FALSE,         -- Default

    copy_mvlog          => FALSE);        -- Default

    

  DBMS_OUTPUT.put_line('num_errors=' || l_num_errors); 

END;

/

Complete the redefinition process.

exec dbms_redefinition.finish_redef_table('DILLI', 'TAB_CUSTOMER', 'TAB_CUSTOMER1');

select count(*) from TAB_CUSTOMER;

select count(*) from TAB_CUSTOMER1;

select segment_name, bytes/power(1024,3) size_in_GB

from user_segments where segment_name like 'TAB_CUSTOMER%';

Drop original table.

drop table TAB_CUSTOMER1;

Verify the objects and its status on the schema. Re-compile the schema objects if they are invalid.
select object_name, status from user_objects;