Category: oracle database

ORA-32771: Cannot Add File To Bigfile Tablespace

When trying to add a datafile to a tablespace , got the error – ORA-32771: cannot add file to bigfile tablespace.


SQL> ALTER TABLESPACE BIG_TBSP1 add datafile ‘+DATA/df01.dbf’ size 130G;
ALTER TABLESPACE BIG_TBSP1 add datafile ‘+DATA/df01.dbf’ size 130G;
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace


SOLUTION:


A bigfile tablespace can contain only one datafile and no other datafile can be added to that.


SQL> select BIGFILE from dba_tablespaces where tablespace_name=’BIG_TBSP1’;


BIGFILE
———————– —
YES


How to increase space in Bigfile Tablespace?




BIGFILE tablespace stores its data in a single datafile with a much larger capacity.


We can resize the size of the datafile in BIGFILE tablespace using ALTER DATABASE Command




ALTER DATABASE DATAFILE ‘/+DATA/df0101.dbf’ RESIZE 180G;


Since BIGFILE Tablespace has only one datafile, there is no need to identify the datafile and increase its size.
We can use ALTER TABLESPACE command to resize at the tablespace level.


ALTER TABLESPACE BIG_TBSP1 RESIZE 180G;

How to Use Logon Trigger to trace sessions connected to Oracle database?

To start tracing:


create or replace trigger user_logon_trg
after logon on database
begin
if USER = ‘xxxx’ then
execute immediate
‘Alter session set events ”10046 trace name context forever, level 8”’;
end if;
end;
/


/* Login a new session as User ‘xxxx’ and execute your selects to be traced */




NOTE: The following syntax can also be used within the if logic to also get the user name or to add more granularity by specifying a host name where the connection originated.


IF SYS_CONTEXT(‘USERENV’,’SESSION_USER’) = ‘<USER_NM>’ AND SYS_CONTEXT(‘USERENV’,’HOST’) = ‘<HOST_NM>’ THEN


To stop tracing: via LogOff Trigger (needs to be created before logging off)


create or replace trigger user_logoff_trg
before logoff on database
begin
if USER = ‘xxxx’ then
execute immediate
‘Alter session set events ”10046 trace name context off”’;
end if;
end;
/


What is the difference between Switchover and Failover in Oracle Dataguard?

A switchover means just switching roles between the primary database and standby db.
nswitchover, the primary database chnaged to a standby role, and the standby database changed to the primary role.
This is typically done for planned maintenance of the primary db server.


A failover is when the primary database fails and one of the standby databases is 
transitioned to take over the primary role. Failover is performed only in the event 
of a catastrophic failure of the primary database, and there is no possibility of 
recovering the primary database in a timely manner. Failover may or may not result 
in data loss depending on the protection mode in effect at the time of the failover.

How to enable ArchiveLog mode in Oracle Database RAC Environment?

Steps to enable Archivelog mode in RAC environment:


The following steps need to be taken to enable archive logging in a RAC database environment:


1. Shutdown immediate all database instances
$ srvctl stop database -d <db_unique_name>


2. Startup database in mount mode
$ srvctl start database -d <db_unique_name> -o mount


3. Enable archive logging
$ sqlplus / as sysdba
sql> alter database archivelog;
sql> exit;


4. Stop database
$ srvctl stop database -d <db_unique_name>


5.Start all database instances
$ srvctl start database -d <db_unique_name>


6.Verify archiving is enabled/disabled 
sql> archive log list;

Change Oracle Database to ArchiveLog Mode

Steps to change the database to Archivelog Mode:


1. Login to the database server. Connect to sqlplus as sysdba


$sqlplus / as sysdba


2. Shutdown the database


SQL> shutdown immediate


3. Take a full database Backup (Cold Backup in this case)


4. Startup the database in mount stage


SQL> startup mount


5. Enable Archivelog mode


SQL> alter database archivelog;


6. Open the Database.


SQL> Alter database open;


7. Verify the changes.


SQL> archive log list;

Enable ArchiveLog Mode on Oracle RAC database fails with ORA-00265

On a test RAC environment, while archiving is being enabled, “ALTER DATABASE ARCHIVELOG” command errored with ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


Below are the steps performed to enable Archivelog mode on a 3-node RAC database environment.


test0115:TEST1011 $ srvctl status database -d TEST101
Instance TEST1011 is running on node test0115
Instance TEST1012 is running on node test0116
Instance TEST1013 is running on node test0117
test0115:TEST1011 $
test0115:TEST1011 $ srvctl stop database -d TEST101




test0115:TEST1011 $ srvctl status database -d TEST101
Instance TEST1011 is not running on node test0115
Instance TEST1012 is not running on node test0116
Instance TEST1013 is not running on node test0117
test0115:TEST1011 $




test0115:TEST1011 $ srvctl start database -d TEST101 -o mount
test0115:TEST1011 $  srvctl status database -d TEST101
Instance TEST1011 is running on node test0115
Instance TEST1012 is running on node test0116
Instance TEST1013 is running on node test0117
test0115:TEST1011 $




test0115:TEST1011 $ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Sat May 9 14:13:54 2020


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options


SQL> select name,open_mode from gv$database;


NAME      OPEN_MODE
——— ——————–
TEST101   MOUNTED
TEST101   MOUNTED
TEST101   MOUNTED


SQL>SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


Solution:
=========


1. Shutdown the database cleanly


SQL> shutdown immediate (on 3 RAC nodes)


2. Startup the database in mount stage


SQL> startup mount


3. Enable Archivelog mode


SQL> Alter database archivelog;


Database altered.


4. Open the database


SQL> Alter database open.


5. Verify archielog mode is enabled.


SQL> ARCHIVE LOG LIST;

Impdp fails with error ORA-31604

Issue:
When tried to Import a table from one Oracle database to another using IMPDP utility,encountered the error ORA-31604: invalid transform NAME parameter “MODIFY” for object type PROCACT_INSTANCE in function ADD_TRANSFORM
Impdp logfile has the below error:
Starting “<LOGIN_SCHEMA>”.”SYS_IMPORT_FULL_10″:  <LOGIN>/******** parfile=<PARFILE_NAME>.par logfile=<LOG_NAME>.log dumpfile=<DUMPFILE_NAME>.dmp parallel=1
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_MD_TRANSFORMS []
ORA-31604: invalid transform NAME parameter “MODIFY” for object type PROCACT_INSTANCE in function ADD_TRANSFORM
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.KUPW$WORKER”, line 8996
Cause: This is a bug in Oracle Database versio 11g, fixed in Oracle Database version 12c
Solution:
As a workaround, implement any of the below solutions:
1. Use an additional parameter that is exclude=PROCACT_INSTANCE during impdp
2. Redo the export with exclude=PROCACT_INSTANCE and perform import using new dumpfiles.
The use of the exclude=PROCACT_SYSTEM will exclude the resource manager objects such as resource plans and groups. We need to re-create resource plans and groups after impdp is successfully completed.