Herding Elephants: admin

Showing posts with label admin. Show all posts

2013-08-21

Taming Your postgresql.conf Changes With Includes

A few weeks ago, my comrade +Douglas Hunley and I were working on a small project together for a customer that involved a fair number of changes to parameters in the Postgres database configuration file, postgresql.conf. And being the ridiculously anal admin type that I am when it comes to the organization of and commentary throughout my config files for any service, I was beginning to have fits with the havoc this was wreaking upon the relative beauty that is postgresql.conf. (I blame my years in design engineering working with engineering change requests for this particular trait.)

postgresql.conf is a masterpiece of a configuration file, being ridiculously well documented throughout with a plethora of commentary to boot and parameters grouped by category and functionality, rather than just a straight alphabetical listing. The numerous edits being made, plus my penchant for thorough commentary on each change was breaking up the flow of the file. The result was not making for easy reading. And the more I tried to address that issue, the less the changes that were being made stood out.

And then I recalled those wonderful includes in the Apache config files I used to know and love, wondering if there was any chance Postgres might have a similar capability. Praise be to the Postgres Docs, it does. Just throw them at the end of the file and they'll override any previous settings!

Okay, so why do this? Consider the elegant simplicity of organization includes provide...

Easily Set Standard Configs For Related Parameters

Say, for example, your organization has a standard logging config you want running on every server. You might consider having a standard postgresql.conf file with these parameters set. But what if there are physical differences between the servers that affect other parameters, such as work_mem, effective_shared_cache, etc.? Or your WAL settings differ? Or autovacuum? You can easily see where this is going.

Organization That Self Documents

Whether you work with many databases or just one, you're eventually going to return to one after enough time has passed for you to have forgotten everything you (or someone else, for that matter) you'd set and/or why it was set that way. Let's say your organization comes up with a standard naming convention for these files. As I work for +EnterpriseDB (EDB), I might use this to name my files:

    edb_logging.conf
    edb_tuning.conf
    edb_vacuuming.conf

and so on. Now, when you look at the directory listing it becomes readily apparent fairly quickly where to look for any custom settings I or my colleagues may have made, doesn't it.

2013-05-29

Book Review — Instant PostgreSQL Starter

Following up on the positive experience I had recently with another of Packt Publishing's "Instant" titles for Postgres, I picked up a copy of Instant PostgreSQL Starter and dove right in. (And I must say I'm enjoying these short format books.)

The first major section of the book seems targeted at someone with absolutely no experience, or perhaps minimal experience, with databases whatsoever. Yet I still found it useful, as I'm coming to Postgres from an Oracle direction. Simply having to walk through the installation process, perform the basic table creation, inserts, updates, queries, etc. that you will be walked through provided an easy way of becoming more familiar with the nuances of the database and its GUI admin tool, pgAdmin3.

As a brief aside — I think the author's choice of going down a fairly platform agnostic path by utilizing +EnterpriseDB's installer and interacting with the database through pgAdmin3, rather than psql in a shell, was a good one. It provides a one size fits all approach for anyone to get up and running without having to delve into the nuances of shells on different systems, various installation methods, and so on.

What I think was the most valuable portion of the book for me was "Top 9 features you need to know about", which gives an overview of such topics as hashing passwords for storage, XML in Postgres, and full-text search, to name a few. For someone coming to Postgres from another database, it's learning about these kinds of features that truly help you get up to speed a bit faster. And I appreciate being able to get a brief overview of these topics in order to simply know about them and how they work, without becoming an expert in any (just yet!).

2012-05-23

Renaming Your Oracle Instance, Part 2

Alrighty then... Let's finish this thing up, shall we?

As I had mentioned in part 1 of this 2 part post, I don't feel that a database rename is complete until all of the associated file system structures also reflect the name change. I mean, how'd you like to be the next DBA to be working on this database, now named 'JGDB', and not be able to quickly locate the data files because they are still under $ORACLE_BASE/oradata/orcl rather than $ORACLE_BASE/oradata/jgdb, as you'd expect?

First, let's shut down the database and put the init.ora file where it belongs and name it such that we don't have to define the PFILE location when starting the instance.

SQL> shutdown

SQL> exit

$ mv /home/oracle/jgdb_init.ora $ORACLE_HOME/dbs/initjgdb.ora

Another piece of housekeeping to attend to, if you haven't done so already, is to change your environment variables. Mine are being set in ~/.bashrc

export ORACLE_SID=jgdb
export ORACLE_UNQNAME=jgdb # set for 'emctl start dbconsole'

Now we start back up again, without designating the PFILE location, as it should be found automatically now that we have renamed it and placed in in the proper location.

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 23 11:57:43 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

My steps below are based upon the following Oracle docs:

Creating Additional Copies, Renaming, and Relocating Control Files
http://docs.oracle.com/cd/E11882_01/server.112/e25494/control003.htm#i1106242

Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces
http://docs.oracle.com/cd/E11882_01/server.112/e25494/control003.htm#i1006277

Relocating and Renaming Redo Log Members
http://docs.oracle.com/cd/E11882_01/server.112/e25494/onlineredo004.htm#i1006447

We have three major items to move: data files, redo logs, and control files.

I like to avoid any work that the system can do for me. (I like to call this 'efficiency'!) So, I will use SQL to create the SQL needed to update the database with the location to which I will be moving the redo logs and data files. I saved the following as create_rename_script.sql

set linesize 200
set pagesize 100
set heading off

spool rename_files.sql

select
    'ALTER DATABASE RENAME FILE ''' ||
    file_name ||
    ''' TO ''' ||
    replace( file_name, 'orcl', 'jgdb') || ''';'
from
    dba_data_files
/

select
    'ALTER DATABASE RENAME FILE ''' ||
    member ||
    ''' TO ''' ||
    replace( file_name, 'orcl', 'jgdb') || ''';'
from
    v$logfile
/

spool off

You should now have a file named rename_files.sql in the directory in which you ran the script.

Shut down the database. It's time to move stuff... 'n things...

SQL > shutdown

SQL > exit

Taking a look at initjgdb.ora, you will note that there are a couple of parameters that refer to OS file system locations with 'orcl' in them. They are listed below. I have replaced 'orcl' with 'jgdb' as we did in part 1.

*audit._file_dest='home/oracle/app/oracle/admin/jgdb/adump'

*.control_files='/home/oracle/app/oracle/oradata/jgdb/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/jgdb/control02.ctl'

Moving the redo logs, data files, control files, and adump destination...

$ mv $ORACLE_BASE/oradata/orcl $ORACLE_BASE/oradata/jgdb

$ mv $ORACLE_BASE/flash_recovery_area/orcl $ORACLE_BASE/flash_recovery_area/jgdb

Because we've updated initjgdb.ora with the location of the control files and adump, we can start the database up again and mount it.

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 23 12:15:32 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Now we can update the data file and redo log locations using our script rename_files.sql that we created earlier and open the database for business...

SQL> @rename_files.sql
Database altered.
.
.
.
Database altered.

SQL> alter database open;
Database altered.

Done!

If you have any feedback whatsoever on the steps I followed to rename my Oracle database, I'd be grateful for your input!

2012-04-23

Renaming Your Oracle Instance, Part 1

For DBA's, there's nothing better or more useful than having your own database to play in. How else are you going to learn, practice, and try new features?

So with that in mind, I recently set up an Oracle Linux 6 virtual machine running on Oracle VirtualBox, downloaded Oracle Database 11g R2, and installed it with DBCA. Not a very difficult process, and I now had an up and running instance with the default name of ORCL.

And that presented a problem, in that even though this is going to be playground of sorts for me, I do intend to have it on the network. The default, generic SID of orcl is likely going to be a problem. I needed to rename my database. I'll be renaming it to jgdb.

While I could have just started over, I thought it would be an worthwhile exercise to try renaming the database and modifying it's various components to reflect the new SID, as it's not generally the kind of this you're doing every day at work. The following is the process I went though, starting with the DBNEWID utility.

To get started, I used the documentation on DBNEWID for 11.2 found here.

1) If you using a binary SPFILE (the default when your database has been created by DBCA, as mine was), you'll want to back it up to a text init.ora file. This is needed to start the instance after DBNEWID does its magic.

SQL> connect / as sysdba
Connected.

SQL> create pfile='/home/oracle/jgdb_init.ora' from spfile;
File created.

2) Run DBNEWID from the shell. (I'm on Linux, running the bash shell.)

$ nid TARGET=SYS DBNAME=jgdb SETNAME=YES

DBNEWID: Release 11.2.0.1.0 - Production on Thu Apr 19 10:08:32 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Password: 
Connected to database ORCL (DBID=1308307467)

Connected to server version 11.2.0

Control Files in database:
    /home/oracle/app/oracle/oradata/orcl/control01.ctl
    /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

Change database name of database ORCL to JGDB? (Y/[N]) => Y

Proceeding with operation
Changing database name from ORCL to JGDB
    Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - modified
    Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - modified
    Datafile /home/oracle/app/oracle/oradata/orcl/system01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/sysaux01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/undotbs01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/users01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/example01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/temp01.db - wrote new name
    Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - wrote new name
    Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - wrote new name
    Instance shut down

Database name changed to JGDB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

3) As you can see above, DBNEWID shut down the instance. So now we edit the text init.ora file to reflect the new database name. Below, is jgdb_init.ora that we exported earlier. What I have done is boldface everywhere that I replaced 'orcl' with 'jgdb'.

What you may notice, is that I have NOT changed the various file system paths in which 'orcl' appears. Now's not the time, nor is it strictly necessary.

Also, I did change the name of the local listener. This change was not actually required. However, my goal here is not just a database rename. I feel strongly that, if possible, everything that referenced the old SID should be modified for the sake of consistency.

jgdb.__db_cache_size=209715200
jgdb.__java_pool_size=4194304
jgdb.__large_pool_size=4194304
jgdb.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
jgdb.__pga_aggregate_target=293601280
jgdb.__sga_target=545259520
jgdb.__shared_io_pool_size=0
jgdb.__shared_pool_size=314572800
jgdb.__streams_pool_size=4194304
*.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl'
    '/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain='gotoracle.com'
*.db_name='jgdb'
*.db_recovery_file_dest='/home/oracle/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jgdbXDB)'
*.local_listener='LISTENER_JGDB'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1

4) Having changed the name of the local listener, we now need to modify the tnsnames.ora file to reflect the change.

# tnsnames.ora Network Configuration File:
#    /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_JGDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


JGDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jgdb.gotoracle.com)
    )
  )

5) At this point we should be able to start the database using our PFILE that was exported and modified earlier.

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 20 11:33:25 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup pfile='/home/oracle/jgdb_init.ora'
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size      2217952 bytes
Variable Size    620759072 bytes
Database Buffers   209715200 bytes
Redo Buffers      2412544 bytes
Database mounted.
Database opened.

Great! We're up and running. But we are not completely done. In my next post, we will be relocating the control file(s), redo logs, and data files from $ORACLE_BASE/oradata/orcl to $ORACLE_BASE/oradata/jgdb for consistency.

On to Part 2...