Change from global names to non-global names

Had some requests from friends on how to change the global names, there are lots of other posts on this same topic. Here’s another.

If you started up your database with global_names=true and want to change, here is how to do this.

Perform the following tasks.

Example – Global names is using “dbname.dbdomainname.com” as the DB_NAME and DB_DOMAIN combination, non-global names would use “dbname”, with no DB_DOMAIN.

Open up a command tool and Log in as sysdba or SYS.
cmd> sqlplus / as sysdba

Create the file initDB_NAMETEMP.ora (where DB_NAME is your DBNAME).
sql> create pfile=’initTHESHOWTEMP.ora’ from spfile;

Shutdown the database.
sql> shutdown immediate

Keep the command tool open, you’ll jump back to it later.

Now edit the initDB_NAMETEMP.ora file … C:\app\oracle\product\11.2.0\server\database\initDB_NAMETEMP.ora (or $ORACLE_HOME/dbs).

Remove the line:
db_domain=’dbdomainname.com’

Change the line with the *.global_names to false
*.global_name=FALSE

Save the file initDB_NAMETEMP.ora

Back to the command tool from earlier:

create spfile from pfile=’initTHESHOWTEMP.ora’;

Startup the database.
sql> startup;

sql> show parameter db_domain
Check db_domain – should be null.

sql> show parameter global_name
Check global_names – should be false.

sql> show parameter service_name
Check the service name to be sure it matches the db_name. This is just an extra check.

You can run the listener status from the command line to verify the database is correctly registered:
cmd&gt lsnrctl status

Look for the db_name without the domain name

Change any tnsnames.ora files to match accordingly.

Test with tnsping
tnsping db_name

Try to connect, and you’re done.