ASM files db wise

ASM files db wise to see all list of files in db


---for ar database
column path format a70
column file_type format a15


select concat('+'||gname, sys_connect_by_path(aname, '/')) path,
       system_created, alias_directory, file_type
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
       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 = '&dbname'
                )
connect by prior rindex = pindex;

---

emca -DBCONSOLE_HTTP_PORT 5500 -RMI_PORT 5520 -JMS_PORT 5540
-AGENT_PORT 1830


emca -config dbcontrol db -repos create

emca -config dbcontrol db -repos recreate

Running script on all database

If we have multiple instances running on one servers and some script we need to run on all db's, then its good we use below script to set ORACLE_SID and login and run the script.

This specially used for below scenario:

1. DB level patching in one go.
2. While patching take snap of before and after patch.
3. Some DB level script for reporting.

for i in `ps -aef | grep pmon |awk '{print $8}' |  egrep -i -v 'grep|asm' | cut -d "_" -f3`
do
  echo "ORACLE_SID $i : `hostname`"
  export ORACLE_SID=$i
  sqlplus -s / as sysdba <<EOF
  set lin 180
  select instance_name,host_name from v\$instance;
  srvctl status database -d $i
  exit
EOF
done


awk index = oracle instr

[oracle@node1 ~]$ cat /etc/oratab | egrep node | grep -v '#B' | awk -F ':' '{print substr($1,1,index($1,"1")-1)}'
node
[oracle@node1 ~]$

Filter blank lines


cat /etc/oratab | grep -v '^$'

Check before RMAN resetlogs.

 ---Check before resetlogs

 alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;


 SYS@FAKE> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;

FUZ STATUS  ERROR                                                             REC CHECKPOINT_CHANGE# CHECKPOINT_TIME               COUNT(*)
--- ------- ----------------------------------------------------------------- --- ------------------ -------------------- -----------------
YES ONLINE                                                                                    267865 15-MAY-2015 14:45:25                 4
YES ONLINE                                                                                    269515 15-MAY-2015 15:19:13                 1

2 rows selected.

5 rows selected.

SYS@FAKE> select file#,status, error, recover,fuzzy from v$datafile_header where fuzzy='YES';

            FILE# STATUS  ERROR                                                             REC FUZ
----------------- ------- ----------------------------------------------------------------- --- ---
                1 ONLINE                                                                        YES
                2 ONLINE                                                                        YES
                3 ONLINE                                                                        YES
                4 ONLINE                                                                        YES
                5 ONLINE                                                                        YES

5 rows selected.


SYS@FAKE> recover database using backup controlfile;
ORA-00279: change 267865 generated at 05/15/2015 14:45:25 needed for thread 1
ORA-00289: suggestion : C:\APP\DILIP\PRODUCT\11.1.0\DB_1\RDBMS\ARC00004_0741132407.001
ORA-00280: change 267865 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\APP\DILIP\ORADATA\clone_test\O1_MF_2_6MP1X3CM_.log
Log applied.
Media recovery complete.
SYS@FAKE> select file#,status, error, recover,fuzzy from v$datafile_header where fuzzy='YES';  ---NO fuzzy

no rows selected

SYS@FAKE>


SYS@FAKE> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;

FUZ STATUS  ERROR                                                             REC CHECKPOINT_CHANGE# CHECKPOINT_TIME               COUNT(*)
--- ------- ----------------------------------------------------------------- --- ------------------ -------------------- -----------------
NO  ONLINE                                                                                    272859 15-MAY-2015 16:51:48                 5

1 row selected.

SYS@FAKE>

---Good to go for resetlogs

SYS@FAKE> alter database open resetlogs;

Database altered.

SYS@FAKE>

RMAN cataloging tape backup piece

Catalog Backset backup on Symantec 

1.
Activate automatic rman configuration
catalog device type 'sbt_tape' backuppiece '668ghi8_1_1';

2.
DECLARE 
v_dev varchar2(50); 
v_fullname varchar2(900); 
recid number; 
stamp number; 
BEGIN v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape', ident=>'test'); sys.dbms_backup_restore.inspectBackupPiece('hggfddhgf_1_1',v_fullname,recid,stamp); sys.dbms_backup_restore.deviceDeallocate; 
END;
 /