Jul 5, 2007

10g Recycle Bin

Oracle 10g Recycle Bin is a virtual container to hold dropped objects until owner or DBA purges them out or when no more free space left in its tablespace which needs to save more data or a new object.

Default recyclebin is ON. After a table or index is dropped, you will see a new table or index with a long name like BIN$% under current schema. As some systems created and dropped tables very often "by design", in 10g this may bother some users to see many BIN$% tables which are not able to be dropped by using "drop table" command".

The following are something need to know about it:


1. Check recycle bin

SQL> show recyclebin;
SQL> select * from recyclebin;
SQL> select * from user_recyclebin;
SQL> select * from dba_recyclebin; --with DBA privilege

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
SEQ_TELE BIN$NEZqOOkqZdrgRAADung28g==$0 TABLE 2007-07-02:06:37:26
TORCMPLIST BIN$NH0oo3EvagvgRAADung28g==$0 TABLE 2007-07-04:23:56:05
TORCMPLIST BIN$NGnVEBsMIJ/gRAADung28g==$0 TABLE 2007-07-04:00:52:42
TORCMPLIST BIN$NFlS+mOZHurgRAADung28g==$0 TABLE 2007-07-03:05:11:00


you can see multiple versions of dropped table. Cool!

2. Check the content in a dropped table.

Dropped objects still occupy the space in the database until manually purged or automatically removed by the database itself.

SQL> select * from "BIN$NFlS+mOZHurgRAADung28g==$0"


3. Flashback dropped table from recycle bin

This gives you a chance to bring back the dropped table.

SQL> FLASHBACK TABLE
SEQ_TELE TO BEFORE DROP RENAME TO SEQ_TELE_old;

--Flashback dropped table when it has mltiple versions
SQL> FLASHBACK TABLE "BIN$NFlS+mOZHurgRAADung28g==$0" TO BEFORE DROP RENAME TO TORCMPLIST_20070703051100;

4. Purge recycle bin

In order to avoid losing data by accident, by checking drop time of dropped objects, they can permanently and safely be removed from the database.
SQL> PURGE TABLE tablename;                   -- Remove specific table from recycle bin.
SQL> PURGE INDEX indexname; -- Remove specific index from recycle bin.
SQL> PURGE TABLESPACE ts_name; -- Empty recycle bin for a specific tablespace under current user.
SQL> PURGE TABLESPACE ts_name USER username; -- Empty recycle bin for a specific user under a specific tablespace.
SQL> PURGE RECYCLEBIN; -- Empty recycle bin for current user.
SQL> PURGE DBA_RECYCLEBIN; -- Empty the entire recycle bin in the database by DBA
5. Disable recycle bin feature

It is NOT recommneded as you lose the chance to easily restore the accidentally dropped table back. Think about how difficultly to do this prior to 10g.

SQL> alter seesion set recyclebin=off;
SQL> alter system set recyclebin=off;

Jul 4, 2007

[MySQL]Step by Step to Set up MySQL v4.x Replication

==
== Step by Step to Set up MySQL Replication on FC4
==
== MySQL Version: 4.1.11
== Fedora Core 4
==
== master: mysql1
== slave : mysql2
==
== It is a good practice, even with these old releases.
==
==============================================

== Configure Master Node
===============================================
--
--1. Update my.cnf
--
vi /etc/my.cnf

[mysqld]
log-bin ==> trun on binary log for replication
server-id=1 ==> unique against all slaves
binlog-do-db = mysql ==> database needs to generate binary log
binlog-do-db = test ==> reapet this for more databases

[mysqld_safe]
err-log=/var/log/mysqld.log ==> MySQL log file
pid-file=/var/run/mysqld/mysqld.pid ==> to save MySQL pid

--
--2. Restart master mysql
--
$ ps -efgrep mysqld
$ KILL -9 pid
$ /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid &

or
$ /etc/init.d/mysqld restart

--check if the log file is generated under datadir directory
$ ls -ltr datadir
or
mysql> show master logs;

--
--3. Create replcation account, which is used to access master from slave.
--
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'mysql2.domain' IDENTIFIED BY 'password';

mysql> FLUSH PRIVILEGES;

mysql> select * from mysql.user where user='repl'\G;

--
--4. Verfiy access in slave node
--
$ mysql -u repl -p -h mysql1

--
--5. Take database snapshot on master node
--
-- Closes all open tables and locks all tables for all databases with a read lock.
mysql> FLUSH TABLES WITH READ LOCK;

mysql> show master status;
+-------------------+----------+-------------------+------------------+
File Position Binlog_Do_DB Binlog_Ignore_DB
+-------------------+----------+-------------------+------------------+
mysql1-bin.000010 79 mysql,test
+-------------------+----------+-------------------+------------------+
1 row in set (0.00 sec)

--
--tar database files for each database
--
$ cd datadir/mysql
$ tar -cvf /tmp/mysql-snapshot.tar .
$ cd datadir/test
$ tar -cvf /tmp/test-snapshot.tar .

--re-enable write activities
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)


===========================================
== Configure Slave - mysql2
===========================================
--
--1. restore BACKUP SNAPSHOT to datadir
--
$ cd datadir/mysql
$ tar -xvf mysql-snapshot.tar
$ cd datadir/test
$ tar -xvf test-snapshot.tar

--
--2. Verfiy master database access from slave node
-- make sure access is successful before continuing
$ mysql -u repl -p -h mysql1

--Check mysql log if failed
$ tail -f /var/log/mysqld.log

--
--3. Configure my.cnf for replication
--
vi /etc/my.cnf

[mysqld]
server-id=2 ==> unique among master and slaves
replicate-do-db = mysql ==> database needs to be replicated
replicate-do-db = test ==>

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Note:
without setting replicate-do-db, I got the following error:
070703 15:26:29 [ERROR] Slave I/O thread: error connecting to master 'repl@mysql1:3306': Error: 'Can't connect to MySQL server on 'mysql1' (13)' errno: 2003 retry-time: 60 retries: 86400

--

--4. Start slave mysql
--
/usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid &
or
$ /etc/init.d/mysqld start

--
--5. Set up master info for replication
--
$ mysql -u root -p mysql

--set master
--log file and position from above result of "show master status;"
--
CHANGE MASTER TO
MASTER_HOST='mysql1',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql1-bin.000010',
MASTER_LOG_POS=79;

--
--6. start slave I/O and SQL Thread
-- stop slave
mysql> START SLAVE;

--
--7. Verify slave is running
--
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql1-bin.000013 ==> read by I/O Thread
Read_Master_Log_Pos: 139
Relay_Log_File: mysql2-relay-bin.000001 ==> read by SQL Thread
Relay_Log_Pos: 1054
Relay_Master_Log_File: mysql1-bin.000013
Slave_IO_Running: Yes ==> make sure I/O thread is running
Slave_SQL_Running: Yes ==> make sure SQL thread is running
Replicate_Do_DB: mysql,test ==> replicated databases
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 139
Relay_Log_Space: 1054
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

ERROR:
No query specified

--
--8. Check mysql log
--
$ tail -f /var/log/mysqld.log