Posted in Linux

Some Linux commands I use often

Posted on by mardaff

Get the total size of some files:

du -ch /path/patern* | grep total

Get the total number of files being backed up using the log of the RMAN job:

grep -i 'input datafile file number=' rman_BKUP.log | wc -l

Use grep to display the next 2 lines after the pattern you’re looking for (-A= AFTER , B=BEFORE):

grep -i -A 2 'input datafile file number'  rman_BKUP.log
input datafile file number=00001 name=/storage/datafile.dbf
channel ch02: starting piece 1 at 14-MAR-18 15:30:25
channel ch01: finished piece 1 at 14-MAR-18 15:30:32
Advertisements
Posted in Oracle, SQL, Sqlplus

List all running jobs

Posted on by mardaff 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
 
SELECT a.job "Job",
       a.sid,
       a.failures "Failures",       
       Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date",      
       Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date"             
FROM   dba_jobs_running a
/
Posted in clone, Oracle

How to clone Oracle Software fast and easy

Posted on by mardaff

This is the approach I’m usually taking to clone the Oracle Software from one server to another.

[oracle@PROD product]$ zip -r 11204_clone.zip 11.2.0.4/
 adding: 11.2.0.4/ (stored 0%)
 adding: 11.2.0.4/owm/ (stored 0%)
 adding: 11.2.0.4/owm/jlib/ (stored 0%)
 adding: 11.2.0.4/owm/jlib/owm-images.jar (deflated 2%)

..........

  adding: 11.2.0.4/bin/okinit0 (stored 0%)  adding: 11.2.0.4/bin/okinit0 (stored 0%)  adding: 11.2.0.4/bin/nmozip warning: Permission denied zip warning: could not open for reading: 11.2.0.4/bin/nmo  adding: 11.2.0.4/bin/agtctlO (stored 0%)  adding: 11.2.0.4/bin/nmcbufp (deflated 65%)  adding: 11.2.0.4/bin/impdpO (stored 0%)

...........

adding: 11.2.0.4/odbc/mesg/oraodbcus.msb (deflated 68%)
 adding: 11.2.0.4/odbc/mesg/oraodbcja.msb (deflated 68%)

zip warning: Not all files were readable
 files/entries read: 40416 (4.9G bytes) skipped: 3 (144K bytes)
[oracle@PROD product]$ exit

Now send the zip file to the target server:

[oracle@PROD software]$ scp /u01/app/oracle/product/11204_clone.zip DEV:/tmp

Create the same oracle path for the ORACLE_HOME

mkdir -p /u01/app/oracle/product

Unzip the archive to previously created folder:

unzip 11204_clone.zip -d /u01/app/oracle/product/

Navigate to this clone folder within the unzipped oracle software:

cd /u01/app/oracle/product/11.2.0.4/clone/bin

[oracle@DEV bin]$ pwd
/u01/app/oracle/product/11.2.0.4/clone/bin
[oracle@DEV bin]$ perl clone.pl ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0.4 ORACLE_HOME_NAME=OH_11204
./runInstaller -clone -waitForCompletion "ORACLE_BASE=/u01/app/oracle" "ORACLE_HOME=/u01/app/oracle/product/11.2.0.4" "ORACLE_HOME_NAME=OH_11204" -silent -noConfig -nowait 
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 20479 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-10-11_03-40-32PM. Please wait ...Oracle Universal Installer, Version 11.2.0.4.0 Production
Copyright (C) 1999, 2013, Oracle. All rights reserved.

You can find the log of this install session at:
 /u01/app/oraInventory/logs/cloneActions2017-10-11_03-40-32PM.log
.................................................................................................... 100% Done.




Installation in progress (Wednesday, October 11, 2017 3:40:39 PM UTC)
.............................................................................. 78% Done.
Install successful

Linking in progress (Wednesday, October 11, 2017 3:40:42 PM UTC)
Link successful

Setup in progress (Wednesday, October 11, 2017 3:41:02 PM UTC)
Setup successful

End of install phases.(Wednesday, October 11, 2017 3:41:24 PM UTC)
WARNING:A new inventory has been created in this session. However, it has not yet been registered as the central inventory of this system.
To register the new inventory please run the script '/u01/app/oraInventory/orainstRoot.sh' with root privileges. 
If you do not register the inventory, you may not be able to update or patch the products you installed.
The following configuration scripts need to be executed as the "root" user.
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/11.2.0.4/root.sh
To execute the configuration scripts:
 1. Open a terminal window
 2. Log in as "root"
 3. Run the scripts
 
The cloning of OH_11204 was successful.
Please check '/u01/app/oraInventory/logs/cloneActions2017-10-11_03-40-32PM.log' for more details.
[oracle@DEV bin]$ /u01/app/oracle/product/11.2.0.4/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2017, Oracle Corporation. All rights reserved.




Oracle Home : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oraInventory
 from : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-10-11_15-43-56PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/lsinv/lsinventory2017-10-11_15-43-56PM.txt

Execute the 2 scripts as root and you’re done:

/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/11.2.0.4/root.sh

End of story.

Posted in Dataguard, Oracle

ORA-16631: operation requires shutdown of database or instance

Posted on by mardaff

On short, after upgrading one of my databases from 11203 to 11204, I’ve noticed that the standby database was in a DISABLED state:

 

DGMGRL> show configuration

Configuration - DG_CONFIG

Protection Mode: MaxPerformance
 Databases:
DB_PRI- Primary database
 DB_STANDBY - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

 

I tried to set it to ENABLED state like this:

DGMGRL> enable database 'DB_STANDBY'
Error: ORA-16631: operation requires shutdown of database or instance ""

Failed.
DGMGRL> exit

To fix this, I’ve found the following document on Metalink:

Ora-16631: Operation Requires Shutdown Of Database Or Instance “” On Physical Standby (Doc ID 1258074.1)

DGMGRL> show configuration

Configuration - DG_CONFIG

Protection Mode: MaxPerformance
 Databases:
 DB_PRI - Primary database
 DB_STANDBY - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> edit database 'DB_STANDBY' set state='ONLINE';
Error: ORA-16635: network connection failed during transmission

Failed.
DGMGRL> show configuration

Configuration - DG_CONFIG

Protection Mode: MaxPerformance
 Databases:
 DB_PRI - Primary database
 DB_STANDBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

 

Posted in Oracle, SQL, Sqlplus

ORA-01450 maximum key length (3118) exceeded

Posted on by mardaff

All of a sudden, deploying a new version of the apps, I got this error. The fix is simple. The error is related to the block size of the tablespace where you want to deploy your object. In my case it was about the creation of an index an the key was exceeding the size of the default block size of the default tablespace. All I had to do is to enable the 16K block size and to create a new tablespace using the 16k block size.

SQL> ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "TBS_DEFAULT";
ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "TBS_DEFAULT"
*
ERROR at line 1:
ORA-01450: maximum key length (3118) exceeded

SQL> show parameter db_16

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
SQL> alter system set db_16k_cache_size=500M;

System altered.

SQL> CREATE TABLESPACE "NEW_TBS" DATAFILE ...... SIZE 100M BLOCKSIZE 16K;

Tablespace created.

SQL> ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "NEW_TBS";

Table altered.

SQL>