A Guide : Patch Oracle 19c Grid Infrastructure

   To patch Oracle Grid Infrastructure 19c, follow these detailed steps. This guide assumes you have already downloaded and unzipped the necessary patch files.

20 ASM Realtime | Interview Questions | 3+ years

Download link : https://www.scribd.com/document/461028011/Asm-Interview-Scenarios-Oracle-dba-Help-Blog

Happy Learning ..!

How to make a cold backup for database running on ASM ( using RMAN )

Many a times, you will come in a situation… Or they may ask you in Interview that how to take a cold backup using RMAN.

You heard Right, Cold backup using RMAN…

Using RMAN Utility, we can perform the backup in MOUNT mode.

Lets see…

Follow the below steps to take cold back of database running on ASM.

1.Start the instances in all the nodes in mount stage.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1.7504E+10 bytes

Fixed Size                  2235448 bytes

Variable Size            4429185992 bytes

Database Buffers         1.3019E+10 bytes

Redo Buffers               53477376 bytes

Database mounted.

2.Connect RMAN from any node and take the backup as below.

[oracle@backupServer ]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon May 2 05:53:43 2016

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

connected to target database: SPSHIDMP (DBID=465850406, not open)

RMAN> backup device type disk format '/backupServer/test/rman_backup_43/backup/%U' database plus archivelog;

Starting backup at 02-MAY-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=4566 instance=spshidmp1 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=2 sequence=55 RECID=107 STAMP=783110838

input archived log thread=1 sequence=58 RECID=106 STAMP=783107220

input archived log thread=1 sequence=59 RECID=108 STAMP=783139625

input archived log thread=2 sequence=56 RECID=109 STAMP=783155353

input archived log thread=1 sequence=60 RECID=112 STAMP=783164816

3.Once done, start the instances on all the nodes.

SQL> alter database open;

Database altered

We can take a database Backup using RMAN in Mount, this will be said as COLD-BACKUP

Creating ASM Instance and diskgroups manually without DBCA

create the password file :

[oracle@oracleasm1 dbs]$ orapwd file=orapw+ASM1 password=oracle entries=5

create required directories :

[oracle@oracleasm1 dbs]$ cd $ORACLE_BASE

[oracle@oracleasm1 oracle]$ cd admin/

[oracle@oracleasm1 admin]$ mkdir +ASM1

[oracle@oracleasm1 admin]$ cd +ASM1/

[oracle@oracleasm1 +ASM1]$ mkdir bdump

[oracle@oracleasm1 +ASM1]$ mkdir udump

[oracle@oracleasm1 +ASM1]$ mkdir dump

[oracle@oracleasm1 +ASM1]$ mkdir cdump

[oracle@oracleasm1 +ASM1]$ mkdir pfile

Create the init+ASM1.ora file:

Using vi editor or any other editor you like, create the init+ASM1.ora file under the $ORACLE_HOME/dbs directory and add the below lines into this file.

[oracle@oracleasm1 dbs]$ vi init+ASM1.ora

#background_dump_dest='/u01/app/oracle/admin/+ASM1/bdump'  //commented

core_dump_dest='/u01/app/oracle/admin/+ASM1/cdump'

instance_type='asm'

large_pool_size=12M

remote_login_passwordfile='SHARED'

#user_dump_dest='/u01/app/oracle/admin/+ASM1/udump'                  //commented

start the instance using that file :

[oracle@oracleasm1 dbs]$ . oraenv

ORACLE_SID = [orcl] ? +ASM1

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0/grid

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid is /u01/app/oracle

[oracle@oracleasm1 dbs]$ rlwrap sqlplus / as sysasm

SQL> startup pfile='/u01/app/oracle/product/11.2.0/grid/dbs/init+ASM1.ora' ;

ASM instance started

Total System Global Area  284565504 bytes

Fixed Size                    1336036 bytes

Variable Size                        258063644 bytes

ASM Cache                 25165824 bytes

ORA-15110: no diskgroups mounted

SQL> alter system set asm_diskstring='/dev/oracleasm/disks';

System altered.

SQL> select name,path,state,mount_status,header_status,group_number from v$asm_disk;

SQL> alter system set asm_diskgroups='NEWDATA','DATA';

System altered.

SQL> create diskgroup data_disk_grp

  2  disk '/dev/oracleasm/disks/NEWDATA' , '/dev/oracleasm/disks/DATAVOL1' ;

create diskgroup data_disk_grp

*

ERROR at line 1:

ORA-15018: diskgroup cannot be created

ORA-15033: disk '/dev/oracleasm/disks/DATAVOL1' belongs to diskgroup "DATA"

SQL> create diskgroup data_disk_grp

  2  disk '/dev/oracleasm/disks/NEWDATA' , '/dev/oracleasm/disks/DATAVOL1'  force;

Diskgroup created.

SQL> set linesize 100

SQL> col path format a30

SQL> select name, path from v$asm_disk where name is not null;

NAME                                          PATH

------------------------------      --------------------------------------------

DATA_DISK_GRP_0000        /dev/oracleasm/disks/NEWDATA

DATA_DISK_GRP_0001        /dev/oracleasm/disks/DATAVOL1

SQL>

SQL> select name, type, total_mb, free_mb from v$asm_diskgroup;

NAME                                          TYPE      TOTAL_MB    FREE_MB

------------------------------ ------ ---------- ----------

DATA                                                                   0    0

DATA_DISK_GRP                     NORMAL             12283      12181

Open the /etc/oratab file and add the following line at the end:

[oracle@oracleasm1 ~]$ vi /etc/oratab

+ASM1:/u01/app/oracle/product/11.2.0/grid:N

Recovery Related Doubts I faced During my Initial Stage

Q. Why do i have to take backup of redologfiles in hot backup ?

Q. Can i take backups without the redolog files and is it a valid backup ?

Q. If my database is noarchivelog mode and i put all the tablespace in hot backup can i still take hot backup ? and do restore ?

Answer :

You can take hot backup without redo log file and it is valid backup.

And if your database is in no archivelog mode then first of all you can’t take hot backup.

Moving a Single Datafile from File System to ASM System (METHOD 1)

Moving a Single Datafile from File System to ASM System 

Connect to sqlplus and create a new tablespace

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

+DATA/myasm/datafile/system.302.907085491

+DATA/myasm/datafile/sysaux.303.907085493

+DATA/myasm/datafile/undotbs1.304.907085495

+DATA/myasm/datafile/users.305.907085495

SQL> create tablespace demo datafile '/u01/demo2.dbf' size 10M;

Tablespace created.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

+DATA/myasm/datafile/system.302.907085491

+DATA/myasm/datafile/sysaux.303.907085493

+DATA/myasm/datafile/undotbs1.304.907085495

+DATA/myasm/datafile/users.305.907085495

/u01/demo2.dbf

SQL> alter tablespace demo offline;

Tablespace altered.

SQL> !rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 21 21:57:43 2016

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

connected to target database: MYASM (DBID=36010415)

RMAN> copy datafile '/u01/demo2.dbf' to '+DATA';

Starting backup at 21-MAR-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=42 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/u01/demo2.dbf

output file name=+DATA/myasm/datafile/demo.319.907106321 tag=TAG20160321T215829 RECID=1 STAMP=907106329

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26

Finished backup at 21-MAR-16

RMAN> exit

Recovery Manager complete.

SQL> alter database rename file '/u01/demo2.dbf' to '+DATA/myasm/datafile/demo.319.907106321' ;

Database altered.

SQL> alter tablespace demo online;

Tablespace altered.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

+DATA/myasm/datafile/system.302.907085491

+DATA/myasm/datafile/sysaux.303.907085493

+DATA/myasm/datafile/undotbs1.304.907085495

+DATA/myasm/datafile/users.305.907085495

+DATA/myasm/datafile/demo.319.907106321

6 rows selected.

SQL>

Moving a Single Datafile from File System to ASM System (METHOD 2)

Condition ,

-  If you want to move SYSTEM & UNDO tablespace, DB stage is mounted.

-  If you want to other tablespace datafiles , Tablespace should be OFFLINE

Step 1:

Check datafile & ASM diskgroup size

SQL> col FILE_NAME format a40

SQL> select tablespace_name , file_name from dba_data_files;

TABLESPACE   FILE_NAME

---------------       --------------------------------

USERS                   +DATA/myasm/datafile/users.305.907085495

UNDOTBS1         +DATA/myasm/datafile/undotbs1.304.907085495

SYSAUX                +DATA/myasm/datafile/sysaux.303.907085493

SYSTEM                +DATA/myasm/datafile/system.302.90708541

USERS                  /u01/demo.dbf

TABLESPACE   FILE_NAME

---------------       --------------------------------

DEMO                  +DATA/myasm/datafile/demo.319.907106321

6 rows selected.

Step 2: Tablespace offline

SQL> alter tablespace users offline;

Tablespace altered.

Step 3:

Here i just moved Users tablespace datafile to ‘+DATA’ diskgroup.

SQL> !rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 21 22:15:46 2016

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

connected to target database: MYASM (DBID=36010415)

RMAN> copy datafile '/u01/demo.dbf' to '+DATA';

Starting backup at 21-MAR-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=47 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/demo.dbf

output file name=+DATA/myasm/datafile/users.320.907107373 tag=TAG20160321T221611 RECID=2 STAMP=907107378

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08

Finished backup at 21-MAR-16

Step 4 :

Updataing the controlfile

RMAN> switch datafile '/u01/demo.dbf' to copy;

datafile 5 switched to datafile copy "+DATA/myasm/datafile/users.320.907107373"

RMAN> exit

Recovery Manager complete.

Step 5:

Check datafile location

SQL> select file_name from dba_data_files;

TABLESPACE   FILE_NAME

---------------       --------------------------------

USERS                   +DATA/myasm/datafile/users.305.907085495

UNDOTBS1         +DATA/myasm/datafile/undotbs1.304.907085495

SYSAUX                +DATA/myasm/datafile/sysaux.303.907085493

SYSTEM              +DATA/myasm/datafile/system.302.90708541

USERS                  +DATA/myasm/datafile/users.320.907107373

TABLESPACE   FILE_NAME

---------------       --------------------------------

DEMO                  +DATA/myasm/datafile/demo.319.907106321

6 rows selected.

SQL>

Things to consider while adding new disk/ Rebalancing existing Disk Group

Take care for the following things while adding new disk OR Rebalancing existing Disk Group

1)         Make sure the disk are visible on all the cluster nodes; Disk readiness purely depend on the Os & version;

For ex:

Linux 5 – we will be using oracleasm as utility

Linux 6 – we will be using udev utility

2)         When you are planning to add disk to the disk group on the primary database, try to do it when database is having less load.

3)         Increase the ASM power limit to 9 or 10, depending on your environment. Once the rebalancing is completed, bring it to the original value.

Parameter è asm_power_limit

To increase or decrease the asm_power_limit

SQL> alter system set asm_power_limit=9;                 -- you need to issue it in the ASM instance.

4)         Add disks to one disk group at a time because rebalancing might cause the slowness to the database.

5)         Have a eye on the alert log of the ASM instance. If you are using cluster databases then you need to have a eye on all the instances in the cluster.

6)         You can monitor the rebalancing on the ASM instance using the below query

select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from gv$asm_operation;

7)         you can check the size of the diskgroup before & after adding the Diskgroup in the ASM.

$ asmcmd –p

ASMCMD> lsdg NIK_B_SYSTEM

8)         Messages in the alert, when the rebalancing starts & when it completes

Disk added on the Node#1 and final status updates on the Node#2

-On Node One

SQL> ALTER DISKGROUP NIK_B_SYSTEM ADD  DISK '/dev/mapper/NIK_B_SYSTEM_05' SIZE 51200M /* ASMCA */

NOTE: GroupBlock outside rolling migration privileged region

NOTE: Assigning number (21,4) to disk (/dev/mapper/NIK_B_SYSTEM_05)

NOTE: requesting all-instance membership refresh for group=21

NOTE: initializing header on grp 21 disk NIK_B_SYSTEM_0004

NOTE: requesting all-instance disk validation for group=21

Wed Aug 06 17:09:45 2014

NOTE: skipping rediscovery for group 21/0xdb38ead3 (NIK_B_SYSTEM) on local instance.

NOTE: requesting all-instance disk validation for group=21

NOTE: skipping rediscovery for group 21/0xdb38ead3 (NIK_B_SYSTEM) on local instance.

NOTE: initiating PST update: grp = 21

Wed Aug 06 17:09:51 2014

GMON updating group 21 at 9546 for pid 73, osid 16865

NOTE: PST update grp = 21 completed successfully

NOTE: membership refresh pending for group 21/0xdb38ead3 (NIK_B_SYSTEM)

GMON querying group 21 at 9547 for pid 18, osid 101970

NOTE: cache opening disk 4 of grp 21: NIK_B_SYSTEM_0004 path:/dev/mapper/NIK_B_SYSTEM_05

Wed Aug 06 17:09:56 2014

NOTE: Attempting voting file refresh on diskgroup NIK_B_SYSTEM

GMON querying group 21 at 9548 for pid 18, osid 101970

SUCCESS: refreshed membership for 21/0xdb38ead3 (NIK_B_SYSTEM)

Wed Aug 06 17:09:56 2014

SUCCESS: ALTER DISKGROUP NIK_B_SYSTEM ADD  DISK '/dev/mapper/NIK_B_SYSTEM_05' SIZE 51200M /* ASMCA */

NOTE: starting rebalance of group 21/0xdb38ead3 (NIK_B_SYSTEM) at power 9

Starting background process ARB0

Wed Aug 06 17:09:56 2014

ARB0 started with pid=74, OS id=91751

NOTE: assigning ARB0 to group 21/0xdb38ead3 (NIK_B_SYSTEM) with 9 parallel I/Os

NOTE: Attempting voting file refresh on diskgroup NIK_B_SYSTEM

Wed Aug 06 17:11:01 2014

-On Second One

NOTE: disk validation pending for group 21/0xdb38cf46 (NIK_B_SYSTEM)

SUCCESS: validated disks for 21/0xdb38cf46 (NIK_B_SYSTEM)

NOTE: disk validation pending for group 21/0xdb38cf46 (NIK_B_SYSTEM)

NOTE: Assigning number (21,4) to disk (/dev/mapper/NIK_B_SYSTEM_05)

SUCCESS: validated disks for 21/0xdb38cf46 (NIK_B_SYSTEM)

NOTE: membership refresh pending for group 21/0xdb38cf46 (NIK_B_SYSTEM)

Wed Aug 06 17:09:54 2014

GMON querying group 21 at 9721 for pid 18, osid 84704

NOTE: cache opening disk 4 of grp 21: NIK_B_SYSTEM_0004 path:/dev/mapper/NIK_B_SYSTEM_05

GMON querying group 21 at 9722 for pid 18, osid 84704

SUCCESS: refreshed membership for 21/0xdb38cf46 (NIK_B_SYSTEM)

Wed Aug 06 17:09:58 2014

NOTE: Attempting voting file refresh on diskgroup NIK_B_SYSTEM

Adding disk

1)         I am using asmca to add the disk to the existing Diskgroup.

2)         You need to plan what kind of redundancy you are opting for, if you are going for a new diskgroup.

3)   As soon as you added the disk in the asmca, you will be shown an output as “Disks added successfully to disk group DISK_GROUP_NAME”.
You need to see it in the sql prompt(on ASM instance) to know about the rebalancing status