Thursday, June 15, 2017

Oracle Local Registry [OLR]


Introduction:-

In Oracle Clusterware 11g release 2 (11.2), each node in a cluster has a local registry for node-specific resources, called an Oracle Local Registry (OLR), that is installed and configured when Oracle Clusterware installs OCR. OLR is local registry for node specific resources. OLR is not shared by other nodes in cluster. Multiple processes on each node have simultaneous read and write access to the OLR particular to the node on which they reside, regardless of whether Oracle Clusterware is running or fully functional.


Purpose Of OLR:-

It is the very first file that is accessed while starting clusterware when OCR is stored on ASM. OCR should be available to find out the resources which should be up on a node. If OCR is on ASM, it can’t be read until ASM (which itself is a resource for the node and this information is stored in OCR) is up. To solve this issue, information about the resources which need to be started on a node is stored in an operating system  file which is called OLR. Since OLR is operating system file, it can be accessed by various processes on the node for read/write irrespective of the status of the clusterware (up/down).
Hence, when  a node joins the cluster,OLR on that node is read and various resources including ASM are started on the node. Once ASM is up,OCR is accessible and is used henceforth to manage all the clusterware resources.If OLR is missing or corrupted, clusterware can’t be started on that node!


Details :-

With the OCR and Voting disks being placed in ASM, these files are accessible only when the cluster processes have started up and ASM instance is up and running. However without the OCR file the clusterware processes cannot start up. To resolve this, a copy of the Oracle Local Registry(OLR) registry is now created on each node during the initial installation which stores node specific information and allows us to get around the problem. OLR stores the information on the clusterware configuration, version information, and GpnP wallets. OHASD process mostly manages OLR. The OCR in turn is managed by the CRSD processes. The OLR is stored in GRID_HOME/cdata and must be available on the node before other services can be started up.Without this the clusterware will fail to start.


Location:-

The OLR location is stored in '/etc/oracle/olr.loc' or '/var/opt/oracle/olr.loc' depending on platform.


#The default location after installing Oracle Clusterware is:-

GI Cluster: <GI_HOME>/cdata/<hostname.olr>
GI Standalone (Oracle Restart): <GI_HOME>/cdata/localhost/<hostname.olr>

#To check the location using command (as oracle user):-
[oracle@rac1 ~]$ ocrcheck -local -config
Oracle Local Registry configuration is :
         Device/File Name         : /u01/app/11.2.0/grid/cdata/rac1.olr

#To check the location using command (as root user):-
[root@rac1 ~]# ocrcheck -local
Status of Oracle Local Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2372
         Available space (kbytes) :     259748
         ID                       : 2017112488
         Device/File Name         : /u01/app/11.2.0/grid/cdata/rac1.olr
                                    Device/File integrity check succeeded

         Local registry integrity check succeeded

         Logical corruption check succeeded



What does OLR contain?

Information stored in the OLR is needed by OHASD to start or join a cluster.
The OLR stores data about :-
ORA_CRS_HOME
localhost version
active version
GPnP details
OCR latest backup time and location
information about OCR daily, weekly backup location
node name etc.


Commands :-


To display the content of OLR on the local node to the text terminal that initiated the program using the OCRDUMP utility
# ocrdump -local -stdout
# ocrdump -h

To export OLR to a file
# ocrconfig –local –export file_name

Note :-
Oracle recommends that you use the -manualbackup and -restore commands and not the -import and -export commands.When exporting OLR, Oracle recommends including "olr", the host name, and the timestamp in the name string. For example: olr_myhost1_20090603_0130_export

To import a specified file to OLR
# ocrconfig -local –import file_name

To manually back up OLR:(root)
# ocrconfig -local -manualbackup

Note:-
The OLR is backed up at the end of an installation or an upgrade. After that time, you can only manually back up the OLR. Automatic backups are not supported for the OLR. You should create a new backup when you migrate OCR from Oracle ASM to other storage, or when you migrate OCR from other storage to Oracle ASM. The default backup location for the OLR is in the path Grid_home/cdata/host_name.

To view the contents of the OLR backup file
#ocrdump -local -backupfile olr_backup_file_name

To change the OLR backup location
#ocrconfig -local -backuploc new_olr_backup_path

To List Available Backup
#ocrconfig -local -showbackup

Note:- 
Clusterware maintains the history of the five most recent manual backups and will not update/delete a manual backups after it has been created. Below command shows manual backups in the registry though they are removed or archived manually in OS file system by OS commands

[oracle@rac1 rac1]$ ocrconfig -local -showbackup

rac1    /u01/app/11.2.0/grid/cdata/rac1/backup_20170307_060226.olr
rac1    /u01/app/11.2.0/grid/cdata/rac1/backup_20170307_060225.olr
rac1    /u01/app/11.2.0/grid/cdata/rac1/backup_20170307_060222.olr
rac1    /u01/app/11.2.0/grid/cdata/rac1/backup_20170307_060220.olr
rac1    /u01/app/11.2.0/grid/cdata/rac1/backup_20170307_060218.olr

Here we can see list of 5 latest backups but physically we have only 3 latest backups available.
      
[oracle@rac1 rac1]$ ls -ltr
total 18964
-rw------- 1 root root 6447104 Mar  7 06:02 backup_20170307_060222.olr
-rw------- 1 root root 6467584 Mar  7 06:02 backup_20170307_060225.olr
-rw------- 1 root root 6467584 Mar  7 06:02 backup_20170307_060226.olr

Restoring OLR
We need to follow few steps to restore OLR, which have been described in my post "How To Restore OLR" Here .



References :-
https://docs.oracle.com/cd/E11882_01/rac.112/e41959/votocr.htm#CWADD837


Tuesday, June 6, 2017

What to do if password of SYS got changed on primary database

I have changed the password of SYS user on primary database in RAC configuration.


Questions in your mind ????

How shall the password for SYS user be changed in the Standby database?

Can the password file be copied to standby while instances are up and running?


Let's assume following setup in the RAC Data Guard environment:
Primary: 2 node RAC, instance name: orcl1 and orcl2
Standby: 2 node RAC, instance name: orclp1 and orclp2


Key points to note :-

1. You must already know that for RAC instance, both password files on both nodes should be identical except the name eg:orapworcl1 and orapworcl2.
They can be copied from 1 instance to the other, then rename the name to match the SID used on that node.

2. From 11g onwards, due to strong password authentication, when creating physical standby database, no matter it is single or RAC, password file needs to be physically copied from either of the primary instance, then renamed to match the SID for physical standby, eg:
On standby_host1:
$ scp oracle@<primary_host1>:<$ORACLE_HOME>/dbs/orapworcl1 <$ORACLE_HOME>/dbs
$ mv $ORACLE_HOME/dbs/orapworcl1 $ORACLE_HOME/dbs/orapworclp1

On standby_host2:
$ scp oracle@<primary_host1>:<$ORACLE_HOME>/dbs/orapworcl1 <$ORACLE_HOME>/dbs
$ mv $ORACLE_HOME/dbs/orapworcl1 $ORACLE_HOME/dbs/orapworclp2


Two cases might occur :-


1. SYS user password is altered on primary instance

2. Password file on primary has been recreated via orapwd command

3. alert log will report (on both primary instances):
Sun Mar 26 15:20:43 2010
Error 1031 received logging on to the standby
Errors in file /u01/diag/rdbms/orcl/orcl1/trace/tl1_arc10_97329.trc:
ORA-01031: insufficient privileges
FAL[server, ARC3]: FAL archive failed, see trace file.



Solution:


1. Copy (scp) the orapw<SID> file from any of the primary instance to all standby instances:
$ scp $ORACLE_HOME/dbs/orapworcl1 oracle@<standby_host1>:<$ORACLE_HOME>/dbs/
$ scp $ORACLE_HOME/dbs/orapworcl1 oracle@<standby_host2>:<$ORACLE_HOME>/dbs/

2. Rename the password file on all standby nodes to match the standby SID.
on standby_host1:
mv $ORACLE_HOME/dbs/orapworcl1 $ORACLE_HOME/dbs/orapworclp1
on standby_host2:
mv $ORACLE_HOME/dbs/orapworcl1 $ORACLE_HOME/dbs/orapworclp2

3. It is not required to shutdown/restart any of the primary or standby instances.

4. Log shipping will resume automatically after this.
If not, alter parameter log_archive_dest_state_<n> to defer, then enable.
SQL> alter system set log_archive_dest_state_2=defer scope=both sid='*';
SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*';



Migrate Database from Non-ASM to ASM


Assumption(s):
Existing Database Name: - test_db (non-asm)
New Database Name: - test_db (asm)

SQL> select name from v$datafile;
NAME
------------------------------------------------------------
/data/mount01/test_db/system_01.dbf
/data/mount01/test_db/sysaux_01.dbf
/data/mount01/test_db/undo_t01_01.dbf
/data/mount01/test_db/tools_t01_01.dbf
/data/mount01/test_db/users_t01_01.dbf
/data/mount01/test_db/xdb_01.dbf
/data/mount01/test_db/test_c.dbf

7 rows selected.


SQL> select member from v$logfile;
MEMBER
----------------------------------------------------------------------
/data/mount03/test_db/ora_log_03_01.rdo
/data/mount03/test_db/ora_log_03_02.rdo
/data/mount03/test_db/ora_log_02_01.rdo
/data/mount03/test_db/ora_log_02_02.rdo
/data/mount03/test_db/ora_log_01_01.rdo
/data/mount03/test_db/ora_log_01_02.rdo

6 rows selected.

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING
ERROR at line 1:
ORA-19759: block change tracking is not enabled

SQL> show parameter db_create_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string


SQL>  show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0.2/test_db/dbs/spfitetest_db.ora

SQL> !ls -ltr /u01/app/oracle/product/12.1.0.2/test_db/dbs/spfiletest_db.ora
-rw-r----- 1 oracle dba 4608 Dec 27 00:00 /u01/app/oracle/product/12.1.0.2/test_db/dbs/spfiletest_db.ora

/*
  This parameter (db_create_file_dest ) define the default location for data files,   control_files etc, if no location for these files is specified  at the time of their creation.
*/

SQL> alter system set db_create_file_dest='+DG1' scope=spfile;
System altered.

/*
If you set db_create_online_log_dest_n, controlfile will get created at the location specified by db_create_online_log_dest. the database does not create a control file in DB_CREATE_FILE_DEST or in DB_RECOVERY_FILE_DEST
We skipped this step as redo log creating in diskgroup can be taken care later.
SQL> alter system set db_create_online_log_dest_1='XXX' scope=spfile;
System altered.
“Specifying Control Files at Database Creation”
*/

SQL> SHOW PARAMETER control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /data/mount03/test_db/control1.ctl

/*
   Here we removed the control_files parameter from spfile.  So next time we restore the control file it will automatically go to +DG1 diskgroup since it is defined in db_create_file_dest, and the new path will be automatically updated in spfile.
*/
SQL> alter system reset control_files scope=spfile sid='*';
System altered.

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             671091792 bytes
Database Buffers          159383552 bytes
Redo Buffers                2371584 bytes
SQL> SHOW PARAMETER control_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/product/12.1.0.2/test_db/dbs/cntrtest_db.dbf ----Dummy Controlfile

SQL> show parameter db_create_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DG1

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

$ ./rman target /

RMAN> restore controlfile from '/data/mount03/test_db/control1.ctl';

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

channel ORA_DISK_1: copied control file copy
output file name=+DG1/test_db/controlfile/current.301.900620801
Finished restore at 08-JAN-16

RMAN> alter database mount;
database mounted
RMAN>run
{
BACKUP AS COPY DATAFILE 1 FORMAT "+DG2";
BACKUP AS COPY DATAFILE 2 FORMAT "+DG2";
BACKUP AS COPY DATAFILE 3 FORMAT "+DG1";
BACKUP AS COPY DATAFILE 4 FORMAT "+DG1";
BACKUP AS COPY DATAFILE 5 FORMAT "+DG1";
BACKUP AS COPY DATAFILE 6 FORMAT "+DG1";
BACKUP AS COPY DATAFILE 7 FORMAT "+DG1";
}

RMAN> report schema;

Report of database schema for database with db_unique_name TEST_DB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    500      SYSTEM               ***     /data/mount01/test_db/system_01.dbf
2    500      SYSAUX               ***     /data/mount01/test_db/sysaux_01.dbf
3    1000     UNDO_T01             ***     /data/mount01/test_db/undo_t01_01.dbf
4    100      TOOLS_T01            ***     /data/mount01/test_db/tools_t01_01.dbf
5    1024     USERS_T01            ***     /data/mount01/test_db/users_t01_01.dbf
6    200      XDB                  ***     /data/mount01/test_db/xdb_01.dbf
7    100      TEST_C               ***     /data/mount01/test_db/test_c.dbf

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DG2/test_db/datafile/system.294.900618889"
datafile 2 switched to datafile copy "+DG2/test_db/datafile/sysaux.300.900618895"
datafile 3 switched to datafile copy "+DG1/test_db/datafile/undo_t01.297.900618897"
datafile 4 switched to datafile copy "+DG1/test_db/datafile/tools_t01.301.900618905"
datafile 5 switched to datafile copy "+DG1/test_db/datafile/users_t01.257.900618907"
datafile 6 switched to datafile copy "+DG1/test_db/datafile/xdb.267.900618913"
datafile 7 switched to datafile copy "+DG1/test_db/datafile/test_c.268.900618917"





RMAN> run
 { set newname for tempfile 1 to "+DG1";
   switch tempfile all;
 }

executing command: SET NEWNAME
renamed tempfile 1 to +DG1 in control file

RMAN> alter database open;
database opened

RMAN> report schema;

Report of database schema for database with db_unique_name TEST_DB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    500      SYSTEM               ***        +DG2/test_db/datafile/system.297.900620831
2    500      SYSAUX               ***         +DG2/test_db/datafile/sysaux.298.900620837
3    1000     UNDO_T01             ***    +DG1/test_db/datafile/undo_t01.299.900620839
4    100      TOOLS_T01            ***     +DG1/test_db/datafile/tools_t01.296.900620847
5    1024     USERS_T01            ***    +DG1/test_db/datafile/users_t01.269.900620849
6    200      XDB                  ***            +DG1/test_db/datafile/xdb.268.900620855
7    100      TEST_C               ***         +DG1/test_db/datafile/test_c.267.900620857

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    500      TEMP_T01             500         +DG1/test_db/tempfile/temp_t01.257.900620955

Update the redo log file location from non-asm to asm

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP# MEMBER                                              
---------- -------------------------------------------------------
         3 /data/mount01/test_db/ora_log_03_01.rdo         
         3 /data/mount01/test_db/ora_log_03_02.rdo         
         2 /data/mount01/test_db/ora_log_02_01.rdo         
         2 /data/mount01/test_db/ora_log_02_02.rdo         
         1 /data/mount01/test_db/ora_log_01_01.rdo         
         1 /data/mount01/test_db/ora_log_01_02.rdo         
6 rows selected.

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.

SQL> ALTER DATABASE ADD LOGFILE group 3 ('+REDO1');
Database altered.

SQL>  ALTER DATABASE ADD LOGFILE MEMBER '+REDO2' TO GROUP 3;
Database altered.

SQL>  ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.

SQL>  ALTER DATABASE ADD LOGFILE group 2 ('+REDO1');
Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+REDO2' TO GROUP 2;
Database altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP# MEMBER                                              
---------- ----------------------------------------------------------
         3 +REDO1/test_db/onlinelog/group_3.257.898874349         
         3 +REDO2/test_db/onlinelog/group_3.269.898874371        
         2 +REDO1/test_db/onlinelog/group_2.268.898874411        
         2 +REDO2/test_db/onlinelog/group_2.267.898874417        
         1 /data/mount01/test_db/ora_log_01_01.rdo         
         1 /data/mount01/test_db/ora_log_01_02.rdo         

6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> alter system checkpoint;
System altered.
SQL>  ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE group 1 ('+REDO1');
Database altered.
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+REDO2' TO GROUP 1;
Database altered.
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP# MEMBER                                       
---------- ---------------------------------------------
         3 +REDO1/test_db/onlinelog/group_3.257.898874349
         3 +REDO2/test_db/onlinelog/group_3.269.898874371
         2 +REDO1/test_db/onlinelog/group_2.268.898874411
         2 +REDO2/test_db/onlinelog/group_2.267.898874417
         1 +REDO1/test_db/onlinelog/group_1.266.898874499
         1 +REDO2/test_db/onlinelog/group_1.265.898874509

Multiplex Controlfile

SQL> select name from v$controlfile;
NAME
--------------------------------------------------
+DG1/test_db/controlfile/current.301.900620801

SQL> alter system set control_files='+DG1/test_db/controlfile/current.301.900620801','+REDO1','+DG1' scope=spfile sid='*';

System altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>  startup nomount
ORACLE instance started.
Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             671091792 bytes
Database Buffers          159383552 bytes
Redo Buffers                2371584 bytes

$ ./rman target /


RMAN> restore controlfile from '+DG1/test_db/controlfile/current.301.900620801';

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

channel ORA_DISK_1: copied control file copy
output file name=+DG1/test_db/controlfile/current.301.900620801
output file name=+REDO1/test_db/controlfile/current.272.900623351
output file name=+DG1/test_db/controlfile/current.304.900623351
Finished restore at 08-JAN-16

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> alter database open;
database opened

SQL> select name from v$controlfile;
NAME
----------------------------------------
+DG1/test_db/controlfile/current.301.900620801
+REDO/test_db/controlfile/current.272.900623351
+DG1/test_db/controlfile/current.304.900623351

Enable Block change tracking

SQL> select status from V$BLOCK_CHANGE_TRACKING;
 STATUS
----------
DISABLED
 SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;
 FILENAME
-----------------------------------------------------------------------------  
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
 Database altered.

SQL>  select status from V$BLOCK_CHANGE_TRACKING;
 STATUS
----------
ENABLED
SQL>  SELECT filename FROM V$BLOCK_CHANGE_TRACKING;
 FILENAME
-----------------------------------------------------------------------------
+DG1/test_db/changetracking/ctf.563.900723605

Move spfile in diskgroup

SQL>create pfile=’/tmp/inittest_db.ora’ from spfile;

SQL>create spfile=’+DG1’ from pfile=’/tmp/inittest_db.ora’;


------------------------------------------------End of Document-----------------------------------

Note :- When I used below script
RMAN>run {
BACKUP AS COPY DATAFILE 7 FORMAT "+REDO";
BACKUP AS COPY DATABASE FORMAT “+DG1”;
}
RMAN> SWITCH DATABASE TO COPY;

All the datafiles including datafile 7 was present in “+DG1” diskgroup only. So we should do the mapping for each datafile with their respective diskgroup.