[ORACLE-FLASHBACK] FLASHBACK TABLE TO TIMESTAMP

 Flashback Technology provided by Oracle is one of the most useful in a production environment. It is a life saver. You can even rollback a query to a point in time without requiring traditional database restore. I will demonstrate below how to do flashback a table to a point in time. But first you have to enable flashback.

From 11gR2 onwards you don’t have to shutdown the database to enable Flashback. You can do it online. To enable flashback in 11gR2 do the below steps.

1.  alter system set db_recovery_file_dest=’+FRA’ scope=both sid=’*’;

2. alter system set db_recovery_file_dest_size=’400G’ scope=both sid=’*’;

3.  select flashback_on from v$database;

FLASHBACK_ON
——————
NO

4. alter database flashback on;
select flashback_on from v$database;

FLASHBACK_ON
——————
YES
_______________________________________________________

— For a table to be flash-backed you need to have row movement enabled on it. We have a table here called Employees in Schema HR
ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;

— Check current SCN and Database Time
SELECT current_scn, TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI:SS’) FROM v$database;
4784136875    2012-04-30 8:30:00

— Check the current total no. of records in the table
SELECT COUNT(*) FROM HR.EMPLOYEES
Count(*)
126

— Check the current total no. of records in the table as it existed half an hour back at 8:00 AM
SELECT COUNT(*) FROM HR.EMPLOYEES AS OF TIMESTAMP TO_TIMESTAMP(‘2012-04-30 8:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
Count(*)
127
This means there is one record was deleted. We need to flashback the table as it existsed at 8:00AM

FLASHBACK TABLE HR.EMPLOYEES TO TIMESTAMP TO_TIMESTAMP(‘2012-04-30 8:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);

SELECT COUNT(*) FROM HR.EMPLOYEES
Count(*)
127

Now we can see that the record are displaying as 127. Which means table is back to the state before the record was deleted.

[ORACLE GOLDENGATE] Fixed - Oracle GGS Extract PROCESS ABENDING

1. Checking Extract PROCESS ABENDING, Last Log Checkpoint:

GGSCI (SOURCE) 85> info extract FEXTR1,detail

EXTRACT    FEXTR1    Last Started 2021-09-02 20:54   Status ABENDED

Checkpoint Lag       00:00:00 (updated 00:31:37 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2021-08-30 15:11:05  Seqno 678, RBA 571035136

                     SCN 2183.433217452 (9376346824620)

2. Stop extract/pump/replicat both Source and Target:

Source:

stop extract FEXTR1

stop extract FPXTR1

info all

Target:

stop REPLICAT FRP1

info all

3. Fix GGS extract/pump on DB Source

Soure:

--Check log to determine what time extract was ABENDED.

--or show info extract FEXTR1,detail -->2021-08-30 15:11:05

--Extract

ALTER EXTRACT FEXTR1 BEGIN 2021-08-30 15:11

ALTER EXTRACT FEXTR1 EXTRBA 0

ALTER EXTRACT FEXTR1 ETROLLOVER

start extract FEXTR1

INFO EXTRACT FEXTR1 SHOWCH ---> Current Checkpoint (current write position): Sequence #: 246 

--Pump

--Get EXTSEQNO from extract INFO EXTRACT FEXTR1 SHOWCH ---> Current Checkpoint (current write position): Sequence #: 246 

--Get EXTSEQNO from et new generating. ls -alt /u01/app/oracle/GGS19c/dirdat --> File et000000246 -> EXTSEQNO=246

ALTER EXTRACT FPXTR1 EXTSEQNO 246

ALTER EXTRACT FPXTR1 EXTRBA 0

ALTER EXTRACT FPXTR1 ETROLLOVER

start extract FPXTR1

INFO EXTRACT FPXTR1 SHOWCH --> Write Checkpoint #1 GGS Log Trail  Current Checkpoint (current write position): Sequence #: 2

4. Fix GGS replicat on DB Target:

--Get EXTSEQNO from pump on DB Source INFO EXTRACT FPXTR1 SHOWCH --> Write Checkpoint #1 GGS Log Trail  Current Checkpoint (current write position): Sequence #: 2

--Get EXTSEQNO from new file generating from # ls -alt /u01/app/ogg/dirdat | more --> rt000000002 --> EXTSEQNO 2

ALTER REPLICAT FRP1 EXTSEQNO 2

ALTER REPLICAT FRP1 EXTRBA 0

start REPLICAT FRP1

5. Check GGS status

info all

...

Replicate is successful. The Problem was fixed.  Thank you

[MySQL] Replication database with GTID

 

Master::::::::::::::::::::::::

systemctl stop mysqld

vi /etc/my.cnf

The following parameters should be added under the [mysqld] section of my.cnf file

server-id = 1

log-bin = mysql-bin

binlog_format = row

relay-log = relay-log-server

relay-log = relay-log-server

gtid-mode=ON

enforce-gtid-consistency

log-slave-updates

systemctl start mysqld

mysql>create user 'repl_user'@'%' identified by '123456';

mysql>Grant replication slave on *.* to 'repl_user'@'%';

mysql>SET @@GLOBAL.read_only = ON;

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SET GLOBAL read_only = ON;

mysql> show master status;

show global variables like 'gtid_executed';

mysqldump --all-databases -flush-privileges --single-transaction --flush-logs --triggers --routines --events -hex-blob --host=localhost --port=3306 --user=root  --password=123456 > /tmp/mysqlbackup_dump.sql

SET @@GLOBAL.read_only = OFF;

SET GLOBAL read_only = OFF;

UNLOCK TABLES;

FLUSH PRIVILEGES;

slave::::::::::::::::

service mysqld stop

vi /etc/my.cnf

server-id = 2

log-bin = mysql-bin

binlog_format = row

relay-log = relay-log-server

relay-log = relay-log-server

read-only = ON

gtid-mode=ON

enforce-gtid-consistency

log-slave-updates

service mysqld start

mysql> show global variables like 'gtid_executed';

mysql> source mysqlbackup_dump.sql ;

mysql> show global variables like 'gtid_executed';

CHANGE MASTER TO MASTER_HOST = '192.168.114.135',MASTER_PORT = 3306,MASTER_USER = 'repl_user',MASTER_PASSWORD = '123456',MASTER_AUTO_POSITION = 1;

SET @@GLOBAL.read_only = OFF;

SET GLOBAL read_only = OFF;

UNLOCK TABLES;

FLUSH PRIVILEGES;

start slave;

mysql> show slave status \G

SELECT RECEIVED_TRANSACTION_SET FROM peformance_schema.replication_connection_status;

Switchover::::::::::::::

Master:

SET @@GLOBAL.read_only = ON;

FLUSH TABLES WITH READ LOCK;

SET GLOBAL read_only = ON;

FLUSH PRIVILEGES;

Slave:

Make sure that all slaves has processed any statements in their relay log with:

mysql> STOP SLAVE IO_THREAD;

mysql> SHOW PROCESSLIST; to see `Has read all relay log` state.on slave 1, promote it to become a master with:

mysql> STOP SLAVE;

mysql> RESET MASTER;

SET @@GLOBAL.read_only = OFF;

SET GLOBAL read_only = OFF;

UNLOCK TABLES;

FLUSH PRIVILEGES;

on old master (or slave 2), point to new master with:

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.114.136',MASTER_PORT = 3306,MASTER_USER = 'repl_user',MASTER_PASSWORD = '123456',MASTER_AUTO_POSITION = 1;

SET @@GLOBAL.read_only = OFF;

SET GLOBAL read_only = OFF;

UNLOCK TABLES;

FLUSH PRIVILEGES;

mysql> START SLAVE;

Failover::::::::::::::

mysql> STOP SLAVE IO_THREAD;

mysql> SHOW PROCESSLIST; to see `Has read all relay log` state.on slave 1, promote it to become a master with:

mysql> STOP SLAVE;

mysql> RESET MASTER;

SET @@GLOBAL.read_only = OFF;

SET GLOBAL read_only = OFF;

UNLOCK TABLES;

FLUSH PRIVILEGES;