Oracle DBA Tips and Techniques

By Arjun Raja

Archive for the ‘Uncategorized’ Category

RESTORE AND RECOVER NON SYSTEM TABLESPACE IN PLUGGABLE DATABASE

Posted by Arjun Raja on June 11, 2014

RESTORE AND RECOVER NON SYSTEM TABLESPACE IN PLUGGABLE DATABASE.

cdbd1:/u02/oradata/cdbd1/pdb1d1> l
total 4116376
-rw-r—– 1 oracle dba 20979712 May 11 06:02 temp01.dbf
-rw-r—– 1 oracle dba 1073750016 May 12 09:29 test12c01.dbf
-rw-r—– 1 oracle dba 283123712 May 12 10:15 system01.dbf
-rw-r—– 1 oracle dba 749740032 May 12 10:15 sysaux01.dbf

cdbd1:/u02/oradata/cdbd1/pdb1d1> rm test12c01.dbf

cdbd1:/u02/oradata/cdbd1/pdb1d1> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon May 12 10:23:49 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select count (*) from test;
select count (*) from test
*
ERROR at line 1:
ORA-01116: error in opening database file 14
ORA-01110: data file 14: ‘/u02/oradata/cdbd1/pdb1d1/test12c01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Additional information: 4
Additional information: 4194304

SQL>

Now restore and recover tablespace TEST12C in PDB pdb1d1

First take tablespace offline –

sqlplus sys/xxxxx@pdb1d1 as sysdba

SQL> alter tablespace test12c offline immediate;

Tablespace altered.

SQL>

From CDB root.

RMAN> RESTORE TABLESPACE PDB1D1:TEST12C;

Starting restore at 12-05-2014 10:26:51
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=140 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=14 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00014 to /u02/oradata/cdbd1/pdb1d1/test12c01.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rny_.bkp
channel ORA_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rny_.bkp tag=TAG20140512T085552
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 12-05-2014 10:27:01

RMAN>

RMAN> RECOVER TABLESPACE PDB1D1:TEST12C;

Starting recover at 12-05-2014 10:27:39
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 12-05-2014 10:27:40
starting full resync of recovery catalog
full resync complete

RMAN>

cdbd1:/u02/oradata/cdbd1/pdb1d1> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon May 12 10:28:05 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter tablespace test12c online;

Tablespace altered.

SQL>

All good.

Posted in Uncategorized | Leave a Comment »

DROP AND RECREATE PLUGGABLE DATABASE

Posted by Arjun Raja on June 11, 2014

DROP AND RECREATE PLUGGABLE DATABASE

I created a pluggable database using this syntax.

CREATE PLUGGABLE DATABASE pdb1d1 ADMIN USER pdb1d1admin IDENTIFIED BY oracle12c
FILE_NAME_CONVERT=(‘/u02/oradata/cdbd1/pdbseed/’,’/u02/oradata/cdbd1/pdb1d1′);

Forgot to put a slash after pdb1d1 – hence the datafiles were created in the same directory as container db.

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
/u02/oradata/cdbd1/pdb1d1system01.dbf
/u02/oradata/cdbd1/pdb1d1sysaux01.dbf

To rectify this :

First drop the pluggable database and remove all datafiles .

Connect to CBD

sqlplus / as sysdba

SQL> drop pluggable database pdb1d1 including datafiles;
drop pluggable database pdb1d1 including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database PDB1D1 is not closed on all instances.

Therefore close the pluggable database first –

SQL> alter pluggable database pdb1d1 close;

Pluggable database altered.

SQL>drop pluggable database pdb1d1 including datafiles;

Pluggable database dropped.

SQL>

Now try logging in to the pluggable database.

cdbd1:/u01/oracle/scripts> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 9 09:51:23 2014

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:
Create the pluggable database again.

CREATE PLUGGABLE DATABASE pdb1d1 ADMIN USER pdb1d1admin IDENTIFIED BY oracle12c
FILE_NAME_CONVERT=(‘/u02/oradata/cdbd1/pdbseed/’,’/u02/oradata/cdbd1/pdb1d1/’);

sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL> select file_name from dba_Data_files;
select file_name from dba_Data_files
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

OPEN THE PDB

Login as sysdba into the CDB.

SQL> ALTER PLUGGABLE DATABASE PDB1D1 OPEN READ WRITE;

Pluggable database altered.

SQL>

Now login to the PDB

SQL> select file_name from dba_Data_files;

FILE_NAME
——————————————————————————–
/u02/oradata/cdbd1/pdb1d1/system01.dbf
/u02/oradata/cdbd1/pdb1d1/sysaux01.dbf

SQL>
SQL> select file_name from dba_temp_files;

FILE_NAME
——————————————————————————–
/u02/oradata/cdbd1/pdb1d1/temp01.dbf

SQL>

The alert.log file as a CDB shows new DDL statements such as:

• CREATE PLUGGABLE DATABASE
• ALTER PLUGGABLE DATABASE
• DROP PLUGGABLE DATABASE

This alert log

drop pluggable database pdb1d1 including datafiles
ORA-65025 signalled during: drop pluggable database pdb1d1 including datafiles…
alter pluggable database pdb1d1 close
Fri May 09 09:50:08 2014
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
Pluggable database PDB1D1 closed
Completed: alter pluggable database pdb1d1 close
drop pluggable database pdb1d1 including datafiles
Fri May 09 09:50:43 2014
Deleted file /u02/oradata/cdbd1/pdb1d1temp01.dbf
Deleted file /u02/oradata/cdbd1/pdb1d1sysaux01.dbf
Deleted file /u02/oradata/cdbd1/pdb1d1system01.dbf
Completed: drop pluggable database pdb1d1 including datafiles
Fri May 09 09:54:54 2014
CREATE PLUGGABLE DATABASE pdb1d1 ADMIN USER pdb1d1_admin IDENTIFIED BY *FILE_NAME_CONVERT=(‘/u02/oradata/cdbd1/pdbseed/’,’/u02/oradata/cdbd1/pdb1d1/’)
Fri May 09 09:55:07 2014
****************************************************************
Pluggable Database PDB1D1 with pdb id – 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#2 from file$
Deleting old file#4 from file$
Adding new file#10 to file$(old file#2)
Adding new file#11 to file$(old file#4)
Successfully created internal service pdb1d1 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB1D1 with pdb id – 3 is now marked as NEW.

Posted in Uncategorized | Leave a Comment »

UPGRADE FROM 11.2.0.4 TO 12C

Posted by Arjun Raja on March 6, 2014

11.2.0.4 to 12c upgrade using DBUA

Posted in Uncategorized | Leave a Comment »

DELETE/REMOVE TARGET FROM CLOUD/GRID CONTROL REPOSITORY

Posted by Arjun Raja on February 11, 2014

To remove a target from the database repository –

Login as sysman

SQL> select target_name,target_type from mgmt_targets where target_name like ‘%LN%’;

TARGET_NAME
——————————————————————————–
TARGET_TYPE
—————————————————————-
LNAC_sys
oracle_dbsys

SQL> exec mgmt_admin.delete_target(‘LNAC_sys’,’oracle_dbsys’);

PL/SQL procedure successfully completed.

To delete agent :

SQL> select target_name from mgmt_targets where target_type=’oracle_emd’;
SQL> exec mgmt_admin.cleanup_agent(’Server1:3872′);

Posted in Oracle 12c Cloud Control, Uncategorized | Tagged: , | Leave a Comment »

Oracle Cloud Control 12c ( 12.1.0.2 with Plug-ins) Installation

Posted by Arjun Raja on October 10, 2013

OMS_12C_REL2_INSTALL

Posted in Oracle 12c Cloud Control, Uncategorized | Leave a Comment »

suspended on broken target error in 12c cloud control.

Posted by Arjun Raja on April 12, 2013

If you get this error while running jobs via cloud control on a particular database – “suspended on broken target” – this is the fix.

We just need to configure the target again – in case of this error.

No need to remove the target beforehand

simply click on targets/databases, choose the database and click configure – uses already saved dbsnmp password so simply click Next and proceed.

Run job again – it should go through.

Posted in Uncategorized | Leave a Comment »

IMPDP FAILS IN DATABASE WITHOUT OLAP – ‘DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG’ must be declared

Posted by Arjun Raja on March 19, 2013

Export taken from database with OLAP – 11.1.0.7 database

While importing the exported dump into a 11.2.0.3 database – following warnings appear –

ORA-39083: Object type PROCACT_SCHEMA failed to create with error: ORA-06550: line 2, column 1:
PLS-00201: identifier ‘DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG’ must be declared ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
ORA-06550: line 3, column 1:
PLS-00201: identifier ‘DBMS_CUBE_EXP.SCHEMA_INFO_IMP_LOOP’ must be declared ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
ORA-06550: line 8, column 1:
PLS-00201: identifier ‘DBMS_CUBE_EXP.SCHEMA_INFO_IMP_END’ must be declared

The above are just warnings and can be ignored if OLAP is not required.

But if you prefer a clean import without all this clutter and provided OLAP is not required then

Export the data in the source 11g database with the following option added to the expdp command

VERSION=10.2

Example of parameter file for export –

directory=data_pump_dir
dumpfile=schemaowner.dmp
logfile=schemaowner.log
CONTENT=ALL
SCHEMAS=SCHEMAOWNER
VERSION=10.2

Warning -If VERSION=10.2 is used, the COMPRESSION=ALL cannot be used in the parameter file….Therefore the export dump size will be much larger.

The dump above can be imported into the 11.2.0.3 database which does not have OLAP – no warnings will be displayed.

Posted in Uncategorized | Leave a Comment »

emctl config agent addinternaltargets – to discover targets manually in Oracle 12c Cloud Control

Posted by Arjun Raja on March 18, 2013

If for any reason you lose an agent in the cloud console area and want to discover targets manually from the command line- login to the agent server and run the following from the agent home/bin

This is a replacement for the agentca -d command issued in Oracle 10g and 11g grid control.

cd $AGENT_HOME/bin

1. Stop and start the OMS:

./emctl stop oms

./emctl start oms

2. Secure the Management Agent:

./emctl secure agent

3. Add the targets:

./emctl config agent addinternaltargets

4. Upload the targets:

./emctl upload agent

Posted in EM Grid Control, Uncategorized | Leave a Comment »

Deinstalling ORACLE 12c AGENT HOME

Posted by Arjun Raja on November 28, 2012

Login to server

cd $AGENT_HOME/core/12.1.0.2.0/oui/bin

./runInstaller

Click on Deinstall Products

Choose from the bottom – and deinstall one by one – plugins first and then sbin and finally agent 12c home.

Posted in Uncategorized | Leave a Comment »

DEINSTALL 12c CLOUD CONTROL

Posted by Arjun Raja on July 16, 2012

Since my cloud control set-up on AIX was still in test mode, I decided to de-install it and then install the new version with the bundle patch included.

I copied the new Oracle 12c cloud control software with the bundle patch for AIX and unzipped it.

Using the Graphical interface to deinstall….

Launch the de-installer from the staging area.

cloud:/u01/oracle/stage/newoms> ./runInstaller -deinstall
Starting Oracle Universal Installer…

Deinstall in this order.

1. On the Inventory screen, select the plug-in homes, and click Remove. 2. On the Inventory screen, select the sbin home, and click Remove. 3. On the Inventory screen, select the Management Agent, and click Remove.
4. On the Inventory screen, select the Web Tier home , and click Remove. 5. On the Inventory screen, select the remaining plug-ins homes, and click Remove.
6. On the Inventory screen, select the jdk home, and click Remove. 7. On the Inventory screen, select the oms home, and click Remove. 8. On the Inventory screen, select the common home, and click Remove.

Oracle Home
“/u01/oracle/Middleware/agent/plugins/oracle.sysman.oh.discovery.plugin_12.1.0.1.0” is being deinstalled ..

Oracle Home
“/u01/oracle/Middleware/agent/plugins/oracle.sysman.emas.discovery.plugin_12.1.0.1.0” is being deinstalled ..

Oracle Home
“/u01/oracle/Middleware/agent/plugins/oracle.sysman.db.discovery.plugin_12.1.0.1.0” is being deinstalled ..

Oracle Home
“/u01/oracle/Middleware/agent/plugins/oracle.sysman.csa.discovery.plugin_12.1.0.1.0” is being deinstalled ..

Oracle Home
“/u01/oracle/Middleware/agent/plugins/oracle.sysman.beacon.agent.plugin_12.1.0.1.0” is being deinstalled ..

Oracle Home
“/u01/oracle/Middleware/agent/plugins/oracle.sysman.emrep.agent.plugin_12.1.0.1.0” is being deinstalled ..

Oracle Home
“/u01/oracle/Middleware/agent/plugins/oracle.sysman.emas.agent.plugin_12.1.0.1.0” is being deinstalled ..

Oracle Home “/u01/oracle/Middleware/agent/sbin” is being deinstalled ..

Oracle Home “/u01/oracle/Middleware/agent/core/12.1.0.1.0” is being deinstalled ..

Oracle Home “/u01/oracle/Middleware/Oracle_WT” is being deinstalled ..

Oracle Home
“/u01/oracle/Middleware/plugins/oracle.sysman.db.oms.plugin_12.1.0.1.0” is being deinstalled ..

Oracle Home
“/u01/oracle/Middleware/plugins/oracle.sysman.xa.oms.plugin_12.1.0.1.0” is being deinstalled ..

Oracle Home
“/u01/oracle/Middleware/plugins/oracle.sysman.emas.oms.plugin_12.1.0.1.0” is being deinstalled ..

Oracle Home
“/u01/oracle/Middleware/plugins/oracle.sysman.mos.oms.plugin_12.1.0.1.0” is being deinstalled ..

Oracle Home “/u01/oracle/Middleware/jdk16” is being deinstalled ..

Oracle Home “/u01/oracle/Middleware/oms” is being deinstalled ..

Oracle Home “/u01/oracle/Middleware/oracle_common” is being deinstalled ..

Finally only ORACLE_HOME remains

(Embedded image moved to file: pic24355.gif)

Posted in Uncategorized | Leave a Comment »