Archivelog,and RMAN Backup copy from ASM to Local filesystem

15 08 * * *  /u04/MasterDB/scripts/grid_scripts/asm_rman_backup.sh >>/u04/MasterDB/smrrac_backup/SMRRAC_FULL.log | mail -s "RMAN ASM Bacckup to filesystem" varun.yadav@mind-infotech.com

0 */1 * * * /u04/MasterDB/scripts/grid_scripts/
>>//u04/MasterDB/scripts/grid_scripts/asm_ls.txt | mail -s "Archivelog smrrac " varun.yadav@mind-infotech.com

################# asm_rman_backup.sh Script ############################

grid@sgdcplm02:/home/oracle> cat /u04/MasterDB/scripts/grid_scripts/asm_rman_backup.sh
#!/bin/bash

#

# This script copies files from FRA on ASM to local disk

#

export ORACLE_SID=+ASM1

export ORACLE_HOME=/u01/app/12.1.0/grid  ##{Grid OH}
export PATH=$ORACLE_HOME/perl/bin:$PATH
ASMLS=/u04/MasterDB/scripts/grid_scripts/asm_rman.txt ##{ASM files list}

FRA=+BACKUP/rman_bkp ##{source location of files}

LOCALBACKUPDIR=/u04/MasterDB/smrrac_backup  ##{destination filesystem}

LOG=/u04/MasterDB/scripts/grid_scripts/asm_rman1.txt ##{log file}

#

# Get the list of files

#

$ORACLE_HOME/bin/asmcmd > $ASMLS <<EOF

ls $FRA

exit

EOF

#

# Clean the list by removing "ASMCMD>"

#

sed -i 's/ASMCMD> //g' $ASMLS

##cat $ASMLS

echo `date` > $LOG

#

# Copy files one by one

#



for FILENAME in `cat $ASMLS`

do

if [[ ! -f $LOCALBACKUPDIR/${FILENAME} ]]

then

$ORACLE_HOME/bin/asmcmd >> $LOG <<EOF

cp $FRA/$FILENAME $LOCALBACKUPDIR

EOF

fi

done

echo `date` >> $LOG
grid@sgdcplm02:/home/oracle>

#################################################################################

##########################  asm_archive.sh SCRIPTS ############################### 

grid@sgdcplm02:/home/oracle> cat /u04/MasterDB/scripts/grid_scripts/asm_archive.sh

#!/bin/bash

#

# This script copies files from FRA on ASM to local disk

#

export ORACLE_SID=+ASM1

#export ORACLE_HOME=/u01/app/12.1.0/grid  ##{Grid OH}

export ORACLE_HOME=/u01/app/12.1.0/grid/

#export PATH=$ORACLE_HOME/OPatch:$PATH:$ORACLE_HOME/bin

export PATH=$ORACLE_HOME/perl/bin:$PATH

ASMLS=/u04/MasterDB/scripts/grid_scripts/asm_ls.txt ##{ASM files list}

FRA=+FRA2/archivelog ##{source location of files}

LOCALBACKUPDIR=/u04/MasterDB/smrrac_archivelog  ##{destination filesystem}

LOG=/u04/MasterDB/scripts/grid_scripts/asm_log.txt ##{log file}

#

# Get the list of files

#

$ORACLE_HOME/bin/asmcmd > $ASMLS <<EOF

ls $FRA

exit

EOF

#

# Clean the list by removing "ASMCMD>"

#

sed -i 's/ASMCMD> //g' $ASMLS

##cat $ASMLS

echo `date` > $LOG

#

# Copy files one by one

#

for FILENAME in `cat $ASMLS`

do

if [[ ! -f $LOCALBACKUPDIR/${FILENAME} ]]

then

$ORACLE_HOME/bin/asmcmd >> $LOG <<EOF

cp $FRA/$FILENAME $LOCALBACKUPDIR

EOF

fi

done

echo `date` >> $LOG

grid@sgdcplm02:/home/oracle>

EXPDP -Error - ORA-31626,ORA-31633,ORA-06512, ORA-06512,ORA-00955

DataPump Export Or Import Fails With Errors ORA-31633 ORA-6512 ORA-955 (Doc ID 556425.1)
oracle@sgdcplm02:/u04/MasterDB/scripts> sh expdp_SMRRAC_full.sh

Export: Release 12.1.0.2.0 - Production on Fri Oct 25 12:40:28 2019

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.FULL_EXPDP_SMRRAC1"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1048
ORA-00955: name is already used by an existing object


oracle@sgdcplm02:/u04/MasterDB/scripts> sqlplus sys/system123@smrrac1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 25 12:40:38 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> @d
SP2-0310: unable to open file "d.sql"
SQL> select sid, serial#, sofar, totalwork,
dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;  2    3    4

no rows selected

SQL> SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;  2

OWNER_NAME
--------------------------------------------------------------------------------
JOB_NAME
--------------------------------------------------------------------------------
OPERATION
--------------------------------------------------------------------------------
JOB_MODE
--------------------------------------------------------------------------------
STATE                          ATTACHED_SESSIONS
------------------------------ -----------------
SYS
FULL_EXPDP_SMRRAC1
EXPORT

OWNER_NAME
--------------------------------------------------------------------------------
JOB_NAME
--------------------------------------------------------------------------------
OPERATION
--------------------------------------------------------------------------------
JOB_MODE
--------------------------------------------------------------------------------
STATE                          ATTACHED_SESSIONS
------------------------------ -----------------
FULL
NOT RUNNING                                    0


OWNER_NAME
--------------------------------------------------------------------------------
JOB_NAME
--------------------------------------------------------------------------------
OPERATION
--------------------------------------------------------------------------------
JOB_MODE
--------------------------------------------------------------------------------
STATE                          ATTACHED_SESSIONS
------------------------------ -----------------
SYS
FULL_EXPDP_SMRRAC2
EXPORT

OWNER_NAME
--------------------------------------------------------------------------------
JOB_NAME
--------------------------------------------------------------------------------
OPERATION
--------------------------------------------------------------------------------
JOB_MODE
--------------------------------------------------------------------------------
STATE                          ATTACHED_SESSIONS
------------------------------ -----------------
FULL
NOT RUNNING                                    0


SQL> SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;  2

STATUS   OBJECT_ID OBJECT_TYPE
------- ---------- -----------------------
OWNER.OBJECT
--------------------------------------------------------------------------------
VALID       105403 TABLE
SYS.FULL_EXPDP_SMRRAC1

VALID       106260 TABLE
SYS.FULL_EXPDP_SMRRAC2


SQL>

Table dropped.

SQL> drop table SYS.FULL_EXPDP_SMRRAC2;

Table dropped.

SQL> select o.status, o.object_id, o.object_type,

       o.owner||'.'||object_name "OWNER.OBJECT"

from   dba_objects o, dba_datapump_jobs j

where  o.owner=j.owner_name and

       o.object_name=j.job_name and

       j.job_name not like 'BIN$%'

order  by 4, 2;  2    3    4    5    6    7

no rows selected

RAC - application continuity ADD ,Failover and relocate services command

Application continuity commands:

Instance name: SMRRAC1, SMRRAC2


STEP 1 : Add service acsmmrac  failovertype TRANSACTION  commit_outcome TRUE

• srvctl add service -db smrrac -service acsmrrac -preferred smrrac1 -available smrrac2 -failovertype TRANSACTION -commit_outcome TRUE -failoverretry 30 -failoverdelay 10 -retention 86400 -replay_init_time 1800 -notification TRUE

STEP 2: Start acsmrrac service

• srvctl start service -db smrrac -service acsmrrac

STEP 3: Check cluster service

•  crsctl status resource -t

STEP 4:

Check service details on node

•  srvctl status database -d smrrac -v

STEP 5 :

Failover planned instance smrrac1

• srvctl stop instance -i smrrac1 -d smrrac -failover

STEP 6 :

Relocate services 

•  srvctl relocate service -s acsmrrac-db smrrac -oldinst smrrac2 -newinst smrrac1

#################################################################################

• SQL PLUS Command : 

col username format a10
col service_name format a20
SELECT INST_ID, USERNAME, SERVICE_NAME, FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER FROM GV$SESSION WHERE USERNAME='INFODBA' ORDER BY INST_ID;

#################################################################################

• TNSNAMES.ORA Entry 

acsmrrac =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =scan-smrplm.smr.motherson.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = acsmrrac)
    )
  )