Maintaining large numbers of client tnsnames.ora files is a pain. Oracle Internet Directory is the recommended solution to this, but it is not an easy exercise to set up or migrate to. An easier alternative is to set TNS_ADMIN in all client PCs to use a single common tnsnames.ora file set TNS_ADMIN=\\network\share\path All the client PCs […]
Read More Replace all tnsnames.ora files with a single master file using TNS_ADMINThe best utilities to use when copying/mirroring/backing up files are: robocopy – Windows rsync – Linux lftp – Linux without setting up password-less ssh Scripts can supply a password to sftp using lftp like this: lftp -u user:password sftp://server.domain.com << END_LFTP put /directory/file.txt quit END_LFTP You can set up password-less ssh this way.
Read More Copy mirror files with robocopy rsync lftpSQL Server transaction logs need backed up regularly, or they will grow and grow forever, eventually filling up all available disk space. That is best done in a maintenance plan. Unfortunately you have to remember to set that up post install, it doesn’t happen out of the box by default (why doesn’t it? Surely it […]
Read More Shrink Transaction Logs in all every database SQL ServerRemote Desktop Connection Manager, a free download from http://www.microsoft.com/en-gb/download/details.aspx?id=21101, lets you switch quickly between windows servers, saves the servers and your login details in a list.
Read More Remote Desktop Connection ManagerIf data has been removed due to user error, it can be easily and quickly be brought back without the need for restores, using flashback query: CREATE TABLE temp_rescue AS SELECT * FROM owner.table AS OF TIMESTAMP TO_TIMESTAMP ( ‘Thu 14-Nov-2013 13:00:00’ , ‘Dy DD-Mon-YYYY HH24:MI:SS’ ) ; Or if the entire table has been […]
Read More Flashback query alternative to point in time recoveryErrors in /var/log/messages: kernel: Current sda: sense key Recovered Error kernel: Additional sense: Failure prediction threshold exceeded kernel: EXT3-fs error (device sda9) in ext3_setattr: Journal has aborted kernel: EXT3-fs error (device sda9) in start_transaction: Journal has aborted Fix with (requires reboot + can be slow): fsck.ext3 -f -y /dev/sda9 To identify that name /dev/sda9 I […]
Read More sda: sense key Recovered Error linux fsck forcefsckRman point in time restore: rman connect target / connect catalog user/password@catalog_database run { shutdown immediate ! now take an OS copy of current control file and current online redo logs in case need to regress startup mount set until time “to_date(‘Mon 04-Nov-2013 09:00:00′,’Dy DD-Mon-YYYY HH24:MI:SS’)” ; restore database preview ; — optional, to check […]
Read More RMAN point in time restoreList recently changed procedures and views in a database: SELECT name , create_date , modify_date FROM sys.objects WHERE ( create_date >= DATEADD ( DAY , -7 , GETDATE() ) OR modify_date >= DATEADD ( DAY , -7 , GETDATE() ) ) AND type_desc IN ( ‘VIEW’ , ‘SQL_STORED_PROCEDURE’ ) ORDER BY name
Read More SQL Server recently changed objectsPublic database links are a well known security problem – all database users with the most minimal privileges (create session) are able to use the public database link, even including deleting data across the link. Use this to see any public database links going outwards from a database: col host form a30 col db_link form […]
Read More Public Database Links security fix using viewsScript to hunt and lock/drop user accounts, can call from a central script connecting to multiple databases: BEGIN FOR d1 IN ( SELECT username FROM dba_users WHERE username LIKE ‘AGXDL%’ AND account_status != ‘LOCKED’ ORDER BY 1 ) LOOP dbms_output.put_line ( ‘alter user ‘ || d1.username || ‘ account lock ‘ ) ; execute immediate […]
Read More PL/SQL to find and lock or drop user accounts