Rename and/or move Tablespaces Datafiles

Hi everyone,

 

This article will describe how to rename and/or move Tablespaces Datafiles.

 

Put desired Tablespaces offline

SQL> alter tablespace tb1 offline normal;
SQL> alter tablespace tb2 offline normal;
SQL> alter tablespace tb3 offline normal;

 

OS side, move and rename Datafiles

# mv /previous_path/tb1.dbf /new_path/tb1_newname.dbf
# mv /previous_path/tb2.dbf /new_path/tb2_newname.dbf
# mv /previous_path/tb3.dbf /new_path/tb3_newname.dbf

 

Update file pointers in the Database control file

The following is done if you are acting on multiple Tablespaces

SQL> alter database rename file '/previous_path/tb1.dbf', '/previous_path/tb2.dbf', '/previous_path/tb3.dbf' to '/new_path/tb1_newname.dbf', '/new_path/tb2_newname.dbf', '/new_path/tb3_newname.dbf';

If only acting on one Tablespace, you can use this syntax

SQL> alter tablespace rename datafile '/previous_path/tb1.dbf', '/previous_path/tb2.dbf', '/previous_path/tb3.dbf' to '/new_path/tb1_newname.dbf', '/new_path/tb2_newname.dbf', '/new_path/tb3_newname.dbf';

 

Get Tablespaces back online

SQL> alter tablespace tb1 online;
SQL> alter tablespace tb2 online;
SQL> alter tablespace tb3 online;

 

Done.

Source : Oracle DBA Guide

 

 

 

Advertisements

Cannot connect remotely – ORA-01017: invalid username/password; logon denied

Hi everyone,

 

When trying to connect to my Oracle instance 12.1, I faced an issue where i was able to log in locally with SYS as SYSDBA but not remotely.

ORA-01017: invalid username/password; logon denied

In fact, for remote connection, Oracle is using a password file named “orapwSID” located in your “$ORACLE_HOME/dbs“.

Anyway, to solve this, you simply need to set your SYS password again. This will update the password file used over the network.

SQL> alter user sys identified by password;

You should now be able to connect remotely.

 

Some useful queries used during troubleshooting :

SQL> show parameter remote_login_passwordfile;
SQL> select * from v$pwfile_users;

Another way to solve this would be to delete the password file or set the parameter below to “none“.

remote_login_passwordfile Oracle doc link : https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams198.htm#REFRN10184

 

Install Oracle Database 12.1 and PostgreSQL 9.4 + PostGIS on Oracle Linux 7.4

Hey everyone,

I had to install an Oracle Linux 7.4 Server with a running Oracle Database 12.1 and PostgreSQL 9.4 + PostGIS. I will share my steps below.

 

Install Oracle Linux 7.4

  • Select “Agent for Hypervisor” during installation

 

[Optional if previous step is done] Install VMWareTools

  • From vSphere client mount source
  • From VM
# mount /dev/cdrom /mnt 
# cp /mnt/VMwareTools-8.6.17-3814316.tar.gz /tmp 
# tar -xvzf /mnt/VMwareTools-8.6.17-3814316.tar.gz 
# cd /tmp/vmware-tools-distrib/ 
# ./vmware-install.pl

 

Configure Network settings

# ip addr add 192.168.100.43/17 dev ens160 
# route add default gw 192.168.100.160 ens160 
# vi /etc/resolv.conf add  "nameserver 192.168.100.125 nameserver 192.168.100.128" 
# vi /etc/hosts add "192.168.100.43   servername.localdomain"

 

Update Yum and install Oracle Prerequisites

# yum update 
# yum install oracle-rdbms-server-12cR1-preinstall.x86_64

For Oracle Database 18c

yum install oracle-database-preinstall-18c

Set “oracle” account password

# passwd oracle

 

Set secure Linux to permissive

By editing the “/etc/selinux/config” file, making sure the SELINUX flag is set as follows “SELINUX=permissive

Once the change is complete, restart the server or run the following command

# setenforce permissive

 

Disable (or configure) firewall

# systemctl stop firewalld 
# systemctl disable firewalld

 

Add new Disk for Oracle Data from vSphere client

Reboot the VM or rescan SCSI bus manually

# echo "- - -" > /sys/class/scsi_host/host#/scan

Replace “#” by “0”,”1″, … Can be found by running

# ls /sys/class/scsi_host

Then check if the new Disk is visible

# fdisk -l

 

Create a new partition and format it or follow this link to create an LVM first.

# fdisk /dev/sdb

Select option “n” select “w” to write changes and exit

# mkfs.ext4 /dev/sdb1

 

Edit fstab

# vi /etc/fstab

Add “/dev/sdb1       /u01    ext4    defaults        1 2

 

Create “u01” directory, mount the new partition, create sub-folders structure and modify permissions and ownership

# mkdir u01 
# mount -a 
# mkdir -p /u01/app/oracle/product/12.1.0.2/db_1 
# chown -R oracle:oinstall /u01 
# chmod -R 775 /u01

 

Edit “/home/oracle/.bash_profile” file

# vi /home/oracle/.bash_profile

Add the following at the end of the file

# Oracle Settings

export TMP=/tmp 
export TMPDIR=$TMP
export ORACLE_HOSTNAME=servername.localdomain 
export ORACLE_UNQNAME=orcl 
export ORACLE_BASE=/u01/app/oracle 
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1 
export ORACLE_SID=orcl
export PATH=/usr/sbin:$PATH 
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib 
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Note : For the following steps to work, Xming should be installed and running. Putty should be configured to “Enable X11 Forwarding” and you have to login with oracle user directly from Putty
Upload “linuxamd64_12102_database_1of2.zip” and  linuxamd64_12102_database_2of2.zip” to /tmp

 

Unzip the files

# cd /tmp 
# unzip linuxamd64_12102_database_1of2.zip 
# unzip linuxamd64_12102_database_2of2.zip

 

Install Oracle Database 12.1.02

# cd database
# ./runInstaller

 

Note : EM Url become https://servername.localdomain:5500/em

 

Set automatic DB startup for ORCL instance  

# vi /etc/oratab

Switch the “N” value to “Y” “orcl:/u01/app/oracle/product/12.1.0.2/db_1:Y

 

 

PostgreSQL install will come later.

Temp source: gokhanatil.com

 

 

 

 

Remotely SQLPlus as sysdba – ORA-01031: insufficient privileges

Hello,

SQLPlus as SYSDBA remotely can throw the following error due to security reason :

>sqlplus sys/syspass@servername/instance as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mer. Ao¹t 30 14:25:48 2017

Copyright (c) 1982, 2011, Oracle. All rights reserved.

ERROR:
ORA-01031: insufficient privileges

 

To allow to connection, connect locally using SQLPlus and make sure the parameter “remote_login_passwordfile” is set to ‘”EXCLUSIVE” :

SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE

 

Then check a password file exist for your instance :

SQL> select * from v$pwfile_users;

no selected row.

 

It appears it does not exist and needs to be created to allow remote connection. We will create on a Windows Server and I will refer to Oracle doc https://docs.oracle.com/html/E25494_01/dba007.htm

D:\oracle\11.2.0\db\BIN>orapwd.exe file=D:\oracle\11.2.0\db\database\PWDINSTANCENAME.ora

Enter password for SYS:

 

You can now log on remotely.

Oracle Veeam Backup | VSS_FAILED_AT_FREEZE | ORA-00257: archiver error from remote SQLPlus | ORA-16038 ORA-19809 ORA-00312 found in Eventvwr | Fast Recovery Area is Full

Today, I was facing an issue where my Veeam Backup job failed on one Oracle Server hosting many Instances.

Only one of them was failing and Veeam reported the following error :

Processing SERVER_NAME Error: Unfreeze error: [Backup job failed.
Cannot create a shadow copy of the volumes containing writer's data.
A VSS critical writer has failed. Writer name: [Oracle VSS Writer - INSTANCE_NAME]. Class ID: [{26d02976-b909-43ad-af7e-62a4f625e372}]. Instance ID: [{bf669252-5552-433d-a0b5-cab28e14a19b}]. Writer's state: [VSS_WS_FAILED_AT_FREEZE]. Error code: [0x800423f4].]

 

Checking the Server Eventlogs reported Event 46, Oracle.VSSWriter.INSTANCE_NAME

General Tab :

VSS-00046: failure to switch the current database redo logs

Cause : OCI call failed.

Action : Check the accompanying error message.

Additional info :
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Oracle VSS writer version 11.2.0.2.0 Production
Error at line : 4856
Failure on Freeze event

Details Tab reported (example copied from Internet) :

ORA-16038: log 1 sequence# 49 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1:
'/opt/oracle/db/home/data/imapdb/redo01.log'

 

Also, trying to connect remotely using SQLPlus trowed the following :

ERROR:
ORA-00257: archiver error. Connect internal only, until freed.

 

With all these information, I suspected the Fast_Recovery_Area to be full and it has been confirmed by running the following SQL statement (locally to avoid SQLPlus connection issue explained above) :

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME
--------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
D:\oracle\fast_recovery_area
 4322230272 3,4490E+10 759962624 809

Indeed, it is more than full.

 

Finally, I tried to delete expired Archivelogs using RMAN but it did not find any files to delete. It’s why I suggest to 1st run a Crosscheck and then delete expired items :

  • Start RMAN in a Command Prompt
  • Connect to your Instance
rman > connect target SYS/oracle@trgt
  • Run a Crosscheck
rman > crosscheck archivelog all;
  • Delete expired Archivelogs
rman > delete expired archivelog all;

 

In my case,  after deleting expired Archivelogs, the FRA was still Full so I decided to simply delete all of them and just keep the last 10 days by running :

rman>delete archivelog until time ‘SYSDATE-10’;

 

Veeam Backup & Oracle VSS is now working, Remote SQLPlus connection too. Solved. Hope this helps.

Oracle Client 12.1 crashes / closes when clicking on Install at Step 6 of 8

CAUSE

OUI is unable to create or update the appropriate registry keys for 32-bit installation.

You can run the below query to make sure registry does not have the required key.

C:> reg query HKLM\SOFTWARE\WOW6432Node\ORACLE /v inst_loc
ERROR: The system was unable to find the specified registry key or value.

Development team is currently working on this issue via bug 20219460

SOLUTION

Monitor this Bug for the final solution, Bug 20219460 INSTALLER OUI DISAPPEARED ON INSTALLING WIN32 BIT CLIENT ON WINDOWS2012 R2 64BIT

Workaround:

1. Open registry
2. Go to HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node
3. Create a new key with name Oracle
4. Go to Oracle and then create a new String Value with name inst_loc
5. Give the value as C:\Program Files (x86)\Oracle\Inventory
6. Retry installation.

 

Source : oracle.com

Rename Oracle server – Listener won’t start

Hello everyone,

Today, I had to clone an Oracle server several times. I will not explain the steps but just let you know what I had to do to get Oracle working after this change.

The Listener service won’t start. I had to edit its configuration to match to new Server name.

  1. Open the Listener configuration file : “C:\app\oracle\product\12.2.0\dbhome_1\network\admin\listener.ora”. Could be different depending on your installation settings.
  2. Replace the old Server name by the new one.
  3. Starting the Listener won’t allow me to connect. I also had to restart completely the Server.