Wednesday, September 28, 2011

Resolving Oracle networking problems..

This simple two part procedure will help to diagnose and fix the most common sqlnet and tnsnames configuration problems.

  • 1. Test communication between the client and the listener

    We will use tnsping to complete this step. It's a common misconception that tnsping tests connectivity to the instance. In actual fact, it only tests connectivity to the listener.

    Here, we will use it to prove that a) the tnsnames.ora has the correct hostname and port, and b) that there is a listener listening on the specified host and port. Run tnsping:

    tnsping <your_tns_entry_name>
    If it is successful you will see something like this:

    [oradev@testerp] $ tnspinng dev

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
    (PROTOCOL = TCP) (HOST = dev)(PORT = 1521))) (CONNECT_DATA =
    (SERVER = DEDICATED) (SERVICE_NAME = scr9)))
    OK (40 msec)

    If not, here are some common errors, and some suggestions for fixing them:

    TNS-03505: Failed to resolve name
    The specified database name was not found in the tnsnames.ora, onames or ldap. This means that tnsping hasn't even got as far as trying to make contact with a server - it simply can't find any record of the database that you are trying to tnsping. Make sure that you've spelled the database name correctly, and that it has an entry in the tnsnames.ora.

    If you have a sqlnet.ora, look at for the setting NAMES.DEFAULT_DOMAIN. If it is set, then all entries in your tnsnames.ora must have a matching domain suffix.

    TNS-12545: Connect failed because target host or object does not exist
    The host specified in the tnsnames is not contactable. Verify that you have spelled the host name correctly. If you have, try pinging the host with 'ping <hostname>'. If ping returns 'unknown host', speak to your network admin. It might be that you have a DNS issue (you could try using the IP address if you have it to hand). If you get 'host unreachable', again speak to your network person, the problem could be down to a routing or firewall issue.

    TNS-12541: TNS:no listener
    The hostname was valid but the listener was not contactable. Things to check are that the tnsnames has the correct port (and hostname) specified, and that the listener is running on the server and using the correct port.

    tnsping hangs for a long time
    I've seen this happen in situations where there is something listening on the host/port - but it isn't an oracle listener. Make sure you have specified the correct port, and that your listener is running. If all looks ok, try doing a 'netstat -ap | grep 1521' (or whatever port you are using) to find out what program is listening on that port.

  • 2. Attempt a connection to the instance

    Once you have proven that the tnsnames is talking to the listener properly, the next step is to attempt a full connection to the instance. To do this we.ll use sqlplus:

    sqlplus [username]/[password]@<your_tns_entry_name>
    If it works you will successfully log into the instance. If not, here are some common errors:

    ORA-01017: invalid username/password; logon denied
    This is actually a good error in these circumstances! Even though you didn't use the correct username or password, you must have successfully made contact with the instance.

    ORA-12505: TNS:listener does not currently know of SID given in connect
    Either the SID is misspelled in the tnsnames, or the listener isn't listening for it. Check the tnsnames.ora first. If it looks ok, do a 'lsnrctl status' on your server, to see what databases the listener is listening for.

    ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
    This is quite a common error and it means that, while the listener was contactable, the database (or rather the service) specified in the tnsnames wasn't one of the things that it was listening out for.

    Begin by looking at your tnsnames.ora. In it, you will a setting like SERVICE_NAME=<name>.

    If you are running a single instance database (ie. not RAC), and you are sure that you are not using services, it might be easier to change SERVICE_NAME= to SID= in your tnsnames. Using service names is the more modern way of doing things, and it does have benefits, but SID still works perfectly well (for now anyway).

    If you would prefer to continue using service names, you must first check that you have not misspelled the service name in your tnsnames. If it looks alright, next check that the listener is listening for the service. Do this by running 'lsnrctl services' on your server. If there isn't an entry for your service, you need to make sure that the service_names parameter is set correctly on the database.

Sukhwinder Singh
Apps DBA

Converting from Rollback to Undo

This procedure will drop the existing rollback segments and tablespace, and replace them with an auto-managed undo tablespace.

  • 1. Identify the rollback segments and tablespaces to be removed:
    select segment_name
    , tablespace_name
    , status
    from dba_rollback_segs
    where tablespace_name != 'SYSTEM'
    /
  • 2. Produce offline and drop statements for each rollback segment:
    select 'alter rollback segment ' || segment_name || ' offline;'
    from dba_rollback_segs
    where tablespace_name = '&TS'
    and tablespace_name != 'SYSTEM'
    /

    select 'drop rollback segment ' || segment_name || ';'
    from dba_rollback_segs
    where tablespace_name = '&TS'
    and tablespace_name != 'SYSTEM'
    /
    Review the output and, if you are happy with it, cut and paste it back into sqlplus.

  • 3. Make a note of the old tablespace's size and location, then drop it.
    drop tablespace <tablespace_name> including contents and datafiles
    /
  • 4. Create a new 'undo' tablespace in place of the old one.
    create undo tablespace undo
    datafile '<path/file>' size <size>
    extent management local
    /
  • 5. Update the initialisation parameters
    If you are using a spfile (and you should be!) run the following commands:
    alter system reset rollback_segments scope=spfile sid='*';
    alter system set undo_management=auto scope=spfile;
    alter system set undo_tablespace=undo scope=spfile;
     If you are still using a pfile (init.ora) then do the following:
    Remove the following settings:
    rollback_segments=...
    Add/alter these two lines:
    undo_management=auto
    undo_tablespace=undo
  • 6. Restart the instance
    Note the location of the alert log. If there is a problem during start-up, you will probably receive nothing more than a message saying 'instance aborted'. The alert log will contain a slightly more detailed explanation.

    Restart the instance:
    shutdown immediate
    startup
    Rerun the query from step 1. There will be a whole load of rollback segments with names like '_SYSSMU1$'. This is an indictaion that the instance is now using auto undo. 

Sukhwinder Singh
Apps DBA

Backup - RMAN..

    * Create a catalog
    * Register a database
    * Un-register a database
    * Reset the catalog after a restlogs on the target
    * Resync the catalog with the target controlfile
    * Delete a backup
    * Backup a database
    * Restore/recover a database
    * Show the controlfile backup record
    * Misc commands

Create a catalog
create a tablespace
create a user
grant connect, resource, recovery_catalog_owner to user

rman catalog user/pass@db
create catalog tablespace "<tablespace_name>";
 
Note. <tablespace_name> is case sensitive (i.e. it must be uppercase)
Note. If you get the error 'rman: can't open catalog', make sure that oracle's rman is being run (which rman). X11 also has a command called rman. Rename it if necessary.
Register a database
Note. ensure the target db has a password file
rman catalog user/pass@rmandb target user/pass@db
register database;
Un-register a database
sqlplus user/pass@rmandb
select * from rc_database;
select db_key, db_id from db;

execute dbms_rcvcat.unregisterdatabase(<db_key>, <db_id>);
Reset the catalog after a restlogs on the target
reset database;
Resync the catalog with the target controlfile
resync catalog;
Delete a backup
allocate channel... delete backuppiece <number>; 
release channel; 
Backup a database
backup database plus archivelog format '/u01/ora_backup/rman/%d_%u_%s';

run {
allocate channel t1 type disk;
backup current controlfile format '/u01/ora_backup/rman/%d_%u_%s';
backup database format '/u01/ora_backup/rman/%d_%u_%s';
backup archivelog all delete input format '/u01/ora_backup/rman/arch_%d_%u_%s';
release channel t1;
}

run {
allocate channel t1 type disk;
backup archivelog all delete input format '/u01/ora_backup/rman/arch_%d_%u_%s';
release channel t1;
}
 
Cold backup (archivelog or noarchivelog mode)
run {
allocate channel t1 type disk;
shutdown immediate;
startup mount;
backup database include current controlfile format '/u01/ora_backup/rman/%d_%u_%s';
alter database open;
}

run {
allocate channel t1 type disk;
backup archivelog all delete input;
}
Restore/recover a database
Full restore and recovery
startup nomount; 
run { allocate channel t1 type disk; 
allocate channel t2 type disk; 
allocate channel t3 type disk; 
allocate channel t4 type disk; 
restore controlfile; 
restore archivelog all; 
alter database mount; 
restore database; 
recover database; 

sql 'alter database open resetlogs';

Restore and roll forward to a point in time
startup nomount; 
run { set until time ="to_date('30/08/2006 12:00','dd/mm/yyyy hh24:mi')"; 
allocate channel t1 type disk; 
allocate channel t2 type disk; 
allocate channel t3 type disk; 
allocate channel t4 type disk; 
restore controlfile; 
restore archivelog all; 
alter database mount; 
restore database; 
recover database; 
sql 'alter database open resetlogs';

If the archive logs are already in place:
startup mount; 
run { set until time ="to_date('08/02/2007 14:00','dd/mm/yyyy hh24:mi')"; 
allocate channel t1 type disk; 
allocate channel t2 type disk; 
allocate channel t3 type disk; 
allocate channel t4 type disk; 
restore database; 
recover database; 
sql 'alter database open resetlogs';

startup mount; 
run { allocate channel t1 type disk; 
recover database; 
}
Show the control file backup record
set pages 999 lines 100 
col name format a60 
break on set_stamp skip 1 

select set_stamp 
, to_char(ba.completion_time, 'HH24:MI DD/MM/YY') finish_time 
, df.name 
from v$datafile df 
, v$backup_datafile ba 
where df.file# = ba.file# 
and ba.file# != 0 
order by set_stamp, ba.file# 
/
Misc commands
list backupset; 
list backup of database; 
list backup of archivelog all; 
report obsolete; 
report obsolete redundancy = 2; 
delete obsolete; - remove unneeded backups 
restore database validate; - check the backup 
report unrecoverable; 
report schema; - show current db files 
crosscheck backup; - make sure the backups in the catalog still physically exist 
delete expired backup; - delete epired backups found by crosscheck 

rman target sys/*****@scr10 catalog rman/rman@dbarep 
LIST BACKUPSET OF DATABASE; 
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK; 
DELETE OBSOLETE REDUNDANCY = 4 device type disk; 
delete obsolete REDUNDANCY = 2 device type disk;

Delete archive log older than...
DELETE NOPROMPT ARCHIVELOG UNTIL TIME "SYSDATE-5" 

Crosscheck the available archivelogs (fixes RMAN-06059)
change archivelog all crosscheck;


Sukhwinder Singh
Apps DBA

Clone an Oracle database using RMAN duplicate (same server)..

This procedure will clone a database onto the same server using RMAN duplicate.
  • 1. Backup the source database.
    To use RMAN duplicate an RMAN backup of the source database is required. If there is already one available, skip to step 2. If not, here is a quick example of how to produce an RMAN backup. This example assumes that there is no recovery catalog available:
    rman target sys@<source database> nocatalogbackup database plus archivelog format '/u01/ora_backup/rman/%d_%u_%s';
     This will backup the database and archive logs. The format string defines the location of the backup files. Alter it to a suitable location.

  • 2. Produce a pfile for the new database
    This step assumes that the source database is using a spfile. If that is not the case, simply make a copy the existing pfile.

    Connect to the source database as sysdba and run the following:
    create pfile='init<new database sid>.ora' from spfile;
    This will create a new pfile in the $ORACLE_HOME/dbs directory.

    The new pfile will need to be edited immediately. If the cloned database is to have a different name to the source, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary.

    Because in this example the cloned database will reside on the same machine as the source, Oracle must be told how convert the filenames during the RMAN duplicate operation. This is achieved by adding the following lines to the newly created pfile:
    db_file_name_convert=(<source_db_path>,<target_db_path>)
    log_file_name_convert=(<source_db_path>,<target_db_path>)
    Here is an example where the source database scr9 is being cloned to dg9a. Note the trailing slashes and lack of quotes:
    db_file_name_convert=(/u01/oradata/scr9/,/u03/oradata/dg9a/)
    log_file_name_convert=(/u01/oradata/scr9/,/u03/oradata/dg9a/)

  • 3. Create bdump, udump & cdump directories
    Create bdump, udump & cdump directories as specified in the pfile from the previous step.

  • 4. Add a new entry to oratab, and source the environment
    Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.

    Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
    echo $ORACLE_SID
    If this doesn't output the new database sid go back and investigate why not.

  • 5. Create a password file
    Use the following command to create a password file (add an appropriate password to the end of it):
    orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=<your password>
  • 6. Duplicate the database
    From sqlplus, start the instance up in nomount mode:
    startup nomount
    Exit sqlplus, start RMAN and duplicate the database. As in step 1, it is assumed that no recovery catalog is available. If one is available, simply amend the RMAN command to include it.
    rman target sys@<source_database> nocatalog auxiliary /

    duplicate target database to <clone database name>;
    This will restore the database and apply some archive logs. It can appear to hang at the end sometimes. Just give it time - I think it is because RMAN does a 'shutdown normal'.

    If you see the following error, it is probably due to the file_name_convert settings being wrong. Return to step 2 and double check the settings.
    RMAN-05001: auxiliary filename '%s' conflicts with a file used by the target database
    Once the duplicate has finished RMAN will display a message similar to this:
    database opened
    Finished Duplicate Db at 26-FEB-11

    RMAN> exit RMAN.

  • 7. Create an spfile
    From sqlplus:
    create spfile from pfile;

    shutdown immediate

    startup

    Now that the clone is built, we no longer need the file_name_convert settings:
    alter system reset db_file_name_convert scope=spfile sid='*'
    /

    alter system reset log_file_name_convert scope=spfile sid='*'
    /
  • 8. Optionally take the clone database out of archive log mode
    RMAN will leave the cloned database in archive log mode. If archive log mode isn't required, run the following commands from sqlplus:
    shutdown immediate
    startup mount
    alter database noarchivelog;
    alter database open;
  • 9. Configure TNS
    Add entries for new database in the listener.ora and tnsnames.ora as necessary.


Sukhwinder singh
Apps DBA

Clone an Oracle database using a cold backup..

This procedure will clone a database using a cold copy of the source database files. If a cold backup of the database is available, restore it to the new location and jump to step 2.

  • 1. Identify and copy the database files
    With the source database started, identify all of the database's files. The following query will display all datafiles, tempfiles and redo logs:
    set lines 100 pages 999
    col name format a50
    select name, bytes
    from (select name, bytes
    from v$datafile
    union all
    select name, bytes
    from v$tempfile
    union all
    select lf.member "name", l.bytes
    from v$logfile lf
    , v$log l
    where lf.group# = l.group#) used
    , (select sum(bytes) as poo
    from dba_free_space) free
    /
    Make sure that the clone databases file-system is large enough and has all necessary directories. If the source database has a complex file structure, you might want to consider modifying the above sql to produce a file copy script.

    Stop the source database with:
    shutdown immediate
    Copy, scp or ftp the files from the source database/machine to the target. Do not copy the control files across. Make sure that the files have the correct permissions and ownership.

    Start the source database up again
    startup
  • 2. Produce a pfile for the new database
    This step assumes that you are using a spfile. If you are not, just copy the existing pfile.

    From sqlplus:
    create pfile='init<new database sid>.ora' from spfile;
    This will create a new pfile in the $ORACLE_HOME/dbs directory.

    Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.

    Note. Pay particular attention to the control locations.

  • 3. Create the clone controlfile
    Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:
    alter database backup controlfile to trace as '/home/oracle/cr_<new sid>.sql'
    /
    The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:

    • Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).
    • Remove any lines that start with --
    • Remove any lines that start with a #
    • Remove any blank lines in the 'CREATE CONTROLFILE' section.
    • Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'
    • Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.
    • If the file paths are being changed, alter the file to reflect the changes.

    Here is an example of how the file would look for a small database called dg9a which isn't in archivelog mode:
    STARTUP NOMOUNT
    CREATE CONTROLFILE SET DATABASE "DG9A" RESETLOGS FORCE LOGGING NOARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 453
    LOGFILE
    GROUP 1 '/u03/oradata/dg9a/redo01.log' SIZE 100M,
    GROUP 2 '/u03/oradata/dg9a/redo02.log' SIZE 100M,
    GROUP 3 '/u03/oradata/dg9a/redo03.log' SIZE 100M
    DATAFILE
    '/u03/oradata/dg9a/system01.dbf',
    '/u03/oradata/dg9a/undotbs01.dbf',
    '/u03/oradata/dg9a/cwmlite01.dbf',
    '/u03/oradata/dg9a/drsys01.dbf',
    '/u03/oradata/dg9a/example01.dbf',
    '/u03/oradata/dg9a/indx01.dbf',
    '/u03/oradata/dg9a/odm01.dbf',
    '/u03/oradata/dg9a/tools01.dbf',
    '/u03/oradata/dg9a/users01.dbf',
    '/u03/oradata/dg9a/xdb01.dbf',
    '/u03/oradata/dg9a/andy01.dbf',
    '/u03/oradata/dg9a/psstats01.dbf',
    '/u03/oradata/dg9a/planner01.dbf'
    CHARACTER SET WE8ISO8859P1
    ;

    ALTER DATABASE OPEN RESETLOGS;

    ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oradata/dg9a/temp01.dbf'
    SIZE 104857600 REUSE AUTOEXTEND OFF;
  • 4. Add a new entry to oratab and source the environment
    Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
    Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
    echo $ORACLE_SID
    If this doesn't output the new database sid go back and investigate.

  • 5. Create the a password file
    Use the following command to create a password file (add an appropriate password to the end of it):
    orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=<your password>
  • 5. Create the new control file(s)
    Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:
    sqlplus "/ as sysdba"
    @/home/oracle/cr_<new database sid>
    It is quite common to run into problems at this stage. Here are a couple of common errors and solutions:
    ORA-01113: file 1 needs media recovery
    You probably forgot to stop the source database before copying the files. Go back to step 1 and recopy the files.
    ORA-01503: CREATE CONTROLFILE failed
    ORA-00200: controlfile could not be created
    ORA-00202: controlfile: '/u03/oradata/dg9a/control01.ctl'
    ORA-27038: skgfrcre: file exists
    Double check the pfile created in step 2. Make sure the control_files setting is pointing at the correct location. If the control_file setting is ok, make sure that the control files were not copied with the rest of the database files. If they were, delete or rename them.

  • 6. Perform a few checks
    If the last step went smoothly, the database should be open. It is advisable to perform a few checks at this point:

    • Check that the database has opened with:
      select status from v$instance;
      The status should be 'OPEN'
    • Make sure that the datafiles are all ok:
      select distinct status from v$datafile;
      It should return only ONLINE and SYSTEM.
    • Take a quick look at the alert log too.

  • 7. Set the databases global name
    The new database will still have the source databases global name. Run the following to reset it:
    alter database rename global_name to <new database sid>
    /
  • 8. Create a spfile
    From sqlplus:
    create spfile from pfile;
  • 9. Change the database ID
    If RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.
    From sqlplus:
    shutdown immediate
    startup mount
    exit
    From unix:
    nid target=/
    NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:
    shutdown immediate
    startup mount
    alter database open resetlogs
    /
  • 10. Configure TNS
    Add entries for new database in the listener.ora and tnsnames.ora as necessary.

  • 11. Finished

Sukhwinder Singh
Apps DBA

Correcting invalid spfile parameters

Consider the following situation. An alteration is made to the spfile which results in the instance being unable to start. Because the instance will not start, the mistake can not be corrected:

SQL> show parameter sga_max_size
sga_max_size big integer 537989896

SQL> alter system set sga_max_size=99999999999 scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-27102: out of memory

SQL> startup nomount
ORA-27102: out of memory

SQL> alter system set sga_max_size=537989896 scope=spfile;
alter system set sga_max_size=537989896 scope=spfile
*
ERROR at line 1:
ORA-01034: ORACLE not available

The usual way to fix this problem (apart from being more careful in the first place) is to:
  • create pfile from spfile
  • edit the pfile
  • startup nomount
  • create spfile from pfile
  • shutdown
  • startup
  • remove the pfile
There is another way however - and one that I prefer. It relies on the fact that a database can have a spfile and a pfile at the same time, and furthermore parameters specified in the pfile override those in the spfile! The spfile location must be specified in the pfile for this to work. Check out the following trace:

SQL> !
[oradev@testerp] $ vi $ORACLE_HOME/dbs/init${ORACLE_SID}.ora

spfile=/u02/oradata/scr9/spfilescr9.ora
sga_max_size=537989896


:wq

oracle@bloo$ exit

SQL> startup
ORACLE instance started.
Total System Global Area 554767132 bytes
Fixed Size 451356 bytes
Variable Size 402653184 bytes
Database Buffers 150994944 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

SQL> alter system set sga_max_size=537989896 scope=spfile;
System altered.

SQL> !rm $ORACLE_HOME/dbs/init${ORACLE_SID}.ora

SQL>
Sukhwinder Singh
Apps DBA

Clone an Oracle database using an Online / Hot backup..

This procedure will clone a database using a online copy of the source database files. Before beginning though, there are a few things that are worth noting about online/hot backups:

  • When a tablespace is put into backup mode, Oracle will write entire blocks to redo rather than the usual change vectors. For this reason, do not perform a hot backup during periods of heavy database activity - it could lead to a lot of archive logs being created.
  • This procedure will put all tablespaces into backup mode at the same time. If the source database is quite large and you think that it might take a long time to copy, consider copying the tablespaces one at a time, or in groups.
  • While the backup is in progress, it will not be possible to take the tablespaces offline normally or shut down the instance.
Ok, lets get started...
  • 1. Make a note of the current archive log change number
    Because the restored files will require recovery, some archive logs will be needed. This applies even if you are not intending to put the cloned database into archive log mode. Work out which will be the first required log by running the following query on the source database. Make a note of the change number that is returned:
    select max(first_change#) chng
    from v$archived_log
    /
  • 2. Prepare the begin/end backup scripts
    Two scripts; begin_backup.sql and end_backup.sql. When executed, these scripts will either put the tablespaces into backup mode or take them out of it.
  • 3. Put the source database into backup mode
    Run the begin backup script created in the last step:
    From sqlplus: @begin_backup
    This will put all of the databases tablespaces into backup mode.

  • 4. Copy the files to the new location
    Copy, scp or ftp the files from the source database/machine to the target. Do not copy the control files across. Make sure that the files have the correct permissions and ownership.

  • 5. Take the source database out of backup mode
    Once the file copy has been completed, take the source database out of backup mode. Run the end backup script created in step 2. From sqlplus: @end_backup
  • 6. Copy archive logs
    It is only necessary to copy archive logs created during the time the source database was in backup mode. Begin by archiving the current redo:
    alter system archive log current;
    Then, identify which archive log files are required. When run, the following query will ask for a change number. This is the number noted in step 1.
    select name from v$archived_log
    where first_change# >= &change_no
    order by name
    /
    Create an archive directory in the clone database.s file system and copy all of the identified logs into it.

  • 7. Produce a pfile for the new database
    This step assumes that you are using a spfile. If you are not, just copy the existing pfile.
    From sqlplus:
    create pfile='init<new database sid>.ora' from spfile;
    This will create a new pfile in the $ORACLE_HOME/dbs directory.

    Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.

    Ensure that the archive log destination is pointing to the directory created in step 6.

  • 8. Create the clone controlfile
    Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:
    alter database backup controlfile to trace as '/home/oracle/cr_<new sid>.sql'
    /
    The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:

    • Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).
    • Remove any lines that start with --
    • Remove any lines that start with a #
    • Remove any blank lines in the 'CREATE CONTROLFILE' section.
    • Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'
    • Remove the line 'ALTER DATABASE OPEN RESETLOGS;'
    • Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file. Make sure that you hang onto the command, it will be used later.
    • Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.
    • If the file paths are being changed, alter the file to reflect the changes.

    Here is an example of how the file would look for a small database called dg9a which isn't in archivelog mode:
    STARTUP NOMOUNT
    CREATE CONTROLFILE SET DATABASE "DG9A" RESETLOGS FORCE LOGGING NOARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 453
    LOGFILE
    GROUP 1 '/u03/oradata/dg9a/redo01.log' SIZE 100M,
    GROUP 2 '/u03/oradata/dg9a/redo02.log' SIZE 100M,
    GROUP 3 '/u03/oradata/dg9a/redo03.log' SIZE 100M
    DATAFILE
    '/u03/oradata/dg9a/system01.dbf',
    '/u03/oradata/dg9a/undotbs01.dbf',
    '/u03/oradata/dg9a/cwmlite01.dbf',
    '/u03/oradata/dg9a/drsys01.dbf',
    '/u03/oradata/dg9a/example01.dbf',
    '/u03/oradata/dg9a/indx01.dbf',
    '/u03/oradata/dg9a/odm01.dbf',
    '/u03/oradata/dg9a/tools01.dbf',
    '/u03/oradata/dg9a/users01.dbf',
    '/u03/oradata/dg9a/xdb01.dbf',
    '/u03/oradata/dg9a/andy01.dbf',
    '/u03/oradata/dg9a/psstats01.dbf',
    '/u03/oradata/dg9a/planner01.dbf'
    CHARACTER SET WE8ISO8859P1
    ;
  • 9. Add a new entry to oratab and source the environment
    Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
    Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
    echo $ORACLE_SID
    If this doesn't output the new database sid go back and investigate.

  • 10. Create the a password file
    Use the following command to create a password file (add an appropriate password to the end of it):
    orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=<your password>
  • 11. Create the new control file(s)
    Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:
     
    sqlplus "/ as sysdba"
    @/home/oracle/cr_<new database sid>
    If all goes to plan you will see the instance start and then the message 'Control file created'.

  • 12. Recover and open the database
    The archive logs that were identified and copied in step 6 must now be applied to the database. Issue the following command from sqlplus:
    recover database using backup controlfile until cancel
    When prompted to 'Specify log' enter 'auto'. Oracle will then apply all the available logs, and then error with ORA-00308. This is normal, it simply means that all available logs have been applied. Open the database with reset logs:
    alter database open resetlogs;

  • 13. Create temp files
    Using the 'ALTER TABLESPACE TEMP...' command from step 8, create the temp files. Make sure the paths to the file(s) are correct, then run it from sqlplus.

  • 14. Perform a few checks
    If the last couple of steps went smoothly, the database should be open. It is advisable to perform a few checks at this point:

    • Check that the database has opened with:
      select status from v$instance;
      The status should be 'OPEN'

    • Make sure that the datafiles are all ok:
      select distinct status from v$datafile;
      It should return only ONLINE and SYSTEM.

    • Take a quick look at the alert log too.

  • 15. Set the databases global name
    The new database will still have the source databases global name. Run the following to reset it:
    alter database rename global_name to <new database sid>
    /
    Note. no quotes!

  • 16. Create a spfile
    From sqlplus:
    create spfile from pfile;
  • 17. Change the database ID
    If RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.

    From sqlplus:
    shutdown immediate
    startup mount
    exit
    From unix:
    nid target=/
    NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:
    shutdown immediate
    startup mount
    alter database open resetlogs
    /
  • 18. Configure TNS
    Add entries for new database in the listener.ora and tnsnames.ora as necessary.
  • 19. Finished 

Sukhwinder Singh
Apps DBA

Cannot add data file - when attempting to re-add a missing tempfile to a temporary tablespace

SQL> select name from v$tempfile;

NAME
-----------------------------------
/u02/oradata/scr9/temp01.dbf

SQL> !ls //u02/oradata/scr9/temp01.dbf
/u02/oradata/scr9/temp01.dbf not found

SQL> alter tablespace TEMP
add tempfile '/u02/oradata/scr9/temp01.dbf' reuse;
2 alter tablespace TEMP
*
ERROR at line 1:
ORA-01537: cannot add data file '/u02/oradata/scr9/temp01.dbf' - file already
part of database.
Why is this happening?

This can happen if a step has been missed during a database cloning exercise.

How to fix it?

With a temporary tablespace it is possible to drop the missing tempfile and then add a new one. You can only drop a tempfile if it is not in use - but in this case the temp file doesn't actually exist, so it can't be in use. Use the following commands:
SQL> alter tablespace <TEMP_TS_NAME>
2 drop tempfile '<FILE_PATH_AND_NAME>';

SQL> alter tablespace <TEMP_TS_NAME>
2 add tempfile '<FILE_PATH_AND_NAME>' size <FILE_SIZE>;
For example:

SQL> alter tablespace temp
2 drop tempfile '/u02/oradata/scr9/temp01.dbf';

Tablespace altered.

SQL> alter tablespace TEMP
2 add tempfile '/u02/oradata/scr9/temp01.dbf' size 8192m;

Tablespace altered.

Sukhwinder Singh
Apps DBA

Maximum number of processes exceeded..

This means that you have too many database connections basically. Unfortunately since the demise of svrctl, it also means that you as a DBA cannot connect internal to do something about it!

You need to either ask a couple of users to log out, or you need to kill some sessions at the OS level. Once you managed to connect, see if you can spot anyone making abnormal numbers of connections. If you can't find anything you will need to increase the init parameter 'processes'. Unfortunately, this will require a restart of the database to take effect:
 
**********------------------------------------ **********
**********------------------------------------ **********
If you are using spfile: -

SQL> show parameter spfile;
SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
processes                            integer     150

SQL> select count(*) from v$process;

COUNT(*)
----------
149

SQL> alter system set processes=300 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
processes                            integer     300 
 
 
**********------------------------------------ **********
**********------------------------------------ ********** 
If you use pfile then  create spfile from pfile;

Shutdown db
then startup

then run 
SQL> alter system set PROCESSES= xx scope = spfile;


**********------------------------------------ **********
**********------------------------------------ **********
If you want to use pfile instead of spfile,than go to $ORACLE_HOME/dbs/


cp initSID.ora initSID.ora_org
Now Edit initSID.ora file
 
vi initSID.ora
Enter "i" (Without Quotes)
PROCESSES=XX
SESSIONS  2*XX
Press ESC
Then Type ":w"  (Without Quotes to save Value in initSID.ora file)
Then ":q!" (Without Quotes to exit from initSID.ora)
You may need to reboot db after change this parameter
SHUTDOWN DB
Start DB 
Note. The maximum allowed processes is one less than the init parameter 'processes'

Note. When connecting via TNS, you may get this error
when the database is out of processes:

ORA-12516: TNS:listener could not find available handler with matching protocol stack
 
 
 =========
 
Frist decide your system open wiht spfile or init file. You can understand it by using below query

show parameter spfile;

If you use spfile then issue:

alter system set PROCESSES= xx scope = spfile;

If you use pfile than 

create spfile from pfile;

Shutdown db

then startup
then run 

alter system set PROCESSES= xx scope = spfile;

If you want to use pfile instead of spfile,than go to $ORACLE_HOME/dbs/

cp initSID.ora initSID.ora_org

vi initSID.ora
PROCESSES=xxx
You may need to reboot db after change this parameter 

=============================
 
I assume you are using pfile.

to check if you are using pfile or spfile ,

go to sql> / as sysdba

show parameter spfile;
If it shows none , means you are using pfile.


Before making any changes in the init.ora file always make a copy for the same as init.ora.backup.

Once that is done , then open your init.ora file , it would be by the name initSID.ora.

Once you open that , then there will be a parameter process=150 or any value.

Change it there and then , since you are using a pfile , you need to restart the database to bring the changes into effect.

go to sql > / as sysdba

shutdown immediate <enter>

startup < enter>

Then the new processes will be reflected in the database.

If you are using spfile , then use the following command :


alter system set processes=value scope=both;

to check the value of the processes parameter :
sqlplus "/ as sysdba"

show parameter processes;


========

To Increase the number of process
Step-1
Create pfile= /oracle/.... from spfile
Step 2
Shutdown database
Step-3
Amend that pfile and increase the number of processes
Step -4
startup the database with new pfile
startup pfile='/oracle/....'
Step-5
Create spfile from pfile='/otacle/....'
So that when you startup database next time it will read spfile (new)

 
Sukhwinder Singh
Apps DBA
There was an error in this gadget