Flashback Version Query (Recover update/insert statements)

Flashback Version Query (Recover update/insert statements)

With help of flashback version query, we can check the value of particular column at specified timing it also depend upon the undo segments as long as it available in undo.
Note: its depend upto undo_retention parameter value, if it is 900 thats means 15 min, you can check this query within 15 min of server data.

In an example, we will show how it help to recover the data which is wrongly updated and don’t have previous record without full restore of database.

Suppose we have employee salary column in salary table that is updated wrongly but we need to roll-back it then we have to know the previous value of salary column for restore.
Other wise we have to restore completed backup at other location and copy that table to production environment

Example
This is our salary column in employees table we have to modified the salary of LEX (id 102) but by mistake it modified for Steven (id 100)

SQL> select employee_id,first_name,salary from employees;
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
100 Steven 24000
101 Neena 17000
102 Lex 17000
103 Alexander 9000

Updated the STEVEN salary instead of LEX salary

SQL> update employees set salary=18000 where employee_id=100;
1 row updated.
SQL> commit;
Commit complete.

Check the salary update and got it wrongly updated

SQL> select employee_id,first_name,salary from employees;
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
100 Steven 18000
101 Neena 17000
102 Lex 17000
103 Alexander 9000

On checking we modified the salary of wrong employee, we need to recover it.

Now with help of Flashback version Query we will tried to get old value of column

SELECT versions_starttime, versions_endtime, versions_xid, versions_operation, salary
FROM employees versions BETWEEN timestamp minvalue AND maxvalue where employee_id = 100 ORDER BY VERSIONS_STARTTIME

VERSIONS_STARTTIME    VERSIONS_ENDTIME VERSIONS_XID     V     SALARY
--------------------- ---------------- ---------------- ----- ---------
06-SEP-18 10.19.19 AM                  03001E001D030000 U     18000
06-SEP-18 10.19.19 AM                                         24000


Note: V= U means update, D means delete, I mmeans insert operation

Alternative method to check query with help of timestamp:

SELECT EMPLOYEE_ID, salary, versions_starttime, versions_endtime
FROM employees versions BETWEEN timestamp TO_DATE('9/6/2018 10:18:00','mm/dd/yyyy hh24:mi:ss') AND TO_DATE('9/6/2018 10:29:00','mm/dd/yyyy hh24:mi:ss') WHERE EMPLOYEE_ID = 100;

EMPLOYEE_ID SALARY VERSIONS_STARTTIME    VERSIONS_ENDTIME
----------- ------ --------------------- -----------------
        100 18000  06-SEP-18 10.19.19 AM 
        100 24000  06-SEP-18 10.19.19 AM


Note: If you donot use where clause then it will return all tables rows

You can check the Query as follows with Version_xid value

SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = '03001E001D030000';

Error: ORA-30052: invalid lower limit snapshot expression

SQL> SELECT EMPLOYEE_ID, salary, versions_starttime, versions_endtime
FROM employees versions BETWEEN timestamp TO_DATE('9/6/2018 10:00:00','mm/dd/yyyy hh24:mi:ss')
AND TO_DATE('9/6/2018 10:29:00','mm/dd/yyyy hh24:mi:ss') ;
FROM employees versions BETWEEN timestamp TO_DATE('9/6/2018 10:30:00','mm/dd/yyyy hh24:mi:ss') AND
TO_DATE('9/6/2018 10:29:00','mm/dd/yyyy hh24:mi:ss')
*
ERROR at line 2:
ORA-30052: invalid lower limit snapshot expression

Solution:
It depend upon undo retention value. If Undo retention is 15 minutes and the system time is 10:30 AM. So the first query won’t work because the time period is more than undo retention.
Following query will work if you change the time limit in first parameter successfully.

SELECT EMPLOYEE_ID, salary, versions_starttime, versions_endtime
FROM employees versions BETWEEN timestamp TO_DATE('9/6/2018 10:18:00','mm/dd/yyyy hh24:mi:ss') AND TO_DATE('9/6/2018 10:29:00','mm/dd/yyyy hh24:mi:ss');

Advertisements

Convert TIMESTAMP to SCN and SCN to TIMESTAMP in Oracle

Convert TIMESTAMP to SCN and SCN to TIMESTAMP in Oracle

In many recovery scenario we need to know our SCN and timestamps.
We can convert this by using the following function
SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN

We can use this function with help of dual functions.

Example of using this function as follows:

1. Convert the SCN to Timestamp

SQL> select scn_to_timestamp(2011955) from dual;

SCN_TO_TIMESTAMP(2011955)
-----------------------------------------------------
05-SEP-18 12.46.20.000000000 PM

2. Convert the Timestamp to SCN

SQL> select timestamp_to_scn(to_timestamp('05-09-2018 12:46:21','dd-mm-yyyy hh24:mi:ss')) scn from dual;

SCN
----------
2011955

Restore table data with Flashback Query in Oracle

Restore table data with Flashback Query in Oracle

Here is the example of table which is deleted by mistake and you can recovered this table from FLASBBACK Query.
Example will explain the Flashback Query concept and use. Flashback Query dependent on UNDO_RETENTION parameter.
So do recovery as quick as you can.

1. Check the no of rows in table

SQL> select count(*) from employee_bkp;

COUNT(*)
———-
101436

2. Get the current SCN Number and timestamp

SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') ddate, dbms_flashback.get_system_change_number() scn from dual;

DDATE SCN
——————– ———-
05-09-2018 12:46:21 2011955

3. Delete the table for shown in example (informed by application team accidentally deleted at what time).

SQL> delete from employee_bkp;
101436 rows deleted.

SQL> commit;
Commit complete.

4. Check the actual table data present after delete command.

SQL> select count(*) from employee_bkp;

COUNT(*)
———-
0

5. Different way to check the table data with flashback query:

--Check the table data with flashback query using Timestamp parameter.
SQL> select count(*) from employee_bkp as of timestamp to_timestamp('05-09-2018 12:45:00','dd-mm-yyyy hh24:mi:ss');
COUNT(*)
----------
101436

–Check the table data with flashback query using SCN number.
SQL> select count(*) from employee_bkp as of scn 2011955;
COUNT(*)
———-
101436

–Check the data present if it happens 90 min before. you can specify your time as you wanted.
select count(*) from employee_bkp as of timestamp (systimestamp -interval ’90’ minute);
COUNT(*)
———-
101436

Restore of table depends upon two cases:
1. Complete table restore
2. Some data of table restore

Case 1. If complete table is deleted then you can simple restored complete table as follows:

-- Insert into same table as follows:
insert into employee_bkp as select * from employee_bkp as of scn 2011955;

Case 2. If some rows need to recovered then first create a backup of table then you can use find deleted rows and insert into it by using minus/not in operator
— create new table with traditional method CTAS

SQL> create table emp_bkp as select * from employee_bkp as of timestamp to_timestamp('05-09-2018 12
:45:00','dd-mm-yyyy hh24:mi:ss');

SQL> select count(*) from emp_bkp;

COUNT(*)
———-
101436

SQL> select * from emp_bkp;
minus
select * from employee_bkp;
Note: Minus will give you detail of only distinct rows, so you need to find the count of rows how many time it need to insert into production table. If table having any primary or unique id then it will give you exact count.

Note:
Convert time stamp to SCN and SCN to Timestamp

SQL Reporting service error connection shared memory handshake

SQL Reporting service error connection shared memory handshake

I am tried to open the reporting service but getting the following error of connectivity with the database.

Error: Not able to handshake with Named Pipes
SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: )

Following are the caused of having handshake error. Hopefully one solution will fixed your problem

1. Check the SQL Server service is up or running.
Open the Services.msc and check SQL Server Service is running.

2. Check the Reporting database is present in SQL Server which you connected.

3. Go to SQL Serve configuration Manager –> Check SQL Serve Network configuration –> Protocol of SQL Server.

Check Shared Memory, TCP/IP and Named Pipes Protocol should be enabled. If disable right click and enabled them

4. Check the service SQL SERVER BROWSER SERVICE Should be running in Services.msc

5. Check the firewall setting of the windows and enable for the port 1433 with TCP protocol.
Detail:
Port Number: 1433
Protocol: TCP

6. Check the SQL Server authentication is mixed mode or windows authentication. Enable mixed mode authentication if SQL Server is in Window Authentication mode.
By right click on connected SQL Server in SQL Management Studio and go to properties and move windows authentication to mixed mode authentication and choose SA user password
Note: sa is the default user-name in SQL Server.
You should enable the Server authentication mode to mixed mode as following:
In SQL Studio, select YourServer -> Property -> Security -> Select SqlServer and Window Authentication mode.

7. At last, Check if both SQL Server Services and Reporting Service are running from same logon in Services.msc. If not then make them with one logon and tried once.

8. Check the log at Event Viewer in Windows and SQL Server error log files at %Program-Files%\Microsoft SQL Server\MSSQL13.SQL\MSSQL\LOG\ERRORLOG

Map EBS Storage with Linux EC2 instance in AWS

Map EBS Storage with Linux EC2 instance in AWS

In this blog, we are going to configure the EBS Storage to the redhat linux EC2 instance in Amazon web services.

Following are the steps

1. Create an EC2 instance of Redhat linux from AWS Console.

2. Create addition volume of EBS storage from AWS Console.

3. Attach the EC2 instance with EBS volume from EBS tab by right click on EBS Storage which is created in step 2.
Note: Both EC2 instance and EBS volume should be in same availability zone.

4. Open the putty session with private key.

5. Now first step is to check the disk present as shown below before attached the new EBS volume in step 3

-- command executed before step 3 it show only one volume
[ec2-user@ip-172-31-7-21 ~]$ lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
xvda 202:0 0 10G 0 disk
├─xvda1 202:1 0 1M 0 part
└─xvda2 202:2 0 10G 0 part /

6. After step 3 executed, command show the volume is attached

[ec2-user@ip-172-31-7-21 ~]$ lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
xvda 202:0 0 10G 0 disk
├─xvda1 202:1 0 1M 0 part
└─xvda2 202:2 0 10G 0 part /
xvdf 202:80 0 1G 0 disk

7. Switch to root user

sudo su -

8. Check the file system type which need to be formatted and already existed/used by Linux system

[root@ip-172-31-7-21 ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
xvda 202:0 0 10G 0 disk
├─xvda1 202:1 0 1M 0 part
└─xvda2 202:2 0 10G 0 part /
xvdf 202:80 0 1G 0 disk
[root@ip-172-31-7-21 ~]#
[root@ip-172-31-7-21 ~]# file -s /dev/xvda
/dev/xvda: x86 boot sector; partition 1: ID=0xee, active, starthead 0, startsector 1, 20971519 sectors, code offset 0x63
[root@ip-172-31-7-21 ~]#
[root@ip-172-31-7-21 ~]# file -s /dev/xvda1
/dev/xvda1: data
[root@ip-172-31-7-21 ~]# file -s /dev/xvda2
/dev/xvda2: SGI XFS filesystem data (blksz 4096, inosz 512, v2 dirs)
[root@ip-172-31-7-21 ~]#

Note: It show that linux system is using XFS filesystem.

8. Make a file system XFS as shown below example:

[root@ip-172-31-7-21 data1]# mkfs.xfs /dev/xvdf
meta-data=/dev/xvdf isize=512 agcount=4, agsize=65536 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=0, sparse=0
data = bsize=4096 blocks=262144, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=1
log =internal log bsize=4096 blocks=2560, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0

Note: You can make other filesystem also like ext4 of one partition

[root@ip-172-31-7-21 ~]# mkfs -t ext4 /dev/xvdg
mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
65536 inodes, 262144 blocks
13107 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=268435456
8 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376

Allocating group tables: done
Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

9. Mount the device to a new folder

mkdir /data1
mount /dev/xvdf /data1

10. Now check the lsblk command for output

[root@ip-172-31-7-21 data1]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
xvda 202:0 0 10G 0 disk
├─xvda1 202:1 0 1M 0 part
└─xvda2 202:2 0 10G 0 part /
xvdf 202:80 0 1G 0 disk /data1

11. You can check with df -Th command for file format:

[root@ip-172-31-7-21 data2]# df -Th
Filesystem Type Size Used Avail Use% Mounted on
/dev/xvda2 xfs 10G 925M 9.1G 10% /
devtmpfs devtmpfs 474M 0 474M 0% /dev
tmpfs tmpfs 496M 0 496M 0% /dev/shm
tmpfs tmpfs 496M 13M 483M 3% /run
tmpfs tmpfs 496M 0 496M 0% /sys/fs/cgroup
tmpfs tmpfs 100M 0 100M 0% /run/user/1000
tmpfs tmpfs 100M 0 100M 0% /run/user/0
/dev/xvdf xfs 976M 2.6M 907M 1% /data1

12. For make change permanent add entry in /etc/fstab file.
vi /etc/fstab

# /etc/fstab
# Created by anaconda on Fri Mar 23 17:41:14 2018
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
UUID=50a9826b-3a50-44d0-ad12-28f2056e9927 / xfs defaults 0 0
/dev/xvdf /data1 xfs defaults 0 0

13. Check the entry is good

mount -a
Example:
[root@ip-172-31-7-21 data2]# mount -a
[root@ip-172-31-7-21 data2]#

Note: If you type wrong name like /dev/svdf then you got following message
[root@ip-172-31-7-21 data2]# mount -a
mount: special device /dev/svdg does not exist

14. Now your drive is ready to use.

For Unmount the drive

[root@ip-172-31-7-21 data2]# umount /data1
[root@ip-172-31-7-21 data2]#

Map EBS Storage with Window EC2 instance in AWS

Map EBS Storage with Window EC2 instance in AWS

We are showing the example of mapping the EBS Storage device in Windows EC2 instance of Amazon web services. We created seperate EBS Storage and tried to map this storage with one of my instance EC2 which is running windows Server 2016

Note: For Mapping EBS storage and EC2 instance both in same zone.

following are the steps configured disk in Window EC2 instance

1. Create an Window EC2 instance and login in into it.

2. Go to EBS Storage on right side and create a volume.
Important: Before create check EC2 instance zone both EBS volume and EC2 instance should be in same zone.

Elastic Block Storage 1.PNG

3. Start Creating the EBS Volume from CREATE VOLUME button on AWS console.

Create EBS 2

4. Select the Size as you need and Zone (same as EC2 instance). Press CREATE Volume button at last.
In example i am choosing min as i need to show example. Always pick min as you need because it is model pay as you grow.

Create Volume 3

5. Attach the created EBS to EC2 instance.

Attached Volume 4

6. EBS volume is only attached if both are in same Zone.

Attach instance 6

7. Go to EC2 instance, Open the Computer Management –> Select Disk Management.

Computer Management 7.PNG

8. In Disk Management is show offline then online by right click on it.

Online the disk 8

10. Right Click then initialise disk for use.

Initialize Disk 9.PNG

11. Complete the initialize disk process.

MBT 10

12. Create new volume and select the drive name as D: drive and finish the process by next button.

New Sample Volume 11

13. Your device is formatted and ready for use.

Disable or Delete the Drive EBS

1. First detached the volume on AWS Console at EBS console –> volume –> go to action and select deattach the volume.

Deattach volume 13

2. Delete the volume.

Delete volume.PNG

Configure VNC for Amazon Cloud EC2 Redhat version from Windows

Configure VNC for Amazon Cloud EC2 Redhat version from Windows

1. Start the Redhat EC2 Machine from Amazon Web Service Cloud.

2. Connect with Redhat EC2 Machine with putty.

Connect with Linux EC2 instance

3. Switch ec2-user to root user for install the following packages.

sudo su -

4. Install the following packages in same sequence with root user.

yum groupinstall 'Server with GUI'
yum install -y pixman pixman-devel libXfont
yum -y install tigervnc-server

5. You can set password for ec2-user

passwd ec2-user

6. Open the /etc/ssh/sshd_config file and set following parameter

cd /etc/ssh
vi sshd-config
--set the following parameter to YES option and uncomment with NO option
password authentication parameter to yes
ChallengeResponseAuthentication to yes

Change SSHD config file

7. Restart the sshd service using the below command service sshd restart

[root@ip-172-31-16-82 ssh]# service sshd restart
Redirecting to /bin/systemctl restart sshd.service

8. Exit the root user and connect with ec2-user

[root@ip-172-31-16-82 ssh]# exit
logout
[ec2-user@ip-172-31-16-82 ~]$

9. Setup a VNC password with command vncpasswd

[ec2-user@ip-172-31-16-82 ~]$ vncpasswd
Password:
Verify:
Would you like to enter a view-only password (y/n)? n
A view-only password is not used
[ec2-user@ip-172-31-16-82 ~]$

10. Start a VNC Server using the below command vncserver :1

[ec2-user@ip-172-31-16-82 ~]$ vncserver :1
xauth: file /home/ec2-user/.Xauthority does not exist

New 'ip-172-31-16-82.us-east-2.compute.internal:1 (ec2-user)' desktop is ip-172-31-16-82.us-east-2.compute.internal:1
Creating default startup script /home/ec2-user/.vnc/xstartup
Creating default config /home/ec2-user/.vnc/config
Starting applications specified in /home/ec2-user/.vnc/xstartup
Log file is /home/ec2-user/.vnc/ip-172-31-16-82.us-east-2.compute.internal:1.log
[ec2-user@ip-172-31-16-82 ~]$

VNC Putty connection.PNG

11. In AWS Cloud, we need to go to Security group of EC2 Redhat Linux instance and open the VNC port 5901 for connectivity, for multiple connectivity 5900-5910 port need to open.

Go to EC2 Dashboard --> Select the Redhat machine --> Below on description tab, Select the security group to modified --> In Security group go to inbound --> add new rule ---> TCP --> PORT RANGE : 5900-5910 , SORUCE: anywhere --> Save it

12. Install your vnc viewer of ULTRA VNC Viewer or as you want.

For connectivity use public address or Public IP with PORT 5901
Enter : at vnc viewer window
Enter password set for VNC Server in step 9

VNCViewer.PNG