Yann Neuhaus

Nowadays I have been working on an interesting customer project where I had to migrate windows oracle standard databases to ODA. The ODAs are X7-2M Models, running version 18.5. This version is coming with Red Hat Enterprise Linx 6.10 (Santiago). Both windows databases and target ODA databases are running PSU 11.2.0.4.190115. But this would definitively also be working for oracle 12c and oracle 18c databases. The databases are licensed with Standard Edition, so migrating through data guard was not possible. Through this blog I would like to share the experience I could get on this topic as well as the method and steps I have been using to successfully migrate those databases.

Limitations

Windows and Linux platform been on the same endian, I have been initially thinking that it would not be more complicated than simply duplicating the windows database to an ODA instance using the last backup. ODA databases are OMF databases, so can not be easier, as no convert parameter is needed.
After having created a single instance database on the ODA, exported the current database pfile and adapted it for the ODA, created the needed TNS connections, I have been running a single RMAN duplicate command :

RMAN> run {
2> set newname for database to new;
3> duplicate target database to 'ODA_DBNAME' backup location '/u99/app/oracle/backup';
4> }


Note : If the database is huge, as for example, more than a Tera bytes, and your sga is small, you might want to increase it. Having a bigger sga size will lower the restore time. Minimum 50 GB would be a good compromise. Also if your ODA is from the ODA-X7 family you will benefit from the NVMe technologie. As per my experience, a duplication of 1.5 TB database, with backup stored locally, did not take more than 40 minutes.

I have been more than happy to see the first duplication step been successfully achieved :

Finished restore at 17-JUL-2019 16:45:10


And I was expecting the same for the next recovery part.

Unfortunately, this didn’t end as expected and I quickly got following restore errors :

Errors in memory script
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted
ORA-06512: at "SYS.X$DBMS_RCVMAN", line 13661
ORA-06512: at line 1
RMAN-03015: error occurred in stored script Memory Script
RMAN-20000: abnormal termination of job step
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_07_17/o1_mf_1_25514_glyf3yd3_.arc'
RMAN-11001: Oracle Error:
ORA-10562: Error occurred while applying redo to data block (file# 91, block# 189)
ORA-10564: tablespace DBVISIT
ORA-01110: data file 91: '/u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/o1_mf_dbvisit_glyczqcj_.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 501874
ORA-00600: internal error code, arguments: [4502], [0], [], [], [], [], [], [], [], [], [], [] RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/17/2019 16:45:32
RMAN-05501: aborting duplication of target database


Troubleshooting the problem I could understand that migrating database from Windows to Linux might not be so simple. Following oracle Doc ID is describing the problem :
Restore From Windows To Linux using RMAN Fails (Doc ID 2003327.1)
Cross-Platform Database Migration (across same endian) using RMAN Transportable Database (Doc ID 1401921.1)
RMAN DUPLICATE/RESTORE/RECOVER Mixed Platform Support (Doc ID 1079563.1)
Restore From Windows To Linux using RMAN Fails (Doc ID 2003327.1)

The problem is coming from the fact that recovering redo transactions between windows and linux platform is not supported if the database is not a standby one. For standard database version, the only possibility would be to go through a cold backup which, in my case, was impossible knowing the database size, the time taken to execute a backup and the short maintenance windows.

Looking for other solution and doing further tests, I could find a solution that I’m going to describe in the next steps.

Restoring the database from the last backup

In order to restore the database, I have been running next steps.

1. Start the ODA instance in no mount :


SQL> startup nomount


2. Restore the last available control file from backup with rman :


RMAN> connect target /
 
RMAN> restore controlfile from '/mnt/backupNFS/oracle/ODA_DBNAME/20190813_233004_CTL_ODA_DBNAME_1179126808_S2864_P1.BCK';


3. Mount the database :


SQL> alter database mount;


4. Catalog the backup path :


RMAN> connect target /
 
RMAN> catalog start with '/mnt/backupNFS/oracle/ODA_DBNAME';


5. And finally restore the database :


RMAN> connect target /
 
RMAN> run {
2> set newname for database to new;
3> restore database;
4> switch datafile all;
5> }


Convert the primary database to a physical standby database

In order to be able to recover the database we will convert the primary database to a physical standby one.

1. We can check the actual status and see that our database is a primary one in mounted state :


SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
 
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED ODA_DBNAME PRIMARY MOUNTED


2. We will convert the database to a physical standby


SQL> alter database convert to physical standby;
 
Database altered.


3. We need to restart the database.


SQL> shutdown immediate
 
SQL> startup mount


4. We can check new database status


SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
 
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED ODA_DBNAME PHYSICAL STANDBY MOUNTED


Get the current windows SCN database

We are now ready to recover the database and the application can be stopped. The next steps will now be executed during the maintenance windows. The windows database listener can be stopped to make sure there is no new connection.

1. We will make sure there is no existing application session on the database :


SQL> set linesize 300
SQL> set pagesize 500
SQL> col machine format a20
SQL> col service_name format a20
 
SQL> select SID, serial#, username, machine, process, program, status, service_name, logon_time from v$session where username not in ('SYS', 'PUBLIC') and username is not null order by status, username;


2. We will create a restore point :


SQL> create restore point for_migration_14082019;
 
Restore point created.


3. We will get the last online log transactions archived :


SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
 
System altered.


4. We will retrieve the SCN corresponding to the restore point :


SQL> col scn format 999999999999999
 
SQL> select scn from v$restore_point where lower(name)='for_migration_14082019';
 
SCN
----------------
13069540631


5. We will backup the last archive log. This will be executed on the windows database using our dbi services internal DMK tool (https://www.dbi-services.com/offering/products/dmk-management-kit/) :


servicedbi@win_srv:E:\app\oracle\local\dmk_custom\bin\ [ODA_DBNAME] ./rman_backup_ODA_DBNAME_arc.bat
 
E:\app\oracle\local\dmk_custom\bin>powershell.exe -command "E:\app\oracle\local\dmk_ha\bin\check_primary.ps1 ODA_DBNAME 'dmk_rman.ps1 -s ODA_DBNAME -t bck_arc.rcv -c E:\app\oracle\admin\ODA_DBNAME\etc\rman.cfg
 
[OK]::KSBL::RMAN::dmk_dbbackup::ODA_DBNAME::bck_arc.rcv
 
Logfile is : E:\app\oracle\admin\ODA_DBNAME\log\ODA_DBNAME_bck_arc_20190814_141754.log
 
RMAN return Code: 0
2019-08-14_02:19:01::check_primary.ps1::MainProgram ::INFO ==> Program completed


Recover the database

The database can now be recovered till our 13069540631 SCN number.

1. We will first need to catalog new archive log backups :


RMAN> connect target /
 
RMAN> catalog start with '/mnt/backupNFS/oracle/ODA_DBNAME';


2. And recover the database till SCN 13069540632 :


RMAN> connect target /
 
RMAN> run {
2> set until scn 13069540632;
3> recover database;
4> }
 
archived log file name=/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30098_go80084r_.arc RECID=30124 STAMP=1016289320
archived log file name=/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30099_go80084x_.arc thread=1 sequence=30099
channel default: deleting archived log(s)
archived log file name=/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30099_go80084x_.arc RECID=30119 STAMP=1016289320
archived log file name=/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30100_go8008bg_.arc thread=1 sequence=30100
channel default: deleting archived log(s)
archived log file name=/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30100_go8008bg_.arc RECID=30121 STAMP=1016289320
media recovery complete, elapsed time: 00:00:02
Finished recover at 14-AUG-2019 14:35:23


3. We can check the alert log and see that recovering has been performed until SCN 13069540632 :


oracle@ODA02:/u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/ [ODA_DBNAME] taa
ORA-279 signalled during: alter database recover logfile '/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30098_go80084r_.arc'...
alter database recover logfile '/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30099_go80084x_.arc'
Media Recovery Log /u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30099_go80084x_.arc
ORA-279 signalled during: alter database recover logfile '/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30099_go80084x_.arc'...
alter database recover logfile '/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30100_go8008bg_.arc'
Media Recovery Log /u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30100_go8008bg_.arc
Wed Aug 14 14:35:23 2019
Incomplete Recovery applied until change 13069540632 time 08/14/2019 14:13:46
Media Recovery Complete (ODA_DBNAME)
Completed: alter database recover logfile '/u03/app/oracle/fast_recovery_area/ODA_DBNAME_RZA/archivelog/2019_08_14/o1_mf_1_30100_go8008bg_.arc'

4. We can check the new ODA database current SCN :


SQL> col current_scn format 999999999999999
 
SQL> select current_scn from v$database;
 
CURRENT_SCN
----------------
13069540631


Convert database to primary again

Database can now be converted back to primary.

SQL> alter database activate standby database;
 
Database altered.


SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
 
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED ODA_DBNAME PRIMARY MOUNTED


At this step if the windows source database would be running 11.2.0.3 version, we could successfully upgrade the new ODA database to 11.2.0.4 following common oracle database upgrade process.

And finally we can open our database and have the database been migrated from windows to linux.


SQL> alter database open;
 
Database altered.



SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
 
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN ODA_DBNAME PRIMARY READ WRITE



oracle@ODA02:/u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/ [ODA_DBNAME] ODA_DBNAME
********* dbi services Ltd. *********
STATUS : OPEN
DB_UNIQUE_NAME : ODA_DBNAME_RZA
OPEN_MODE : READ WRITE
LOG_MODE : ARCHIVELOG
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 11.2.0.4.0
*************************************


Post migration steps

There will be a few post migration steps to be executed.

Created redo logs again

Redo logs are still stamped with windows path and therefore have been created in $ORACLE_HOME/dbs folder. In this steps we will create new OMF one again.

1. Checking current online log members :


SQL> set linesize 300
SQL> set pagesize 500
SQL> col member format a100
 
SQL> select a.GROUP#, b.member, a.status, a.bytes/1024/1024 MB from v$log a, v$logfile b where a.GROUP#=b.GROUP#;
 
GROUP# MEMBER STATUS MB
---------- ---------------------------------------------------------------------------------------------------- ---------------- ----------
6 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_6_1.LOG UNUSED 500
6 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_6_2.LOG UNUSED 500
5 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_5_2.LOG UNUSED 500
5 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_5_1.LOG UNUSED 500
4 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_4_2.LOG UNUSED 500
4 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_4_1.LOG UNUSED 500
3 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_3_2.LOG UNUSED 500
3 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_3_1.LOG UNUSED 500
2 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_2_2.LOG UNUSED 500
2 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_2_1.LOG UNUSED 500
1 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_1_2.LOG CURRENT 500
1 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_1_1.LOG CURRENT 500


2. Drop the first unused redo log group keeping only one :


SQL> alter database drop logfile group 6;
 
Database altered.
 
SQL> alter database drop logfile group 5;
 
Database altered.
 
SQL> alter database drop logfile group 4;
 
Database altered.
 
SQL> alter database drop logfile group 3;
 
Database altered.
 
SQL> alter database add logfile group 3 size 500M;
 
Database altered.


3. Create the recent dropped group again :


SQL> alter database add logfile group 3 size 500M;
 
Database altered.
 
SQL> alter database add logfile group 4 size 500M;
 
Database altered.
 
SQL> alter database add logfile group 5 size 500M;
 
Database altered.
 
SQL> alter database add logfile group 6 size 500M;
 
Database altered.


4. Drop the last unused redo log group and create it again :


SQL> alter database drop logfile group 2;
 
Database altered.
 
SQL> alter database add logfile group 2 size 500M;
 
Database altered.


5. Execute a switch log file and checkpoint so the current redo group becomes unused :


SQL> alter system switch logfile;
 
System altered.
 
SQL> alter system checkpoint;
 
System altered.


6. Drop it and create it again :


SQL> alter database drop logfile group 1;
 
Database altered.
 
SQL> alter database add logfile group 1 size 500M;
 
Database altered.


7. Check redo group members :


SQL> select a.GROUP#, b.member, a.status, a.bytes/1024/1024 MB from v$log a, v$logfile b where a.GROUP#=b.GROUP#;
 
GROUP# MEMBER STATUS MB
---------- ---------------------------------------------------------------------------------------------------- ---------------- ----------
3 /u03/app/oracle/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_3_go81rj4t_.log INACTIVE 500
3 /u02/app/oracle/oradata/ODA_DBNAME_RZA/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_3_go81rjqn_.log INACTIVE 500
4 /u03/app/oracle/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_4_go81ron1_.log UNUSED 500
4 /u02/app/oracle/oradata/ODA_DBNAME_RZA/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_4_go81rp6o_.log UNUSED 500
5 /u03/app/oracle/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_5_go81rwhs_.log UNUSED 500
5 /u02/app/oracle/oradata/ODA_DBNAME_RZA/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_5_go81rx1g_.log UNUSED 500
6 /u03/app/oracle/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_6_go81s1rk_.log UNUSED 500
6 /u02/app/oracle/oradata/ODA_DBNAME_RZA/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_6_go81s2bx_.log UNUSED 500
2 /u03/app/oracle/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_2_go81sgdf_.log CURRENT 500
2 /u02/app/oracle/oradata/ODA_DBNAME_RZA/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_2_go81sgxd_.log CURRENT 500
1 /u03/app/oracle/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_1_go81vpls_.log UNUSED 500
1 /u02/app/oracle/oradata/ODA_DBNAME_RZA/redo/ODA_DBNAME_RZA/onlinelog/o1_mf_1_go81vq4v_.log UNUSED 500


8. Delete the wrong previous redo log members files :


oracle@ODA02:/u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/ [ODA_DBNAME] cdh
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/ [ODA_DBNAME] cd dbs
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] ls -ltrh *REDO*.LOG
-rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_6_2.LOG
-rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_6_1.LOG
-rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_5_2.LOG
-rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_5_1.LOG
-rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_4_2.LOG
-rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_4_1.LOG
-rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_3_2.LOG
-rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_3_1.LOG
-rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_2_2.LOG
-rw-r----- 1 oracle asmadmin 501M Aug 14 14:59 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_2_1.LOG
-rw-r----- 1 oracle asmadmin 501M Aug 14 15:05 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_1_2.LOG
-rw-r----- 1 oracle asmadmin 501M Aug 14 15:05 I:FAST_RECOVERY_AREAODA_DBNAME_SITE1ONLINELOGREDO_1_1.LOG
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] rm *REDO*.LOG


Created temp file again

1. Checking current temp file we can see that the path is still the windows one :


SQL> set linesize 300
SQL> col name format a100
 
SQL> select b.name, b.status, b.bytes/1024/1024 MB, a.name from v$tablespace a, v$tempfile b where a.TS#=b.TS#;
 
NAME STATUS MB NAME
---------------------------------------------------------------------------------------------------- ------- ---------- -------------------------------------------
F:\ORADATA\ODA_DBNAME\TEMPORARY_DATA_1.DBF ONLINE 8192 TEMPORARY_DATA


2. We can check that the default temporary tablespace is TEMPORARY_DATA


SQL> col property_value format a50
 
SQL> select property_name, property_value from database_properties where property_name like '%DEFAULT%TABLESPACE%';
 
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMPORARY_DATA
DEFAULT_PERMANENT_TABLESPACE USER_DATA


3. Let’s create a new temp tablespace and make it the default one


SQL> create temporary tablespace TEMP tempfile size 8G;
 
Tablespace created.
 
SQL> alter database default temporary tablespace TEMP;
 
Database altered.
 
SQL> select property_name, property_value from database_properties where property_name like '%DEFAULT%TABLESPACE%';
 
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE USER_DATA


4. Drop previous TEMPORARY_DATA tablespace


SQL> drop tablespace TEMPORARY_DATA including contents and datafiles;
 
Tablespace dropped.
 
SQL> select b.file#, b.name, b.status, b.bytes/1024/1024 MB, a.name from v$tablespace a, v$tempfile b where a.TS#=b.TS#;
 
FILE# NAME STATUS MB NAME
---------- ---------------------------------------------------------------------------------------------------- ------- ----------
3 /u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/o1_mf_temp_go83m1tp_.tmp ONLINE 8192 TEMP


5. Create TEMPORARY_DATA tablespace again and make it the default one :


SQL> create temporary tablespace TEMPORARY_DATA tempfile size 8G;
 
Tablespace created.
 
SQL> select b.file#, b.name, b.status, b.bytes/1024/1024 MB, a.name from v$tablespace a, v$tempfile b where a.TS#=b.TS#;
 
FILE# NAME STATUS MB NAME
---------- ---------------------------------------------------------------------------------------------------- ------- ----------
1 /u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/o1_mf_temporar_go83wfd7_.tmp ONLINE 8192 TEMPORARY_DATA
3 /u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/o1_mf_temp_go83m1tp_.tmp ONLINE 8192 TEMP
 
SQL> alter database default temporary tablespace TEMPORARY_DATA;
 
Database altered.
 
SQL> select property_name, property_value from database_properties where property_name like '%DEFAULT%TABLESPACE%';
 
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMPORARY_DATA
DEFAULT_PERMANENT_TABLESPACE USER_DATA


6. And finally drop the intermediare temp tablespace :


SQL> drop tablespace TEMP including contents and datafiles;
 
Tablespace dropped.
 
SQL> select b.file#, b.name, b.status, b.bytes/1024/1024 MB, a.name from v$tablespace a, v$tempfile b where a.TS#=b.TS#;
 
FILE# NAME STATUS MB NAME
---------- ---------------------------------------------------------------------------------------------------- ------- ----------
1 /u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/o1_mf_temporar_go83wfd7_.tmp ONLINE 8192 TEMPORARY_DATA


7. Appropriate max size can be given to the new created temp tablespace


SQL> alter database tempfile '/u02/app/oracle/oradata/ODA_DBNAME_RZA/ODA_DBNAME_RZA/datafile/o1_mf_temporar_go83wfd7_.tmp' autoextend on maxsize 31G;
 
Database altered.


8. Remove wrong temp file stored in $ORACLE_HOME/dbs


oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] ls -ltr
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r--r-- 1 oracle oinstall 64 Jul 25 08:10 initODA_DBNAME.ora.old
-rw-r----- 1 oracle oinstall 2048 Jul 25 08:10 orapwODA_DBNAME
-rw-r--r-- 1 oracle oinstall 67 Jul 25 08:31 initODA_DBNAME.ora
-rw-r----- 1 oracle asmadmin 8589942784 Aug 14 08:14 F:ORADATAODA_DBNAMETEMPORARY_DATA_1.DBF
-rw-rw---- 1 oracle asmadmin 1544 Aug 14 14:59 hc_ODA_DBNAME.dat
-rw-r----- 1 oracle asmadmin 43466752 Aug 14 15:48 snapcf_ODA_DBNAME.f
 
oracle@RZA-ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] rm F:ORADATAODA_DBNAMETEMPORARY_DATA_1.DBF


Apply specific ODA parameters

Following specific ODA parameters can be updated to the new created instance.


SQL> alter system set "_datafile_write_errors_crash_instance"=false scope=spfile;
 
System altered.
 
SQL> alter system set "_db_writer_coalesce_area_size"=16777216 scope=spfile;
 
System altered.
 
SQL> alter system set "_disable_interface_checking"=TRUE scope=spfile;
 
System altered.
 
SQL> alter system set "_ENABLE_NUMA_SUPPORT"=FALSE scope=spfile;
 
System altered.
 
SQL> alter system set "_FILE_SIZE_INCREASE_INCREMENT"=2143289344 scope=spfile;
 
System altered.
 
SQL> alter system set "_gc_policy_time"=0 scope=spfile;
 
System altered.
 
SQL> alter system set "_gc_undo_affinity"=FALSE scope=spfile;
 
System altered.
 
SQL> alter system set db_block_checking='FULL' scope=spfile;
 
System altered.
 
SQL> alter system set db_block_checksum='FULL' scope=spfile;
 
System altered.
 
SQL> alter system set db_lost_write_protect='TYPICAL' scope=spfile;
 
System altered.
 
SQL> alter system set sql92_security=TRUE scope=spfile;
 
System altered.
 
SQL> alter system set use_large_pages='only' scope=spfile;
 
System altered.


“_fix_control”parameter is specific to Oracle12c and not compatible Oracle 11g. See Doc ID 2145105.1.

Register database in grid

After applying specific ODA instance parameters, we can register the database in the grid and start it with the grid.


oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl add database -d ODA_DBNAME_RZA -o /u01/app/oracle/product/11.2.0.4/dbhome_1 -c SINGLE -i ODA_DBNAME -x RZA-ODA02 -m ksbl.local -p /u02/app/oracle/oradata/ODA_DBNAME_RZA/dbs/spfileODA_DBNAME.ora -r PRIMARY -s OPEN -t IMMEDIATE -n ODA_DBNAME -j "/u02/app/oracle/oradata/ODA_DBNAME_RZA,/u03/app/oracle"
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl start database -d ODA_DBNAME_RZA
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl status database -d ODA_DBNAME_RZA
Instance ODA_DBNAME is running on node rza-oda02
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] ODA_DBNAME
********* dbi services Ltd. *********
STATUS : OPEN
DB_UNIQUE_NAME : ODA_DBNAME_RZA
OPEN_MODE : READ WRITE
LOG_MODE : ARCHIVELOG
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 11.2.0.4.0
*************************************


We can check the well functionning :

oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl stop database -d ODA_DBNAME_RZA
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl status database -d ODA_DBNAME_RZA
Instance ODA_DBNAME is not running on node rza-oda02
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] ODA_DBNAME
********* dbi services Ltd. *********
STATUS : STOPPED
*************************************
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl start database -d ODA_DBNAME_RZA
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] srvctl status database -d ODA_DBNAME_RZA
Instance ODA_DBNAME is running on node rza-oda02
 
oracle@ODA02:/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ [ODA_DBNAME] ODA_DBNAME
********* dbi services Ltd. *********
STATUS : OPEN
DB_UNIQUE_NAME : ODA_DBNAME_RZA
OPEN_MODE : READ WRITE
LOG_MODE : ARCHIVELOG
DATABASE_ROLE : PRIMARY
FLASHBACK_ON : NO
FORCE_LOGGING : YES
VERSION : 11.2.0.4.0
*************************************

Conclusion

Going through a physical standby database, I was able to migrate successfully the windows databases into ODA linux one. I have been able to achieve migration of source 11.2.0.4 databases but also 11.2.0.3 database by adding an upgrade step in the process.

Cet article Migrating Oracle database from windows to ODA est apparu en premier sur Blog dbi services.

This is something that I waited for a while, in fact since SQL Server 2017 … and the news came out on Wednesday 09th September 2019. Running Non-Root SQL Server Containers is now possible either on the next version of SQL Server (2019) and it has been backported on SQL Server 2017 as well. Non-root SQL Server containers will likely be part of hidden gem of SQL Server new features, but this is definitely a good news for me because it will facilitate the transition of SQL Server containers on production from a security standpoint.

At this stage, no need to precise why it is not a best practice to run SQL Server containers or more generally speaking applications with root privileges within a container. For further information, I invite you to take a look at the different threats implied by a such configuration with your google-fu. 

Let’s start with docker environments. First, Microsoft provides a Docker file to build an image either for SQL Server 2017 and SQL Server 2019. We may notice the Docker file is already based on a SQL Server docker image and performs some extra configuration for non-root privilege capabilities. I put here the interesting part:

# Exmple of creating a SQL Server 2019 container image that will run as a user 'mssql' instead of root
# This is example is based on the official image from Microsoft and effectively changes the user that SQL Server runs as
# and allows for dumps to generate as a non-root user


FROM mcr.microsoft.com/mssql/server:2019-latest

# Create non-root user and update permissions
#
RUN useradd -M -s /bin/bash -u 10001 -g 0 mssql
RUN mkdir -p -m 770 /var/opt/mssql && chgrp -R 0 /var/opt/mssql

# Grant sql the permissions to connect to ports <1024 as a non-root user
#
RUN setcap 'cap_net_bind_service+ep' /opt/mssql/bin/sqlservr

# Allow dumps from the non-root process
# 
RUN setcap 'cap_sys_ptrace+ep' /opt/mssql/bin/paldumper
RUN setcap 'cap_sys_ptrace+ep' /usr/bin/gdb

# Add an ldconfig file because setcap causes the os to remove LD_LIBRARY_PATH
# and other env variables that control dynamic linking
#
RUN mkdir -p /etc/ld.so.conf.d && touch /etc/ld.so.conf.d/mssql.conf
RUN echo -e "# mssql libs\n/opt/mssql/lib" >> /etc/ld.so.conf.d/mssql.conf
RUN ldconfig

USER mssql
CMD ["/opt/mssql/bin/sqlservr"]

 

Note the different sections where the mssql user is created and is used when running the image. So, the new image specification implies running the sqlservr process using this mssql user as shown below:

$ docker exec -ti sql19 top

 

The user process is well identified by its name because it is already defined in the /etc/password file within the container namespace:

$ docker exec -ti sql19 cat /etc/passwd | grep mssql
mssql:x:10001:0::/home/mssql:/bin/bash

 

Let’s go ahead and let’s talk about persisting SQL Server database files on an external storage. In this case, we need to refer to the Microsoft documentation to configure volumes and underlying storage permissions regarding the scenario we will have to deal with.

If you don’t specify any user (and group) when spinning up the container, the sqlservr process will run with the identity of the mssql user created inside the container and as part of the root group. The underlying host filesystem must be configured accordingly, either a user with same UID = 10001 or the root group GUID = 0). Otherwise chances are you will experience permission issues with the following error message:

SQL Server 2019 will run as non-root by default.
This container is running as user mssql.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
/opt/mssql/bin/sqlservr: Error: Directory [/var/opt/mssql/system/] could not be created.  Errno [13]

 

If you want to run the container as part of a custom user and group created on your own, you must be aware of the different database file placement scenarios. The first one consists in using the default configuration with all the SQL Server logs, data and transaction log files in /var/opt/mssql path. In this case, your custom user UID and GUID can be part of the security context of the hierarchy folder on the host as follows:

$ ls -l | grep sqlserver
drwxrwx---. 6 mssql mssql 59 Sep 27 19:08 sqlserver

$ id mssql
uid=1100(mssql) gid=1100(mssql) groups=1100(mssql),100(users)

 

The docker command below specifies the UID and GUID of my custom user through the -u parameter:

docker run -d \
 --name sql19 \
 -u $(id -u mssql):$(id -g mssql) \
 -e "MSSQL_PID=Developer" \
 -e "ACCEPT_EULA=Y" \
 -e "SA_PASSWORD=Password1" \
 -e "MSSQL_AGENT_ENABLED=True" \
 -e "MSSQL_LCID=1033" \
 -e "MSSQL_MEMORY_LIMIT_MB=2048" \
 -v "/u00/sqlserver:/var/opt/mssql" \
 -p 1451:1433 -d 2019-latest-non-root

 

Note the username is missing and replaced by the UID of the mssql user created on my own.

This is a normal behavior because my user is not known within the container namespace. There is no record from my user with UID = 1001. The system only knows the mssql user with UID = 10001 as shown below:

I have no name!@e698c3db2180:/$ whoami
whoami: cannot find name for user ID 1100

$ cat /etc/passwd | grep mssql | cut -d":" -f 1,3
mssql:10001

 

For a sake of curiosity, we may wonder how SQL Server makes the choice of using the correct user for the sqlservr process. Indeed, I created two users with the same name but with different UIDs and I think that after some investigations, taking a look at the uid_entry point definition in the microsoft/mssql-docker github project could help understanding this behavior:

If we don’t specify the UID / GUID during the container’s creation, the whoami command will fail and the mssql user’s UID defined in the Dockerfile (cf. USER mssql) will be chosen.

 The second scenario consists in introducing some SQL Server best practices in terms of database file placement. In a previous blog post, I wrote about a possible implementation based on a flexible architecture for SQL Server on Linux and which may fit with containers. In this case, database files will be stored outside of the /var/opt/mssql default path and in this case, the non-root container has the restriction that it must run as part of the root group as mentioned in the Microsoft documentation:

The non-root container has the restriction that it must run as part of the root group unless a volume is mounted to '/var/opt/mssql' that the non-root user can access. The root group doesn’t grant any extra root permissions to the non-root user.

 

Here my implementation of the flexible architecture template with required Linux permissions in my context:

$ ls -ld /u[0-9]*/sql*2/
drwxrwx---. 2 mssql root    6 Sep 24 22:02 /u00/sqlserver2/
drwxrwx---. 2 mssql root 4096 Sep 27 14:20 /u01/sqlserverdata2/
drwxrwx---. 2 mssql root   25 Sep 27 14:20 /u02/sqlserverlog2/
drwxrwx---. 2 mssql root    6 Sep 24 22:04 /u03/sqlservertempdb2/
drwxrwx---. 2 mssql root    6 Sep 27 10:09 /u98/sqlserver2/

 

… with:

• /u00/sqlserver2 (binaries structure that will contain remaining files in /var/opt/mssql path)
• /u01/sqlserverdata2 (data files including user, system and tempdb databases)
• /u02/sqlserverlog2 (transaction log files)
• /u98/sqlserver2 (database backups)

And accordingly, my docker command and parameters to start my SQL Server container that will sit on my flexible architecture:

docker run -d \
 --name sql19 \
 -u $(id -u mssql):0 \
 -e "MSSQL_PID=Developer" \
 -e "ACCEPT_EULA=Y" \
 -e "SA_PASSWORD=Password1" \
 -e "MSSQL_AGENT_ENABLED=True" \
 -e "MSSQL_LCID=1033" \
 -e "MSSQL_MEMORY_LIMIT_MB=2048" \
 -e "MSSQL_MASTER_DATA_FILE=/u01/sqlserverdata/master.mdf" \
 -e "MSSQL_MASTER_LOG_FILE=/u02/sqlserverlog/mastlog.ldf" \
 -e "MSSQL_DATA_DIR=/u01/sqlserverdata" \
 -e "MSSQL_LOG_DIR=/u02/sqlserverlog" \
 -e "MSSQL_BACKUP_DIR=/u98/sqlserver" \
 -v "/u00/sqlserver2:/var/opt/mssql" \
 -v "/u01/sqlserverdata2:/u01/sqlserverdata" \
 -v "/u02/sqlserverlog2:/u02/sqlserverlog" \
 -v "/u98/sqlserver2:/u98/sqlserver" \
 -p 1451:1433 -d 2019-latest-non-root

 

The mssql user created on my own from the host (with UID = 1100) is used by the sqlservr process:

The system and user database files are placed according to my specification:

master> create database test;
Commands completed successfully.
Time: 0.956s
master> \n ldd %%
+--------+----------------+---------------------------------+-----------+
| DB     | logical_name   | physical_name                   | size_MB   |
|--------+----------------+---------------------------------+-----------|
| master | master         | /u01/sqlserverdata/master.mdf   | 71        |
| master | mastlog        | /u02/sqlserverlog/mastlog.ldf   | 32        |
| tempdb | tempdev        | /u01/sqlserverdata/tempdb.mdf   | 128       |
| tempdb | templog        | /u01/sqlserverdata/templog.ldf  | 128       |
| tempdb | tempdev2       | /u01/sqlserverdata/tempdb2.ndf  | 128       |
| tempdb | tempdev3       | /u01/sqlserverdata/tempdb3.ndf  | 128       |
| tempdb | tempdev4       | /u01/sqlserverdata/tempdb4.ndf  | 128       |
| model  | modeldev       | /u01/sqlserverdata/model.mdf    | 128       |
| model  | modellog       | /u01/sqlserverdata/modellog.ldf | 128       |
| msdb   | MSDBData       | /u01/sqlserverdata/MSDBData.mdf | 236       |
| msdb   | MSDBLog        | /u01/sqlserverdata/MSDBLog.ldf  | 12        |
| test   | test           | /u01/sqlserverdata/test.mdf     | 128       |
| test   | test_log       | /u02/sqlserverlog/test_log.ldf  | 128       |
+--------+----------------+---------------------------------+-----------+

 

I may correlate the above output with corresponding files persisted on underlying storage according to my flexible architecture specification:

$ sudo ls -lR /u[0-9]*/sqlserver*2/
/u00/sqlserver2/:
total 4
drwxrwx---. 2 mssql root 4096 Sep 28 17:39 log
drwxr-xr-x. 2 mssql root   25 Sep 28 17:39 secrets

/u00/sqlserver2/log:
total 428
-rw-r-----. 1 mssql root  10855 Sep 28 17:39 errorlog
-rw-r-----. 1 mssql root  10856 Sep 28 17:37 errorlog.1
-rw-r-----. 1 mssql root      0 Sep 28 17:37 errorlog.2
-rw-r-----. 1 mssql root  77824 Sep 28 17:37 HkEngineEventFile_0_132141586653320000.xel
-rw-r-----. 1 mssql root  77824 Sep 28 17:39 HkEngineEventFile_0_132141587692350000.xel
-rw-r-----. 1 mssql root   2560 Sep 28 17:39 log_1.trc
-rw-r-----. 1 mssql root   2560 Sep 28 17:37 log.trc
-rw-r-----. 1 mssql root   6746 Sep 28 17:37 sqlagent.1
-rw-r-----. 1 mssql root   6746 Sep 28 17:39 sqlagent.out
-rw-r-----. 1 mssql root    114 Sep 28 17:39 sqlagentstartup.log
-rw-r-----. 1 mssql root 106496 Sep 28 17:37 system_health_0_132141586661720000.xel
-rw-r-----. 1 mssql root 122880 Sep 28 17:41 system_health_0_132141587698940000.xel

/u00/sqlserver2/secrets:
total 4
-rw-------. 1 mssql root 44 Sep 28 17:39 machine-key

/u01/sqlserverdata2/:
total 105220
-rw-r-----. 1 mssql root      256 Sep 27 14:20 Entropy.bin
-rw-r-----. 1 mssql root  4653056 Sep 28 17:39 master.mdf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 modellog.ldf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 model.mdf
-rw-r-----. 1 mssql root 14024704 Sep 27 14:20 model_msdbdata.mdf
-rw-r-----. 1 mssql root   524288 Sep 27 14:20 model_msdblog.ldf
-rw-r-----. 1 mssql root   524288 Sep 27 14:20 model_replicatedmaster.ldf
-rw-r-----. 1 mssql root  4653056 Sep 27 14:20 model_replicatedmaster.mdf
-rw-r-----. 1 mssql root 15466496 Sep 28 17:39 msdbdata.mdf
-rw-r-----. 1 mssql root   786432 Sep 28 17:39 msdblog.ldf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 tempdb2.ndf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 tempdb3.ndf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 tempdb4.ndf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 tempdb.mdf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 templog.ldf
-rw-r-----. 1 mssql root  8388608 Sep 28 17:39 test.mdf

/u02/sqlserverlog2/:
total 10240
-rw-r-----. 1 mssql root 2097152 Sep 28 17:39 mastlog.ldf
-rw-r-----. 1 mssql root 8388608 Sep 28 17:39 test_log.ldf

/u03/sqlservertempdb2/:
total 0

/u98/sqlserver2/:
total 0

 

What next? Because in production your containers will run on the top of orchestrator like Kubernetes, the question is how to implement such privilege restriction in this context? Kubernetes provides security context at different levels including pod and containers. In this blog post example, I applied the security context at the container level within the container specification.  

Let’s set the context. Here the picture of my K8s environment:

$ kubectl get nodes
NAME                     STATUS   ROLES    AGE   VERSION
k8m.dbi-services.test    Ready    master   97d   v1.14.1
k8n1.dbi-services.test   Ready    <none>   97d   v1.14.1
k8n2.dbi-services.test   Ready    <none>   97d   v1.14.1

 

I used the new local-storage Storage class (available with K8s v.1.14+):

$ kubectl get sc
NAME            PROVISIONER                    AGE
local-storage   kubernetes.io/no-provisioner   4d

$ kubectl describe sc local-storage
Name:                  local-storage
IsDefaultClass:        No
Annotations:           <none>
Provisioner:           kubernetes.io/no-provisioner
Parameters:            <none>
AllowVolumeExpansion:  <unset>
MountOptions:          <none>
ReclaimPolicy:         Delete
VolumeBindingMode:     WaitForFirstConsumer
Events:                <none>

 

I configured a persistent volume based on this local-storage class and that pointing to the /mnt/local-storage on my K81n node. The access mode and Retain policy are configured according to meet the best practices for databases.

$ cat StoragePV.yaml
apiVersion: v1
kind: PersistentVolume
metadata:
  name: my-local-pv
spec:
  capacity:
    storage: 5Gi
  accessModes:
  - ReadWriteOnce
  persistentVolumeReclaimPolicy: Retain
  volumeMode: Filesystem
  storageClassName: local-storage
  local:
    path: /mnt/localstorage
  nodeAffinity:
    required:
      nodeSelectorTerms:
      - matchExpressions:
        - key: kubernetes.io/hostname
          operator: In
          values:
          - k8n1.dbi-services.test

 

For a sake of simplicity, I applied the default configuration with all SQL Server related files related stored in /var/opt/mssql. I configured the underlying storage and folder permissions accordingly with my custom mssql user (UID = 10001) and group (GUID = 10001) created on the K8n1 host. Note that the UID matches with that of the mssql user created within the container.

$ hostname
k8n1.dbi-services.test

$ id mssql
uid=10001(mssql) gid=10001(mssql) groups=10001(mssql)

$ ls -ld /mnt/localstorage/
drwxrwx--- 6 mssql mssql 59 Sep 26 20:57 /mnt/localstorage/

 

My deployment file is as follows. It includes the security context that specifies a non-root container configuration with my custom user’s UID / GUID created previously (runAsUser and runAsGroup parameters):

$ cat ReplicaSet.yaml
apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: mssql-deployment-2
spec:
  replicas: 1
  template:
    metadata:
      labels:
        app: mssql-2
    spec:
      securityContext:
        runAsUser: 10001
        runAsGroup: 10001
      terminationGracePeriodSeconds: 10
      containers:
      - name: mssql-2
        image: trow.kube-public:31000/2019-latest-non-root
        ports:
        - containerPort: 1433
        env:
        - name: MSSQL_PID
          value: "Developer"
        - name: ACCEPT_EULA
          value: "Y"
        - name: MSSQL_SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: sql-secrets
              key: sapassword
        volumeMounts:
        - name: mssqldb
          mountPath: /var/opt/mssql
      volumes:
      - name: mssqldb
        persistentVolumeClaim:
          claimName: mssql-data-2

 

Obviously, if you don’t meet the correct security permissions on the underlying persistent volume, you will get an error when provisioning the MSSQL pod because the sqlservr process will not get the privileges to create or to access the SQL Server related files as shown below:

$ kubectl get pod
NAME                                 READY   STATUS   RESTARTS   AGE
mssql-deployment-2-8b4d7f7b7-x4x8w   0/1     Error    2          30s

$ kubectl logs mssql-deployment-2-8b4d7f7b7-x4x8w
SQL Server 2019 will run as non-root by default.
This container is running as user mssql.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
/opt/mssql/bin/sqlservr: Error: Directory [/var/opt/mssql/system/] could not be created.  Errno [13]

 

If well configured, everything should work as expected and your container should run and interacts correctly with the corresponding persistent volume in the security context defined in your YAML specification:

All this stuff applies to SQL Server 2017.

See you!

 

 

 

 

 

 

Cet article Using non-root SQL Server containers on Docker and K8s est apparu en premier sur Blog dbi services.

The Initial Release (8.0.0) of Red Hat Enterprise Linux 8 is available since May 2019.
I’ve already blogged about one of its new feature (AppStream) during the Beta version. In this post I will present Stratis, which is a new local storage-management solution available on RHEL8.

Introduction

LVM, fdisk, ext*, XFS,… there is plenty of terms, tools and technologies available for managing disks and file systems on a Linux server. In a general way, setting up the initial configuration of storage is not so difficult, but when it comes to manage this storage (meaning most of the time extend it), that’s where things can get a bit more complicated.
The goal of Stratis is to provide an easy way to work on local storage, from the initial setup to the usage of more advanced features.
Like Btrfs or ZFS, Stratis is a “volume-managing filesystems”. VMF’s particularity is that it can be used to manage volume-management and filesystems layers into one, using the concept of “pool” of storage, created from one or more block devices.

Stratis is implemented as a userspace daemon triggered to configure and monitor existing components :
[root@rhel8 ~]# ps -ef | grep stratis
root 591 1 0 15:31 ? 00:00:00 /usr/libexec/stratisd –debug
[root@rhel8 ~]#
To interact with the deamon a CLI is available (stratis-cli) :
[root@rhel8 ~]# stratis --help
usage: stratis [-h] [--version] [--propagate] {pool,blockdev,filesystem,fs,daemon} ...
Stratis Storage Manager
optional arguments:
-h,              --help show this help message and exit
--version        show program's version number and exit
--propagate      Allow exceptions to propagate
subcommands:
{pool,blockdev,filesystem,fs,daemon}
pool             Perform General Pool Actions
blockdev         Commands related to block devices that make up the pool
filesystem (fs)  Commands related to filesystems allocated from a pool
daemon           Stratis daemon information
[root@rhel8 ~]#

Among the Stratis features we can mention :
> Thin provisioning
> Filesystem snapshots
> Data integrity check
> Data caching (cache tier)
> Data redundancy (raid1, raid5, raid6 or raid10)
> Encryption

Stratis is only 2 years old and the current version is 1.0.3. Therefore, certain features are not yet available such as redundancy for example :
[root@rhel8 ~]# stratis daemon redundancy
NONE: 0
[root@rhel8 ~]#

Architecture

Startis architecture is composed of 3 layers :
Block device
A blockdev is the storage used to make up the pool. That could be :
> Hard drives / SSDs
> iSCSI
> mdraid
> Device Mapper Multipath
> …

Pool
A pool is a set of Block devices.

Filesystem
Filesystems are created from the pool. Stratis supports up to 2^4 filesystems per pool. Currently you can only created XFS filesystem on top of a pool.

Let’s try…

I have a new empty 5G disk on my system. This is the blockdev I want to use :
[root@rhel8 ~]# lsblk /dev/sdb
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sdb    8:16   0   5G  0 disk
[root@rhel8 ~]#

I create pool composed of this unique blockdev…
[root@rhel8 ~]# stratis pool create pool01 /dev/sdb

…and verify :
[root@rhel8 ~]# stratis pool list
Name    Total Physical Size Total Physical Used
pool01                5 GiB              52 MiB
[root@rhel8 ~]#

On top of this pool I create a XFS filesystem called “data”…
[root@rhel8 ~]# stratis fs create pool01 data
[root@rhel8 ~]# stratis fs list
Pool Name   Name        Used       Created             Device                      UUID
pool01      data        546 MiB   Sep 04 2019 16:50   /stratis/pool01/data        dc08f87a2e5a413d843f08728060a890
[root@rhel8 ~]#

…and mount it on /data directory :
[root@rhel8 ~]# mkdir /data
[root@rhel8 ~]# mount /stratis/pool01/data /data
[root@rhel8 ~]# df -h /data
Filesystem                                                                                      Size Used Avail Use% Mounted on
/dev/mapper/stratis-1-8fccad302b854fb7936d996f6fdc298c-thin-fs-f3b16f169e8645f6ac1d121929dbb02e 1.0T 7.2G 1017G 1%   /data
[root@rhel8 ~]#
Here the ‘df’ command report the current used and free sizes as seen and reported by XFS. In fact this is the thin-device :
[root@rhel8 ~]# lsblk /dev/mapper/stratis-1-8fccad302b854fb7936d996f6fdc298c-thin-fs-f3b16f169e8645f6ac1d121929dbb02e
NAME                                                                                           MAJ:MIN  RM  SIZE  RO  TYPE     MOUNTPOINT
/dev/mapper/stratis-1-8fccad302b854fb7936d996f6fdc298c-thin-fs-f3b16f169e8645f6ac1d121929dbb02e  253:7   0    1T   0  stratis  /data
[root@rhel8 ~]#

This is not very useful, because the real usage of the storage is less due to thin provisioning. And also because Stratis will automatically grow the filesystem if it nears XFS’s currently sized capacity.

Let’s extend the pool with a new disk of 1G…
[root@rhel8 ~]# lsblk /dev/sdc
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sdc    8:32   0   1G  0 disk
[root@rhel8 ~]#
[root@rhel8 ~]# stratis pool add-data pool01 /dev/sdc

…and check :
[root@rhel8 ~]# stratis blockdev
Pool Name  Device Node  Physical Size   State  Tier
pool01     /dev/sdb              5 GiB  In-use  Data
pool01     /dev/sdc              1 GiB  In-use  Data
[root@rhel8 pool01]# stratis pool list
Name   Total Physical Size    Total Physical Used
pool01                6 GiB                602 MiB
[root@rhel8 ~]#


A nice feature of Stratis is the possibility to duplicate a filesystem with a snapshot.
For this test I create a new file on the filesystem “data” we just added :
[root@rhel8 ~]# touch /data/new_file
[root@rhel8 ~]# ls -l /data
total 0
-rw-r--r--. 1 root root 0 Sep 4 20:43 new_file
[root@rhel8 ~]#

The operation is straight forward :
[root@rhel8 ~]# stratis fs snapshot pool01 data data_snap
[root@rhel8 ~]#

You can notice that Stratis don’t make a difference between a filesystem and a snapshot filesystem. They are the same kind of “object” :
[root@rhel8 ~]# stratis fs list
Pool Name   Name        Used       Created             Device                      UUID
pool01      data        546 MiB   Sep 04 2019 16:50   /stratis/pool01/data        dc08f87a2e5a413d843f08728060a890
pool01      data_snap   546 MiB   Sep 04 2019 16:57   /stratis/pool01/data_snap   a2c45e9a15e74664bab5de992fa884f7
[root@rhel8 ~]#

I can now mount the new Filesystem…
[root@rhel8 ~]# mkdir /data_snap
[root@rhel8 ~]# mount /stratis/pool01/data_snap /data_snap
[root@rhel8 ~]# df -h /data_snap
Filesystem                                                                                       Size  Used  Avail  Use%  Mounted on
/dev/mapper/stratis-1-8fccad302b854fb7936d996f6fdc298c-thin-fs-a2c45e9a15e74664bab5de992fa884f7  1.0T  7.2G  1017G  1%    /data_snap
[root@rhel8 ~]#

…and check that my test file is here :
[root@rhel8 ~]# ls -l /data_snap
total 0
-rw-r--r--. 1 root root 0 Sep 4 20:43 new_file
[root@rhel8 ~]#

Nice ! But… can I snapshot a filesystem in “online” mode, meaning when data are writing on it ?
Let’s create another snapshot from one session, while a second session is writing on the /data filesystem.
From session 1 :
[root@rhel8 ~]# stratis fs snapshot pool01 data data_snap2

And from session 2, in the same time :
[root@rhel8 ~]# dd if=/dev/zero of=/data/bigfile.txt bs=4k iflag=fullblock,count_bytes count=4G

Once done, the new filesystem is present…
[root@rhel8 ~]# stratis fs list
Pool Name Name Used Created Device UUID
pool01 data_snap2 5.11 GiB Sep 27 2019 11:19 /stratis/pool01/data_snap2 82b649724a0b45a78ef7092762378ad8

…and I can mount it :
[root@rhel8 ~]# mkdir /data_snap2
[root@rhel8 ~]# mount /stratis/pool01/data_snap /data_snap2
[root@rhel8 ~]#

But the file inside seems to have changed (corruption) :
[root@rhel8 ~]# md5sum /data/bigfile.txt /data_snap2/bigfile.txt
c9a5a6878d97b48cc965c1e41859f034 /data/bigfile.txt
cde91bbaa4b3355bc04f611405ae4430 /data_snap2/bigfile.txt
[root@rhel8 ~]#

So, the answer is no. Stratis is not able to duplicate a file system online (at least for the moment). Thus I would strongly recommend to un-mount the filesystem before creating a snapshot.

Conclusion

Stratis is an easy-to-use tool for managing local storage on RHEL8 server. But due to its immaturity I would not recommend to use it in a productive environment yet. Moreover some interesting features like raid management or data integrity check are not available for the moment, but I’m quite sure that the tool will evolve quickly !

If you want to know more, all is here.
Enjoy testing Stratis and stay tuned to discover its evolution…

Cet article Red Hat Enterprise Linux 8 – Stratis est apparu en premier sur Blog dbi services.

Introduction

Computing environments became more and more agile over these last years. Companies need to provide solutions helping people to quickly set up new resources, starting and stopping them, scaling them according to the need and finally, removing them. In such environments, it could be tricky to follow license compliance when resources are changing on hourly basis.

Having a look on AWS services, I saw that AWS provides a license managing tool named “AWS License Manager”. I took few minutes in order to:

• Understand which resources this service is able to monitor
• How it works
• Test it with an on-premise Linux server executing an oracle database

License Manager Service

The first step in order to use License Manager is to select it in the list of AWS Services.

After having clicked on AWS License Manager, the AWS License Manager window will appear.

Now, we simply have to create a license configuration with required license terms according to the software vendor. You can setup different kind of metrics such as

• vPCUs
• Cores
• Sockets
• Instances

License Manager also provides the possibility to enforce license limit, meaning that it prevents license usage after available licenses are exhausted.

In a context of on-premise License monitoring, it is important to notice that sockets and cores license’s type are not accepted. Therefore, in this example I used vCPUs.

AWS System Manager

Once the license configuration created, it’s now mandatory to use another AWS Service, AWS System Manager. This service allows you to view and control your infrastructure on AWS. AWS System Manager not only allows you to view and control your Amazon EC2 Instance but also on-premises servers, virtual machines (including VMs in other cloud environments). Some System Manager capabilities are not free, however in the context of this example everything is free.

 

AWS System Manager Agent (SSM Agent)

In order to benefit from AWS System Manager we need to install AWS Systems Manager Agent (SSM Agent) on our on-premised host. Indeed, SSM Agent is an Amazon software that can be installed and configured on an Amazon EC2 instance, an on-premises server, or a virtual machine (VM) and provides a solution to update, manage, and configure resources. SSM Agent is installed, by default on instances created from Windows Server 2016 and Windows Server 2019, Amazon Linux, Ubuntu Server Images AMIs. However, if you are running an on-premise server you need to install it. The process is really straightforward as presented below.

[root@vmrefdba01 ~]# mkdir /tmp/ssm
[root@vmrefdba01 ~]# curl https://s3.amazonaws.com/ec2-downloads-windows/SSMAgent/latest/linux_amd64/amazon-ssm-agent.rpm -o /tmp/ssm/amazon-ssm-agent.rpm
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 18.9M  100 18.9M    0     0  3325k      0  0:00:05  0:00:05 --:--:-- 4368k
[root@vmrefdba01 ~]# sudo yum install -y /tmp/ssm/amazon-ssm-agent.rpm
Loaded plugins: refresh-packagekit, ulninfo
Setting up Install Process
Examining /tmp/ssm/amazon-ssm-agent.rpm: amazon-ssm-agent-2.3.707.0-1.x86_64
Marking /tmp/ssm/amazon-ssm-agent.rpm to be installed
public_ol6_UEK_latest                                    | 2.5 kB     00:00
public_ol6_UEK_latest/primary_db                         |  64 MB     00:07
public_ol6_latest                                        | 2.7 kB     00:00
public_ol6_latest/primary_db                             |  18 MB     00:07
Resolving Dependencies
--> Running transaction check
---> Package amazon-ssm-agent.x86_64 0:2.3.707.0-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================================
 Package                            Arch                     Version                        Repository                           Size
======================================================================================================================================
Installing:
 amazon-ssm-agent                   x86_64                   2.3.707.0-1                    /amazon-ssm-agent                    61 M

Transaction Summary
======================================================================================================================================
Install       1 Package(s)

Total size: 61 M
Installed size: 61 M
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : amazon-ssm-agent-2.3.707.0-1.x86_64                                                                                1/1
amazon-ssm-agent start/running, process 3896
  Verifying  : amazon-ssm-agent-2.3.707.0-1.x86_64                                                                                1/1

Installed:
  amazon-ssm-agent.x86_64 0:2.3.707.0-1

Complete!

 

Creating an activation

Once the agent installed, we have to create a new “Activation” in the AWS System Manager Service by clicking on “Create activation“. At the end of the creation you will get an Activation Code and an Activation ID (in the green field below). You have to keep this information for the agent configuration.

Agent Configuration

In order to register your on-premise instance on AWS, you simply have to execute the following command with the activation code and activation id provided by AWS System Manager

sudo amazon-ssm-agent -register -code "<cf Activation Code>" -id "<cf Activation ID>" -region "us-east-2"

2019-09-19 13:53:05 INFO Successfully registered the instance with AWS SSM using Managed instance-id: mi-0756a9f0dc25be3cd

 

Once registered the Managed Instance should appear as presented below in AWS Systems Manager

The Platform type is detected as well as the Kernel version, IP address and computer name. AWS Systems Manager provides also a package inventory and many other kinds of inventory such as Network inventory, Files inventory, aso…

 

Association between License Configuration and Resource ID

We now have to make the link between the Managed Instance (resource) and the license configuration. The goal of course is to define which license configuration will be applied to which resource. In order to proceed, we have to go into the AWS License Manager, and select “Search Inventory” into the menu. Then we simply have to select the resource and then click on “Associate license Configuration”.

The following window will appear, allowing you to define to which license configuration matches which resource:

Having a look in the AWS License Manager Dashboard, you can see that 1 out of 1 license is consumed since I dedicated one vCPU to my virtual machine and I provided 1vCPU  license to this instance.

Core Messages

• AWS License Manager offers more functionalities for EC2 Instances than for on-premise servers.
• AWS License Manager offers functionalities in order to monitor socket, vCPU, Cores and Instances.
• AWS License Manager definitively helps to manage licenses but doesn’t fit with all requirements and license model.
• AWS Systems Manager is a powerful tool providing several functionalities.

Strenghts

• AWS License Manager is free.
• AWS License Manager offers possibilities to monitor on-premise resources.
• AWS License Manager provides solution in order to prevent instance to run if license compliance doesn’t fit.
• AWS License Manager and AWS System Manager are straightforward to install and configure.
• AWS License Manager and AWS System Manager offers a good documentation.
• AWS System Manager offers many free functionalities (Patch Manager, Session Manager, Insights Dashboard, aso…).
• AWS System Manager offers many  functionalities and is the basis of several other AWS tools such as AWS Config which allows to monitor instance’s compliance.

Weaknesses

• AWS License Manager is not able by default to monitor options usage such as the ones of an Oracle database (Partitioning, Active Data Guard, aso…).
• AWS License Manager is not able to calculate Oracle Processors, meaning taking into consideration core factors.
• AWS System Manager is not able to monitor socket or cores on on-premise resources, only vCPUs.

Cet article Managing Licenses with AWS License Manager est apparu en premier sur Blog dbi services.

Rolling Upgrade is easy

In this blog, I will show you how easy it is with Clustercontrol to perform a Galera cluster “Rolling Upgrade” without any loss of service.
Let’s say we want to upgrade from Percona XtraDB Cluster version 5.6 to 5.7.
The same procedure can be used to upgrade MariaDB (from 10.1 to 10.2 or 10.3)

Prerequisites

First of all, make sure that on your Galera cluster all nodes are synchronized.
From the Dashboard, on the tab Overview, then the Galera Nodes window, in the Last “Committed” column , all 3 figures must be identical.

Then disable from the GUI the “cluster & node auto-recovery”either by clicking on both until it gets red or by setting temporarely on the clustercontrol server in the
/etc/cmon.d/cmon_N.cnf file (N stands for Cluster ID) the 2 following parameters:
– enable_cluster_autorecovery=0
– enable_node_autorecovery=0
don’t forget to restart the cmon service.
systemctl restart cmon
It is very important & even crucial otherwise Clustercontrol will try everytime to restart the Galera node when you will stop it during the upgrade process.

Now we have to put the first Galera node in maintenance mode for one hour.


The Cluster status bar should be now as following.

Cluster Upgrade

Log on the first master node using your favorite terminal emulator “putty” or “MobaXterm”, open 2 sessions and stop the Percona service on the first node.
# service mysql status
SUCCESS! MySQL (Percona XtraDB Cluster) running (19698)
# service mysql stop
Shutting down MySQL (Percona XtraDB Cluster).. SUCCESS!
# service mysql status
ERROR! MySQL (Percona XtraDB Cluster) is not running
Remove now existing Percona XtraDB Cluster and Percona XtraBackup packages
#[root@node1 yum.repos.d]# yum remove percona-xtrabackup* Percona-XtraDB-Cluster*
Loaded plugins: fastestmirror, ovl
Setting up Remove Process
Resolving Dependencies
--> Running transaction check
---> Package Percona-XtraDB-Cluster-56.x86_64 1:5.6.44-28.34.1.el6 will be erased
---> Package Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.44-28.34.1.el6 will be erased
---> Package Percona-XtraDB-Cluster-galera-3.x86_64 0:3.34-1.el6 will be erased
---> Package Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.44-28.34.1.el6 will be erased
---> Package Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.44-28.34.1.el6 will be erased
---> Package percona-xtrabackup.x86_64 0:2.3.10-1.el6 will be erased
--> Finished Dependency Resolution
Removed:
Percona-XtraDB-Cluster-56.x86_64 1:5.6.44-28.34.1.el6 Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.44-28.34.1.el6 Percona-XtraDB-Cluster-galera-3.x86_64 0:3.34-1.el6
Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.44-28.34.1.el6 Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.44-28.34.1.el6 percona-xtrabackup.x86_64 0:2.3.10-1.el6
Complete!
Install the new packages
#[root@node1 yum.repos.d]# yum install Percona-XtraDB-Cluster-57
Resolving Dependencies
--> Running transaction check
---> Package Percona-XtraDB-Cluster-57.x86_64 0:5.7.26-31.37.1.el6 will be installed
--> Processing Dependency: Percona-XtraDB-Cluster-client-57 = 5.7.26-31.37.1.el6 for package: Percona-XtraDB-Cluster-57-5.7.26-31.37.1.el6.x86_64
--> Processing Dependency: Percona-XtraDB-Cluster-server-57 = 5.7.26-31.37.1.el6 for package: Percona-XtraDB-Cluster-57-5.7.26-31.37.1.el6.x86_64
--> Running transaction check
---> Package Percona-XtraDB-Cluster-client-57.x86_64 0:5.7.26-31.37.1.el6 will be installed
---> Package Percona-XtraDB-Cluster-server-57.x86_64 0:5.7.26-31.37.1.el6 will be installed
--> Processing Dependency: Percona-XtraDB-Cluster-shared-57 = 5.7.26-31.37.1.el6 for package: Percona-XtraDB-Cluster-server-57-5.7.26-31.37.1.el6.x86_64
--> Processing Dependency: percona-xtrabackup-24 >= 2.4.12 for package: Percona-XtraDB-Cluster-server-57-5.7.26-31.37.1.el6.x86_64
--> Processing Dependency: qpress for package: Percona-XtraDB-Cluster-server-57-5.7.26-31.37.1.el6.x86_64
--> Running transaction check
---> Package Percona-XtraDB-Cluster-shared-57.x86_64 0:5.7.26-31.37.1.el6 will be installed
---> Package percona-xtrabackup-24.x86_64 0:2.4.15-1.el6 will be installed
---> Package qpress.x86_64 0:11-1.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
Installed:
Percona-XtraDB-Cluster-57.x86_64 0:5.7.26-31.37.1.el6
Dependency Installed:
Percona-XtraDB-Cluster-client-57.x86_64 0:5.7.26-31.37.1.el6 Percona-XtraDB-Cluster-server-57.x86_64 0:5.7.26-31.37.1.el6
Percona-XtraDB-Cluster-shared-57.x86_64 0:5.7.26-31.37.1.el6 percona-xtrabackup-24.x86_64 0:2.4.15-1.el6
qpress.x86_64 0:11-1.el6
Complete!
Start the node outside the cluster (in standalone mode) by setting the wsrep_provider variable to none.
$ ps -edf|grep -i mysql
$ mysqld --user=mysql --wsrep-provider='none'
Run now mysql_upgrade in the second session
[root@node1 /]# mysql_upgrade -u root -p
Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.user OK
Upgrading the sys schema.
Checking databases.
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed
When the upgrade is over, stop the mysqld process.
You can either kill the mysqld process ID or use mysqladmin shutdown with the MySQL root user credentials.
$ mysqladmin shutdown -uroot -p
Now you can restart the upgraded node to join the Galera cluster in the first session.
$ service mysql start
Starting Percona-Xtradb-server.190612 13:04:33 mysqld_safe Logging to '/var/log/mysql/mysqld.log'.
190612 13:04:33 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
.. SUCCESS!

Post-exec tasks

From the GUI, disable the maintenance mode and check for the new version by logging in the instance.
[root@node1 /]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 64
Server version: 5.7.27-30-57 Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39
Once the first node is upgraded, you can repeat exactly the same procedure for all the other nodes in the cluster.

Now you can repeat exactly the same procedure for the other nodes of the cluster. At the end, Clustercontrol should display the same version for all nodes.

Conclusion

Rolling upgrade of a Galera cluster with Clustercontrol is really easy and fast with no or very few impact on the service.

Cet article Rolling Upgrade of a Galera Cluster with ClusterControl est apparu en premier sur Blog dbi services.

Point-In-Time Recovery is also possible in a multitenant environment. As in Non-CDB, a recovery catalog can be used or not. In this blog we will see how to recover a dropped tablespace in a PDB. We will also see the importance of using a recovery catalog or not.
A PITR of a PDB does not affect remaining PBDs. That means that while doing a PITR in PDB, people can use the other PDBs. In this blog we are using an oracle 19c database with local undo mode enabled

SQL> 
  1  SELECT property_name, property_value
  2  FROM   database_properties
  3* WHERE  property_name = 'LOCAL_UNDO_ENABLED'

PROPERTY_NAME        PROPE
-------------------- -----
LOCAL_UNDO_ENABLED   TRUE
SQL>

SELECT con_id, tablespace_name FROM   cdb_tablespaces WHERE  tablespace_name LIKE 'UNDO%';

    CON_ID TABLESPACE_NAME
---------- ------------------------------
         3 UNDOTBS1
         4 UNDOTBS1
         1 UNDOTBS1

SQL>

We suppose that
-We have a tablespace named MYTABPDB2
-We have a valid backup of the whole database
-A recovery catalog is not used

Now connecting to the PDB2, let’s drop a tablespace after creating a restore point.

SQL> show con_name;

CON_NAME
------------------------------
PDB2

SQL> create restore point myrestpoint;

Restore point created.

SQL>
SQL> drop tablespace mytabpdb2 including contents and datafiles;

Tablespace dropped.

SQL>

And now let’s perform a PITR to the restore point myrestpoint

1- Connect to the root container

[oracle@oraadserver ~]$ rman target /

[oracle@oraadserver ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Sep 20 13:07:07 2019
Version 19.3.0.0.0

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

connected to target database: ORCL (DBID=1546409981)

RMAN>

2- Close the PDB

RMAN> ALTER PLUGGABLE DATABASE PDB2 close;

using target database control file instead of recovery catalog
Statement processed

RMAN>

3- Do the PITR

RMAN> run
{
  SET TO RESTORE POINT myrestpoint;
   RESTORE PLUGGABLE DATABASE pdb2;
   RECOVER PLUGGABLE DATABASE pdb2;
}2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 20-SEP-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=54 device type=DISK

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 00013 to /u01/app/oracle/oradata/ORCL/pdb2/system01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/ORCL/pdb2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/ORCL/pdb2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00016 to /u01/app/oracle/oradata/ORCL/pdb2/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_20/o1_mf_nnndf_TAG20190920T141945_gr9jzry9_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_20/o1_mf_nnndf_TAG20190920T141945_gr9jzry9_.bkp tag=TAG20190920T141945
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 20-SEP-19

Starting recover at 20-SEP-19
current log archived
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 20-SEP-19

RMAN>

4- Open the PDB on resetlogs mode

RMAN> alter pluggable DATABASE  pdb2 open resetlogs;

Statement processed

RMAN>

I did not get any error from RMAN, but when looking the alert log file, I have following errors

PDB2(4):Pluggable database PDB2 dictionary check beginning
PDB2(4):Tablespace 'MYTABPDB2' #7 found in data dictionary,
PDB2(4):but not in the controlfile. Adding to controlfile.
PDB2(4):File #25 found in data dictionary but not in controlfile.
PDB2(4):Creating OFFLINE file 'MISSING00025' in the controlfile.
PDB2(4):Pluggable Database PDB2 Dictionary check complete
PDB2(4):Database Characterset for PDB2 is AL32UTF8

Seems there is some issue with the recovery of MYTABPDB2 tablespace. Connected to PDB2 I can have

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='MYTABPDB2';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/product/19.0.0/dbhome_3/dbs/MISSING00025
MYTABPDB2

The tablespace was not recovered as expected.
What happens? In fact this issue is expected according Doc ID 2435452.1 where we can find
If the point in time recovery of the pluggable database is performed without the catalog, then it is expected to fail

As we are not using a recovery catalog, backup information are stored in the control file and it seems that the actual control file is no longer aware of the data file 25.
As specified in the document, we have to use a recovery catalog

Now let’s connect to a catalog and do again the same PITR
After connecting to the catalog we do a full backup. Then we drop the tablespace and run again the same recovery command while connecting to the catalog. We use the time before the tablespace was dropped.

[oracle@oraadserver trace]$ rman catalog rman/rman@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Sep 20 15:28:29 2019
Version 19.3.0.0.0

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

connected to recovery catalog database

RMAN> connect target /

connected to target database: ORCL (DBID=1546409981)

After closing PDB2 we run following bloc

RMAN> run
{
  SET UNTIL TIME "to_date('20-SEP-2019 15:27:00','DD-MON-YYYY HH24:MI:SS')";
   RESTORE PLUGGABLE DATABASE pdb2;
   RECOVER PLUGGABLE DATABASE pdb2;
}
2> 3> 4> 5> 6>
executing command: SET until clause

Starting restore at 20-SEP-19
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 00013 to /u01/app/oracle/oradata/ORCL/pdb2/system01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/ORCL/pdb2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/ORCL/pdb2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00016 to /u01/app/oracle/oradata/ORCL/pdb2/users01.dbf
channel ORA_DISK_1: restoring datafile 00026 to /u01/app/oracle/oradata/ORCL/pdb2/mytabpdb201.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_20/o1_mf_nnndf_TAG20190920T152554_gr9nws0x_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_20/o1_mf_nnndf_TAG20190920T152554_gr9nws0x_.bkp tag=TAG20190920T152554
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

datafile 26 switched to datafile copy
input datafile copy RECID=5 STAMP=1019489668 file name=/u01/app/oracle/oradata/ORCL/pdb2/mytabpdb201.dbf
Finished restore at 20-SEP-19
starting full resync of recovery catalog
full resync complete

Starting recover at 20-SEP-19
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 20-SEP-19

RMAN>

We then open PDB2 with resetlogs mode and then verify with sqlplus

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='MYTABPDB2';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/ORCL/pdb2/mytabpdb201.dbf
MYTABPDB2


SQL>

And this time the PITR works fine. The tablespace was restored.

Conclusion

As seen in this blog, it is recommended to use a recovery catalog when coming to do some PITR operations in a multitenant environment.

Cet article Oracle 19c : Point-In-Time Recovery in a PDB est apparu en premier sur Blog dbi services.

Today was the OpenShift Partner Introduction Day by RedHat. This event happened at Lausanne
There were people with different backgrounds.

After presenting the RedHat compagny, the speaker explained what is Openshifts and why people must adopt it.
What we will retain is that OpenShift is trusted enterprise Kubernetes

With OpenShift we can for example
-Automated, full-stack installation from the container host to application services
-Seamless Kubernetes deployment to any cloud or on-premises environment
-Autoscaling of cloud resources
-One-click updates for platform, services,and applications

The new features in the version 4.1 were presented. The speaker also showed the Red Hat Openshift business value

The notions of Ansible Playbooks, Operator, CRI-O, Helm … were also explained.
The speaker also did a little demonstration of creating a small project with OpenShift.
Below the speaker during the demonstration

This was a very interesting event. It was general and allowed people to understand where Openshift is located in the architecture of containerization. But we have to retain that there are lot of components to understand when using OpenShift

Cet article OpenShift 4.1 Partner Introduction Day est apparu en premier sur Blog dbi services.

The Oracle Multitenant architecture came with Oracle 12c a few years ago. For people usually working with traditional Non-CDB database it might be confusing the first time to do Backup and Recovery with pluggable databases (PDBs)
In this document we are trying to explain how to use RMAN backup and recovery in a multitenant environment for an oracle 19c database with 2 pluggable databases.
Below the configuration we are using.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

We will not use a recovery catalog, but the use of recovery is the same than in a non-CDB environment.
Note that starting with Oracle 19c, we can now connect to a recovery catalog when the target database is a PDB.

Whole CDB Backups

Backin up a whole CDB is like backing up non-CDB database. We have to backup
-root pdb
-all pluggable databases
-archived logs
The steps are:
1- Connect to the root container with a common user having SYSBACKUP or SYSDBA privileges

RMAN> connect target /

connected to target database: ORCL (DBID=1546409981)

RMAN>

2- Launch the backup

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Starting backup at 11-SEP-19
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=279 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=1 sequence=6 RECID=1 STAMP=1018632767
input archived log thread=1 sequence=7 RECID=2 STAMP=1018690452
input archived log thread=1 sequence=8 RECID=3 STAMP=1018691169
input archived log thread=1 sequence=9 RECID=4 STAMP=1018693343
channel ORA_DISK_1: starting piece 1 at 11-SEP-19
channel ORA_DISK_1: finished piece 1 at 11-SEP-19
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2019_09_11/o1_mf_annnn
...
...
Finished backup at 11-SEP-19

Starting Control File and SPFILE Autobackup at 11-SEP-19
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2019_09_11/o1_mf_s_1018693411_gqkcr46z_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 11-SEP-19

Oracle also recommends to backup sometimes the root container.
Once connected to the root container with a common user, run the backup command

RMAN> BACKUP DATABASE ROOT;

Starting backup at 11-SEP-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-SEP-19
channel ORA_DISK_1: finished piece 1 at 11-SEP-19
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2019_09_11/o1_mf_nnndf_TAG20190911T103019_gqkd4vxb_.bkp tag=TAG20190911T103019 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 11-SEP-19

Starting Control File and SPFILE Autobackup at 11-SEP-19
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2019_09_11/o1_mf_s_1018693836_gqkd5d65_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 11-SEP-19

RMAN>

PDBSs Backups

Backing up PDBs is not difficult, there are just some mechanisms to know. When connecting with RMAN to the root container, we can back up one or more PDBs while directly connecting to a PDB, we can only back up this PDB.
1- Connect to the root container with a common user having SYSBACKUP or SYSDBA privileges

RMAN> connect target /

connected to target database: ORCL (DBID=1546409981)

RMAN>

And backup individual PDBs

RMAN> BACKUP PLUGGABLE DATABASE PDB1,PDB2;

Starting backup at 11-SEP-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
...
...
Starting Control File and SPFILE Autobackup at 11-SEP-19
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2019_09_11/o1_mf_s_1018695111_gqkff85l_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 11-SEP-19

RMAN>

2- Connecting to the PDBs with a local user having SYSBACKUP or SYSDBA privileges
PDB1

RMAN> connect target sys/root@pdb1

connected to target database: ORCL:PDB1 (DBID=4178439423)

RMAN> BACKUP DATABASE;

Starting backup at 11-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=279 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/pdb1/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/pdb1/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/pdb1/undotbs01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/pdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-SEP-19
channel ORA_DISK_1: finished piece 1 at 11-SEP-19
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/92359B0BEC8B4545E0531502A8C0F64E/backupset/2019_09_11/o1_mf_nnndf_TAG20190911T110707_gqkg9w5n_.bkp tag=TAG20190911T110707 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 11-SEP-19

RMAN>

PDB2

RMAN> connect target sys/root@pdb2

connected to target database: ORCL:PDB2 (DBID=3996013191)

RMAN>  BACKUP DATABASE;

Starting backup at 11-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=279 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=/u01/app/oracle/oradata/ORCL/pdb2/sysaux01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/ORCL/pdb2/system01.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/ORCL/pdb2/undotbs01.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/ORCL/pdb2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-SEP-19
channel ORA_DISK_1: finished piece 1 at 11-SEP-19
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_11/o1_mf_nnndf_TAG20190911T110844_gqkgdwmm_.bkp tag=TAG20190911T110844 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 11-SEP-19

RMAN>

Tablespace Backup in a PDB

Tablespaces in different PDBs can have the same name. So to eliminate ambiguity always connect to the PDB you want to back up tablespaces.
1- Connect to the PDB with a local user having SYSBACKUP or SYSDBA privilege

[oracle@oraadserver ~]$ rman target sys/root@pdb1

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 11 11:35:53 2019
Version 19.3.0.0.0

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

connected to target database: ORCL:PDB1 (DBID=4178439423)

2- Issue the BACKUP TABLESPACE command

RMAN> BACKUP TABLESPACE USERS;

Starting backup at 11-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=290 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/pdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-SEP-19
channel ORA_DISK_1: finished piece 1 at 11-SEP-19
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/92359B0BEC8B4545E0531502A8C0F64E/backupset/2019_09_11/o1_mf_nnndf_TAG20190911T113623_gqkj0qxl_.bkp tag=TAG20190911T113623 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-SEP-19

RMAN>

Data File Backup in a PDB

Data Files are identified by a unique number across the CDB. So for the Backup we can connect either to the root container or directly to the PDB.
Note that while directly connecting to the PDB, we can only backup files belonging to this PDB.

1- Connect to the root container with a common user having SYSBACKUP or SYSDBA privileges

[oracle@oraadserver admin]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 11 11:54:42 2019
Version 19.3.0.0.0

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

connected to target database: ORCL (DBID=1546409981)

2- Backup the Data File

RMAN> BACKUP DATAFILE 10;

Starting backup at 11-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=274 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/pdb1/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 11-SEP-19
channel ORA_DISK_1: finished piece 1 at 11-SEP-19
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/92359B0BEC8B4545E0531502A8C0F64E/backupset/2019_09_11/o1_mf_nnndf_TAG20190911T115504_gqkk3s44_.bkp tag=TAG20190911T115504 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 11-SEP-19

Starting Control File and SPFILE Autobackup at 11-SEP-19
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2019_09_11/o1_mf_s_1018698908_gqkk3wwt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 11-SEP-19

RMAN>

Whole CDB Complete Recovery

Suppose we lose all Data Files, Control Files and Redo Log Files of the whole container. We can restore with the following steps
1- Restore Control Files while connecting to the root container

[oracle@oraadserver ORCL]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 11 14:25:25 2019
Version 19.3.0.0.0

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area     872413680 bytes

Fixed Size                     9140720 bytes
Variable Size                297795584 bytes
Database Buffers             557842432 bytes
Redo Buffers                   7634944 bytes

RMAN>

RMAN> restore controlfile FROM AUTOBACKUP;

Starting restore at 11-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2019_09_11/o1_mf_s_1018698908_gqkk3wwt_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2019_09_11/o1_mf_s_1018698908_gqkk3wwt_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/ORCL/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl
Finished restore at 11-SEP-19
RMAN>

2- Mount the CDB

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

3- List Backup of archived logs

RMAN> list backup of archivelog all;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
2       397.52M    DISK        00:00:04     11-SEP-19
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20190911T102225
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2019_09_11/o1_mf_annnn_TAG20190911T102225_gqkcp1k9_.bkp

  List of Archived Logs in backup set 2
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    6       2120330    10-SEP-19 2155559    10-SEP-19
  1    7       2155559    10-SEP-19 2257139    11-SEP-19
  1    8       2257139    11-SEP-19 2327294    11-SEP-19
  1    9       2327294    11-SEP-19 2342937    11-SEP-19

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       5.00K      DISK        00:00:00     11-SEP-19
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20190911T102330
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2019_09_11/o1_mf_annnn_TAG20190911T102330_gqkcr2n1_.bkp

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    10      2342937    11-SEP-19 2342996    11-SEP-19

RMAN>

4- Restore the database according to the sequence

RMAN> restore database until sequence 11;

Starting restore at 11-SEP-19
Starting implicit crosscheck backup at 11-SEP-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
Crosschecked 18 objects
...
...
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 11-SEP-19

RMAN>

5- Recover the database

RMAN> recover database until sequence 11;

Starting recover at 11-SEP-19
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2019_09_11/o1_mf_1_10_gqkcr27d_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2019_09_11/o1_mf_1_10_gqkcr27d_.arc thread=1 sequence=10
media recovery complete, elapsed time: 00:00:00
Finished recover at 11-SEP-19

RMAN>

6- Open database in Resetlogs mode

RMAN> alter database open resetlogs;

Statement processed

RMAN>

PDBs Complete Recovery

To recover a PDB we can :
– Connect to the root and then use the RESTORE PLUGGABLE DATABASE and RECOVER PLUGGABLE DATABASE commands.
1- Close the PDB to recover

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           READ WRITE NO
SQL>

2- Connect to rman on the root container and issue the restore command

RMAN> RESTORE PLUGGABLE DATABASE  PDB1;

Starting restore at 11-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=56 device type=DISK

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 00009 to /u01/app/oracle/oradata/ORCL/pdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/ORCL/pdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/ORCL/pdb1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ORCL/pdb1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/92359B0BEC8B4545E0531502A8C0F64E/backupset/2019_09_11/o1_mf_nnndf_TAG20190911T144816_gqkv9btm_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/92359B0BEC8B4545E0531502A8C0F64E/backupset/2019_09_11/o1_mf_nnndf_TAG20190911T144816_gqkv9btm_.bkp tag=TAG20190911T144816
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 11-SEP-19

3- Recover the pluggable database

RMAN> RECOVER PLUGGABLE DATABASE  PDB1;

Starting recover at 11-SEP-19
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 11-SEP-19

4- Open the pluggable database

RMAN> alter pluggable database PDB1 open;

Statement processed

RMAN>

– Connect to the PDB and use the RESTORE DATABASE and RECOVER DATABASE commands.
1- Close the PDB to recover

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED
SQL>

2- Connect to the PDB and issue the RESTORE DATABASE command

[oracle@oraadserver pdb1]$ rman target sys/root@pdb2

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 11 15:19:03 2019
Version 19.3.0.0.0

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

connected to target database: ORCL:PDB2 (DBID=3996013191, not open)

RMAN> RESTORE DATABASE;

Starting restore at 11-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK

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 00013 to /u01/app/oracle/oradata/ORCL/pdb2/system01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/ORCL/pdb2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/ORCL/pdb2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00016 to /u01/app/oracle/oradata/ORCL/pdb2/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_11/o1_mf_nnndf_TAG20190911T144816_gqkv9tfq_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_11/o1_mf_nnndf_TAG20190911T144816_gqkv9tfq_.bkp tag=TAG20190911T144816
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 11-SEP-19

RMAN>

3- Recover the pluggable database

RMAN> recover database;

Starting recover at 11-SEP-19
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 11-SEP-19

4- Open the database

RMAN> alter database open ;

Statement processed

RMAN>

Complete Tablespace Recovery in a PDB

-Non-SYSTEM Tablespace

To recover a Non-SYSTEM Tablespace in a PDB we can do next steps
1-Put the tablespace offline while connecting to the PDB

SQL> ALTER TABLESPACE MYTAB OFFLINE;

Tablespace altered.

SQL>

2- Connect to the PDB with RMAN and issue the RESTORE TABLESPACE command

[oracle@oraadserver pdb2]$ rman target sys/root@pdb2

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 11 16:52:37 2019
Version 19.3.0.0.0

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

connected to target database: ORCL:PDB2 (DBID=3996013191)

RMAN> RESTORE TABLESPACE MYTAB;

Starting restore at 11-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=274 device type=DISK

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 00017 to /u01/app/oracle/oradata/ORCL/pdb2/mytab01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_11/o1_mf_nnndf_TAG20190911T163708_gql1o4gx_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_11/o1_mf_nnndf_TAG20190911T163708_gql1o4gx_.bkp tag=TAG20190911T163708
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 11-SEP-19

3- Issue the RECOVER TABLESPACE command

RMAN> RECOVER TABLESPACE MYTAB;

Starting recover at 11-SEP-19
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 11-SEP-19

RMAN>

4- Put back the tablespace ONLINE

RMAN> ALTER TABLESPACE MYTAB ONLINE;

Statement processed

RMAN>

-SYSTEM Tablespace

To recover a SYSTEM Tablespace in a PDB

1- Shutdown the entire CDB and Mount it

[oracle@oraadserver pdb2]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 11 17:09:33 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> shut immediate
ORA-01116: error in opening database file 13
ORA-01110: data file 13: '/u01/app/oracle/oradata/ORCL/pdb2/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shut abort;
ORACLE instance shut down.
SQL>

SQL> startup mount
ORACLE instance started.

Total System Global Area  872413680 bytes
Fixed Size                  9140720 bytes
Variable Size             310378496 bytes
Database Buffers          545259520 bytes
Redo Buffers                7634944 bytes
Database mounted.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
SQL>

2- Connect to root container and restore the corresponding files (Files can be identified using command REPORT SCHEMA for example)

RMAN> RESTORE DATAFILE 13;

Starting restore at 11-SEP-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK

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 00013 to /u01/app/oracle/oradata/ORCL/pdb2/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_11/o1_mf_nnndf_TAG20190911T163708_gql1o4gx_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/92359E387C754644E0531502A8C02C00/backupset/2019_09_11/o1_mf_nnndf_TAG20190911T163708_gql1o4gx_.bkp tag=TAG20190911T163708
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 11-SEP-19

RMAN>

3- Recover the Data File

RMAN> RECOVER DATAFILE 13;

Starting recover at 11-SEP-19
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 11-SEP-19

4- Open all containers

RMAN> alter database open;

Statement processed

RMAN> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;

Statement processed

RMAN>

Conclusion
In this blog we basically explain how to use RMAN in a multitenant environment. We did not talked about PITR recovery, we will do it in a coming blog.
Note also that we did not use RMAN commands like LIST FAILURE, ADVISE FAILURE and REPAIR FAILURE. But these commands also work.

Cet article RMAN in a Multitenant Environment est apparu en premier sur Blog dbi services.

Currently the Red Hat Forum 2019 in Zürich is ongoing and people just finished lunch before the more technical sessions are starting.

As expected a lot is around OpenShift 4 and automation with Ansible. As dbi is a Red Hat advanced business partner we took the opportunity to be present with a booth for getting in touch with our existing customers and to meet new people:

All the partners got their logo on a huge wall at the entrance to the event:

As the event is getting more and more popular, Red Hat moved to the great and huge location, the Stage One in Zürich Oerlikon. So all of the 850 participants found their space.

There is even space for some fun stuff:

Important as well: the catering was excellent:

The merger with IBM was an important topic and Red Hat again stated several times: Red Hat will stay Red Hat. Let’s see what happens here, not all people trust this statement. All in all it is a great atmosphere here in Oerlikon, great people to discuss with, interesting topics, great organization and a lot of “hybrid cloud”. Technology is moving fast and Red Hat is trying to stay at the front. From a partner perspective the Forum is a great chance to meet the right people within Red Hat, no matter what topic you want to discuss: Technology, marketing, training, whatever. I am pretty sure we will attend the next forum as well.

Cet article Red Hat Forum Zürich, 2019, some impressions est apparu en premier sur Blog dbi services.

SQL Server 2019 RC1 was released out a few weeks ago and it is time to start blogging about my favorite core engine features that will be shipped with the next version of SQL Server. Things should not be completely different with the RTM, so let’s introduce the accelerated database recovery (aka ADR) which is mainly designed to solve an annoying issue that probably most of SQL Server DBAs already faced at least one time: long running transactions that impact the overall recovery time. As a reminder with current versions of SQL Server, database recovery time is tied to the largest transaction at the moment of the crash. This is even more true in high-critical environments where it may have a huge impact on the service or application availability and ADR is another feature that may help for sure.

Image from Microsoft documentation

In order to allow very fast rollback and recovery process the SQL Server team redesigned completely the SQL database engine recovery process and the interesting point is they have introduced row-versioning to achieve it. Row-versioning, however, exist since the SQL Server 2005 version through RCSI and SI isolation levels and from my opinion this is finally good news to extend (finally) such capabilities to address long recovery time.

Anyway, I performed some testing to get an idea of what could be the benefit of ADR and the impact of the workload as well. Firstly, I performed a recovery test without ADR and after initiating a long running transaction, I simply crashed my SQL Server instance. I used an AdventureWorks database with the dbo.bigTransactionHistory table which is big enough (I think) to get a relevant result.

The activation of ADR is per database meaning that row-versioning is also managed locally per database. It allows a better workload isolation compared to using the global tempdb version store with previous SQL Server versions.

USE AdventureWorks_dbi;

ALTER DATABASE AdventureWorks_dbi SET
    ACCELERATED_DATABASE_RECOVERY = OFF; 

ALTER DATABASE AdventureWorks_dbi SET
	COMPATIBILITY_LEVEL = 150;
GO

 

The dbo.bigtransactionHistory table has only one clustered primary key …

EXEC sp_helpindex 'dbo.bigTransactionHistory';
GO

 

… with 158’272’243 rows and about 2GB of data

EXEC sp_helpindex 'dbo.bigTransactionHistory';
GO

 

I simulated a long running transaction with the following update query that touches every row of the dbo.bigTransactionHistory table to get a relevant impact on the recovery process duration time.

BEGIN TRAN;

UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

 

The related transactions wrote a log of records into the transaction log size as show below:

SELECT 
	DB_NAME(database_id) AS [db_name],
	total_log_size_in_bytes / 1024 / 1024 AS size_MB,
	used_log_space_in_percent AS [used_%]
FROM sys.dm_db_log_space_usage;
GO

 

The sys.dm_tran_* and sys.dm_exec_* DMVs may be helpful to dig into the transaction detail including the transaction start time and log used in the transaction log:

SELECT 
   GETDATE() AS [Current Time],
   [des].[login_name] AS [Login Name],
   DB_NAME ([dtdt].database_id) AS [Database Name],
   [dtdt].[database_transaction_begin_time] AS [Transaction Begin Time],
   [dtdt].[database_transaction_log_bytes_used] / 1024 / 1024 AS [Log Used MB],
   [dtdt].[database_transaction_log_bytes_reserved] / 1024 / 1024 AS [Log Reserved MB],
   SUBSTRING([dest].text, [der].statement_start_offset/2 + 1,(CASE WHEN [der].statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),[dest].text)) * 2 ELSE [der].statement_end_offset END - [der].statement_start_offset)/2) as [Query Text]
FROM 
   sys.dm_tran_database_transactions [dtdt]
   INNER JOIN sys.dm_tran_session_transactions [dtst] ON  [dtst].[transaction_id] = [dtdt].[transaction_id]
   INNER JOIN sys.dm_exec_sessions [des] ON  [des].[session_id] = [dtst].[session_id]
   INNER JOIN sys.dm_exec_connections [dec] ON   [dec].[session_id] = [dtst].[session_id]
   LEFT OUTER JOIN sys.dm_exec_requests [der] ON [der].[session_id] = [dtst].[session_id]
   OUTER APPLY sys.dm_exec_sql_text ([der].[sql_handle]) AS [dest]
GO

 

The restart of my SQL Server instance kicked-in the AdventureWorks_dbi database recovery process. It took about 6min in my case:

EXEC sp_readerrorlog 0, 1, N'AdventureWorks_dbi'

 

Digging further in the SQL Server error log, I noticed the phase2 (redo) and phase3 (undo) of the recovery process that took the most of time (as expected).

However, if I performed the same test with ADR enabled for the AdventureWorks_dbi database …

USE AdventureWorks_dbi;

ALTER DATABASE AdventureWorks_dbi SET
    ACCELERATED_DATABASE_RECOVERY = ON;

 

… and I dig again into the SQL Server error log:

Well, the output above is pretty different but clear and irrevocable: there is a tremendous improvement of the recovery time process here. The SQL Server error log indicates the redo phase took 0ms and the undo phase 119ms. I also tested different variations in terms of long transactions and logs generated in the transaction log (4.5GB, 9.1GB and 21GB) without and with ADR. With the latter database recovery remained fast irrespective to the transaction log size as shown below:

But there is no free lunch when enabling ADR because it is a row-versioning based process which may have an impact on the workload. I was curious to compare the performance of my update queries between scenarios including no row-versioning (default), row-versioning with RCSI only, ADR only and finally both RCSI and ADR enabled. I performed all my tests on a virtual machine quad core Intel® Core i7-6600U CPU @ 2.6Ghz and 8GB of RAM. SQL Server memory is capped to 6GB. The underlying storage for SQL Server data files is hosted on SSD disk Samsung 850 EVO 1TB.

Here the first test I performed. This is the same update I performed previously which touches every row on the dbo.bigTransactionHistory table:

BEGIN TRAN;

UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

 

And here the result with the different scenarios:

Please don’t focus strongly on values here because it will depend on your context but the result answers to the following questions: Does the activation of ADR will have an impact on the workload and if yes is it in the same order of magnitude than RCSI / SI? The results are self-explanatory.

Then I decided to continue my tests by increasing the impact of the long running transaction with additional updates on the same data in order to stress a little bit the version store.

BEGIN TRAN;

UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO
UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO
UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO
UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

 

Here the new results:

This time ADR seems to have a bigger impact than RCSI in my case. Regardless the strict values of this test, the key point here is we have to be aware that enabling ADR will have an impact to the workload.

After performing these bunch of tests, it’s time to get a big picture of ADR design with several components per database including a persisted version store (PVS), a Logical Revert, a sLog and a cleaner process. In this blog post I would like to focus on the PVS component that acts as persistent version store for the concerned database. In other words, with ADR, tempdb will not be used to store row versions anymore. The interesting point is that RCSI / SI row-versioning will continue to be handle through the PVS if ADR is enabled according to my tests.

There is the new added column named is_accelerated_database_recovery_on to the sys.databases system view. In my case both RCSI and ADR are enabled in AdventureWorks_dbi database.

SELECT 
	name AS [database_name],
	is_read_committed_snapshot_on,
	is_accelerated_database_recovery_on
FROM sys.databases
WHERE database_id = DB_ID()

 

The sys.dm_tran_version_store_space_usage DMV displays the total space in tempdb used by the version store for each database whereas the new sys.dm_tran_persistent_version_store_stats DMV provides information related to the new PVS created with the ADR activation.

BEGIN TRAN;

UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

SELECT 
	DB_NAME(database_id) AS [db_name],
	oldest_active_transaction_id,
	persistent_version_store_size_kb / 1024 AS pvs_MB
FROM sys.dm_tran_persistent_version_store_stats;
GO

SELECT 
	database_id,
	reserved_page_count / 128 AS reserved_MB
FROM sys.dm_tran_version_store_space_usage;
GO

 

After running my update query, I noticed the PVS in AdventureWorks_dbi database was used rather the version store in tempdb.

So, getting rid of the version store in tempdb seems to be a good idea and probably more scalable per database but according to my tests and without drawing any conclusion now it may lead to performance considerations … let’s see in the future what happens …

In addition, from a storage perspective, because SQL Server doesn’t use tempdb anymore as version store, my curiosity led  to see what happens behind the scene and how PVS interacts with the data pages where row-versioning comes into play. Let’s do some experiments:

Let’s create the dbo.bigTransationHistory_row_version table from the dbo.bigTransationHistory table with fewer data:

USE AdventureWorks_dbi;
GO

DROP TABLE IF EXISTS [dbo].[bigTransactionHistory_row_version];

SELECT TOP 1 *
INTO [dbo].[bigTransactionHistory_row_version]
FROM [dbo].[bigTransactionHistory]

 

Now, let’s have a look at the data page that belongs to my dbo.bigTransacitonHistory_row_version table with the page ID 499960 in my case:

DBCC TRACEON (3604, -1);
DBCC PAGE (AdventureWorks_dbi, 1, 499960, 3);

 

Versioning info exists in the header but obviously version pointer is set to Null because there is no additional version of row to maintain in this case. I just inserted one.

Let’s update the only row that exists in the table as follows:

BEGIN TRAN;
UPDATE [dbo].[bigTransactionHistory_row_version]
SET Quantity = Quantity + 1

 

The version pointer has been updated (but not sure the information is consistent here or at least the values displayed are weird). One another interesting point is there exists more information than the initial 14 bytes of information we may expect to keep track of the pointers. There is also extra 21 bytes at the end of row as show above. On the other hand, the sys.dm_db_index_physical_stats() DMF has been updated to reflect the PVS information with new columns inrow_*, total_inrow_* and offrow_* and may help to understand some of the PVS internals.

SELECT 
	index_id,
	index_level,
	page_count,
	record_count,
	version_record_count,
	inrow_version_record_count,
	inrow_diff_version_record_count,
	total_inrow_version_payload_size_in_bytes,
	offrow_regular_version_record_count,
	offrow_long_term_version_record_count
FROM sys.dm_db_index_physical_stats(
	DB_ID(), OBJECT_ID('dbo.bigTransactionHistory_row_version'), 
	NULL, 
	NULL, 
	'DETAILED'
)

 

Indeed, referring to the above output and correlating them to results I found inside the data page, I would assume the extra 21 bytes stored in the row seems to reflect a (diff ?? .. something I need to get info) value of the previous row (focus on in_row_diff_version_record_count and in_row_version_payload_size_in_bytes columns).

Furthermore, if I perform the update operation on the same data the storage strategy seems to switch to a off-row mode if I refer again to the sys.dm_db_index_physical_stats() DMF output:

Let’s go back to the DBCC PAGE output to confirm this assumption:

Indeed, the extra payload has disappeared, and it remains only the 14 bytes pointer which has been updated accordingly.

Finally, if I perform multiple updates of the same row, SQL Server should keep the off-row storage and should create inside it a chain of version pointers and their corresponding values.

BEGIN TRAN;

UPDATE [dbo].[bigTransactionHistory_row_version]
SET Quantity = Quantity + 1
GO 100000

 

My assumption is verified by taking a look at the previous DMVs. The persistent version store size has increased from ~16MB to ~32MB and we still have 1 version record in off-row mode meaning there is still one version pointer that references the off-row mode structure for my record.

Finally, let’s introduce the cleaner component. Like the tempdb version store, cleanup of old row versions is achieved by an asynchronous process that cleans page versions that are not needed. It wakes up periodically, but we can force it by executing the sp_persistent_version_cleanup stored procedure.

Referring to one of my first tests, the PVS size is about 8GB.

BEGIN TRAN;

UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO


UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO
UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO
UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO
SELECT 
	DB_NAME(database_id) AS [db_name],
	oldest_active_transaction_id,
	persistent_version_store_size_kb / 1024 AS pvs_MB
FROM sys.dm_tran_persistent_version_store_stats;
GO
-- Running PVS cleanu process
EXEC sp_persistent_version_cleanup

 

According to my tests, the cleanup task took around 6min for the entire PVS, but it was not a blocking process at all as you may see below. As ultimate test, I executed in parallel an update query that touched every row of the same table, but it was not blocked by the cleaner as show below:

This is a process I need to investigate further. Other posts are coming as well .. with other ADR components.

See you!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Cet article Introducing Accelerated Database Recovery with SQL Server 2019 est apparu en premier sur Blog dbi services.

POUG (Pint with Oracle users group) organized his annual conference on 6-7th September in Wroclaw in New Horizons Cinema.

My abstract about “MySQL 8.0 Community: Ready for GDPR?” was accepted, so I had the opportunity to be there.

Sessions

My talk was planned for the first day. New MySQL 8.0 version introduces several improvements about security and these are the main points I discussed:
– Encryption of Redo/Undo and Binary/Relay log files, which comes to enrich existing datafile encryption
– Some password features such as:
* Password Reuse Policy, to avoid a user to always use the same passwords
* Password Verification Policy, to require current password before changing it
* validate_password Component (which replaces the old validate_password Plugin), to define a secure password policy through some system variables and 3 different levels
– New caching_sha2_password plugin, which let you manage authentication in a faster and more secure way
– SQL Roles, to simplify the user access right management

Here some interesting sessions that I attended.

Keep them out of the database!

How to avoid unwanted connections to have access to our database? Flora Barrièle and Martin Berger explained some possibilities.
Following methods have limitations:
– Filter through a firewall, cause we have to involve the network team
– Use a dedicated listener for each instance, cause it’s difficult to manage in case of big number of databases and environments
To solve these issues we can use instead:
– Connection Manager (a sort of listener with in addition a set of rules to define the source, service, activity, destination)
– Access Control List (ACL, a new functionality of Oracle 12.2 which is used to protect PDBs and associated services)
– Logon triggers
– Audit and reports
In conclusion, different solutions exist. First of all we have to know our ecosystem and our environments before deciding to put something in place. Then we should make it as simple as possible, test and check what is the best for our specific situation.

The MacGyver approach

Lothar Flatz explained an approach to analyze what’s wrong with a query and how to fix it when we don’t have a lot of time.
The first step is to optimize, and for this point we have to know how the optimizer works. Then we can enforce new plans (inserting hints, changing statements text, …) and look for the outline.
Sometimes it’s not easy. Lothar’s session ended with this quote: “Performance optimization is not magic: it’s based on knowledge and facts”.

From transportable tablespaces to pluggable databases

Franck Pachot showed different ways to transport data in different Oracle versions:
– Simple logical move through export/import -> slow
– Logical move including direct-path with Data Pump export/import -> flexible, but slow
– Physical transport with RMAN duplicate -> fast, but not cross-versions
– Transportable Tablespaces which provides a mix between logical move (for metadata) and physical transport (for application/user data) -> fast and flexible (cross-versions)
– Physical transport through PDB clone -> fast, efficient, ideal in a multi-tenant environment
– Full Transportable Tablespaces to move user tablespaces and other objects such as roles, users, … -> flexible, ideal to export from 11R2 to 12c and then to non-CDB to multi-tenant, no need to run scripts on dictionary

Data Guard new features

The Oracle MAA (Maximum Availability Architectures) describes 4 HA reference architectures in order to align Oracle capabilities with customer Service Level requirements. Oracle Data Guard can match Silver, Gold and Platinum reference architectures.
Pieter Van Puymbroeck (Oracle Product Manager for Data Guard) talked about following new 19c features:
– Flashback operations are propagated automatically to the standby (requirements: configure standby for flashback database and in MOUNT state first, set DB_FLASHBACK_RETENTION_TARGET)
– Restore points are automatically propagated from the primary to the standby
– On the Active Data Guard standby, the database buffer cache state is preserved during a role change
– Multi-Instance Redo Apply (parallel redo log apply in RAC environments)
– Observe-Only mode to test fast-start failover without having any impact on the production database
– New commands such as “show configuration lag;” to check all members, and to export/import the Broker configuration

Discussion Panel

In the form of a discussion animated by Kamil Stawiarski, and with funny but serious exchanges with the audience, some Oracle Product Managers and other Oracle specialists talked about one of most topical subject today: Cloud vs on-prem. Automation, Exadata Cloud at Customer, Oracle documentation and log files and much more…

Networking moments

Lots of networking moments during this conference: a game in the city center, a speakers dinner, lunch time at the conference, the party in the Grey Music Club.

As usual it was a real pleasure to share knowledge and meet old friends and new faces.
Thanks to Luiza, Kamil and the ORA-600 Database Whisperers for their warm welcome and for the perfect organization of the event.

A suggestion? Don’t miss it next year!

Cet article POUG Conference 2019 est apparu en premier sur Blog dbi services.

Recently, we had a project to modernize a little bit a pretty old Documentum installation. As part of this project, there were a refresh of the Application Server hosting a WebTop 6.8. In this blog, I will be talking about an issue that we faced in encryption & decryption of passwords in the refresh environment. This new environment was using WebLogic 12.1.3 with the latest PSU in conjunction with the JDK 1.8u192. Since WebTop 6.8 P08, the JDK 1.8u111 is supported so a newer version of the JDK8 should mostly be working without much trouble.

To properly deploy a WebTop application, you will need to encrypt some passwords like the Preferences or Preset passwords. Doing so in the new environment unfortunately failed:

[weblogic@wls_01 ~]$ work_dir=/tmp/work
[weblogic@wls_01 ~]$ cd ${work_dir}/
[weblogic@wls_01 work]$
[weblogic@wls_01 work]$ jar -xf webtop_6.8_P27.war WEB-INF/classes WEB-INF/lib
[weblogic@wls_01 work]$
[weblogic@wls_01 work]$ kc="${work_dir}/WEB-INF/classes/com/documentum/web/formext/session/KeystoreCredentials.properties"
[weblogic@wls_01 work]$
[weblogic@wls_01 work]$ sed -i "s,use_dfc_config_dir=[^$]*,use_dfc_config_dir=false," ${kc}
[weblogic@wls_01 work]$
[weblogic@wls_01 work]$ sed -i "s,keystore.file.location=[^$]*,keystore.file.location=${work_dir}," ${kc}
[weblogic@wls_01 work]$
[weblogic@wls_01 work]$ grep -E "^use_dfc_config_dir|^keystore.file.location" ${kc}
use_dfc_config_dir=false
keystore.file.location=/tmp/work
[weblogic@wls_01 work]$
[weblogic@wls_01 work]$ enc_classpath="${work_dir}/WEB-INF/classes:${work_dir}/WEB-INF/lib/*"
[weblogic@wls_01 work]$
[weblogic@wls_01 work]$ java -classpath "${enc_classpath}" com.documentum.web.formext.session.TrustedAuthenticatorTool "MyP4ssw0rd"
Aug 27, 2019 11:02:23 AM java.io.ObjectInputStream filterCheck
INFO: ObjectInputFilter REJECTED: class com.rsa.cryptoj.o.nc, array length: -1, nRefs: 1, depth: 1, bytes: 72, ex: n/a
java.security.UnrecoverableKeyException: Rejected by the jceks.key.serialFilter or jdk.serialFilter property
        at com.sun.crypto.provider.KeyProtector.unseal(KeyProtector.java:352)
        at com.sun.crypto.provider.JceKeyStore.engineGetKey(JceKeyStore.java:136)
        at java.security.KeyStoreSpi.engineGetEntry(KeyStoreSpi.java:473)
        at java.security.KeyStore.getEntry(KeyStore.java:1521)
        at com.documentum.web.formext.session.TrustedAuthenticatorUtils.getSecretKey(Unknown Source)
        at com.documentum.web.formext.session.TrustedAuthenticatorUtils.decryptByDES(Unknown Source)
        at com.documentum.web.formext.session.TrustedAuthenticatorTool.main(TrustedAuthenticatorTool.java:64)
[weblogic@wls_01 work]$

 

As you can see above, the encryption of password is failing with some error. The issue is that starting with the JDK 1.8u171, Oracle introduced some new restrictions. From the Oracle release note (JDK-8189997):

New Features
security-libs/javax.crypto
Enhanced KeyStore Mechanisms
A new security property named jceks.key.serialFilter has been introduced. If this filter is configured, the JCEKS KeyStore uses it during the deserialization of the encrypted Key object stored inside a SecretKeyEntry. If it is not configured or if the filter result is UNDECIDED (for example, none of the patterns match), then the filter configured by jdk.serialFilter is consulted.

If the system property jceks.key.serialFilter is also supplied, it supersedes the security property value defined here.

The filter pattern uses the same format as jdk.serialFilter. The default pattern allows java.lang.Enum, java.security.KeyRep, java.security.KeyRep$Type, and javax.crypto.spec.SecretKeySpec but rejects all the others.

Customers storing a SecretKey that does not serialize to the above types must modify the filter to make the key extractable.

 

On recent versions of Documentum Administrator for example, there is no issue because it complies but for WebTop 6.8, it doesn’t and therefore to be able to encrypt/decrypt the password, you will have to modify the filter. There are several solutions to our current problem:

• Downgrade the JDK: this isn’t a good solution since it might introduce security vulnerabilities and it will also prevent you to upgrade it in the future so…
• Extend the ‘jceks.key.serialFilter‘ definition inside the ‘$JAVA_HOME/jre/lib/security/java.security‘ file: that’s a possibility but it means that any processes using this Java will use the updated filter list. Whether or not that’s fine, it’s up to you
• Override the ‘jceks.key.serialFilter‘ definition using a JVM startup parameter on a per-process basis: better control on which processes are allowed to use updated filters and which ones aren’t

 

So the simplest way, and most probably the better way, to solve this issue is to simply add a command line parameter to specify that you want to allow some additional classes. By default, the ‘java.security‘ provides a list of some classes that are allowed and it ends with ‘!*‘ which means that everything else is forbidden.

[weblogic@wls_01 work]$ grep -A2 "^jceks.key.serialFilter" $JAVA_HOME/jre/lib/security/java.security
jceks.key.serialFilter = java.lang.Enum;java.security.KeyRep;\
  java.security.KeyRep$Type;javax.crypto.spec.SecretKeySpec;!*

[weblogic@wls_01 work]$
[weblogic@wls_01 work]$ grep "^security.provider" $JAVA_HOME/jre/lib/security/java.security
security.provider.1=com.rsa.jsafe.provider.JsafeJCE
security.provider.2=com.rsa.jsse.JsseProvider
security.provider.3=sun.security.provider.Sun
security.provider.4=sun.security.rsa.SunRsaSign
security.provider.5=sun.security.ec.SunEC
security.provider.6=com.sun.net.ssl.internal.ssl.Provider
security.provider.7=com.sun.crypto.provider.SunJCE
security.provider.8=sun.security.jgss.SunProvider
security.provider.9=com.sun.security.sasl.Provider
security.provider.10=org.jcp.xml.dsig.internal.dom.XMLDSigRI
security.provider.11=sun.security.smartcardio.SunPCSC
[weblogic@wls_01 work]$
[weblogic@wls_01 work]$ # Using an empty parameter allows everything (not the best idea)
[weblogic@wls_01 work]$ java -Djceks.key.serialFilter='' -classpath "${enc_classpath}" com.documentum.web.formext.session.TrustedAuthenticatorTool "MyP4ssw0rd"
Encrypted: [4Fc6kvmUc9cCSQXUqGkp+A==], Decrypted: [MyP4ssw0rd]
[weblogic@wls_01 work]$
[weblogic@wls_01 work]$ # Using the default value from java.security causes the issue
[weblogic@wls_01 work]$ java -Djceks.key.serialFilter='java.lang.Enum;java.security.KeyRep;java.security.KeyRep$Type;javax.crypto.spec.SecretKeySpec;!*' -classpath "${enc_classpath}" com.documentum.web.formext.session.TrustedAuthenticatorTool "MyP4ssw0rd"
Aug 27, 2019 12:05:08 PM java.io.ObjectInputStream filterCheck
INFO: ObjectInputFilter REJECTED: class com.rsa.cryptoj.o.nc, array length: -1, nRefs: 1, depth: 1, bytes: 72, ex: n/a
java.security.UnrecoverableKeyException: Rejected by the jceks.key.serialFilter or jdk.serialFilter property
        at com.sun.crypto.provider.KeyProtector.unseal(KeyProtector.java:352)
        at com.sun.crypto.provider.JceKeyStore.engineGetKey(JceKeyStore.java:136)
        at java.security.KeyStoreSpi.engineGetEntry(KeyStoreSpi.java:473)
        at java.security.KeyStore.getEntry(KeyStore.java:1521)
        at com.documentum.web.formext.session.TrustedAuthenticatorUtils.getSecretKey(Unknown Source)
        at com.documentum.web.formext.session.TrustedAuthenticatorUtils.encryptByDES(Unknown Source)
        at com.documentum.web.formext.session.TrustedAuthenticatorTool.main(TrustedAuthenticatorTool.java:63)
[weblogic@wls_01 work]$
[weblogic@wls_01 work]$ # Adding com.rsa.cryptoj.o.nc to the allowed list
[weblogic@wls_01 work]$ java -Djceks.key.serialFilter='com.rsa.cryptoj.o.nc;java.lang.Enum;java.security.KeyRep;java.security.KeyRep$Type;javax.crypto.spec.SecretKeySpec;!*' -classpath "${enc_classpath}" com.documentum.web.formext.session.TrustedAuthenticatorTool "MyP4ssw0rd"
Aug 27, 2019 12:06:14 PM java.io.ObjectInputStream filterCheck
INFO: ObjectInputFilter REJECTED: class com.rsa.jcm.f.di, array length: -1, nRefs: 3, depth: 2, bytes: 141, ex: n/a
java.security.UnrecoverableKeyException: Rejected by the jceks.key.serialFilter or jdk.serialFilter property
        at com.sun.crypto.provider.KeyProtector.unseal(KeyProtector.java:352)
        at com.sun.crypto.provider.JceKeyStore.engineGetKey(JceKeyStore.java:136)
        at java.security.KeyStoreSpi.engineGetEntry(KeyStoreSpi.java:473)
        at java.security.KeyStore.getEntry(KeyStore.java:1521)
        at com.documentum.web.formext.session.TrustedAuthenticatorUtils.getSecretKey(Unknown Source)
        at com.documentum.web.formext.session.TrustedAuthenticatorUtils.encryptByDES(Unknown Source)
        at com.documentum.web.formext.session.TrustedAuthenticatorTool.main(TrustedAuthenticatorTool.java:63)
[weblogic@wls_01 work]$
[weblogic@wls_01 work]$ # Adding com.rsa.jcm.f.* + com.rsa.cryptoj.o.nc to the allowed list
[weblogic@wls_01 work]$ java -Djceks.key.serialFilter='com.rsa.jcm.f.*;com.rsa.cryptoj.o.nc;java.lang.Enum;java.security.KeyRep;java.security.KeyRep$Type;javax.crypto.spec.SecretKeySpec;!*' -classpath "${enc_classpath}" com.documentum.web.formext.session.TrustedAuthenticatorTool "MyP4ssw0rd"
Encrypted: [4Fc6kvmUc9cCSQXUqGkp+A==], Decrypted: [MyP4ssw0rd]
[weblogic@wls_01 work]$

 

So as you can see above, to encrypt passwords for WebTop 6.8 using a JDK 8u171+, you will need to add both ‘com.rsa.cryptoj.o.nc‘ and ‘com.rsa.jcm.f.*‘ in the allowed list. There is a wildcard for the JCM one because it will require several classes from this package.

The above was for the encryption of the password. That’s fine but obviously, when you will deploy WebTop, it will need to decrypt these passwords at some point… So you will also need to put the same JVM parameter for the process of your Application Server (for the Managed Server’s process in WebLogic):

-Djceks.key.serialFilter='com.rsa.jcm.f.*;com.rsa.cryptoj.o.nc;java.lang.Enum;java.security.KeyRep;java.security.KeyRep$Type;javax.crypto.spec.SecretKeySpec;!*'

 

You can change the order of the classes in the list, it just needs to be before the ‘!*‘ section because everything after that is ignored.

 

Cet article Documentum – Encryption/Decryption of WebTop 6.8 passwords ‘REJECTED’ with recent JDK est apparu en premier sur Blog dbi services.

In a recent project on Alfresco, I had to setup a Clustering environment. It all went smoothly but I did face one single issue with the setup of the Clustering on the Alfresco Share layer. That’s something I never faced before and you will understand why below.

Initially, to setup the Alfresco Share Clustering, I used the sample file packaged in the distribution zip (E.g.: alfresco-content-services-distribution-6.1.0.5.zip):

<?xml version='1.0' encoding='UTF-8'?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:hz="http://www.hazelcast.com/schema/spring"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
                http://www.hazelcast.com/schema/spring
                https://hazelcast.com/schema/spring/hazelcast-spring-2.4.xsd">

   <!--
        Hazelcast distributed messaging configuration - Share web-tier cluster config
        - see http://www.hazelcast.com/docs.jsp
        - and specifically http://docs.hazelcast.org/docs/2.4/manual/html-single/#SpringIntegration
   -->
   <!-- Configure cluster to use either Multicast or direct TCP-IP messaging - multicast is default -->
   <!-- Optionally specify network interfaces - server machines likely to have more than one interface -->
   <!-- The messaging topic - the "name" is also used by the persister config below -->
   <!--
   <hz:topic id="topic" instance-ref="webframework.cluster.slingshot" name="slingshot-topic"/>
   <hz:hazelcast id="webframework.cluster.slingshot">
      <hz:config>
         <hz:group name="slingshot" password="alfresco"/>
         <hz:network port="5801" port-auto-increment="true">
            <hz:join>
               <hz:multicast enabled="true"
                     multicast-group="224.2.2.5"
                     multicast-port="54327"/>
               <hz:tcp-ip enabled="false">
                  <hz:members></hz:members>
               </hz:tcp-ip>
            </hz:join>
            <hz:interfaces enabled="false">
               <hz:interface>192.168.1.*</hz:interface>
            </hz:interfaces>
         </hz:network>
      </hz:config>
   </hz:hazelcast>

   <bean id="webframework.cluster.clusterservice" class="org.alfresco.web.site.ClusterTopicService" init-method="init">
      <property name="hazelcastInstance" ref="webframework.cluster.slingshot" />
      <property name="hazelcastTopicName"><value>slingshot-topic</value></property>
   </bean>
   -->

</beans>

 

I obviously uncommented the whole section and configured it properly for the Share Clustering. The above content is only the default/sample content, nothing more.

Once configured, I restarted Alfresco but it failed with the following messages:

24-Aug-2019 14:35:12.974 INFO [main] org.apache.catalina.core.StandardService.startInternal Starting service [Catalina]
24-Aug-2019 14:35:12.974 INFO [main] org.apache.catalina.core.StandardEngine.startInternal Starting Servlet Engine: Apache Tomcat/8.5.34
24-Aug-2019 14:35:12.988 INFO [localhost-startStop-1] org.apache.catalina.startup.HostConfig.deployDescriptor Deploying configuration descriptor [/opt/tomcat/conf/Catalina/localhost/share.xml]
Aug 24, 2019 2:35:15 PM org.apache.jasper.servlet.TldScanner scanJars
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
Aug 24, 2019 2:35:15 PM org.apache.catalina.core.ApplicationContext log
INFO: No Spring WebApplicationInitializer types detected on classpath
Aug 24, 2019 2:35:15 PM org.apache.catalina.core.ApplicationContext log
INFO: Initializing Spring root WebApplicationContext
2019-08-23 14:35:16,052  WARN  [factory.xml.XmlBeanDefinitionReader] [localhost-startStop-1] Ignored XML validation warning
 org.xml.sax.SAXParseException; lineNumber: 18; columnNumber: 92; schema_reference.4: Failed to read schema document 'https://hazelcast.com/schema/spring/hazelcast-spring-2.4.xsd', because 1) could not find the document; 2) the document could not be read; 3) the root element of the document is not <xsd:schema>.
	at java.xml/com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.createSAXParseException(ErrorHandlerWrapper.java:204)
	at java.xml/com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.warning(ErrorHandlerWrapper.java:100)
	at java.xml/com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:392)
	at java.xml/com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:306)
	at java.xml/com.sun.org.apache.xerces.internal.impl.xs.traversers.XSDHandler.reportSchemaErr(XSDHandler.java:4218)
  ... 69 more
Caused by: java.net.ConnectException: Connection refused (Connection refused)
	at java.base/java.net.PlainSocketImpl.socketConnect(Native Method)
	at java.base/java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:399)
	at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:242)
	... 89 more
...
2019-08-23 14:35:16,067  ERROR [web.context.ContextLoader] [localhost-startStop-1] Context initialization failed
 org.springframework.beans.factory.parsing.BeanDefinitionParsingException: Configuration problem: Failed to import bean definitions from relative location [surf-config.xml]
Offending resource: class path resource [web-application-config.xml]; nested exception is org.springframework.beans.factory.parsing.BeanDefinitionParsingException: Configuration problem: Failed to import bean definitions from URL location [classpath*:alfresco/web-extension/*-context.xml]
Offending resource: class path resource [surf-config.xml]; nested exception is org.springframework.beans.factory.xml.XmlBeanDefinitionStoreException: Line 18 in XML document from file [/opt/tomcat/shared/classes/alfresco/web-extension/custom-slingshot-application-context.xml] is invalid; nested exception is org.xml.sax.SAXParseException; lineNumber: 18; columnNumber: 92; cvc-complex-type.2.4.c: The matching wildcard is strict, but no declaration can be found for element 'hz:topic'.
	at org.springframework.beans.factory.parsing.FailFastProblemReporter.error(FailFastProblemReporter.java:68)
	at org.springframework.beans.factory.parsing.ReaderContext.error(ReaderContext.java:85)
	at org.springframework.beans.factory.parsing.ReaderContext.error(ReaderContext.java:76)
  ... 33 more
Caused by: org.springframework.beans.factory.parsing.BeanDefinitionParsingException: Configuration problem: Failed to import bean definitions from URL location [classpath*:alfresco/web-extension/*-context.xml]
Offending resource: class path resource [surf-config.xml]; nested exception is org.springframework.beans.factory.xml.XmlBeanDefinitionStoreException: Line 18 in XML document from file [/opt/tomcat/shared/classes/alfresco/web-extension/custom-slingshot-application-context.xml] is invalid; nested exception is org.xml.sax.SAXParseException; lineNumber: 18; columnNumber: 92; cvc-complex-type.2.4.c: The matching wildcard is strict, but no declaration can be found for element 'hz:topic'.
	at org.springframework.beans.factory.parsing.FailFastProblemReporter.error(FailFastProblemReporter.java:68)
	at org.springframework.beans.factory.parsing.ReaderContext.error(ReaderContext.java:85)
	at org.springframework.beans.factory.parsing.ReaderContext.error(ReaderContext.java:76)
	... 42 more
Caused by: org.springframework.beans.factory.xml.XmlBeanDefinitionStoreException: Line 18 in XML document from file [/opt/tomcat/shared/classes/alfresco/web-extension/custom-slingshot-application-context.xml] is invalid; nested exception is org.xml.sax.SAXParseException; lineNumber: 18; columnNumber: 92; cvc-complex-type.2.4.c: The matching wildcard is strict, but no declaration can be found for element 'hz:topic'.
	at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.doLoadBeanDefinitions(XmlBeanDefinitionReader.java:397)
	at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:335)
	at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:303)
	... 44 more
Caused by: org.xml.sax.SAXParseException; lineNumber: 18; columnNumber: 92; cvc-complex-type.2.4.c: The matching wildcard is strict, but no declaration can be found for element 'hz:topic'.
	at java.xml/com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.createSAXParseException(ErrorHandlerWrapper.java:204)
	at java.xml/com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.error(ErrorHandlerWrapper.java:135)
	at java.xml/com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:396)
	... 64 more
...
24-Aug-2019 14:35:16.196 SEVERE [localhost-startStop-1] org.apache.catalina.core.StandardContext.startInternal One or more listeners failed to start. Full details will be found in the appropriate container log file
24-Aug-2019 14:35:16.198 SEVERE [localhost-startStop-1] org.apache.catalina.core.StandardContext.startInternal Context [/share] startup failed due to previous errors
Aug 24, 2019 2:35:16 PM org.apache.catalina.core.ApplicationContext log
...

 

As you can see above, the message is pretty clear: there is a problem within the file “/opt/tomcat/shared/classes/alfresco/web-extension/custom-slingshot-application-context.xml” which is causing Share to fail to start properly. The first warning message points you directly to the issue: “Failed to read schema document ‘https://hazelcast.com/schema/spring/hazelcast-spring-2.4.xsd’”

After checking the content of the sample file and comparing it with a working one, I found out what was wrong. To solve this specific issue, you can simply replace “https://hazelcast.com/schema/spring/hazelcast-spring-2.4.xsd” with “http://www.hazelcast.com/schema/spring/hazelcast-spring-2.4.xsd“. Please note the two differences in the URL:

• Switch from “https” to “http“
• Switch from “hazelcast.com” to “www.hazelcast.com“

 

The issue was actually caused by the fact that this installation was completely offline, with no access to internet. Because of that, Spring wasn’t able to check for the XSD file to validate the definition in the context file. The solution is therefore to switch the URL to http with www.hazelcast.com so that the Spring internal resolution can understand and use the local file to do the validation and not look for it online.

As mentioned previously, I never faced this issue before for two main reasons:

• I usually don’t use the sample files provided by Alfresco, I always prefer to build my own
• I mainly install Alfresco on servers which have internet access (outgoing communications allowed)

 

Once the URL is corrected, Alfresco Share is able to start and the Clustering is configured properly:

24-Aug-2019 14:37:22.558 INFO [main] org.apache.catalina.core.StandardService.startInternal Starting service [Catalina]
24-Aug-2019 14:37:22.558 INFO [main] org.apache.catalina.core.StandardEngine.startInternal Starting Servlet Engine: Apache Tomcat/8.5.34
24-Aug-2019 14:37:22.573 INFO [localhost-startStop-1] org.apache.catalina.startup.HostConfig.deployDescriptor Deploying configuration descriptor [/opt/tomcat/conf/Catalina/localhost/share.xml]
Aug 24, 2019 2:37:24 PM org.apache.jasper.servlet.TldScanner scanJars
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
Aug 24, 2019 2:37:25 PM org.apache.catalina.core.ApplicationContext log
INFO: No Spring WebApplicationInitializer types detected on classpath
Aug 24, 2019 2:37:25 PM org.apache.catalina.core.ApplicationContext log
INFO: Initializing Spring root WebApplicationContext
Aug 24, 2019 2:37:28 PM com.hazelcast.impl.AddressPicker
INFO: Resolving domain name 'share_n1.domain' to address(es): [10.10.10.10]
Aug 24, 2019 2:37:28 PM com.hazelcast.impl.AddressPicker
INFO: Resolving domain name 'share_n2.domain' to address(es): [127.0.0.1, 10.10.10.11]
Aug 24, 2019 2:37:28 PM com.hazelcast.impl.AddressPicker
INFO: Interfaces is disabled, trying to pick one address from TCP-IP config addresses: [share_n1.domain/10.10.10.10, share_n2.domain/10.10.10.11, share_n2.domain/127.0.0.1]
Aug 24, 2019 2:37:28 PM com.hazelcast.impl.AddressPicker
INFO: Prefer IPv4 stack is true.
Aug 24, 2019 2:37:28 PM com.hazelcast.impl.AddressPicker
INFO: Picked Address[share_n2.domain]:5801, using socket ServerSocket[addr=/0:0:0:0:0:0:0:0,localport=5801], bind any local is true
Aug 24, 2019 2:37:28 PM com.hazelcast.system
INFO: [share_n2.domain]:5801 [slingshot] Hazelcast Community Edition 2.4 (20121017) starting at Address[share_n2.domain]:5801
Aug 24, 2019 2:37:28 PM com.hazelcast.system
INFO: [share_n2.domain]:5801 [slingshot] Copyright (C) 2008-2012 Hazelcast.com
Aug 24, 2019 2:37:28 PM com.hazelcast.impl.LifecycleServiceImpl
INFO: [share_n2.domain]:5801 [slingshot] Address[share_n2.domain]:5801 is STARTING
Aug 24, 2019 2:37:28 PM com.hazelcast.impl.TcpIpJoiner
INFO: [share_n2.domain]:5801 [slingshot] Connecting to possible member: Address[share_n1.domain]:5801
Aug 24, 2019 2:37:28 PM com.hazelcast.nio.ConnectionManager
INFO: [share_n2.domain]:5801 [slingshot] 54991 accepted socket connection from share_n1.domain/10.10.10.10:5801
Aug 24, 2019 2:37:29 PM com.hazelcast.impl.Node
INFO: [share_n2.domain]:5801 [slingshot] ** setting master address to Address[share_n1.domain]:5801
Aug 24, 2019 2:37:35 PM com.hazelcast.cluster.ClusterManager
INFO: [share_n2.domain]:5801 [slingshot]

Members [2] {
	Member [share_n1.domain]:5801
	Member [share_n2.domain]:5801 this
}

Aug 24, 2019 2:37:37 PM com.hazelcast.impl.LifecycleServiceImpl
INFO: [share_n2.domain]:5801 [slingshot] Address[share_n2.domain]:5801 is STARTED
2019-08-23 14:37:37,664  INFO  [web.site.ClusterTopicService] [localhost-startStop-1] Init complete for Hazelcast cluster - listening on topic: share_hz_test
...

 

Cet article Alfresco – Share Clustering fail with ‘Ignored XML validation warning’ est apparu en premier sur Blog dbi services.

Introduction

Oracle & Linux is a great duet. Very powerfull, very scriptable. Here are several commands that make my life easier. These tools seems to be widespread on most of the Linux distributions.

watch with diff

It’s my favorite tool since a long time. watch can repeat a command indefinitely until you stop it with Ctrl+C. And it’s even more useful with the – -diff parameter. All the differences since last run are highlighted. For example if you want to monitor a running backup, try this:

watch -n 60 --diff 'sqlplus -s /nolog @check_backup; echo ; du -hs /backup'

The check_backup.sql being:


conn / as sysdba
set feedback off
set lines 150
set pages 100
col status for a30
alter session set NLS_DATE_FORMAT="DD/MM-HH24:MI:SS";
select start_time "Start", round (input_bytes/1024/1024,1) "Source MB", round(output_bytes/1024/1024,1) "Backup MB", input_type "Type", status "Status", round(elapsed_seconds/60,1) "Min", round(compression_ratio,1) "Ratio" from v$rman_backup_job_details where start_time >= SYSDATE-1 order by 1 desc;
exit;


Every minute (60 seconds), you will check, in the rman backup views, the amount of data already backed up. And the amount of data in your backup folder.

Very convenient to keep an eye on things without actually repeating the commands.

Truncate a logfile in one simple command

Oracle is generating a lot of logfiles, some of them can reach several GB and fill up your filesystem. How to quickly empty a big logfile without removing it? Simply use the true command:

true > listener.log

Run a SQL script on all the running databases

You need to check something on every databases running on your system? Or eventually make the same change to all these databases? A single line will do the job:

for a in `ps -ef | grep pmon | grep -v grep | awk '{print $8}' | cut -c 10- | sort`; do . oraenv <<< $a; sqlplus -s / as sysdba @my_script.sql >> output.log; done

Don’t forget to put an exit at the end of your SQL script my_script.sql. Using this script through ansible will even increase the scope and save hours of work.

Copy a folder to another server

scp is fine for copying single file or multiple files inside a folder. But copying a folder recursively to a remote server with scp is more complicated. Actually, you need to do a tarfile for that purpose. A clever solution is to use tar without creating any archive on the source server, but with a pipe to the destination server. Very useful and efficient, with just one line:

tar cf - source_folder | ssh oracle@192.168.50.167 "cd destination_folder_for_source_folder; tar xf -"

For sure, you will need +rwx on destination_folder_for_source_folder for oracle user on 192.168.50.167.

Check the network speed – because you need to check

As an Oracle DBA you probably have to deal with performance: not a problem it’s part of your job. But are you sure your database system is running at full network speed? You probably didn’t check that, but low network speed could be the root cause of some performance issues. This concerns copper-based networks.

Today’s servers handle 10Gb/s ethernet speed but can also work with 1Gb/s depending on the network behind the servers. You should be aware that you can still find 100Mb/s network speeds, for example if the network port of the switch attached to your server has been limitated for some reason (needed for the server connected to this port before yours for example). If 1Gb/s is probably enough for most of the databases, 100Mb/s is clearly inadequate, and most of the recent servers will even not handle correctly 100Mb/s network speed. Your Oracle environment may work, but don’t expect high performance level as your databases will have to wait for the network to send packets. Don’t forget that 1Gb/s gives you about 100-120MBytes/s in real condition, and 100Mb/s only allows 10-12MBytes/s, “Fast Ethernet” of the 90’s…

Checking the network speed is easy, with ethtool.

[root@oda-x6-2 ~]# ethtool btbond1
Settings for btbond1:
Supported ports: [ ] Supported link modes: Not reported
Supported pause frame use: No
Supports auto-negotiation: No
Advertised link modes: Not reported
Advertised pause frame use: No
Advertised auto-negotiation: No
Speed: 1000Mb/s <= Network speed is OK
Duplex: Full
Port: Other
PHYAD: 0
Transceiver: internal
Auto-negotiation: off
Link detected: yes


In case of a network bonding interface, please also check the real interfaces associated to the bonding, all the network interfaces belonging to the bonding need to have the same network speed :

[root@oda-x6-2 ~]# ethtool em1
Settings for em1:
Supported ports: [ TP ] Supported link modes: 100baseT/Full <= This network interface is physically supporting 100Mb/s
1000baseT/Full <= also 1Gb/s
10000baseT/Full <= and 10Gb/s
Supported pause frame use: Symmetric
Supports auto-negotiation: Yes
Advertised link modes: 100baseT/Full
1000baseT/Full
10000baseT/Full
Advertised pause frame use: Symmetric
Advertised auto-negotiation: Yes
Speed: 1000Mb/s <= Network speed is 1Gb/s
Duplex: Full
Port: Twisted Pair
PHYAD: 0
Transceiver: external
Auto-negotiation: on
MDI-X: Unknown
Supports Wake-on: d
Wake-on: d
Current message level: 0x00000007 (7)
drv probe link
Link detected: yes <= This interface is connected to a switch


Conclusion

Hope this helps!

Cet article Useful Linux commands for an Oracle DBA est apparu en premier sur Blog dbi services.

Sometimes business plans change and maybe you need to move your OpenLeap 15 Server to the supported version SUSE Linux Enterprise Server 15. Upgrade is getting really easy with version 15. It can be performed online. So your server does not need to be offline during the upgrade.

So let’s have a look on the upgrade.

First of all, you need a SUSE Subscription. We will help you with this. Just send us a message.
As soon as you got it you can go on with the upgrade.

Let’s start with checking the actual version running on the server.

openleap:~ $ cat /etc/os-release
NAME="openSUSE Leap"
VERSION="15.0"
ID="opensuse-leap"
ID_LIKE="suse opensuse"
VERSION_ID="15.0"
PRETTY_NAME="openSUSE Leap 15.0"
ANSI_COLOR="0;32"
CPE_NAME="cpe:/o:opensuse:leap:15.0"
BUG_REPORT_URL="https://bugs.opensuse.org"
HOME_URL="https://www.opensuse.org/"

Now we can install SUSEConnect, so we can register the system in the next step.

openleap:~ $ zypper in SUSEConnect
Retrieving repository 'openSUSE-Leap-15.0-Update' metadata ..............................................................................................................................................................[done]
Building repository 'openSUSE-Leap-15.0-Update' cache ...................................................................................................................................................................[done]
Loading repository data...
Reading installed packages...
Resolving package dependencies...

The following 3 NEW packages are going to be installed:
  SUSEConnect rollback-helper zypper-migration-plugin

3 new packages to install.
Overall download size: 138.9 KiB. Already cached: 0 B. After the operation, additional 213.9 KiB will be used.
Continue? [y/n/...? shows all options] (y): y
Retrieving package SUSEConnect-0.3.17-lp150.2.14.1.x86_64                                                                                                                                 (1/3), 100.9 KiB (176.3 KiB unpacked)
Retrieving: SUSEConnect-0.3.17-lp150.2.14.1.x86_64.rpm ..................................................................................................................................................................[done]
Retrieving package rollback-helper-1.0+git20181218.5394d6e-lp150.3.3.1.noarch                                                                                                             (2/3),  22.6 KiB ( 19.9 KiB unpacked)
Retrieving: rollback-helper-1.0+git20181218.5394d6e-lp150.3.3.1.noarch.rpm ..................................................................................................................................[done (7.9 KiB/s)]
Retrieving package zypper-migration-plugin-0.11.1520597355.bcf74ad-lp150.1.1.noarch                                                                                                       (3/3),  15.5 KiB ( 17.6 KiB unpacked)
Retrieving: zypper-migration-plugin-0.11.1520597355.bcf74ad-lp150.1.1.noarch.rpm ..............................................................................................................................[done (253 B/s)]
Checking for file conflicts: ............................................................................................................................................................................................[done]
(1/3) Installing: SUSEConnect-0.3.17-lp150.2.14.1.x86_64 ................................................................................................................................................................[done]
(2/3) Installing: rollback-helper-1.0+git20181218.5394d6e-lp150.3.3.1.noarch ............................................................................................................................................[done]
(3/3) Installing: zypper-migration-plugin-0.11.1520597355.bcf74ad-lp150.1.1.noarch ......................................................................................................................................[done]
openleap:~ # 

Register your system at the SUSE Customer Center, so you get full access to the repositories. This step is mandatory, otherweise it’s not possible to upgrade.

openleap:~ $ SUSEConnect -r REGISTRATION_CODE -p SLES/15/x86_64
Registering system to SUSE Customer Center

Announcing system to https://scc.suse.com ...

Activating SLES 15 x86_64 ...
-> Adding service to system ...
-> Installing release package ...

Successfully registered system

Now check for available extensions and the command to activate it using SUSEConnect

openleap:~ $ SUSEConnect --list-extensions
AVAILABLE EXTENSIONS AND MODULES

    Basesystem Module 15 x86_64
    Activate with: SUSEConnect -p sle-module-basesystem/15/x86_64

        Containers Module 15 x86_64
        Activate with: SUSEConnect -p sle-module-containers/15/x86_64

        Desktop Applications Module 15 x86_64
        Activate with: SUSEConnect -p sle-module-desktop-applications/15/x86_64

            Development Tools Module 15 x86_64
            Activate with: SUSEConnect -p sle-module-development-tools/15/x86_64

            SUSE Linux Enterprise Workstation Extension 15 x86_64
            Activate with: SUSEConnect -p sle-we/15/x86_64 -r ADDITIONAL REGCODE

        SUSE Cloud Application Platform Tools Module 15 x86_64
        Activate with: SUSEConnect -p sle-module-cap-tools/15/x86_64

        SUSE Linux Enterprise Live Patching 15 x86_64
        Activate with: SUSEConnect -p sle-module-live-patching/15/x86_64 -r ADDITIONAL REGCODE

        SUSE Package Hub 15 x86_64
        Activate with: SUSEConnect -p PackageHub/15/x86_64

        Server Applications Module 15 x86_64
        Activate with: SUSEConnect -p sle-module-server-applications/15/x86_64

            Legacy Module 15 x86_64
            Activate with: SUSEConnect -p sle-module-legacy/15/x86_64

            Public Cloud Module 15 x86_64
            Activate with: SUSEConnect -p sle-module-public-cloud/15/x86_64

            SUSE Linux Enterprise High Availability Extension 15 x86_64
            Activate with: SUSEConnect -p sle-ha/15/x86_64 -r ADDITIONAL REGCODE

            Web and Scripting Module 15 x86_64
            Activate with: SUSEConnect -p sle-module-web-scripting/15/x86_64


REMARKS

(Not available) The module/extension is not enabled on your RMT/SMT
(Activated)     The module/extension is activated on your system

MORE INFORMATION

You can find more information about available modules here:
https://www.suse.com/products/server/features/modules.html

In case you need more modules, you can add now any module you need. Please keep in mind, for the extensions you need a separate subscription. For my needs the base module is enough.

openleap:~ $ SUSEConnect -p sle-module-basesystem/15/x86_64
Registering system to SUSE Customer Center

Updating system details on https://scc.suse.com ...

Activating sle-module-basesystem 15 x86_64 ...
-> Adding service to system ...
-> Installing release package ...

Successfully registered system

Next step is to do the upgrade itself. As the output is quite huge, I put some [***] as place holders.

openleap:~ $ zypper dup --force-resolution
Warning: You are about to do a distribution upgrade with all enabled repositories. Make sure these repositories are compatible before you continue. See 'man zypper' for more information about this command.
Refreshing service 'Basesystem_Module_15_x86_64'.
Refreshing service 'SUSE_Linux_Enterprise_Server_15_x86_64'.
Loading repository data...
Warning: Repository 'openSUSE-Leap-15.0-Update-Non-Oss' appears to be outdated. Consider using a different mirror or server.
Reading installed packages...
Computing distribution upgrade...

The following 11 NEW packages are going to be installed:
  dejavu-fonts glibc-locale-base google-opensans-fonts issue-generator kernel-default-4.12.14-lp150.12.67.1 man-pages man-pages-posix release-notes-sles rpcgen yast2-vm zypper-search-packages-plugin

The following 286 packages are going to be upgraded:
  NetworkManager NetworkManager-lang PackageKit PackageKit-backend-zypp PackageKit-gstreamer-plugin PackageKit-gtk3-module PackageKit-lang aaa_base aaa_base-extras apparmor-abstractions 
[***]
[***]
  yast2-storage-ng yast2-users

The following 288 packages have no support information from their vendor:
  NetworkManager NetworkManager-lang PackageKit PackageKit-backend-zypp PackageKit-gstreamer-plugin PackageKit-gtk3-module PackageKit-lang aaa_base aaa_base-extras apparmor-abstractions apparmor-docs apparmor-parser
[***]
[***]

The following package is not supported by its vendor:
  zypper-search-packages-plugin

286 packages to upgrade, 11 new.
Overall download size: 322.4 MiB. Already cached: 0 B. After the operation, additional 343.9 MiB will be used.
Continue? [y/n/...? shows all options] (y): y
Retrieving package issue-generator-1.6-1.1.noarch                                                                                                                                       (1/297),  28.0 KiB ( 25.6 KiB unpacked)
Retrieving: issue-generator-1.6-1.1.noarch.rpm ..........................................................................................................................................................................[done]
Retrieving package man-pages-4.16-3.3.1.noarch                                                                                                                                          (2/297),   
[***]
[***]

Executing %posttrans scripts ............................................................................................................................................................................................[done]
There are some running programs that might use files deleted by recent upgrade. You may wish to check and restart some of them. Run 'zypper ps -s' to list these programs.

Disable the openLeap repository to prevent warnings when using zypper (this is optional).

openleap:~ $ zypper lr -d
Repository priorities are without effect. All enabled repositories share the same priority.

#  | Alias                                                                       | Name                                      | Enabled | GPG Check | Refresh | Priority | Type   | URI                                                                                                                                                                                                                                                        | Service       
---+-----------------------------------------------------------------------------+-------------------------------------------+---------+-----------+---------+----------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------
 1 | Basesystem_Module_15_x86_64:SLE-Module-Basesystem15-Debuginfo-Pool          | SLE-Module-Basesystem15-Debuginfo-Pool    | No      | ----      | ----    |   99     | NONE   | https://updates.suse.com/SUSE/Products/SLE-Module-Basesystem/15/x86_64/product_debug?8YsR5pv4h6qQr15qW8KWqXRBK0MDd9EONPOcnYjrQyXxeU4PVhIX5FRdwf5ziU1Oa8rdtuE2W4NyVotHhKeQrdvQMM9OQ3sEllMJno1VxgQEPq-1QyaCv24cSZsg2H21-d3hQqkxXD3iUKRgNTqHGtkRHHCN71yMa28   | Basesystem_Module_15_x86_64
 2 | Basesystem_Module_15_x86_64:SLE-Module-Basesystem15-Debuginfo-Updates       | SLE-Module-Basesystem15-Debuginfo-Updates | No      | ----      | ----    |   99     | NONE   | https://updates.suse.com/SUSE/Updates/SLE-Module-Basesystem/15/x86_64/update_debug?jjKAgTm0AAAAAAAAq_jTGwRAkx4zc8EQV0ANMjmrFjIoJBofX8ETJPW9qS9ojjVsnoDNK1TRGjk5t31J0Y9Bv_KRzpdYdJVmoH_gO-WaIo-dsZHiDXUm9fjYvLJcjsm0TidUzPnNkAqDAQsPZGZUUCXrek3JjRZl        | Basesystem_Module_15_x86_64
 3 | Basesystem_Module_15_x86_64:SLE-Module-Basesystem15-Pool                    | SLE-Module-Basesystem15-Pool              | Yes     | (r ) Yes  | No      |   99     | rpm-md | https://updates.suse.com/SUSE/Products/SLE-Module-Basesystem/15/x86_64/product?MbepfbRQy5WToAHi4xjhC2KOqjwW00ax8Xj23W9iMukhhWz78BKVY5sSDHiT4nurfz1JyHJrqcqpZiJU-PdajPthp3lQx4hyu-5FzifML0ALTTvKY6XEYA7qlwbn0E6fmA_iSbMl2JOWvZDpeQUZtMlCjQI                 | Basesystem_Module_15_x86_64
 4 | Basesystem_Module_15_x86_64:SLE-Module-Basesystem15-Source-Pool             | SLE-Module-Basesystem15-Source-Pool       | No      | ----      | ----    |   99     | NONE   | https://updates.suse.com/SUSE/Products/SLE-Module-Basesystem/15/x86_64/product_source?86sSfrO8KT3dMsapcn4ihtYRbSwy2kunffEZ6oUiH-vBC-0IkEZQPniCPn63-DeOwlX9brw3vR-BqMNjC9KiOAq0JR0aHZUcyHP5sGhjitLFGTx9zUYo3F4u0KNC3rqIq2WGq-kZEhLm1s2U-vVJHpr6x5RWmMjuBDAe | Basesystem_Module_15_x86_64
 5 | Basesystem_Module_15_x86_64:SLE-Module-Basesystem15-Updates                 | SLE-Module-Basesystem15-Updates           | Yes     | (r ) Yes  | Yes     |   99     | rpm-md | https://updates.suse.com/SUSE/Updates/SLE-Module-Basesystem/15/x86_64/update?WzCCey-NrSLfBHonPxWuaTXt1QuGMemPZsFEhtMfDC_jKtn5XUsqbdI8JZ9D6YNveeYrthpKY2uLTOIB_vtbMQsQUblAr2dU4D59yIBIjZv1l91CLeZD2z61oLPc7ad0UkZjl9R_e6bSNAGP8oz94Fp5                      | Basesystem_Module_15_x86_64
 6 | SUSE_Linux_Enterprise_Server_15_x86_64:SLE-Product-SLES15-Debuginfo-Pool    | SLE-Product-SLES15-Debuginfo-Pool         | No      | ----      | ----    |   99     | NONE   | https://updates.suse.com/SUSE/Products/SLE-Product-SLES/15/x86_64/product_debug?xtsT1GSwugZaHDGElBaTczgwJS79hgJDOy_tkzInodgbplBttQlatgP5rI0SnLQqLCw5WsfSqBIyN_tnMVZn4ZLJ3S3ENBDiZsYhg0vGZf7ILMix03bcXoHEKlzAYRntcEIx877RvS7DDHAAR4cj1V5gzcu6               | SUSE_Linux_Enterprise_Server_15_x86_64
 7 | SUSE_Linux_Enterprise_Server_15_x86_64:SLE-Product-SLES15-Debuginfo-Updates | SLE-Product-SLES15-Debuginfo-Updates      | No      | ----      | ----    |   99     | NONE   | https://updates.suse.com/SUSE/Updates/SLE-Product-SLES/15/x86_64/update_debug?tkJ9rVV33hinQtEBnPYH_5D8OCs1ZtB4WEQFAShIaq1yN6Lwg2-W2Zu2AFALp5Jk3Oh1g1XVBqEOSPnSgACvcCIWuXr_cRfirUHEwbNqIcaSwcjxGjJYdhsb97t01_X-LPT0FDiGGezP64HheC_CzdV6xA                   | SUSE_Linux_Enterprise_Server_15_x86_64
 8 | SUSE_Linux_Enterprise_Server_15_x86_64:SLE-Product-SLES15-Pool              | SLE-Product-SLES15-Pool                   | Yes     | (r ) Yes  | No      |   99     | rpm-md | https://updates.suse.com/SUSE/Products/SLE-Product-SLES/15/x86_64/product?887kGBgH3AfONFY1X3wVkuYn_5nm8sTKex06X1JSRI9gXQNqJioSBea5sAECwbVhqs510L3YRdVlVLgsavZ9D8PPplk8S_oEvhWEQdS-jfFH9dTKcukF09RkjliWQkcaNHkFzY4uQWbHzXJYekkn                             | SUSE_Linux_Enterprise_Server_15_x86_64
 9 | SUSE_Linux_Enterprise_Server_15_x86_64:SLE-Product-SLES15-Source-Pool       | SLE-Product-SLES15-Source-Pool            | No      | ----      | ----    |   99     | NONE   | https://updates.suse.com/SUSE/Products/SLE-Product-SLES/15/x86_64/product_source?XhlzrvfoPp1qTZqv1hErqkUwBGOoZMYY4RAS-c78IKoacswAmOXTemuxa8ZiAFfopgedlQfewbcC7_gxUERoKGdlcW7E4WaqpcuSDYh-xlJr2SG9-4OuxPDToPfZ1CgvDDZIAlqIyXDKGcwvl3EjALH9msDNHg            | SUSE_Linux_Enterprise_Server_15_x86_64
10 | SUSE_Linux_Enterprise_Server_15_x86_64:SLE-Product-SLES15-Updates           | SLE-Product-SLES15-Updates                | Yes     | (r ) Yes  | Yes     |   99     | rpm-md | https://updates.suse.com/SUSE/Updates/SLE-Product-SLES/15/x86_64/update?j0Qh2SfH11scgFCBmZI3R9B4GMJWAh5l6C0P7_jtUle_3dAATzJ2wwwo3SR_dOpn4bBYL4wSkD9bMuCRJlzcmWSkeh1W06Rz8Jsq1KysLODXqUtsBgeE5Tju1Pf-XTpNJF1RQMRRRmb_Tj8RPA                                 | SUSE_Linux_Enterprise_Server_15_x86_64
11 | openSUSE-Leap-15.0-1                                                        | openSUSE-Leap-15.0-1                      | No      | ----      | ----    |   99     | rpm-md | cd:///?devices=/dev/disk/by-id/ata-VBOX_CD-ROM_VB0-01f003f6                                                                                                                                                                                                |               
12 | repo-debug                                                                  | openSUSE-Leap-15.0-Debug                  | No      | ----      | ----    |   99     | NONE   | http://download.opensuse.org/debug/distribution/leap/15.0/repo/oss/                                                                                                                                                                                        |               
13 | repo-debug-non-oss                                                          | openSUSE-Leap-15.0-Debug-Non-Oss          | No      | ----      | ----    |   99     | NONE   | http://download.opensuse.org/debug/distribution/leap/15.0/repo/non-oss/                                                                                                                                                                                    |               
14 | repo-debug-update                                                           | openSUSE-Leap-15.0-Update-Debug           | No      | ----      | ----    |   99     | NONE   | http://download.opensuse.org/debug/update/leap/15.0/oss/                                                                                                                                                                                                   |               
15 | repo-debug-update-non-oss                                                   | openSUSE-Leap-15.0-Update-Debug-Non-Oss   | No      | ----      | ----    |   99     | NONE   | http://download.opensuse.org/debug/update/leap/15.0/non-oss/                                                                                                                                                                                               |               
16 | repo-non-oss                                                                | openSUSE-Leap-15.0-Non-Oss                | Yes     | (r ) Yes  | Yes     |   99     | rpm-md | http://download.opensuse.org/distribution/leap/15.0/repo/non-oss/                                                                                                                                                                                          |               
17 | repo-oss                                                                    | openSUSE-Leap-15.0-Oss                    | Yes     | (r ) Yes  | Yes     |   99     | rpm-md | http://download.opensuse.org/distribution/leap/15.0/repo/oss/                                                                                                                                                                                              |               
18 | repo-source                                                                 | openSUSE-Leap-15.0-Source                 | No      | ----      | ----    |   99     | NONE   | http://download.opensuse.org/source/distribution/leap/15.0/repo/oss/                                                                                                                                                                                       |               
19 | repo-source-non-oss                                                         | openSUSE-Leap-15.0-Source-Non-Oss         | No      | ----      | ----    |   99     | NONE   | http://download.opensuse.org/source/distribution/leap/15.0/repo/non-oss/                                                                                                                                                                                   |               
20 | repo-update                                                                 | openSUSE-Leap-15.0-Update                 | Yes     | (r ) Yes  | Yes     |   99     | rpm-md | http://download.opensuse.org/update/leap/15.0/oss/                                                                                                                                                                                                         |               
21 | repo-update-non-oss                                                         | openSUSE-Leap-15.0-Update-Non-Oss         | Yes     | (r ) Yes  | Yes     |   99     | rpm-md | http://download.opensuse.org/update/leap/15.0/non-oss/                                                                                                                                                                                                     |               
openleap:~ # zypper mr -d 21
Repository 'repo-update-non-oss' has been successfully disabled.

Check for orphaned packages.

openleap:~ $ zypper rm $(zypper --no-refresh packages --orphaned | gawk '{print $5}'  | tail -n +5 )
Too few arguments.
At least one package name is required.
remove (rm) [OPTIONS]  ...

Remove packages with specified capabilities. A capability is NAME[.ARCH][OP], where OP is
one of <, =, >.

  Command options:

-r, --repo     Load only the specified repository.
-t, --type            Type of package (package, patch, pattern, product).
-n, --name                  Select packages by plain name, not by capability.
-C, --capability            Select packages by capability.
-u, --clean-deps            Automatically remove unneeded dependencies.
-U, --no-clean-deps         No automatic removal of unneeded dependencies.
-D, --dry-run               Test the removal, do not actually remove.
    --details               Show the detailed installation summary.
-y, --no-confirm            Don't require user interaction. Alias for the --non-interactive global
                            option.

  Solver options:

    --debug-solver          Create a solver test case for debugging.
    --force-resolution      Force the solver to find a solution (even an aggressive one) rather than
                            asking.
    --no-force-resolution   Do not force the solver to find solution, let it ask.

openleap:~ $ zypper --no-refresh packages --orphaned
Loading repository data...
Reading installed packages...
No packages found.

My whole migration lasts about 30 minutes. But that’s really a small server.

And in the end – you have to reboot, anyway.

openleap:~ $ systemctl reboot

Let’s check if we really run a SLES15 server now.

openleap:~ # cat /etc/os-release
NAME="SLES"
VERSION="15"
VERSION_ID="15"
PRETTY_NAME="SUSE Linux Enterprise Server 15"
ID="sles"
ID_LIKE="suse"
ANSI_COLOR="0;32"
CPE_NAME="cpe:/o:suse:sles:15"

Looks good! The system is running SLES15 now. Now you can enjoy the full support and service of SUSE.

Cet article Upgrading from OpenLeap to SLES15 est apparu en premier sur Blog dbi services.

Introduction

One of the key features of the ODA (Oracle Database Appliance) is the ability to patch the entire stack every three months, the goal being to keep all the components up-to-date. Most of the customers won’t patch so often, but one patch a year is quite a good average. But when comes the time for patching, comes the time for anxiety for the DBA. And it’s totally justified.

Why ODA patching can eventually be a nightmare?

First of all, patching all the products is a complex operation. ODA is not a real appliance: it’s classic hardware composed of parts from various vendors, and with nearly standard software, including Linux, Grid Infrastructure, ASM, ACFS and database engines. And all these products need to be patched together. If you were allowed to patch the ODA components separately, it could last quite a long time. Yes, Oracle provides a single patch for the ODA, but it’s just a bundle of dozen of patches. It’s easier to apply, all together the patches are certified, but it’s still a complex operation to bring all the modules to the target patch level. This is why you can encounter multiple problems. For example if you installed your own RPMs onto the system (unable to update the OS), if you lack some free space (unable to complete the patching), if your databases have specific configuration, or eventually if you discover that there is a bug in the patch related to the version you come from and linked to your ODA model.

Also, some of the patches are not cumulative, meaning that you cannot directly upgrade to the latest version. You sometimes need to apply 4 or 5 patches to upgrade, making the patching even more uncertain.

Starting from these facts, you may think about reimaging, and you’re probably right.

What are the advantages and drawbacks of reimaging?

For sure, reimaging has a lot of advantages:

• Guarantee of success (you start from scratch)
• Cleaner ODA (no question about that)
• Make sure you are able to do the reimage (in case of you really need it)
• Make sure your documentation is good (and this is the only way to validate it!)
• Avoid later problems if patching not worked correctly

These are the drawbacks:

• Longer than a single patch succesfully applied on the first try (who knows)
• Need to erase everything and restart as if it were a new ODA
• You need to know how your ODA was installed and configured (not so simple if someone did the job for you)
• You probably need another ODA with Data Guard or DBVisit to limit the downtime

Can reimaging be quicker than patching?

Patching last about 3 hours if everything is OK. But it’s only for one patch and only if everything is OK. With my patching experience, you probably need to plan 1 day for the first ODA you will patch.

Reimaging also last about 3 hours (more or less depending on your ODA version). But it’s only for reinstalling the software without any database. You will need to restore all your databases, and do all the things you’ve done at the first deployment: copy your scripts, setup your additional software, restore your crontabs, your specific configuration, put back monitoring, and so on.

So, reimaging is probably longer, but you are quite sure to redeploy your ODA in a known time. This is a strong argument. “It will take 8 hours” is always better than “it would take between 3 and 8 hours. Or maybe more. If I succeed”.

How to proceed with patches?

If you need to patch regularly, try to apply the patch on a ODA you can live without. If something goes wrong, you can decide to reimage very quickly instead of opening a SR on MOS. Please don’t get stuck because a patch is not applying correctly, it’s a waste of time.

If you patch every year, consider redeploying instead of patching. It’s probably more work but it will take the same amount of time, with success guarantee (you will love that point). Also, you will ensure that you are able to reimage completely. Reimaging is sometimes also needed if you move your ODA to another datacenter with a network change, so you could have to reimage even for other reasons than patching.

How to make sure that you are able to reimage?

This is the key: be able to reimage

Rules to follow:

• restrict the access on your ODA to only people concerned about the appliance
• document every change you make on the server, even a simple chmod
• never use the GUI to deploy the appliance: deploy your ODA using odacli and save the deployment json file outside of the ODA
• never use the GUI to create the databases: create the database with odacli and backup the used parameters in the documentation
• use scripts to configure your databases (avoid one-shot changes)
• install other products only if necessary: do you really need a backup tool on ODA? NFS backups are great and easy to configure without installing anything
• install only RPMs manually from Oracle ISOs and only if needed
• do everything from the command line and avoid using vi. Text editors prevent you from being able to repeat the exact same operation. For example, replace vi /etc/fstab by echo "srv-nfs:/orabackups /backup nfs rw,bg,hard,nolock,nointr" >> /etc/fstab
• always consider your ODA not so critical by having the possibility to restore your database elsewhere (understand on another ODA), or adopt Data Guard or DBVisit for all your databases that cannot support to be down for hours (even development databases are production for developpers!)
• keep the install zipfiles corresponding to your version somewhere secured to avoid searching for them on MOS the day you need to reimage

Regarding the scripts, I always create a scripts folder in /home/oracle on ODA, and each database has 3 dedicated scripts to speed up the database recreation if needed: create_SID.sh, configure_SID.sql and tbs_SID.sql. First script is for odacli database creation, first SQL script if for specific configuration (controlfile multiplexing for example, disabling the recycle bin or enabling the archive_lag_target, etc). Second SQL script is for tablespace creation. Target is to be able to recreate the database even for datapump-based restore. Make sure to backup these scripts somewhere else.

Few words about RPMs : for me the best way to install additional RPMs on ODA is to download the Oracle Linux ISO corresponding to the version on your ODA (the ISO you would use if you need to deploy a normal server), mount the ISO on your ODA and pickup only the RPMs you need from it (you can also put these few RPMs on /home/oracle/RPMs).

Conclusion

Reimaging should always be considered as an alternative way of patching. Or the best way. Companies already having integrated this are happier with their ODAs. And are taking the best from these appliances.

Cet article Patching or reimaging your ODA? est apparu en premier sur Blog dbi services.

While community PostgreSQL 12 is in beta quite some time now (currently in beta 3) it usually takes some time until EDB EPAS will be available on top of the next major PostgreSQL release. Yesterday EDB finally released a beta and you can register for access here, release notes here. One of the new features is interval partitioning which you already might know from Oracle. Basically you do not need to create range partitions in advance but the system will create the partitions for you once you add data that does not fit into any of the current partitions. Lets see how that works.

Without interval partitioning you would need to create a range partitioned table like this (note that this is Oracle syntax which is supported by EPAS but not by community PostgreSQL):

create table my_part_tab ( id int
                         , dummy text
                         , created date
                         )
                         partition by range (created)
                         ( partition my_part_tab_1 values less than (to_date('01.02.2019','DD.MM.YYYY'))
                         );

The issue with that is, that once you want to add data that does not fit into any of the current partitions you will run into issues like that:

edb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.01.2019','DD.MM.YYYY'));
INSERT 0 1
edb=# select * from my_part_tab;
 id | dummy |      created       
----+-------+--------------------
  1 | aaa   | 05-JAN-19 00:00:00
(1 row)

edb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.02.2019','DD.MM.YYYY'));
psql: ERROR:  no partition of relation "my_part_tab" found for row
DETAIL:  Partition key of the failing row contains (created) = (05-FEB-19 00:00:00).
edb=# 

Only when you create the partition required manually you will be able to store the data (or it goes to a default partition, which comes with its own issues):

edb=# alter table my_part_tab add partition my_part_tab_2 values less than (to_date('01.03.2019','DD.MM.YYYY'));
ALTER TABLE
edb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.02.2019','DD.MM.YYYY'));
INSERT 0 1
edb=# select * from my_part_tab;
 id | dummy |      created       
----+-------+--------------------
  1 | aaa   | 05-JAN-19 00:00:00
  1 | aaa   | 05-FEB-19 00:00:00
(2 rows)
edb=# \d+ my_part_tab
                                     Partitioned table "public.my_part_tab"
 Column  |            Type             | Collation | Nullable | Default | Storage  | Stats target | Descripti
---------+-----------------------------+-----------+----------+---------+----------+--------------+----------
 id      | integer                     |           |          |         | plain    |              | 
 dummy   | text                        |           |          |         | extended |              | 
 created | timestamp without time zone |           |          |         | plain    |              | 
Partition key: RANGE (created) NULLS LAST
Partitions: my_part_tab_my_part_tab_1 FOR VALUES FROM (MINVALUE) TO ('01-FEB-19 00:00:00'),
            my_part_tab_my_part_tab_2 FOR VALUES FROM ('01-FEB-19 00:00:00') TO ('01-MAR-19 00:00:00')

Of course it is not a big deal to create the partitions for the next 20 years in advance but there is a more elegant way of doing this by using interval partitioning:

drop table my_part_tab;
create table my_part_tab ( id int
                         , dummy text
                         , created date
                         )
                         partition by range (created)
                         interval (numtoyminterval(1,'month'))
                         ( partition my_part_tab_1 values less than (to_date('01.02.2019','DD.MM.YYYY'))
                         );

Having the table partitioned like that new partitions will be created on the fly as required:

edb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.01.2019','DD.MM.YYYY'));
INSERT 0 1
edb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.02.2019','DD.MM.YYYY'));
INSERT 0 1
edb=# insert into my_part_tab (id,dummy,created) values (1,'aaa',to_date('05.03.2019','DD.MM.YYYY'));
INSERT 0 1
edb=# \d+ my_part_tab
                                     Partitioned table "public.my_part_tab"
 Column  |            Type             | Collation | Nullable | Default | Storage  | Stats target | Descripti
---------+-----------------------------+-----------+----------+---------+----------+--------------+----------
 id      | integer                     |           |          |         | plain    |              | 
 dummy   | text                        |           |          |         | extended |              | 
 created | timestamp without time zone |           |          |         | plain    |              | 
Partition key: RANGE (created) INTERVAL ('1 mon'::interval)
Partitions: my_part_tab_my_part_tab_1 FOR VALUES FROM (MINVALUE) TO ('01-FEB-19 00:00:00'),
            my_part_tab_sys138880102 FOR VALUES FROM ('01-FEB-19 00:00:00') TO ('01-MAR-19 00:00:00'),
            my_part_tab_sys138880103 FOR VALUES FROM ('01-MAR-19 00:00:00') TO ('01-APR-19 00:00:00')

edb=# 

A nice addition which is not (yet) available in community PostgreSQL.

Cet article EDB EPAS 12 comes with interval partitioning est apparu en premier sur Blog dbi services.

A few weeks ago I attended the Red Hat EMEA Partner Conference for the first time. The Red Hat Conference took place in Prague from June 25th to 27th. If you are interested in Open Source technologies and in Red Hat, feel free to read this personal feedback on trends at Red Hat and in the IT sector.

Stronger Together!

Representing dbi services at the Partner Conference in Prague was a great opportunity for us as a Red Hat Advanced Partner.

About 850 people attended this amusing event! Interactions with the Red Hat Community were very interesting and relaxed. Is it because of the Open Source atmosphere? The organization, catering, and location were great also! Many thanks to the organizers !

Also a sincere thank you to all Swiss Red Hat and Tech Data contacts at the event for welcoming and assisting Swiss Partners during the 3 days. Everything went extremely professional thanks to Leonard Bodmer (Country Manager Red Hat Switzerland), Richard Zobrist (Head of Partner & Alliances Red Hat), Maja Zurovec (Account Manager Partner & Alliances Red Hat), Sandra Maria Sigrist (Tech Data), and Daria Stempkowski (Tech Data). Many thanks to all of you, also for the warm and relaxed evening at Villa Richter at the foot of Prague’s castle !

We are Stronger Together!

All about automation, integration, hybrid cloud, and multi-cloud

With this 3 days Partner Conference, Red Hat proposed a bride agenda of Breakouts, Exams, Hackathon, Keynotes, Labs, and an Open Innovation Lab. I mostly attended sessions where Red Hat partners and customers had the opportunity to give feedbacks on their experience with Red Hat products. Some of the sessions and keynotes were remarkable.

Red Hat Middleware Roadmap

The “Red Hat Middleware Roadmap” sessions (Part 1 & 2) with Rich Sharpels were a good opportunity to learn more about productivity (automation, integration, runtimes), reliability (security, reduced complexity), and flexibility (containers for scaling, cloud, hybrid-cloud, multi-cloud) with OpenShift. With these 2 presentations you also got informed on the iPaaS which is a new private integration Platform-as-a-Service offering to provide cloud-based services for application integration and messaging. The goal is here to strengthen collaboration within the business teams (devOps) thanks to Managed Integration + OpenShift Dedicated. Rich Sharpels summarizes the benefits of the iPaaS with: “cloud services and packages where customers don’t have to administrate anything!”

Ansible Partner Enablement Offerings

Günter Herold from Red Hat and Daniel Knözinger from Open Networks Austria held the session “Ansible Partner Enablement Offerings”. This session was focusing on advantages of automating tasks with Ansible for reducing mistakes, errors, and complexity because “Ansible is the universal language for the whole IT team”. With Ansible, “start small and develop” .

Best Practices for Working with Red Hat Support

Who wanted to get informed on “Best Practices for Working with Red Hat Support” attended the session with Caroline Baillargeon, Leona Meeks, and Peter Jakobs from Red Hat. This presentation gave the opportunity to learn and discuss on:

• The Customer Portal which is said to be “full of information and best practices that could be useful before opening and escalating issues”. For example, should you search for information on Ansible, have a look at this page
• The TSAnet Connect where answers for multi IT solutions are centralized
• The Case Management Tool for sharing the open source spirit and to be part of the community (example)
• Tips to work efficiently with the Red Hat support:
  1. “Make sure the customer is registered on the correct time zone”
  2. “To get 7×24 support, a Premium Support subscription is needed”
  3. “in case the answer on an issue is not appropriate, use the escalation button”
  4. “contact Red Hat to get access to the trainings that also the Red Hat engineers follow for technical problem solving”

While keeping the end customer’s satisfaction in mind, this session could probably be best summarized with “why not contributing and sharing knowledge within the Red Hat community ?”

Keynotes

Keynotes mainly concentrated on “marketing topics” that aim at boosting partner engagement, but still interesting, in particular:

• “The Killer App in digital transformation is human connection” with Margaret Dawson on collaborating within the community
• “Open Hybrid Cloud Ecosystems: Bold Goals for Tomorrow” with Lars Herrmann on innovations that have been considered impossible. In short, “if you think it is impossible, just do it”, so develop cloud, hybrid cloud and multi-cloud opportunities…

 

On Red Hat’s and Partner’s booths at the Congress Center

Besides sessions and presentations, lots of interesting technical booths at the Congress Center Prague did promote the work of the Red Hat engineers within the Open Source community. In particular I spent some time with Romain Pelisse (Red Hat Berlin) and Robert Zahradnícek (Red Hat Brno) to let me explain how they work and what are the trends in their areas. Of course we did speak about automation and integration, and about findings that are developed within the open source community first, before getting implemented in Red Hat solutions.

Last but not least, some Red Hat partners were present with a booth to promote their activities and products during the conference, among which Tech Data and Arrows ECS which are well know at dbi services.

What to take from the Red Hat EMEA Conference? On Red Hat and the Conference

At the end of the day, the keywords from the Red Hat EMEA Conference were probably not that far from the keywords you would get from other technology conferences. Concepts and products like “automation”, “integration”, “Ansible”, or “OpenShift” are means to get companies into the cloud. But why not? The trend into the cloud is getting more and more clear as it now makes sense for lots of projects at least for Disaster Recovery, Test and Development in the cloud.

If private cloud, hybrid cloud, or multi-cloud is not the topic at Red Hat. Their solutions are agnostic. And Red Hat’s strategy is clearly based on a strong commitment to open source. It’s all about “products” (not projects), “collaboration”, “community”, and “customer success”.

On Open Source trends and strategy

Now you may ask why subscribing to Red Hat’s products and support? Sure, with Ansible and other Open Source products you can easily “start small and develop”. Therefore the community version may fit. But what if you go in production? The more and the bigger the projects will become, the more you will need support. And to subscribe will probably make sense.

Then don’t forget that Open Source is not for free. That you go for community or enterprise Open Source makes no difference, at the end you will need to invest at least in time and knowledge. And, depending on the situation, you may subscribe in products and support. If you don’t know where to start, ask dbi services for Open Source expertise.

Looking forward to reading your comments.

Cet article Feedback on the EMEA Red Hat Partner Conference 2019 in Prague est apparu en premier sur Blog dbi services.

For some reason, you may need to sparse OVM virtual disks in an Oracle appliances. Even though that feature is present trough the OVM Manager, most of the Oracle appliances doesn’t have any OVM Manager deployed on it. Therefore if you un-sparse your virtual disk by mistake, you are on your own.

This is a note on how to sparse virtual disks which are un-sparse.

Stop I/Os on the virtual disk

First, ensure the VM using the disk is stopped:
xm shutdown {VM_NAME}

For instance:
xm shutdown exac01db01.domain.local

Sparse disk


dd if={PATH_TO_DISK_TO_BE_SPARSED} of={PATH_TO_NEW_SPARSED_DISK} conv=sparsed


For instance:

dd if=/EXAVMIMAGES/GuestImages/exac01db01.domain.local/vdisk_root_01.img \
of=/staging/vdisk_root_01.img \
conv=sparsed


Move disk to former location

After the sparsing operation finished, copy the disk back to their former location:

# Retrieve the disks path:
cat /EXAVMIMAGES/GuestImages/{VM_NAME}/vm.cfg | grep disk
# Copy each disk back to its location:
mv /staging/{DISK_NAME}.img /EXAVMIMAGES/GuestImages/{VM_NAME}/{DISK_NAME}.img


For instance:

mv /staging/vdisk_root_01.img /EXAVMIMAGES/GuestImages/exac01db01.domain.local/vdisk_root_01.img


Start back the VM

Then you can start back the VM which use the new disk:
xm create /EXAVMIMAGES/GuestImages/{VM_NAME}/vm.cfg

I hope this helps and please contact us or comment below should you need more details.

Cet article Sparse OVM virtual disks on appliances est apparu en premier sur Blog dbi services.

In previous blogs, I talked about some basis and presented some possible architectures for Alfresco, I talked about the Clustering setup for the Alfresco Repository, the Alfresco Share and for ActiveMQ. I also setup an Apache HTTPD as a Load Balancer. In this one, I will talk about the last layer that I wanted to present, which is Solr and more particularly Solr6 (Alfresco Search Services) Sharding. I planned on writing a blog related to Solr Sharding Concepts & Methods to explain what it brings concretely but unfortunately, it’s not ready yet. I will try to post it in the next few weeks, if I find the time.

 

I. Solr configuration modes

So, Solr supports/provides three configuration modes:

• Master-Slave
• SolrCloud
• Standalone

Master-Slave: It’s a first specific configuration mode which is pretty old. In this one, the Master node is the only to index the content and all the Slave nodes will replicate the Master’s index. This is a first step to provide a Clustering solution with Solr, and Alfresco supports it, but this solution has some important drawbacks. For example, and contrary to an ActiveMQ Master-Slave solution, Solr cannot change the Master. Therefore, if you lose your Master, there is no indexing happening anymore and you need to manually change the configuration file on each of the remaining nodes to specify a new Master and target all the remaining Slaves nodes to use the new Master. This isn’t what I will be talking about in this blog.

SolrCloud: It’s another specific configuration mode which is a little bit more recent, introduced in Solr4 I believe. SolrCloud is a true Clustering solution using a ZooKeeper Server. It adds an additional layer on top of a Standalone Solr which is slowing it down a little bit, especially on infrastructures with a huge demand on indexing. But at some points, when you start having dozens of Solr nodes, you need a central place to organize and configure them and that’s what SolrCloud is very good at. This solution provides Fault Tolerance as well as High Availability. I’m not sure if SolrCloud could be used by Alfresco because sure SolrCloud also has Shards and its behaviour is pretty similar to a Standalone Solr but it’s not entirely working in the same way. Maybe it’s possible, however I have never seen it so far. Might be the subject of some testing later… In any cases, using a SolrCloud for Alfresco might not be that useful because it’s really easier to setup a Master-Master Solr mixed with Solr Sharding for pretty much the same benefits. So, I won’t talk about SolrCloud here either.

You guessed it, in this blog, I will only talk about Standalone Solr nodes and only using Shards. Alfresco supports Solr Shards only since the version 5.1. Before that, it wasn’t possible to use this feature, even if Solr4 provided it already. When using the two default cores (the famous “alfresco” & “archive” cores), with all Alfresco versions (all supporting Solr… So since Alfresco 4), it is possible to have a High Available Solr installation by setting up two Solr Standalone nodes and putting a Load Balancer in front of it but in this case, there is no communication between the Solr nodes so, it’s only a HA solution, nothing more.

 

In the architectures that I presented in the first blog of this series, if you remember the schema N°5 (you probably don’t but no worry, I didn’t either), I put a link between the two Solr nodes and I mentioned the following related to this architecture:
“N°5: […]. Between the two Solr nodes, I put a Clustering link, that’s in case you are using Solr Sharding. If you are using the default cores (alfresco and archive), then there is no communication between distinct Solr nodes. If you are using Solr Sharding and if you want a HA architecture, then you will have the same Shards on both Solr nodes and in this case, there will be communications between the Solr nodes, it’s not really a Clustering so to speak, that’s how Solr Sharding is working but I still used the same representation.”

 

II. Solr Shards creation

As mentioned earlier in this blog, there are real Cluster solutions with Solr but in the case of Alfresco, because of the features that Alfresco adds like the Shard Registration, there is no real need to set up complex things like that. Having just a simple Master-Master installation of Solr6 with Sharding is already a very good and strong solution to provide Fault Tolerance, High Availability, Automatic Failover, Performance improvements, aso… So how can that be setup?

First, you will need to install at least two Solr Standalone nodes. You can use exactly the same setup for all nodes and it’s also exactly the same setup to use the default cores or Solr Sharding so just do what you are always doing. For the Tracking, you will need to use the Load Balancer URL so it can target all Repository nodes, if there are several.

If you created the default cores, you can remove them easily:

[alfresco@solr_n1 ~]$ curl -v "http://localhost:8983/solr/admin/cores?action=removeCore&storeRef=workspace://SpacesStore&coreName=alfresco"
*   Trying 127.0.0.1...
* TCP_NODELAY set
* Connected to localhost (127.0.0.1) port 8983 (#0)
> GET /solr/admin/cores?action=removeCore&storeRef=workspace://SpacesStore&coreName=alfresco HTTP/1.1
> Host: localhost:8983
> User-Agent: curl/7.58.0
> Accept: */*
>
< HTTP/1.1 200 OK
< Content-Type: application/xml; charset=UTF-8
< Content-Length: 150
<
<?xml version="1.0" encoding="UTF-8"?>
<response>
<lst name="responseHeader"><int name="status">0</int><int name="QTime">524</int></lst>
</response>
* Connection #0 to host localhost left intact
[alfresco@solr_n1 ~]$
[alfresco@solr_n1 ~]$ curl -v "http://localhost:8983/solr/admin/cores?action=removeCore&storeRef=archive://SpacesStore&coreName=archive"
*   Trying 127.0.0.1...
* TCP_NODELAY set
* Connected to localhost (127.0.0.1) port 8983 (#0)
> GET /solr/admin/cores?action=removeCore&storeRef=archive://SpacesStore&coreName=archive HTTP/1.1
> Host: localhost:8983
> User-Agent: curl/7.58.0
> Accept: */*
>
< HTTP/1.1 200 OK
< Content-Type: application/xml; charset=UTF-8
< Content-Length: 150
<
<?xml version="1.0" encoding="UTF-8"?>
<response>
<lst name="responseHeader"><int name="status">0</int><int name="QTime">485</int></lst>
</response>
* Connection #0 to host localhost left intact
[alfresco@solr_n1 ~]$

 

A status of “0” means that it’s successful.

Once that’s done, you can then simply create the Shards. In this example, I will:

• use the DB_ID_RANGE method
• use two Solr nodes
• for workspace://SpacesStore: create 2 Shards out of a maximum of 10 with a range of 20M
• for archive://SpacesStore: create 1 Shard out of a maximum of 5 with a range of 50M

Since I will use only two Solr nodes and since I want a High Availability on each of the Shards, I will need to have them all on both nodes. With a simple loop, it’s pretty easy to create all the Shards:

[alfresco@solr_n1 ~]$ solr_host=localhost
[alfresco@solr_n1 ~]$ solr_node_id=1
[alfresco@solr_n1 ~]$ begin_range=0
[alfresco@solr_n1 ~]$ range=19999999
[alfresco@solr_n1 ~]$ total_shards=10
[alfresco@solr_n1 ~]$
[alfresco@solr_n1 ~]$ for shard_id in `seq 0 1`; do
>   end_range=$((${begin_range} + ${range}))
>   curl -v "http://${solr_host}:8983/solr/admin/cores?action=newCore&storeRef=workspace://SpacesStore&numShards=${total_shards}&numNodes=${total_shards}&nodeInstance=${solr_node_id}&template=rerank&coreName=alfresco&shardIds=${shard_id}&property.shard.method=DB_ID_RANGE&property.shard.range=${begin_range}-${end_range}&property.shard.instance=${shard_id}"
>   echo ""
>   echo "  -->  Range N°${shard_id} created with: ${begin_range}-${end_range}"
>   echo ""
>   sleep 2
>   begin_range=$((${end_range} + 1))
> done

*   Trying 127.0.0.1...
* TCP_NODELAY set
* Connected to localhost (127.0.0.1) port 8983 (#0)
> GET /solr/admin/cores?action=newCore&storeRef=workspace://SpacesStore&numShards=10&numNodes=10&nodeInstance=1&template=rerank&coreName=alfresco&shardIds=0&property.shard.method=DB_ID_RANGE&property.shard.range=0-19999999&property.shard.instance=0 HTTP/1.1
> Host: localhost:8983
> User-Agent: curl/7.58.0
> Accept: */*
>
< HTTP/1.1 200 OK
< Content-Type: application/xml; charset=UTF-8
< Content-Length: 182
<
<?xml version="1.0" encoding="UTF-8"?>
<response>
<lst name="responseHeader"><int name="status">0</int><int name="QTime">254</int></lst><str name="core">alfresco-0</str>
</response>
* Connection #0 to host localhost left intact

  -->  Range N°0 created with: 0-19999999


*   Trying 127.0.0.1...
* TCP_NODELAY set
* Connected to localhost (127.0.0.1) port 8983 (#0)
> GET /solr/admin/cores?action=newCore&storeRef=workspace://SpacesStore&numShards=10&numNodes=10&nodeInstance=1&template=rerank&coreName=alfresco&shardIds=1&property.shard.method=DB_ID_RANGE&property.shard.range=20000000-39999999&property.shard.instance=1 HTTP/1.1
> Host: localhost:8983
> User-Agent: curl/7.58.0
> Accept: */*
>
< HTTP/1.1 200 OK
< Content-Type: application/xml; charset=UTF-8
< Content-Length: 182
<
<?xml version="1.0" encoding="UTF-8"?>
<response>
<lst name="responseHeader"><int name="status">0</int><int name="QTime">228</int></lst><str name="core">alfresco-1</str>
</response>
* Connection #0 to host localhost left intact

  -->  Range N°1 created with: 20000000-39999999

[alfresco@solr_n1 ~]$
[alfresco@solr_n1 ~]$ begin_range=0
[alfresco@solr_n1 ~]$ range=49999999
[alfresco@solr_n1 ~]$ total_shards=4
[alfresco@solr_n1 ~]$ for shard_id in `seq 0 0`; do
>   end_range=$((${begin_range} + ${range}))
>   curl -v "http://${solr_host}:8983/solr/admin/cores?action=newCore&storeRef=archive://SpacesStore&numShards=${total_shards}&numNodes=${total_shards}&nodeInstance=${solr_node_id}&template=rerank&coreName=archive&shardIds=${shard_id}&property.shard.method=DB_ID_RANGE&property.shard.range=${begin_range}-${end_range}&property.shard.instance=${shard_id}"
>   echo ""
>   echo "  -->  Range N°${shard_id} created with: ${begin_range}-${end_range}"
>   echo ""
>   sleep 2
>   begin_range=$((${end_range} + 1))
> done

*   Trying 127.0.0.1...
* TCP_NODELAY set
* Connected to localhost (127.0.0.1) port 8983 (#0)
> GET /solr/admin/cores?action=newCore&storeRef=archive://SpacesStore&numShards=4&numNodes=4&nodeInstance=1&template=rerank&coreName=archive&shardIds=0&property.shard.method=DB_ID_RANGE&property.shard.range=0-49999999&property.shard.instance=0 HTTP/1.1
> Host: localhost:8983
> User-Agent: curl/7.58.0
> Accept: */*
>
< HTTP/1.1 200 OK
< Content-Type: application/xml; charset=UTF-8
< Content-Length: 181
<
<?xml version="1.0" encoding="UTF-8"?>
<response>
<lst name="responseHeader"><int name="status">0</int><int name="QTime">231</int></lst><str name="core">archive-0</str>
</response>
* Connection #0 to host localhost left intact

-->  Range N°0 created with: 0-49999999

[alfresco@solr_n1 ~]$

 

On the Solr node2, to create the same Shards (another Instance of each Shard) and therefore provide the expected setup, just re-execute the same commands but replacing solr_node_id=1 with solr_node_id=2. That’s all there is to do on Solr side, just creating the Shards is sufficient. On the Alfresco side, configure the Shards registration to use the Dynamic mode:

[alfresco@alf_n1 ~]$ cat $CATALINA_HOME/shared/classes/alfresco-global.properties
...
# Solr Sharding
solr.useDynamicShardRegistration=true
search.solrShardRegistry.purgeOnInit=true
search.solrShardRegistry.shardInstanceTimeoutInSeconds=60
search.solrShardRegistry.maxAllowedReplicaTxCountDifference=500
...
[alfresco@alf_n1 ~]$

 

After a quick restart, all the Shard’s Instances will register themselves to Alfresco and you should see that each Shard has its two Shard’s Instances. Thanks to the constant Tracking, Alfresco knows which Shard’s Instances are healthy (up-to-date) and which ones aren’t (either lagging behind or completely silent). When performing searches, Alfresco will make a request to any of the healthy Shard’s Instances. Solr will be aware of the healthy Shard’s Instances as well and it will start the distribution of the search request to all the Shards for the parallel query. This is the communication between the Solr nodes that I mentioned earlier: it’s not really Clustering but rather query distribution between all the healthy Shard’s Instances.

 

 

Other posts of this series on Alfresco HA/Clustering:

• Basis & Architectures
• ActiveMQ
• Apache HTTPD as Load Balancer
• Share
• Solr6
• Repository

Cet article Alfresco Clustering – Solr6 est apparu en premier sur Blog dbi services.