Thursday, December 28, 2017

Custom Gitlab Hooks

8:12 PM Posted by Dilli Maharjan No comments

Gitlab installation

Update linux machine with yum update.
yum -y update


Once update is completed install postfix for notifications. 
yum install postfix
service postfix start
chkconfig postfix on



Add the GitLab package repository and install the package
curl https://packages.gitlab.com/install/repositories/gitlab/gitlab-ee/script.rpm.sh | bash


Install gitlab package. Here specify the External URL you want gitlab to be accessed.
EXTERNAL_URL="http://192.168.100.25" yum -y install gitlab-ee



If you need to change EXTERNAL_URL or IP address of the host where gitlab is installed following are the steps.
1. Modify external_url parameter in file /etc/gitlab/gitlab.rb
2. gitlab-ctl reconfigure

Start gitlab with command below
gitlab-ctl start



Configure gitlab on 8888 port so that apache can be started on port 80.
Open /etc/gitlab/gitlab.rb and modify 
nginx['listen_port'] = nil to nginx['listen_port'] = 8888
external_url 'http://192.168.100.25' to external_url 'http://192.168.100.25:8888'






Once modification completes execute reconfigure command
gitlab-ctl reconfigure



Restart gitlab to get the new setting in effect.
gitlab-ctl restart



Login to gitlab external url with following address
http://192.168.100.25:8888

Once you open page for the first time you need to set the root user password. Type root user password and click on Submit.


Now register new user. Click on Create project to add new gitlab project.



Set Project name. Click on Create Project.


 Clone git repository locally.
git clone http://192.168.100.25:8888/dmaharjan/dilli_com_np.git


Add index page on local repository.
cd dilli_com_np
echo "This is index page" >> index.html



Add file, commit and push to git remote repository.
git add .
git commit -m "Adding index.html"
git push


Install apache. In our case apache server is web hosting for the client.
yum -y install httpd


Change directory to /var/www/html and clone git repo.
git clone http://dmaharjan@192.168.100.25:8888/dmaharjan/dilli_com_np.git



The page is accessible with the URL below:
http://192.168.100.25/dilli_com_np/index.html


Change directory to remote repository in our case /var/opt/gitlab/git-data/repositories/dmaharjan/dilli_com_np.git . Here dmaharjan is repos user and dilli_com_np is repository directory.
cd /var/opt/gitlab/git-data/repositories/dmaharjan/dilli_com_np.git
Create directory custom_hooks


Create hook file named post-receive with the content below
#!/bin/bash

cd /var/www/html/dilli_com_np
sudo /usr/bin/git stash
sudo /usr/bin/git pull



Change ownership of hook file and make it executable.
chown -R git:git post-receive 
chmod 755 post-receive 



Change sudo configuration and add following
git         ALL=NOPASSWD: /usr/bin/git stash, /usr/bin/git pull



Change ownership of /var/www/html/dilli_com_np
chown -R apache:apache /var/www/html/dilli_com_np


Login as user git and verify sudo is working for user git. Make sure Password is not required for sudo and git pull



Now change the content of the local repo, add the file, commit it and push it. The hook should be executed and change should be reflected immediately on the web directory.
Change directory to local repository and add content to the file.
echo "Adding new line 1" >> index.html 
git add .
git commit -m "Testing hook"
git push




View change on the web directory.









Monday, December 18, 2017

Configure samba to share oracle files with Windows server

10:00 PM Posted by Dilli Maharjan No comments

Environment: CentOS/Windows Machine


Configure following on linux server with Oracle database.


Add os group for samba
groupadd smbgrp

Create Linux OS user with useradd command. In my case I am adding user ajay and setting password using command passwd.
useradd ajay -G smbgrp
passwd ajay


Create directory that will be shared with Windows server.  I am using this directory to store my oracle dump files which will be shared with my Windows Server. Owner and group of this folder have permission of read, write and execute. If you do not need write permission to group then you may use chmod 750 instead of 770.
mkdir /u01/app/oracle/dumpdir
chmod 770 /home/oracle/dumpdir
chown oracle:smbgrp /u01/app/oracle/dumpdir



Create oracle directory with SQL command below. Oracle directory object need to be created which will point to OS directory. We will use this directory object name while using expdp.
create directory dir_dump as '/u01/app/oracle/dumpdir';



If samba linux package is not installed then install samba package and its dependency with yum command.
yum install samba -y



Use smbpasswd command to configure username and password that will be used to access the share directory. Note you can use different password from the password used for OS user ajay. 
smbpasswd -a ajay


Open samba configuration file with editor to configure samba.
vi /etc/samba/smb.conf


Change workgroup to your existing workgroup 
workgroup = WORKGROUP


Add following samba configuration parameters at the end of the configuration files.
[Share]
path=/u01/app/oracle/dumpdir
valid users=ajay
read only=yes
public=no
writable=no
valid host=192.168.100.44



Explanation:
[Share] Share directory name that will be visible to the user
path OS location of the directory we are sharing
valid users list of the trusted users. The user need to supply password to authenticate 
read only defines the shared directory is read only or read write.
public defines this shared directory is publicly available or not. If the value is set to yet then username/password will not be prompted while accessing shared directory.
writable defines the share directory is writeable or not 
valid host list of the trusted hosts that have access on share directory.

Restart samba service to reflect the configuration added.
/etc/init.d/smb restart



Now export dump from the database server, make sure that shared directory is used
expdp system directory=dir_dump dumpfile=system.dmp logfile=system.log schemas=system

    .....................

Windows host

Now open Windows server with the IP address specified above. If my case my windows machine's IP address is 192.168.100.44. Only this machine has access to this share directory.

Type \\192.168.100.101  on run or \\hostname of linux. You may also click on network which will list the name of the linux server.



Since public=no is set on configuration, you will be prompt for the password. Please provide the samba user and the password for samba server. In my case I am using ajay as the user and the passwod that I have provided with smbpasswd.


Now we can easily access the dump files created on the shared directory of Linux server.



Saturday, July 22, 2017

Oracle tablespace point in time recovery(TSPITR)

9:16 AM Posted by Dilli Maharjan No comments


RMAN TSPITR(Recovery Manager Tablespace Point In Time Recovery) enables quick recovery of one or more tablespaces in a database to an earlier time without affecting the rest of the tablespaces and objects in the database.

RMAN TSPITR is useful in following scenarios:
  • Recover from data loss after DDL as truncate, Drop table with purge.
  • Recover from logical corruption of the table.
  • Recover from dropped tablespaces.
  • Recover from incorrect batch job on table belongs to single tablespace.

Modes:
  • Fully Automated 
  • Automated: RMAN-Managed Auxiliary Instance with User Settings
  • Non-Automated: TSPITR and User-Managed Auxiliary Instance

Scenario: There is batch data operation: insert, update into a table. It is noticed that all the data inserted after certain time is incorrect and need to revert it back. The time until which date need to be recovered is termed as Target time. We are using Fully Automated TSPITR to recover tablespace tbs01 till target time.


Create tablespace for Demo propose.
create tablespace tbs01
datafile '/u01/app/oracle/oradata/orcl/tbs01.dbf'
size 10m
autoextend on;



Create user that holds table.
Create user demo 
identified by oracle
default tablespace tbs01
quota unlimited on tbs01;



Grant required privileges to the user.
grant connect,resource to demo;
grant execute on dbms_lock to demo;



Connect as user demo and create table that will be used on recovery
conn demo/oracle;
create table tbl_demo(
sn number,
data varchar2(20)
);



Create PL/SQL code to insert data into demo TABLE
declare
a number;
begin
for a in 1..50
loop
insert into tbl_demo values(a,to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
commit;
dbms_lock.sleep(5); --sleep for 5 second
end loop;
end;
/



Wait for few minutes to complete this PL/SQL Block.
Right now there are 50 number of rows on the table tbl_demo. Now we are doing
Tablespace point in time recovery till 46th row and data: 2017-07-18 21:16:04
That means we are recovering until Date and Time: 2017-07-18 21:16:05.
select * from tbl_demo;

..............


Querying DBMS_TTS.TRANSPORT_SET_CHECK for a Subset of Tablespaces and check for any violations. 
begin
dbms_tts.transport_set_check('TBS01',TRUE,TRUE);
end;
/

select * from transport_set_violations;



Query the TS_PITR_OBJECTS_TO_BE_DROPPED data dictionary view on the primary database to list all the database objects that will be dropped once you perform TSPITR. We will search with the reference of the target time. In our case target time is 2017-07-19 08:00:01.

select owner, name, tablespace_name, to_char(creation_time,'YY-MM-DD:HH24:MI:SS')
from ts_pitr_objects_to_be_dropped
where tablespace_name='TBS01'
and creation_time > to_date('2017-07-19 08:00:01','YYYY-MM-DD HH24:MI:SS')
order by tablespace_name,creation_time;



AUXILIARY DESTINATION parameter is use to set a location for RMAN to use for the auxiliary set data files. Create auxiliary destination for tablespace point in time recovery
mkdir /tmp/auxdest

Execute rman and connect to target Database and execute following command
rman target /
run
{
recover tablespace tbs01
until time "to_date('2017-07-18 21:16:05','YYYY-MM-DD HH24:MI:SS')"
auxiliary destination '/tmp/auxdest';
}

..........


Once recovery is completed the tablespace in our case tbs01 will stay offline. We need to bring it to online before accessing.
alter tablespace tbs01 online;



Now connect user demo and select the table tbl_demo to access the recovered table as we have expected.
select * from tbl_demo;

.........


Following is the output of the RMAN command with detail explantation. Text with background are the output.

Starting Tablespace PITR.
Starting recover at 18-JUL-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDO01

Creating auxiliary instance with random SID

Creating automatic instance, with SID='btar'

initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=btar_tspitr_ORCL
compatible=11.2.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/tmp/auxdest
log_archive_dest_1='location=/tmp/auxdest'
#No auxiliary parameter file used


starting up automatic instance ORCL

Oracle instance started

Total System Global Area     292933632 bytes

Fixed Size                     1336092 bytes
Variable Size                100666596 bytes
Database Buffers             184549376 bytes
Redo Buffers                   6381568 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

Starting Restoration of database on auxiliary instance with the following RMAN commands.

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2017-07-18 21:16:05','YYYY-MM-DD HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log 
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 18-JUL-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2017_07_18/o1_mf_s_949698787_dpwc4c64_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2017_07_18/o1_mf_s_949698787_dpwc4c64_.bkp tag=TAG20170718T211307
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/auxdest/ORCL/controlfile/o1_mf_dpwcskct_.ctl
Finished restore at 18-JUL-17

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2017-07-18 21:16:05','YYYY-MM-DD HH24:MI:SS')";
plsql <<<-- font="" tspitr_2="">
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'TBS01' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  5 to 
 "/u01/app/oracle/oradata/orcl/tbs01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 5;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TBS01 offline immediate

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/auxdest/ORCL/datafile/o1_mf_temp01_%u_.tmp in control file

Starting restore at 18-JUL-17
using channel ORA_AUX_DISK_1

creating datafile file number=5 name=/u01/app/oracle/oradata/orcl/tbs01.dbf
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/auxdest/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/auxdest/ORCL/datafile/o1_mf_undo01_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /tmp/auxdest/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_07_17/o1_mf_nnndf_FULL_DB_BACKUP_ON_ST_dpsokmr7_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_07_17/o1_mf_nnndf_FULL_DB_BACKUP_ON_ST_dpsokmr7_.bkp tag=FULL_DB_BACKUP_ON_START
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 18-JUL-17

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=949699496 file name=/tmp/auxdest/ORCL/datafile/o1_mf_system_dpwcsq07_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=949699496 file name=/tmp/auxdest/ORCL/datafile/o1_mf_undo01_dpwcsq0y_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=949699496 file name=/tmp/auxdest/ORCL/datafile/o1_mf_sysaux_dpwcsq0t_.dbf

Start recovering auxiliary database instance until the specified time.

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2017-07-18 21:16:05','YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  5 online";
# recover and open resetlogs
recover clone database tablespace  "TBS01", "SYSTEM", "UNDO01", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  5 online

Starting recover at 18-JUL-17
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_17/o1_mf_1_14_dpsol2ry_.arc
archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_15_dpty3y5f_.arc
archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_16_dpty41xy_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_17_dpv1y2s4_.arc
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_18_dpv22p7t_.arc
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_19_dpv2c55w_.arc
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_20_dpw8rzoy_.arc
archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_21_dpwcspjx_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_17/o1_mf_1_14_dpsol2ry_.arc thread=1 sequence=14
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_15_dpty3y5f_.arc thread=1 sequence=15
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_16_dpty41xy_.arc thread=1 sequence=16
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_17_dpv1y2s4_.arc thread=1 sequence=17
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_18_dpv22p7t_.arc thread=1 sequence=18
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_19_dpv2c55w_.arc thread=1 sequence=19
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_20_dpw8rzoy_.arc thread=1 sequence=20
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_21_dpwcspjx_.arc thread=1 sequence=21
media recovery complete, elapsed time: 00:00:03
Finished recover at 18-JUL-17

database opened

Making target tablespace on auxiliary instance read only and creating oracle directory and performing transportable tablespace. 

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  TBS01 read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/tmp/auxdest''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/tmp/auxdest''";
}
executing Memory Script

sql statement: alter tablespace  TBS01 read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/auxdest''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/auxdest''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_btar":  
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_btar" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_btar is:
   EXPDP>   /tmp/auxdest/tspitr_btar_43317.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TBS01:
   EXPDP>   /u01/app/oracle/oradata/orcl/tbs01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_btar" successfully completed at 21:25:12
Export completed


Dropping the target tablespace on the main database and importing the transportable tablespace exported earlier.

contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace  TBS01 including contents keep datafiles';
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

sql statement: drop tablespace  TBS01 including contents keep datafiles

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_btar" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_btar":  
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_btar" successfully completed at 21:25:21
Import completed

Removing and cleaning the auxiliary instance leaving the recovered tablespace offline.

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  TBS01 read write';
sql 'alter tablespace  TBS01 offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace  TBS01 read write

sql statement: alter tablespace  TBS01 offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /tmp/auxdest/ORCL/datafile/o1_mf_temp01_dpwctmy8_.tmp deleted
auxiliary instance file /tmp/auxdest/ORCL/onlinelog/o1_mf_3_dpwctmn8_.log deleted
auxiliary instance file /tmp/auxdest/ORCL/onlinelog/o1_mf_2_dpwctmgt_.log deleted
auxiliary instance file /tmp/auxdest/ORCL/onlinelog/o1_mf_1_dpwctm49_.log deleted
auxiliary instance file /tmp/auxdest/ORCL/datafile/o1_mf_sysaux_dpwcsq0t_.dbf deleted
auxiliary instance file /tmp/auxdest/ORCL/datafile/o1_mf_undo01_dpwcsq0y_.dbf deleted
auxiliary instance file /tmp/auxdest/ORCL/datafile/o1_mf_system_dpwcsq07_.dbf deleted
auxiliary instance file /tmp/auxdest/ORCL/controlfile/o1_mf_dpwcskct_.ctl deleted
Finished recover at 18-JUL-17



RMAN>