Wednesday 25 March 2020

pg_restore with -C option fails to create database. FATAL: database "nexaprod" does not exist

When you use pg_restore you have various options available, for example : 

-c to drop database objects before recreating them,
-C to create a database before restoring into it,

Thursday 19 March 2020

initialise multiple postgres instance on same RHEL server


It is pretty easy to install multiple instances of PostgreSQL servers on same server and have its benefits.

Who uses Postgresql

A good read Who uses Postgresql 

Tuesday 10 March 2020

How to stop starting of clusters while starting up services


Infra activities such as upgrade, OS patching or Sevrer maintaince activity may require disabling the auto startup of any specific cluster or if you want  NOT auto start on a call such as sudo service postgresql start

pgtune | installation and usage

 pgtunepgtune helps expands the database server to be as powerful as the hardware it's being deployed on , by taking an existing postgresql.conf file as an input, making changes to it based on the amount of RAM in your server and suggested workload, and output a new file.

Monday 9 March 2020

pg_basebackup: could not connect to server: FATAL: number of requested standby connections exceeds



-bash-4.1$ pg_basebackup -h oracleasm1.localdomain -D /u01/PG_Adventure
pg_basebackup: could not connect to server: FATAL:  number of requested standby connections exceeds  

pg_basebackup : could not connect to server: FATAL: no pg_hba.conf entry for replication connection


pg_basebackup: could not connect to server: FATAL:  no pg_hba.conf entry for replication connection from host "127.0.0.1", user "postgres", SSL off

Monday 21 October 2019

Postgres Drop table : ERROR: must be owner of relation table_name


In Postgres, The right to drop an object is not treated with any grantable privilege. It is only achived by the ownership. How .. ?

Thursday 29 August 2019

Creating a database on Postgresql server


1.    Login to PostgreSQL server

[oracle@oracleasm1 ~]$ su - postgres
Password:
-bash-4.1$
 
-bash-4.1$ psql
psql (8.4.18, server 9.6.15)
WARNING: psql version 8.4, server version 9.6.
         Some psql features might not work.
Type "help" for help.

Installating PostgreSQL Server on CentOS, RHEL 6 / 7 Systems


This article will help you for installing PostgreSQL on CentOS, RHEL 6 / 7 Systems

Tuesday 2 October 2018

RMAN-08591: WARNING: invalid archived log deletion policy


For one of database we were getting backup failure alerts where the backup was configured on standby database.

We saw below Error messsages in backup-log :


RMAN-08591: WARNING: invalid archived log deletion policy


-

  
Cause: If archive log location is set to FRA, then there might be a chance of the deleting the archives automatically when the space pressure in FRA.
In that case at least one of the destination standby must be set as a "MANDATORY" destination.

Solution: To eliminate the RMAN warning message, at least one archive destination must be set as a mandatory destination.

Steps : 

1. Log in to the broker command line utility


[oracle@host1 log]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration

Configuration  primdb1
  Protection Mode: MaxPerformance
  Members:

  primdb1 - Primary database
    bcpdb1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 7 seconds ago)


2. For the standby site check the "Binding" property


DGMGRL> show database verbose bcpdb1

Database - bcpdb1

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      11 minutes (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 625.00 KByte/s
  Active Apply Rate:  523.00 KByte/s
  Maximum Apply Rate: 18.62 MByte/s
  Real Time Query:    ON
  Instance(s):
    bcpdb1
Properties:
----------
----------
Binding = 'optional' ---------- ----------




3. Set the "binding" Property as - MANDATORY
        
//This parameter controls whether the destination is mandatory or not

DGMGRL> edit database bcpdb1 set property Binding='mandatory';

Property "binding" updated

DGMGRL> exit


Once the broker configuration has set a standby site as a mandatory destination, the RMAN configuration can be altered to set the archivelog deletion policy to applied on standby.


RMAN> configure archivelog deletion policy to applied on standby;

Once the broker configuration has set a standby site as a mandatory destination, then RMAN will not report this error again.


Reference: Data Guard Physical Standby - RMAN configure archivelog deletion policy reports RMAN-08591 (Doc ID 1984064.1)

-

Found this post interesting? Subscribe us  😊😉

Read more 

Configure Streaming Replication in PostgreSQL

Installation and configure Postgres 13 on RHEL

All about Physical Replication and Log shipping in Postgres 

Possible ways to recover space from deleted rows with insufficient disk space

Streaming-Replication Sync and Async, benefits of streaming replication over Log-shipping


“Be like a tree. Stay grounded. Connect with your roots. Turn over a new leaf. Bend before you break. Enjoy your unique beauty. Keep growing.” -- Joanne Rapits.

Query to find the session creating more redo

How to find the session generating more number of Redo or we can say Archivelogs as well.
Using the view v$sess_io, we can find out the sessions which are performing the block_changes in database. From this we can determine the sessions.

Below is the query we can use to find the Session IDs

set pages 1000 lines 1000
SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc;


Monday 13 August 2018

Linux Important Files and Description



/etc/passwd - User Settings

/etc/group - Group settings for users.

/etc/hosts -  Host name lookup information

/etc/sysctl.conf  - Kernel parameters for Linux.

/etc/fstab  - Files to check for File System entries

/etc/oratab  - Oracle Registered instance (DBCA)

/etc/redhat- release - get the OS release information

/etc/selinux/config  - Enable or disable security feature.

/proc/meminfo  - To determine To determine the RAM size

/var/log/messages  - Check System and error logs and messages

/home/oracle/.bash_profile  - Oracle user profile setting file in Linux.

/etc/security/limits.conf  - Specify process and open files related limits



-

Attack every problem with enthusiasm... as if your survival depends upon it



ASM Interview Questions | PDF Download

How to Crack DBA Interviews | Tips 

Frequently Asked Questions for 2+ Years

30+ Oracle Dataguard Questions | PDF Download

Sunday 12 August 2018

Troubleshooting Internal Errors and Error-Look-up Tool on MOS




If you’re an Oracle DBA, you are likely to have come across an error message in your Oracle Database alert.log files prefixed by either ORA-600 or ORA-7445. Let’s see when, how and why does these appears.

Example: One of the ORA 600 could look like as below

ORA 600 [ktfbtgex-7], [1015817] ORA 600 [3600]
ORA 7445 [kewa_dump_time_diff()+157]

The internal error messages include no attached explanation in the way that other  error messages do (for example ORA-01017: invalid username/password" while connecting ), it is difficult to assess the seriousness of the error and whether it is cause for concern.

ORA-600 is a message that indicates an error internal to the database code. Oracle Database performs checks to confirm that the information being used in internal processing is healthy,  the variables being used for codes are within a valid range, that changes are being made to a consistent structure, and that a change won’t put a structure into an unstable state. If a check fails, Oracle Database signals an ORA-600 error and, if necessary, terminates the operation to protect the health of the database.
The first argument to the ORA-600 error message indicates the location in the code where the check is performed.The subsequent arguments have different meanings, depending on the particular check.

Whereas, An ORA-7445 error, on the other hand, traps a notification the operating system has sent to a process and returns that notification to the user. Unlike the ORA-600 error, the ORA-7445 error is an unexpected failure rather than a handled failure.
Both ORA-600 and ORA-7445 errors will Write the error message to the alert.log, along with details about the location of a trace containing further information.

Assessing and resolving ORA-600 and ORA-7445 errors

ORA-600 and ORA-7445 errors, you can either identify the cause and resolve the error on your own or find ways to avoid the error. The information provided in this section will help you resolve or work around some of the more common errors.

Example 1: ORA-600 [729]. The first argument to this ORA-600 error message, 729, indicates a memory-handling issue. The error message text will always include the words space leak, but the number after 729 will vary:
ORA-00600: internal error code, arguments: [729], [800], [space leak], [], [],
The number in the second set of brackets (800) is the number of bytes of memory discovered. A space leak occurs when some code doesn't completely release the memory it used while executing. In this example, when that process disconnected from the database, it discovered that some memory was not cleaned up at some point during its life and reported ORA-600 [729].  You cannot determine the cause of the space leak by checking your application code, because the error is internal to Oracle Database.  OR you can enable the tracing in database to investigate the issue

e.g.
SQL>alter system set events '10262 trace name context forever, level xxxx' scope=spfile;
Note : You will also need to shut the database down and restart it to enable the event to take effect.

Replace xxxx with a number greater than the value in the second set of brackets in the ORA-600 [729] error message. In the example above, you could set the number to 1000, in which case the event instructs the database to ignore all user space leaks that are smaller than 1000 bytes.

Example 2 :

ORA-00600: internal error code, arguments: [3020], [117], [125002], [352446538], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 117, block# 125002, file offset is 1024016384 bytes)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 02: '+DATA01/myprod_host129 /datafile/sysaux_01.422.914282521'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 119838Incident details in: /app/ora/local/admin/myprod/diag/rdbms/myprod_host129/myprod/incident/incdir_63521/myprod_mrp0_2616275_i63521.trc

This error tells that there is issue with datafile #02. It could be corruption on file system or a block corruption on DB level. How I solved this one, Check this post.


Example 3 :

ORA-600 [6033]. This error is reported with no additional arguments, as shown in the following alert.log file describes :
bdump/prod_ora_4345618.trc:
ORA-600: internal error code, arguments: [6033], [], [], [], [], [], [], []

The ORA-600 [6033] error often indicates an index corruption. To identify the affected index, you’ll need to look at the associated trace file whose name is provided in the alert.log file, just above the error message. In this alert.log excerpt, the trace file you need to look at is called prod_ora_4345618.trc which  is located in  /bdump directory. There are two possible ways to identify the table on which the affected index is built: Look for the SQL statement that was executing at the time of the error. This statement should appear at the top of the trace file, under the heading “Current SQL Statement.” The affected index will belong to one of the tables accessed by that statement.


Example 4 :

ERROR at line 1:
ORA-00600: internal error code, arguments: [kfgpCreate_60], [10], [2], [65535], [65535], [65535], [65535], [], [], [], [], []

This will issue appear while dropping disk in ASM.
As per MOS it’s a bug (metalink doc  2031394.1). Here is the post how I solved it


 Oracle Database users with Oracle support introduces support for the ORA-600/ORA-7445 lookup tool Knowledge on this Article 153788.1 it enables you to enter the first argument to an ORA-600 or ORA-7445 error message and use that information to identify known defects, workarounds, and other knowledge targeted specifically to that error/argument combination.