SQL> create tablespace rman
2 datafile '/datadir/rman01.dbf'
3 size 100M
4 autoextend on
5 maxsize 10G
6 extent management local;
Tablespace created.
oracle@rman:/home/oracle> sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 19 00:28:16 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user rman
identified by rman
temporary tablespace temp
default tablespace rman
quota unlimited on users
quota unlimited on rman ;
User created.
SQL> GRANT RECOVERY_CATALOG_OWNER TO rman;
Grant succeeded.
oracle@rman:/home/oracle> rman CATALOG rman/rman@RMAN
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 19 00:30:29 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> CREATE CATALOG;
recovery catalog created
oracle@rman:/home/oracle> rman target system/oracle123@TARGETDB CATALOG rman/rman@RMAN
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 19 00:44:23 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges
--------------------------------
Execute the below in target database for above error resolution
SQL> grant sysdba to system;
Grant succeeded.
--------------------------------
oracle@rman:/home/oracle> rman target system/oracle123@TARGETDB CATALOG rman/rman@RMAN
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 19 00:47:07 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TARGETDB (DBID=3477861)
connected to recovery catalog database
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 19 00:47:07 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TARGETDB (DBID=3477861)
connected to recovery catalog database
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>
Monday, December 19, 2011
Monday, August 15, 2011
Create a Restore Point
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=25G;
System altered.
SQL> alter system set db_recovery_file_dest='+DSKGRP';
System altered.
SQL> shu immediate
startup Database closed.
Database dismounted.
SQL> startup mount
ORACLE instance started.
Total System Global Area 6580703232 bytes
Fixed Size 2219168 bytes
Variable Size 3758097248 bytes
Database Buffers 2801795072 bytes
Redo Buffers 18591744 bytes
Database mounted.
SQL> ALTER DATABASE ARCHIVELOG ;
Database altered.
SQL> CREATE RESTORE POINT TEST_REC_PT GUARANTEE FLASHBACK DATABASE;
Restore point created.
SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>startup
ORACLE instance started.
Total System Global Area 6580703232 bytes
Fixed Size 2219168 bytes
Variable Size 3758097248 bytes
Database Buffers 2801795072 bytes
Redo Buffers 18591744 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 320
Next log sequence to archive 322
Current log sequence 322
SQL>
System altered.
SQL> alter system set db_recovery_file_dest='+DSKGRP';
System altered.
SQL> shu immediate
startup Database closed.
Database dismounted.
SQL> startup mount
ORACLE instance started.
Total System Global Area 6580703232 bytes
Fixed Size 2219168 bytes
Variable Size 3758097248 bytes
Database Buffers 2801795072 bytes
Redo Buffers 18591744 bytes
Database mounted.
SQL> ALTER DATABASE ARCHIVELOG ;
Database altered.
SQL> CREATE RESTORE POINT TEST_REC_PT GUARANTEE FLASHBACK DATABASE;
Restore point created.
SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>startup
ORACLE instance started.
Total System Global Area 6580703232 bytes
Fixed Size 2219168 bytes
Variable Size 3758097248 bytes
Database Buffers 2801795072 bytes
Redo Buffers 18591744 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 320
Next log sequence to archive 322
Current log sequence 322
SQL>
Thursday, August 11, 2011
How to enable Automatic ArchiveLog in Oracle 11g Database
To check whether the database is in Archive log mode or not
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination None
Oldest online log sequence 564
Current log sequence 566
Set the archival destination
SQL> alter system set log_archive_dest_1='LOCATION=PRMRYDSK_GRP' scope=both;
System altered.
Shutdown the database and start in Mount state
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 8752304128 bytes
Fixed Size 2213792 bytes
Variable Size 1543506016 bytes
Database Buffers 7180648448 bytes
Redo Buffers 25935872 bytes
Database mounted.
Enable archive log
SQL>alter database archivelog;
Database altered.
Open the database
SQL> alter database open;
Database altered.
Shutdown and startup the DB.(This step is not mandatory)
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 8752304128 bytes
Fixed Size 2213792 bytes
Variable Size 1543506016 bytes
Database Buffers 7180648448 bytes
Redo Buffers 25935872 bytes
Database mounted.
Database opened.
Check whether the automatic archive log mode is enabled
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination PRMRYDSK_GRP
Oldest online log sequence 564
Next log sequence to archive 566
Current log sequence 566
SQL>
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination None
Oldest online log sequence 564
Current log sequence 566
Set the archival destination
SQL> alter system set log_archive_dest_1='LOCATION=PRMRYDSK_GRP' scope=both;
System altered.
Shutdown the database and start in Mount state
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 8752304128 bytes
Fixed Size 2213792 bytes
Variable Size 1543506016 bytes
Database Buffers 7180648448 bytes
Redo Buffers 25935872 bytes
Database mounted.
Enable archive log
SQL>alter database archivelog;
Database altered.
Open the database
SQL> alter database open;
Database altered.
Shutdown and startup the DB.(This step is not mandatory)
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 8752304128 bytes
Fixed Size 2213792 bytes
Variable Size 1543506016 bytes
Database Buffers 7180648448 bytes
Redo Buffers 25935872 bytes
Database mounted.
Database opened.
Check whether the automatic archive log mode is enabled
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination PRMRYDSK_GRP
Oldest online log sequence 564
Next log sequence to archive 566
Current log sequence 566
SQL>
Tuesday, June 21, 2011
ORA-20600: The specified target is in the process of being deleted
Recived the following error while adding a database(which was removed earlier)through Oracle Grid Control 10.2.0.5.
Database Instance Configuration Result
TESTA:
java.sql.SQLException: ORA-20600: The specified target is in the process of being deleted.(target name = TESTA)(target type = oracle_database)(target guid = 48BA0AB2565D68E6437E6F5062A7426F) ORA-06512: at "SYSMAN.TARGETS_INSERT_TRIGGER", line 46 ORA-04088: error during execution of trigger 'SYSMAN.TARGETS_INSERT_TRIGGER' ORA-06512: at "SYSMAN.EM_TARGET", line 2117 ORA-06512: at "SYSMAN.MGMT_TARGET", line 2701 ORA-06512: at line 1
SQL> select TARGET_NAME from mgmt_targets_delete;
TARGET_NAME
-----------
TESTA
The above entry needs to be deleted.For the purose we tried executing the below script, which resulted in an error.
SQL> begin
2 mgmt_admin.delete_target('TESTA','oracle_database');
3 end;
4 /
begin
*
ERROR at line 1:
ORA-20206: Target does not exist:
ORA-06512: at "SYSMAN.MGMT_ADMIN", line 462
ORA-06512: at "SYSMAN.MGMT_ADMIN", line 624
ORA-06512: at line 2
After some google search we could find another proc to achieve the required.
SQL> begin
2 mgmt_admin.delete_target_internal('TESTA','oracle_database');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
Once this was done, we were able to add a new database Grid Control Target.
Database Instance Configuration Result
TESTA:
java.sql.SQLException: ORA-20600: The specified target is in the process of being deleted.(target name = TESTA)(target type = oracle_database)(target guid = 48BA0AB2565D68E6437E6F5062A7426F) ORA-06512: at "SYSMAN.TARGETS_INSERT_TRIGGER", line 46 ORA-04088: error during execution of trigger 'SYSMAN.TARGETS_INSERT_TRIGGER' ORA-06512: at "SYSMAN.EM_TARGET", line 2117 ORA-06512: at "SYSMAN.MGMT_TARGET", line 2701 ORA-06512: at line 1
SQL> select TARGET_NAME from mgmt_targets_delete;
TARGET_NAME
-----------
TESTA
The above entry needs to be deleted.For the purose we tried executing the below script, which resulted in an error.
SQL> begin
2 mgmt_admin.delete_target('TESTA','oracle_database');
3 end;
4 /
begin
*
ERROR at line 1:
ORA-20206: Target does not exist:
ORA-06512: at "SYSMAN.MGMT_ADMIN", line 462
ORA-06512: at "SYSMAN.MGMT_ADMIN", line 624
ORA-06512: at line 2
After some google search we could find another proc to achieve the required.
SQL> begin
2 mgmt_admin.delete_target_internal('TESTA','oracle_database');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
Once this was done, we were able to add a new database Grid Control Target.
Tuesday, June 14, 2011
Convert Standby Database to independent operational DB
We had a requirement to convert the existing standby database to an independent one.
This is the steps that we followed to achieve this.
1.Shutdown the primary database consistently.
2.Do a failover on the standby database by issuing the following command.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
Database altered.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN;
Database altered.
SQL> alter database open ;
Database altered.
In the primary database comment/remove the following parameters:
*.log_archive_config
*.log_archive_dest_2
*.log_archive_dest_state_2='ENABLE'
*.dg_broker_start
*.standby_file_management
The above changes would prevent the log shipping to old standby database and dataguard brocker wont start.
If the "*.dg_broker_start" parameter is set to true, expect the following error while opening the database.
ORA-16649: possible failover to another database prevents this database from
being opened
Once the above changes are made, the database could be started independently.
This is the steps that we followed to achieve this.
1.Shutdown the primary database consistently.
2.Do a failover on the standby database by issuing the following command.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
Database altered.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN;
Database altered.
SQL> alter database open ;
Database altered.
In the primary database comment/remove the following parameters:
*.log_archive_config
*.log_archive_dest_2
*.log_archive_dest_state_2='ENABLE'
*.dg_broker_start
*.standby_file_management
The above changes would prevent the log shipping to old standby database and dataguard brocker wont start.
If the "*.dg_broker_start" parameter is set to true, expect the following error while opening the database.
ORA-16649: possible failover to another database prevents this database from
being opened
Once the above changes are made, the database could be started independently.
Wednesday, June 08, 2011
ORA - 17628
Was recieving the following error during the creation of the standby database on 11.2 Oracle Database.
RMAN-00571: ==================================================
RMAN-00569: ====== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ==================================================
RMAN-03002: failure of Duplicate Db command at 06/07/2011 14:07:03
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on prmy1 channel at 06/07/2011 14:07:03
ORA-17628: Oracle error 19505 returned by remote Oracle server
Check the alert log file at standby location.The software will be trying to access/write into an non-existent directory.
Resolution:
Create the directory and give the read/write permission.
RMAN-00571: ==================================================
RMAN-00569: ====== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ==================================================
RMAN-03002: failure of Duplicate Db command at 06/07/2011 14:07:03
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on prmy1 channel at 06/07/2011 14:07:03
ORA-17628: Oracle error 19505 returned by remote Oracle server
Check the alert log file at standby location.The software will be trying to access/write into an non-existent directory.
Resolution:
Create the directory and give the read/write permission.
Tuesday, June 07, 2011
RMAN Error 04014
I was recieving the below error while creating stand by database through RMAN.
RMAN-04014: startup failed: ORA-01261: Parameter db_create_file_dest destination string cannot be translated
The value of parameter db_create_file_dest locaiton was proper and was present at both the source and destination servers with proper files permissions.
But the issue later turned out to be because of an improper settting of another parameter -control_files, which had a extra character added to it(yes ..by mistake).Anyway the issue was with the improper location setting.
RMAN-04014: startup failed: ORA-01261: Parameter db_create_file_dest destination string cannot be translated
The value of parameter db_create_file_dest locaiton was proper and was present at both the source and destination servers with proper files permissions.
But the issue later turned out to be because of an improper settting of another parameter -control_files, which had a extra character added to it(yes ..by mistake).Anyway the issue was with the improper location setting.
Subscribe to:
Posts (Atom)