TTS transfer on DataGuard

Oracle TTS(Transportable tablespace) allows us to copy datafiles between databases. TTS was introducted in Oracle 8i.

Lets create a tablespace, schema, create table and insert some data on the table. 

-- Create tablespace to test transportable tablespace

Create tablespace tts_test
datafile '/u01/app/oracle/oradata/orcl/tts_test01.dbf'
size 2m
autoextend on 
next 2m
maxsize unlimited;

-- Create user schema 

Create user tts_user
identified by oracle
default tablespace tts_test;

-- Grant required privileges

Grant connect, resource to tts_user;

-- Connect as user tts_user and create table

Conn tts_user/oracle
Create table tbl_test(
sn number,
name varchar2(200)
);

-- Insert huge data using PL/SQL below

declare
begin
for i in 1..50000000
loop
insert into tbl_test values(i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');                   
commit;
end loop;
end;
/

Verify the number of rows on the table.

Select count(*) from tbl_test;

Connect as user sys, create directory and grant read, write privileges on directory to public.

-- Connect as user sys

Conn / as sysdba

-- Create directory

Create directory dump_dir
as '/home/oracle';

-- Grant read, write privileges on directory to public.

Grant read,write on directory dump_dir to public;

Alter tablespace to read only state.

ALTER TABLESPACE tts_test READ ONLY;

Export tablespace with transport_tablespaces option. 

expdp dumpfile=tts_test_$(date +%Y_%m_%d).dmp logfile=exp_tts_test_$(date +%Y_%m_%d).log directory=dump_dir transport_tablespaces=tts_test

Copy datafile to oracle home.

In destination database with DataGuard configured. Execute impdp on Primary Database.

Copy datafiles to default datafile location.

cp ~oracle/tts_test01.dbf /u01/app/oracle/oradata/orcl/tts_test01.dbf

Create user tts_user and grant required privileges.

Create user tts_user
identified by oracle;
grant connect, resource to tts_user;

Now start import on destination database

impdp dumpfile=tts_test_$(date +%Y_%m_%d).dmp logfile=imp_tts_test_$(date +%Y_%m_%d).log directory=dump_dir transport_datafiles='/u01/app/oracle/oradata/orcl/tts_test01.dbf'

Now modify user and set default tablespace and imported tablespace

alter user tts_user default tablespace tts_test;

Set imported tablespace to read write.

alter tablespace tts_test read write;

Verify table count.

select count(*) from tbl_test;

Now insert rows on primary database so that log switch will occur and we can check recovery state on Standby database

insert /*+ APPEND */ into tbl_test
select /*+ Parallel */ * from tbl_test;
insert /*+ APPEND */ into tbl_test
select /*+ Parallel */ * from tbl_test;
insert /*+ APPEND */ into tbl_test
select /*+ Parallel */ * from tbl_test;

Execute following command on the Standby database to verify the status of recovery.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;

Check the name of the database we have imported with impdp. we can notice datafile with the name UNNAMED. Actually the file doesnot exists. So we need to copy the datafile that has been shipped with expdp and rename it.

select name from v$datafile;

Set standby_file_management database parameter to manual.

Alter system set standby_file_management=manual;

Now rename UNNAMED with the datafile we have copied.

alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00005' to '/u01/app/oracle/oradata/orcl/tts_test01.dbf';

Revert back standby_file_management database parameter to Auto.

Alter system set standby_file_management=auto;

Now start standby recover mode.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Now we can noticed that archivelog has been applied on standby database. Check alert<SID>.log for details. 

Read More

MySQL trigger that will execute OS command

Download and Install lib_mysqludf_sys plugin 

Go to URL https://github.com/mysqludf/lib_mysqludf_sys

Click on Download zip or use wget to download.

wget https://github.com/mysqludf/lib_mysqludf_sys/archive/master.zip

Extract the downloaded plugin file and change directory to extracted directory.

unzip master.zip
cd lib_mysqludf_sys-master/

Compile module with command below.

gcc -m64 -fPIC -Wall -I/usr/include/mysql -I. \

-shared lib_mysqludf_sys.c -o /usr/lib64/mysql/plugin/lib_mysqludf_sys.so

Login to mysql and create function sys_exec and sys_eval on required database.

mysql -u root -p

use test;

CREATE FUNCTION sys_exec RETURNS int SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_eval RETURNS int SONAME 'lib_mysqludf_sys.so';

Now for testing purpose we will create trigger that will execute after insert on each row of table tab_trig_test. First create table tab_trig_test.

use test;
create table tab_trig_test(
sn int,
name varchar(100)
);

Drop and create trigger that will execute after insert for each row on table tab_trig_test.
drop trigger if exists trig_test;

DELIMITER @@

CREATE TRIGGER trig_test 
AFTER INSERT ON tab_trig_test 
FOR EACH ROW 
BEGIN
 DECLARE cmd CHAR(255);
 DECLARE result int;
 SET cmd=CONCAT('echo ', new.name, ' > /tmp/haha',new.name);
 SET result = sys_exec(cmd);
END;
@@
DELIMITER ;

Now insert a row on table to verify that trigger has executed as expected.

insert into tab_trig_test values(2,'rajiv');

Verify that OS file has been created with command below.

\! cat /tmp/haha

Read More

Install zabbix agent on Window and Linux host

Install Zabbix agent on Windows

Create Zabbix directory. We can create directory on any location. In my case I choose to create directory on desktop.

Open URL:http://www.zabbix.com/download.php on browser.

Under Zabbix pre-compiled agents heading Click on Download on Windows Platform.

Save the downloaded file to newly create Zabbix directory on Desktop.

Extract Zabbix_agents_2.4.4.win.zip

Enter bin directory and on basic of architecture of your OS enter win32 or win64

Copy all *.exe to Zabbix directory on Desktop.

Enter conf directory and copy zabbix_agentd.win.conf file to Zabbix directory on Desktop and rename it as zabbix_agentd.conf

Open the file and remove all the contents and paste following lines
Server=127.0.0.1,192.168.1.250
ServerActive=192.168.1.250

Remove all unnecessary files as bin,conf directories and even source leaving 3 .exe files and 1 .conf file

Open windows terminal(command) as user Administrator to install zabbix-agentd services
Change directory to Desktop/Zabbix
Execute Following command

zabbix_agentd.exe --install -c zannox_agentd.conf

After Successfully install zabbix-agentd service start with command below

net start "Zabbix Agent"

Install Zabbix agent on Linux

Go to http://www.zabbix.com/download.php

Under Zabbix Packages heading select required Distribution, Version and Architecture and click on Download.

Download the required version of the zabbix-agent.

wget http://repo.zabbix.com/zabbix/2.4/rhel/6/x86_64/zabbix-agent-2.4.7-1.el6.x86_64.rpm

Install zabbix-agent with command below

rpm -ivh zabbix-agent-2.4.7-1.el6.x86_64.rpm

Modify agent file. We need to modify Server, ServerActive and hostname parameters values.

vi /etc/zabbix/zabbix_agentd.conf

Start Zabbix-agentd with command below

/etc/init.d/zabbix-agent start

Configure Zabbix-agent host on Zabbix server.

Open Zabbix server UI.

Click on Configuration and then Hosts.

Click Create host button on Top Right.

Add Host name, Visible name ad Group under which the host will be displayed. Enter IP address or DNS name of the host.

Click on Templates tab and add required templates that need to be monitored. Click on Update.

Verify 10050 port is listening on the host where zabbix agent is installed.

Finally we can find the Added host with Status Enabled and Green on Availability.

Read More