11.2 Oracle Restart – Changing Hostname
It has taken me a good deal longer that I would have expected to change the hostname on some cloned VMs that are home to Oracle 11.2 databases running on ASM with Oracle Restart.
I was clearly not alone in thinking that it would just be a case of running localconfig delete/localconfig add, as I have done in the past for hosts running Oracle database 10.2 with ASM for storage. However, things have changed and having done a fair amount of searching using both MetaLink (My Oracle Support) and Google it appears that there isn’t much out there yet in the form of clear instructions. The best I could find was My Oracle Support article ID 887658.1, which got me moving in the right direction.
So, in the hope of saving others the time I have lost working this out, here are the steps I used to rename a Linux host running Oracle Database 11.2 with ASM in an Oracle Restart configuration. The steps have been written for an installation that splits the ownership of the “Grid Infrastructure” and the database between a user named grid and a user named oracle respectively. It is important who you run at least some of these commands as, so please check which user you are before each command. Or more likely, if things don’t look to be working then check which user you ran the command as before going further. It is easy to backtrack if you do get it wrong.
Remove the existing configuration as root (with ORACLE_HOME set to the grid home):
[root@old-host grid]# $ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/perl/lib -I $ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl -delete
* In some of my earlier attempts I stopped everything gracefully, but it does not appear to be necessary.
Change the value of HOSTNAME in the network file using the editor of your choice.
[root@old-host ~]# vi /etc/sysconfig/network
Update /etc/hosts as appropriate
[root@old-host ~]# vi /etc/hosts
Change the IP address in ifcfg-<interface> as required
[root@old-host ~]# vi /etc/sysconfig/network-scripts/ifcfg-eth0
Change the hostname in the listener configuration file
[root@old-host ~]# vi $ORACLE_HOME/network/admin/listener.ora
* Clearly you don’t need to be root to do the previous command, but as reboot is next it doesn’t seem worth switching user.
Reboot to pickup hostname change
When the host has restarted run roothas.pl as root (with ORACLE_HOME set to the grid home)
[root@new-host grid]# $ORACLE_HOME/perl/bin/perl -I $ORACLE_HOME/perl/lib -I $ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl
The output should look something like:
2009-12-19 15:10:36: Checking for super user privileges 2009-12-19 15:10:36: User has super user privileges 2009-12-19 15:10:36: Parsing the host name Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params LOCAL ADD MODE Creating OCR keys for user 'grid', privgrp 'oinstall'.. Operation successful. CRS-4664: Node new-host successfully pinned. Adding daemon to inittab CRS-4123: Oracle High Availability Services has been started. ohasd is starting 2009/12/19 15:11:58 /u01/app/grid/product/11.2.0/grid/cdata/new-host/backup_20091219_151158.olr Successfully configured Oracle Grid Infrastructure for a Standalone Server
Exit from root user.
You now need to add the listener, ASM, database and services (if applicable) into the Oracle Restart configuration.
[grid@new-host ~]$ srvctl add listener [grid@new-host ~]$ srvctl add asm -d '/dev/sd[a-h]1' [grid@new-host ~]$ srvctl start listener [grid@new-host ~]$ srvctl start asm
* Your asm_diskstring, specified with the -d option to “srvctl add asm”, with almost certainly be different!
Note that the diskgroups are not automatically added back to the Oracle Restart configuration (as shown by the output of “srvctl status” below) and must be manually started:
[grid@new-host ~]$ srvctl status diskgroup -g data PRCR-1001 : Resource ora.DATA.dg does not exist [grid@new-host ~]$ sqlplus /nolog SQL> conn / as sysasm Connected. SQL> alter diskgroup data mount; Diskgroup altered. SQL> exit [grid@new-host ~]$ srvctl status diskgroup -g data Disk Group data is running on new-host [grid@new-host ~]$
Adding the database to Oracle Restart:
[oracle@new-host ~]$ srvctl add database -d ora11gr2 -o $ORACLE_HOME -n ora11gr2 -p +DATA/ora11gr2/spfileora11gr2.ora -a DATA [oracle@new-host ~]$ srvctl config database -d ora11gr2 Database unique name: ora11gr2 Database name: ora11gr2 Oracle home: /u01/app/oracle/product/11.2.0/db_1 Oracle user: grid Spfile: +DATA/ora11gr2/spfileora11gr2.ora Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Disk Groups: DATA Services: [oracle@new-host ~]$ srvctl start database -d ora11gr2 [oracle@new-host ~]$ srvctl status database -d ora11gr2 Database is running. [oracle@new-host ~]$
That should be it. The only other place to update the home name is in the tnsnames.ora file.
Drop Yourself
This slightly odd little quirk interested me so I thought I’d do a quick post… It is just something that I hadn’t considered before.
Create a DBA user:
SYS@xe> create user dba_user identified by basic_pass; User created. SYS@xe> grant dba to dba_user; Grant succeeded.
Create a SYSDBA user:
SYS@xe> create user sysdba_user identified by basic_pass; User created. SYS@xe> grant sysdba to sysdba_user; Grant succeeded.
Connect as DBA user and try to drop yourself
SYS@xe> connect dba_user/basic_pass@xe Connected. DBA_USER@xe> drop user dba_user; drop user dba_user * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected
As expected, you can’t drop yourself.
Switch to SYSDBA user and drop yourself
DBA_USER@xe> connect sysdba_user/basic_pass@xe as sysdba Connected. SYS@xe> drop user sysdba_user; User dropped. SYS@xe>
The interesting point to me is that at this point you have just dropped yourself as a user and therefore can’t connect anymore (unless you have another way in):
SYS@xe> connect sysdba_user/basic_pass@xe as sysdba ERROR: ORA-01031: insufficient privileges Warning: You are no longer connected to ORACLE. SYS@xe>
This would never be possible without a SYSDBA connection as you would hit an ORA-01940 as demonstrated above.
I’m doing a lot of work with privileges at the moment and this seemed worth commenting on.
CLUSTER_DATABASE=FALSE
I recently encountered a procedure that stated:
Set CLUSTER_DATABASE=FALSE
Set CLUSTER_DATABASE_INSTANCES=1
At that point I stopped for a second to wonder if the second parameter was required… It seems logical that if the database is not in a cluster then you wouldn’t need to tell Oracle how many database are in the cluster…
I didn’t have opportunity to test it at the time, but a SEV 1 a few days later put me in a position of attempting to start a RAC database in single instance mode and give me the perfect chance to find out…
The database in question was running on 9.2.0.8, but showed the same behaviour as the output below (generated on version 11.1.0.7):
19:05:33 SYS@erac1> show parameter cluster NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean TRUE cluster_database_instances integer 2 cluster_interconnects string 19:05:42 SYS@erac1> alter system set cluster_database = false scope = spfile; System altered. Elapsed: 00:00:00.12 19:06:03 SYS@erac1> startup force ORACLE instance started. Total System Global Area 509411328 bytes Fixed Size 2161152 bytes Variable Size 322962944 bytes Database Buffers 180355072 bytes Redo Buffers 3932160 bytes Database mounted. Database opened. 19:08:44 SYS@erac1> show parameter cluster NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean FALSE cluster_database_instances integer 1 cluster_interconnects string 19:09:00 SYS@erac1>
I’d learnt something new and as the rest of the team was surprised by the behaviour it seems something worth sharing… I’m all for removing redundant commands.
Oracle OpenWorld – First Keynote
At the end of a busy first day at Oracle OpenWorld 2009 I took myself along to the first keynote presentation “Extreme Innovation”. I wasn’t sure if it would be worth going to what is clearly focused on marketing and customer relations rather than technical education, but it was…
The number of people, size of the screens and general buzz in the room was something worth experiencing and that’s before anyone even started talking. You can watch highlights on the Oracle site. It is hard to not marvel at how Larry Ellison has grown Oracle and for all the chatter to the contrary it does appear that Oracle a full committed to SPARC, Solaris, Java and MySQL.
11gR2 Installed and Looking Good
Just a quick post to express my excitement about having 11gR2 up and running on a 8 disk ASM configuration including an ACFS mount point… Some people play with their Wii, I’m happy with an Oracle database and *nix OS!
Is that so wrong?
I particularly like the new installer, but then again I’ve only just started by 11gR2 experience… 
Grid Control with ASM
A colleague recently hit a problem configuring Grid Control with a repository database that uses ASM.
You do not have sufficient privilege to write to the specified patch for tablespaces
He checked out MetaLink or, as I should say, “My Oracle Support” and found note 738445.1
… Now, I don’t know if I’m misunderstanding what it is suggesting, but an alternative solution that I have used previously is to specify a file system location for both the management tablespaces, then move them to ASM using RMAN after they have been created.
In summary:
- Allocate sufficient temporary storage (referred to as the “staging” area) for the Grid Control specific tablespaces (2GB should be more than enough judging by the size of these tablespaces in my current installation)
- Install Grid Control (I followed Jeff Hunter’s excellent instructions) specifying a location on the “staging” filesystem for the Grid Control tablespaces
- Post installation, shutdown the OMS using (assuming ORACLE_HOME set to OMS home):
- Use RMAN to backup “as copy” the relevant tablespaces (MGMT_TABLESPACE and MGMT_ECM_DEPOT_TS) with format ‘+DATA’ (or other disk group as applicable), i.e.:
- Take relevant tablespaces offline
- Switch tablespaces to copy using RMAN
- Recover tablespaces
- Bring tablespaces back online
- Start the OMS processes (assuming ORACLE_HOME set to OMS home)
$ $ORACLE_HOME/opmn/bin/opmnctl stopall
RMAN> backup as copy tablespace MGMT_TABLESPACE format '+DATA';
RMAN> backup as copy tablespace MGMT_ECM_DEPOT_TS format '+DATA';
RMAN> sql 'alter tablespace MGMT_TABLESPACE offline';
RMAN> sql 'alter tablespace MGMT_ECM_DEPOT_TS offline';
RMAN> switch tablespace MGMT_TABLESPACE to copy;
RMAN> switch tablespace MGMT_ECM_DEPOT_TS to copy;
RMAN> recover tablespace MGMT_TABLESPACE;
RMAN> recover tablespace MGMT_ECM_DEPOT_TS;
RMAN> sql 'alter tablespace MGMT_TABLESPACE online';
RMAN> sql 'alter tablespace MGMT_ECM_DEPOT_TS online';
$ $ORACLE_HOME/opmn/bin/opmnctl startall
Now that really has got to be easier than (only edited highlights because repoducing MetaLink/My Oracle Support Documentation is not going to win me friends at Oracle:
Do not install grid console on the local machine on which ASM instance exist.
Then
After successful installation of grid control console, install additional OMS on local machine where the ASM is installed.
Maybe I missed something that is RAC specific, but the note does state:
Same problem occurs for
+ Multiple node RAC Database.
+ Single node RAC Database.
+ Single instance Database.
With RAC the logical option would be to use a clustered filesystem as the tablespace staging area, but I can’t see why dropping to a single node RAC configuration in order to get Grid Control setup and then moving back to multi-node would not work…
Anyone out there with other experiences of this?
Microsoft Shortcuts
I few weeks ago a colleague showed me the shortcut I’ve been hoping to find for many years:
Application: Microsoft Word
Context: Select text
Shortcut: shift + f3
… Watch the selected text cycle through uppercase, lowercase and init caps.
The conversation moved on to other useful shortcuts…
Application: Microsoft Excel
Context: Focus in cell
Shortcut: ctrl + ‘
… Copy the value from the cell above.
The final one that pleased a few colleagues is the fast way to lock your computer:
Windows + L
I hope these prove useful to readers.
Purging Audit Data
When looking to implement something to purge the audit data after 90 days from some Oracle databases I found a nice blog post from Alex Gorbachev. The post provides a procedure for keeping X days of records in SYS.AUD$ and deleting anything older. It also provides an example for submitting a scheduled job via DBMS_SCHEDULER so that the purging of the data can be set up, and hopefully largely forgotten about.
I wanted to take this a little further and create a user specifically for the purpose of purging the SYS.AUD$ and most importantly I wanted the user to be locked down as much as possible. Here is what I came up with…
As SYSTEM:
create user purge_aud identified by {appropriately complex password};
grant create session, create job, create procedure to purge_aud;
As SYS:
grant delete on sys.aud$ to purge_aud;
grant execute on sys.dbms_system to purge_aud;
I feel that I should explain why I didn’t just run all the above as SYS. Well, the logic is that if I have the parameter AUDIT_SYS_OPERATIONS set to TRUE, I believe is is wise to do this, then every command that is run as SYS will be logged in an audit file in AUDIT_FILE_DEST. I don’t want to create more audit data than I need to, so therefore try to limit the commands that I run as SYS. In this case the creation of a user and the granting of system privileges can be handled by SYSTEM – I can also see value in either having a user specifically to create new users or having individual accounts for the DBAs so that they can create users using an account that should only ever be used by a named individual.
Maybe I don’t need to point this out, but performing the object level grants as a user other than SYS would require me giving the same privilege I want to grant to PURGE_AUD to another user with the “WITH GRANT” option, which to me is not appropriate and doesn’t actually help. I would say that as a general rule I want the object owner to be the user granting the privileges on that object.
As I final note on this section, I have not named my user “PURGE_AUD” in reality on the basis that it would feel a little too obvious what the purpose of the user is and would potentially make it a target for the bad guys/girls!
The next step was to create the PURGE_AUDIT_TRAIL procedure in the schema of the PURGE_AUD user. I won’t repoduce Alex’s code here, but note that I used fully qualified object names, i.e. sys.aud$ and sys.dbms_system. Once the procedure was created I then scheduled the job as specified by Alex, but with the addition of “SYS.” before the schedule_name parameter and using 90 as the parameter value for PURGE_AUDIT_TRAIL. There would also be the option of creating synonyms, but I prefer to keep it simple and to me more objects in increased complexity.
At this point we have a user with pretty tight privileges and a scheduled job to run each day and clear out any audit data older than 90 days. I wanted to lock this user right down and remove any privileges that were no longer needed…
As SYSTEM:
alter user purge_aud account lock;
revoke create session, create job, create procedure from purge_aud;
The above highlights something that I have the impression is often overlooked: A user might need a certain set of privileges when being “setup”, but it is highly likely that the user will not need that same set of privileges for its entire life.
The scheduled job is not impeded by revoking any of the above privileges and the job will continue to run each day in the “maintenance window”. In the unlikely event that anything needs to be changed it will simply be a case of granting the required privilege(s) to PURGE_AUD, making the change and then revoke the privilege(s) previously granted specifically for the change… A little extra work I admit, but I’d say a fair price for improved security.
It has been commented that auditors might not like the granting of execute on DBMS_SYSTEM to the PURGE_AUD user, but if that is the case for you then there is the option of not writing to the alert log and cutting this bit out of the PURGE_AUDIT_TRAIL procedure.
Finally, this maybe obvious to most readers, but the text will only be written to the alert log of the instance running the scheduled job in a RAC environment.
Understanding EXPIRY_DATE
It is quite a shocking fact that all the databases I have ever worked on have had areas of security that would benefit from a few simple changes, but without fail there has always been a “decision maker” above me that has been too concerned about the unknown and too complacent about security to agree to some testing that would allow some simple best practices to be applied.
So when I was asked to implement some security profiles for the Oracle users on the databases I’m currently supporting I was keen to get started…
This was pretty simple stuff. We wanted password expiry, but didn’t see the need for grace times; password complexity rules and life time that matched the company standards. And, that was about it.
I created a password function, profile and user in a test system and satisfied myself that everything worked as I expected. There was one horror story regarding profiles from the other members of the team, but the people involved couldn’t remember the detail. The general gist of the problem was the profiles had been applied and all the accounts expired immediately. Which didn’t match what I’d observed, as described below.
When the user was initially created with default profile DBA_USERS.EXPIRY_DATE was null. When the profile was applied DBA_USERS.EXPIRY_DATE was showed a value X days in the future. I’m a little embarassed to say that that is where my testing finished. I had envisaged two possible scenarios: 1) the account would be expired immediately; 2) the account would be set to expiry X days in the future… I’ll put it down to time pressures, but it had never occurred to me that Oracle was keeping a track of when password were changed. After all it wasn’t a field in DBA_USERS. Doh!
Anyway, when implemented, which was communicated to the end users, there were a few calls from people stating that their password had expired and they couldn’t change it – the application they use relies on Oracle authentication, but does not allow password changes directly from the application. Not the best design.
After telling a few end-users how to change their password via SQL*Plus, all the time wondering what had gone wrong, I was able to get back to the next most pressing task.
I was intrigued. Why were accounts expiring that I thought wouldn’t? It didn’t take me long to find the answer and I kicked myself for not digging deeper sooner.
I’d assumed that the expiry_date was set rather than derived. The view definiton for DBA_USERS gives it all away…
select u.name, u.user#, u.password,
m.status,
decode(u.astatus, 4, u.ltime,
5, u.ltime,
6, u.ltime,
8, u.ltime,
9, u.ltime,
10, u.ltime, to_date(NULL)),
decode(u.astatus,
1, u.exptime,
2, u.exptime,
5, u.exptime,
6, u.exptime,
9, u.exptime,
10, u.exptime,
decode(u.ptime, '', to_date(NULL),
decode(pr.limit#, 2147483647, to_date(NULL),
decode(pr.limit#, 0,
decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
dp.limit#/86400),
u.ptime + pr.limit#/86400)))),
dts.name, tts.name, u.ctime, p.name,
nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'),
u.ext_username
from sys.user$ u left outer join sys.resource_group_mapping$ cgm
on (cgm.attribute = 'ORACLE_USER' and cgm.status = 'ACTIVE' and
cgm.value = u.name),
sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
where u.datats# = dts.ts#
and u.resource$ = p.profile#
and u.tempts# = tts.ts#
and u.astatus = m.status#
and u.type# = 1
and u.resource$ = pr.profile#
and dp.profile# = 0
and dp.type#=1
and dp.resource#=1
and pr.type# = 1
and pr.resource# = 1
SYS.USER$.PTIME was the field I hadn’t know about, but will not forget…
I feel happier now I understand where user expiry dates come from and the more I think about it the more it makes sense. After all it wouldn’t be good design if Oracle needed to update a table holding user data every time a profile was changed.
Backup From Physical Standby
The subject of taking backups from an Oracle Physical Standby database is something that has cropped up in conversation at work for me a few times over the past 6 months. It was something I had read about rather than done myself, and 2 of my colleagues reported trying, but without successfully restoring. I wanted to get my hands dirty and have a try…
The only problem was getting into a position where I could test. There was no opportunity at work and I had my sights set on something else at home. Well, that something else left me with an Oracle VM Server and Oracle VM Manager setup that would allow me to set up a Data Guard test environment with ease.
Anyway, what I ended up with was a bit much for a blog post, so I thought a PDF was in order… Backup From Physical Standby
Martin Nash
Neil Johnson
Pawel Krol
2 comments