Press "Enter" to skip to content

George Woods Posts

FindTime for meetings

I received an email today from FindTime Notifications. If you haven’t heard of this Outlook add-in, it’s a pretty cool addition from Microsoft, that allows you to ask people outside of your company to vote on times that they would be available to meet. You can mark a time slot as preferred, yes, or no. You can also suggest other time slots. Next time you need to schedule a meeting, take a look at https://findtime.microsoft.com/

 

 

 

 

 

 

Leave a Comment

Using alias to upgrade old servers

One trick that I have used numerous times and really like is to use a server alias when moving to new servers. For instance, we were moving numerous databases from a 2000 server named serversql0001. Since this server was so old, we had no real idea how many places or connections people may have used the alias, so we decided during the downtime to remove this alias and point it to the new server that we were moving the databases to. After we brought the new server up, we checked some of the software that was connecting to the databases, and as expected, everything was fine, with no need for connection changes.

Leave a Comment

Connect to SQL Server when you don’t know SA account or don’t have sysadmin access

Below are the steps you need to perform to grant SYSADMIN access to a user in SQL Server in case you are completely locked out.

1. Download PSexec to connect using SQL Server Management Studio using the NT Authority\System

Download PsTools from https://download.sysinternals.com/files/PSTools.zip
Unzip the content and copy PsExec.exe to C:\Windows\System32

2. Stop the SQL Server and SQL Server Agent services on the server.

3. Open a cmd prompt window as administrator and navigate to SQL Server’s Binn directory. You may need to adjust your path based on your install location.

ex. C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Binn

4. Once you are in SQL Server’s Binn directory run the ‘sqlservr -m’ command to start SQL Server in single user mode as shown below. Had to add the location of the ERRORLOG

sqlservr -m -e C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Log\ERRORLOG;

If it’s a named instance:
sqlservr -m -s -e C:\Program Files\Microsoft SQL Server\MSSQL11.SERVICECORE\MSSQL\Log\ERRORLOG;

After the SQL Server instance was started in single user mode, I was receiving logon errors form other users that were trying to logon from the application, when it was restarted in single user mode. I ignored the errors and moved on.

5. Execute PsExec

run cmd as administrator

PsExec -s -i “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”

The above command will launch SQL Server Management Studio and gives you a “Connect to Server” window and the User Name will be pre-populated with NT AUTHORITY\SYSTEM

6. Click Connect and then go in to Security > Logins and add your account as a sysadmin

7. restart the sql server services

 

 

 

Leave a Comment

Reading List – 2018

I don’t normally do New Year Resolutions, but during the Christmas break I was reading numerous articles on LinkedIn, I’ve always enjoyed Linkedin, with it’s career connections and articles. However, I also realized how much wasted time I spent on Facebook and Twitter, so because it’s that time of year, I am calling this my “New Year’s Resolution”: Spend more time bettering myself and my career

First up, purchase a kindle and I got a great deal. I wasn’t looking for two kindles, but when I saw Amazon had a special deal, buy two kindles get $60 off, I couldn’t pass the opportunity to gain some bonus points with my wife. Her kindle is a few years old and only 6gb, so I was able to get two Kindle Fire HD 8’s for $99.

Create a list of books to read and afterwards write a review. I am sure this list will continue to grow, but for starters and in no particular order:

Hit Refresh, Microsoft CEO, Satya Nadella 

Business Adventures, John Brooks  

Mindset, Carol Dweck 

The Better Angels of Our Nature, Steven Pinker 

The Innovators, Walter Isaccson 

Hackers: Heroes of the Computer Revolution, Steven Levy 

The Four: The Hidden DNA of Amazon, Apple, Facebook, and Google, Scott Galloway

Not a book, but… Oracle E-Business Suite Architecture

Leave any book recommendations in the comments.

 

Leave a Comment

Determine what Linux Version

4 different commands that you can run to see information about your Linux distro.

[oracle@server ~]$ cat /etc/issue
Oracle Linux Server release 6.8

[oracle@server ~]$ cat /etc/*release
LSB_VERSION=base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Oracle Linux Server release 6.8
NAME=”Oracle Linux Server”
VERSION=”6.8″
ID=”ol”
VERSION_ID=”6.8″
PRETTY_NAME=”Oracle Linux Server 6.8″
ANSI_COLOR=”0;31″
CPE_NAME=”cpe:/o:oracle:linux:6:8:server”
HOME_URL=”https://linux.oracle.com/”
BUG_REPORT_URL=”https://bugzilla.oracle.com/”

ORACLE_BUGZILLA_PRODUCT=”Oracle Linux 6″
ORACLE_BUGZILLA_PRODUCT_VERSION=6.8
ORACLE_SUPPORT_PRODUCT=”Oracle Linux”
ORACLE_SUPPORT_PRODUCT_VERSION=6.8
Red Hat Enterprise Linux Server release 6.8 (Santiago)
Oracle Linux Server release 6.8

Kernel Version commands
[oracle@server ~]$ uname -r
4.1.12-103.9.6.el6uek.x86_64

**Whether you are using 32 bit or 64 bit version… the x86_64 means a 64 bit kernel

[oracle@server ~]$ uname -a
Linux server 4.1.12-103.9.6.el6uek.x86_64 #2 SMP Wed Nov 15 18:03:27 PST 2017 x86_64 x86_64 x86_64 GNU/Linux

Leave a Comment

Create Data Guard standby using RMAN


Production server – serverProd
Standby server – serverStby
Production database – whseprod
Standby database – whsestby
Step 1: Using RMAN, Backup the database that includes backup of datafiles, archivelogs and controlfile for standby

[oracle@serverProd]$ rman target /
connected to target database: whseprod (DBID=123322)
RMAN> run {
allocate channel disk1 type disk format ‘/backup/whseprod_restore/%d%U’ maxpiecesize 5 G;
allocate channel disk2 type disk format ‘/backup/whseprod_restore/%d%U’ maxpiecesize 5 G;
allocate channel disk3 type disk format ‘/backup/whseprod_restore/%d%U’ maxpiecesize 5 G;
allocate channel disk4 type disk format ‘/backup/whseprod_restore/%d%U’ maxpiecesize 5 G;
allocate channel disk5 type disk format ‘/backup/whseprod_restore/%d%U’ maxpiecesize 5 G;
backup force  as BACKUPSET tag ‘%TAG’ database;
backup as BACKUPSET tag ‘%TAG’ archivelog;
release channel disk1;
release channel disk2;
release channel disk3;
release channel disk4;
release channel disk5;
}
RMAN> run {
allocate channel disk1 type disk format ‘/backup/whseprod_restore/%d%U’ maxpiecesize 5 G;
backup force  as BACKUPSET tag ‘%TAG’ current controlfile for standby;
release channel disk1;
}
Step 2: Move the backups to the standby server using ftp
               cd to directory that you want the files to go in on standby system
[oracle@serverStby ..]$ cd /backup/whseprod_restore
[oracle@serverStby /backup/whseprod_restore]$ ftp serverProd
ftp> bin
    ftp> prompt
change directory on the source system
                    ftp> cd /backup/whseprod_restore
                    ftp> mget *
                    ftp> quit

Step 3: Make proper changes in the parameter files of both primary and standby database
whseprod init file
DB_NAME=whseprod
DB_UNIQUE_NAME=whse
CONTROL_FILES=’/whseredo/oradata/control01.ctl’,’/whseredo/oradata/control02.ctl’,’/whseredo/oradata/control03.ctl’
LOG_ARCHIVE_DEST_1=’LOCATION=/whsearch/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)’
alter system set FAL_CLIENT=whseprod scope=both;
alter system set FAL_SERVER=whsestby scope=both;
alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(whseprod, whsestby)’ scope=both;
alter system set log_archive_dest_2 =’SERVICE=whsestby LGWR ASYNC=40960 DB_UNIQUE_name=whsestby OPTIONAL REOPEN=120 MAX_FAILURE=120 NET_TIMEOUT=900′ scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
 whsestby init file
DB_NAME=whse
DB_UNIQUE_NAME=whsestby
CONTROL_FILES=’/whseredo/oradata/control01.ctl’,’/whseredo/oradata/control02.ctl’,’/whseredo/oradata/control03.ctl’
LOG_ARCHIVE_DEST_1=’LOCATION=/whsearch/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)’
alter system set FAL_CLIENT=whsestby scope=both;
alter system set FAL_SERVER=whseprod scope=both;
alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(whseprod, whsestby)’ scope=both;
alter system set log_archive_dest_2 =’SERVICE=whseprod LGWR ASYNC=40960 DB_UNIQUE_name=whseprod OPTIONAL REOPEN=120 MAX_FAILURE=120 NET_TIMEOUT=900′ scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
Step 4: Do the restore and recover on standbydatabase
           serverStby> rman target /
          RMAN> startup nomount
          RMAN> restore standby controlfile from ‘/backup/whseprod_restore/c-1778-20171202-02’;
          serverStby>sqlplus as sysdba
          sql> alter database mount standby database;
          serverStby> rman target /
          RMAN> restore database
          Rman> list backup of archivelog all
***copied all of the archives that were created after the backup to the /whsearch/arch directory from the source
               cd to directory that you want the files to go in on standby system
[oracle@serverStby ..]$ cd /whsearch/arch
[oracle@serverStby /whsesarch/arch]$ ftp serverProd
ftp> bin
    ftp> prompt
change directory on the source system
                    ftp> cd /whsearch/arch
                    ftp> mget arch_111_2*
                    ftp> quit
          Rman> recover database
Step 5: Put the standby database in recover managed mode
        
        sql> alter database recover managed standby database disconnect from session;
Database altered.
Verify the Physical Standby Database Is Performing Properly
On the standby database, query the V$ARCHIVED_LOG view to identify existing files in
the archived redo log.
          sql> select sequence#, applied, to_char(first_time,’mm/dd/yy hh24:mi:ss’) first from v$archived_log order by first_time;
Step 6: Add standby redo logs
          sql>alter database recover managed standby database cancel;
sql>alter database add standby logfile group 15 ‘/whseredo/oradata/whse_sr15.log’ size 1048576000;
sql>alter database add standby logfile group 16 ‘/whseredo/oradata/whse_sr16.log’ size 1048576000;
sql>alter database add standby logfile group 17 ‘/whseredo/oradata/whse_sr17.log’ size 1048576000;
sql>alter database recover managed standby database disconnect from session;
Step 7: Add Data Guard Broker through OEM
                –make sure the standby’s are in oem
    –set preferred credentials by trying to log in through oem

      Click on primary database in OEM

      On Home Page ->availability->add standby database
      Choose third option Manage an Existing standby database with data guard broker and follow the on-screen instructions
Leave a Comment

2018’s list of goals

Many items peak my interest and that can sometimes make it difficult to choose a few that I would like to gain a deeper learning of, but I think my current role will guide me in 2018.

In no particular order:

SQL Server on Linux: This is a no brainer. As long as I can remember, I have preferred to work with command line over gui based tools. I’ve always felt like I have more control over what I am doing. And now that SQL Server is on Linux, I want to dig in and learn whatever I can. I think I also like the idea, that there aren’t a lot of SQL Server DBA’s that are familiar with Linux.

Cloud: I’ve played around in the cloud for years, but with little reason. Between Azure options and the Oracle Cloud, it’s time to start looking at the differences and see where each can help me.

Power BI: With Power BI Premium, I think this is a game changer for Corporate adoption.

Make it to PASS Summit: I’ve been to Oracle Open World 3 times, but do to job responsibilities or other co-workers training needs, I have unfortunately never made it to PASS. I don’t mind Open World, but more and more I can see PASS being more beneficial.

Leave a Comment

Oracle OEM details in PowerBI


Instead of filling out excel files with all of our Oracle database details, I decided to use Power BI to organize that information.
Prerequisites

  •  Must have Oracle client installed on computer
  • Must have a tnsnames.ora file to connect to Oracle database
  • Connecting to Oracle Database


The server entry is actually the entry in your tnsnames. Example below

Also fill in the select statement for the view that you want



In the next window choose database and then supply database credentials

A few of the OEM tables that I find interesting
mgmt.$hw_nic
mgmt.$jobs
mgmt.$os_hw_summary
mgmt.$target
mgmt.$target_properties

Leave a Comment

Oracle Open World and PASS Summit

A little less than a month ago I attended Oracle Open World in San Francisco. It’s been a few years since I last attended the conference. Although San Francisco seems to have changed the conference has stayed the same. Numerous days of sessions that are very informative. As I felt the last time I attended, many of the sessions seem like marketing sessions, this isn’t a bad thing, just different. I did really enjoy some sessions dealing with Oracle EBS, Oracle Cloud and DBA topics. One session “Navigating your DBA Career in the Oracle Cloud” by Craig Shallahamer from orapub.com was very eye opening. Enough so, that I have started to look more closely at my future.

PASS Summit is going on right now and unfortunately, I am not in attendance. However, thanks to PASSTV, I have been able to watch numerous days worth of keynotes and sessions. This is definitely a conference that I would like to attend in the future. I know a good amount of people that attend every year and love this conference.

Leave a Comment

Combination of Both Worlds

I just recently came back to my blog and noticed that my last post stated that I was going back to Oracle. I did, but the fit with that employer was not the best and I moved on to another company at the beginning of 2016. I couldn’t be happier with that move in 2016. I get to work with SQL Server and Oracle along with other software packages. Some items that I am working on or researching and would like to blog about in the future, Automic Scheduling software, SQL Server on Linux, Azure, Azure Analysis Services, Business Intelligence, Power BI, Oracle Data Guard, Oracle Cloud and other various items.

Leave a Comment