Oracle

Showing posts with label Oracle. Show all posts

Monday, August 22, 2016

How to rename datafiles in Oracle 11g

10:00 AM Posted by Vtgdb No Comments

[Problem]

I want to rename datafile E:\ORACLE\VNPT\DATAFILE\O1_MF_EXAMPLE_CVNONK37_.DBF to E:\ORACLE\VNPT\DATAFILE\EXAMPLE.DBF

[FIX]

D:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 22 09:48:56 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

E:\ORACLE\VNPT\DATAFILE\O1_MF_SYSTEM_CVNOJMJ1_.DBF
E:\ORACLE\VNPT\DATAFILE\O1_MF_SYSAUX_CVNOJXF6_.DBF
E:\ORACLE\VNPT\DATAFILE\O1_MF_UNDOTBS1_CVNOK3LH_.DBF
E:\ORACLE\VNPT\DATAFILE\O1_MF_USERS_CVNOK42T_.DBF
E:\ORACLE\VNPT\DATAFILE\O1_MF_EXAMPLE_CVNONK37_.DBF

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host move E:\ORACLE\VNPT\DATAFILE\O1_MF_EXAMPLE_CVNONK37_.DBF E:\ORACLE\VNP
T\DATAFILE\EXAMPLE.DBF;
1 file(s) moved.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 436208080 bytes
Database Buffers 92274688 bytes
Redo Buffers 5804032 bytes
Database mounted.
SQL> alter database rename file 'E:\ORACLE\VNPT\DATAFILE\O1_MF_EXAMPLE_CVNONK37_
.DBF' to 'E:\ORACLE\VNPT\DATAFILE\EXAMPLE.DBF';

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

E:\ORACLE\VNPT\DATAFILE\O1_MF_SYSTEM_CVNOJMJ1_.DBF
E:\ORACLE\VNPT\DATAFILE\O1_MF_SYSAUX_CVNOJXF6_.DBF
E:\ORACLE\VNPT\DATAFILE\O1_MF_UNDOTBS1_CVNOK3LH_.DBF
E:\ORACLE\VNPT\DATAFILE\O1_MF_USERS_CVNOK42T_.DBF
E:\ORACLE\VNPT\DATAFILE\EXAMPLE.DBF

SQL>

Thursday, May 26, 2016

How to fix the Account Locked (Timed) Oracle

3:22 PM Posted by Vtgdb 1 Comments

Step 1: Check status of Account:

select username, account_status,profile from dba_users where username='APPUSER';

Step 2: Create an profile

CREATE PROFILE APP_DEFAULT LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;

Step 3: Grant the profile to user 'APPUSER':

ALTER USER appusr PROFILE app_default;

ALTER USER appusr ACCOUNT UNLOCK;

Step 4: Check againt status

select username, account_status,profile from dba_users where username='APPUSER';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
PROFILE
------------------------------
APPUSER OPEN
APP_DEFAULT

Monday, March 14, 2016

Install or upgrade Oracle APEX 5.0.3 for ORACLE DATABASE 12C on Linux

10:22 AM Posted by Vtgdb No Comments

Step 1: Create tablespace

create tablespace apex datafile '/u01/app/oracle/oradata/PTUD/datafile/apex.dbf' size 100M autoextend on next 10M;

Step 2: Download Oracle Apex 5.0.3.00.03 from Oracle or My Google Drive

http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

https://drive.google.com/open?id=0B7a3DEnQ0-uzMVFwVnRFZkRfYlk

Step 3: extract file apex_5.0.3_en.zip to directory: /u01/setup

[oracle@VTGDB.COM ~]$cd /u01/setup

[oracle@VTGDB.COM setup]$unzip apex_5.0.3_en.zip

[oracle@VTGDB.COM setup]$ ls

apex apex_5.0.3_en.zip

Step 4: Go to the apex directory ( /u01/setup/apex), connect sys as sysdba then run sql scripts:

[oracle@VTGDB.COM ~]$cd /u01/setup/apex

[oracle@VTGDB.COM ~]$sqlplus / as sysdba

SQL > @apexins.sql apex apex temp /i/

Step 5: Run script apex_epg_config.sql /u01/setup

[oracle@VTGDB.COM ~]$cd /u01/setup/apex

[oracle@VTGDB.COM apex]$sqlplus / as sysdba

SQL > @apex_epg_config.sql /u01/setup

Step 6: Unlock user account oracle

[oracle@VTGDB.COM apex]$sqlplus / as sysdba

SQL > alter user APEX_PUBLIC_USER account unlock;

SQL > alter user APEX_050000 account unlock;

SQL > alter user ANONYMOUS account unlock;

Step 7: Verifying Oracle XML DB HTTP Port and set it to 8080

[oracle@VTGDB.COM apex]$sqlplus / as sysdba

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

GETHTTPPORT

-----------

SQL> EXEC DBMS_XDB.sethttpport(8080);

PL/SQL procedure successfully completed.

Step 8: Configure Apex application

[oracle@VTGDB.COM apex]$ sqlplus / as sysdba

SQL> @apxconf.sql

PORT

----------

8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.

Default values are in brackets [ ].

Press Enter to accept the default value.

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

This script can be used to change the password of an Application Express

instance administrator. If the user does not yet exist, a user record will be

created.

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

Enter the administrator's username [ADMIN]

User "ADMIN" exists.

Enter ADMIN's email [ADMIN]

Enter ADMIN's password []

Changed password of instance administrator ADMIN.

Enter a port for the XDB HTTP listener [ 8080]

...changing HTTP Port

SQL>

Step 9: Access your Apex application web:

http://YOUR_HOST:8080/apex

workspace: internal

username: ADMIN ( uppercase)

pass: YOUR_PASS

After you login successfully, you have your interface following:

Thursday, March 3, 2016

How to select columns with null values

5:21 PM Posted by Vtgdb No Comments

Connect with your schema and run the sql scripts

SET LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000;
SELECT t.table_name,
t.column_name
FROM user_tab_columns t
WHERE t.nullable = 'Y'
AND t.num_distinct = 0 AND t.table_name='TABLE_NAME';

For example:

SET LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000;
SELECT t.table_name,
t.column_name
FROM user_tab_columns t
WHERE t.nullable = 'Y'
AND t.num_distinct = 0 AND t.table_name='SUBS';

Result:

TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
SUBS FINISH_REASON_ID
SUBS CONTRACT_VALID
SUBS PROMOTION_VALID
SUBS NOTES
SUBS FIRST_SHOP_CODE
SUBS FIRST_EXP_DATE
SUBS SECOND_EXP_DATE
SUBS PROM_VALID_DATETIME
SUBS LAST_USERNAME
SUBS PASSWORD
SUBS SECRET
SUBS EXPIRE_DATE
SUBS NATIONALITY
SUBS SELLING_PRICE
SUBS CENTER_CODE
SUBS ZONE_ID
SUBS ZONE_ADDRESS
SUBS ZONE_AREA_CODE
SUBS PRODUCT_ID
SUBS NUM_REFILLS
SUBS UPDATE_STATUS
SUBS CUST_TYPE
SUBS CONTACT_NAME
SUBS ZONE_CODE
SUBS NUM_CHANGE_METZONE
SUBS LAST_CHANGE_METZONE
SUBS AREA_CODE_ACTIVE
SUBS CELL_ID_ACTIVE
SUBS OLD_EMP_CODE
SUBS EMP_CODE_BK
SUBS FLAG
SUBS SERIAL_MACHINE
SUBS COUNT_UPDATE_INFOR
SUBS UPDATE_DATE
SUBS G_TYPE
SUBS TYPE_MACHINE
SUBS NUM_3G
SUBS SOURCE_TYPE
SUBS ID_ADDRESS
SUBS FAX
SUBS DATA
SUBS ID_NO
SUBS ID_ISSUE_PLACE
SUBS CONTRACT_NO
SUBS CONTRACT_TYPE
SUBS CONTRACT_DATE
SUBS REG_REASON_ID
SUBS PROM_PROGRAM_CODE1
SUBS PROM_PROGRAM_CODE2

49 rows selected

How to connect PENTAHO with Oracle Database (12c/11g)

11:29 AM Posted by Vtgdb 5 Comments

Step 1: Set JAVA environment for PENTAHO

My Computer --> Properties( Click right mouse My Computer) --> Advanced system settings --> Environment Variables --> User Variables --> New

PENTAHO_JAVA=C:\Program Files\Java\jdk1.7.0_79\jre\bin\java.exe

PENTAHO_JAVA_HOME=C:\Program Files\Java\jdk1.7.0_79\jre

Step 2: Download OJDBC 11g and 12c for Oracle Database 11g/12c

Accesshttp://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

Example:

12c:

I download JDBC Driver for Oracle Database 12.1.0.2 from http://www.oracle.com/technetwork/database/features/jdbc/default-2280470.html

I choose ojdbc7.jar (3,698,857 bytes) - (SHA1 Checksum: 7c9b5984b2c1e32e7c8cf3331df77f31e89e24c2)

Certified with JDK7 and JDK 8; It contains the JDBC driver classes except classes for NLS support in Oracle Object and Collection types.

11g:

I download JDBC Driver for Oracle Database 11.2.0.4 from http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

I choose ojdbc6.jar (2,739,670 bytes) - (SHA1 Checksum: a483a046eee2f404d864a6ff5b09dc0e1be3fe6c)

Certified with JDK 8, JDK 7 and JDK 6: It contains the JDBC driver classes except classes for NLS support in Oracle Object and Collection types.

Step 3: Copy ojdbc6.jar and ojdbc7.jar to directory PENTAHO_HOME/data-integration/lib

Step 4: Check your connection

Good luck!

Monday, February 29, 2016

Example Creating tablespace in Oracle Database.

9:27 AM Posted by Unknown No Comments

Create undo tablespace:

create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/ORCL/datafile/undotbs2.dbf' size 100M autoextend on next 10M;

Create tablespace:

create tablespace BOC datafile '/u01/app/oracle/oradata/ORCL/datafile/BOC.dbf' size 100M autoextend on next 10M;

Create temporary tablespace:

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '/u01/app/oracle/oradata/ORCL/datafile/temp.dbf' SIZE 2000M;

Wednesday, February 24, 2016

Displaying Backup Information Oracle Database

2:10 PM Posted by Vtgdb No Comments

[oracle@VTGDB-Server app]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Feb 25 13:56:15 2016

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 session_key, input_type, status, start_time, end_time, elapsed_seconds/3600 hrs from v$rman_backup_job_details;

SESSION_KEY INPUT_TYPE STATUS START_TIM END_TIME HRS
----------- ------------- ----------------------- --------- --------- ----------
2 DB FULL COMPLETED 25-FEB-16 25-FEB-16 .331388889
6 DB FULL FAILED 25-FEB-16 25-FEB-16 .004166667
11 DB FULL COMPLETED 25-FEB-16 25-FEB-16 .097777778
22 DB FULL COMPLETED 25-FEB-16 25-FEB-16 .098333333
31 DB FULL RUNNING 25-FEB-16 25-FEB-16 .040555556

Tuesday, February 2, 2016

Clone a Pluggable Database (PDB) Manually

9:52 AM Posted by Vtgdb No Comments

[oracle@VTGDB.BLOGSPOT.COM ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 2 08:34:44 2016

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

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

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OLD_PDB READ WRITE NO

SQL> ALTER PLUGGABLE DATABASE "OLD_PDB" CLOSE IMMEDIATE;
Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE "OLD_PDB" OPEN READ ONLY;
Pluggable database altered.

SQL> CREATE PLUGGABLE DATABASE "NEW_PDB" FROM "OLD_PDB"
STORAGE UNLIMITED TEMPFILE REUSE
FILE_NAME_CONVERT=NONE';
Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE "OLD_PDB" CLOSE IMMEDIATE;

SQL> ALTER PLUGGABLE DATABASE "OLD_PDB" OPEN READ WRITE;

SQL> ALTER PLUGGABLE DATABASE "NEW_PDB" OPEN READ WRITE;

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OLD_PDB READ WRITE NO
4 NEW_PDB READ WRITE NO

Wednesday, January 27, 2016

How to create a role and grant the role for user/schema in Oracle Database

11:12 AM Posted by Vtgdb No Comments

create role "DEV" NOT IDENTIFIED;

grant CREATE TRIGGER to "DEV";
grant CREATE SEQUENCE to "DEV";
grant CREATE TABLE to "DEV";
grant CREATE TYPE to "DEV";
grant CREATE SESSION to "DEV";
grant "CONNECT" to "DEV";
grant CREATE JOB to "DEV";
grant CREATE LIBRARY to "DEV";
grant CREATE INDEXTYPE to "DEV";
grant CREATE ASSEMBLY to "DEV";
grant CREATE PROCEDURE to "DEV";
grant CREATE SYNONYM to "DEV";
grant CREATE VIEW to "DEV";

grant DEV to schema_name;

Friday, January 22, 2016

SQL ORACLE select column name of a table

3:21 PM Posted by Vtgdb No Comments

SET LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000;
SELECT column_name
FROM USER_TAB_COLUMNS
WHERE table_name = 'TABLE_NAME'
order by COLUMN_NAME

Friday, December 25, 2015

Backup Full Database 12c Script

5:48 PM Posted by Vtgdb No Comments

Task 1: Preparing to Use the Oracle Suggested Backup Strategy

To use the Oracle suggested backup strategy, ensure that:

The database is ARCHIVELOG mode
The fast recovery area size is configured, or a default device for storing backups is configured.
You have added a database host user to the OSBACKUPUPDBA operating system group, for operating system authentication.

Task 2: Create the Backup full Script-UNIX and Linux

[oracle@SERVER_DATABASE rman]$ pwd

/u01/app/oracle/rman_backup

[oracle@SERVER_DATABASE rman]$ more backup_script_full.sh

#!/bin/bash

ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1;export ORACLE_HOME

ORACLE_SID=orcl; export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin; export PATH

chk_date=`date +%d`

chk_parity=`expr $chk_date % 2`

export bkdir=/backup11

BACKUP_DIR=$bkdir; export BACKUP_DIR

export DATA_DIR=$BACKUP_DIR/backup_full

export ARC_DIR=$BACKUP_DIR/backup_full

export BACKUP_DATA=$DATA_DIR/$ORACLE_SID"_data_f%t_s%s_s%p"

export BACKUP_ARC=$ARC_DIR/$ORACLE_SID"_arc_f%t_s%s_s%p"

export ORACLE_OWNER=oracle

export DATE=`date +%Y%m%d`

export IP=168_133

########### SCRIPT CHECK BACKUP FAILED############

backup_log_err='/u01/app/monitoring/log/backup_log.err';

backup_log_finish='/u01/app/monitoring/log/backup_log.finish';

/usr/bin/ls -lt /u01/app/rman_backup/log_full > $list_file;

i=1

while read line

let i=$i+1;

if [ $i -gt 2 ]

then

backup_log='/u01/app/rman_backup/log_full/'`echo $line | awk '{print $9}'`;

break;

done < "$list_file"

grep -i error $backup_log > $backup_log_err

grep "Recovery Manager complete" $backup_log > $backup_log_finish

if [ `cat $backup_log_err|wc -l` -gt 0 ]

then

echo 'Backup have error';

exit;

if [ !`cat $backup_log_finish|wc -l` -gt 0 ]

then

echo 'Backup have not finished yet';

exit;

#######################################

cd $BACKUP_DIR

rm -rf backup_full_*

mv backup_full backup_full_$DATE

mkdir -p $DATA_DIR

mkdir -p $ARC_DIR

$ORACLE_HOME/bin/rman target / log /u01/app/rman_backup/log_full/rman_backup_full_$IP"_"$DATE.log <<EOF

RUN {

CROSSCHECK BACKUP OF DATABASE;

CROSSCHECK ARCHIVELOG ALL;

BACKUP AS COMPRESSED BACKUPSET FORMAT '$BACKUP_ARC' ARCHIVELOG ALL DELETE INPUT;

BACKUP AS COMPRESSED BACKUPSET FORMAT '$BACKUP_DATA' DATABASE SKIP READONLY;

SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';

BACKUP AS COMPRESSED BACKUPSET FORMAT '$BACKUP_ARC' ARCHIVELOG ALL DELETE INPUT;

BACKUP CURRENT CONTROLFILE;

}

exit;

EOF

Task 3: Testing the Backup Script

[oracle@SERVER_DATABASE rman]$ chmod 775 backup_script_full.sh

[oracle@SERVER_DATABASE rman]$ nohup ./ backup_script_full.sh &

[oracle@SERVER_DATABASE rman]$ tailf nohup.log

RMAN-00557: could not open MSGLOG "/u01/app/rman_backup/log_full/rman_backup_full_165_145_20151225.log"

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Dec 25 17:05:50 2015

connected to target database:ORCL (DBID=1723703927)

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

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=4018 instance=ptud11 device type=DISK

specification does not match any backup in the repository

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=4018 instance=ptud11 device type=DISK