Oracle

Rollback applied patch ?

Sometimes a patch is applied to the system may need to take back because of its effect. In this case, rollback is performed as follows.

$ORACLE_HOME/OPatch/opatch rollback -id Patch_id

For example :

$ORACLE_HOME/OPatch/opatch rollback -id 2435623

Advertisements
Standard
Oracle

How to Find Character Set?

Oracle recommends Unicode AL32UTF8 as the database character set.

Character Set Control:

SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

PARAMETER                     VALUE

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

NLS_CHARACTERSET       AL32UTF8

It should return the value AL32UTF8.

Another option is to run the following SQL :

SQL> select * from nls_database_parameters;

PARAMETER VALUE
 ------------------------------ -------------------- ----------
 NLS_RDBMS_VERSION 12.1.0.2.0
 NLS_NCHAR_CONV_EXCP FALSE
 NLS_LENGTH_SEMANTICS BYTE
 NLS_COMP BINARY
 NLS_DUAL_CURRENCY $
 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
 NLS_TIME_FORMAT HH.MI.SSXFF AM
 NLS_SORT BINARY
 NLS_DATE_LANGUAGE AMERICAN
 NLS_DATE_FORMAT DD-MON-RR
 NLS_CALENDAR GREGORIAN
 NLS_NUMERIC_CHARACTERS.,
 NLS_NCHAR_CHARACTERSET AL16UTF16
 NLS_CHARACTERSET AL32UTF8
 NLS_ISO_CURRENCY AMERICA
 NLS_CURRENCY $
 NLS_TERRITORY AMERICA
 NLS_LANGUAGE AMERICAN

20 rows selected.

SQL>

The most important settings have been highlighted, which are:

  • NLS_CHARACTERSET = AL32UTF8
  • NLS_NCHAR_CHARACTERSET = AL16UTF16
  • NLS_RDBMS_VERSION = 12.1.0.2.0

Character set is used by CHAR, VARCHAR2 and CLOB data types and national character set is used by NCHAR, NVARCHAR2 and NCLOB data types.

  • Use char when the sizes of the column data entries are consistent.
  • Use varchar when the sizes of the column data entries vary considerably.
  • Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
  • A CLOB (character large object) value can be up to 2,147,483,647 characters long.

Screen Shot 2017-08-17 at 18.45.37

Standard
Oracle

Cloning an Oracle Home DB 12C

Perform the following to clone an Oracle home

  1. If you install patches, then check their status using the following

$ cd $ORACLE_HOME/OPatch
$ opatch lsinventory

[oracle@ermankara OPatch]$ /u01/app/oracle/product/12.1.2/db/OPatch/opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.9

Copyright (c) 2017, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/12.1.2/db

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/oracle/product/12.1.2/db/oraInst.loc

OPatch version    : 12.2.0.1.9

OUI version       : 12.1.0.2.0

Log file location : /u01/app/oracle/product/12.1.2/db/cfgtoollogs/opatch/opatch2017-08-11_10-52-37AM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.1.2/db/cfgtoollogs/opatch/lsinv/lsinventory2017-08-11_10-52-37AM.txt

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

Local Machine Information::

Hostname: ermankara.localdomain

ARU platform id: 226

ARU platform description:: Linux x86-64

Installed Top-level Products (1): 

Oracle Database 12c                                                  12.1.0.2.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  24732082     : applied on Thu Jul 20 20:45:37 EEST 2017

Unique Patch ID:  20904347

Patch description:  "Database Patch Set Update : 12.1.0.2.170117 (24732082)"

   Created on 21 Dec 2016, 07:15:01 hrs PST8PDT

Sub-patch  24006101; "Database Patch Set Update : 12.1.0.2.161018 (24006101)"

Sub-patch  23054246; "Database Patch Set Update : 12.1.0.2.160719 (23054246)"

Sub-patch  22291127; "Database Patch Set Update : 12.1.0.2.160419 (22291127)"

Sub-patch  21948354; "Database Patch Set Update : 12.1.0.2.160119 (21948354)"

Sub-patch  21359755; "Database Patch Set Update : 12.1.0.2.5 (21359755)"

Sub-patch  20831110; "Database Patch Set Update : 12.1.0.2.4 (20831110)"

Sub-patch  20299023; "Database Patch Set Update : 12.1.0.2.3 (20299023)"

Sub-patch  19769480; "Database Patch Set Update : 12.1.0.2.2 (19769480)"

   Bugs fixed:

     19309466, 19902195, 18250893, 21172913, 17655240, 21266085, 19028800

     19035573, 19366375, 18456643, 24523374, 19289642, 18845653, 19915271

     21291274, 18417036, 20475845, 22148226, 22528741, 19243521, 19658708

     21153266, 19326908, 19597583, 17414008, 20897759, 19174430, 22046677

     22243719, 24825843, 21960504, 19054077, 22657942, 20688221, 20428621

     21899588, 21387964, 13542050, 19723336, 19835133, 17532734, 19333670

     21373473, 23260854, 19687159, 14643995, 20977794, 20734332, 19012119

     19869255, 17551063, 18681056, 23324000, 19676012, 20588502, 19520602

     19841800, 19439759, 20245930, 19303936, 19001359, 21476308, 22916353

     19393542, 23533524, 21099555, 22353346, 19141838, 19644859, 21915719

     21106027, 19908836, 21421886, 22529728, 19358317, 19134173, 19524158

     20447445, 21188584, 19335438, 20803014, 23025340, 19390567, 19058490

     18799993, 19769480, 21097043, 19475971, 21225209, 19284031, 20677396

     19450314, 19016730, 18967382, 20919320, 22075064, 20347562, 20348653

     22551446, 19896336, 24812585, 20048359, 21896069, 19524384, 22496904

     16439813, 18354830, 20596234, 20440930, 20936905, 22022760, 19171086

     23197103, 17867700, 19791273, 18990023, 21241829, 19591608, 22296366

     20173897, 17210525, 18914624, 19571367, 21260431, 19501299, 20425790

     20181030, 25056052, 19124589, 19402853, 19888853, 16870214, 17722075

     18202441, 18743542, 19001390, 20882568, 19081128, 22173980, 21875360

     19178851, 19149990, 20951038, 22168163, 19606174, 16777441, 25161298

     20848335, 24308635, 19791377, 19050649, 19382851, 20920911, 20528052

     22762046, 19189525, 19469538, 20598042, 18988834, 22458049, 19176326

     19048007, 19238590, 20387265, 21263635, 24397438, 22160989, 20356733

     22380919, 18499088, 18436647, 20825533, 18952989, 21059919, 22294260

     20794034, 19468347, 20284155, 19883092, 20657441, 17365043, 21285458

     20952966, 19176223, 21300341, 18288842, 19699191, 22353199, 24437510

     22083366, 20669434, 18964978, 19577410, 21373076, 14283239, 19931709

     21239530, 20880215, 20798891, 18122373, 20043616, 23124895, 19013183

     18856999, 21450666, 18893947, 21133343, 22695831, 21196809, 21354456

     19587324, 20464614, 24808595, 22062026, 19189317, 18307021, 21917884

     19708632, 20711718, 20134339, 22815955, 22077517, 18973548, 24690216

     22507210, 20101006, 19197175, 18797519, 23501901, 19597439, 21387128

     19180770, 19879746, 19354335, 21785691, 19730508, 22366558, 6599380

     24285405, 18964939, 21575362, 22365117, 21542577, 22507234, 23170620

     20627866, 20124446, 16923858, 19518079, 19371175, 20466322, 18940497

     19074147, 22923409, 20842388, 17274537, 21380789, 19154375, 20474192

     19044962, 19532017, 19662635, 22374754, 20560611, 18899974, 18705806

     20471920, 19052488, 22024071, 20074391, 22809871, 21184223, 23089357

     19157754, 21220620, 24316947, 18921743, 19865345, 19065677, 19065556

     19018447, 19018206, 19777862, 22223463, 19304354, 21322887, 20879889

     20890311, 19578350, 20869721, 21756699, 20217801, 18819908, 19363645

     21072646, 20898391, 19291380, 20373598, 19248799, 20031873, 19155797

     19279273, 18886413, 18618122, 20922010, 19990037, 20509482, 24739928

     18966843, 19077215, 21526048, 20704450, 19068970, 21620471, 19023822

     19670108, 19068610, 20267166, 21756677, 20476175, 18549238, 20328248

     18674047, 19385656, 18849537, 19684504, 20315311, 20899461, 19308965

     19143550, 19024808, 18948177, 19468991, 20009833, 20868862, 20466628

     21756661, 20397490, 19706965, 22178855, 19604659, 16359751, 19032777

     19329654, 19928926, 20212067, 20603378, 18974476, 23314180, 20859910

     19307662, 21847223, 21668627, 20281121, 19075256, 20877664, 19487147

     19076343, 24577566, 19430401, 19676905, 20844426, 20904530, 20925795

     20441797, 21296029, 21629064, 21442094, 23229229, 22865673, 20708701

     19280225, 21315084, 18840932, 18740837, 20294666, 19989009, 18191823

     21517440, 19174942, 17319928, 19703301, 20122715, 18411216, 20117253

     19258504, 21188532, 24386767, 17890099, 21649497, 16887946, 25264559

     18791688, 19721304, 22092979, 19490948, 19619732, 20879709, 20165574

     19272708, 19547370, 19818513, 20139391, 24693382, 20228093, 21281532

     19978542, 22165897, 22359063, 19409212, 19805359, 19461270, 19434529

     18799063, 18990693, 20378086, 21246723, 21632821, 20831538, 20424899

     20361671, 18674024, 19689979, 20235511, 23220453, 19873610, 16619249

     20562898, 21091431, 19440586, 22757364, 18610915, 22175564, 19561643

     19399918, 19195895, 20832516, 20017509, 20907061, 21665897, 20746251

     19183343, 21787056, 21273804, 19067244, 18043064, 21329301, 18885870

     19536415, 17835294, 20446883, 24413809, 21744290, 18254023, 20591183

     20413820, 19185876, 22465352, 20558005, 20402832, 19627012, 20093776

     18909599, 20618595, 16863642, 19639483, 19315691, 20331945, 19512341

     21479753, 19637186, 19174521, 19022470, 18607546, 20401975, 18306996

     18851894, 19649152, 20581111, 19201867, 20318889, 20936731, 21060755

     21294938, 20898997, 18510194, 19534363, 19188927

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

OPatch succeeded.

[oracle@ermankara OPatch]$

2. Create a ZIP or TAR file with the Oracle home (but not the Oracle base) directory.

zip -r name.zip ORACLE_HOME_LOCATION

[oracle@ermankara ~]$ zip -r ermandbhome.zip /u01/app/oracle/product/12.1.2/db/

zip warning: name not matched: /u01/app/oracle/product/12.1.2/db/lib/libocci.so

  adding: u01/app/oracle/product/12.1.2/db/ (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/rest/ (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/rest/ords.2.0.7.101.03.51.zip (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/rest/cloud_patch.zip (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/install/ (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/install/modmakedeps.pl (deflated 61%)

  adding: u01/app/oracle/product/12.1.2/db/install/readme.txt (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/install/make.log (deflated 94%)

  adding: u01/app/oracle/product/12.1.2/db/install/checkpoints/ (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/install/envVars.properties (deflated 54%)

  adding: u01/app/oracle/product/12.1.2/db/install/.ee (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/install/oratab (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/ (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.hadoopcore.sh (deflated 18%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.network.sh (deflated 18%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.options.sh (deflated 18%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.install.deinstalltool.sh (deflated 39%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.assistants.deconfig.sh (deflated 18%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.rdbms.install.ovabplugin.sh (deflated 18%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.tfa.sh (deflated 17%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.rdbms.install.seeddb.sh (deflated 18%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.assistants.netca.client.sh (deflated 18%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.rdbms.install.seeddb.sample_schema.sh (deflated 18%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.rdbms.install.common.sh (deflated 39%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.server.sh (deflated 33%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.rsf.sh (deflated 13%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.rdbms.install.plugins.sh (deflated 18%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.dbtoolslistener.sh (deflated 18%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.assistants.acf.sh (deflated 18%)

  adding: u01/app/oracle/product/12.1.2/db/install/permissions/oracle.assistants.server.sh (deflated 18%)

  adding: u01/app/oracle/product/12.1.2/db/install/portlist.ini (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/install/jlib/ (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/install/jlib/instclient.jar (deflated 6%)

  adding: u01/app/oracle/product/12.1.2/db/install/jlib/instcrs.jar (deflated 5%)

  adding: u01/app/oracle/product/12.1.2/db/install/jlib/instcommon.jar (deflated 6%)

  adding: u01/app/oracle/product/12.1.2/db/install/jlib/installcommons_1.0.0b.jar (deflated 15%)

  adding: u01/app/oracle/product/12.1.2/db/install/jlib/instdb.jar (deflated 5%)

  adding: u01/app/oracle/product/12.1.2/db/install/orabasetab (deflated 21%)

  adding: u01/app/oracle/product/12.1.2/db/install/chainedInstall/ (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/install/chainedInstall/globalcontext.xml (deflated 92%)

  adding: u01/app/oracle/product/12.1.2/db/install/utl/ (stored 0%)

  adding: u01/app/oracle/product/12.1.2/db/install/utl/unixenv (deflated 55%)

  adding: u01/app/oracle/product/12.1.2/db/install/utl/read.sh (deflated 40%)

  adding: u01/app/oracle/product/12.1.2/db/install/utl/rootmacro.sbs (deflated 53%)

  adding: u01/app/oracle/product/12.1.2/db/install/utl/rootinstall.sh (deflated 61%)

  adding: u01/app/oracle/product/12.1.2/db/install/utl/rootmacro.sh (deflated 53%)

  adding: u01/app/oracle/product/12.1.2/db/install/root_ermankara_2017-07-20_16-38-00.log (deflated 42%)

Do not include the admin, FRA, oradata directories that are under the Oracle base directory. These directories are created in the target installation later, when you create a new database there.

3. Copy the ZIP or TAR file to the root directory of the target computer

[oracle@ermankara]$ cd $ORACLE_HOME
[oracle@ermankara db]$ ls

addnode     ccr          crs  db12cc_dbhome.zip  demo         has            inventory  jlib  md       oc4j    OPatch.old   ord                                perl     QOpatch  relnotes   slax          sqlplus   ucp  xdk

apex        cdata        css  dbs                diagnostics  hs             javavm     ldap  mgw      odbc    opmn         oui                                plsql    R        rest       sqldeveloper  srvm      usm

assistants  cfgtoollogs  ctx  dc_ocm             dmu          install        jdbc       lib   network  olap    oracore      owm                                plugins  racg     root.sh    sqlj          suptools  utl

bin         clone        cv   deinstall          dv           instantclient  jdk        log   nls      OPatch  oraInst.loc  p25171037_121020_Linux-x86-64.zip  precomp  rdbms    scheduler  sqlpatch      sysman    wwg

[oracle@ermankara db]$ scp -r db12cc_dbhome.zip oracle@newdb:/.

4. Extract the ZIP or TAR file content using the following command:

[oracle@newdb]$ unzip db12cc_dbhome.zip

 

[oracle@newdb ~]$ cd /u01/app/oracle/product/12.1.2/db/clone/bin/

[oracle@newdb bin]$ ls

clone.pl  clone.pl.sbs  prepare_clone.pl

run the clone.pl file for the unzipped Oracle home. Use the following syntax:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_BASE="/u01/app/oracle/" ORACLE_HOME="/u01/app/oracle/product/12.1.0/db"
OSDBA_GROUP=dba OSOPER_GROUP=oper -defaultHomeName

5. To configure the connection information for the new database, run Net Configuration Assistant:

Screen Shot 2017-07-16 at 16.17.44Screen Shot 2017-07-16 at 16.17.09Screen Shot 2017-07-16 at 16.17.27Screen Shot 2017-07-16 at 16.17.21Screen Shot 2017-07-16 at 16.17.15Screen Shot 2017-07-16 at 16.17.33Screen Shot 2017-07-16 at 16.17.38

6. To create a new database for the newly cloned Oracle home, run Database Configuration Assistant:

[oracle@newdb ]$ cd $ORACLE_HOME/bin
[oracle@newdb bin]$ ./dbca

Standard
Troubleshooting

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

OS: Oracle Linux 6.8
Oracle Database: 12c
Problem Description:

SQL> startup
ORACLE instance started.
Total System Global Area 1644167168 bytes
Fixed Size     2925024 bytes
Variable Size 1056968224 bytes
Database Buffers   570425344 bytes
Redo Buffers   13848576 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open 

In this case, the following operations must be performed.

SQL> shutdown abort;
SQL> startup mount;
$ rman target / 
RMAN> recover database;
SQL> alter database open resetlogs;
RMAN> reset database;

After the commands are executed successfully, the database is opened again.

SQL> startup
ORACLE instance started.
Total System Global Area 1644167168 bytes
Fixed Size     2925024 bytes
Variable Size 1056968224 bytes
Database Buffers   570425344 bytes
Redo Buffers   13848576 bytes
Database mounted.
Database opened.
SQL>
Standard
RMAN

RMAN > could not access datafile Error

This error is occur when RMAN is not able to access the desired datafile.

RMAN>  backup database skip inaccessible;
 Starting backup at 10-AUG-17
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=7 device type=DISK
 could not access datafile 10
 skipping inaccessible file 10

Solutions :

RMAN> restore datafile 10;
Starting restore at 10-AUG-17
using channel ORA_DISK_1
creating datafile file number=10 name=/data/DB12CC/datafile/dummy.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 10-AUG-17
Standard
Oracle

Creating Control File in oracle database

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘/home/oracle/dbscripts/control_file.sql’;

Database altered.

SQL>exit

Now open your control_file.sql

[oracle@ermankara dbscripts]$ cat control_file.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “DB12CC” NORESETLOGS  NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
‘/data/DB12CC/onlinelog/o1_mf_1_dq1dsxry_.log’,
‘/reco/DB12CC/onlinelog/o1_mf_1_dq1dsxvb_.log’
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
‘/data/DB12CC/onlinelog/o1_mf_2_dq1dsy3t_.log’,
‘/reco/DB12CC/onlinelog/o1_mf_2_dq1dsy5p_.log’
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
‘/data/DB12CC/onlinelog/o1_mf_3_dq1dsyf4_.log’,
‘/reco/DB12CC/onlinelog/o1_mf_3_dq1dsyg2_.log’
) SIZE 50M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘/data/DB12CC/datafile/o1_mf_system_dq1dqmhx_.dbf’,
‘/data/DB12CC/datafile/o1_mf_denemene_dr5p3o6t_.dbf’,
‘/data/DB12CC/datafile/o1_mf_sysaux_dq1dpjdm_.dbf’,
‘/data/DB12CC/datafile/o1_mf_undotbs1_dq1ds1qg_.dbf’,
‘/data/DB12CC/datafile/o1_mf_example_dq1dt1pc_.dbf’,
‘/data/DB12CC/datafile/o1_mf_users_dq1ds0nv_.dbf’,
‘/u01/app/oracle/erman.dbf’,
‘/u01/app/oracle/product/12.1.2/db/dbs/bigtbs_f1.dat’,
‘/data/DB12CC/datafile/o1_mf_omf_ts2_dr5q53y7_.dbf’,
‘/data/DB12CC/datafile/dummy.dbf’
CHARACTER SET AL32UTF8
;
Standard
lvm

Rename LVM

Hi, It is very easy to rename LVM. Only one command can change LVM name. Just run the below command:

# lvrename /dev/oraclevg/oracle /dev/oraclevg/oraclelv
Standard