Install rlwrap to enable command line history with sqlplus and rman

Download rlwrap package

wget http://freecode.com/urls/de7d8482e030110354012880805e76fd

Extract rlwrap source with command below.

tar xzvf rlwrap-0.41.tar.gz 

Change directory to extracted source. Configure it, make it and install it with commands below.

[root@oel1 dilli]# cd rlwrap-0.41
[root@oel1 rlwrap-0.41]# ./configure
[root@oel1 rlwrap-0.41]# 
[root@oel1 rlwrap-0.41]# make 
[root@oel1 rlwrap-0.41]# 
[root@oel1 rlwrap-0.41]# make install

Now create alias so that alias will execute rlwrap with sqlplus and rman as an argument. Include it on profile so that this alias will be executed for all the users.

[root@oel1 rlwrap-0.41]# cd /etc/profile.d/
[root@oel1 profile.d]# cat > oracle.sh
alias rsqlplus='rlwrap sqlplus'
alias rrman='rlwrap rman'
^C
[root@oel1 profile.d]# chmod 755 oracle.sh 

Now login to sqlplus as rsqlplus

rsqlplus scott

Execute SQL command

select count(*) from emp;

Now SQL command is saved on history. We can recall commands executed with UP Arrow.

Read More

Step by step mysql replication

Replication Master: oel1.localdomain, 192.168.1.201/24
Replication Slave: oel2.localdomain, 192.168.1.202/24

Perform following task on master server(oel1.localdomain). 

Create MySQL user in master server that will be used as mysql replication slave.

mysql> grant replication slave on *.* to rep_admin@'192.168.1.202' identified by 'rep_password';
mysql> flush privileges;

Add following parameters on /etc/my.cnf

[mysqld]
server-id = 1
log-bin = oel1-bin

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

Lock master database before generating dump.

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Generate dump of master database.

mysqldump -u root -p --master-data=2 --all-databases > oel1_database.dmp

Release lock from mysql master database.

UNLOCK TABLES;

Ship dumpfile to slave server.

scp oel1_database.dmp dilli@192.168.1.202:

Perform following task on slave server(oel2.localdomain). 

Search for change master statement on dumpfile.

[root@oel2 dilli]# grep -i "change master" oel1_database.dmp 

Add following lines on the /etc/my.cnf file of slave server.

[mysqld]

server-id = 2

relay-log = /var/lib/mysql/mysql-relay-bin.log

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

Start mysql server on replication client.

/etc/init.d/mysql start

Restore dumpfile on the slave server.

mysql -u root -p < oel1_database.dmp

Change master setting on slave.

CHANGE MASTER TO MASTER_HOST='192.168.1.201',MASTER_USER='rep_admin', MASTER_PASSWORD='rep_password', MASTER_LOG_FILE='oel1-bin.000001', MASTER_LOG_POS=120;

Begin log apply with Start slave.

start slave;

Verify replication setup. Add few rows on the master server and verify the change on slave server.

Insert rows on Master server.

Verify change on the slave server.

Additional my.cnf parameters.

# Master related Parameters

server-id=666

log-bin=oel1-bin

binlog_format=mixed

# Slave related Parameters

replicate-ignore-db=mysql

replicate-ignore-table=dlee.test

max_relay_log_size=104857600

skip-slave-start

read_only=1

# Connection related Parameters

max_connections=2048

max_allowed_packet=67108864

wait_timeout=60

# logging related parameters

long_query_time=5

slow_query_log=OFF

slow_query_log_file=/var/log/mysql-slow.log

log_queries_not_using_indexes=OFF

# Innodb related parameters

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

innodb_file_per_table=1

innodb_buffer_pool_size=12G

innodb_buffer_pool_instances = 1

innodb_max_dirty_pages_pct = 70

innodb_log_file_size = 2024M

innodb_flush_log_at_trx_commit=0

innodb_flush_method=O_DSYNC

innodb_autoinc_lock_mode = 2

innodb_open_files = 2024

innodb_purge_threads = 2

innodb_purge_batch_size = 300

innodb_max_purge_lag = 5000000

innodb_io_capacity = 200

innodb_read_io_threads = 4

innodb_write_io_threads = 4

innodb_thread_concurrency=0

innodb_file_format = barracuda

innodb_table_locks = 0

innodb_stats_on_metadata = 0

innodb_change_buffering = ALL

innodb_log_buffer_size=28M

innodb_log_files_in_group=2

# Innodb flush parameters

innodb_flush_method=O_DIRECT

Read More

Step by step DataGuard Physical Standby Setup

Oracle Version: 11.2.0.3
OS Version: Oracle Enterprise Linux 6.3
Primary DB Host: oel1.localdomain, 192.168.1.201/24
Standby DB Host: oel2.localdomain, 192.168.1.202/24

Perform following task on Primary Server(oel1.localdomain)

Execute following SQL statement to verify database is on archivelog mode or not. 

SQL> SELECT log_mode FROM v$database;

If database is not on archivelog mode the alter database to archivelog mode

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

Execute following command to enable force logging.

SQL> alter database force logging;

In case of primary server both db_name and db_unique_name should be same. In our case it is orcl for both.

SQL> show parameter db_name

SQL> show parameter db_unique_name

Modify archive related oracle database parameter.

SQL> alter system set log_archive_config='DG_CONFIG=(ORCL,ORCL_STDBY)'; 

SQL> alter system set log_archive_dest_2='SERVICE=orcl_stdby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STDBY';

SQL> alter system set log_archive_dest_state_2=DEFER;

Change additional oracle parameters related to archive.

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

SQL> alter system set log_archive_max_processes=10;

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

SQL> alter system set fal_server=ORCL_STDBY;

SQL> alter system set standby_file_management=auto;

Add tns entry of standby server on  file $ORACLE_HOME/network/admin/tnsnames.ora

orcl_stdby =

    (DESCRIPTION = 

(ADDRESS_LIST = 

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))

)

(CONNECT_DATA =

    (SERVICE_NAME = orcl)

)

    )

Now create pfile from existing spfile and modify.

SQL> create pfile='/home/oracle/pfile' from spfile;

Modify following lines on pfile.

*.db_unique_name='orcl_stdby'

*.fal_server='orcl'

*.fal_client='orcl_stdby'

*.log_archive_dest_2='SERVICE=orcl NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'

Now ship pfile, password file , tnsnames.ora to standby server.

[oracle@oel1 ~]$ scp pfile oracle@192.168.1.202:

[oracle@oel1 ~]$ cd /u01/app/oracle/product/11.2.0.3/db_1/dbs/

[oracle@oel1 dbs]$ scp orapworcl oracle@192.168.1.202:

[oracle@oel1 dbs]$ cd ../network/admin/

[oracle@oel1 admin]$ scp tnsnames.ora oracle@192.168.1.202:

Check the number of redo log files and create standby redolog files at least one extra group than the number of redo logs. 

SQL> select group#, f.member, l.bytes from v$log l join v$logfile f using(group#);

In our case There are 3 number of logfiles so we need to create at least 4 standby redo logfiles.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby_redo01.log') size 52428800;
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby_redo02.log') size 52428800;
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby_redo03.log') size 52428800;
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby_redo04.log') size 52428800;

SQL> select * from v$logfile;

Perform following task on Standby Server(oel2.localdomain)

Create necessary directories on the standby server.

[oracle@oel2 admin]$ mkdir -p /u01/app/oracle/oradata/orcl

[oracle@oel2 admin]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcl

[oracle@oel2 admin]$ mkdir -p /u01/app/oracle/admin/orcl/adump

[oracle@oel2 admin]$ 

Configure listener with static SID and start listener.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@oel2 admin]$ lsnrctl start

Copy password file, tnsnames file and pfile to required location.
Startup database in nomount mode with pfile shipped earlier.

Now from primary database use rman to connect target and auxiliary database.

[oracle@oel1 trace]$ rman target sys/password@orcl auxiliary sys/password@orcl_stdby nocatalog

Execute Duplicate command from RMAN prompt

If standby redolog error occured as below then re-create standby redolog files manually.

Start redo apply on standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Now enable log archive destination on both Primary and Standby server.

SQL> alter system set log_archive_dest_state_2=enable;

Read More

Reset mysql root password

Note mysql running process. Copy mysqld_safe executed process 

ps ax | grep mysql

Stop running mysql process.

/etc/init.d/mysql stop

Append skip-grant-tables at the end of the mysqld_safe process and execute mysqld_safe manually with command below.

/usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/oel1.localdomain.pid --skip-grant-tables

On next terminal window, login to mysql as user root without password.

[root@oel1 ~]# mysql -u root

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Reset mysql password with command below. 

mysql>  update mysql.user set password=PASSWORD('P@ssw0rd5308')

    -> where user='root';

Query OK, 4 rows affected (0.01 sec)

Rows matched: 4  Changed: 4  Warnings: 0

mysql> 

Reload privileges with Flush privilege command.

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Kill the existing running mysql process.

[root@oel1 ~]# ps ax | grep mysql | grep -v grep | awk '{print $1}' | xargs kill

Start mysql process now you can login with new password

/etc/init.d/mysql start
mysql -u root -pP@ssw0rd5308 -h 127.0.0.1

Read More