Looking for something? Try here..

Tuesday, August 23, 2022

Oracle Centrally Managed Users (CMU) configuration

Centrally managed users (CMU) provides a simpler integration with Microsoft Active Directory to allow centralized authentication and authorization of users. 

Quote from official documentation
"In order for the Oracle Database CMU with Active Directory integration to work, the Oracle database must be able to login to a service account specifically created for the database in Active Directory. The database uses this service account to query Active Directory for user and group information when a user logs into the database. This Active Directory service account must have all the privileges required to query the user and group information as well as being able to write updates related to the password policies in Active Directory (for example, failed login attempts, clear failed login attempts). Users can authenticate using passwords, Kerberos, or PKI and either be assigned to an exclusive schema or a shared schema. Mapping of an Active Directory user to a shared schema is determined by the association of the user to an Active Directory group that is mapped to the shared schema. Active Directory groups can also be mapped to database global roles. An Active Directory security administrator can assign a user to groups that are mapped to shared database global users (schemas) and/or database global roles, and hence update privileges and roles that are assigned to the Active Directory user in a database."

In this post, I'll be explaining how to configure Oracle Centrally Managed Users using Windows server 2019 Active Directory and Oracle database version 19c. 


Since the configuration is a bit long with multiple screen shots I have made, I have divided the steps into 4 major parts which are as below

  • Part A: AD Configuration
  • Part B: Database Server Configuration
  • Part C: Enhancing to CMU
  • Part D: Enhancing CMU with Shared users and roles

All the steps are explained in the downloadable document. Click below link to download the file.

>>> Centrally Managed Users - orabliss.com <<<

Please make use of the document and share the feedback or any clarification if you have, I'll try to help solve them. 

I had to set up my own AD on virtual box for which I have not covered the steps in the document but the first 2 reference links will help you set up one if you need to.. 

References: 


Happy CMing Users!!! :)

Thursday, June 30, 2022

RMAN Merged incremental backups

 We are changing our backup solutions vendor in our company and the new vendor utilizes the RMAN merged incremental backup. So let's see what is this and what are the advantages of this RMAN capability. 

Merged Incremental creates a level 0 image copy backup of all of your database's datafiles in a disk location. All datafiles are backed up using the same tag name. Block Change Tracking can be used for the incremental level 1 backups for Fast Incremental Backups


Let's see a quick demo on how to perform a Merged Incremental backup
run {
allocate channel c1 device type disk format '/media/sf_Oracle/RMAN-training/%U';
recover copy of database with tag 'Demo_merged_l0';
backup incremental level 1 copies=1 for recover of copy with tag 'Demo_merged_l0' database;
}
This is the default strategy which OEM uses. Let's say we are scheduling backup every day at 12 pm, then the copy of datafiles will be 24 hrs behind. 

First day: Run 1
[oracle@linux-8 RMAN-training]$ rman target sys/oracle@odb1

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 30 01:10:09 2022
Version 19.10.0.0.0

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

connected to target database: CDB19:ODB1 (DBID=223390358)

RMAN> run {
allocate channel c1 device type disk format '/media/sf_Oracle/RMAN-training/%U';
recover copy of database with tag 'Demo_merged_l0';
backup incremental level 1 copies=1 for recover of copy with tag 'Demo_merged_l0' database;
}2> 3> 4> 5>

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=414 device type=DISK

Starting recover at 30-JUN-22
no copy of datafile 16 found to recover
no copy of datafile 17 found to recover
no copy of datafile 18 found to recover
no copy of datafile 19 found to recover
Finished recover at 30-JUN-22

Starting backup at 30-JUN-22
no parent backup or copy of datafile 18 found
no parent backup or copy of datafile 16 found
no parent backup or copy of datafile 17 found
no parent backup or copy of datafile 19 found
channel c1: starting datafile copy
input datafile file number=00018 name=+DATA/ODB1/DATAFILE/undotbs1.273.1084998671
output file name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-UNDOTBS1_FNO-18_kr11ag49 tag=DEMO_MERGED_L0 RECID=95 STAMP=1108689035
channel c1: datafile copy complete, elapsed time: 00:00:03
channel c1: starting datafile copy
input datafile file number=00016 name=+DATA/ODB1/DATAFILE/system.272.1084998669
output file name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-SYSTEM_FNO-16_ks11ag4c tag=DEMO_MERGED_L0 RECID=96 STAMP=1108689038
channel c1: datafile copy complete, elapsed time: 00:00:03
channel c1: starting datafile copy
input datafile file number=00017 name=+DATA/ODB1/DATAFILE/sysaux.274.1084998671
output file name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-SYSAUX_FNO-17_kt11ag4f tag=DEMO_MERGED_L0 RECID=97 STAMP=1108689040
channel c1: datafile copy complete, elapsed time: 00:00:01
channel c1: starting datafile copy
input datafile file number=00019 name=+DATA/CDB19/CD7591F2458BB5B4E053B538A8C08E48/DATAFILE/users.275.1108164853
output file name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-USERS_FNO-19_ku11ag4g tag=DEMO_MERGED_L0 RECID=98 STAMP=1108689040
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-JUN-22
released channel: c1

RMAN> exit


Recovery Manager complete.
[oracle@linux-8 RMAN-training]$ ls -lrt
...
-rwxrwx---. 1 root vboxsf 1447043072 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-UNDOTBS1_FNO-18_kr11ag49
-rwxrwx---. 1 root vboxsf 1258299392 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-SYSTEM_FNO-16_ks11ag4c
-rwxrwx---. 1 root vboxsf  650125312 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-SYSAUX_FNO-17_kt11ag4f
-rwxrwx---. 1 root vboxsf   41951232 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-USERS_FNO-19_ku11ag4g
 
We can see we get "no copy of database # found to recover" as this is the first run and we don't have any copy made before to recover. 
Also the backup command throws "no parent backup or copy of datafile # found" since we are triggering a incremental level 1 backup without a prior level 0 backup. In this case, Oracle will create a fresh level 0 copy of the datafiles. This can be identified using "channel c1: starting datafile copy" without the mention of incremental level 1 which we will see in the next run. 

Second day: Run 2

RMAN> run {
allocate channel c1 device type disk format '/media/sf_Oracle/RMAN-training/%U';
recover copy of database with tag 'Demo_merged_l0';
backup incremental level 1 copies=1 for recover of copy with tag 'Demo_merged_l0' database;
}2> 3> 4> 5>

allocated channel: c1
channel c1: SID=414 device type=DISK

Starting recover at 30-JUN-22
no copy of datafile 16 found to recover
no copy of datafile 17 found to recover
no copy of datafile 18 found to recover
no copy of datafile 19 found to recover
Finished recover at 30-JUN-22

Starting backup at 30-JUN-22
channel c1: starting compressed incremental level 1 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00018 name=+DATA/ODB1/DATAFILE/undotbs1.273.1084998671
input datafile file number=00016 name=+DATA/ODB1/DATAFILE/system.272.1084998669
input datafile file number=00017 name=+DATA/ODB1/DATAFILE/sysaux.274.1084998671
input datafile file number=00019 name=+DATA/CDB19/CD7591F2458BB5B4E053B538A8C08E48/DATAFILE/users.275.1108164853
channel c1: starting piece 1 at 30-JUN-22
channel c1: finished piece 1 at 30-JUN-22
piece handle=/media/sf_Oracle/RMAN-training/kv11agra_671_1_1 tag=DEMO_MERGED_L0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-JUN-22
released channel: c1

RMAN> exit
[oracle@linux-8 RMAN-training]$ ls -lrt
...
-rwxrwx---. 1 root vboxsf 1447043072 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-UNDOTBS1_FNO-18_kr11ag49
-rwxrwx---. 1 root vboxsf 1258299392 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-SYSTEM_FNO-16_ks11ag4c
-rwxrwx---. 1 root vboxsf  650125312 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-SYSAUX_FNO-17_kt11ag4f
-rwxrwx---. 1 root vboxsf   41951232 Jun 30 01:10 data_D-CDB19_I-3846007102_TS-USERS_FNO-19_ku11ag4g
-rwxrwx---. 1 root vboxsf    2482176 Jun 30 01:22 kv11agra_671_1_1

We can still see "no copy of database # found to recover" as we don't have a previous level 1 backup. Remember the run 1 created a level 0 backup. Now, second created a incremental level 1 backup which you can see from "starting compressed incremental level 1 datafile backup set" line and also from the files list kv11agra_671_1_1

Third day: Run 3 and above
RMAN> run {
allocate channel c1 device type disk format '/media/sf_Oracle/RMAN-training/%U';
recover copy of database with tag 'Demo_merged_l0';
backup incremental level 1 copies=1 for recover of copy with tag 'Demo_merged_l0' database;
}2> 3> 4> 5>

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=414 device type=DISK

Starting recover at 30-JUN-22
channel c1: starting incremental datafile backup set restore
channel c1: specifying datafile copies to recover
recovering datafile copy file number=00016 name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-SYSTEM_FNO-16_ks11ag4c
recovering datafile copy file number=00017 name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-SYSAUX_FNO-17_kt11ag4f
recovering datafile copy file number=00018 name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-UNDOTBS1_FNO-18_kr11ag49
recovering datafile copy file number=00019 name=/media/sf_Oracle/RMAN-training/data_D-CDB19_I-3846007102_TS-USERS_FNO-19_ku11ag4g
channel c1: reading from backup piece /media/sf_Oracle/RMAN-training/kv11agra_671_1_1
channel c1: piece handle=/media/sf_Oracle/RMAN-training/kv11agra_671_1_1 tag=DEMO_MERGED_L0
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
Finished recover at 30-JUN-22

Starting backup at 30-JUN-22
channel c1: starting compressed incremental level 1 datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00018 name=+DATA/ODB1/DATAFILE/undotbs1.273.1084998671
input datafile file number=00016 name=+DATA/ODB1/DATAFILE/system.272.1084998669
input datafile file number=00017 name=+DATA/ODB1/DATAFILE/sysaux.274.1084998671
input datafile file number=00019 name=+DATA/CDB19/CD7591F2458BB5B4E053B538A8C08E48/DATAFILE/users.275.1108164853
channel c1: starting piece 1 at 30-JUN-22
channel c1: finished piece 1 at 30-JUN-22
piece handle=/media/sf_Oracle/RMAN-training/l011ahbn_672_1_1 tag=DEMO_MERGED_L0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-JUN-22
released channel: c1

RMAN> exit
[oracle@linux-8 RMAN-training]$ ls -lrt
...
-rwxrwx---. 1 root vboxsf    2482176 Jun 30 01:22 kv11agra_671_1_1
-rwxrwx---. 1 root vboxsf   41951232 Jun 30 01:31 data_D-CDB19_I-3846007102_TS-USERS_FNO-19_ku11ag4g
-rwxrwx---. 1 root vboxsf  650125312 Jun 30 01:31 data_D-CDB19_I-3846007102_TS-SYSAUX_FNO-17_kt11ag4f
-rwxrwx---. 1 root vboxsf 1447043072 Jun 30 01:31 data_D-CDB19_I-3846007102_TS-UNDOTBS1_FNO-18_kr11ag49
-rwxrwx---. 1 root vboxsf 1258299392 Jun 30 01:31 data_D-CDB19_I-3846007102_TS-SYSTEM_FNO-16_ks11ag4c
-rwxrwx---. 1 root vboxsf    2580480 Jun 30 01:31 l011ahbn_672_1_1

Now we can see the previous level 1 backup has been applied on the datafile copies and also an incr level 1 copy has been taken. 
You can see the copy is been recovered the next day and hence the copy will always be 24 hrs behind.

We can change the strategy to make the copies current by flipping the backup and recover commands like below. 
run {
allocate channel c1 device type disk format '/media/sf_Oracle/RMAN-training/%U';
backup incremental level 1 copies=1 for recover of copy with tag 'Demo_merged_l0' database;
recover copy of database with tag 'Demo_merged_l0';
}
Coming to retention...

The above strategy will work for us if we need to restore the backup on the same day or from the period the datafile copy is made. Now, if we need to have a retention for a longer period say 30 days, then we can lag the recover by 30 days by using the below command
RECOVER COPY OF DATABASE WITH TAG 'Demo_merged_l0' UNTIL TIME "SYSDATE-31" FROM TAG 'Demo_merged_l0';
Block change tracking can also be used to take the incremental backups. In order to track 30 days of incr level 1, the hidden parameter _bct_bitmaps_per_file should be increased from its default value of 8 (8 days) to 30. 

Advantages: 

For VLDBs, this solution will provide quicker backups as only the 1st day we will take the full backup and then it will be continuous ever incremental backup only.

References: 

Happy Backups...!!!