DBCA is great tool to create database using GUI , but sometimes when we don’t have access to GUI, we can still create database using DBCA silent mode creation where you need to pass silent option with dbca and some parameter values to create database. In following example we have used template General_Purpose , […]
Monthly Archives: December 2017
RMAN maxpiecesize command
Use of RMAN maxpiecesize command To avoid large size of backupset we can set maxpiecesize of rman backup piece. I have taken following backup without setting any parameter :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
RMAN> backup database; Starting backup at 29-DEC-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=8 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=D:\ORACLE\ORADATA\TEST1\SYSTEM01.DBF input datafile file number=00002 name=D:\ORACLE\ORADATA\TEST1\SYSAUX01.DBF input datafile file number=00003 name=D:\ORACLE\ORADATA\TEST1\UNDOTBS01.DBF input datafile file number=00004 name=D:\ORACLE\ORADATA\TEST1\USERS01.DBF channel ORA_DISK_1: starting piece 1 at 29-DEC-17 channel ORA_DISK_1: finished piece 1 at 29-DEC-17 piece handle=D:\ORACLE\FAST_RECOVERY_AREA\TEST1\BACKUPSET\2017_12_29\O1_MF_NNNDF_TAG20171229T195530_F4DN2VNO_.BKP tag=TAG20171229T195530 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 29-DEC-17 channel ORA_DISK_1: finished piece 1 at 29-DEC-17 piece handle=D:\ORACLE\FAST_RECOVERY_AREA\TEST1\BACKUPSET\2017_12_29\O1_MF_NCSNF_TAG20171229T195530_F4DN4XYX_.BKP tag=TAG20171229T195530 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 29-DEC-17 |
By list backup command we can see all details of backup , here we can see my backup set size is 1013.84M.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 9.36M DISK 00:00:02 29-DEC-17 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20171229T194540 Piece Name: D:\ORACLE\FAST_RECOVERY_AREA\TEST1\BACKUPSET\2017_12_29\O1_MF_NCSNF_TAG20171229T194540_F4DMJGYG_.BKP SPFILE Included: Modification time: 29-DEC-17 SPFILE db_unique_name: TEST1 Control File Included: Ckp SCN: 894345 Ckp time: 29-DEC-17 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 1013.84M DISK 00:00:59 29-DEC-17 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20171229T195530 Piece Name: D:\ORACLE\FAST_RECOVERY_AREA\TEST1\BACKUPSET\2017_12_29\O1_MF_NNNDF_TAG20171229T195530_F4DN2VNO_.BKP List of Datafiles in backup set 2 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 895016 29-DEC-17 D:\ORACLE\ORADATA\TEST1\SYSTEM01.DBF 2 Full 895016 29-DEC-17 D:\ORACLE\ORADATA\TEST1\SYSAUX01.DBF 3 Full 895016 29-DEC-17 D:\ORACLE\ORADATA\TEST1\UNDOTBS01.DBF 4 Full 895016 29-DEC-17 D:\ORACLE\ORADATA\TEST1\USERS01.DBF BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 3 Full 9.36M DISK 00:00:02 29-DEC-17 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20171229T195530 Piece Name: D:\ORACLE\FAST_RECOVERY_AREA\TEST1\BACKUPSET\2017_12_29\O1_MF_NCSNF_TAG20171229T195530_F4DN4XYX_.BKP SPFILE Included: Modification time: 29-DEC-17 SPFILE db_unique_name: TEST1 Control File Included: Ckp SCN: 895040 Ckp time: 29-DEC-17 RMAN> |
now configure […]
ORA-16179 incremental changes to “log_archive_dest_10” not allowed with SPFILE
You might encounter ORA-16179 error while changing the archive destination.
|
1 2 3 4 5 6 |
SQL> alter system set log_archive_dest_10='d:\oracle\arc\dbtest\'; alter system set log_archive_dest_10='d:\oracle\arc\dbtest\' * ERROR at line 1: ORA-32017: failure in updating SPFILE ORA-16179: incremental changes to "log_archive_dest_10" not allowed with SPFILE |
There is syntax error in above command . It should be like log_archive_dest_n [n is a integer from 1 to 31]=’LOCATION=path_for_archive_log’ example :
|
1 2 3 |
SQL> alter system set log_archive_dest_10='LOCATION=d:\oracle\arc\dbtest\'; System altered. |
Thank you for giving your valuable time to read the above information. If you want to be updated with all […]
Database Cloning without RMAN on windows
CREATE INITSRDB.ORA PFILE IN D:\DATABASE\APP FOLDER
|
1 2 |
*.db_name='srdb2' *.control_files='D:\DATABASE\APP\SHRIRAM\REDOLOGS\srdb2\CONTROLFILE\O1_srdb2.CTL' |
2. CREATE DESTINATION FOLDER DATAFILES FOLDER ONLINE REDOLOGS FOLDER CONTROLFILE FOLDER 3. CREATE SERVICE USING ORADIM
|
1 |
ORADIM -NEW -SID SRDB2 |
NOTE: CHECK WHETHER THE SERVICE IS RUNNING OR NOT. 4. COPY ALL THE DATAFILES, REDOLOGS TO DESTINATION FOLDER AS IT IS THE CASE OF DATABASE CLONING WITHOUT RMAN 5. GO TO […]
Connecting PDB in Oracle 12c
To connect as sys or other elevated local users, use the same methods as below, but use ‘as sysdba‘ where necessary Method 1:
|
1 |
alter session set container = pdbName; |
Method 2:For other methods, you need to know the service-name for the PDB: Get the service name for the PDB:
|
1 2 3 |
SELECT name || ' '|| pdb FROM v$services ORDER BY name; |
or by doing “lsnrctl services LISTENER” and locating service for […]
Recover a datafile from copy
Let’s recover if some datafile is unavailable. Let’s delete users01.dbf datafile which is in USERS tablespace.
|
1 2 3 4 5 |
[oracle@node214 DB11G]$ pwd /u01/app/oracle/oradata/DB11G [oracle@node214 DB11G]$ [oracle@node214 DB11G]$ mv users01.dbf users01.dbf.bak #as backup file. [oracle@node214 DB11G]$ |
Let’s try to create a table name A in tablespace Users.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> create table a(id number) tablespace USERS * ERROR at line 1: SQL> select file#,name from v$datafile; FILE# NAME ---------- ------------------------------------------------------------ 1 /u01/app/oracle/oradata/DB11G/system01.dbf 2 /u01/app/oracle/oradata/DB11G/sysaux01.dbf 3 /u01/app/oracle/oradata/DB11G/undotbs01.dbf 4 /u01/app/oracle/oradata/DB11G/users01.dbf 5 /u01/app/oracle/oradata/DB11G/example01.dbf 6 /u01/app/oracle/oradata/DB11G/test_assm01.dbf 7 /u01/app/oracle/oradata/DB11G/test_mssm01.dbf 8 /stripe/d1/system02.dbf 8 rows selected. SQL> |
We need to restore and recover the datafile. Restore means copy from backup, recover means applying changes since the backup is taken.
|
1 2 3 4 |
SQL> alter database datafile 4 offline; Database altered. SQL> |
Restore datafile:
|
1 2 |
[oracle@node214 DB11G]$ mv users01.dbf.bak users01.dbf [oracle@node214 DB11G]$ |
[…]
Recover controlfile
Controlfiles saved at least in 2 locations. If one of them is corrupted then you can copy from other one after shutdown database. In this case I will show you how to recover from autobackup via RMAN. So you need CONFIGURE CONTROLFILE AUTOBACKUP ON; in RMAN and you should have full backup database. It can […]
Difference between .bashrc and .bash_profile
Difference Between .bashrc and .bash_profile : We use .bashrc and .bash_profile frequently to set environment for Oracle. Here is the difference between both files ,which file is used when . location of .bashrc : /home/username/.bashrc location of .bash_profile : /home/oracle/.bash_profile .bashrc : This file is executed for non-login shells . .bash_profile : This file is executed […]
Oracle Engineered System MiniCluster S7-2 installation, step by step, short info
Here , I will write about how to initial software setup MiniCluster S7-2. (short guide) Pre-Installation Checklist DNS server NTP server at least 12 ip addresses for initial setup. Additional 7 ip addresses for 2 nodes RAC setup. (3 scan , 2 vm, 2 vip) More IP addresses for additional virtual machines or more grid […]
ESTIMATE in Oracle Expdp
The value for parameter ESTIMATE is either BLOCKS (default) or STATISTICS. BLOCKS: The estimated space used is calculated by multiplying the number of database blocks used by the target objects with the appropriate block sizes. It is the least accurate because of: The table was created with a much bigger initial extent size than was needed for […]