Nothing but my learning and experience with Oracle..!

Monday, October 10, 2016

Creating a PDB Using SQL Plus

There are different methods that a DBA can use to create CDBs and PDBs. Here I am going to create one PDB database using SQL plus.

You can even use DBCA to create a PDB in an existing CDB, using the PDB$SEED database as a template for your new PDB.

Creating a PDB Using SQL Plus

To create a PDB from the SQL*Plus command line, use the CREATE PLUGGABLE DATABASE SQL statement. This PDB is also created from the PDB$SEED database (that will be always in read only mode and we cant alter its state) and includes the full data dictionary, which includes internal links to system-supplied root objects and metadata.You can create a PDB within a CDB, but not from within a non-CDB or from within another PDB.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

Use the following query to identify which datafile belongs to which container.

SQL> set lines 222

col file_name for a81

col con_name for a15

select a.name "file_name",a.CON_ID,b.NAME "con_name" from v$datafile a,v$containers b where a.con_id=b.con_id order by a.CON_ID;

file_name CON_ID con_name
---------------------------------------------------------------------------------- ---------- ---------------
/data3/TDCDB/datafile/o1_mf_system_cz9f6hfw_.dbf 1 CDB$ROOT
/data3/TDCDB/datafile/o1_mf_users_cz9f75cx_.dbf 1 CDB$ROOT
/data3/TDCDB/datafile/o1_mf_undotbs1_cz9f6pc3_.dbf 1 CDB$ROOT
/data3/TDCDB/datafile/o1_mf_sysaux_cz9f6mwy_.dbf 1 CDB$ROOT
/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_sysaux_cz9f6ob6_.dbf 2 PDB$SEED
/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_system_cz9f6kjf_.dbf 2 PDB$SEED
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3 PDB1
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3 PDB1
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf 3 PDB1
9 rows selected .

SQL>

If you are using OMF, then create new pluggable database using following syntax.

SQL> create pluggable database pdb2 admin user syspdb2 identified by syspdb2 roles=(CONNECT,DBA);

Pluggable database created.

SQL>

Note the clause "admin user syspdb2 identified by syspdb2". It means the PDB has a user called syspdb2 which is an admin user. The next line "roles=(CONNECT,DBA)" indicates that the user has the CONNECT and DBA roles. This becomes the DBA user of the PDB. Let's see that by connecting as that user and confirming that the roles have enabled.

if you are not using OMF,you have to use FILE_NAME_CONVERT parameter while creating PDB , for example

SQL>CREATE PLUGGABLE DATABASE pdb2 ADMIN USER syspdb2 IDENTIFIED BY syspdb2 FILE_NAME_CONVERT=('/oradata/cdb1/pdbseed/','/oradata/cdb1/pdb2/');

Alternatively, we can specify the PDB_FILE_NAME_CONVERT initialization parameter at CDB level before calling the command without using the FILE_NAME_CONVERT clause.
SQL>CONN / AS SYSDBA

SQL>ALTER SESSION SET PDB_FILE_NAME_CONVERT='/oradata/cdb1/pdbseed/','/oradata/cdb1/pdb3/';

So while creating the PDB, you can simply create PDB without specifying the FILE_NAME_CONVERT clause. For example,

SQL> CREATE PLUGGABLE DATABASE pdb3 ADMIN USER syspdb3 IDENTIFIED BY syspdb3;

Admin user clause:-

Use this clause to create an administrative user who can be granted the privileges required to perform administrative tasks on the PDB. For admin_user_name, specify name of the user to be created. Use the IDENTIFIED BY clause to specify the password for admin_user_name. Oracle Database creates a local user in the PDB and grants the PDB_DBA local role to that user. Oracle prefers not to use SYS(common user) to do administrative task in PDBs , instead use this admin user.

SQL> select a.name "file_name",a.CON_ID,b.NAME "con_name" from v$datafile a,v$containers b where a.con_id=b.con_id order by a.CON_ID;

file_name CON_ID con_name

----------------------------------------------------------------------------------------------- ---------- ---------------

/data3/TDCDB/datafile/o1_mf_system_cz9f6hfw_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_users_cz9f75cx_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_undotbs1_cz9f6pc3_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_sysaux_cz9f6mwy_.dbf 1 CDB$ROOT

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_sysaux_cz9f6ob6_.dbf 2 PDB$SEED

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_system_cz9f6kjf_.dbf 2 PDB$SEED

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf 3 PDB1

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_system_czpbmw7q_.dbf 4 PDB2

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_sysaux_czpbmw7w_.dbf 4 PDB2

11 rows selected.

SQL>

We could see two new datafile got created one for SYSTEM and another is for SYSAUX. There is a separate SYSTEM and SYSAUX tablespace for the root container of the CDB and each PDB.

Newly created pluggable database will be in MOUNTED mode, we need to open it.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 MOUNTED

SQL> alter pluggable database PDB2 open;

Pluggable database altered.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 READ WRITE

SQL>

Now we can see that new pluggable database PDB2 is detected by listener.

[oracle@mydb01]:[TDCDB] $ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 10-OCT-2016 16:47:12

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 27-SEP-2016 19:51:35

Uptime 12 days 19 hr. 55 min. 37 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/grid/product/12.1.0.2/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/mydb01/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.81.155.146)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.81.155.155)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "-MGMTDBXDB" has 1 instance(s).

---output truncated--

Service "pdb1" has 1 instance(s).

Instance "TDCDB", status READY, has 1 handler(s) for this service...

Service "pdb2" has 1 instance(s).

Instance "TDCDB", status READY, has 1 handler(s) for this service...

Service "ptgracscan" has 1 instance(s).

Instance "-MGMTDB", status READY, has 1 handler(s) for this service...

The command completed successfully

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL>

SQL> alter session set container=pdb2;

Session altered.

SQL>

SQL> select USERNAME,COMMON from dba_users;

USERNAME COM

---------------------------- ---

SYSPDB2 NO

SYSTEM YES

SYS YES

--- output truncated---.

You could see syspdb2 is not a common user - ie it is available only to pdb2 .

I created one TNS entry PDB2 , that will connects to PDB2.

[oracle@ehrsvpitoel02]:[TDCDB] $ tnsping PDB2

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 10-OCT-2016 16:48:30

Used parameter files:

/u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ptgracscan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB2)))

OK (0 msec)

[oracle@ehrsvpitoel02]:[TDCDB] $ sqlplus syspdb2/syspdb2@PDB2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 10 16:48:48 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from session_roles;

ROLE

----------------------

CONNECT

DBA

PDB_DBA

... output truncated ...

26 rows selected.

SQL> sho con_name

CON_NAME

------------------------------

PDB2

Since this user(syspdb2) is a DBA, it can alter the parameters of the PDB as needed: You can change parameters in specific containers to meet the application's requirement.

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE

------------------------------------ ----------- ------------------------

optimizer_index_cost_adj integer 100

SQL> alter system set optimizer_index_cost_adj = 50;

System altered.

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE

------------------------------------ ----------- ------------------------

optimizer_index_cost_adj integer 50

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

[oracle@mydb01]:[TDCDB] $ ss

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 10 16:50:18 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,Advanced Analytics and Real Application Testing options

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE

------------------------------------ ----------- ------------------

optimizer_index_cost_adj integer 100

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL>

Note that not all the parameters can be modified in a PDB. A column ISPDB_MODIFIABLE in V$PARAMETER shows whether the parameter can be modified in a PDB or not. Here is an example:

SQL> select name, ispdb_modifiable from v$parameter where name in ( 'optimizer_index_cost_adj','audit_trail');

NAME ISPDB

----------------------------- -----

audit_trail FALSE

optimizer_index_cost_adj TRUE

SQL>

The audit_trail parameter is for the entire CDB; you can't modify them for individual PDBs.

Reference:- http://www.oracle.com/technetwork/articles/database/multitenant-part1-pdbs-2193987.html

Tuesday, September 20, 2016

Recovering from the loss of a system tablespace to a differnet location in OMF

When you lose the SYSTEM tablespace,the instance could crash or hang: anyway the instance will alert you as soon as possible.

In this example we will consider the scenario where we need to restore the lost tablespace to a differnet location as the disk cotroller where we created our SYSTEM tablespace is not working.

Unlike recovery of non–system tablespaces that can be recovered with the database in the OPEN state, the database must be in the MOUNT state to recover either the SYSTEM or UNDO tablespace.

First take a full database backup and simulate this issue.

[oracle@mydb01]:[TTREC1] $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Sep 21 15:39:29 2016

connected to target database: TTREC (DBID=4201939161)

RMAN> backup incremental level 0 database ;

Starting backup at 21-SEP-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=365 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=728 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=564 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=135 device type=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/data1/TTREC/datafile/o1_mf_users_cvf5oss4_.dbf

channel ORA_DISK_1: starting piece 1 at 21-SEP-16

channel ORA_DISK_2: starting incremental level 0 datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00003 name=/data1/TTREC/datafile/o1_mf_undotbs1_cvf5npon_.dbf

input datafile file number=00008 name=/data1/TTREC/datafile/o1_mf_orion_da_cvf5npp4_.dbf

input datafile file number=00007 name=/data1/TTREC/datafile/o1_mf_tbs_test_cvf5ostw_.dbf

channel ORA_DISK_2: starting piece 1 at 21-SEP-16

channel ORA_DISK_3: starting incremental level 0 datafile backup set

channel ORA_DISK_3: specifying datafile(s) in backup set

input datafile file number=00002 name=/data1/TTREC/datafile/o1_mf_sysaux_cvf5jnh9_.dbf

input datafile file number=00006 name=/data1/TTREC/datafile/o1_mf_oraion_t_cvf5jnjb_.dbf

input datafile file number=00010 name=/data1/TTREC/datafile/o1_mf_testb_cxnhzbbf_.dbf

channel ORA_DISK_3: starting piece 1 at 21-SEP-16

channel ORA_DISK_4: starting incremental level 0 datafile backup set

channel ORA_DISK_4: specifying datafile(s) in backup set

input datafile file number=00001 name=/data1/TTREC/datafile/o1_mf_system_cy46np73_.dbf

input datafile file number=00005 name=/data1/TTREC/datafile/o1_mf_orinon_t_cvf5lomr_.dbf

input datafile file number=00009 name=/data1/TTREC/datafile/o1_mf_testa_cxf1h6kw_.dbf

channel ORA_DISK_4: starting piece 1 at 21-SEP-16

channel ORA_DISK_1: finished piece 1 at 21-SEP-16

piece handle=/t4_nfs/TTREC/database_07rgcdi4_1_1 tag=TAG20160921T153948 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08

channel ORA_DISK_2: finished piece 1 at 21-SEP-16

piece handle=/t4_nfs/TTREC/database_08rgcdi4_1_1 tag=TAG20160921T153948 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:26

channel ORA_DISK_4: finished piece 1 at 21-SEP-16

piece handle=/t4_nfs/TTREC/database_0argcdi5_1_1 tag=TAG20160921T153948 comment=NONE

channel ORA_DISK_4: backup set complete, elapsed time: 00:01:15

channel ORA_DISK_3: finished piece 1 at 21-SEP-16

piece handle=/t4_nfs/TTREC/database_09rgcdi4_1_1 tag=TAG20160921T153948 comment=NONE

channel ORA_DISK_3: backup set complete, elapsed time: 00:04:15

Finished backup at 21-SEP-16

Starting Control File and SPFILE Autobackup at 21-SEP-16

piece handle=/data2/TTREC/autobackup/2016_09_21/o1_mf_s_923154244_cy47k48c_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 21-SEP-16

RMAN> exit

Recovery Manager complete.

[oracle@mydb01]:[TTREC1] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 21 15:44:54 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> select file_name from dba_data_files where tablespace_name='SYSTEM';

FILE_NAME

-----------------------------------------------------

/data1/TTREC/datafile/o1_mf_system_cy46np73_.dbf

Lets remove the datafile.

SQL> host rm -f /data1/TTREC/datafile/o1_mf_system_cy46np73_.dbf

SQL> host ls -lrt /data1/TTREC/datafile/o1_mf_system_cy46np73_.dbf

ls: /data1/TTREC/datafile/o1_mf_system_cy46np73_.dbf: No such file or directory

SQL> alter system switch logfile;

System altered.

SQL>

SQL> /

System altered.

SQL>

SQL> /

alter system switch logfile

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 13353

Session ID: 232 Serial number: 41851

SQL> /

ERROR:

ORA-03114: not connected to ORACLE

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

Could see that database got crashed.

[oracle@mydb01]:[TTREC1] $ cdtrace

[oracle@mydb01]:[TTREC1] $ tail -30f alert_TTREC1.log

Wed Sep 21 15:46:48 2016

Thread 1 cannot allocate new log, sequence 19

Checkpoint not complete

Current log# 3 seq# 18 mem# 0: /data1/TTREC/onlinelog/o1_mf_3_cwzoock3_.log

Current log# 3 seq# 18 mem# 1: /data2/TTREC/onlinelog/o1_mf_3_cwzoocvl_.log

Wed Sep 21 15:46:48 2016

Errors in file /u01/app/oracle/diag/rdbms/ttrec/TTREC1/trace/TTREC1_ckpt_22047.trc:

ORA-01243: system tablespace file suffered media failure

ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/data1/TTREC/datafile/o1_mf_system_cy46np73_.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Wed Sep 21 15:46:48 2016

Errors in file /u01/app/oracle/diag/rdbms/ttrec/TTREC1/trace/TTREC1_ckpt_22047.trc:

ORA-01243: system tablespace file suffered media failure

ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/data1/TTREC/datafile/o1_mf_system_cy46np73_.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Wed Sep 21 15:46:48 2016

USER (ospid: 22047): terminating the instance due to error 1243

Wed Sep 21 15:46:48 2016

System state dump requested by (instance=1, osid=22047 (CKPT)), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/ttrec/TTREC1/trace/TTREC1_diag_21981_20160921154648.trc

Wed Sep 21 15:46:48 2016

Dumping diagnostic data in directory=[cdmp_20160921154648], requested by (instance=1, osid=22047 (CKPT)), summary=[abnormal instance termination].

Wed Sep 21 15:46:48 2016

Instance terminated by USER, pid = 22047

[oracle@mydb01]:[TTREC1] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 21 15:47:34 2016

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 3712904 bytes

Variable Size 1996490872 bytes

Database Buffers 134217728 bytes

Redo Buffers 13062144 bytes

Database mounted.

SQL> /

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID

---------- ------- ------- -------------------- ---------- --------- ----------

1 ONLINE ONLINE FILE NOT FOUND 0 0

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

[oracle@mydb01]:[TTREC1] $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Sep 21 15:48:49 2016

connected to target database: TTREC (DBID=4201939161, not open)

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name TTREC

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1 0 SYSTEM *** /data1/TTREC/datafile/o1_mf_system_cy46np73_.dbf

2 1640 SYSAUX *** /data1/TTREC/datafile/o1_mf_sysaux_cvf5jnh9_.dbf

3 1265 UNDOTBS1 *** /data1/TTREC/datafile/o1_mf_undotbs1_cvf5npon_.dbf

4 4341 USERS *** /data1/TTREC/datafile/o1_mf_users_cvf5oss4_.dbf

5 50 ORINON_TS *** /data1/TTREC/datafile/o1_mf_orinon_t_cvf5lomr_.dbf

6 546 ORAION_TS *** /data1/TTREC/datafile/o1_mf_oraion_t_cvf5jnjb_.dbf

7 10 TBS_TEST *** /data1/TTREC/datafile/o1_mf_tbs_test_cvf5ostw_.dbf

8 1000 ORION_DATA *** /data1/TTREC/datafile/o1_mf_orion_da_cvf5npp4_.dbf

9 50 TESTA *** /data1/TTREC/datafile/o1_mf_testa_cxf1h6kw_.dbf

10 50 TESTB *** /data1/TTREC/datafile/o1_mf_testb_cxnhzbbf_.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1 50 TEMP 50 /data1/TTREC/datafile/o1_mf_temp_cy40hxov_.tmp

RMAN> exit

Recovery Manager complete.

[oracle@mydb01]:[TTREC1] $

Now we will restore our system tablespace to a different location. As I want to use OMF for storing my datafiles – I will set alter system set db_create_file_dest='/data3' followed by set newname for datafile 1 to new from rman run prompt,Where /data3 is the new storage location.

In case if you are not using OMF you can use following syntax.

set newname for datafile 1 to ‘/newlocatoin’;

Its important to do swith datafile all before doing recovery as its update the controlfile with the new location of datafile.

[oracle@mydb01]:[TTREC1] $ ls -ld /data3

drwxr-xr-x 3 oracle oinstall 4096 Sep 21 15:25 /data3

[oracle@mydb01]:[TTREC1] $ cd /data3

[oracle@mydb01]:[TTREC1] $ ll

total 0

[oracle@mydb01]:[TTREC1] $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Sep 21 15:49:51 2016

connected to target database: TTREC (DBID=4201939161, not open)

RMAN> run

{

alter system set db_create_file_dest='/data3';

set newname for datafile 1 to new;

restore tablespace system;

switch datafile all;

recover tablespace system;

alter database open;

}

2> 3> 4> 5> 6> 7> 8> 9>

using target database control file instead of recovery catalog

Statement processed

executing command: SET NEWNAME

Starting restore at 21-SEP-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=365 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=398 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=431 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=464 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 00001 to /data3/TTREC/datafile/o1_mf_system_%u_.dbf

channel ORA_DISK_1: reading from backup piece /t4_nfs/TTREC/database_0argcdi5_1_1

channel ORA_DISK_1: piece handle=/t4_nfs/TTREC/database_0argcdi5_1_1 tag=TAG20160921T153948

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:36

Finished restore at 21-SEP-16

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=923154658 file name=/data3/TTREC/datafile/o1_mf_system_cy47wz7v_.dbf

Starting recover at 21-SEP-16

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

starting media recovery

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

Finished recover at 21-SEP-16

Statement processed

RMAN> exit

Recovery Manager complete.

[oracle@mydb01]:[TTREC1] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 21 15:51:37 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name from v$datafile ;

NAME

------------------------------------------------------

/data3/TTREC/datafile/o1_mf_system_cy47wz7v_.dbf

/data1/TTREC/datafile/o1_mf_sysaux_cvf5jnh9_.dbf

/data1/TTREC/datafile/o1_mf_undotbs1_cvf5npon_.dbf

/data1/TTREC/datafile/o1_mf_users_cvf5oss4_.dbf

/data1/TTREC/datafile/o1_mf_orinon_t_cvf5lomr_.dbf

/data1/TTREC/datafile/o1_mf_oraion_t_cvf5jnjb_.dbf

/data1/TTREC/datafile/o1_mf_tbs_test_cvf5ostw_.dbf

/data1/TTREC/datafile/o1_mf_orion_da_cvf5npp4_.dbf

/data1/TTREC/datafile/o1_mf_testa_cxf1h6kw_.dbf

/data1/TTREC/datafile/o1_mf_testb_cxnhzbbf_.dbf

10 rows selected.

If our /data1 storage is recoverd , we can simpley move the datafile online(in 12c).

SQL> show parameter create;

NAME TYPE VALUE

------------------------------------ ----------- --------

create_bitmap_area_size integer 8388608

create_stored_outlines string

db_create_file_dest string /data3

db_create_online_log_dest_1 string /data1

db_create_online_log_dest_2 string /data2

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

SQL>

SQL> alter session set db_create_file_dest='/data1';

Session altered.

SQL> alter database move datafile 1;

Database altered.

SQL> select name from v$datafile ;

NAME

----------------------------------------------------------

/data1/TTREC/datafile/o1_mf_system_cy4c4zg0_.dbf

/data1/TTREC/datafile/o1_mf_sysaux_cvf5jnh9_.dbf

/data1/TTREC/datafile/o1_mf_undotbs1_cvf5npon_.dbf

/data1/TTREC/datafile/o1_mf_users_cvf5oss4_.dbf

/data1/TTREC/datafile/o1_mf_orinon_t_cvf5lomr_.dbf

/data1/TTREC/datafile/o1_mf_oraion_t_cvf5jnjb_.dbf

/data1/TTREC/datafile/o1_mf_tbs_test_cvf5ostw_.dbf

/data1/TTREC/datafile/o1_mf_orion_da_cvf5npp4_.dbf

/data1/TTREC/datafile/o1_mf_testa_cxf1h6kw_.dbf

/data1/TTREC/datafile/o1_mf_testb_cxnhzbbf_.dbf

10 rows selected.

SQL>

Datafile is successfully moved back to its original location.

Nothing but my learning and experience with Oracle..!

Pages

Monday, October 10, 2016

Creating a PDB Using SQL Plus

Tuesday, September 20, 2016

Recovering from the loss of a system tablespace to a differnet location in OMF

Popular Posts