Skip to content

Archive for

Can we create additional database for Oracle Express Edition?

Can we create new additional database for Oracle Expression Edition? Because No DBCA available in express edition, However we can create database using manual script.

We can follow way for normal database creation manual steps only…

Consider new Database Name is “XETEST”

Step 1:  Create suitable directory for new database.

like below

C:\oraclexe\app\oracle\admin\XETEST
  • adump
  • dpdump
  • pfile
C:\oraclexe\app\oracle\oradata\XETEST

Step 2: Create new instance for new database

       C:\Windows\system32>oradim -new -sid xetest
         Instance created.

Step 3 : Create password file for new database

C:\Windows\system32>orapwd file=C:\oraclexe\app\oracle\product\11.2.0\server\database\PWDXETEST.ora password=sysxe

Step 4 : Create New pfile like below

xe.__db_cache_size=117440512
xe.__java_pool_size=4194304
xe.__large_pool_size=8388608
xe.__oracle_base='C:\oraclexe\app\oracle'#ORACLE_BASE set from environment
xe.__pga_aggregate_target=188743680
xe.__sga_target=566231040
xe.__shared_io_pool_size=92274688
xe.__shared_pool_size=335544320
xe.__streams_pool_size=0
*.audit_file_dest='C:\oraclexe\app\oracle\admin\XETEST\adump'
*.compatible='11.2.0.0.0'
*.control_files='C:\oraclexe\app\oracle\oradata\XETEST\control.dbf'
*.db_name='XETEST'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\flash_recovery_area'
*.diagnostic_dest='C:\oraclexe\app\oracle'###########################################
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XETESTXDB)'
*.job_queue_processes=4
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=AZAR-PC.ace-ins.com)(PORT=1522))'
*.open_cursors=300
*.os_authent_prefix=''
*.pga_aggregate_target=180M
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.sga_target=540M
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Step 5: Stratup nomount stage db using new pfile

.
C:\Windows\system32>SET ORACLE_SID=XETEST

C:\Windows\system32>sqlplus

SQL*Plus: Release 11.2.0.2.0 Beta on Sun May 22 15:23:42 2011

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

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup nomount pfile='d:\temp\initXETEST.ora';
ORACLE instance started.

Total System Global Area  564957184 bytes
Fixed Size                  1384956 bytes
Variable Size             171970052 bytes
Database Buffers          385875968 bytes
Redo Buffers                5726208 bytes

Step 6: Create Database Script  like below

create database XETEST
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\temp\redolog\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'D:\temp\redolog\REDO02.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE'C:\oraclexe\app\oracle\oradata\XETEST\SYSTEM.DBF' size 100m autoextend on
sysaux datafile 'C:\oraclexe\app\oracle\oradata\XETEST\SYSAUX.DBF' size 100m autoextend on
undo tablespace undotbs1 datafile  'C:\oraclexe\app\oracle\oradata\XETEST\UNDOTBS1.DBF' size 100m autoextend on
CHARACTER SET AL32UTF8
;

Step 7: After created it, you can run at sql prompt


SQL> @D:\temp\create.sql

Database created.

Step 8: Now database was created, Check Instance status & Version

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta
PL/SQL Release 11.2.0.2.0 - Beta
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Beta
NLSRTL Version 11.2.0.2.0 - Production

SQL>
The above newly created database is Express edition version.
And also RUN
SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\catalog.sql

SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\catproc.sql

Advertisements

Oracle 11g R1/R2 Real Application Clusters Essentials Book live now

One of My Best Advisor Mr.Syed Jaffar Hussain book has been released. Now It’s on live. It should be useful for who willing to learn more things about RAC High Availability.

You can buy it from follow publication URL.

http://www.packtpub.com/oracle-11g-r1-r2-real-application-clusters-essentials/book#in_detail

What you will learn from this book :

  • Learn how to administer, implement, and manage Oracle 11g RAC environments for real world deployments
  • Discover the key architectural design and installation techniques required to successfully deploy Oracle 11g RAC
  • Effectively manage the complex 11g Clusterware, using key troubleshooting tips and techniques
  • Successfully implement database creation methods, manage the Oracle RAC database and handle workload in your RAC environment efficiently
  • Appropriately plan your backup and recovery strategy
  • Know when and how to upgrade your Oracle RAC environment effectively
  • Discover how to deploy Oracle 11g RAC with complex standard-off-the-shelf systems like Oracle EBS
  • Understand new key features for Oracle 11g R1/R2 RAC and ASM

RMAN-06091: no channel allocated for maintenance (of an appropriate type)

When I tried to delete obsolete backup, it shows following error.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 05/16/2011 14:41:39
RMAN-06091: no channel allocated for maintenance (of an appropriate type)

We need to find out whether tape device or disk type using list backup command.

and then if you want to delete disk type backup files, you can use following command.
RMAN> allocate channel for maintenance type disk;

allocated channel: ORA_MAINT_DISK_2
channel ORA_MAINT_DISK_2: sid=68 devtype=DISK

RMAN> delete obsolete device type disk;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           816    01-MAY-11
  Backup Piece       816    01-MAY-11          J:\BROKER\RMAN\BROKER749986431_S8
28_S1
Backup Set           817    01-MAY-11
  Backup Piece       817    01-MAY-11          J:\BROKER\RMAN\BROKER749986548_S8
29_S1
Backup Set           818    01-MAY-11
  Backup Piece       818    01-MAY-11          J:\BROKER\RMAN\BROKER749986664_S8
30_S1
Backup Set           819    01-MAY-11
  Backup Piece       819    01-MAY-11          J:\BROKER\RMAN\BROKER749986782_S8
31_S1
Backup Set           822    01-MAY-11
  Backup Piece       822    01-MAY-11          D:\ORACLE\PRODUCT\FLASHRECOVERY\B
ROKER\AUTOBACKUP\2011_05_01\O1_MF_S_749988140_6VT1CXPZ_.BKP
Backup Set           829    03-MAY-11
  Backup Piece       829    03-MAY-11          J:\BROKER\RMAN\BROKER750185116_S8
42_S1
Backup Set           830    03-MAY-11
  Backup Piece       830    03-MAY-11          J:\BROKER\RMAN\BROKER750185233_S8
43_S1
Backup Set           831    03-MAY-11
  Backup Piece       831    03-MAY-11          J:\BROKER\RMAN\BROKER750185349_S8
44_S1
Backup Set           859    11-MAY-11
  Backup Piece       859    11-MAY-11          J:\BROKER\RMAN\BROKER750868262_S8
72_S1
Backup Set           860    11-MAY-11
  Backup Piece       860    11-MAY-11          J:\BROKER\RMAN\BROKER750868389_S8
73_S1
Backup Set           863    11-MAY-11
  Backup Piece       863    11-MAY-11          J:\BROKER\RMAN\BROKER750868770_S8
76_S1

Do you really want to delete the above objects (enter YES or NO)?
deleted backup piece
backup piece handle=J:\BROKER\RMAN\BROKER749986431_S828_S1 recid=816 stamp=74998
6432
deleted backup piece
backup piece handle=J:\BROKER\RMAN\BROKER749986548_S829_S1 recid=817 stamp=74998
6549
deleted backup piece
backup piece handle=J:\BROKER\RMAN\BROKER749986664_S830_S1 recid=818 stamp=74998
6666
deleted backup piece
backup piece handle=J:\BROKER\RMAN\BROKER749986782_S831_S1 recid=819 stamp=74998
6783
deleted backup piece
backup piece handle=D:\ORACLE\PRODUCT\FLASHRECOVERY\BROKER\AUTOBACKUP\2011_05_01
\O1_MF_S_749988140_6VT1CXPZ_.BKP recid=822 stamp=749988141
deleted backup piece
backup piece handle=J:\BROKER\RMAN\BROKER750185116_S842_S1 recid=829 stamp=75018
5118
deleted backup piece
backup piece handle=J:\BROKER\RMAN\BROKER750185233_S843_S1 recid=830 stamp=75018
5234
deleted backup piece
backup piece handle=J:\BROKER\RMAN\BROKER750185349_S844_S1 recid=831 stamp=75018
5351
deleted backup piece
backup piece handle=J:\BROKER\RMAN\BROKER750868262_S872_S1 recid=859 stamp=75086
8264
deleted backup piece
backup piece handle=J:\BROKER\RMAN\BROKER750868389_S873_S1 recid=860 stamp=75086
8391
deleted backup piece
backup piece handle=J:\BROKER\RMAN\BROKER750868770_S876_S1 recid=863 stamp=75086
8772
Deleted 11 objects

RMAN>

or If you want TAPE,
rman>allocate channel for maintenance device type 'sbt_tape' PARMS '
Change your tape parms.
See Metalink ID [ ID 567555.1]