Tag Archives: SQL*Plus

Shutdown/Startup vs. Alter Statement – 12c Edition

And most of these new commands are available via a simple right-click in the current release of Oracle’s SQLDeveloper (DBA Menu).

When everyone finally moves to Oracle pluggable databases, one thing to remember is that stopping and starting a database is not going to be as simple as it use to be.  Shutting down and starting up a database is done simply with the following commands:

shutdown normal
shutdown immediate
shutdown abort
startup
startup nomount
startup mount

If we use one of these traditional shutdown methods at the container database (CDB) layer, we will bring down the CDB and the associated PDBs under it. This is something to be aware of when looking to reboot a container databases.  Lets take a look at bringing down a CDB.

[oracle@oel ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 21 21:42:53 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real…

View original post 687 more words

Advertisements

Changing ORACLE_BASE in Oracle 11gR2 (11.2.0.3.4) Database

During a routine installation, I managed to mis-type the filesystem path for the ORACLE_BASE during execution of the Oracle Universal Installer (./runInstaller).

[nb: ORACLE_BASE by normal definition should be a standard starting filesystem mountpoint under which all other instance-specific data/files for this ORACLE_HOME would be found. Typically contains your diagnostic logs, Fast Recovery Area files, all configuration tool logs, and any Flashback checkpoint saves.

$ORACLE_BASE/diag/.

./asm
./clients
./crs
./diagtool
./lsnrctl
./netcman
./ofm
./rdbms
./tnslsnr

$ORACLE_BASE/admin/<SID>/.

./adump          # Audit data./dpdump       # Data Pump related
./pfile               # init.ora files generated by config tools

–end of note]

Since the service account (oracle) had filesystem permissions to do so, it went ahead and put all of the flashback recovery, diagnostics, and audit trail files (and my backup controlfiles) into the incorrectly named directory.  The database itself was running, but how to correct this without starting all over again?

Start with backing up your init.ora configuration from spfile to pfile format:

SQL> create pfile from spfile;

File created.

(this defaults to $ORACLE_HOME/dbs/init.ora)

Then shutdown services:

SQL> shutdown immediate;

Database closed.

SQL> exit;

#oracle> lsnrctl stop

Now go find all the places you’ll need to modify/correct the ORACLE_BASE filepath declarations:

$ORACLE_HOME/dbs/init<SID>.ora

$ORACLE_HOME/network/admin (or $TNS_ADMIN)

  • listener.ora: ADR_BASE_LISTENER
  • sqlnet.ora: ADR_BASE
    (Note: these two are auto-generated using netca – the Oracle Net Configuration Assistant, if you execute that after making the database and environment-side changes.

$ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml : ORACLE_BASE

All finished editing? Start everything back up:

#oracle> lsnrctl start

SQL> startup pfile=?/dbs/init<SID>.ora  — to use revised settings

SQL> create spfile from pfile;       — copy new settings to spfile

SQL> shutdown immediate;

SQL> startup;                                — uses spfile instead

Now future installs using dbca, dbua and netca should recognize your defined/modified/corrected ORACLE_BASE.