Category: Database

datapump Imports

I am trying to do an import using data pump, but I am struggling to come up with the right options. The export was taken as a schema export. I am trying to import just the tables, with no other database objects. I am currently using INCLUDE=TABLE TABLE_EXISTS_ACTION=REPLACE REMAP_SCHEMA= REMAP_TABLESPACE= This is correctly importing the […]

Tags but I am a little surprised to see that the import is also creating the constraints and triggers on the tables, but I am struggling to come up with the right options. The export was taken as a schema export. I am trying to import just the tables, I am trying to do an import using data pump, or trying to at least. The triggers are failing because they include a schema name that does not exist in the database. I thought that by us, with no other database objects. I am currently using INCLUDE=TABLE TABLE_EXISTS_ACTION=REPLACE REMAP_SCHEMA= REMAP_TABLESPACE= This is corr

Database Development

Is it safe to rely on transactions in Firebird 2.5 Embedded DB in case of power outage?

Post author By Jessica Alba
Post date October 28, 2020
No Comments on Is it safe to rely on transactions in Firebird 2.5 Embedded DB in case of power outage?

How it’s safe to say that the following list of action will never be reflected in DB in case of power outage somewhere in the middle of #2 line, before transaction is committed? #1 begin transaction #2 delete record in table A that cascade deletes another record in table B #3 update another record in […]

Tags before transaction is committed? #1 begin transaction #2 delete record in table A that cascade deletes another record in table B #3 update a, How it's safe to say that the following list of action will never be reflected in DB in case of power outage somewhere in the middle of #2 li

Database Development

High PREEMPTIVE_OS Wait Stats [duplicate]

Post author By Jessica Alba
Post date October 28, 2020
No Comments on High PREEMPTIVE_OS Wait Stats [duplicate]

How to reduce PREEMPTIVE_OS_AUTHENTICATIONOPS wait type stats in SQL Server, any advice on it would be greatly appreciated. Thanks

Tags any advice on it would be greatly appreciated. Thanks, How to reduce PREEMPTIVE_OS_AUTHENTICATIONOPS wait type stats in SQL Server

Database Development

mysql upgrade from 5.6 to 5.7, how to config mysql backup

Post author By Jessica Alba
Post date October 28, 2020
No Comments on mysql upgrade from 5.6 to 5.7, how to config mysql backup

we have a lot of mysql database running from 5.6 to 5.7 , we use netbackup to backup mysql , the local script (mysql_backup.sh) is as belows： /opt/mysql/meb-4.1.3/bin/mysqlbackup –login-path=root –backup-image=sbt:bkpsbtNB$backuptime –sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 –sbt-environment="NB_ORA_SERV=pnbumaster,NB_ORA_CLIENT=prmbsage_svc,NB_ORA_POLICY=mysql_cdeskdb,NB_ORA_SCHED=Default-Application-Backup,ORACLE_HOME=/age/mysql" –backup-dir=/usr/openv/meb_bkdir/ backup-to-image –socket=/age/mysql/data-5.7.29/mysql.sock –port=3306 >> $MYSQL_LOG_FILE every time we have upgrade database , we should re-config mysql_backup.sh is there any method to auto […]

Tags NB_ORA_CLIENT=prmbsage_svc, NB_ORA_POLICY=mysql_cdeskdb, NB_ORA_SCHED=Default-Application-Backup, ORACLE_HOME=/age/mysql" --backup-dir=/usr/openv/meb_bkdir/ backup-to-image --socket=/age/mysql/data-5.7.29/mysql.sock --port=3306 >> $MYSQL_L, the local script (mysql_backup.sh) is as belows： /opt/mysql/meb-4.1.3/bin/mysqlbackup --login-path=root --backup-image=sbt:bkpsbtNB$backupti, we have a lot of mysql database running from 5.6 to 5.7, we should re-config mysql_backup.sh is there any method to auto config mysql backup?, we use netbackup to backup mysql

Database Development

Intermittent SQL Delegated Authentication Errors

Post author By Jessica Alba
Post date October 28, 2020
No Comments on Intermittent SQL Delegated Authentication Errors

Here is an odd issue that is causing me to lose all my hair. Every few days or so our delegated authentication from one SQL server to another fails with the error: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. The SQL server logs show: "Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find […]

Tags and multiple Microsoft tickets. We have a suspicion that leaving SSMS sessions to the SQL servers overnight triggers the Kerberos delegated c, but I'm not sure what to look for in the case that the DCs are the problem. Oddly, deleting and recreating SPNs, Here is an odd issue that is causing me to lose all my hair. Every few days or so our delegated authentication from one SQL server to anothe, however over the last year it has affected several servers with several different delegated connections. To date we have tried updating SQL, it's just the AD delegated permissions that stop working., Severity: 14, State: 5." Our environment consists of 6 Domain controllers ranging from Windows 2012 to Windows 2019, The SQL servers are Windows Server 2016 running SQL Server 2017. After a few hours the issue goes away by itself, unless the Kerberos tickets are purged on either one of the affected SQL servers which resolves the issue in about 15 minutes. At first we t, updating the domain functionality level, upgrading the bulk of our DCs to Windows 2019, we are able to connect to the individual servers via protected AD accounts during the outage and the log ins are listed as Kerberos

Database Development

What does MariaDB use for an IV?

Post author By Jessica Alba
Post date October 27, 2020
No Comments on What does MariaDB use for an IV?

I have been using MySQL 5.6 in development environment and MariaDB 5.6 in production until I needed to use encryption. MySQL 5.6 has multiple encryption modes and allows AES-256-CBC. MariaDB 5.6 only uses ECB. So, I was working towards and upgrade to MariaDB 10, and noticed that the AES_ENCRYPT() and AES_DECRYPT() functions don’t use an […]

Tags and noticed that the AES_ENCRYPT() and AES_DECRYPT() functions don't use an IV. This is strange since CBC mode requires an IV. MySQL 5.6 and, I have been using MySQL 5.6 in development environment and MariaDB 5.6 in production until I needed to use encryption. MySQL 5.6 has multiple, I was working towards and upgrade to MariaDB 10

Database Development

Oracle 12cR2 RMAN – ORA-01547, ORA-01194, RMAN-06054

Post author By Jessica Alba
Post date October 27, 2020
No Comments on Oracle 12cR2 RMAN – ORA-01547, ORA-01194, RMAN-06054

Oracle 12cR2. Error: channel default: deleting archived log(s) archived log file name=+RECO/…/thread_1_seq_166200.26582.1054942673 RECID=62 STAMP=1054943183 archived log file name=+RECO/…/thread_1_seq_166201.11529.1054941077 thread=1 sequence=166201 channel default: deleting archived log(s) archived log file name=+RECO/…/thread_2_seq_223357.10403.1054942671 RECID=58 STAMP=1054943182 unable to find archived log archived log thread=2 sequence=223358 Oracle Error: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file […]

Tags archivelog backups, daily spfile backup and daily controlfile backup are under this path '/.../RESTORE/'. I setted my limit to scn 1871183871663 but it says "me, Oracle 12cR2. Error: channel default: deleting archived log(s) archived log file name=+RECO/.../thread_1_seq_166200.26582.1054942673 RECID

Database Development

Debug why Postgres wants to use an uninstalled extension for a function

Post author By Jessica Alba
Post date October 27, 2020
No Comments on Debug why Postgres wants to use an uninstalled extension for a function

I recently moved a Postgres function app_user_id from an old, custom extension called intpair into a new, custom extension called heap_exts. This worked well except for machines I restored from a Postgres backup. Here’s the error: db=> select app_user_id(1, 2); ERROR: could not access file "$libdir/intpair": No such file or directory intpair is not installed […]

Tags 'app_user_id_make' LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; Why is Postgres trying to look in the intpair extension for the app_user_id fu, 2); ERROR: could not access file "$libdir/intpair": No such file or directory intpair is not installed and heap_exts is installed as shown, 3; -[ RECORD 1 ]---------- extname | heap_exts extschema | public proname | app_user_id proschema | public I initially migrated the fun, BIGINT) RETURNS app_user_id AS 'MODULE_PATHNAME', bigint); ALTER EXTENSION heap_exts ADD FUNCTION app_user_id(bigint, bigint); However, custom extension called heap_exts. This worked well except for machines I restored from a Postgres backup. Here's the error: db=> select ap, custom extension called intpair into a new, for the new machine restored via backup from the old machine, I created the app_user_id function directly because the old extension intpair no longer exists: CREATE FUNCTION app_user_id(BIGINT, I recently moved a Postgres function app_user_id from an old, ne.nspname AS extschema, np.nspname AS proschema FROM pg_catalog.pg_extension AS e INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid) INNER JOIN pg_catalog., p.proname

Database Development

SQL server memory pressure

Post author By Jessica Alba
Post date October 27, 2020
No Comments on SQL server memory pressure

First observed few months ago at end-user’s laptop (rather slow i3 CPU, 4GB RAM, SSD) with SQL 2014 Express. The problem persists with SQL 2019, even with clean Windows 10 1903 installation. The database has 250MB so pretty small even for Express Edition. Application mostly works smooth, but there are time to time very looong […]

Tags 4GB RAM, but there are time to time very looong pauses, committed (KB): 577992, even with clean Windows 10 1903 installation. The database has 250MB so pretty small even for Express Edition. Application mostly works smoo, First observed few months ago at end-user's laptop (rather slow i3 CPU, memory utilization: 35%%. Based on this MSDN article about paging I should turn on Locked pages. There is a caveat that the hungry SQL Serve, so user can't work at all. Are my worries relevant? What struggles me most is that it start happening quite recently (approx. 2 months ago), SSD) with SQL 2014 Express. The problem persists with SQL 2019, there are no other queries nor databases. Recently the same problem start occurring on my dev machine too. I can confirm the memory pressure, while simple query which is usually finished within 2 secs timeouts after 5 mins! It's client machine, why freeing few hundreds MBs is taking several minutes with NVMe drive (and in my case also quite performant CPU). Are there some recent cha

Database Development

SQL Log Shipping – What is the date time format used by transaction log file name?

Post author By Jessica Alba
Post date October 27, 2020
No Comments on SQL Log Shipping – What is the date time format used by transaction log file name?

Looking at my SQL log shipping file name of Q:\Backups\LogShipping\MyAudit\MyAudit_20201027165400.trn What date time format is the transaction log expressed in (local military time, UTC time)?

Tags Looking at my SQL log shipping file name of Q:\Backups\LogShipping\MyAudit\MyAudit_20201027165400.trn What date time format is the transacti, UTC time)?