Sunday, January 24, 2016

RMAN-06820: WARNING: failed to archive current log at primary database

RMAN archive log backup at the standby site is throws the following errors

********************************************************************************
RMAN-06820: WARNING: failed to archive current log at primary database ORACLE error from target database: ORA-17629: Cannot connect to the remote database server
********************************************************************************


RMAN> backup database plus archivelog tag 'FULL_AL_BKP';


Starting backup at 29-FEB-16
RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
skipping archived logs of thread 1 from sequence 192 to 201; already backed up
Finished backup at 29-FEB-16

Starting backup at 29-FEB-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCLSTB1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCLSTB1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCLSTB1/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCLSTB1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCLSTB1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 29-FEB-16
channel ORA_DISK_1: finished piece 1 at 29-FEB-16
piece handle=/u01/app/oracle/backup/ORCLSTB1/ORCL_20160229_82_1_1.bak tag=TAG20160229T114824 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 29-FEB-16

Starting backup at 29-FEB-16
RMAN-06820: WARNING: failed to archive current log at primary database
ORACLE error from target database:
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-00942: table or view does not exist

using channel ORA_DISK_1
specification does not match any archived log in the repository
backup cancelled because there are no files to backup
Finished backup at 29-FEB-16

Starting Control File and SPFILE Autobackup at 29-FEB-16
piece handle=/u01/app/oracle/backup/ORCLSTB1/c-1424488411-20160229-05 comment=NONE
Finished Control File and SPFILE Autobackup at 29-FEB-16


But rest of the backup actually completed successfully.

As per the Source : http://oraclesivaram.blogspot.com/2015/11/rman06820-warning-failed-to-archive.html

****************
CAUSE:
****************

11.2.0.4 onward as per 'unpublished' Bug 8740124, we now include the current standby redo log as part of an RMAN archivelog backup at the standby site. This is achieved by forcing a log switch at the primary site. However, the connection to the primary failed when attempting to do so.This is due to this bug:
Bug 17580082 ACTIVE STANDBY RMAN06820: WARNING: FAILED TO ARCHIVE CURRENT LOG AT PRIMARY

****************
Workaround:
****************
Do not use operating system authentication to login with RMAN. Use a username and password.

That is, do not use just the "/" (operating system authentication) connect to the standby database:
$ rman target /
Connecting as 'rman target /'

# it gets the sys user but not the password and so, it does NOT mean it is being explicitly specified to connect as sysdba.
Instead put in the username and password for the SYSDBA user:

$ rman target sys/password@stby
Connecting as 'rman target sysdba_user/password@stby'

Note: Also make sure password, within the (ORAPWD) password file, in primary and standby should be identical.

For more details please look at the Doc ID: 1616074.1

Saturday, January 23, 2016

restore archive logs from backup

Restoring a archive log (LOG SEQ) from backups.

[oracle@Linux02 backups]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Feb 16 10:47:13 2016

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

connected to target database: ORCL (DBID=1424488411, not open)

RMAN> restore archivelog logseq 140;

Starting restore at 16-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=140
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/rman_bak_ORCL_DB_0rqtvod6_27_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/rman_bak_ORCL_DB_0rqtvod6_27_1 tag=LEVEL0BACKUP_FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 16-FEB-16



Restoring archive from backups from SEQ. 

This will restore all archive logs from SEQ till date

RMAN> restore archivelog from logseq 140;   

Restoring archive from backups from SEQ until SEQ .

RMAN> restore archivelog from logseq 140 until logseq 150;

Starting restore at 16-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

archived log for thread 1 with sequence 140 is already on disk as file /u01/app/oracle/oraarch/ORCLSTB1_1_140_896707677.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=141
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=142
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=143
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=144
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=145
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=146
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=147
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=148
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=149
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=150
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/rman_bak_ORCL_DB_0rqtvod6_27_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/rman_bak_ORCL_DB_0rqtvod6_27_1 tag=LEVEL0BACKUP_FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 16-FEB-16

Thursday, January 21, 2016

Start and Stop an Instance in RAC environment

This is a simple example on how to start and stop and Instance in RAC environment.

----------------------------------------------------------------
STOP AN INSTANCE IN RAC ENVIRONMENT :
----------------------------------------------------------------
CMD TO STOP INSTANCE:

srvctl stop instance -d ORCLRAC -i RAC212

Description:

-d crm  : HERE -d IS TO PASS DATABASE AND CRM IS THE DB_UNIQUE_NAME
-i crm1 : HERE -i IS TO PASS INSTANCE AND CRM1 IS THE INSTANCE_NAME

EXAMPLE :

oracle@RAC212:[~] $ echo $ORACLE_SID
ORCLRAC2

LET US FIRST CHECK IF THE INSTANCE IS RUNNING ::

oracle@RAC212:[~] $ ps -ef |grep pmon
oracle   13020     1  0 09:59 ?        00:00:00 ora_pmon_ORCLRAC2
grid     28699     1  0 Jan18 ?        00:00:22 asm_pmon_+ASM2
oracle   41095 21699  0 11:45 pts/0    00:00:00 grep pmon

STOP INSTANCE FROM RAC :

oracle@RAC212:[~] $ srvctl stop instance -d ORCLRAC -i ORCLRAC2
oracle@RAC212:[~] $
--------------------------------------------------------------------------------------------------------------------------------

NOW FROM NODE 1 LETS VERIFY:

oracle@RAC211:[~] $ sqlplus atoorpu@ORCLRAC

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 20 11:46:30 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
ORCLRAC1

SQL> select instance_name from gv$instance;

INSTANCE_NAME
----------------
ORCLRAC1

oracle@RAC212:[~] $ ps -ef |grep pmon
grid     28699     1  0 Jan18 ?        00:00:22 asm_pmon_+ASM2
oracle   41618 21699  0 11:47 pts/0    00:00:00 grep pmon

----------------------------------------------------------------
YOU CAN ALSO CONFIRM LIKE THIS :
----------------------------------------------------------------

oracle@RAC212:[~] $ echo $ORACLE_SID
ORCLRAC2

oracle@RAC212:[~] $ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 20 12:39:30 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

----------------------------------------------------------------
START AN INSTANCE IN RAC ENVIRONMENT :
----------------------------------------------------------------

oracle@RAC212:[~] $ ps -ef |grep pmon
grid     28699     1  0 Jan18 ?        00:00:23 asm_pmon_+ASM2
oracle   53731 21699  0 12:42 pts/0    00:00:00 grep pmon

oracle@RAC212:[~] $ srvctl start instance -d ORCLRAC -i ORCLRAC2

oracle@RAC212:[~] $ ps -ef |grep pmon
grid     28699     1  0 Jan18 ?        00:00:23 asm_pmon_+ASM2
oracle   53851     1  0 12:42 ?        00:00:00 ora_pmon_ORCLRAC2
oracle   54051 21699  0 12:43 pts/0    00:00:00 grep pmon

Monday, January 18, 2016

RAC - Missing Cvuqdisk package

If you are trying to install the RAC, then you might come up across a missing cvuqdisk* rpm error.This package comes with the Grid install software.Once you have downloaded the Grid infrastructure software. You will find the Cvuqdisk* package in the rpm directory.


grid@RAC01:[/u01/app/ORCL_SFTW/grid_software] $ ls

install  readme.html  response  rpm  runcluvfy.sh  runInstaller  sshsetup  stage  welcome.html

Now change dir to rpm :

grid@RAC01:[/u01/app/ORCL_SFTW/grid_software] $ cd rpm

grid@RAC01:[/u01/app/ORCL_SFTW/grid_software/rpm] $ exit
logout

atoorpu@RAC01:[/u01/app] $ cd /u01/app/ORCL_SFTW/grid_software/rpm


You need to be logged in as root to install the package.

atoorpu@RAC01:[/u01/app/ORCL_SFTW/grid_software/rpm] $ sudo rpm -Uvh cvuqdisk-1.0.9-1.rpm
Preparing...                ########################################### [100%]
   1:cvuqdisk               ########################################### [100%]


NOTE : Now lets see if the package was installed or not. Check this on both servers.
 
atoorpu@RAC01:[/u01/app/ORCL_SFTW/grid_software/rpm] $ rpm -qa |grep cvuqdisk-1.0*
cvuqdisk-1.0.9-1.x86_64

Sunday, January 17, 2016

Count rows in all tables in a schema

Below PL/SQL block will allow you to count rows in all tables in a given schema: 


Plsql :

declare
    v_count integer;
begin

    for r in (select table_name, owner from all_tables
              where owner in ('&owner'))
    loop
        execute immediate 'select count(*) from ' ||r.owner||'.'|| r.table_name
            into v_count;
        --INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED) VALUES (r.table_name,r.owner,v_count,SYSDATE);
        DBMS_OUTPUT.PUT_LINE('TABLE OWNER : '||lpad(to_char(r.owner),10)||', Table_name : '||lpad(to_char(r.table_name),10)||
        ', No or rows: '||rpad(to_char(v_count),6));
    end loop;

end;

 /


 Sample output:

TABLE OWNER :      ATEST, Table_name :  TEST_TAB1, No or rows: 11   
TABLE OWNER :      ATEST, Table_name :     LOOKUP, No or rows: 3    
TABLE OWNER :      ATEST, Table_name : BIG_TABLE2, No or rows: 100000
TABLE OWNER :      ATEST, Table_name :  BIG_TABLE, No or rows: 100000


Note :
when run this will prompt you for the schema you want to count the the table rows.You can alter this block to count for all tables in multiple schemas by changing below part to list of schemas like below.

or r in (select table_name, owner from all_tables
              where owner in ('&owner')


You can change the &owner  to 'SCHEMA1','SCHEMA2','SCHEMA3'...

Wednesday, January 13, 2016

Adding disk group to ASM instance using ASMCA


Here is the step by step process on how to add diskgroup to existing ASM instance via ASMCA.

on cmd prompt set the ASM insatance and then invoke asmca. by using below cmd.

$ asmca