Backup Oracle Database from MS Windows using UNC to Oracle Database Appliance (Samba / CIFS) and Oracle CloudFS

Every customer will help us to build up additional knowledge (in other words, they request something you haven't done or needed somewhere else before).

My actual customer has bought two Oracle Database Appliances, ODA X7-2 HA, and they are migrating all Oracle databases with different versions from the old, VMWare based, Windows environment to the ODA (Virtualized Platform).

After the successful deployment of the ODAs in the customers vlan (maybe worth another post) the question was, how to put the things from the old environments to the ODAs.

Secure copy (scp/Puttys pscp) is ok, but either you need to install putty everywhere or you need to map all server drives at your client and (p)scp all to the ODAs,... not really a fun task.
Also you have an additional step if you create e.g. the RMAN backup locally at the Windows server and then (p)scp it to the ODA. You know it's an additional step which means (down) time and money, so we wanted to avoid this for the time of the migration.
As nfs was not a solution (due to some internal configuration issues) we thought about creating a direct connection between the ODAs and the Windows servers.

CloudFS is a nice feature of ACFS (not only) on the ODAs, but it can't be used without some additional configuration to map a drive at the Windows Server or to use UNC from  RMAN or as directory for Datapump Exports to the ODA.

The customer wants to allow the Windows Server for the time of the migration of all databases to write from any machine directly (without to set any user/password) to the CloudFS on the ODA X7-2 HA, so we had to configure Samba (smb) at the ODA.

If you may need it, here are the steps you can walk through if you want to use CloudFS within UNC paths or if you need to map the CloudFS directory from the ODA to your Windows Server without user/password.

1. Ensure, that CloudFS is configured and sized right to have enough space for your files. Typically, CloudFS is created when the ODA_BASE is deployed, but you can skip this step. If you have skipped it, you can create CloudFS using the asmca (ASM Configuration Assistent) or you can do it manually. I didn't need it, but I think Matthew described in his cloudfs post very well, how to do it if you don't want to use asmca. Even resize can be done very easy by following this oracle support note.
   
   To check the existence and the size of your CloudFs, run
   df -h /cloudfs
   
   CloudFS is mapped on both ODA nodes to /cloudfs. Owner:group should be oracle:oinstall.
   

2. Configure Samba at the ODA_BASE (as root), part GLOBAL
   You need to edit the smb.conf file which is located at /etc/samba:
   vi /etc/samba/smb.conf
   
   Skip to - roundabout - line 100 (Standalone Server Options). There you can see this configuration:
       security = user
       passdb backend = tdbsam
   
   Change this (even if it's marked deprecated to use security = share) to:
      security = share
      passdb backend = tdbsam
      guest account = oracle
   
   
3. Configure Samba on both nodes with the name and the rights of your share.
   Now it's time to bring your share configuration into the smb.conf file. At approx. line 255 the "Share Defnitions" part starts. There you do need to put in the configuration for your share. The part in [ ] is the name you later see if you use your Windows explorer - so it's the public name of the share: We named it ODAPROD_CLOUDFS, but typically only CLOUDFS is enough.
   
   [ODAPROD_CLOUDFS]
   comment=cloudfs files
   path=/cloudfs
   browsable=yes
   public = yes
   writable = yes
   printable = no
   
   After you have inserted this write the smb.conf and quit from vi.
   
   
4. Copy the smb.conf to second node
   To be able to use the share "high available" later on, copy the changed smb.conf from your local nodes /etc/samba/smb.conf to the second node using scp.
   
   
5. Start / Stop the Samba Service on both nodes.
   You need to start the service at both nodes manually.
   To do this, connect as root to ODA_BASE on both nodes and run
   service smb start
   
   If you want to stop the Samba share, you can run
   service smb stop
   
   
6. Test UNC path from Windows
   You can now test the connection. Open a File Explorer Window at MS Windows. Then you should be able to use the share. Don't use the node names to connect to the share, use the SCAN name of the system, so you are able to use the share even if one of the nodes is not available, e.g. while reboot.
   
   In my case, we can now use the UNC path from Windows without to specify a user/password to write into /cloudfs at the ODA using in Windows explorer:
   \\ODA-SCAN\ODAPROD_CLOUDFS
   
   You could also map the share as drive in Windows using the net use command.
   
   Now we can make backups from the Windows server direct to the ODA, using e.g.
   backup database format '\\ODA-SCAN\ODAPROD_CLOUDFS\%U';
   
   In addition, we don't need to install putty on all windows servers to pscp e.g. the VM templates for the application VMs we want to use at the virtualized platform.
   
   

Some remarks:

• As this configuration don't need a username and password, you should start and stop the smb service explicitly - don't let it run all the time, start it when needed and stop it if you have put everything onto the ODA.
  It's a question of security and you sure don't want that someone uses the storage of the ODA to put some photos or videos on and share it. 
• If you need the cloudfs share as a long term solution, use the standard configuration with security, add your share to the smb.conf and map the drive on the MS Windows systems you need it.
• I don't have registered the smb service to the cluster infrastructure (as it is - in our case - only started when needed). If you want to keep the smb share(s) (so SMB is automatically started after ACFS/databases/...) you can configure that - the solution would be: crsctl add resource. 
  An example can be found (for Samba) at the documentation of Oracle (this link is for version 12.1)

New Achievement :-)

Nice Certicificate for reaching "Expert" Level at My Oracle Support Community.

Dynamic datapump export without expdp client using DBMS_DATAPUMP API and SQL*Plus (also for PDBs)

Long time, since I have written my last blog entry, but I was busy with a bigger customer project, where we needed to migrate roundabout 30 databases to a new environment.

Nearly finished with the project, I got a script from our customer to schedule it with cronjob.
The script should do a full export every Sunday and a schema export of all schemas that don't use the SYSTEM or SYSAUX tablespace as default tablespace all other days.

What I have seen then was unbelievable, I nearly couldn't stop head-shaking.
The former consulting company created a procedure that was stored in the SYS user. Not worse enough, this procedure was scheduled from inside the database (using dbms_jobs) and the only job was - do you sit? - creating a linux shell script dynamically, with a full export on Sunday and the schema exports on all other days.
The last step and the next head-shaking was, that this dynamically created script afterwards was scheduled in cronjob. I don't need to mention that the export wasn't made consistent using any FLASHBACK_ parameter.

What the hell...

First rule: Never put anything in the SYS schema of the database, especially not when it is scheduled inside the database.
Second rule: Don't do things more complicated, as they are, so use your brain but let the things to run to be simple and understandable (I extra don't say stupid).
Third rule: Don't rely on a job cascade combining inside and outside database schedules. Worst thing here is that the dynamic shell script could not be written by the database, so if you add e.g. a schema, this will never be exported - and you may also never get a full export (or you get a full export all the time).

Well, these three and a lot more topics convinced me to streamline the export a little bit:

1. Keep it simple and easy
2. Make only one job
3. Don't put any own code into the SYS schema
4. Make the script dynamic
5. Make the export consistent
6. Add own logfile entries

So I created a new script, you can copy and paste it into your notepad/vi and save it (maybe you need to deal with wrong quotation marks after copy/paste). The script exports to the temp directory with the DB_UNIQUE_NAME as subdirectory. Please create this directory before you start the export or add "host 'mkdir -p /tmp/&lv_db'" after the create or replace directory statement to create it also dynamically.

Afterwards, you can run the script by just start sqlplus as a SYS/SYSTEM user and provide the script name, e.g. if you save the script as JoergsDatapumpExport.sql you can start the script after setting oraenv by calling $ sqlplus / as sysdba @JoergsDatapumpExport.sql .

Hope, this script is helpful for someone - if you connect e.g. to a pdb using sqlplus and if you have added the mkdir part, you can dynamically export from any pdb. There are also some enhancements I have done for my test environment you could do: Adding parallel export support depending on the numbers of cores you can use or you can stop the datapump export if there is no schema to export - before ugly stuff is written into the logfile.

Comments are welcome.

WHENEVER SQLERROR EXIT FAILURE;
set serveroutput on
set feedback off
column db_unique_name new_value lv_db
select db_unique_name from v$database;
create or replace directory "DATA_PUMP_DIR" as '/tmp/&lv_db';

declare
l_dp_handle NUMBER;
v_job_state varchar2(4000);
v_day varchar2(100);
v_host varchar2(50);
v_db varchar2(20);
v_schemaexport varchar2(1000);
l_current_scn number;
cursor c1 is
select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX')
and username not in ('PERFSTAT');

BEGIN
select rtrim(to_char(sysdate,'DAY')) into v_day from dual;
select host_name into v_host from v$instance;
select db_unique_name, current_scn into v_db, l_current_scn from v$database;

if v_day in ('SUNDAY','SONNTAG') then
l_dp_handle := DBMS_DATAPUMP.open(operation => 'EXPORT',
job_mode => 'FULL',
remote_link => NULL,
version => 'LATEST');
else
l_dp_handle := DBMS_DATAPUMP.open(operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => NULL,
version => 'LATEST');
end if;
DBMS_DATAPUMP.add_file(handle => l_dp_handle,
filename => v_host||'_'||v_db||'_'||v_day||'.dmp',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE,
reusefile => 1);
DBMS_DATAPUMP.add_file(handle => l_dp_handle,
filename => v_host||'_'||v_db||'_'||v_day||'.log',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,
reusefile => 1);

DBMS_DATAPUMP.SET_PARAMETER(handle => l_dp_handle, name => 'FLASHBACK_SCN', value => l_current_scn);

if v_day in ('SUNDAY','SONNTAG') then
DBMS_DATAPUMP.log_entry(handle => l_dp_handle,
message => 'FULL Export.');
else
begin
DBMS_DATAPUMP.log_entry(handle => l_dp_handle,
message => 'SCHEMA Export (without schemas with SYSTEM and SYSAUX default Tablespace).');
v_schemaexport := 'IN (' ;
for c1_rec in c1 loop
begin
if c1%rowcount=1 then
v_schemaexport := v_schemaexport||''''||c1_rec.username||'''';
else
v_schemaexport := v_schemaexport||','||''''||c1_rec.username||'''';
end if;
end;
end loop;
v_schemaexport:= v_schemaexport||')';
DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_EXPR', v_schemaexport);
end;
end if;

DBMS_DATAPUMP.log_entry(handle => l_dp_handle,
message => 'Please wait until the export is completed.');

DBMS_DATAPUMP.log_entry(handle => l_dp_handle,
message => 'Export Parameters: Flashback_SCN='||to_char(l_current_scn),
log_file_only => 0);

DBMS_DATAPUMP.log_entry(handle => l_dp_handle,
message => 'Start time: '||to_char(sysdate,'DD.MM.YYYY HH24:MI:SS'),
log_file_only => 0);

DBMS_DATAPUMP.START_JOB (l_dp_handle);
END;
/
exit;