ASM Standalone Database creation using DBCA Tool - ORACLEAGENT BLOG

Backup Based Physical Standby Configuration

Backup Based Physical Standby Configuration Manually

Description:
In this blog we are going to see step by step configuration and demo of oracle database primary to physical standby.

Backup Based Standby Configuration:
Mostly we have configure standby database in active data guard method. using level 0 backup we configure physical standby database.

Overall Steps:

1. check the role in primary database.
2. Take level 0 backup using RMAN.
3. Primary database parameter configuration.
4. Control and Pfile creation for standby database.
5. Create directory in standby database.
6. Copy archivelogs,control file,pfile, backupsets to standby database.
7. Standby configuration.
8. Bounce database and set the standby control file.
9. Connect RMAN and restore ,recover the database.
10. Check the database can be created.
11. Start the MRP process.
12. Check the Log sync status.

Step 1:- Check the database role in primary database.
SQL> select name,open_mode,database_role from v$database;
Step 2:- Take level 0 backup using RMAN.
RMAN> backup incremental level 0 database plus archivelog;
Step 3:- Primary database parameter configuration.

SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(cloud,local)’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_1=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=cloud’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_2=’service=local async valid_for=(online_logfiles,primary_role) db_unique_name=local’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET fal_server=’local’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET fal_client=’c’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=both;

System altered.

Step 4:- Create control file and Pfile for standby database.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/u01/app/oracle/oradata/CLOUD/standby.ctl’;

Database altered.

SQL> CREATE PFILE=’$ORACLE_HOME/dbs/initcompany.ora’ FROM SPFILE;

File created.

Step 5:- Create directory in standby database.
[oracle@standby ~]$ mkdir -p /u01/app/oracle/oradata/COMPANY/controlfile
[oracle@standby ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
mkdir -p /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17
[oracle@standby ~]$ mkdir -p /u01/app/oracle/admin/COMPANY/adump
/u01/app/oracle/fast_recovery_area/CLOUD/backupset/

Step 6:- Copy archivelogs,control file,pfile, backupsets to standby database.
Pfile and password file:
[oracle@primary dbs]$ scp orapwcloud [email protected]:$ORACLE_HOME/dbs
[email protected]'s password:
orapwcloud 100% 2048 2.1MB/s 00:00
[oracle@primary dbs]$ scp initcompany.ora [email protected]:$ORACLE_HOME/dbs
[email protected]'s password:
initcompany.ora 100% 1512 1.3MB/s 00:00
[oracle@primary dbs]$ cd
[oracle@primary ~]$ cd /u01/app/oracle/fast_recovery_area/
[oracle@primary fast_recovery_area]$ ls

Archivelogs:
/u01/app/oracle/fast_recovery_area/CLOUD/archivelog/2021_12_17
[oracle@primary 2021_12_17]$ ll
total 24288
-rw-rw----. 1 oracle oracle 22827008 Dec 17 07:38 o1_mf_1_2_jvqw80t4_.arc
-rw-rw----. 1 oracle oracle 2004480 Dec 17 07:39 o1_mf_1_3_jvqwcpbc_.arc
-rw-rw----. 1 oracle oracle 35840 Dec 17 07:41 o1_mf_1_4_jvqwglmx_.arc
[oracle@primary 2021_12_17]$ scp o1_mf_1_2_jvqw80t4_.arc [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17
[email protected]'s password:
o1_mf_1_2_jvqw80t4_.arc 100% 22MB 42.4MB/s 00:00
[oracle@primary 2021_12_17]$ scp o1_mf_1_3_jvqwcpbc_.arc [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17
[email protected]'s password:
Permission denied, please try again.
[email protected]'s password:
o1_mf_1_3_jvqwcpbc_.arc 100% 1958KB 63.2MB/s 00:00
[oracle@primary 2021_12_17]$ scp o1_mf_1_4_jvqwglmx_.arc [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17
[email protected]'s password:
o1_mf_1_4_jvqwglmx_.arc 100% 35KB 20.1MB/s 00:00
[oracle@primary 2021_12_17]$ cd ..
[oracle@primary archivelog]$ cd ..

Backup sets:
[oracle@primary backupset]$ cd 2021_12_17
[oracle@primary 2021_12_17]$ ll
total 2049948
-rw-rw----. 1 oracle oracle 519864832 Dec 17 07:40 o1_mf_annnn_TAG20211217T073959_jvqwcqrw_.bkp
-rw-rw----. 1 oracle oracle 196301312 Dec 17 07:40 o1_mf_annnn_TAG20211217T073959_jvqwdkp0_.bkp
-rw-rw----. 1 oracle oracle 24836608 Dec 17 07:40 o1_mf_annnn_TAG20211217T073959_jvqwds4r_.bkp
-rw-rw----. 1 oracle oracle 38912 Dec 17 07:41 o1_mf_annnn_TAG20211217T074130_jvqwgls4_.bkp
-rw-rw----. 1 oracle oracle 1358094336 Dec 17 07:41 o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp
[oracle@primary 2021_12_17]$ scp o1_mf_annnn_TAG20211217T073959_jvqwcqrw_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_annnn_TAG20211217T073959_jvqwcqrw_.bkp 100% 496MB 30.3MB/s 00:16 
[oracle@primary 2021_12_17]$ scp o1_mf_annnn_TAG20211217T073959_jvqwdkp0_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_annnn_TAG20211217T073959_jvqwdkp0_.bkp 100% 187MB 37.4MB/s 00:05 
[oracle@primary 2021_12_17]$ scp o1_mf_annnn_TAG20211217T073959_jvqwds4r_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_annnn_TAG20211217T073959_jvqwds4r_.bkp 100% 24MB 42.7MB/s 00:00 
[oracle@primary 2021_12_17]$ scp o1_mf_annnn_TAG20211217T074130_jvqwgls4_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_annnn_TAG20211217T074130_jvqwgls4_.bkp 100% 38KB 2.3MB/s 00:00 
[oracle@primary 2021_12_17]$ scp o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp 100% 1295MB 37.2MB/s 00:34 
[oracle@primary 2021_12_17]$ cd ../..
[oracle@primary CLOUD]$ ls

Step 7:- Standby Configuration.

1. Pfile parameter changes:
*.audit_file_dest='/u01/app/oracle/admin/cloud/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/CLOUD/controlfile/o1_mf_jvbn3nsp_.ctl','/u01/app/oracle/fast_recovery_area/CLOUD/controlfile/o1_mf_jvbn3nxt_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='cloud'
*.db_unique_name='company'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cloudXDB)'
*.fal_client='company'
*.fal_server='cloud'
*.log_archive_config='dg_config=(cloud,company)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=cloud'
*.log_archive_dest_2='service=company async valid_for=(online_logfiles,primary_role) db_unique_name=cloud'
*.log_archive_dest_state_2='ENABLE'
*.open_cursors=300
*.pga_aggregate_target=379m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1136m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

2. Start the Database using spfile:

[oracle@local dbs]$ export ORACLE_SID=company
[oracle@local dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Dec 17 08:17:06 2021
Version 19.3.0.0.0

Connected to an idle instance.

SQL> CREATE SPFILE FROM PFILE=’initcompany.ora’;

File created.

Step 8:- Bounce database and set the standby control file.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1191181696 bytes
Fixed Size 8895872 bytes
Variable Size 318767104 bytes
Database Buffers 855638016 bytes
Redo Buffers 7880704 bytes
Database mounted.
SQL> exit

SQL> alter system set control_files=’/u01/app/oracle/oradata/COMPANY/standby.ctl’ scope=spfile;

System altered.

Step 9:-Connect RMAN and restore ,recover the database.

RMAN> restore database;

Starting restore at 17-DEC-21
Starting implicit crosscheck backup at 17-DEC-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
Crosschecked 18 objects
Finished implicit crosscheck backup at 17-DEC-21

Starting implicit crosscheck copy at 17-DEC-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-DEC-21

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T073959_jvqwcqrw_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T073959_jvqwdkp0_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T073959_jvqwds4r_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T074130_jvqwgls4_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_2_jvqw80t4_.arc
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_3_jvqwcpbc_.arc
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_4_jvqwglmx_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_system_jvbmzxk8_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_sysaux_jvbn1c3g_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_undotbs1_jvbn24dz_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_users_jvbn25hj_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp tag=TAG20211217T074035
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 17-DEC-21

RMAN> recover database;

Starting recover at 17-DEC-21
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_4_jvqwglmx_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_4_jvqwglmx_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-DEC-21

RMAN> exit

Step 10:- Check the database can be created.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
CLOUD MOUNTED

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
CLOUD MOUNTED PHYSICAL STANDBY

Step 11:- Start the MRP process.

SQL> alter database recover managed standby database disconnect nodelay;

Database altered.

Step 12:- Log sync status.

switch logs in primary, defer and enable the dest.

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
SQL>

check the status in standby.

SQL> alter database recover managed standby database disconnect nodelay;

Database altered.

SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; 2 3 4

Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1 9 9 0

SQL>

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

ASM Configuration for Standalone Database

Oracle ASM standalone configuration

Description:
In this blog, we are going to see the step-by-step configuration for standalone ASM using the oracle restart method.

Standalone Database:
The Oracle Grid Infrastructure for a standalone server is the Oracle software that provides system support for an Oracle database including volume management, file system, and automatic restart capabilities.

ASM server:
Automatic Storage Management (ASM) is an integrated, high-performance database file system and disk manager. ASM is based on the principle that the database should manage storage instead of requiring an administrator to do it.

High Level Steps

Adding disk group in Virtual Machine
Grid software Installation
Oracle software installation
ASM standalone DB using the DBCA tool.

Adding Disk Group in Virtual Machine:

Step 1: open virtual machine hardware menu clicks add button:

Step 2:- Select an independent option and click next:

Step 3:- select create a new virtual disk and click next:

Step 4:- select allocate all disk space now option:

Step 5:- Select the folder and create the Disk:

ASM Hard Disk added:

Step 6:- OS-level disk partition :
Power on the virtual machine and check fdisk -l
Partition the disks:
fdisk/dev/sdb
Configure ASMLib using the following command.
oracleasm configure -i
create disk in OS level
/usr/sbin/oracleasm init
/usr/sbin/oracleasm createdisk DISK1 /dev/sdb1

GRID installation pre requests:

Check pre-install
Yum search preinstall

yum install oracle-database-preinstall-19c.x86_64 -y

yum install kmod-oracleasm.x86_64

yum install oracleasm-support.x86_64

Create a grid home directory and unzip the grid home

Add asm groups
[root@asm ~]# groupadd -g 54327 asmdba
[root@asm ~]# groupadd -g 54328 asmoper
[root@asm ~]# groupadd -g 54329 asmadmin
usermod -g oinstall -G dba,oper,backupdba,dgdba,kmdba,asmdba,asmoper,asmadmin,racdba oracle

Install the package cvudisk :
rpm -ivh /u01/app/grid/cv/rpm/cvuqdisk-1.0.10-1.rpm

Run the script: