You can issue the following SQL statement to get a list of all deprecated initialization parameters:

SQL> SELECT name FROM v$parameter WHERE isdeprecated = 'TRUE';

SQL> select name, isspecified from v$obsolete_parameter;

-- On Primary and standby

SQL> shutdown immediate

SQL> startup mount

-- On Standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- On primary and standby

ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('/oracle/oradata/ORCL/stbyredo14a.log', '/oracle/oradata/ORCL/stbyredo14b.log') SIZE 512M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 ('/oracle/oradata/ORCL/stbyredo15a.log', '/oracle/oradata/ORCL/stbyredo15b.log') SIZE 512M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 ('/oracle/oradata/ORCL/stbyredo16a.log', '/oracle/oradata/ORCL/stbyredo16b.log') SIZE 512M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 17 ('/oracle/oradata/ORCL/stbyredo17a.log', '/oracle/oradata/ORCL/stbyredo17b.log') SIZE 512M;

-- On Primary and standby

ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;

-- On Standby

SQL> shutdown immediate

-- On Primary

ALTER DATABASE ADD LOGFILE GROUP 11 ('/oracle/oradata/ORCL/stbyredo11a.log', '/oracle/oradata/ORCL/stbyredo11b.log') SIZE 512M;

ALTER DATABASE ADD LOGFILE GROUP 12 ('/oracle/oradata/ORCL/stbyredo12a.log', '/oracle/oradata/ORCL/stbyredo12b.log') SIZE 512M;

ALTER DATABASE ADD LOGFILE GROUP 13 ('/oracle/oradata/ORCL/stbyredo13a.log', '/oracle/oradata/ORCL/stbyredo13b.log') SIZE 512M;

SQL> alter database open;

ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE DROP LOGFILE GROUP 2;

ALTER DATABASE DROP LOGFILE GROUP 3;

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/orclstby_cntrl01.ctl';

- Copy STANDBY CONTROLFILE to standby site

#scp /tmp/orclstby_cntrl01.ctl server2:/tmp/orclstby_cntrl01.ctl

-- On Standby

SQL> startup mount

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

- Restart DBs on Primary and Standby sites

QUICK GUIDE

old primary site
SQL>select switchover_status from v$database;
SQL>alter database commit to switchover to physical standby with session shutdown;
SQL>shutdown immediate
SQL>startup nomount
SQL>alter database mount standby database;
SQL>alter system set log_archive_dest_state_2=defer;
old standby site
SQL>select switchover_status from v$database;
SQL>alter database commit to switchover to primary;
SQL>shutdown immediate
SQL>startup
old primary site
SQL>recover managed standby database disconnect


EXPLANATIONS

1. Verify that it is possible to perform a switchover operation.

On the primary query the switchover_status column of v$database to verify that
switchover to standby is possible.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

------------------

TO STANDBY

In order to perform a switchover all sessions to the database need to be disconnected. This process has been automated with the “with session shutdown” clause that has been added to the alter database commit to switchover command.
If SWITCHOVER_STATUS returns SESSIONS ACTIVE then you should either disconnect all sessions manually or when performing step 2 you should append the “with session shutdown” clause.

For example:

SQL> alter database commit to switchover to standby with session shutdown;

Note that the clause also works with the switchover to primary command.

The SWITCHOVER_STATUS column of v$database can have the following values:

NOT ALLOWED - Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.

SESSIONS ACTIVE - Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.

SWITCHOVER PENDING - This is a standby database and the primary database switchover request has been received but not processed.

SWITCHOVER LATENT - The switchover was in pending mode, but did not complete and went back to the primary database.

TO PRIMARY - This is a standby database, with no active sessions, that is allowed to switch over to a primary database.

TO STANDBY - This is a primary database, with no active sessions, that is allowed to switch over to a standby database.

RECOVERY NEEDED - This is a standby database that has not received the switchover request.

During normal operations it is acceptable to see the following values for
SWITCHOVER_STATUS on the primary to be SESSIONS ACTIVE or TO STANDBY.

During normal operations on the standby it is acceptable to see the values of
NOT ALLOWED or SESSIONS ACTIVE.

2. Convert the primary database to the new standby:

SQL> alter database commit to switchover to physical standby ;

Database altered.

3. Shutdown the former primary and mount as a standby database:

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 85020944 bytes

Fixed Size 454928 bytes

Variable Size 71303168 bytes

Database Buffers 12582912 bytes

Redo Buffers 679936 bytes

SQL> alter database mount standby database;

Database altered.

4. Defer the remote archive destination on the old primary:

SQL> alter system set log_archive_dest_state_3=defer;

5. Verify that the physical standby can be converted to the new primary:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

------------------

SWITCHOVER PENDING

Note that if the status returns SESSIONS ACTIVE then you should append the with session shutdown clause to the command in step 6.

6. Convert the physical standby to the new primary:

SQL> alter database commit to switchover to primary;

Database altered.

7. Shutdown and startup the new primary:

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 85020944 bytes

Fixed Size 454928 bytes

Variable Size 71303168 bytes

Database Buffers 12582912 bytes

Redo Buffers 679936 bytes

Database mounted.

Database opened.

8. Enable remote archiving on the new primary to the new standby:

SQL> alter system set log_archive_dest_state_3=enable;

9. Start managed recover on the new standby database:

SQL> recover managed standby database disconnect;

Media recovery complete.

-----------------------

This article describes how one can change the character set of a database.It should be done with extreme caution having noted the following limitations.The database character set should only be changed if the characters of the code page of the originating database correspond to the same characters of the target database, ie. if the database was created with the characterset US7ASCII and it is to be updated to WE8ISO8859P1. Since these have the same encoding scheme for the first 127 bits, changing the character set from US7ASCII to WE8ISO8859P1 will display all characters up to 127 bits as the same character before and after. In addition, in this particular example, if any characters have been entered with the 8th bit set, then updating the database characterset to 8 bit will allow that 8th bit to be displayed. You must not change the characterset from one encoding scheme to another encoding scheme where the code pages do not correspond. This will completely scramble your database. In addition, if case*designer diagrams are stored in the database, this method must not be used.

Before attempting to run any of the scripts below, you must take a full cold backup of your database. In addition, the procedure must be thoroughly tested before attempting this on a production instance.

Here's a SQL*PLUS script that allows a database's character set to be changed to a different encoding scheme without having to rebuild the database.

set echo off

set verify off

The data dictionary table that records the database

character set is sys.props$

SQL> describe sys.props$

Name Null? Type

------------------------------- -------- ----

NAME NOT NULL VARCHAR2(30)

VALUE$ VARCHAR2(2000)

COMMENT$ VARCHAR2(2000)

For example:

SQL> column c1 format a30

SQL> select name c1, value$ c1 from sys.props$;

C1 C1

------------------------------ ------------------------------

DICT.BASE 2

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_DATE_FORMAT DD-MON-YY

NLS_DATE_LANGUAGE AMERICAN

NLS_CHARACTERSET WE8DEC

NLS_SORT BINARY

GLOBAL_DB_NAME NLSV7.WORLD

NLS_CHARACTERSET can be changed by updating its value, for example:

update sys.props$

set value$ = 'WE8ISO8859P1'

Where name = 'NLS_CHARACTERSET';

The database has to be shutdown and restarted before the change becomes effective.
It is very important to specify the character set name correctly.

IMPORTANT NOTE

=============

If NLS_CHARACTERSET is updated to an invalid value, it will not then be possible to restart the database once it has been shutdown. To recover, it will be necessary to re-create the database, since it cannot be restarted to correct the invalid NLS_CHARACTERSET entry.

The character set name should be in uppercase.

The new value is not effective until the database has been shutdown and restarted. A suggested procedure is as follows, and can be done by running this script from SQL*Plus when logged into the SYSTEM account.

USAGE : SQL> start ch_db.sql

where is the desired database character set

Prompt First check that the character set name is valid.

set echo on

select convert('a','&1','us7ascii') from dual;

set echo off

prompt If this select statement returns error ORA-01482, then the prompt specified character set name is not valid for this installation.
prompt Abort the procedure now with Control-c
prompt To continue, press return
accept ans CHAR
Prompt Check the current value of database character set.

column c1 format a30

select name c1, value$ c1 from sys.props$
where name = 'NLS_CHARACTERSET';

prompt To continue, press return
Prompt Update to new character set

update sys.props$
set value$ = upper('&1')
where name = 'NLS_CHARACTERSET';

set echo off
prompt To continue, press return
accept ans CHAR
Prompt Check the new value of database character set

select name c1, value$ c1 from sys.props$
where name = 'NLS_CHARACTERSET';

Prompt If the value is updated as required, press return to continue and
Prompt then manually type COMMIT; to commit the change. Then shutdown and
Prompt restart the database.
Prompt
Prompt If the value is not updated as required, press return to continue and
Prompt than manually type ROLLBACK; to prevent the change.
prompt To continue, press return
accept ans CHAR

---------------------

sqlplus /as sysdba

alter tablespace INDEX16 offline;

!mv /data1/TTS/oracle/app/oracle/oradata/TTS/index16*.dbf /data2/TTS/oracle/app/oracle/oradata/TTS/

alter tablespace INDEX16 rename datafile '/data1/TTS/oracle/app/oracle/oradata/TTS/index16a.dbf' to '/data2/TTS/oracle/app/oracle/oradata/TTS/index16a.dbf';

alter tablespace INDEX16 rename datafile '/data1/TTS/oracle/app/oracle/oradata/TTS/index16b.dbf' to '/data2/TTS/oracle/app/oracle/oradata/TTS/index16b.dbf';

alter tablespace INDEX16 rename datafile '/data1/TTS/oracle/app/oracle/oradata/TTS/index16c.dbf' to '/data2/TTS/oracle/app/oracle/oradata/TTS/index16c.dbf';

alter tablespace INDEX16 rename datafile '/data1/TTS/oracle/app/oracle/oradata/TTS/index16d.dbf' to '/data2/TTS/oracle/app/oracle/oradata/TTS/index16d.dbf';

alter tablespace INDEX16 rename datafile '/data1/TTS/oracle/app/oracle/oradata/TTS/index16e.dbf' to '/data2/TTS/oracle/app/oracle/oradata/TTS/index16e.dbf';

alter tablespace INDEX16 rename datafile '/data1/TTS/oracle/app/oracle/oradata/TTS/index16f.dbf' to '/data2/TTS/oracle/app/oracle/oradata/TTS/index16f.dbf';

alter tablespace INDEX16 online;

Moving Datafiles while the Instance is Mounted

sqlplus "/ as sysdba"

shutdown immediate

!mv /data1/TTS/oracle/app/oracle/oradata/TTS/index16* /data2/TTS/oracle/app/oracle/oradata/TTS/

startup mount

alter database rename file '/data1/TTS/oracle/app/oracle/oradata/TTS/index16a.dbf' to '/data2/TTS/oracle/app/oracle/oradata/TTS/index16a.dbf';

.... (and for the other datafiles)

Do not disconnect after this step. Stay logged in and proceed to open the database!

alter database open;

---------------------

OSDBA & OSOPER Groups on Unix/Linux
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The 'OSDBA' and 'OSOPER' groups are chosen at installation time and usually both default to the group 'dba'. These groups are compiled into the 'oracle' executable and so are the same for all databases running from a given ORACLE_HOME directory. The actual groups being used for OSDBA and OSOPER can be checked thus:

cd $ORACLE_HOME/rdbms/lib
cat config.[cs]

The line '#define SS_DBA_GRP "group"' should name the chosen OSDBA group.
The line '#define SS_OPER_GRP "group"' should name the chosen OSOPER group.

If you wish to change the OSDBA or OSOPER groups this file needs to be modified either directly or using the installer.

Eg: For an OSDBA group of 'mygroup'

If your platform has config.c (this is the case for HP-UX, Compaq Tru64
Unixware and Linux):

Change: #define SS_DBA_GRP "dba"
to: #define SS_DBA_GRP "mygroup"

If your platform has config.s:
Due to the way different compilers under different architectures generate
assembler code, it's not possible to give a universal rule.

Here are some examples:
Sun SPARC Solaris:
------------------
Change both ocurrences of
.ascii "dba\0"
to
.ascii "mygroup\0"

IBM AIX/Intel Solaris:
----------------------
Change both ocurrences of
.string "dba"
to
.string "mygroup"

To effect any changes to the groups and to be sure you are using the groups defined in this file relink the Oracle executable.Be sure to shutdown all databases before relinking:

Eg:
mv config.o config.o.orig
make -f ins_rdbms.mk ioracle

(Note config.o will be re-created by make because of dependencies automatically)

For a group to be accepted by Oracle as the OSDBA or OSOPER group it must:

- Be compiled into the Oracle executable
- The group name must exist in /etc/group (or in 'ypcat group' if NIS is being used)
- It CANNOT be the group called 'daemon'

Note: The commands above are examples and may vary between platforms.
Note: Some Oracle documentation refers to the ability to define OSDBA and OSOPER roles using group names of the form 'ORA_sid_OSDBA'.
This functionality has not been implemented on Unix (See Bug 224071)

The SID is a site identifier. It plus the Oracle_home are hashed together in Unix to create a unique key name for attaching an SGA. If your Oracle_sid or Oracle_home is not set correctly, you'll get "oracle not available" since we cannot attach to a shared memory segment that is identified by magic key. On NT, we don't use shared memory but the SID is still important. We can have more then 1 database on the same oracle home so we need a way to id them.

Changing it harder then it looks. Here are the steps for changing it (or the database name) under Unix.

PURPOSE

This entry describes how to find and change the "db_name" for a database, or the ORACLE_SID for an instance, without recreating the database.

SCOPE & APPLICATION

For DBAs requiring to either find or change the db_name or ORACLE_SID.

To find the current DB_NAME and ORACLE_SID:

===========================================

Query the views v$database and v$thread.

V$DATABASE gives DB_NAME

V$THREAD gives ORACLE_SID

If ORACLE_SID = DB_SID and db_name = DBNAME:

To find the current value of ORACLE_SID:

SQL> select instance from v$thread;

INSTANCE

----------------

DB_SID

To find the current value of DB_NAME:

SQL> select name from v$database;

NAME

---------

DBNAME

===================================================

1. Shutdown the instance

2. Backup all control, redo, and data files.

3. Go through the .profile, .cshrc, .login, oratab, tnsnames.ora, (for SQL*Net version 2), and redefine the ORACLE_SID environment variable to a new value.

or example, search through disks and do a grep ORACLE_SID *

4. Change locations to the "dbs" directory

% cd $ORACLE_HOME/dbs

and rename the following files:

o init.ora (or use pfile to point to the init file.)

o control file(s). This is optional if you do not rename any

of the controlfiles, and the control_files parameter is used.

The "control_files" parameter is set in the "init.ora" file or in a file it references with the ifile parameter. Make sure that the control_files parameter does not point to old file names, if you have renamed them.

o "crdb.sql" & "crdb2.sql", This is optional. These are only used at database creation.

5. Change locations to the "rdbms/admin" directory

% cd $ORACLE_HOME/rdbms/admin

and rename the file:

o startup.sql. This is optional. On some platforms, this file may be in the "$ORACLE_HOME/rdbms/install" directory. Make sure that the contents of this file do not reference old init.ora files that have been renamed. This file simplifies the "startup exclusive" process to start your database.

6. To rename the database files and redo log files, you would follow the instructions

in .

7. Change the ORACLE_SID environment variable to the new value.

8. Check in the "$ORACLE_HOME/dbs" directory to see if the password file has been enabled. If enabled, the file "orapw" will exist and a new password file for the new SID must be created (renaming the old file will not work). If "orapw" does not exist, skip to step 9. To create a new password file, issue the following command as oracle owner:

orapwd file=orapw password=?? entries=

9. Start up the database and verify that it works. Once you have done this, shutdown the database and take a final backup of all control, redo, and data files.

10. When the instance is started, the control file is updated with the current ORACLE_SID.

Changing the "db_name" for a Database:

======================================

1. Login to Server Manager

% svrmgrl

SVRMGR> connect internal

2. Type

SVRMGR> alter system switch logfile;

to force a checkpoint.

3. Type

SVRMGR> alter database backup controlfile to trace resetlogs;

This will create a trace file containing the "CREATE CONTROLFILE"

command to recreate the controlfile in its current form.

4. Shutdown the database and exit SVRMGR

SVRMGR> shutdown

SVRMGR> exit

The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT.

5. Change locations to the directory where your trace files are located. They are usually in the "$ORACLE_HOME/rdbms/log" directory. If "user_dump_dest" is set in the "init.ora" file, then go to the directory listed in the "user_dump_dest" variable. The trace file will have the form "ora_NNNN.trc with NNNN being a number.

6. Get the "CREATE CONTROLFILE" command from the trace file and put it in a new file called something like "ccf.sql".

7. Edit the "ccf.sql" file

FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" NORESETLOGS ...

TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ...

FROM:

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

TO:

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.

# RECOVER DATABASE USING BACKUP CONTROLFILE

8. Save and exit the "ccf.sql" file

9. Rename the old control files for backup purposes and so that they do not exist when creating the new ones.

10. Edit the "init.ora" file so that db_name="newdb_name" .

11. Login to Server Manager

% svrmgrl

SVRMGR> connect internal

12. Run the "ccf.sql" script

SVRMGR> @ccf

This will issue a startup nomount, and then recreate the controlfile.

If, at this point, you receive the error that a file needs media recovery, the database was not shutdown normally as specified in step 4. You can try recovering the database using the redo in the current logfile, by issuing:

SVRMGRL> recover database using backup controlfile;

This will prompt for an archived redologfile. It may be possible to open the database after applying the current logfile. BUT this is not guaranteed. If, after applying the current logfile, the database will not open then it is highly likely that the operation must be restarted having shutdown the database normally.

To apply the necessary redo, you need to check the online logfiles and apply the one with the same sequence number as reported in the message. This usually is the logfile with status=CURRENT.

To find a list of the online logfiles:

SVRMGR> select group#, seq#, status from v$log;

GROUP# SEQUENCE# STATUS

---------- --------- ----------------

1 123 CURRENT <== this redo needs to be applied

2 124 INACTIVE

3 125 INACTIVE

4 126 INACTIVE

5 127 INACTIVE

6 128 INACTIVE

7 129 INACTIVE

7 rows selected.

SVRMGR> select member

from v$logfile

where GROUP# = 1;

Member

------------------------------------

/u02/oradata/V815/redoV81501.log

The last command in ccf.sql should be:

SVRMGR> alter database open resetlogs;

13. You may also need to change the global database name:

See for further detail.

14. Make sure the database is working.

15. Shutdown and backup the database.