TNS-03505: Failed to resolve Name

Check thoses:

lsnrctl status
tnsping <ORACLE_SID>

listener.ora
tnsnames.ora
sqlnet.ora

hostname
variable TNS_ADMIN

Note: This article will be updated with more details and explanations

ORA-01031: insufficient privileges (on Windows)

When you have this error, here is a few things to check:

• you have your password file. If not, recreate it:

orapwd file=c:\.....\database\PWD{SID}.ora force=[y/n] 

Carefull for linux DBA, the file is named differentely on Windows - PWD{ORACLE_SID}.ora (ex. PWDORCL.ora), it kepts me looking for a solution to this problem a loonnnng time...

• remote_login_passwordfile must be set to EXCLUSIVE

• If you have two runtime running on the same server (10g and 11g for example), set the TNS_ADMIN environement variable with the location of your sqlnet.ora, listener.ora and tnsnames.ora files

• The oracle user is in the right group (dba)

• Add this line into your sqlnet.ora file (haven't done it yet, but it supposed to fix the issue)

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Drop an Oracle database on Windows server

step 1: get datafile, control file and redo log file location

select file_name from dba_data_files
select name from v$controlfile
select member from v$logfile

step 2: set env variables if not done yet

set oracle_home=C:\Oracle\Orant1020
set oracle_sid=XXX

step 3: shutdown database and restart it in mount stage and restricted exclusively.

SQL> shutdown immediate;
SQL> startup mount exclusive restrict;

step 4: drop the DB and exit sqlplus

SQL> drop database;

step 5: check if the database files are removed, do it if not. Don't forget the folders, trc, alert file, etc.

step 6: Drop the XXX related services using ORADIM

C:\> oradim -delete -sid XXX
Instance deleted.

step 7: Check the listener if there is any XXX services and unregister them if yes. It does it by default, but in case.

RMAN: restore database skip tablespace

Sometimes we want to restore full database except a certain tablespace (data not needed, or a point in time recovery omiting a big tablespace, etc.). That means we need to skip the tablespace(s) for restore operation. This way, the restore operation time can be much quicker.

Remember that the RESTORE DATABASE command restores all the datafiles, except the offline or read-only tablespaces.

Note that RESTORE DATABASE is not the same as BACKUP DATABASE. With command BACKUP DATABASE, RMAN backs up datafiles along with controlfiles and spfile. But with RESTORE COMMAND operation, RMAN only restores datafiles.

To omit a certain tablespace for restore operation use:

RESTORE DATABASE SKIP TABLESPACE tablespace_name.

Suppose I want to omit the restore of indexes tablespace INDX01,INDX02. Then my restore command will be:

RMAN>RESTORE DATABASE SKIP TABLESPACE INDX01,INDX02;

If you specify SKIP FOREVER TABLESPACE, then RMAN specifies the DROP option of ALTER DATABASE DATAFILE ... OFFLINE when taking the datafiles that belong to the tablespace offline before the restore.

The DROP option indicates that RMAN does not intend to recover these files and instead remove their tablespaces from the database's rman operations after the database is opened again. In other words, FOREVER indicates that RMAN never intends to do anything with the skipped tablespaces again.

Suppose you want to skip forever the restore of the tablespaces EXAMPLE, INDX01 and INDX02 then your command will be:

RMAN>RESTORE DATABASE SKIP FOREVER TABLESPACE EXAMPLE, INDX01, INDX02;

Reinstall datapump on 10g

Sometimes, very often due to miss use, your Datapump setup is all messy. One thing left, clean it up and reinstall it.


To quickly cleanup datapump objects:

Identify datapump objects :

select * from dba_objects where object_name like 'SYS_EXPORT%'

Next identify object currently accessed :

select * from v$access where object like 'SYS_EXPORT%'

The objects that are present on dba_objects but not on v$access can be dropped


And the Datapump installation procedure

Catdph.sql will Re-Install DataPump types and views=

SQL> @?/rdbms/admin/catdph.sql

Note: If XDB is installed the it is required to run "catmetx.sql" script also.

prvtdtde.plb will Re-Install tde_library packages

SQL> @?/rdbms/admin/prvtdtde.plb

Catdpb.sql will Re-Install DataPump packages

SQL> @?/rdbms/admin/catdpb.sql

Dbmspump.sql will Re-Install DBMS DataPump objects

SQL> @?/rdbms/admin/dbmspump.sql

To recompile invalid objects, if any

SQL> @?/rdbms/admin/utlrp.sql

Install Oracle 11g on REHL 6

Step by step Oracle database 11g install (Part 2)

If you're here, you've already have a Linux system setup and ready for the proper Oracle Install (users, group, pre-require packages, etc). If not, you can find it here.

The 2 necessary zip files are available on the Oracle Database Software Downloads page.

Change the SELINUX value to permissive in the /etc/selinux/config

Install the ODBC drivers:

yum install unixODBC-devel unixODBC

Unzip the files and run ./runInstaller from the database directory. Follow the steps.


Then, there is a bit more configuration to do:

In the oracle user .bash_profile file:

ORACLE_BASE="/u01/app/oracle"
ORACLE_HOME=$ORACLE_BASE"/product/11.2.0/dbhome_1"
ORACLE_SID=orcl

PATH=$PATH:$ORACLE_HOME/bin

export PATH
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID

The database is up and running. Check with sqlplus or the Oracle Enterprise Manager (By default https://localhost:1521/em)

Move datafiles

Sometimes, when all your datafiles are on the same volume, you can have a lack of space and risks fulling it. In that case, you should move some of your DB datafiles (*.dbf) to another volume.

To do so, you have a few steps to follow:

On the primary

1. tablespace with the files to move must be offline
2. move the datafile(s) with the OS command
3. rename the datafile with SQL
4. put the tablespace online

For the example, we'll move files from /volume_01 to /volume_02, which are within the DATA tablespace.

1. Put the tablespace offline:

SQL> ALTER TABLESPACE DATA OFFLINE NORMAL;

2. Move the file(s)

[oracle@lab01 ~]$ mv /volume_01/datafile/data_01.dbf /volume_02/datafile/data_01.dbf

[oracle@lab01 ~]$ mv /volume_01/datafile/data_02.dbf /volume_02/datafile/data_02.dbf

3. SQL rename

SQL>  ALTER TABLESPACE DATA_XX_REPORT RENAME DATAFILE

      '/volume_01/datafile/data_01.dbf', '/volume_01/datafile/data_02.dbf'

      TO

      '/volume_02/datafile/data_01.dbf', '/volume_02/datafile/data_02.dbf';

4. Tablespace back online

SQL> ALTER TABLESPACE DATA_XX_REPORT ONLINE;

On the standby

The datafiles are supposed to be renamed automatically thanks to the parameter STANDBY_FILE_MANAGEMENT='AUTO', but it doesn't... To fix this, it has to be done on the standby as well.
The steps are:

1. Switch off managed recovery
2. Shutdown immediate
3. move the affected datafiles
4. startup mount
5. alter the database standby file management to manual
6. alter database rename files to new location.
7. alter database standby file management to auto.
8. switch on managed recovery.

1. Switch off managed recovery

[oracle@lab01 ~]$ dgmgrl /

DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production



Copyright (c) 2000, 2005, Oracle. All rights reserved.



Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> disable configuration

Disable.

 stb file management

SQL> ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;

2. Shutdown immediate

SQL> shutdown immediate

ORA-01109: database not open



Database dismounted.

ORACLE instance shut down.

3.  move the files (OS)
See primary commands

4. Startup mount

SQL> startup mount

ORACLE instance started.



Total System Global Area 8.5899E+10 bytes

Fixed Size      2245688 bytes

Variable Size   1711279048 bytes

Database Buffers  8.4171E+10 bytes

Redo Buffers     14528512 bytes

Database mounted.

6. SQL rename files
As the standby is not open, the alter tablespace command does not work. To do it, use the below command

SQL> alter database rename file

     '/volume_01/datafile/data_01.dbf', '/volume_01/datafile/data_02.dbf'

     TO

     '/volume_02/datafile/data_01.dbf', '/volume_02/datafile/data_02.dbf';

7. AUTO standby file management

SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;

8. switch on managed recovery
On the primary:

[oracle@lab01 ~]$ dgmgrl /

DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production



Copyright (c) 2000, 2005, Oracle. All rights reserved.



Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> show configuration



Configuration

  Name:                db1

  Enabled:             NO

  Protection Mode:     MaxPerformance

  Fast-Start Failover: DISABLED

  Databases:

    db1_lab02 - Primary database

    db1_lab01 - Physical standby database



Current status for "db1":

DISABLED



DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration



Configuration

  Name:                db1

  Enabled:             YES

  Protection Mode:     MaxPerformance

  Fast-Start Failover: DISABLED

  Databases:

    db1_lab02 - Primary database

    db1_lab01 - Physical standby database



Current status for "db1":

SUCCESS