RMAN CrossCheck Command

The CROSSCHECK statement compares backup files on disk with entries in the catalog or controlfile.

If a file is found in the catalog, but does not exist on disk it is marked as "EXPIRED".

To crosscheck all backups use:

RMAN> CROSSCHECK BACKUP;

To list any expired backups detected by the CROSSCHECK command use:

RMAN> LIST EXPIRED BACKUP;

To delete any expired backups detected by the CROSSCHECK command use:

RMAN> DELETE EXPIRED BACKUP;

To crosscheck all archive logs use:

RMAN> CROSSCHECK ARCHIVELOG ALL;

To list all expired archive logs detected by the CROSSCHECK command use:

RMAN> LIST EXPIRED ARCHIVELOG ALL;

To delete all expired archive logs detected by the CROSSCHECK command use:

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

To crosscheck all datafile image copies use:

RMAN> CROSSCHECK DATAFILECOPY ALL;

To list expired datafile copies use:

RMAN> LIST EXPIRED DATAFILECOPY ALL;

To delete expired datafile copies use:

RMAN> DELETE EXPIRED DATAFILECOPY ALL;

To crosscheck all backups of the USERS tablespace use:

RMAN> CROSSCHECK BACKUP OF TABLESPACE USERS;

To list expired backups of the USERS tablespace:

RMAN> LIST EXPIRED BACKUP OF TABLESPACE USERS;

To delete expired backups of the USERS tablespace:

RMAN> DELETE EXPIRED BACKUP OF TABLESPACE USERS;

Problem : sys user password is changed on primary, which generating gap in standby

Primary
scp /u01/app/oracle/product/10.2.0/db_1/dbs/orapwpri oracle@192.168.1.26:/u01/app/oracle/product/10.2.0/db_1/dbs/

standby
cd /u01/app/oracle/product/10.2.0/db_1/dbs
mv orapwpri orapwstd

wait for 5 minute all archived log will be applied to standby

How To Enable/Disable Archive Logging In RAC Environment for 10.2 and higher version (Doc ID 1186764.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

GOAL

How to enable/disable database archivelog in RAC configuration?

SOLUTION

1. The following steps need to be taken to enable archive logging in a RAC database environment:

-- shutdown immediate all database instances
$ srvctl stop database -d <db_unique_name>

-- startup database in mount mode
$ srvctl start database -d <db_unique_name> -o mount

-- enable archive logging
$ sqlplus / as sysdba
sql> alter database archivelog;
sql> exit;

-- stop database
$ srvctl stop database -d <db_unique_name>

-- restart all database instances
$ srvctl start database -d <db_unique_name>

-- verify archiving is enabled/disabled by means of:
sql> archive log list;

You might need to set your log_archive_dest(_n) parameters to a shared location in each spfile, but the log_archive_start parameter does not need to be set anymore as of 10g (see Note 274302.1).

2. To disable archive logging, follow the same steps but use the NOARCHIVELOG clause of the ALTER DATABASE statement.

Please note, from 10.2 onwards, it is no longer required to modify cluster_database parameter to change archive log mode. Please refer to

Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide
What's New in Oracle Real Application Clusters Administration and Deployment?
"Changing the Archiving Mode
You no longer need to modify the CLUSTER_DATABASE parameter setting to change the archiving mode in Oracle RAC. You can change archive log mode as long as the database is mounted in the local instance and not open in any instances."

How To Enable/Disable Archive Log Mode in Real Application Cluster Environment (Doc ID 235158.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.0.1.0 to 10.1.0.5 [Release 9.0.1 to 10.1]
Information in this document applies to any platform.

GOAL

The note is to provide steps to enable/disable archivelog mode in RAC environment.

SOLUTION

1. Shut down all instances.

2. Set the CLUSTER_DATABASE parameter to false on one instance in the parameter
file. If using the server parameter file, make an entry for this:

     *.CLUSTER_DATABASE= False

For Modifying server parameter file (SPFILE):

Alter system set cluster_database=FALSE scope=spfile sid='*';

See Document 137483.1 How to Modify the Content of a SPFILE Parameter File

3. Set the LOG_ARCHIVE_START parameter to true.

4. Set the LOG_ARCHIVE_FORMAT and make sure the parameter containing the %t parameter includes the thread number in the archived logfile name.

5. Set the cluster database wide LOG_ARCHIVE_DEST_1 parameter in the parameter file as follows:

LOG_ARCHIVE_DEST_1='LOCATION=$ORACLE_BASE/oradata/<db_name>/archive'

Note: You can multiplex the destination to up to ten locations, refer to:
Document 66433.1 Oracle8i - Multiple Archive Destinations and Remote Archival

To specify the archive log destinations on a per instance basis for a two-instance cluster database, for example, set the parameter as follows:

   <sid1>.LOG_ARCHIVE_DEST_1='LOCATION=$ORACLE_BASE/oradata/<db_name>/archive'
   <sid2>.LOG_ARCHIVE_DEST_1='LOCATION=$ORACLE_BASE/oradata/<db_name>/archive'

Archive logs can be stored on the shared disk on platforms that support CFS (Cluster File System).  For more information on this see the following note: Document 183408.1   Raw Devices and Cluster Filesystems With Real Application Clusters

6. Mount the database (in exclusive mode) for the instance on which you have set CLUSTER_DATABASE to false.

7. Set the database in ARCHIVELOG mode :
    

     SQL> ALTER DATABASE ARCHIVELOG;

8. Shutdown the instance.

     SQL> SHUTDOWN IMMEDIATE;

9. Change the value of the CLUSTER_DATABASE parameter back to true.

10. Startup all instances.

To disable archive logging, follow the same steps but use the NOARCHIVELOG clause of the ALTER DATABASE statement.

stop and start RAC 10g/11g

Simple Formula Lister>instance>nodeapps>cluster



######################STOP###################################

## Stop listener

## Stop instance
srvctl stop instance -i orcl1 -d orcl -o immidiate
srvctl stop instance -i orcl2 -d orcl -o immidiate


## Stop Nodeapps
srvctl stop nodeapps -n rac1
srvctl stop nodeapps -n rac2

## Stop cluster
crsctl stop crs     (Node1 and Node2)



#####################START################

## Start cluster on both node
crsctl start crs     (on both node)


## Start nodeapps
srvctl start nodeapps -n rac1
srvctl start nodeapps -n rac2

##Start database instance
srvctl start instance -i orcl1 -d orcl
srvctl start instance -i orcl2 -d orcl

##Start listener


################RAC Userfull Commands##########

## ps -ef|grep smon

## ps -ef|grep ohasd

## ps -ef|grep d.bin

## crs_stat -t

## crsctl check crs

## srvctl status nodeapps -n rac1

## srvctl status instance -i orcl1 -d orcl

ORA-15001: DISKGROUP “DATA” DOES NOT EXIST OR IS NOT MOUNTED

Problem:

ORA-00210: cannot open the specified control file
ORA-00202: control file: '+DATA/kbazerdr/controlfile/current.256.825098041'
ORA-17503: ksfdopn:2 Failed to open file +DATA/kbazerdr/controlfile/current.256.825098041
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-205 signalled during: ALTER DATABASE MOUNT STANDBY DATABASE...

Analyze:

1) check if CRS is running on the node

$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

2) check if ASM instance is running

sys@+asm> select status from v$instance;
STATUS
————
STARTED

3) check diskgroup status

bash-3.2$ ps -afe | grep smon
 oracle 1788 1 0 Sep 24 ? 15:48 asm_smon_+ASM
 oracle 2807 2799 0 20:33:44 pts/4 0:00 grep smon
bash-3.2$ . oraenv
ORACLE_SID = [KBAZER] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
bash-3.2$ asmcmd
ASMCMD> ls
ASMCMD> 
ASMCMD> 
ASMCMD> exit
bash-3.2$ 
bash-3.2$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 31 20:35:11 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
SQL> 
SQL> 
SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> select name, state from v$asm_diskgroup;

NAME STATE
------------------------------ -----------
DATA DISMOUNTED
ARCH DISMOUNTED

SQL> show parameter asm_diskgroups;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups string ARCH

Solution:

1) manually mount the diskgroups (sqlplus / as sysasm)

SQL> alter diskgroup DATA mount;
Diskgroup altered.

SQL> alter diskgroup ARCH mount;
Diskgroup altered.

SQL> select name, state from v$asm_diskgroup;

NAME STATE
------------------------------ -----------
DATA MOUNTED
ARCH MOUNTED

SQL> show parameter asm_diskgroups;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups string ARCH, DATA

srvctl stop asm failed on 11gR2

when shutting down ASM using "srvctl stop asm -n linix101", I got this error. 

PRCR-1014 : Failed to stop resource ora.asm
PRCR-1065 : Failed to stop resource ora.asm
CRS-2673: Attempting to stop 'ora.asm' on 'linux101'
ORA-15097: cannot SHUTDOWN ASM instance with connected client
CRS-2675: Stop of 'ora.asm' on 'linux101' failed


The reason you need to stop crs therefore stopping ASM is your OCR is located on ASM disks. Thus ASM can't be shutdown while cluster is running. 

See metalink 984663.1.

this error may occur if my ocr and voting disk were residing on ASM then it will not allow me to stop the asm and I should use (Check below links)

www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=43116

But I have verified my ocr file and voting disk are residing on ASM.

[root@RAC1 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :    8381156
         Used space (kbytes)      :       6060
         Available space (kbytes) :    8375096
         ID                       : 1141966925
         Device/File Name         : /dev/raw/raw1
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded


[root@RAC1 bin]# ./crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   03db5164ffb96f85ff95896b184eb362 (/dev/raw/raw2) []
Located 1 voting disk(s).



From 11g R2 onwards, ASM disk groups are now registered as resources within Grid Infrastructure and the dependency has been redefined, means the dependency is already set in resource definition. This dependency is created with one or more ASM disk groups.

For ex.
-------
[oracle@london1 ~]$ crsctl status resource ora.prod.db -p
NAME=ora.prod.db
TYPE=ora.database.type
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
[...]
SPFILE=+DATA/prod/spfileprod.ora
START_DEPENDENCIES=hard(ora.DATA.dg) [...] pullup(ora.DATA.dg)
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg)

You can see that a start dependency has been created for the disk group DATA, which will also be started if needed as part of the database start. Similarly, a stop dependency exists for the same disk group. The hard dependency at "ora.asm" shows that the database must be shutdown before stopping the ASM.

Note that not only database, but other resources may also have dependency on the ASM, like ACFS. So if you use "force" option to execute the stop command on ASM, still some components won't stop, due to too many dependencies on other resources.

So, to stop ASM in 11gR2, "crsctl stop crs" or "crsctl stop cluster" is the only option to stop the ASM, since it first stop all the dependent resources the stop the ASM.

So, I think in my case, there must be some resources which depends on ASM, like my database files would be on ASM.

Resize your Oracle datafiles down to the minimum without ORA-03297

Your datafiles have grown in the past but now you want to reclaim as much space as possible, because you are short on filesystem space, or you want to move some files without moving empty blocks, or your backup size is too large. ALTER DATABASE DATAFILE … RESIZE can reclaim the space at the end of the datafile, down to the latest allocated extent.

But if you try to get lower, you will get:

ORA-03297: file contains used data beyond requested RESIZE value




So, how do you find this minimum value, which is the datafile’s high water mark?
You have the brute solution: try a value. If it passes, then try a lower value. If it failed, then try a higher one.
Or there is the smart solution: find the datafile high water mark.

You can query DBA_EXTENTS to know that. But did you try on a database with a lot of datafiles? It runs forever. Because DBA_EXTENTS is doing a lot of joins that you don’t need here. So my query directly reads SYS.X$KTFBUE which is the underlying fixed table that gives extent allocation in Locally Managed Tablespaces.

Note that the query may take a few minutes when you have a lot of tables, because the information is on disk, in each segment header, in the bitmaps used by LMT tablepaces. And you have to read all of them.

Here is my query:

set linesize 1000 pagesize 0 feedback off trimspool on with hwm as ( -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents ) select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn ), hwmts as ( -- join ts# with tablespace_name select name tablespace_name,relative_fno,hwm_blocks from hwm join v$tablespace using(ts#) ), hwmdf as ( -- join with datafiles, put 5M minimum for datafiles with no extents select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes from hwmts right join dba_data_files using(tablespace_name,relative_fno) ) select case when autoextensible='YES' and maxbytes>=bytes then -- we generate resize statements only if autoextensible can grow back to current size '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999) ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ ' ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;' else -- generate only a comment when autoextensible is off '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999) ||'M from '||to_char(ceil(bytes/1024/1024),999999) ||'M after setting autoextensible maxsize higher than current size for file ' || file_name||' */' end SQL from hwmdf where bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed order by bytes-hwm_bytes desc /










and here is a sample output:




/* reclaim    3986M from    5169M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_undotbs1_o9pfojva_.dbf' resize 1183M;

/* reclaim    3275M from   15864M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_apcpy_o5pfojni_.dbf' resize 12589M;

/* reclaim    2998M from    3655M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_cpy_qt_oepfok3n_.dbf' resize 657M;

/* reclaim    2066M from    2250M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_undotbs2_olpfokc9_.dbf' resize 185M;

/* reclaim     896M from    4000M */ alter database datafile '/u01/oradata/DB1USV/datafile/o1_mf_cpy_ocpfok3n_.dbf' resize 3105M;

You get directly the resize statements, with the reclaimable space in comments.

A few remarks about my query:

• I generate the resize statements only for datafiles which are autoextensible. This is because I want to be sure that the datafiles can grow back to their original size if needed.
• When datafile is not autoextensible, or maxsize is not higher than the current size, I only generate a comment.
• When a datafile has no extents at all I generate a resize to 5MB. I would like to find the minimum possible size (without getting ORA-3214) but my test do not validate yet what is documented in MOS. If anyone has an idea, please share.
• There is probably a way to get that high water mark in a cheaper way. Because the alter statement gives the ORA-03297 much quicker. Information is probably available in the datafile headers, without going to segment headers, but I don’t know if it is exposed in a safe way. If you have an idea, once again, please share.
•