Advertisements

Recovery of a Missing Datafile that has no backups (database is open).

Datafile Lost due to OS level delete:
=====================================
Recovery of a Missing Datafile that has no backups (database is open).
If a non system datafile that was not backed up since the last backup is missing,
recovery can be performed if all archived logs since the creation
of the missing datafile exist.
Pre requisites: All relevant archived logs.
1. alter datafile offline immediate;
2. alter database create datafile ‘fully_qualified_file_name’;
3. alter database datafile ‘fully_qualified_file_name’ online;

If the create datafile command needs to be executed to place the datafile on a
location different than the original use:
alter database create datafile ‘fully_qualified_file_name’ as ‘fully_qualified_file_name’;

Advertisements

CRS-2800 | Cannot start resource

set lines 190
set pages 150
select host_name,name,instance_name,database_role,status,open_mode,logins from v$instance,v$database;

HOST_NAME NAME INSTANCE_NAME DATABASE_ROLE STATUS OPEN_MODE LOGINS
—————————————————————- ——— —————- —————- ———— ——————– ———-
shanojrac1 ORCL ORCL1 PRIMARY OPEN READ WRITE ALLOWED

[grid@shanojrac1 root]$ srvctl status database -d orcl
Instance ORCL1 is not running on node shanojrac1
Instance ORCL2 is running on node shanojrac2
Instance ORCL3 is running on node shanojrac3

[oracle@shanojrac1 ~]$ srvctl start instance -i ORCL1 -d ORCL
PRCR-1013 : Failed to start resource ora.orcl.db
PRCR-1064 : Failed to start resource ora.orcl.db on node shanojrac1
CRS-2800: Cannot start resource ‘ora.OCR_DATA.dg’ as it is already in the INTERMEDIATE state on server ‘shanojrac1’
[oracle@shanojrac1 ~]$ srvctl stop instance -i ORCL1 -d ORCL
PRCC-1017 : ORCL was already stopped on shanojrac1

[oracle@shanojrac1 ~]$ crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.LISTENER.lsnr
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.OCR_DATA.dg
ONLINE INTERMEDIATE shanojrac1 CHECK TIMED OUT
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.asm
ONLINE ONLINE shanojrac1 Started
ONLINE ONLINE shanojrac2 Started
ONLINE ONLINE shanojrac3 Started
ora.gsd
OFFLINE OFFLINE shanojrac1
OFFLINE OFFLINE shanojrac2
OFFLINE OFFLINE shanojrac3
ora.net1.network
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.ons
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.registry.acfs
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE shanojrac1
ora.cvu
1 ONLINE ONLINE shanojrac1
ora.oc4j
1 ONLINE ONLINE shanojrac1
ora.orcl.db
1 ONLINE OFFLINE Instance Shutdown
2 ONLINE ONLINE shanojrac2 Open
3 ONLINE ONLINE shanojrac3 Open
ora.scan1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac2.vip
1 ONLINE ONLINE shanojrac2
ora.shanojrac3.vip
1 ONLINE ONLINE shanojrac3

=======================================
FIX:
=======================================

1) When you use the Oracle VM RAC templates with 11.2.0.2 and make use of role separation, you will not be able to build and RDBMS unless you
perform the following steps. During the creation of the database you will receive messages, Oracle not available. This is caused by the incorrect
setasmgid group used during the initiel configuration of you RAC 11.2.0.2 cluster.

Note:
—–
Please keep in mind that below work around is only applicable
when using role separation for the GRID infrastructure and uses the ORACLE RAC 11gR2 VM templates.

# cd /u01/app/11.2.0/grid/rdbms/lib
# cp -a ins_rdbms.mk ins_rdbms.mk.orig
# vi +1099 ins_rdbms.mk

Change line 1099 in ins_rdbms.mk, from this:

$(SETASMGID): $(ALWAYS) $(SETASMGID_DEPS) $(CONFIG)

to this:

$(SETASMGID): $(ALWAYS) $(SETASMGID_DEPS) config.$(OBJ_EXT)

2) Then tried to stop and start the ora.crsd to come out of INTERMEDIATE state.

[grid@shanojrac1 lib]$ crsctl stop res ora.crsd -init
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘shanojrac1’
CRS-2677: Stop of ‘ora.crsd’ on ‘shanojrac1’ succeeded
[grid@shanojrac1 lib]$ crsctl start res ora.crsd -init
CRS-2672: Attempting to start ‘ora.crsd’ on ‘shanojrac1’
CRS-2676: Start of ‘ora.crsd’ on ‘shanojrac1’ succeeded

[grid@shanojrac1 bin]$ crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.LISTENER.lsnr
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.OCR_DATA.dg
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.asm
ONLINE ONLINE shanojrac1 Started
ONLINE ONLINE shanojrac2 Started
ONLINE ONLINE shanojrac3 Started
ora.gsd
OFFLINE OFFLINE shanojrac1
OFFLINE OFFLINE shanojrac2
OFFLINE OFFLINE shanojrac3
ora.net1.network
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.ons
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
ora.registry.acfs
ONLINE ONLINE shanojrac1
ONLINE ONLINE shanojrac2
ONLINE ONLINE shanojrac3
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE shanojrac1
ora.cvu
1 ONLINE ONLINE shanojrac1
ora.oc4j
1 ONLINE ONLINE shanojrac1
ora.orcl.db
1 ONLINE ONLINE shanojrac1 Open
2 ONLINE ONLINE shanojrac2 Open
3 ONLINE ONLINE shanojrac3 Open
ora.scan1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac1.vip
1 ONLINE ONLINE shanojrac1
ora.shanojrac2.vip
1 ONLINE ONLINE shanojrac2
ora.shanojrac3.vip
1 ONLINE ONLINE shanojrac3

[grid@shanojrac1 bin]$ srvctl status database -d orcl
Instance ORCL1 is running on node shanojrac1
Instance ORCL2 is running on node shanojrac2
Instance ORCL3 is running on node shanojrac3

How to Audit User Activity in Oracle Database

Step 1.

SQL> alter system set audit_trail=db scope=spfile;
System altered.

SQL> alter system set audit_sys_operations=true scope=spfile;

Step 2. stop Database

Step 3. start database.

Step 4. check parameter
SQL> show parameter audit_
NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /u01/db/product/10.2.0/rdbms/audit
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB

Step 5. Log in as sysdba.

SQL> audit all by apps by access;

OR
Give the audit permission for delete and drop objects.

SQL> audit DELETE ANY TABLE,DROP ANY INDEX,DROP ANY PROCEDURE,DROP ANY TABLE,DROP ANY VIEW,ALTER ANY PROCEDURE,ALTER ANY INDEX by apps
by access whenever successful;

Step 7. Check the operation (log in as sysdba);

SQL> SELECT username, extended_timestamp, owner,obj_name ,action_name FROM dba_audit_trail WHERE owner = ‘APPS’ and extended_timestamp=sysdate -1 ORDER BY timestamp;

SQL> SELECT count(1) FROM dba_audit_trail WHERE owner = ‘APPS’ and extended_timestamp=sysdate -1 ORDER BY timestamp;

SQL> SELECT count(1) FROM dba_audit_trail WHERE owner = ‘APPS’ and extended_timestamp=sysdate – 1

SELECT count(1) FROM dba_audit_trail WHERE owner = ‘APPS’ ORDER BY timestamp;

Step 8. Check how many no of record in audit tables;
select count(1) from sys.aud$;