New answers tagged

0

Log shipping, back up and restore have been tossed on the table Thats your best bet - its low impact and works just fine. You have to perform a full backup, ship it to secondary server and restore it with no recovery on the secondary server. Then it will automatically ship transaction logs depending on the frequency that you have set (minimum is 1 ...


1

When it comes to Windows, something is always different. TWO_TASK works on Linux/UNIX, but not on Windows. The Windows version of TWO_TASK is LOCAL. SQL*Plus Environment Variables LOCAL Windows environment variable to specify a connection string. Performs the same function as TWO_TASK on UNIX. ... TWO_TASK UNIX environment variable ...


1

Is there any other way to associate a metadata with database snapshot when it is created? No. Since a database snapshot is read-only, you cannot modify or add any settings to it. In-fact, extended properties cannot be applied to it. So your best option is to have a table in the database (or a utility database) called e.g. dbo.database_snapshot_history ...


0

Unless your development or testing team already has a set of regression test cases, you are out of luck. The possible things to test are astronomical. Now, as others have pointed out, there are some things to look out for, like more restrictive privileges, and reviewing the "What's New" for both 11g and 12c. One big example from the past that burnt a lot ...


0

Run the following command to track the corrupt pages: SELECT * FROM msdb.dbo.suspect_pages Review the suspect pages. Run DBCC CHECKDB command to check the inconsistencies of the database. Please check the PAGE_VERIFY CHECKSUM option and turn it on. Note: Also check the hardware of your machine.


0

This was tricky but fun. Using a recursive query: with recursive v(l, f) as ( -- select Letter, Figure from table values ('A', 1), ('B', 1), ('B', 2), ('C', 2), ('C', 3), ('D', 3), ('E', 4) ), r as ( select * from ( select array[l] as l, array[l] as al, array[f] as f, array[f] as af, 1 as g, 1 as n from v order by l, f ...


3

Ensure you have a valid backup; hopefully it will be from prior to the corruption, but not so long ago that the data isn't useful. You should set this aside in case direct repair isn't possible and you need to recover data from the backup. The documentation explains what to do to correct the problem - you can try the REPAIR_REBUILD option, and if that ...


0

I've never had a corrupt page before. Can you provide any information from the dbo.suspect_pages table? Please see the below resources: Brent Ozar on Corruption SQL Server Central Corruption


1

Redo logs can be mirrored, your query does not take into account that. Controlfiles are also part of the database. select 'Datafiles', sum(bytes)/1024/1024 from dba_data_files union all select 'Tempfiles', sum(bytes)/1024/1024 from dba_temp_files union all select 'Redo logs', sum(bytes)/1024/1024 from v$log l join v$logfile lf on (l.group# = lf.group#) ...


0

The very crude method is to pair up largest database with smallest, then 2nd largest with 2nd smallest and so on. This should work assuming that your database sizes are evenly distributed. Implementation has been hacked toghether in 5 min, so can probably be improved a lot: ;WITH DBSizes AS ( SELECT db.name, DbSize.TotalDataSize * 8 / 1024 as ...


8

You're using a local variable, not a parameter. So because this is part of your query text: DECLARE @id INT=43865 It gets included as part of the query hash (which converts the query text, including 43865, to binary), and thus produces a different hash every time you change the text. This is how it will work in a Management Studio query window, but it ...


1

Do I have to write SQL for that manually? Yes, but it's not that hard: create table original (id integer, url text); insert into original values (1,'VeryLongURLText'), (2,'VeryLongURLText'), (3,'LoooongURLText'), (4,'LoooongURLText'), (5,'LoooongURLText'); create the dictionary create table dictionary (id serial, url text); insert into dictionary ...


0

MySQL DBA don't need root access. It's very important security principle. The sys admin should create a sudo role with rights to access /var/log (read only), journalctl and systemctl or service stop/start/restart


0

Okay ... Upon installation of postgres another service was using the port 5432 so postgres took 5434. I didn't notice. Works now


0

The SQL Server DB Storage Engine access the data on the hard drive the same way. It can be the exact same physical file. This is why although it's bad practice, you can have just 1 physical .mdf file and have all of your indexed, non indexed data on a clustered index, and heap in the same physical file. Of course performance and disk space usage will be ...


0

I have 2 SQLServers at production site Windows Server Failover Cluster and 1 SqlServer at DR site. I will add the DR node to existing windows cluster and configure AOAG.So for this should I go for File share witness or Disk Witness for Quorum Configuration for this AOAG set up . Generally when there are geographically distributed clusters, having a disk ...


3

You can't add a database that way, you will need to attach the database using the files you have as follows: Copy the files to the correct location. Attached the database with EXEC sp_attach_db '<name>', '<name.mdf>', '<name.ldf>' or using the relevant GUI command in SSMS (if the files are not in the SQL instance's default directories you ...


1

ApexSQL Restore will mount the backup as virtual Database, and you can query it using SSMS. I also published a PowerShell script that will let you analyze your backups in a given folder. I wrote it for analyzing size but you could easily adjust this to include properties like the date taken and such to find which backup you need to mount. You can find the ...


5

I have been in the noSQL field since last 3 years. Being a MongoDB DBA you need to work closely with Development and Ops team. Following are the things you need to do as day to day task as MongoDB DBA: The role can be broadly classified into three parts: Administration: New deployments (manual/automated) Deployment design of you database systems ...


5

DBA is a small acronym but a large role. At various times I have seen a DBA look after storage network VMs compute nodes installation and configuration of the above backups, and testing restores DR strategy enterprise data integrity ETL data security data modelling, normalisation and database design data tier programing performance tuning operational ...


1

If you use Availability Groups you must do log backups. If you use Hallengren's backup solution then you'll need to set it up on both the primary and secondary node(s). Then direct the jobs it creates to point to the same network share. What will happen next is the script will create one directory for each server to hold system databases and any databases ...


3

I think you will be much better off testing your actual workload under sync and async, and see if you observe any differences. Coming up with a sweet spot to suit a particular feature is kind of backwards. Generally, async is primarily beneficial when you don't care if the query that triggered the stats update still runs that last time with the old stats ...


3

In Oracle 12c there are 4 key name-related concepts you need to know: SID, or instance ID. Database name / database unique name Container / Pluggable database names Service names The instance ID is an ID that uniquely identifies one database instance running on one server. On a Linux server, this is normally specified in the $ORACLE_SID variable, but ...


1

When you delete from links_publicreply, it cascades that to delete child rows from links_link which would otherwise become orphaned (because the constraint is defined with ON DELETE CASCADE). But some of those rows being deleted in links_link are still referenced by rows in links_vote. The constraint between links_link and links_vote is not defined with ON ...


0

Solved by changing the value of "kernel.shmall" in the file /etc/sysctl.conf


0

Make sure the database is actually running, and if not, start it. If you have an init script, then it's typically something like: sudo service oracle start This has been asked before for Oracle 11g - the answers and comments to that question may be helpful here: ORA-01034: ORACLE not available in oracle 11g


0

character_set_server is not relevant. The setting on the database is just a default for when you CREATE TABLE without specifying a DEFAULT CHARACTER SET. You need The bytes in your client to be encoded utf8. The connection specifies utf8. If you are using mysqli, use mysqli_set_charset('utf8'). The column in the table must be CHARACTER SET utf8. Look ...


2

Stop postgresql service cp -a source_data_directory destination_data_directory chown -R postgres_user /destination_data_directory export PGDATA=destination_data_directory Changing data directory to destination_data_directory within postgresql.conf pg_ctl start


1

What you are describing is data migration. To do this successfully you need to understand both the old data and new schemas, business rules and the data itself. To migrate the data the most common option is to write conversion scripts which insert data into the new schema that respect the new schemas rules, referential integrity and business logic. If ...


3

Its easy using T-SQL. I would suggest not to use Maiantenance plan. Instead go with a much flexible and widely adapted TSQL based solution - SQL Server Backup by Ola Hallengren EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES, -DB1,DB2', -- All user databases, except Db1, DB2 @Directory = 'C:\Backup', @BackupType = 'FULL', @Verify = 'Y', @Compress ...


1

This seems to me like the opposite of a problem... Regardless of requirements, I just feel better knowing every database is being backed up. Unless these two databases are quite large and can fill up a significant amount of disk space with backups, what negatives are there to backing up those files?


1

I would check the Management > Maintenance Plans folder, in the Object Explorer pane of SSMS. The other place would be under SQL Server Agent > Jobs, so I'm curious what you meant by, "I was able to find the full backup option on the SQL Agent." If there is a job titled "Full Backup" under the Jobs folder, then that's probably what you're looking for.


0

I do not think that you want to test Oracle 12c itself but if the applications still function. In that case I propose that the developers (or test team) perform there 'normal' application tests to see if the applications still function correct. The only thing that the DBA might give is information on the differences between Oracle10g and Oracle 12c at the ...


0

Are all of your parameters set up properly for data guard? On your primary site, FAL server is your standby DB and client is your primary. That should be flip flopped on the DG site. And what about your archive destinations? Are they set up to ship logs over to the standby? Are they enabled? If you do the follow: show parameter fal show parameter ...



Top 50 recent answers are included