Friday 26 April 2019

Oracle - Copy data from A to B while rejecting duplicates

Copying data from table A to B while rejecting duplicates

 

An easy way to copy data from one table to another while ignoring duplicate records is by using the DBMS_ERRLOG package.

The DBMS_ERRLOG package provides a procedure that enables you to create 
an error logging table so that DML operations can continue after encountering 
errors rather than abort and roll back.

To start off you need to create a logging table.

The syntax for creating the table is as follows:

DBMS_ERRLOG.CREATE_ERROR_LOG (
   dml_table_name            IN VARCHAR2,
   err_log_table_name        IN VARCHAR2 := NULL,
   err_log_table_owner       IN VARCHAR2 := NULL,
   err_log_table_space       IN VARCHAR2 := NULL,
   skip_unsupported          IN BOOLEAN := FALSE);

To creates a logging table, only the table name is required.

 exec dbms_errlog.create_error_log('TableA');

This create a table called ERR$_TableA in the current schema based on the table TableA.

You can then copy data from the source table to the target. Duplicate records are rejected and logged but the statement will not fail.

INSERT INTO my_schema.TableB
SELECT *
FROM my_schema.TableA
LOG ERRORS INTO my_schema.ERR$_TableA ('already exists') REJECT LIMIT UNLIMITED;

 

Monday 10 July 2017

rsInvalidReportServerDatabase error after upgrade


rsInvalidReportServerDatabase error after upgrade

We recently upgraded a SQL Server 2014 database from Service Pack 1 to SP2.
The upgrade seemed to be successful. The status of all the features set-up were flagged accordingly.


I checked the error logs and everything seemed fine.
However in the Windows NT log there was an error which stated “The report server database is an invalid version”.
 
When I then tried to view the reports the following message was displayed:
The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is '164'. The expected version is '163'. (rsInvalidReportServerDatabase).
This is due to a bug which was fixed in ‘Cumulative update 1 for SQL Server 2014 SP2’ (KB article number – 3212393 - FIX: rsInvalidReportServerDatabase error after you upgrade).
To fix we applied the latest Cumulative Update Package (which in this instance was CU5).
This resolved the issue and reporting services now appear to be running normally again.

Links:

 







Sunday 20 November 2016

ORA-32028: Syslog facility or level not recognized

Recently I had the opportunity to update the audit settings on an Oracle database. It seemed fairly straight forward: alter the system parameters then recycle the database. Easy!!

Upon issuing the startup command the below error was displayed. 

 ORA-32028: Syslog facility or level not recognized

The issue was because I didn't use single quotes in the alter system command

alter system set audit_trail=NONE

Instead of:

alter system set audit_trail=’NONE’

To fix this issue the value in the database parameter file needed to be amended.

With the database offline, create a copy of the pfile.

create pfile='/tmp/initDB1.ora' from spfile;

Then edit the value.

Edit the pfile

vi /tmp/initDB1.ora

......

*.audit_sys_operations=FALSE

*.audit_syslog_level=' '

*.audit_trail='NONE'

.....

After changing the parameter, start the database using the amended pfile.

sqlplus / as sysdba

startup pfile = /tmp/initDB1.ora

Then create an spfile from the good pfile.

create spfile from pfile='/tmp/initDB1.ora';

This saves an spfile to $ORACLE_HOME/dbs which has the parameters that are in the pfile.

ls -lrt $ORACLE_HOME/DBS

rw-r-----    1 oracle   dba            3584 11 Nov 12:33 spfileDB1.ora


Finally restart the database to check that it now starts successfully when it uses the spfile.

shutdown immediate
startup

Automate a restore of a production database to a development environment

In this example the production database is backed up once a day to a shared file system. So to automate the process I have created a SSIS package which queries the production database and finds out the name and file-path of the last backup. It then restores the last backup to the development instance. This way we do not impact the production environment.



The SSIS Package  has two SQL tasks. The first will query the production backup and find out the name and path of the last backup to a shared file system. The 2nd task will then take the backup and restore it to the development instance.



Create connection managers

Before you start, create two new connection managers: one for the source production database, and the other for the destination development instance.



Create global variable

Create a variable which will be used to store the output of the above query (the location and filename of the last backup). The variable needs to have a scope of the whole package and a String data type.



Task 1 – Get last backup



The below statement queries the backupmediafamily and backupset tables. It extracts the filename and path of the last full backup.

SQL Statement

SELECT

physical_device_name

FROM msdb.dbo.backupmediafamily

WHERE media_set_id =(

SELECT TOP 1 media_set_id

FROM msdb.dbo.backupset

WHERE database_name='DATABASE_NAME'

AND type='D'

ORDER BY backup_start_date DESC)




In the result set tab, change the Result Name to 0 (zero, i.e. the first returned value) and the Variable Name should be your new variable name prefixed with User:: (I.e. User::FILEPATH).

 


Task 2 – Restore backup to Development

 
 
SQL Statement

restore database DATABASE_NAME from disk = ?
with move 'logical_file_name_in_backup' to 'operating_system_file_path.mdf',
move 'logical_log_name_in_backup' to 'operating_system_file_path.ldf'
, replace


In the parameter mapping tab the variable name is again the FILEPATH variable we have used in task 1. The direction is “Input” and the data type NVARCHAR.
Please note that the names that you can use as parameter names in the mappings between variables and parameters also vary by connection manager type.
As I have used the OLE DB connection manager type, this requires that we use the numeric value of a 0-based ordinal as the parameter name.




Once you have tested the package you can upload it to the server and run it using a SQL Agent job.
After you restore the database you may need to fix the login, users and permissions. I chose to just add read / write privileges to a number of specific users as there is a significant difference between the users in production and this development environment. You may choose to script out the accounts using sp_help_revlogin or just use the sp_change_users_login procedure to fix the orphaned accounts.





The package is very basic and could be extended to include some basic error handling, etc.


Links: