Recover standby database using RMAN Incremental backup

1. Get current SCN from standby database 

sql > select current_scn from v$database;

2. Take rman backup and backup of controlfile from primary 

RMAN> backup incremental from scn 12252407093884 database format '/u03/rman_backup/inc_backup_%U';
RMAN> backup current controlfile for standby format '/u03/rman_backup/stnd_%U.ctl';

3. copy all backup pieces to standby and catalog it

RMAN> catalog start with '/u03/rman_backup/';

4. Restore standby database 

RMAN> recover database noredo;

5. shut down database, start in nomount mode and restore standby controlfile. restart db in mount mode

RMAN> shutdown immediate
RMAN> startup nomount
RMAN> restore standby controlfile from '/u03/rman_backup/stnd_12n6p3qt_1_1.ctl';

RMAN> <strong>shutdown immediate
RMAN> startup mount

6. if standby and primary database datafile location is diffrant then catalog standby datafiles and switch to datafile. 

RMAN> catalog start with '+DATA/SSSB/DATAFILE';
RMAN> switch database to copy;

7. Start Recovery. 

SQL> alter database recover managed standby database disconnectfrom session;

8. Switch logfile on primary and check gap. 

# On Primary 

sql > alter system switch logfile;

-- Query to check archivelog GAP on standby 

col SPACE_LIMIT_GB for 999,999,999,999
col SPACE_USED_GB for 999,999,999,999
col name for a30

SELECT ARCH.THREAD# "Thread",
         ARCH.SEQUENCE# "Last Sequence Received",
         APPL.SEQUENCE# "Last Sequence Applied",
         (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
    FROM (SELECT THREAD#, SEQUENCE#
            FROM V$ARCHIVED_LOG
           WHERE (THREAD#, FIRST_TIME) IN (  SELECT THREAD#, MAX (FIRST_TIME)
                                               FROM V$ARCHIVED_LOG
                                           GROUP BY THREAD#)) ARCH,
         (SELECT THREAD#, SEQUENCE#
            FROM V$LOG_HISTORY
           WHERE (THREAD#, FIRST_TIME) IN (  SELECT THREAD#, MAX (FIRST_TIME)
                                               FROM V$LOG_HISTORY
                                           GROUP BY THREAD#)) APPL
   WHERE ARCH.THREAD# = APPL.THREAD# 
ORDER BY 1;

ASM Space Monitoring

# ASM Space Monitoring 


 set line 280 pagesize 10000
col gname form a10
col dbname form a10
col file_type form a30
col MB for 999,999,999,999
col GB for 999,999,999,999
col FREE_MB for 999,999,999,999
col TOTAL_MB for 999,999,999,999
col PERCENTAGE for 999

SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage 
     FROM v$asm_diskgroup;

 
SELECT
    gname,
    dbname,
    file_type,
    round(SUM(space)/1024/1024) mb,
    round(SUM(space)/1024/1024/1024) gb,
    COUNT(*) "#FILES"
FROM
    (
        SELECT
            gname,
            regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname,
            file_type,
            space,
            aname,
            system_created,
            alias_directory
        FROM
            (
                SELECT
                    concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
                    system_created,
                    alias_directory,
                    file_type,
                    space,
                    level,
                    gname,
                    aname
                FROM
                    (
                        SELECT
                            b.name            gname,
                            a.parent_index    pindex,
                            a.name            aname,
                            a.reference_index rindex ,
                            a.system_created,
                            a.alias_directory,
                            c.type file_type,
                            c.space
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b,
                            v$asm_file c
                        WHERE
                            a.group_number = b.group_number
                        AND a.group_number = c.group_number(+)
                        AND a.file_number = c.file_number(+)
                        AND a.file_incarnation = c.incarnation(+) ) START WITH (mod(pindex, power(2, 24))) = 0
                AND rindex IN
                    (
                        SELECT
                            a.reference_index
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b
                        WHERE
                            a.group_number = b.group_number
                        AND (
                                mod(a.parent_index, power(2, 24))) = 0
                            and a.name like '%'
                    ) CONNECT BY prior rindex = pindex )
        WHERE
            NOT file_type IS NULL
            and system_created = 'Y' )
WHERE
    dbname like '%'
GROUP BY
    gname,
    dbname,
    file_type
ORDER BY 5 desc,
    gname,
    dbname,
    file_type
/

ORA-01341: LogMiner out-of-memory

ORA-01280: Fatal LogMiner Error.
ORA-01341: LogMiner out-of-memory


Solution :  

Increase size of below parameter to fix error.

exec dbms_capture_adm.stop_capture(‘CAPTURE_NAME’);
exec dbms_capture_adm.set_parameter(‘CAPTURE_NAME’,’_SGA_SIZE’,’50’);
exec dbms_capture_adm.start_capture(‘CAPTURE_NAME’);

The above changes space for named capture process : 'CAPTURE_NAME'  from  10M -> 50M

Note: If you change the value of the hidden capture parameter _SGA_SIZE, 

then you should also increase the streams_pool_size (10.1 and above) correspondingly. 
For 9.2, increase the shared_pool parameter correspondingly.

Creating FS on linux

# Creating FS on linux 

fdisk /dev/xvdb
pvcreate /dev/xvdb1
vgcreate vg_u01 /dev/xvdb1
df -h
vgs
lvcreate -L 99.99g -n lv_u01 vg_u01
lvs
df -h
mkfs.ext4 /dev/mapper/vg_u01-lv_u01
mkdir /u01
vi /etc/fstab  -- add new entry in it
mount -a

Memory Usage by Sessions

-- Memory Usage 


-- check PGA memory stats 

select * from v$pgastat;

-- memory used by sessions

SELECT    TO_CHAR (ssn.sid, '9999')
         || ' - '
         || NVL (ssn.username, NVL (bgp.name, 'background'))
         || NVL (LOWER (ssn.machine), ins.host_name)
            "SESSION",
         TO_CHAR (prc.spid, '999999999') "PID/THREAD",
         TO_CHAR ( (se1.VALUE / 1024) / 1024, '999G999G990D00') || ' MB'
            " CURRENT SIZE",
         TO_CHAR ( (se2.VALUE / 1024) / 1024, '999G999G990D00') || ' MB'
            " MAXIMUM SIZE"
    FROM v$sesstat se1,
         v$sesstat se2,
         v$session ssn,
         v$bgprocess bgp,
         v$process prc,
         v$instance ins,
         v$statname stat1,
         v$statname stat2
   WHERE     se1.statistic# = stat1.statistic#
         AND stat1.name = 'session pga memory'
         AND se2.statistic# = stat2.statistic#
         AND stat2.name = 'session pga memory max'
         AND se1.sid = ssn.sid
         AND se2.sid = ssn.sid
         AND ssn.paddr = bgp.paddr(+)
         AND ssn.paddr = prc.addr(+)
ORDER BY 4;

-- Memory used by group of sessions

SELECT ssn.sid,
         ssn.username,
         SUM ( (se1.VALUE / 1024) / 1024) Current_size,
         SUM ( (se2.VALUE / 1024) / 1024) MAXIMUM_SIZE
    FROM v$sesstat se1,
         v$sesstat se2,
         v$session ssn,
         v$bgprocess bgp,
         v$process prc,
         v$instance ins,
         v$statname stat1,
         v$statname stat2
   WHERE     se1.statistic# = stat1.statistic#
         AND stat1.name = 'session pga memory'
         AND se2.statistic# = stat2.statistic#
         AND stat2.name = 'session pga memory max'
         AND se1.sid = ssn.sid
         AND se2.sid = ssn.sid
         AND ssn.paddr = bgp.paddr(+)
         AND ssn.paddr = prc.addr(+)
GROUP BY ssn.sid, ssn.username
ORDER BY 4 DESC;

RAC - NTP service failed

ERROR : 

"NTP common Time Server Check started...
PRVF-5410 : Check of common NTP Time Server failed
PRVF-5416 : Query of NTP daemon failed on all nodes
Result: Clock synchronization check using Network Time Protocol(NTP) failed

PRVF-9652 : Cluster Time Synchronization Services check failed


Solution : 

Besides the section that "Generated by Exadata",

/etc/ntp.conf  -- should have the following un-commented lines

restrict default ignore
restrict -6 default ignore
restrict 127.0.0.1
restrict -6 ::1
server 127.127.1.0 # local clock
fudge 127.127.1.0 stratum 10

-------------------------------------------------------------

# yum install ntp

# service ntpd stop
Shutting down ntpd:                                        [  OK  ]
# chkconfig ntpd off
# mv /etc/ntp.conf /etc/ntp.conf.org
# rm /var/run/ntpd.pid


If you wish to use NTP, you must add the "-x" option into the following line in the "/etc/sysconfig/ntpd" file.

OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"

# service ntpd restart

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

Error : RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

Solution : If standby database cloned from primary backup then dataguard configuration was there so it will not allow to delete archive logs, then need to remove using force option.

rman > delete noprompt force archivelog until time 'sysdate-3';

SSH key setup for RAC

# Turn on the SSH service and make sure it starts automatically on reboot.

# service sshd start

# Manual User Equivalence (Key-Based Authentication) Configuration

Assuming we have a two node cluster (rac1.localdomain, rac2.localdomain), log in as the "oracle" user and perform the following tasks on each node.

su - oracle
mkdir ~/.ssh
chmod 700 ~/.ssh
/usr/bin/ssh-keygen -t rsa # Accept the default settings.
The RSA public key is written to the ~/.ssh/id_rsa.pub file and the private key to the ~/.ssh/id_rsa file.

Log in as the "oracle" user on rac1.localdomain, generate an "authorized_keys" file and copy it to rac2.localdomainusing the following commands.

su - oracle
cd ~/.ssh
cat id_rsa.pub >> authorized_keys
scp authorized_keys rac2.localdomain:/home/oracle/.ssh/
Next, log in as the "oracle" user on rac2.localdomain and perform the following commands.

su - oracle
cd ~/.ssh
cat id_rsa.pub >> authorized_keys
scp authorized_keys rac1.localdomain:/home/oracle/.ssh/
The "authorized_keys" file on both servers now contains the public keys generated on all nodes.

To enable SSH user equivalency on the cluster member nodes issue the following commands on each node.

ssh rac1 date
ssh rac2 date
ssh rac1.localdomain date
ssh rac2.localdomain date
exec /usr/bin/ssh-agent $SHELL
/usr/bin/ssh-add


You should check the permission of the files ( else ssh will not work)
on the remote machine using ls -l ~/.ssh and setup the permission:

chmod 600 ~/.ssh/authorized_keys
chmod 600 ~/.ssh/id_rsa
chmod 700 ~/.ssh/id_rsa.pub
chmod 700 /home/oracle/.ssh

----------------------------------------------------------------------------

# sshUserSetup.sh (Oracle Method)

The Oracle 11gR2 grid and database media includes a "sshsetup" directory containing a "sshUserSetup.sh" file. Running this file with no parameter shows the usage information.

$ cd /path/to/grid/media/sshsetup
$ ./sshUserSetup.sh


More details : https://oracle-base.com/articles/linux/user-equivalence-configuration-on-linux

Golden Gate -- Coordinated replicat - deadlock issue

-- Golden Gate -- Coordinated replicat  - deadlock issue 

Solution : 

Configure REPERROR something like this:

REPERROR(60, TRANSABORT, MAXRETRIES 10, DELAYSECS 10)

Basically, retry 10 odd times on hitting a deadlock. This is the current recommendation for deadlocks. We automatically retry 3 times and then abend. We can configure to get past the error if 3 aren’t sufficient.

TRANSABORT should ensure data integrity. It will cause the replicat to aborts the transaction and repositions to the beginning of the transaction. This sequence continues either until the record(s) are processed successfully or MAXRETRIES expires.

If MAXRETRIES is not set, the TRANSABORT action will loop continuously. 

MRP stuck on old scan whitch is not available in standby archivelogs

----- MRP stuck on old scan whitch is not available in standby archivelogs

1. check minimum scn from database datafiles.

SQL> select distinct to_char(fhscn, '99999999999999999999') as "Stby_datafile_hdr_SCN" from X$KCVFH;

Stby_datafile_hdr_SCN
---------------------
              1920034
              1921414
              1923571
              1923696
              1923788
              1923903
              1924025
              1924144
       12246209080156

2. found few old scn's in datafiles, now check which datafiles has old scn.
 
select file#, name, to_char(checkpoint_change#, '99999999999999999999') from v$datafile_header where checkpoint_change# = 0;

 FILE# NAME                                             
------ ----------------------------------------------------
    11 +DATA_P/DATAFILE/abc 
    24 +DATA_P/DATAFILE/abc 
    43 +DATA_P/DATAFILE/abc 


  SQL > select file#, name from v$datafile where file# = 299

3. Copy datafile identified in step 2 from primary to standby database
4. Recreate standby control file from current control file at primary. And restore at standby.
5. Restart standby recovery.

  sql> alter database recover managed standby database disconnect;

  SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM GV$MANAGED_STANDBY;

ORA-01665: control file is not a standby control file

ORA-01665: control file is not a standby control file

It means that I have restored not standby control file…

To cope with this situation I have to CREATE STANDBY CONTROLFILEon primary, copy it to standby, then restore it(better read full store here), depending on disk configuration rename data, log-files…, but we have other soluction as well. 

So, the quick solution for ORA-01665 will be:

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Lets see the small example:

SQL> STARTUP MOUNT

ORACLE instance started.

…
Database mounted.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

ERROR at line 1:
ORA-01665: control file is not a standby control file

SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
—————-
PRIMARY

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> STARTUP MOUNT

ORACLE instance started.

…
Database mounted.

SQL> SELECT database_role FROM v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

Rebuild standby from full rman backup

------ Rebuild standby from full rman backup

1. take backup of primary with archivelog
2. copy to DR
3. start database in nomount mode
3.1 Set cluster parameter off
4. restore control file
5. mount database
6. catalog backup
7. run restore command

run {
set newname for datafile 1 to '+DATA_P/datafile1';
set newname for datafile 2 to '+DATA_P/datafile2';
restore database;
switch datafile all;
recover database until canel;
}

8. once restore is done shut down database
9. mount database and create temp and redo logs files
10. convert into physical standby
11. set cluster parameter true and bounce db in to mount
12. start recovery 

Sessions using temp

--- sessions using temp

col user for a15
col OSUSER for a15
col unix_pid for a10
col program for a20 trun
col MACHINE for a30 trunc
col SQL_TEXT for a60 trunc
 col TABLESPACE for a20
compute sum of mb_temp_used on tablespace

select * from (
select distinct
   c.username "user",
   c.osuser,
   c.sid,
   c.serial#,
   b.spid "unix_pid",
   c.machine,
   c.program "program",
   a.blocks * e.block_size/1024/1024 mb_temp_used  ,
   a.tablespace,
   d.sql_text
from
   gv$sort_usage a,
   gv$process b,
   gv$session c,
   gv$sqlarea d,
   dba_tablespaces e
where
   c.saddr=a.session_addr
and
   b.addr=c.paddr
and
   c.sql_address=d.address(+)
and
   a.tablespace = e.tablespace_name
and (a.blocks * e.block_size/1024/1024) > 10
order by MB_TEMP_USED desc
);
 
   

ORA Errors

ORA-12850: Could Not Allocate Slaves On All Specified Instances: 2 Needed

ACTION NAME:(Automatic Report Flush)

Automatic report capturing is a new feature in oracle 12c, As part of this feature, some monitoring SQLs are executed by MMON_SLAVE to identify the resource-intensive SQLs queries. And this monitoring SQLs while executing sometimes fail and this type of error will be logged in the alert log.

Oracle already acknowledged this bug.

 However, to fix this the workaround is to set parameter And this doesn’t need database bounce.

SQL> alter system set "_report_capture_cycle_time"=0 scope=both sid='*'   /* Default is 60