Step By Step - (Sync)How To Setup Physical Steaming Replication in Sync Mode in PostgreSQL

  Step By Step – (Sync)Physical Streaming Replication(Sync) in PostgreSQL

Here I am using two nodes and I have named them as Master and Replica respectively.

To setup physical streaming in Sync mode first we have to configure it in Async mode and then we will convert it into Sync mode.

Step 1 - Initialize  a database cluster on master node using initdb utility. I have initialized a cluster named primary on master node.

Step 2 – Change the port number in postgresql.conf file and start it. I have used the port 5448.

 

 Step 3 – Start the cluster using pg_ctl cluster utility.

 

Step 4 - Prepare the Master Server- Now we have to prepare the master server for physical            replication. For this we need to change below parameters on postgresql.conf of primary db cluster.

1.     Primary db cluster must be in archive log mode so the parameters should be set as below

1.     Wal_level=replica

2.     Archive_mode=on

3.     Archive_command= cp %p <arch_dir>/%f  where arch_dir is the archvie location directory which we have set.

4.     Max_wal_sender=3 or more

5.     Listen_address=’*’ for connection from any user.

Note – After changing the parameters we need to restart the cluster to take into effect.

 

[postgres@Master ~]$ pg_ctl -D primary restart

After restart my parameter setting looks like below  –

 

Step 5 – Create a user with replication privilege or user super user

        

Step 6 – Create a Physical replication slot

           postgres=# select * from pg_create_physical_replication_slot('standby1');

Step 7 – Add entries into pg_hba.conf: Here you have add entries into 2 sections  -

1)# IPv4 local connections – In this section you need to add entries for your standby server(s).

2) # replication privilege – In this section you have to add entries for your replication user(s).

Example – I have added entries which are highlighted in blues lines.

 Note – Restart the cluster after changing the pg_hba.conf file to take it into effect.

Step 8 – Take a bp_basebackup – We can take pg_basebackup from primary and also from standby node. If we are taking backup on primary node then we have to transer this backup to standby node using SCP or any other file transfer method.

In my case I am taking pg_basebackup from standby node. My standby node name is Replica.

Note – Before taking backup make sure you have same archive directory present in the standby node also. In my Master node archive directory is arch_dir so I have created the same directory arch_dir in the standby node also.

I have used below command to take pg_basesbackup from standby server-

[postgres@Replica ~]$ pg_basebackup -h 192.168.204.133 -U postgres -p 5448 -D /home/postgres/replica1 -P -Xs –R

Here I have used used to options in the pg_basebackup –

-R – This option is used to create the standby.signal file on the standby database.

-Xs – This option is used to include the WAL files with sepcified method. Here I have used s which means it will be used for streaming.

Once backup is successful we can see all the required directories and configuration files are created under /home/postgres/replica1 directory.

Step 9 – Prepare Standby Server :   Open postgresql.conf on the standby server and modify the below parameters –

1.     Port – we need to change the port if we are running the primary and standby in the same node. But in my case I have used different machines so I am keeping the same port number as 5448.

2.     Hot_standby=on.  By default it is off and it will not allow queries to run into read only mode. It will just be used for high availability.

3.     Primary_conninfo= 'user=repl password=welcome host=192.168.204.133 port=5448'. Here we have to specify through which user we are going to connect to the primary host. Also we have to mention primary hostname and port number which we will be connecting.

4.     primary_slot_name = 'standby1'

5.     recovery_target_timeline = 'latest'

Save the postgresql.conf and start the standby cluster.

[postgres@Replica ~]$ pg_ctl -D replica1 start

Once it is started we can see the walreceiver process on the standby server –

Also we can verify in the log file 

We can also verify from primary server by querying pg_stat_replication view –

Step 10 – Prepare Standby Server 

Add below parameter in standby server –

-          Add application name in the primary_conninfo of standby server.

-           

primary_conninfo = 'user=repl password=welcome host=192.168.204.133 port=5448 application_name=replica1'

Step 11 – Prepare Primary Server  -

Add below parameters in the primary server :

1.       Synchronous_commit=on

2.       Synchronous_standby_names= replica1

Note – Sometime there is problem with Synchronous_standby_names which simply does not take the value of application name so in that case we use –

             synchronous_standby_names = '*'                               

After making the changes just restart Primary and standby servers.

                    ======================THE END=============================

Step By Step - (Async)How To Setup Physical Steaming Replication in Async Mode in PostgreSQL

 

Step By Step – (Async)Physical Streaming Replication(Async) in PostgreSQL

Here I am using two nodes and I have named them as Master and Replica respectively.

Step 1 - Initialize  a database cluster on master node using initdb utility. I have initialized a cluster named primary on master node.

Step 2 – Change the port number in postgresql.conf file and start it. I have used the port 5448.

 

 Step 3 – Start the cluster using pg_ctl cluster utility.

 

Step 4 - Prepare the Master Server- Now we have to prepare the master server for physical            replication. For this we need to change below parameters on postgresql.conf of primary db cluster.

1.     Primary db cluster must be in archive log mode so the parameters should be set as below

1.     Wal_level=replica

2.     Archive_mode=on

3.     Archive_command= cp %p <arch_dir>/%f  where arch_dir is the archvie location directory which we have set.

4.     Max_wal_sender=3 or more

5.     Listen_address=’*’ for connection from any user.

Note – After changing the parameters we need to restart the cluster to take into effect.

 

[postgres@Master ~]$ pg_ctl -D primary restart

After restart my parameter setting looks like below  –

 

Step 5 – Create a user with replication privilege or user super user

        

Step 6 – Create a Physical replication slot

           postgres=# select * from pg_create_physical_replication_slot('standby1');

Step 7 – Add entries into pg_hba.conf: Here you have add entries into 2 sections  -

1)# IPv4 local connections – In this section you need to add entries for your standby server(s).

2) # replication privilege – In this section you have to add entries for your replication user(s).

Example – I have added entries which are highlighted in blues lines.

 Note – Restart the cluster after changing the pg_hba.conf file to take it into effect.

Step 8 – Take a bp_basebackup – We can take pg_basebackup from primary and also from standby node. If we are taking backup on primary node then we have to transer this backup to standby node using SCP or any other file transfer method.

In my case I am taking pg_basebackup from standby node. My standby node name is Replica.

Note – Before taking backup make sure you have same archive directory present in the standby node also. In my Master node archive directory is arch_dir so I have created the same directory arch_dir in the standby node also.

I have used below command to take pg_basesbackup from standby server-

[postgres@Replica ~]$ pg_basebackup -h 192.168.204.133 -U postgres -p 5448 -D /home/postgres/replica1 -P -Xs –R

Here I have used used to options in the pg_basebackup –

-R – This option is used to create the standby.signal file on the standby database.

-Xs – This option is used to include the WAL files with sepcified method. Here I have used s which means it will be used for streaming.

Once backup is successful we can see all the required directories and configuration files are created under /home/postgres/replica1 directory.

Step 9 – Prepare Standby Server :   Open postgresql.conf on the standby server and modify the below parameters –

1.     Port – we need to change the port if we are running the primary and standby in the same node. But in my case I have used different machines so I am keeping the same port number as 5448.

2.     Hot_standby=on.  By default it is off and it will not allow queries to run into read only mode. It will just be used for high availability.

3.     Primary_conninfo= 'user=repl password=welcome host=192.168.204.133 port=5448'. Here we have to specify through which user we are going to connect to the primary host. Also we have to mention primary hostname and port number which we will be connecting.

4.     primary_slot_name = 'standby1'

5.     recovery_target_timeline = 'latest'

Save the postgresql.conf and start the standby cluster.

[postgres@Replica ~]$ pg_ctl -D replica1 start

Once it is started we can see the walreceiver process on the standby server –

Also we can verify in the log file 

We can also verify from primary server by querying pg_stat_replication view –

                                 ======================The END=============================

Step by step - ASM Architecture, ASM Instance, ASM Diskgroups, ASM Disks

In the video we can learn below points in detail:

ASM Architecture
ASM instance
parameters in the AMS parameter file
understaning of ASM_POWER_LIMIT and disk Rebalancing
understanding the ASM_Diskstring parameter
ASM_diskgroups and its it redundency levels
Status of ASM disks
startup sequecne of an ASM Instance

Step by step - RAID, ASM Introduction, differences and its Benefit

This is the best video I have seen on ASM so just sharing it. Explanation is very good.

Session - 1: In this session we can learn what are the different RAID levels and how it works.
                    It also explains what are the benefits of ASM and its different redundancy levels.

Step by step - How to resolve ORA-01555 from the EXPDP

Scenario - we have around 2 TB of database and daily export job is scheduled to run at 12 AM EST. But it is failing with the below error. There are multiple occurrence of this error. I am just putting out one to show the exact error.

ORA-31693: Table data object "RDR"."REGW_EXTRACT" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 10 with nam"_SYSSMU10_3196945471$" too small 

So we need to heck the few things:

1.      Check the total duration of the export job.

2.      Find the undo retention of the database.

3.      Find the undo table space size

4.      Find the job running timing

Solution:

1.      Change the undo_retention to the total time the export is taking. But remember to add atlease 30 mins more. Suppose export is taking 6 hours then keep the undo_retention to atleast 6:30 hours. Mostly it will resolve the issue.

2.      Also make sure we have enough space on the undo table space.

3.      Also make sure to run the export in non-business hours.

WARNING: Subscription for node down event still pending

SYMPTOMS

We are receiving the following warning messages in the listener log file constantly:

'WARNING: Subscription for node down event still pending'

CHANGES

This may be a new installation or a recent upgrade to 11g or newer.

CAUSE

These messages are related to the Oracle TNS Listener's default subscription to the Oracle Notification Service (ONS). In a non-RAC environment it is recommended to disable this subscription.   This feature was introduced in Oracle 10g.

SOLUTION

Set the following parameter in the listener.ora:

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF

Where <listener_name> should be replaced with the actual listener name configured in the

LISTENER.ORA file.

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name> parameter is to be placed by itself on an empty line.

It will be necessary to restart or reload the listener following the addition of this parameter.

This will prevent the messages from being written to the log file and may also prevent the TNS

Please Note: Setting SUBSCRIBE_FOR_NODE_DOWN_<listener_name> to OFF disables a necessary RAC functionality. The above workaround is recommended only for non-RAC environments.

The issue may be present in all 11g and newer installations.

Step by step - Rolling Forward a Physical Standby Database using RMAN Incremental Backup

1) Stop the managed recovery process (MRP) on the STANDBY database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Note:  For the remaining steps, the standby database must be in a MOUNT state.  

2) Determine the SCN of the STANDBY database.

On the standby database, find the SCN which will be used for the incremental backup at the primary database:

You need to use the 'lowest SCN' from the queries below:

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
--------------
3164433


SQL> select min(checkpoint_change#) from v$datafile_header
where file# not in (select file# from v$datafile where enabled = 'READ ONLY');

MIN(F.FHSCN)
----------------
3162298

You need to use the 'lowest SCN' from the queries, in this example is SCN: 3162298.  Therefore, From the above you need to backup from SCN 3162298

3) Take an incremental backup of the PRIMARY database

In RMAN, connect to the primary database and create an incremental backup from the SCN derived in the previous step:

RMAN> BACKUP INCREMENTAL FROM SCN 3162298 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';

4) Transfer all backup sets to STANDBY server

All backups created by step #3 must be transferred from the primary to the standby system.  If the backup is written to NFS device, this step can be skipped.

  

scp /tmp/ForStandby_* standby:/tmp

5) Catalog the backups in STANDBY controlfile.

In order for the standby database to know about the backups, catalog the backup pieces on the STANDBY database. 

RMAN> CATALOG START WITH '/tmp/ForStandby';

using target database control file instead of recovery catalog

searching for all files that match the pattern /tmp/ForStandby

List of Files Unknown to the Database

=====================================

File Name: /tmp/ForStandby_2lkglss4_1_1

File Name: /tmp/ForStandby_2mkglst8_1_1

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /tmp/ForStandby_2lkglss4_1_1

File Name: /tmp/ForStandby_2mkglst8_1_1

6) Recover the STANDBY database with the cataloged incremental backup:

RMAN> RECOVER DATABASE NOREDO;

starting recover at 03-JUN-09

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=28 devtype=DISK

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: +DATA/mystd/datafile/system.297.688213333

destination for restore of datafile 00002: +DATA/mystd/datafile/undotbs1.268.688213335

destination for restore of datafile 00003: +DATA/mystd/datafile/sysaux.267.688213333

channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_2lkglss4_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/tmp/ForStandby_2lkglss4_1_1 tag=FORSTANDBY

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

Finished recover at 03-JUN-09

7) In RMAN, connect to the PRIMARY database and create a standby control file backup:

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';

8) Copy the standby control file backup to the STANDBY system. 

scp /tmp/ForStandbyCTRL.bck standby:/tmp

9) Capture datafile information in STANDBY database.

We now need to refresh the standby controlfile from primary controlfile (for standby) backup. However, since the datafile names are likely different than primary, let's save the name of datafiles on standby first, which we can refer after restoring controlfile from primary backup to verify if there is any discrepancy. Run below query from Standby and save results for further use.

spool datafile_names_step8.txt

set lines 200

col name format a60

select file#, name from v$datafile order by file# ;

spool off

10) From RMAN, connect to STANDBY database and restore the standby control file:

RMAN> SHUTDOWN IMMEDIATE ;

RMAN> STARTUP NOMOUNT;

RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';

Starting restore at 03-JUN-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

output filename=+DATA/mystd/controlfile/current.257.688583989

Finished restore at 03-JUN-09

11) Shut down the STANDBY database and startup mount:

RMAN> SHUTDOWN;

RMAN> STARTUP MOUNT;

12) Catalog datafiles in STANDBY if location/name of datafiles is different

Note:  We recommend checking the incarnation for primary and standby before completing this step.

example:  RMAN> list incarnation;

Since the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, if the directory structure is different between the standby and primary or you are using Oracle managed file names, catalog the datafiles in STANDBY will do the necessary rename operations.  If the primary and standby have identical structure and datafile names, this step can be skipped.

Perform the below step  in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.

RMAN> CATALOG START WITH '+DATA/mystd/datafile/';

List of Files Unknown to the Database

=====================================

File Name: +data/mystd/DATAFILE/SYSTEM.309.685535773

File Name: +data/mystd/DATAFILE/SYSAUX.301.685535773

File Name: +data/mystd/DATAFILE/UNDOTBS1.302.685535775

File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333

File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333

File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333

File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333

File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335

If any datafiles have been added to Primary AFTER scn 3162298 they will also have to be restored to the standby host (see Note 1531031.1 Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary) and cataloged as shown above before doing the switch.   

To determine if any files have been added to Primary since the standby current scn:

SQL>SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 3162298

If the above query returns with 0 zero rows, you can switch the datafiles. This will rename the datafiles to its correct name at the standby site:

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/mystd/datafile/system.297.688213333"

datafile 2 switched to datafile copy "+DATA/mystd/datafile/undotbs1.268.688213335"

datafile 3 switched to datafile copy "+DATA/mystd/datafile/sysaux.267.688213333"

Note:

a) At this point, you can compare the query output from step 9 for any discrepancy (other than newly added datafiles) to ensure we have all the datafiles added in standby.

b) run the queries from step 2 again to confirm that the incremental apply has moved the datafiles forward. The SCN should now be bigger than its initial values.

13) Configure the STANDBY database to use flashback (optional)

If the STANDBY database needs to be configured for FLASHBACK run the following commands:

SQL> ALTER DATABASE FLASHBACK OFF;

SQL> ALTER DATABASE FLASHBACK ON;

NOTE:  This command may return the following:

ORA-38788 More standby database recovery is needed

Depending on situation, this is expected.  Proceed with the remaining steps.

14) On STANDBY database, clear all standby redo log groups:

SQL> select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

....

15) On the STANDBY database, start the MRP

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;