Moving UNDO and TEMP files in Oracle

This summer I had the pleasure of reading a couple of really good muskie fishing books. The first was Musky Strategy by row-trolling legend Tom Gelb. There were a number of cool things in this book, but the main thing that struck me was how scientific Tom’s approach to muskie fishing is. He went so far as to test the depth his lures ran while trolling by rowing parallel to a shoreline over and over from deep to shallow to see when the lure started contacting the bottom. The book was a fun read, and showed how can you succeed without all the modern electronic tools as long as you’re willing to put in the time. The second was Time on the Water by Bill Gardner. This book was a story instead of a nonfictional book to teach tactics. It tells the story of one man’s quest to catch a big muskie while fishing the entire season for a year in Northern Wisconsin. It was a fun read, and the main takeaway here was just how difficult it is to catch a big muskie, even when fishing for them every day. If you like fishing, check out the books!
Loss of free space on a disk is something that we as DBAs are always dealing with. Perhaps we are able to ask our SAN admin to give us more space, but in many cases we are not. In those cases, moving database files from one logical drive to another can answer this challenge. Note that care should be taken when considering moving database files. Drives can be different speeds, and their may be a reason a database file is on one drive an not another.
I recently needed to move a couple Oracle system tablespace files (UNDO and TEMP) from the C: drive (where they never should have been put in the first place) to a different drive.
20171215 OracleDB Drive Space
The method for doing this is relatively simple. Create a second tablespace for each on the “other” drive, make it the default tablespace, and drop the original tablespace.
CREATE TEMPORARY TABLESPACE temp2 TEMPFILE 'D:\APP\ORACLE\ORADATA\LEGENDOPRD\TEMP03.DBF' size 30G AUTOEXTEND ON NEXT 1G MAXSIZE unlimited;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

CREATE UNDO TABLESPACE undotbs2 datafile 'D:\APP\ORACLE\ORADATA\LEGENDOPRD\UNDO02.DBF' size 20G;
ALTER SYSTEM SET undo_tablespace=undotbs2;
DROP tablespace UNDOTBS1 INCLUDING CONTENTS;

I found after doing this I still needed to delete the old files from the OS folder, and I couldn’t do that until the Oracle database service had been recycled, but after that my drive space was much healthier.

Advertisements

Custom Roles in SSRS

Our world continues to drift into virtual reality. I don’t, of course, mean actual virtual reality, but I do mean we are growing further and further away from the great outdoors and closer and closer to spending all our team immersed in technology. A Nielsen Company audience report from 2016 indicated adults in the US devoted about 10 hours and 39 minutes each day to consuming media (tablets, smartphones, computers, TVs, etc.). A BBC news article┬áreports that kids aged 5 – 16 spend an average of 6.5 hours each day in front of a screen, compared to just three hours back in 1995. This trend is scary for a number of reasons, but it is clear that many people, especially kids, are losing out on experiencing the great outdoors. There is nothing in the virtual world that can compare to feeling a big fish tugging on the end of your line, or sitting in a tree stand as a deer stealthily approaches, or even just taking a walk around a lake and enjoying the fresh air and beautiful view. It’s up to us adults to teach our children the joy of outdoor sports.
Somehow, up until this point in my DBA career I’ve never had to mess with custom SSRS permissions. SSRS installs with five permissions roles by default.
20171205 Default Roles
As such, I was completely unaware that you can create your own custom roles. You can’t do this from Report Manager, as far as I could find in my SSRS 2008 R2 version. I had to log into the Report Server with SSMS. As a side note, logging in with Windows Authentication using localhost as the server didn’t work. I actually had to put in http://servername/reportserver to use Windows Authentication, even when logging in from on the server.
Once I had successfully logged in, I was able to add a new role.
20171205 Add New Role
I wanted to create a role that would allow a user to manage subscriptions for others. The only built in role that has that permission is Content Manager, but that role also has several other permissions that I didn’t want to confer on my target user. Remember a user can have multiple roles, so there is not reason not to get as granular as necessary. Below are the Tasks available for assigning to the custom roles:
20171205 Subscription Manager
Similarly, System Roles can also be customized. By default there is only System Administrator and System User. Here are the options for creating a new System Role.
20171205 Job Manager.PNG
Using custom roles can make your job of managing the permissions on Reporting Services easier and more precise.