Oracle - SQL - MySQL DBA - Life Long Learning Experience

Service Tier Database Server Location(s) Database Server Edition   (and why - Enterprise Edition needs to be justified) Database Server version (including SP and Operating system) and compatibility modes Designated Infrastructure platform Local database High Availability Requirements (not catered for by underlying resilience of infrastructure platform) Database DR Requirements - including technology stack and explanation Breakdown of servers , instances and databases Support for named Instances SQL Server Collation Data Management Tools- GDPR , weeding, retention and tool Database Management Tools - e.g. application level data replication tools Additional SQL Server Features over and above Engine and Management Tools e.g. SSRS, SSIS, SSAS   Demand management Estimated transactions per day Estimated concurrent connections (users) Estimated I/O requirements (Mb/hour) Maximum Database Size / Growth rates per year Support

How To Restore A MySQL Database from a Zmanda Backup Backups are written round robin daily by each of the xxxxctrl01-04mqp backup servers to autofs mapped volumes u05md1,u05md2,u05fc1,u05fd1.  Using the mysql backup schedule determine which mapped volume the latest backup will reside on, or do a search through the successful zmanda backups emails to get the latest. SSH onto one of the 4 mysql backup control servers as root user. cd into the backup location to verify the backup file is there.  As an example "cd /u05md1/mysql/mdcmyq7r_3317_B1/20171015060001/" "ls" then verify that there is a "backup-data" and "index" file within the folder.  In this example the "B1" means schedule B and backup server 1.  The "20171015060001" refers to the date/time the backup was taken,  "backup-data" is the actual backup file zipped up.  "index" contains information about the backup. Perform the rest

SQL Server 2008 R2 Installation Procedures Initial Server Specification SQL Server 2008 R2 is compatible with Windows Server 2012 so should use this configuration where possible unless a specific reason not to.  It will always be the 64 bit configuration using Server 2012. RAM to allocate will depend on usage of the database, minimum should be 2Gb but would recommend more for most production databases. Consider requesting server to be added to the domain. Disk Configuration In a physical environment we should be aiming to separate the OS, Data, Logs, TempDb and Backup areas by spindles. Our fastest disk areas should be Data and Logs and these should be set up as RAID 10 where possible if performance is key. The more disks allocated the more IO we can achieve. Logs should be kept on their own set of spindles as they write sequentially.  Therefore the heads do not have to travel as far. If using a VM environment we will not usually reap the same performance benefits by s

How you import data into an Amazon RDS DB instance depends on the amount of data you have and the number and variety of database objects in your database. For example, you can use Oracle SQL Developer to import a simple, 20 MB database. You can use Oracle Data Pump to import complex databases, or databases that are several hundred megabytes or several terabytes in size. You can also use AWS Database Migration Service (AWS DMS) to import data into an Amazon RDS DB instance. AWS DMS can migrate databases without downtime and, for many database engines, continue ongoing replication until you are ready to switch over to the target database. You can migrate to Oracle from either the same database engine or a different database engine using AWS DMS. If you are migrating from a different database engine, you can use the AWS Schema Conversion Tool to migrate schema objects that are not migrated by AWS DMS. For more information about AWS DMS, see  What is AWS Database Migration Service

MySQL standards In order to make a Db ACID complaint , innodb must be setup as the default , with the correct configuration General settings Directories /mysqltemp mysqld_multi: log = /var/log/mysqld_multi.log Per  Instance settings server-id = n This is required for a multi instance Db port = 33nn This defines the port to connect to an instance on (server_id=1, port=3306: server_id=2, port=3307 and so on) log-error = /mysqllogs1/<instance>/<instance>_err.log The error log file and folder log-bin = /mysqllogs1/<instance>/ defines the binary log location expire-logs_days = n defines the amount of binary logs to keep (this is used by mysql after a log flush) binlog_format = mixed defines the format of the binlogs (the other options are statement and row based, or combined to mixed format) default-storage-engine = innodb defines the default engine (post 5.5.5 it is innodb, but this forces the issue) datadir = /mysqldata1/<instance>/ defi

Guide for Setting Up Log Shipping on SQL Server 2008 R2/2012 Overview: This process will involve backing up the logs to an area and copying them over from the primary to the secondary and applying them via a restore job. This will create LSBackup job on the primary and LSCopy / LSRestore jobs on the secondary. LSBackup will back up the log files to a location on the primary server (job runs on the primary server). LSCopy will copy the log files from the primary to the secondary (job runs on secondary server). LSRestore will restore the log files from the secondary to the database (job runs on the secondary server). Note when using log shipping ensure no log backups are made outside of the log shipping process or you will break the log shipping replication. 1. Ensure both servers are set up to be part of the domain. (control panel - system - Computer name, domain, and workgroup settings - change settings. Set domain to <domain name> ) 2. Create folders used fo

Purging LOBs : If we purge a table which contains LOBs , we may not reclaim space.  BLOBs are stored out of line once they exceed 4,000 bytes (you can also specify that they are always stored out of line as well). If the BLOBs were all inline, each taking 4000 or less bytes, then you did free space in your table.  The table will not shrink in size (tables NEVER shrink) but it will have more blocks on its free list for subsequent inserts. SQL> desc applsys.fnd_lobs Name                                      Null?    Type —————————————– ——– —————————- FILE_ID                                   NOT NULL NUMBER FILE_NAME                                          VARCHAR2(256) FILE_CONTENT_TYPE                         NOT NULL VARCHAR2(256) FILE_DATA                                          BLOB UPLOAD_DATE                                        DATE EXPIRATION_DATE                                    DATE PROGRAM_NAME                                       VARCHAR2(32) PRO

Before drop we should make sure atleast 3 redo group are there and dropping group should be in INACTIVE state. SQL> alter database drop logfile group 3; alter database drop logfile group 3 * ERROR at line 1: ORA-01624: log 3 needed for crash recovery of instance scvdev (thread 1) ORA-00312: online log 3 thread 1: '/oradata/scvdev/datafile/scvdev/redo03.log' So to make ACTIVE to INACTIVE execute system level checkpoint. SQL> ALTER SYSTEM CHECKPOINT GLOBAL; System altered. SQL> alter database drop logfile group 3; Database altered. SQL> alter database add logfile group 3 ('/redolog/scvdev/onlinelog1/redo03a.log','/redolog/scvdev/onlinelog2/redo03b.log') size 512m; Database altered. SQL> select * from v$log;     GROUP#     THREAD#   SEQUENCE#       BYTES   BLOCKSIZE   MEMBERS ARC ---------- ---------- ---------- ---------- ---------- ---------- --- STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHA

Data Sheets and White Papers Managing Oracle Database With a Purpose Built System - ORACLE WHITE PAPER | October 2017 Oracle Database Appliance X7-2 Model Family   (PDF)      Oracle Database Appliance X7-2-HA - ORACLE Data Sheet (PDF) Deploying Oracle Data Guard with Oracle Database Appliance (PDF) Oracle Database Appliance: Implementing Disaster Recovery Solutions Using Oracle Data Guard (PDF) Oracle Documentation Oracle Database Appliance X7-2 Documentation Library https://docs.oracle.com/cd/E72435_01/ HTML Master Site for all Documentation https://docs.oracle.com/cd/E72435_01/html/E72440/index.html HTML Installation Guide (Hardware) https://docs.oracle.com/cd/E72435_01/html/E72441/index.html HTML Operating System Install Guide https://docs.oracle.com/cd/E23161_01/html/E75413/index.html HTML Administration Guide https://docs.oracle.com/cd/E72435_01/html/E72445/ind

Purging LOBs : If we purge a table which contains LOBs , we may not reclaim space.  BLOBs are stored out of line once they exceed 4,000 bytes (you can also specify that they are always stored out of line as well). If the BLOBs were all inline, each taking 4000 or less bytes, then you did free space in your table.  The table will not shrink in size (tables NEVER shrink) but it will have more blocks on its free list for subsequent inserts. SQL> desc applsys.fnd_lobs Name                                      Null?    Type —————————————– ——– —————————- FILE_ID                                   NOT NULL NUMBER FILE_NAME                                          VARCHAR2(256) FILE_CONTENT_TYPE                         NOT NULL VARCHAR2(256) FILE_DATA                                          BLOB UPLOAD_DATE                                        DATE EXPIRATION_DATE                                    DATE PROGRAM_NAME                                       VARCHAR2(32) PROGRAM_TAG