How to install MongoDB on RHEL 6

How to install MongoDB on RHEL 6:-

Os version _ Rhel 6.4
MongoDB Version:-2.6.7

Step:1 System Login as root user. We are checking system OS type and system bits type.

# uname –a
Linux server1.soumya.com 2.6.32-358.el6.x86_64 #1 SMP Tue Jan 29 11:47:41 EST 2013 x86_64 x86_64 x86_64 GNU/Linux

# cat /etc/issue
Red Hat Enterprise Linux Server release 6.4 (Santiago)
Kernel \r on an \m


Step:2 Now we are creating a yum repo file .like /etc/yum.repos.d/mongodb.repo

# vi /etc/yum.repos.d/mongodb.repo
[mongodb]
name=mongodb Repository
baseurl=http://downloads-distro.mongodb.org/repo/redhat/os/x86_64
gpgcheck=0
enabled=1

--save & exit(:wq)

Step:3 Now we install mongodb client and server using yum

# yum install mongo-*

If you face any error while installing the rpms do the following
# yum erase mongo*
yum shell
> install mongodb-org
> remove mongo-10gen
> remove mongo-10gen-server
> run

Step:4 Now we can configure and basic setting in Mongodb Database Server

# vi /etc/mongod.conf
logappend=true
logpath=logpath=/var/log/mongodb/mongod.log
port=27017
dbpath=/var/lib/mongo
smallfiles = true
:wq

Step:5 Start Mongodb Server

# /etc/init.d/mongod start
# chkconfig mongod on

Open another terminal and type
#mongo


Show all database:-
>show dbs

Define a database name as soumya:-
>use soumya

then to check your current db name:-
>db

To add the created db in dblist we need to add collection in this database.For instance, create a document in a customers collection like this:
db.customers.save({"firstName":"Alvin", "lastName":"Alexander"})

Next, verify that your document was created with this command:
db.customers.find()

Now check your db name:-
>show dbs

Now to add a new user in a db:-
>use pizza
>db.createUser( { user: "soumya",
              pwd: "redhat2",
              roles: [ "readWrite", "dbAdmin" ]
            } )

To check all the users in your current db:-
>show users
or
db.system.users.find()

To drop the database pizzas:-
use pizzas;
>db.dropDatabase()

To check current version:-
db.version()

Done..



Please share your ideas and opinions about this topic. If you like this post, then please share with others. Please subscribe on email for every updates on mail.

Fast-Start failover(FSFO) in Oracle 11g Dataguard

Failover is a one way process where your primary database goes down due to some reasons and to get back the production live without any data loss, you convert your existing Physical
Standby database to start behaving as Primary database..
The following conditions must be met before you can use the broker:

■ Primary and standby DB’s must be on same version
■ You must use a SPFILE to ensure the broker can persistently reconcile values between broker properties.
■ DG_BROKER_START parameter must be set to TRUE.
■ DG_BROKER_CONFIG_FILE file should be place in the shared area for RAC.
■ Oracle Net Services network files must be set up on the primary database and on the standby database.
■ To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the local listener of each instance.
■ Ensure the COMPATIBLE initialization parameter is set to the same value on all systems.
■ Flashback database should be enable for fast start failover.

Introduction of FSFO observer:-
The Dataguard Broker Observer (Observer) is used when fast start failover is required. Fast start failover is simply an automatic failover to the standby database.This is not often
desired as most often even the most urgent failover/switchover scenarios require configuration of the Application once the database has been activated at the DR site.
The observer will initiate the failover under the following conditions automatically:
By default, the observer will initiate failover to the target standby if and only if ALL of the following are true:
• observer is running
• observer and the standby both lose contact with the primary

o Note: if the observer loses contact with the primary, but the standby does not, the observer can determine that the primary is still up via the standby.
• observer is still in contact with the standby
• durability constraints are met
• failover threshold timeout has elapsed
In Oracle 11g, the user can configure failover conditions:
User configurable failover conditions (11g and later)
Oracle Database 11g Rel 1 introduced user configurable failover conditions that can trigger the observer to initiate failover immediately.

Health conditions
Broker can be configured to initiate failover on any of the following conditions. Conditions shown in blue are enabled by default.
• Datafile Offline (due to IO errors)
• Corrupted Controlfile
• Corrupted Dictionary
• Inaccessible Logfile (due to IO errors)
• Stuck Archiver


Primary database Server : server1.soumya.com
Standby database Server : server2.soumya.com
Observer database server: observer.soumya.com

Primary database: prim
Standby database: stand
Observer database: observer

Here I have already done a physical standby server setup and its working properly.So I will only show how to configure DGMRL and use Fast-Start Failover using it.

Here is host file entry for for all server , make sure its present in each of the server.

# cat /etc/hosts
[oracle@server2 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.2.102   server1.soumya.com      server1
192.168.2.104   server2.soumya.com      server2
192.168.2.105   observer.soumya.com     observer


Step 1:- Check parameter DG_BROKER_START on primary and standby:
SQL> show parameter DG_BROKER_START

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE

Step 2:- Set DG_BROKER_START  parameter to true on primary:

SQL> alter system set DG_BROKER_START=true scope=both;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE


Step 3:- Define a service for the listener that includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME
Update the listener.ora file on primary and standby database

In primary database :-

[oracle@server1 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = prim.soumya.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = prim)
    )
  )

(SID_DESC =
(GLOBAL_DBNAME = prim_DGMGRL.soumya.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = prim)
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.soumya.com)(PORT = 1521))
  )
)
ADR_BASE_LISTENER = /u01/app/oracle
INBOUND_CONNECT_TIMEOUT_LISTENER=120

-- save and quit (:wq)


In standby database:-

[oracle@server2 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = stand.soumya.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = stand)
    )
  )

(SID_DESC =
(GLOBAL_DBNAME = stand_DGMGRL.soumya.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = stand)

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server2.soumya.com)(PORT = 1521))
  )
ADR_BASE_LISTENER = /u01/app/oracle
INBOUND_CONNECT_TIMEOUT_LISTENER=120

-- save and quit (:wq)

Tnsnames.ora entry for all server:-

[oracle@ ~]$cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
prim =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.soumya.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRIM)
    )
  )

stand =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server2.soumya.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STAND)
    )
  )
observer
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = observer.soumya.com )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = observer)
    )
  )


Once changes are done , restart the listener.

Step 4: Now lets create the data guard broker configurations:-

[oracle@server1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/redhat@prim
Connected.
DGMGRL>  create configuration 'DBTEST'
> as primary database is 'prim'
> connect identifier is prim;
Configuration "DBTEST" created with primary database "prim"

DGMGRL> show configuration

Configuration - DBTEST

  Protection Mode: MaxPerformance
  Databases:
    prim - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED


Step 7 :- Now add standby database in data guard broker configuration:-

DGMGRL> add database 'stand' as
> connect identifier is stand
> maintained as physical;
Database "stand" added
DGMGRL>
DGMGRL> show configuration

Configuration - DBTEST

  Protection Mode: MaxPerformance
  Databases:
    prim  - Primary database
    stand - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Step 8:-Now enable the configurations

DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration - DBTEST

  Protection Mode: MaxPerformance
  Databases:
    prim  - Primary database
    stand - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


DGMGRL> show database verbose stand

Database - stand

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    stand

  Properties:
    DGConnectIdentifier             = 'stand'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'auto'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'prim, stand'
    LogFileNameConvert              = 'prim, stand'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'server2.soumya.com'
    SidName                         = 'stand'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server2.soumya.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stand_DGMGRL)(INSTANCE_NAME=stand)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/flash_recovery_area'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS


DGMGRL>  show database verbose prim;

Database - prim

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    prim

  Properties:
    DGConnectIdentifier             = 'prim'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'auto'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'stand, prim'
    LogFileNameConvert              = 'stand, prim'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'server1.soumya.com'
    SidName                         = 'prim'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1.soumya.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prim_DGMGRL)(INSTANCE_NAME=prim)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/flash_recovery_area'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - DBTEST

  Protection Mode: MaxPerformance
  Databases:
    prim  - Primary database
    stand - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> enable database stand;
Enabled.
DGMGRL> SHOW DATABASE VERBOSE stand;

Database - stand

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    stand

  Properties:
    DGConnectIdentifier             = 'stand'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'auto'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'prim, stand'
    LogFileNameConvert              = 'prim, stand'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'server2.soumya.com'
    SidName                         = 'stand'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server2.soumya.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stand_DGMGRL)(INSTANCE_NAME=stand)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/flash_recovery_area'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

Step 8 :-Ensure standby redologs are configured on primary & standby databases.

on primary Database:
SQL> set lines 200
SQL> col member for a50
SQL>
SQL> SELECT TYPE,MEMBER FROM V$LOGFILE;

TYPE    MEMBER
------- --------------------------------------------------
ONLINE  /u01/app/oracle/oradata/prim/redo03.log
ONLINE  /u01/app/oracle/oradata/prim/redo02.log
ONLINE  /u01/app/oracle/oradata/prim/redo01.log
STANDBY /u01/app/oracle/oradata/prim/redos4A.log
STANDBY /u01/app/oracle/oradata/prim/redos5A.log
STANDBY /u01/app/oracle/oradata/prim/redos6A.log
STANDBY /u01/app/oracle/oradata/prim/redos7A.log

7 rows selected.


On Standby Database:-
SQL> set lines 200
SQL> col member for a50
SQL> SELECT TYPE,MEMBER FROM V$LOGFILE;

TYPE    MEMBER
------- --------------------------------------------------
ONLINE  /u01/app/oracle/oradata/stand/redo03.log
ONLINE  /u01/app/oracle/oradata/stand/redo02.log
ONLINE  /u01/app/oracle/oradata/stand/redo01.log
STANDBY /u01/app/oracle/oradata/stand/redos4A.log
STANDBY /u01/app/oracle/oradata/stand/redos5A.log
STANDBY /u01/app/oracle/oradata/stand/redos6A.log
STANDBY /u01/app/oracle/oradata/stand/redos7A.log

7 rows selected.

Step 9:-  Configure the LogXptMode Property is set to SYNC

DGMGRL> EDIT DATABASE prim SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

DGMGRL> EDIT DATABASE stand SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

Step 10:-Configure the FastStartFailoverTarget property

DGMGRL> EDIT DATABASE prim SET PROPERTY
> FastStartFailoverTarget='stand';
Property "faststartfailovertarget" updated

DGMGRL> EDIT DATABASE stand SET PROPERTY
> FastStartFailoverTarget='prim';
Property "faststartfailovertarget" updated

Step 11:- Upgrade the protection mode to MAXAVAILABILITY, if required.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.

Step 12:-To enable the Flashback Mode run below queries on primary & standby database.

SQL> alter system set db_recovery_file_dest_size=1G;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';

System altered.

SQL> ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;

System altered.

Step 13:- Now restart the database one by one. and start the database in mount stage.But NOT TOGETHER.First on primary.

on Primary:-
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             314574840 bytes
Database Buffers           92274688 bytes
Redo Buffers                4308992 bytes
Database mounted.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL> alter database open;

Database altered.
SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
YES

On standby:-

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             314574840 bytes
Database Buffers           92274688 bytes
Redo Buffers                4308992 bytes
Database mounted.

SQL> alter database recover managed standby database cancel;

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL> alter database open;

SQL> alter database recover managed standby database using current logfile disconnect from session;

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
YES

Step 14:-Enable fast start failover
[oracle@server1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/redhat@prim
Connected.
DGMGRL> show configuration verbose;

Configuration - DBTEST

  Protection Mode: MaxAvailability
  Databases:
    prim  - Primary database
    stand - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database stand;

Database - stand

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    stand

Database Status:
SUCCESS

DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.

Step 15:- Start the observer
At Oberserver server(192.168.2.105):-
[oracle@observer ~]$ mkdir -p /u01/FSFO
[oracle@observer ~]$ cd /u01/FSFO
[oracle@observer  FSFO] ~]$ vi startobserver.sh
#!/bin/bash
# startobserver
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export BASE_PATH=/u01/FSFO:/opt/CTEact/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/bin:/etc:/usr/local/maint/oracle:/usr/ccs/bin:/usr/openwin/bin:/usr/dt/bin:/usr/local/bin:.
export PATH=$ORACLE_HOME/bin:$BASE_PATH
dgmgrl << eof
connect sys/redhat@prim
START OBSERVER;
eof

-- save & exit (:wq)

[oracle@observer  FSFO] ~]$chmod 775 startobserver.sh

Now start the observer:-
[oracle@observer  FSFO] ~]$sh startobserver.sh
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> Connected.
DGMGRL> Observer started


Step 16:-Verify the fast-start failover configuration
DGMGRL> SHOW CONFIGURATION VERBOSE;

Configuration - DBTEST

  Protection Mode: MaxAvailability
  Databases:
    prim  - Primary database
    stand - (*) Physical standby database

  (*) Fast-Start Failover target

Fast-Start Failover: ENABLED

  Threshold:        30 seconds
  Target:           stand
  Observer:         observer.soumya.com
  Lag Limit:        30 seconds (not in use)
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configuration Status:
SUCCESS


DGMGRL> show database prim

Database - prim

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    prim

Database Status:
SUCCESS

DGMGRL> show database stand

Database - stand

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    stand

Database Status:
SUCCESS

Step 17:- EXECUTE THE SWITCHOVER

DGMGRL> switchover to stand;
Performing switchover NOW, please wait...
New primary database "stand" is opening...
Operation requires shutdown of instance "prim" on database "prim"
Shutting down instance "prim"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "prim" on database "prim"
Starting instance "prim"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
        start up instance "prim" of database "prim"


Start the database at primary server(192.168.2.102)
SQL> startup
Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             360712184 bytes
Database Buffers           46137344 bytes
Redo Buffers                4308992 bytes
Database mounted.
Database opened.

[oracle@server1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/redhat@prim
Connected.
DGMGRL>  show configuration verbose

Configuration - DBTEST

  Protection Mode: MaxAvailability
  Databases:
    stand - Primary database
    prim  - (*) Physical standby database

  (*) Fast-Start Failover target

Fast-Start Failover: ENABLED

  Threshold:        30 seconds
  Target:           prim
  Observer:         observer.soumya.com
  Lag Limit:        30 seconds (not in use)
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configuration Status:
SUCCESS

DGMGRL> show database prim

Database - prim

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    prim

Database Status:
SUCCESS


Step 18:-Failover to standby(old primary)
Note:- We will need to run this from the new standby database
[oracle@server1 ~]$ dgmgrl sys/redhat@prim

DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> failover to prim;
Performing failover NOW, please wait...
Failover succeeded, new primary is "prim"
DGMGRL> exit

Now once failover is completed, we will find that our old primary(prim) is back again in as primary database.
[oracle@server1 ~]$ sqlplus / as sysdba

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
prim

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

But we will need to start the standby database as it has been shutdown by dataguard broker process.

At standby server(192.168.2.104):-

SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             352323576 bytes
Database Buffers           54525952 bytes
Redo Buffers                4308992 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Done...




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

The usage of orainstRoot.sh and root.sh scripts in Oracle 11g Standalone and RAC environment

The usage of orainstRoot.sh and root.sh scripts  in Oracle 11g Standalone and RAC environment :-

Oracle Version :- 11g
OS:- Rhel 6.4
ORACLE_HOME:- /u01/app/oracle/product/11.2.0/db_1
ORACLE_BASE:- /u01/app/oracle

Executing orainstRoot.sh and root.sh are the ultimate steps which we execute as root user to ensure that our installation process is done.
Its a good practice to save those scripts in somewhere safe.

Importance of running 'orainstRoot.sh' script:
orainstRoot.sh is  located inside  /u01/app/oraInventory directory.

We execute the script to accomplish the following goals:-
1.It creates the inventory pointer file (/etc/oraInst.loc),This file shows the inventory location and group it is linked to.
2)It Changes permission to 771 for  /u01/app/oraInventory  directory and change the groupname of the oraInventory directory to oinstall group.


Importance of running 'Root.sh' script:
Root.sh is located inside $ORACLE_HOME i.e /u01/app/oracle/product/11.2.0/db_1 directory.

We execute the script to accomplish the following goals:-
1)It will Creates a /etc/oratab file.This is the file which we use to make automatic Database shutdown and startup and we can identify how many databases are there in server.
2)It Sets the Oracle base and home environments.
3)It Sets an appropriate permission to the OCR base directory.
4)If there are any permissions or ownerships to be changed, these are done by root.sh as not all OSs allow regular userids to chmod or chown;
5)If there are any suid requirements (for example. $ORACLE_HOME/bin/oracle is suid/sgid 'oracle')
6)If there are any changes to the OS and root-protected files (crs and inittab)
7)If there are new directories (/var, /opt, especially for temp and logs) in root-only accessible parent dirs
8)If there are new files (/usr/local/bin/oraenv) in root-only accessible parent dirs
9)If there are special processes that need to be initiated
10)It Creates the OCR backup and Network Socket directories.
11)It Configures the OCR and voting disks (only on the first node)
12)It Starts the Clusterware daemons.
13)It adds Clusterware daemons to the inittab file.
14)It Verifies whether the Clusterware is up on all nodes.
15)On the last node, initiates ./vipca in silent mode to configure nodeapps,
such as, GSD, VIP, and ONS for all the nodes.
16)It verifies the super user privileges.
17)It Creates a trace directory.The 'trace' directory is again very vital for generating trace file to keep a track on user sessions in case of any error,troubleshooting and diagnosis purpose.
18)It Generates OCR keys for the 'root' user.
19)It Adds daemon information to the inittab file
20)Starts up the Oracle High Availability Service Daemon (OHASD) process.
21)Creates and configures an ASM instance and starts up the instance.
22)Creates required ASM disk groups, if ASM is being used to put OCR and voting files.
23)Starts up the Cluster Ready Service Daemon (CRSD) process
24)Creates the voting disk file.
25)It Puts the voting disk on the Voting disk,if ASM type is selected.
26)It Displays voting disk details
27)Stops and restarts a cluster stack and other cluster resources on the local node
28)It Updates the Oracle inventory file.
29)Completes with the UpdateNodeList success operation.





Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Create a profile with unlimited access and other unlimited privileges in Oracle 11g

Create a profile with unlimited access and other unlimited privileges in Oracle 11g

SQL> CREATE PROFILE "NEW" LIMIT
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
SESSIONS_PER_USER UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
PRIVATE_SGA UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;

Profile created.


SQL> select * from dba_profiles WHERE PROFILE='NEW';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
NEW                           COMPOSITE_LIMIT                  KERNEL   UNLIMITED
NEW                           SESSIONS_PER_USER                KERNEL   UNLIMITED
NEW                           CPU_PER_SESSION                  KERNEL   UNLIMITED
NEW                           CPU_PER_CALL                     KERNEL   UNLIMITED
NEW                           LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
NEW                           LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
NEW                           IDLE_TIME                        KERNEL   UNLIMITED
NEW                           CONNECT_TIME                     KERNEL   UNLIMITED
NEW                           PRIVATE_SGA                      KERNEL   UNLIMITED
NEW                           FAILED_LOGIN_ATTEMPTS            PASSWORD UNLIMITED
NEW                           PASSWORD_LIFE_TIME               PASSWORD UNLIMITED

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
NEW                           PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
NEW                           PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
NEW                           PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
NEW                           PASSWORD_LOCK_TIME               PASSWORD UNLIMITED
NEW                           PASSWORD_GRACE_TIME              PASSWORD UNLIMITED



Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Percona XtraDB Cluster configuration on CentOS 6.4

Percona XtraDB Cluster configuration on CentOS 6.4

Host 1: 192.168.72.22
Host 2: 192.168.72.23
Linux version : Centos 6.4
Percona version : 5.5

Prerequisites:-

All the nodes must have a CentOS 6.4 installation.
Firewall Must be disabled or atleast 3306 port must be open.
SELinux must be disabled.
Check if Mysql is already installed in the server also check if its running.
Remove mysql if its present by doing
$yum erase mysql
This has to be done in all nodes where mysql is present.Else this will conflict with the installation files of Percona.

Step 1:-(On both nodes)
Create Percona yum Repository

$ rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

Step 2:-(On both nodes)
Install XtraDB Cluster

$ yum install Percona-XtraDB-Cluster-server-55 Percona-XtraDB-Cluster-client-55 Percona-XtraDB-Cluster-galera-2

Step 3:-(On both nodes)
Percona yum Experimental repository

$ rpm -Uhv http://repo.percona.com/testing/centos/6/os/noarch/percona-testing-0.0-1.noarch.rpm


Step 4:-
Configuring the node1

Create a file in the following location

vim /etc/my.cnf  and enter the following lines

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.72.22,192.168.72.23

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This is a recommended tuning variable for performance
innodb_locks_unsafe_for_binlog=1

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=192.168.72.22

# SST method
wsrep_sst_method=xtrabackup

# Cluster name
wsrep_cluster_name=my_centos_cluster

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
wsrep_replicate_myisam=1
~
~
--save & exit (:wq)

After saving the file run the following command
/etc/init.d/mysql start --wsrep-cluster-address="gcomm://"

Now to enter in mysql:-
mysql -u root
Now update mysql root password:-
UPDATE mysql.user SET password=PASSWORD("r00t123007") where user='root';

mysqladmin -u root password NEWPASSWORD
Step 5:-Check the cluster status in node1

$mysql -u root -predhat
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | c2883338-834d-11e2-0800-03c9c68e41ec |
...
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
...
| wsrep_cluster_size         | 1                                    |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
...
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+
40 rows in set (0.01 sec)


This output shows that the cluster has been successfully bootstrapped.

In order to perform successful State Snapshot Transfer using XtraBackup new user needs to be set up with proper privileges:

mysql@percona1> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cr3t123#';
mysql@percona1> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
mysql@percona1> FLUSH PRIVILEGES;

Note MySQL root account can also be used for setting up the SST with Percona XtraBackup, but it’s recommended to use a different (non-root) user for this.

Step 6:-
Configuring node2

Create a file in the following location

vim /etc/my.cnf  and enter the following lines

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.72.22,192.168.72.23

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This is a recommended tuning variable for performance
innodb_locks_unsafe_for_binlog=1

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #2 address
wsrep_node_address=192.168.72.23

# Cluster name
wsrep_cluster_name=my_centos_cluster

# SST method
wsrep_sst_method=xtrabackup

#Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
wsrep_replicate_myisam=1


After saving the file run the following command
[root@percona2 ~]# /etc/init.d/mysql start


Step 7:-Checking the cluster status on node2
$mysql -u root -predhat
mysql> show status like 'wsrep%';
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | c2883338-834d-11e2-0800-03c9c68e41ec |
...
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
...
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
...
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+
40 rows in set (0.01 sec)


Step 8:-
Testing the replication

mysql@percona2> CREATE DATABASE percona;
Query OK, 1 row affected (0.01 sec)

Creating the example table on the second node:
mysql@percona3> USE percona;
Database changed

mysql@percona3> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Query OK, 0 rows affected (0.05 sec)

Inserting records on the first node:
mysql@percona1> INSERT INTO percona.example VALUES (1, 'percona1');
Query OK, 1 row affected (0.02 sec)

Retrieving all the rows from that table on the second node:
mysql@percona2> SELECT * FROM percona.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
|       1 | percona1  |
+---------+-----------+
1 row in set (0.00 sec)






Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.


Install Cassandra (Standalone) on CentOS 6x

Install Cassandra (Standalone) on CentOS 6x ==================================================

Step: 1. Install JAVA :

# cd /tmp
# wget --no-check-certificate --no-cookies --header 'Cookie: oraclelicense=accept-securebackup-cookie' http://download.oracle.com/otn-pub/java/jdk/8u5-b13/jdk-8u5-linux-x64.rpm
# yum -y install jdk-8u5-linux-x64.rpm
# export JAVA_HOME=/usr/java/jdk1.8.0_05
# export PATH=$PATH:$JAVA_HOME
# echo $JAVA_HOME
# vi /etc/profile.d/java.sh

#!/bin/bash
JAVA_HOME=/usr/java/jdk1.8.0_05
PATH=$JAVA_HOME/bin:$PATH
export PATH JAVA_HOME
export CLASSPATH=.

-- Save & Quit (:wq)

chmod +x /etc/profile.d/java.sh
source /etc/profile.d/java.sh
Step: 2. Install the Java Native Access (JNA) :

# yum -y install jna
Step: 3. Add a symbolic link to the Oracle Java SE Runtime Environment 7 installation :

alternatives --install /usr/bin/java java /usr/java/jdk1.8.0_05/bin/java 20000
Step: 4. Then use the alternatives command to verify that the Oracle Java SE Runtime Environment 8 is selected :

alternatives --config java
Selection Command
-----------------------------------------------
+ 1 /usr/lib/jvm/jre-1.7.0-openjdk.x86_64/bin/java
2 /usr/lib/jvm/jre-1.6.0-openjdk.x86_64/bin/java
3 /usr/java/jdk1.8.0_05/bin/java

Enter to keep the current selection[+], or type selection number: 3

Step: 5. Verify Java Version :

java -version
Step: 6. Add the DataStax Community Repository :

vi /etc/yum.repos.d/datastax.repo
[datastax]
name = DataStax Repo for Apache Cassandra
baseurl = http://rpm.datastax.com/community
enabled = 1
gpgcheck = 0

-- Save & Quit (:wq)

Step: 7. Install Apache Cassandra 2 :

yum -y install dsc20
Step: 8. Configure the Apache Cassandra 2 Environment :

export JAVA_HOME=/usr/java/jdk1.8.0_05/
export PATH=$PATH:/usr/java/jdk1.8.0_05/bin/
Step: 9. Get Cassandra Running :

service cassandra start
chkconfig cassandra on
Step: 10. Enter the Cassandra Command Line

cqlsh
The HELP command displays a synopsis & a brief description of all cqlsh commands. Given below is the usage of help command.

cqlsh> help

Documented shell commands: ===========================
CAPTURE COPY DESCRIBE EXPAND SHOW TRACING
CONSISTENCY DESC EXIT HELP SOURCE

CQL help topics: ================
ALTER CREATE_TABLE_OPTIONS SELECT
ALTER_ADD CREATE_TABLE_TYPES SELECT_COLUMNFAMILY
ALTER_ALTER CREATE_USER SELECT_EXPR
ALTER_DROP DELETE SELECT_LIMIT
ALTER_RENAME DELETE_COLUMNS SELECT_TABLE
ALTER_USER DELETE_USING SELECT_WHERE
ALTER_WITH DELETE_WHERE TEXT_OUTPUT
APPLY DROP TIMESTAMP_INPUT
ASCII_OUTPUT DROP_COLUMNFAMILY TIMESTAMP_OUTPUT
BEGIN DROP_INDEX TRUNCATE
BLOB_INPUT DROP_KEYSPACE TYPES
BOOLEAN_INPUT DROP_TABLE UPDATE
COMPOUND_PRIMARY_KEYS DROP_USER UPDATE_COUNTERS
CREATE GRANT UPDATE_SET
CREATE_COLUMNFAMILY INSERT UPDATE_USING
CREATE_COLUMNFAMILY_OPTIONS LIST UPDATE_WHERE
CREATE_COLUMNFAMILY_TYPES LIST_PERMISSIONS USE
CREATE_INDEX LIST_USERS UUID_INPUT
CREATE_KEYSPACE PERMISSIONS
CREATE_TABLE REVOKE

In Cassandra, a keyspace is a container for your application data. It is similar to the schema in a relational database.

cqlsh> desc keyspaces;

system system_traces

Step: 11. To create the keyspace "demo", at the CQL shell prompt, type :

cqlsh> create keyspace demo
WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };

cqlsh> desc keyspaces;

system system_traces demo

cqlsh> use demo;

cqlsh:demo>

Now we have we have a keyspace, we can create tables within that keyspace to store our data in. Tables, or column families,
consist of columns and rows.

Step: 12. Create a “users” table within the keyspace “demo” so that we can insert some data into our database :

cqlsh> USE demo;

cqlsh:demo> create table users ( firstname text,lastname text,age int,city text,primary key (lastname));

cqlsh:demo> DESC SCHEMA;

CREATE KEYSPACE demo WITH replication = {
'class': 'SimpleStrategy',
'replication_factor': '1'
};

USE demo;

CREATE TABLE users (
lastname text,
age int,
city text,
firstname text,
PRIMARY KEY ((lastname))
) WITH
bloom_filter_fp_chance=0.010000 AND
caching='KEYS_ONLY' AND
comment='' AND
dclocal_read_repair_chance=0.100000 AND
gc_grace_seconds=864000 AND
index_interval=128 AND
read_repair_chance=0.000000 AND
replicate_on_write='true' AND
populate_io_cache_on_flush='false' AND
default_time_to_live=0 AND
speculative_retry='99.0PERCENTILE' AND
memtable_flush_period_in_ms=0 AND
compaction={'class': 'SizeTieredCompactionStrategy'} AND
compression={'sstable_compression': 'LZ4Compressor'};

Step: 13. Insert some rows of Data into our newly created ‘users’ table :

Type ENTER after each statement to insert the row into the table:

cqlsh:demo> INSERT INTO users (firstname, lastname, age,city) values ('Soumya', 'Das', 30, 'Calcutta');
cqlsh:demo> INSERT INTO users (firstname, lastname, age,city) values ('udit', 'Gujar', 24, 'Pune');

Now that we have a few rows of data in our table, let’s perform some queries against it. Using a SELECT statement will let us take a peek inside our table. To see all the rows from the users table we’ve created, type

cqlsh:demo> select * from users;

lastname     | age | city    | firstname
--------------+-----+---------+-----------
   das          |  30 | Calcutta| soumya
   Gujar      |  24 |  Pune   | Udit
(2 rows)

cqlsh:demo> exit

Step: 14. Check Cassandra Node Status :

nodetool status
Step: 15. Shutdown Cassandra :

service cassandra stop
service cassandra status
Done...!!!




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Master-Slave replication on PostgreSQL on Rhel/Centos 6.5

Master server:-192.168.100.122
Hostname:-server2.soumya.com

Slave server:-192.168.100.175
Hostname:-server1.soumya.com

Postgresql Version:9.4-1
Linux version:Rhel 6.3


Install postgresql in both server:-
Step 1:-Download the repository
yum install http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm

# vi /etc/yum.repos.d/centos.repo
add the following lines
[centos-6-base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
enabled=1


step 2:- Install Postgresql required packages:-
# yum install postgresql94-server postgresql94-contrib
Incase if the above command gives error regarding the public key, we can use the following command
cd /etc/pki/rpm-gpg
rpm --import RPM-GPG-KEY-CentOS-6

#yum install postgresql94-server

# service postgresql-9.4 initdb

OR

# service postgresql initdb

# chkconfig postgresql-9.4 on

Step 3:-Start postgresql:-

[root@infosystem ~]# service postgresql-9.4 start
Starting postgresql service: [  OK  ]

Check status of postgresql:-
[root@infosystem ~]# service postgresql status
postmaster (pid  4260) is running...

Step 4:- Perform this in both server:-

PostgreSQL creates a user called "postgres" in order to handle its initial databases.
We will configure ssh access between our servers to make transferring files easier.

#passwd postgres
login to postgres user

#su - postgres

Generate an ssh key for the postgres user:(Do this is both server)
In Master server:-
$ ssh-keygen -t rsa
$ ssh-keygen -t dsa
$ cd /var/lib/pgsql/.ssh
$ cat id_rsa.pub >>authorized_keys
$ cat id_dsa.pub >>authorized_keys


In Slave server:-
$ ssh-keygen -t rsa
$ ssh-keygen -t dsa
$ cd /var/lib/pgsql/.ssh
$ cat id_rsa.pub >>authorized_keys
$ cat id_dsa.pub >>authorized_keys


In Master server:-
$ cd /var/lib/pgsql/.ssh
$ cp authorized_keys server2_authorized_keys
$ scp server2_authorized_keys postgres@server1:/var/lib/pgsql/
$ rm -rf authorized_keys

In slave server:-
$ cd /var/lib/pgsql/.ssh
$ cat server2_authorized_keys >>authorized_keys
$ scp authorized_keys postgres@server2:/var/lib/pgsql/

Now test the connectivity from both server.
From master server:-
ssh server1 date

From slave server:-
ssh server2 date

Step 5:-Configure the Master Server.

# su - postgres

$ psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'redhat';"

Next, we will move to the postgres configuration directory:
$ cd /var/lib/pgsql/9.4/data

At any place not at the bottom of the file, add a line to let the new user get access to this server:
$ vi pg_hba.conf

host    replication     rep     192.168.100.175/32   md5

:wq

Next, open the main postgres configuration file:

$ vi postgresql.conf

Find these parameters. Uncomment them if they are commented, and modify the values according to what we have listed below:

listen_addresses = 'localhost,192.168.100.122'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on
Save and close the file.


Restart the master server to implement your changes from root user:-
# service  postgresql-9.4 restart

Step 6.Configure the Slave Server.

Begin on the slave server by shutting down the postgres database software:

# service postgresql-9.4 stop


On the slave server, We then will transfer the database data from master server:-

$ cd /var/lib/pgsql/9.4/data
$ rm -rf *
$ pg_basebackup -D /var/lib/pgsql/9.4/data -h 192.168.100.122 -U rep

Now we can see all the files from the master server has been copied into the slave server.

We will be making some similar configuration changes to postgres files,
so change to the configuration directory:

# su - postgres
$ cd /var/lib/pgsql/9.4/data


Adjust the access file to allow the other server to connect to this.
This is in case we need to turn the slave into the master later on down the road.

$ vi pg_hba.conf

Again, add this line somewhere not at the end of the file:

host    replication     rep     192.168.100.122/32  md5

Save and close the file.

Next, open the postgres configuration file:

$ vi postgresql.conf

You can use the same configuration options you set for the master server, modifying only the IP address
to reflect the slave server's address:

listen_addresses = 'localhost,192.168.100.175'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on


--Save and exit.

Step 7.Create recovery.conf file

Here, we need to create a recovery file called recovery.conf:
$ cd /var/lib/pgsql/9.4/data
$ vi recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=192.168.100.122 port=5432 user=rep password=redhat'
trigger_file = '/tmp/postgresql.trigger.5432'

--save and exit.

Test the replication:-
On Master server:-
$ su - postgres
$psql
#CREATE TABLE rep_test (test varchar(40));
Now, we can insert some values into the table with the following commands:

INSERT INTO rep_test VALUES ('data one');
INSERT INTO rep_test VALUES ('some more words');
INSERT INTO rep_test VALUES ('lalala');
INSERT INTO rep_test VALUES ('hello there');
INSERT INTO rep_test VALUES ('blahblah');
To exit from psql shell
\q

Now on slave server:-
$ su - postgres
$psql
#SELECT * FROM rep_test;

      test    
-----------------
 data one
 some more words
 lalala
 hello there
 blahblah
(5 rows)

Now lets see if try insert data from slave server :-

#INSERT INTO rep_test VALUES ('oops');
ERROR:  cannot execute INSERT in a read-only transaction

As we can see, we are unable to insert data into the slave. This is because the data is only being
transferred in one direction. In order to keep the databases consistent, postgres must make the slave
read-only.




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Duplicate a database in a new host/server where only incremental level 0 backup is available in oracle 11g

Oracle Version :-11g
Target Database : prim
Target Database Server : server1.soumya.com
IP: 192.168.2.102

Oracle Version :-11g
Auxiliary Database : stand
Auxiliary Database Server : server3.soumya.com
IP:192.168.2.12

Step 1:
Take the incremental level 0 backup of the Target database using RMAN.
In my case, I had the backup of my target database (prim) taken at the location '/u01/bkp'
[oracle@server1 bkp]$ pwd
/u01/bkp

[oracle@server1 bkp]$ ls -ltrh
total 1.1G
-rw-r-----. 1 oracle oinstall 9.4M Feb 17 15:06 o1_mf_ncsnf_TAG20150217T150631_bg62x0o8_.bkp
-rw-r-----. 1 oracle oinstall  13M Feb 17 15:08 o1_mf_annnn_TAG20150217T150811_bg63043x_.bkp
-rw-r-----. 1 oracle oinstall 1.1G Feb 17 15:09 o1_mf_nnndf_TAG20150217T150813_bg6305j3_.bkp
-rw-r-----. 1 oracle oinstall 9.4M Feb 17 15:09 o1_mf_ncsnf_TAG20150217T150813_bg632lxo_.bkp
-rw-r-----. 1 oracle oinstall 8.0K Feb 17 15:09 o1_mf_annnn_TAG20150217T150932_bg632nml_.bkp


Step 2:
Copy these backup pieces from the Target server (location /u01/bkp) to the auxiliary server (location /u01/bkp)
Also, copy the pfile (initprim.ora) of the Target database to the Auxiliary server.

[oracle@server1 ]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@server1 ]$ cp initprim.ora /u01/bkp/

[oracle@server1 $ scp -r *.* oracle@192.168.2.12:/u01/

oracle@192.168.2.12's password:
initprim.ora  100% 912Kb 0.9KB/s  00:00    100% 911KB 9.0MB/s  00.00
o1_mf_ncsnf_TAG20150217T150631_bg62x0o8_.bkp                                                                  100% 9600KB   9.4MB/s   00:00  
o1_mf_annnn_TAG20150217T150932_bg632nml_.bkp                                                                  100% 8192     8.0KB/s   00:00  
o1_mf_nnndf_TAG20150217T150813_bg6305j3_.bkp                                                                  100% 1060MB  20.0MB/s   00:53  
o1_mf_annnn_TAG20150217T150811_bg63043x_.bkp                                                                  100%   12MB  12.2MB/s   00:00  
o1_mf_ncsnf_TAG20150217T150813_bg632lxo_.bkp                                                                  100% 9600KB   9.4MB/s   00:00  

Step 3:

On the Auxiliary server, edit the pfile that was copied earlier to the desired entries (dump locations, control file location, datafile locations,
if using ASM then specify the desired disk group) and rename it to the desired instance name file (init<SID>.ora). Below is the sample I had it done.

vi initprim.ora

stand.__db_cache_size=16777216
stand.__java_pool_size=20971520
stand.__large_pool_size=4194304
stand.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stand.__pga_aggregate_target=142606336
stand.__sga_target=272629760
stand.__shared_io_pool_size=0
stand.__shared_pool_size=213909504
stand.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/stand/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/stand/control01.ctl','/u01/app/oracle/flash_recovery_area/stand/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='stand'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.memory_target=415236096
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/u01/app/oracle/oradata/prim/','/u01/app/oracle/oradata/stand/'
*.log_file_name_convert='/u01/app/oracle/oradata/prim/','/u01/app/oracle/oradata/stand/'

:wq

Make sure db_file_name_convert and log_file_name_convert parameters are present in pfile else it would
throw error while creating auxiliary database .
Once changes are done rename the pfile with your instance sid and copy it inside ORACLE_HOME/dbs
[oracle@server3] mv initprim.ora initstand.ora
[oracle@server3] cp initstand.ora $ORACLE_HOME/dbs

Step 4:

Create a password file for the Auxiliary Database using the ORAPWD utility.

orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstand password=redhat


Step 5:

Start the auxiliary instance using the modified by pfile(initstand.ora)

[oracle@uat ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@uat ~]$ export ORACLE_SID=stand

Keep in mind that the auxiliary instance doesnt have any instance running on, only oracle binaries are installed on this.

Step 6 :-
Create the required directory structures are mentioned in pfile.
oracle@server3 ~]$ mkdir -p /u01/app/oracle/admin/stand/adump
[oracle@server3 ~]$ mkdir -p /u01/app/oracle/oradata/stand/
[oracle@server3  ]$ mkdir -p /u01/app/oracle/flash_recovery_area/stand/

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora'
ORACLE instance started.
Total System Global Area  754974720 bytes
Fixed Size                  2928968 bytes
Variable Size             637537976 bytes
Database Buffers          109051904 bytes
Redo Buffers                5455872 bytes


Step 6:

Connect the auxiliary instance through RMAN and start the duplication.
The duplication is done by specifying the location of the backup pieces. The command to be used is
DUPLICATE DATABASE TO '<auxiliary dbname>' BACKUP LOCATION '<location of the backup pieces on the auxiliary server>'

[oracle@server3 dbs]$ rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Jul 11 18:04:20 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: stand (not mounted)

RMAN> duplicate database to 'stand' backup location '/u01/bkp' NOFILENAMECHECK;

Starting Duplicate Db at 08-JAN-15

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     413372416 bytes

Fixed Size                     2213896 bytes
Variable Size                394266616 bytes
Database Buffers              12582912 bytes
Redo Buffers                   4308992 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PRIM'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''STAND'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/2015_02_17/o1_mf_ncsnf_TAG20150217T150813_bg632lxo_.bkp';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PRIM'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''STAND'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     413372416 bytes

Fixed Size                     2213896 bytes
Variable Size                394266616 bytes
Database Buffers              12582912 bytes
Redo Buffers                   4308992 bytes

Starting restore at 08-JAN-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/stand/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/stand/control02.ctl
Finished restore at 08-JAN-15

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   set until scn  58614791;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/stand/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/stand/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/stand/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/stand/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/stand/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 08-JAN-15
using channel ORA_AUX_DISK_1

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 /u01/app/oracle/oradata/stand/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/stand/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/stand/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/stand/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/stand/example01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/2015_02_17/o1_mf_nnndf_TAG20150217T150813_bg6305j3_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/2015_02_17/o1_mf_nnndf_TAG20150217T150813_bg6305j3_.bkp tag=TAG20150217T150813
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:07
Finished restore at 08-JAN-15

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf

contents of Memory Script:
{
   set until scn  58614791;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 08-JAN-15
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_AUX_DISK_1: reading from backup piece /u01/2015_02_17/o1_mf_annnn_TAG20150217T150932_bg632nml_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/2015_02_17/o1_mf_annnn_TAG20150217T150932_bg632nml_.bkp tag=TAG20150217T150932
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/flash_recovery_area/STAND/archivelog/2015_01_08/o1_mf_1_16_bbxko71g_.arc thread=1 sequence=16
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/STAND/archivelog/2015_01_08/o1_mf_1_16_bbxko71g_.arc RECID=1 STAMP=868491263
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-JAN-15

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''STAND'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     413372416 bytes

Fixed Size                     2213896 bytes
Variable Size                394266616 bytes
Database Buffers              12582912 bytes
Redo Buffers                   4308992 bytes

sql statement: alter system set  db_name =  ''STAND'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     413372416 bytes

Fixed Size                     2213896 bytes
Variable Size                394266616 bytes
Database Buffers              12582912 bytes
Redo Buffers                   4308992 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "STAND" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/stand/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/stand/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/stand/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/stand/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/stand/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/stand/sysaux01.dbf",
 "/u01/app/oracle/oradata/stand/undotbs01.dbf",
 "/u01/app/oracle/oradata/stand/users01.dbf",
 "/u01/app/oracle/oradata/stand/example01.dbf",
 "/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf",
 "/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/stand/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/sysaux01.dbf RECID=1 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/undotbs01.dbf RECID=2 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/users01.dbf RECID=3 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/example01.dbf RECID=4 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf RECID=5 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf RECID=6 STAMP=868491274

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 08-JAN-15

RMAN>

SQL> select name from v$database;

NAME
---------
STAND

Done..!!!




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.