Friday, March 11, 2022

Oracle Golden Gate

 Golden gate daily hits

[oracle@ggsrv1 ogg]$ echo $GG_HOME
/u01/app/oracle/product/ogg
[oracle@ggsrv1 ogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (ggsrv1.localdomain) 1>
GGSCI (ggsrv1.localdomain) 1> h

GGSCI Command History

    1: h


GGSCI (ggsrv1.localdomain) 2> history

GGSCI Command History

    1: h
    2: history


GGSCI (ggsrv1.localdomain) 3>

GGSCI (ggsrv1.localdomain) 3> !2   --> this command will execute the history 

1. In source database (db1), verify that all the tables included in your replication have primary key constraints

SQL> select t.OWNER, t.TABLE_NAME from DBA_TABLES t where not exists
(select 1 from DBA_CONSTRAINTS c where c.OWNER = t.OWNER and c.TABLE_NAME = t.TABLE_NAME and c.CONSTRAINT_TYPE = 'P') and t.OWNER='HR';  2

no rows selected
B. Verify the archiving mode is enabled
Make sure the archiving mode is enabled in the databases involved in your GoldenGate configuration. If not, enable it.
2. In both databases, login as sys user and enable the archiving mode:
conn / as sysdba 
SELECT LOG_MODE FROM V$DATABASE ; 
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=BOTH; 
SHUTDOWN IMMEDIATE 
STARTUP MOUNT 
ALTER DATABASE ARCHIVELOG; 
ALTER DATABASE OPEN; 
ALTER SYSTEM SWITCH LOGFILE; 
SELECT NAME FROM V$ARCHIVED_LOG;

C.Set up a GoldenGate database user
In both the source and target databases, you need to create a database user that will be used by GoldenGate. In source database, the GoldenGate user reads the data and transactions made on the source tables. In the target database, GoldenGate user will apply the transactions data. Grant the required privileges to the GoldenGate user.

In each database, create a database user that will be used by Oracle GoldenGate
-- in DB1 -- create tablespace dedicated to OGG 
CREATE TABLESPACE OGG_TBS DATAFILE '/u01/app/oracle/oradata/DB1/oggtbs01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 
--create OGG user 
CREATE USER OGG IDENTIFIED BY oracle DEFAULT TABLESPACE OGG_TBS; 
-- in DB2 
-- create tablespace dedicated to OGG 
CREATE TABLESPACE OGG_TBS DATAFILE '/u01/app/oracle/oradata/DB2/oggtbs01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 
-- create OGG user 
CREATE USER OGG IDENTIFIED BY oracle DEFAULT TABLESPACE OGG_TBS;

4. Grant the required privileges to OGG user
-- (on both databases) grant privileges to OGG user 
EXEC dbms_goldengate_auth.grant_admin_privilege('OGG') ;
-- check out the privileges granted to OGG: 
SELECT PRIVILEGE FROM dba_sys_privs WHERE grantee='OGG' 
union all 
SELECT GRANTED_ROLE FROM dba_role_privs WHERE grantee='OGG' 
union all 
SELECT PRIVILEGE||' on '|| table_name FROM dba_tab_privs WHERE grantee='OGG';

256 rows selected.

-- (on both databases) grant further privileges to OGG:  (on both source and target databases)
GRANT CREATE SESSION TO OGG; 
GRANT ALTER SYSTEM TO OGG; 
GRANT RESOURCE TO OGG; 
GRANT CONNECT TO OGG; 
GRANT DBA to OGG; 
GRANT SELECT ANY DICTIONARY TO OGG; 
GRANT EXECUTE on DBMS_FLASHBACK TO OGG; 
GRANT LOCK ANY TABLE TO OGG;

# (in db1): 
GRANT SELECT ANY TRANSACTION TO OGG; 

# (in db2) only required by Replicat: 
GRANT INSERT ANY TABLE TO OGG; 
GRANT UPDATE ANY TABLE TO OGG; 
GRANT DELETE ANY TABLE TO OGG; 
GRANT CREATE TABLE TO OGG;

D. Enable supplemental logging
5. You must enable the minimal supplemental logging in the database. You also must enable the table-level or schema-level supplemental logging for the data to replicate.

# in both databases 
SELECT SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM V$DATABASE; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
 ALTER DATABASE FORCE LOGGING; 
ALTER SYSTEM SWITCH LOGFILE;

6. (Optional) Open a monitoring session to monitor the ggserr.log file. Use this monitoring session to view how to GoldenGate responds to your commands in the background and for troubleshooting. 
tail -f $GG_HOME/ggserr.log

7. In both databases, enable GoldenGate replication 
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE scope=both;

8. In the source database (db1), 
enable schema-level supplemental logging for HR schema 
cd $GG_HOME 
ggsci 
ggsci> DBLogin UserID ogg, Password oracle 
ggsci> ADD SCHEMATRANDATA hr

E. Configure and start the Manager process
We have not created the Manager process during in the installer wizard and it is time to create it now. As with all GoldenGate processes, you first configure the parameter file then you start the process.
9. At the GGSCI prompt, enter the command as shown below.
This command creates a new mgr.prm file and starts the vi editor to allow you to edit the parameter file. If the file already exists, it will start the editor and opens the file for you. 
ggsci> edit Param mgr

10. Add the following runtime parameter to the file: 
Port 7809 
DynamicPortList 9500-9520 
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MinKeepDays 5

11. Save and close the file.

12. Start the Manager process. The following command is equivalent to the command “start manager” ggsci> start mgr
13. View the status of the Manager process. 
ggsci> Info mgr
14. Repeat the same steps to create and start the Manager process in the target database system (ggsrv2). Put the following parameters in its parameter file:  In the target side, the port number will be different that is 7810

Port 7810 
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MinKeepDays 5

F. Configure the GLOBALS parameter file
The parameters that you add to the GLOBALS file affect all the processes in an Oracle GoldenGate instance. In the following steps, you will create the file and add a parameter to it.
15. In the source system at the ggsci prompt, issue the following command to create the GLOBALS file (uppercase, without a file extension). 
ggsci> EDIT PARAMS ./GLOBALS
16. Add the parameter GGSCHEMA to it.
This parameter tells the GoldenGate which database schema it should use to store its objects. GGSCHEMA OGG

Repeat the same steps in the target system (ggsrv2).

======******=========*****========*****=========*******=====*****=====
GGSCI (ggsrv1.localdomain) 20> send extract ESRV1,status

ERROR: EXTRACT ESRV1 not currently running.

GGSCI (ggsrv1.localdomain) 21> send extract PSRV1, status

Sending STATUS request to EXTRACT PSRV1 ...


EXTRACT PSRV1 (PID 3112)
  Current status: Recovery complete: At EOF

  Current read position:
  Sequence #: 4
  RBA: 404850
  Timestamp: 2022-05-27 22:21:59.218609
  Extract Trail: ./dirdat/es

  Current write position:
  Sequence #: 7
  RBA: 882
  Timestamp: 2022-06-24 11:47:04.239881
  Extract Trail: ./dirdat/rt                                 ----> rt=remote trail

**Please check the environment set for the concerned database for which you want to enter into the GGSCI command prompt mode**


****executing command from history****
GGSCI (ggsrv2.localdomain) 21> h

GGSCI Command History

   12: edit params rsrv1
   13: edit params rsrv2
   14: start r*
   15: info all
   16: stop r*
   17: edit param rsrv2
   18: start r*
   19: info all
   20: stop r*
   21: h


GGSCI (ggsrv2.localdomain) 22> !17   --> here use !(cmd number from above list)

*******get param info *******

-- in ggsrv1:

send extract esrv1 getparaminfo

-- in ggsrv2:

send replicat rsrv2 getparaminfo

 

GGSCI (ggsrv1.localdomain as ogg@db1) 15> lag extract esrv1

 Sending GETLAG request to EXTRACT ESRV1 ...

Last record lag 2 seconds.

At EOF, no more records to process.

To verify that all the tables included in your replication have primary key constraints.

select t.OWNER, t.TABLE_NAME
from DBA_TABLES t
where not exists (select 1
from DBA_CONSTRAINTS c
where c.OWNER = t.OWNER and c.TABLE_NAME = t.TABLE_NAME
and c.CONSTRAINT_TYPE = 'P')
and t.OWNER='HR'; 
at No comments:
Labels:

Wednesday, February 9, 2022

db_validation

 db_validation

set lines 500
col BYTES/1024/1024/1024 for 9999999999999999;
col AUTOEXTENSIBLE for a10;
col FILE_NAME for a60;
col MAXBYTES for 99999999999999999999;
select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 from dba_temp_files;
select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 from dba_data_files;
show parameter memory_max_target;
show parameter memory_target;
show parameter sga_max_size;
show parameter sga_target;
show parameter pga_aggregate_target;
show parameter optimizer_adaptive_features;
show parameter nls_length_semantics;
SELECT  1 FROM NLS_DATABASE_PARAMETERS DBPARMS, NLS_SESSION_PARAMETERS CURRENTPARMS WHERE
DBPARMS.PARAMETER = 'NLS_CHARACTERSET' AND CURRENTPARMS.PARAMETER = 'NLS_LENGTH_SEMANTICS' AND
((DBPARMS.VALUE IN ('AL32UTF8','UTF8') AND (CURRENTPARMS.VALUE = 'CHAR')) OR (DBPARMS.VALUE NOT
IN ('AL32UTF8','UTF8') AND (CURRENTPARMS.VALUE = 'BYTE')));
show parameter max_string_size;
show parameter db_recovery_file_dest_size;
show parameter Use_large_pages;
COL CLIENT_NAME FORMAT a31;
SELECT CLIENT_NAME, STATUS FROM   DBA_AUTOTASK_CLIENT WHERE  CLIENT_NAME = 'auto optimizer stats collection';
show parameter processes
show parameter open_cur
col PROPERTY_NAME for a30;
col PROPERTY_VALUE for a30;
col DESCRIPTION for a20;
show parameter db_block_size
select * from database_properties
where PROPERTY_NAME in ('NLS_CHARACTERSET',
'NLS_NCHAR_CHARACTERSET');
show parameter cpu;
show parameter resource_Manager_plan;
select flashback_on from v$database;
select force_logging from v$database;
select dbtimezone from dual;
col COMP_NAME for a40;
col status for a20;
select COMP_NAME,STATUS from dba_registry;
select * from all_db_links;
select * from dba_db_links;

TABLESPACE CREATION:

CREATE BIGFILE TABLESPACE TESTTBS DATAFILE '+DATAC1' SIZE 10M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

CREATE BIGFILE UNDO TABLESPACE UNDOTBS3  DATAFILE '+DATAC1' SIZE 200M AUTOEXTEND ON NEXT 500M MAXSIZE 200G;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS4 SCOPE=BOTH SID='dbname';
 
DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

srvctl setenv database -d CNPMWAFT -t 'TZ=America/Los_Angeles'

at No comments:
Labels:

Thursday, January 27, 2022

Changing dataguard mode

 Changing Dataguard mode from Maximum performance mode to maximum availability

alter system set log_archive_dest_2='SERVICE=oradb_s2 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) NET_TIMEOUT=30 REOPEN=50 DB_UNIQUE_NAME=oradb_s2';

SERVICE=oradb_s2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb_s2

NET_TIMEOUT – Specifies the time in seconds that the primary database log writer will wait for a response from the Log Network Service (LNS) before terminating the connection and marking the standby (destination) as failed. The default value is 30 seconds.

REOPEN – Specifies the time in seconds that the log writer should wait before attempting to access a previously failed standby (destination). The default is 50 seconds.

Note : Shut down the primary database and restart it in mounted mode if the protection mode is being set to Maximum Protection or being changed from Maximum Performance to Maximum Availability. If the primary database is an Oracle Real Applications Cluster, shut down all of the instances and then start and mount a single instance.

Now, shutdown database :

STARTUP MOUNT

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

alter database open;

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;




alter system dump logfile '/u01/app/oracle/fra/ORADB_S2/onlinelog/o1_mf_5_ho2pm6m3_.log' validate;


PR00 (PID:16647): Media Recovery Log /u01/app/oracle/fra/ORADB_S2/archivelog/2022_01_27/o1_mf_1_124_jz4w8rdo_.arc
Errors with log /u01/app/oracle/fra/ORADB_S2/archivelog/2022_01_27/o1_mf_1_124_jz4w8rdo_.arc
PR00 (PID:16647): MRP0: Background Media Recovery terminated with error 328
2022-01-27T16:58:26.816034+04:00
Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_16647.trc:
ORA-00328: archived log ends at change 3830620, need later change 4142985
ORA-00334: archived log: '/u01/app/oracle/fra/ORADB_S2/archivelog/2022_01_27/o1_mf_1_124_jz4w8rdo_.arc'
PR00 (PID:16647): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!

col name for a80
col thread# for 99
col sequence# for 9999
col archived for a5
col status for a10
select name, thread#, sequence#, archived, applied, status from v$archived_log where 4142985 between FIRST_CHANGE# and NEXT_CHANGE#;
select name, thread#, sequence#, archived, applied, status from v$archived_log where 3830620 between FIRST_CHANGE# and NEXT_CHANGE#;

The standby database is not-in-sync after converting maximum performance mode to maximum availability with the above error

Lets do roll forward for this , as we have no solution and will see the result..

at No comments:
Labels:

Friday, January 7, 2022

what happens at DR side if space is exhausted during tablespace creation at primary side


oradb--> primary db
oradb_s2 -->Standby db

SQL> CREATE BIGFILE TABLESPACE TESTTBS2 DATAFILE '/backup/testtbs02.dbf' SIZE 200M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

Tablespace created.

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      oradb.localdomain     


The below is the alert log from DR side, when space is exhausted at DR side while tablespace creation at primary side. 
WARNING: File being created with same name as in Primary
Existing file may be overwritten
2022-01-07T22:09:20.122725+04:00
Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:
ORA-27072: File I/O error
Additional information: 4
Additional information: 12288
Additional information: 237568
2022-01-07T22:09:20.179041+04:00
Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:
ORA-19502: write error on file "/backup/testtbs02.dbf", block number 12288 (block size=8192)
ORA-27072: File I/O error
Additional information: 4
Additional information: 12288
Additional information: 237568
File #14 added to control file as 'UNNAMED00014'.
Originally created as:
'/backup/testtbs02.dbf'
Recovery was unable to create the file as:
'/backup/testtbs02.dbf'
PR00 (PID:4229): MRP0: Background Media Recovery terminated with error 1274
2022-01-07T22:09:20.343651+04:00
Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:
ORA-01274: cannot add data file that was originally created as '/backup/testtbs02.dbf'
PR00 (PID:4229): Managed Standby Recovery not using Real Time Apply
2022-01-07T22:09:22.638703+04:00
Recovery interrupted!

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
Recovered data files to a consistent state at change 3262529
stopping change tracking
2022-01-07T22:09:23.132280+04:00
Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:
ORA-01274: cannot add data file that was originally created as '/backup/testtbs02.dbf'
2022-01-07T22:09:23.184841+04:00
Background Media Recovery process shutdown (oradb_s2)


Again if we forcefully starting the MRP, it is terminating , below is the log from alert log.

alter database recover managed standby database disconnect
2022-01-07T22:17:03.094994+04:00
Attempt to start background Managed Standby Recovery process (oradb_s2)
Starting background process MRP0
2022-01-07T22:17:03.116141+04:00
MRP0 started with pid=60, OS id=5031
2022-01-07T22:17:03.118382+04:00
Background Managed Standby Recovery process started (oradb_s2)
2022-01-07T22:17:08.146408+04:00
 Started logmerger process
2022-01-07T22:17:08.164904+04:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
PR00 (PID:5037): Managed Standby Recovery starting Real Time Apply
2022-01-07T22:17:08.216985+04:00
Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_dbw0_1947.trc:
ORA-01186: file 14 failed verification tests
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01111: name for data file 14 is unknown - rename to correct file
ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'
2022-01-07T22:17:08.217231+04:00
File 14 not verified due to error ORA-01157
2022-01-07T22:17:08.218311+04:00
Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_dbw0_1947.trc:
ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
ORA-01110: data file 203: '/u01/app/oracle/oradata/ORADB/pdb1/temp01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2022-01-07T22:17:08.431502+04:00
max_pdb is 3
PR00 (PID:5037): MRP0: Background Media Recovery terminated with error 1111
2022-01-07T22:17:08.462884+04:00
Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_5037.trc:
ORA-01111: name for data file 14 is unknown - rename to correct file
ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01111: name for data file 14 is unknown - rename to correct file
ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'
PR00 (PID:5037): Managed Standby Recovery not using Real Time Apply
stopping change tracking
2022-01-07T22:17:08.626152+04:00
Recovery Slave PR00 previously exited with exception 1111
2022-01-07T22:17:08.674098+04:00
Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_mrp0_5031.trc:
ORA-01111: name for data file 14 is unknown - rename to correct file
ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01111: name for data file 14 is unknown - rename to correct file
ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'
2022-01-07T22:17:08.674347+04:00
Background Media Recovery process shutdown (oradb_s2)
2022-01-07T22:17:09.182195+04:00
Completed: alter database recover managed standby database disconnect


at No comments:
Labels:

DATAGUARD

 DATAGUARD

select name,db_unique_name,open_mode,log_mode,database_role from v$database;

alter database recover managed standby database disconnect;

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#;

select PROCESS, PID, STATUS, THREAD#, SEQUENCE# from gv$managed_standby;

select PROCESS, PID, STATUS, THREAD#, SEQUENCE# from gv$managed_standby where STATUS='IDLE';

select PROCESS, PID, STATUS, THREAD#, SEQUENCE# from gv$managed_standby where PROCESS='MRP0';

set lines 555
col DEST_NAME for a30
 col DESTINATION for a30
col ERROR for a30

 select INST_ID,DEST_ID,DEST_NAME,DESTINATION,ERROR,STATUS from gv$archive_dest where DESTINATION IS NOT NULL;
at No comments:
Labels:

RMAN

 RMAN:
=========

RMAN Retention Policy

The RMAN retention policy determines how long backups are kept for and how many copies are retained.

The retention policy can be defined in terms of:

  • REDUNDANCY - number of copies to retain
  • RECOVERY WINDOW - number of days to retain backups

The retention policy is configured by the RETENTION POLICY parameter. The default value is

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

To specify a recovery window of 1 day use:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

Backup files can be marked as expired or obsolete.

  • Expired - RMAN has performed a crosscheck and the file cannot be found
  • Obsolete - based on the retention policy the file is not needed for recovery

The backup retention policy only applies to full or level 0 datafile and control file backups.

The retention policy does not directly affect archived redo logs and incremental level 1 backups. These files become obsolete when no full backups exist that need them.

Datafile backup sets cannot be deleted until all datafile backups within the backup set are obsolete.

Obsolete backups can be identified using the REPORT OBSOLETE command and deleted using the DELETE OBSOLETE command.

The retention policy can also be set to NONE

RMAN> CONFIGURE RETENTION POLICY TO NONE;

To revert to the default value use:

RMAN> CONFIGURE RETENTION POLICY CLEAR;

RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fra/ORADB/autobackup/2022_01_07/o1_mf_s_1093368491_jxjhzcyy_.bkp RECID=10 STAMP=1093368491
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/media/sf_DR_TEST/ORADB_20220107_11_1_FULL RECID=11 STAMP=1093370071
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/media/sf_DR_TEST/ORADB_20220107_12_1_FULL RECID=12 STAMP=1093370127
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/media/sf_DR_TEST/ORADB_20220107_13_1_FULL RECID=13 STAMP=1093370154
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fra/ORADB/autobackup/2022_01_07/o1_mf_s_1093370178_jxjkn50v_.bkp RECID=14 STAMP=1093370181
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/media/sf_DR_TEST/ORADB_20220107_15_1_ARCHIVE RECID=15 STAMP=1093370187
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/media/sf_DR_TEST/ORADB_20220107_16_1_CONTROL RECID=16 STAMP=1093370199
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fra/ORADB/autobackup/2022_01_07/o1_mf_s_1093370201_jxjknsmo_.bkp RECID=17 STAMP=1093370201
Crosschecked 8 objects

RMAN Compression

How does RMAN compression works ?

Goal of this document is to give you a Complete Understanding of

1. Null compression

2. Unused block compression

3. Binary compression




at No comments:
Labels:

Sunday, January 2, 2022

ORA-00742: Log read detects lost write in thread 1 sequence 104 block 30681

 ORA-00742: Log read detects lost write in thread 1 sequence 104 block 30681

You can encounter this error if your Oracle Database machine was suddenly stopped without proper shutdown command due to some unavoidable problem like Power Failure, Power cable issue, etc.

-bash-4.4$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 2 22:49:25 2022
Version 19.5.0.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  281016480 bytes
Fixed Size                  8882336 bytes
Variable Size             218103808 bytes
Database Buffers           50331648 bytes
Redo Buffers                3698688 bytes
Database mounted.
ORA-00742: Log read detects lost write in thread 1 sequence 104 block 30681
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/testdb/redo02.log'

SQL> select status from v$instance;

STATUS
------------
MOUNTED

2.Check the status of log file 2 in v$log & v$logfile and you can notice log group 2 is the current logfile.

    GROUP# STATUS  TYPE    MEMBER                                                       IS_     CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
         1         ONLINE  /u01/app/oracle/oradata/testdb/redo01.log                    NO           0
         2         ONLINE  /u01/app/oracle/oradata/testdb/redo02.log                    NO           0
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         2          1        104   52428800        512          1 NO  CURRENT                 479386 02-JAN-22   9.2954E+18                    0
         1          1        103   52428800        512          1 NO  INACTIVE                479343 02-JAN-22       479386 02-JAN-22          0

3.Try to clear the current log file most likely it will fail as group log 2 is the current logfile.
SQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance testdb (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/testdb/redo02.log'

4.So we will try to perform manual recovery on the database, Please note since Group log 2 is current it will be not archived, and the requested archive log sequence 149 is the current redo logfile, and hence I have provided manually redo log 2 file location. But even after “Media recovery complete” While trying to open DB, I encounter an ORA-00600 error.

SQL> recover database until cancel;
ORA-00279: change 479386 generated at 01/02/2022 00:06:42 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/TESTDB/archivelog/2022_01_02/o1_mf_1_104_%u_.arc
ORA-00280: change 479386 for thread 1 is in sequence #104


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/testdb/redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.


-bash-4.4$ ps -ef | grep pmon
  oracle  1309     1   0 22:49:47 ?           0:00 ora_pmon_testdb
-bash-4.4$


at No comments:
Labels:

Saturday, January 1, 2022

Manual DB creation

 Manual Oracle database creation 19c on Oracle-Solaris_11.4

SunOS kolsol.localdomain 5.11 11.4.0.15.0 i86pc i386 i86pc

If we don't have an option to use GUI mode dbca, then we can create database manually.  we can use dbca in silent mode also, but this is for our knowledge , that how the oracle is using internal processes (.bsq files) to create a database when we issue the command " CREATE DATABASE"

BSQ file is read by the instance and is used to create the database system tablespace and all of the tables, clusters, indexes, sequences, and initial data required by the kernel code to store and maintain the metadata for user-created objects.

Below are the prerequisites to create a database :
1.Create the required directories.
mkdir -p /u01/app/oracle/admin/testdb/adump
mkdir -p /u01/app/oracle/oradata/testdb/
mkdir -p /u01/app/oracle/fast_recovery_area 
chmod 655 /u01/app/oracle/admin/testdb/adump
chmod 655 /u01/app/oracle/oradata/testdb/
chmod 655 /u01/app/oracle/fast_recovery_area

2. create the pfile in ORACLE_HOME/dbs location with name inittestdb.ora
*.audit_file_dest='/u01/app/oracle/admin/testdb/adump'
*.audit_sys_operations=TRUE
*.audit_trail='db'
*.compatible='19.5.0.0.0'
*.control_files='/u01/app/oracle/oradata/testdb/control01.ctl','/u01/app/oracle/control02.ctl'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='testdb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='%t_%s_%r.arc'
*.processes=300
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

-bash-4.4$ export ORACLE_SID=testdb
-bash-4.4$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
-bash-4.4$ sqlplus / as sysdba
-bash: sqlplus: command not found
-bash-4.4$ export PATH=$PATH:$ORACLE_HOME/bin
-bash-4.4$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 1 17:01:48 2022
Version 19.5.0.0.0

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  281016480 bytes
Fixed Size                  8882336 bytes
Variable Size             218103808 bytes
Database Buffers           50331648 bytes
Redo Buffers                3698688 bytes
SQL>

check the alert log is created or not, 
/u01/app/oracle/diag/rdbms/testdb/testdb/trace
-bash-4.4$ ls -ltr alert_testdb.log
-rw-r-----   1 oracle   oinstall    6669 Jan  1 17:03 alert_testdb.log
it is created

from alert log , we can find the below details
ORACLE_HOME:    /u01/app/oracle/product/19.0.0/dbhome_1
System name:    SunOS
Node name:      kolsol.localdomain
Release:        5.11
Version:        11.4.0.15.0
Machine:        i86pc
Using parameter settings in server-side pfile /u01/app/oracle/product/19.0.0/dbhome_1/dbs/inittestdb.ora

After starting the database in nomount mode, then create the database now

SQL> CREATE DATABASE testdb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/testdb/redo01.log') SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 50
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on
SYSAUX DATAFILE '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on
DEFAULT TABLESPACE users datafile '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/testdb/undotbs01.dbf'
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17  SIZE 200M;
CREATE DATABASE testdb
*
ERROR at line 1:
ORA-01518: CREATE DATABASE must specify more than one log file

here we can see that , we require atleast two log files to create a database, means , one is for writing and another is for archiving . 
lets add one more log file and create the database

SQL> CREATE DATABASE testdb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/testdb/redo01.log') SIZE 50M,
GROUP 2 ('/u01/app/oracle/oradata/testdb/redo02.log') SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 50
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on
SYSAUX DATAFILE '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on
DEFAULT TABLESPACE users datafile '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/testdb/undotbs01.dbf'
SIZE 200M;
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18  CREATE DATABASE testdb
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/u01/app/oracle/oradata/testdb/control01.ctl'
ORA-27038: created file already exists
Additional information: 1

here in the first try , the control file and redolog files are already created, lets remove those files, and then try
SQL> startup nomount
ORACLE instance started.

Total System Global Area  281016480 bytes
Fixed Size                  8882336 bytes
Variable Size             218103808 bytes
Database Buffers           50331648 bytes
Redo Buffers                3698688 bytes
SQL> CREATE DATABASE testdb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/testdb/redo01.log') SIZE 50M,
GROUP 2 ('/u01/app/oracle/oradata/testdb/redo02.log') SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 50
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on
SYSAUX DATAFILE '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on
DEFAULT TABLESPACE users datafile '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/testdb/undotbs01.dbf'
SIZE 200M;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18

Database created.

from alert log we can see db creation with some .bsq files

processing ?/rdbms/admin/dcore.bsq
create tablespace SYSTEM datafile  '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on

  default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online
2022-01-01T17:18:00.134237+05:30
Endian type of dictionary set to little
Completed: create tablespace SYSTEM datafile  '/u01/app/oracle/oradata/testdb/system01.dbf' SIZE 100M autoextend on

  default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online
create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)
Completed: create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)

alter tablespace system force logging
Completed: alter tablespace system force logging
2022-01-01T17:18:03.687898+05:30
processing ?/rdbms/admin/dsqlddl.bsq
processing ?/rdbms/admin/dmanage.bsq
CREATE TABLESPACE sysaux DATAFILE  '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on

  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE FORCE LOGGING
2022-01-01T17:18:07.513338+05:30
Completed: CREATE TABLESPACE sysaux DATAFILE  '/u01/app/oracle/oradata/testdb/sysaux01.dbf' SIZE 100M autoextend on

  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE FORCE LOGGING

alter tablespace system default compress for all operations
Completed: alter tablespace system default compress for all operations
alter tablespace sysaux default compress for all operations
Completed: alter tablespace sysaux default compress for all operations
2022-01-01T17:18:07.676503+05:30
processing ?/rdbms/admin/dplsql.bsq
processing ?/rdbms/admin/dtxnspc.bsq
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE  '/u01/app/oracle/oradata/testdb/undotbs01.dbf'
SIZE 200M
2022-01-01T17:18:15.295970+05:30
[1543] Successfully onlined Undo Tablespace 2.
Completed: CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE  '/u01/app/oracle/oradata/testdb/undotbs01.dbf'
SIZE 200M
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE  '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m

Completed: CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE  '/u01/app/oracle/oradata/testdb/temp01.dbf' SIZE 50m

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP"
Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP"
CREATE  TABLESPACE "USERS" DATAFILE  '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on
 SEGMENT SPACE MANAGEMENT AUTO
2022-01-01T17:18:18.232890+05:30

2022-01-01T17:18:18.232890+05:30
Completed: CREATE  TABLESPACE "USERS" DATAFILE  '/u01/app/oracle/oradata/testdb/users01.dbf' size 100m autoextend on
 SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DEFAULT TABLESPACE "USERS"
Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS"
2022-01-01T17:18:18.434077+05:30
processing ?/rdbms/admin/dfmap.bsq
processing ?/rdbms/admin/denv.bsq
processing ?/rdbms/admin/drac.bsq
processing ?/rdbms/admin/dsec.bsq
2022-01-01T17:18:21.857237+05:30
processing ?/rdbms/admin/doptim.bsq
processing ?/rdbms/admin/dobj.bsq
2022-01-01T17:18:23.372012+05:30
processing ?/rdbms/admin/djava.bsq
processing ?/rdbms/admin/dpart.bsq
processing ?/rdbms/admin/drep.bsq
2022-01-01T17:18:26.086262+05:30
processing ?/rdbms/admin/daw.bsq
processing ?/rdbms/admin/dsummgt.bsq
2022-01-01T17:18:27.439197+05:30
processing ?/rdbms/admin/dtools.bsq
processing ?/rdbms/admin/dexttab.bsq
processing ?/rdbms/admin/ddm.bsq
2022-01-01T17:18:28.683148+05:30
processing ?/rdbms/admin/dlmnr.bsq
processing ?/rdbms/admin/ddst.bsq
processing ?/rdbms/admin/dfba.bsq
2022-01-01T17:18:29.786777+05:30
processing ?/rdbms/admin/dpstdy.bsq
processing ?/rdbms/admin/drupg.bsq
processing ?/rdbms/admin/dtlog.bsq
processing ?/rdbms/admin/dmisc.bsq
processing ?/rdbms/admin/dhcs.bsq

Successfully created internal service SYS$BACKGROUND at open
Successfully created internal service SYS$USERS at open

Now, check in the db
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TESTDB    READ WRITE

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
testdb           OPEN

POST STEPS:

Below post scripts create database data dictionary views which is require to fully funtional database creation.
catalog.sql
catproc.sql
pupbld.sql

it creates default dictionary views, we have the dictionaries like dba_users, but accessing views are not available.

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
the below pupbld.sql run as system user.
@?/sqlplus/admin/pupbld.sql

generate password file
bash-4.4$ orapwd file=orapwdtestdb password=oracle321! force=y entries=10

OPW-00029: Password complexity failed for SYS user : Password must not be too simple.
bash-4.4$ orapwd file=orapwdtestdb password=oracle$321! force=y entries=10

OPW-00029: Password complexity failed for SYS user : Password must not be too simple.

by using format=12 , password is created with simple string
orapwd file=orapwdtestdb password=oracle force=y format=12 entries=10 
-rw-r-----   1 oracle   oinstall    2048 Jan  1 18:58 orapwtestdb

Now create spfile

bash-4.4$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 1 19:00:06 2022
Version 19.5.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      testdb.localdomain
SQL>
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

SQL> create spfile from pfile;

File created.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  281016480 bytes
Fixed Size                  8882336 bytes
Variable Size             218103808 bytes
Database Buffers           50331648 bytes
Redo Buffers                3698688 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/spfiletestdb.ora

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 268M
sga_min_size                         big integer 0
sga_target                           big integer 0
unified_audit_sga_queue_size         integer     1048576
SQL>
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2G
pga_aggregate_target                 big integer 10M

I didnot mentioned the SGA and PGA target in the pfile, the above are the values it is created with


Now the database is ready, this is for testing purpose only.
at No comments:
Labels:

Wednesday, December 15, 2021

Database cloning

 Database cloning using RMAN and with existing full database backups taken by RMAN

Create pfile with limited parameters in it. And startup in nomount using this pfile.
As the existing backup is taken from a CDB (container) database , include the enable_pluggable_database parameter as highlighted below.

*.audit_file_dest='/u01/app/oracle/admin/racnew/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.db_block_size=8192
*.db_name='racnew'
*.enable_pluggable_database=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racnewXDB)'
*.log_archive_dest_1='LOCATION=+FRA'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+FRA'

RMAN COMMAND:
DUPLICATE DATABASE TO racnew BACKUP LOCATION '/media/sf_Ahmed/backups_testdir/RESTRICT_GOLDCOPY_TESTPDB' NOFILENAMECHECK;

[oracle@srv1 dbs]$ rman auxiliary /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Dec 15 10:36:57 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: RACNEW (not mounted)

RMAN> DUPLICATE DATABASE TO racnew BACKUP LOCATION '/media/sf_Ahmed/backups_testdir/RESTRICT_GOLDCOPY_TESTPDB' NOFILENAMECHECK;

Starting Duplicate Db at 15-DEC-2021 10:36:59
searching for database ID
found backup of database ID 2633676207

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     666892632 bytes

Fixed Size                     9140568 bytes
Variable Size                599785472 bytes
Database Buffers              50331648 bytes
Redo Buffers                   7634944 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+FRA/RACNEW/CONTROLFILE/current.296.1091356675'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''RAC'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''racnew'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/media/sf_Ahmed/backups_testdir/RESTRICT_GOLDCOPY_TESTPDB/RAC_20211213_6_1_CONTROL';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+FRA/RACNEW/CONTROLFILE/current.296.1091356675'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''RAC'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''racnew'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     666892632 bytes

Fixed Size                     9140568 bytes
Variable Size                599785472 bytes
Database Buffers              50331648 bytes
Redo Buffers                   7634944 bytes

Starting restore at 15-DEC-2021 10:38:47
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1939 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+FRA/RACNEW/CONTROLFILE/current.296.1091356675
Finished restore at 15-DEC-2021 10:38:50

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1939 device type=DISK
duplicating Online logs to Oracle Managed File (OMF) location
duplicating Datafiles to Oracle Managed File (OMF) location

contents of Memory Script:
{
   set until scn  3327291;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   set newname for clone datafile  8 to new;
   set newname for clone datafile  9 to new;
   set newname for clone datafile  10 to new;
   set newname for clone datafile  11 to new;
   set newname for clone datafile  12 to new;
   set newname for clone datafile  13 to new;
   set newname for clone datafile  14 to new;
   set newname for clone datafile  16 to new;
   set newname for clone datafile  17 to new;
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 15-DEC-2021 10:38:59
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00009 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /media/sf_Ahmed/backups_testdir/RESTRICT_GOLDCOPY_TESTPDB/RAC_20211213_1_1_FULL
channel ORA_AUX_DISK_1: piece handle=/media/sf_Ahmed/backups_testdir/RESTRICT_GOLDCOPY_TESTPDB/RAC_20211213_1_1_FULL tag=RACDB_FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00008 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /media/sf_Ahmed/backups_testdir/RESTRICT_GOLDCOPY_TESTPDB/RAC_20211213_3_1_FULL
channel ORA_AUX_DISK_1: piece handle=/media/sf_Ahmed/backups_testdir/RESTRICT_GOLDCOPY_TESTPDB/RAC_20211213_3_1_FULL tag=RACDB_FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00011 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00012 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00013 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00014 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00016 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00017 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /media/sf_Ahmed/backups_testdir/RESTRICT_GOLDCOPY_TESTPDB/RAC_20211213_2_1_FULL
channel ORA_AUX_DISK_1: piece handle=/media/sf_Ahmed/backups_testdir/RESTRICT_GOLDCOPY_TESTPDB/RAC_20211213_2_1_FULL tag=RACDB_FULL
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 15-DEC-2021 10:41:26

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=16 STAMP=1091356886 file name=+DATA/RACNEW/DATAFILE/system.282.1091356741
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=1091356886 file name=+DATA/RACNEW/DATAFILE/sysaux.283.1091356741
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=1091356887 file name=+DATA/RACNEW/DATAFILE/undotbs1.284.1091356741
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=1091356887 file name=+DATA/RACNEW/AA5804C6F40D3D1FE0534738A8C0DEDA/DATAFILE/system.288.1091356807
datafile 6 switched to datafile copy
input datafile copy RECID=20 STAMP=1091356887 file name=+DATA/RACNEW/AA5804C6F40D3D1FE0534738A8C0DEDA/DATAFILE/sysaux.287.1091356807
datafile 7 switched to datafile copy
input datafile copy RECID=21 STAMP=1091356887 file name=+DATA/RACNEW/DATAFILE/users.286.1091356741
datafile 8 switched to datafile copy
input datafile copy RECID=22 STAMP=1091356887 file name=+DATA/RACNEW/AA5804C6F40D3D1FE0534738A8C0DEDA/DATAFILE/undotbs1.289.1091356807
datafile 9 switched to datafile copy
input datafile copy RECID=23 STAMP=1091356887 file name=+DATA/RACNEW/DATAFILE/undotbs2.285.1091356741
datafile 10 switched to datafile copy
input datafile copy RECID=24 STAMP=1091356888 file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/system.292.1091356841
datafile 11 switched to datafile copy
input datafile copy RECID=25 STAMP=1091356888 file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/sysaux.291.1091356841
datafile 12 switched to datafile copy
input datafile copy RECID=26 STAMP=1091356888 file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/undotbs1.293.1091356841
datafile 13 switched to datafile copy
input datafile copy RECID=27 STAMP=1091356888 file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/undo_2.294.1091356841
datafile 14 switched to datafile copy
input datafile copy RECID=28 STAMP=1091356888 file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/users.296.1091356843
datafile 16 switched to datafile copy
input datafile copy RECID=29 STAMP=1091356888 file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/dboard.290.1091356841
datafile 17 switched to datafile copy
input datafile copy RECID=30 STAMP=1091356888 file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/test_ts.295.1091356841

contents of Memory Script:
{
   set until scn  3327291;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 15-DEC-2021 10:41:31
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=14
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=6
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=15
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=7
channel ORA_AUX_DISK_1: reading from backup piece /media/sf_Ahmed/backups_testdir/RESTRICT_GOLDCOPY_TESTPDB/RAC_20211213_5_1_ARCHIVE
channel ORA_AUX_DISK_1: piece handle=/media/sf_Ahmed/backups_testdir/RESTRICT_GOLDCOPY_TESTPDB/RAC_20211213_5_1_ARCHIVE tag=RACDB_ARCHIVE
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/RACNEW/ARCHIVELOG/2021_12_15/thread_1_seq_14.297.1091356897 thread=1 sequence=14
archived log file name=+FRA/RACNEW/ARCHIVELOG/2021_12_15/thread_2_seq_6.298.1091356897 thread=2 sequence=6
channel clone_default: deleting archived log(s)
archived log file name=+FRA/RACNEW/ARCHIVELOG/2021_12_15/thread_1_seq_14.297.1091356897 RECID=1 STAMP=1091356896
archived log file name=+FRA/RACNEW/ARCHIVELOG/2021_12_15/thread_1_seq_15.299.1091356897 thread=1 sequence=15
channel clone_default: deleting archived log(s)
archived log file name=+FRA/RACNEW/ARCHIVELOG/2021_12_15/thread_2_seq_6.298.1091356897 RECID=2 STAMP=1091356896
archived log file name=+FRA/RACNEW/ARCHIVELOG/2021_12_15/thread_2_seq_7.300.1091356897 thread=2 sequence=7
channel clone_default: deleting archived log(s)
archived log file name=+FRA/RACNEW/ARCHIVELOG/2021_12_15/thread_1_seq_15.299.1091356897 RECID=3 STAMP=1091356896
channel clone_default: deleting archived log(s)
archived log file name=+FRA/RACNEW/ARCHIVELOG/2021_12_15/thread_2_seq_7.300.1091356897 RECID=4 STAMP=1091356896
media recovery complete, elapsed time: 00:00:02
Finished recover at 15-DEC-2021 10:41:39
Oracle instance started

Total System Global Area     666892632 bytes

Fixed Size                     9140568 bytes
Variable Size                599785472 bytes
Database Buffers              50331648 bytes
Redo Buffers                   7634944 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''RACNEW'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set  db_name =  ''RACNEW'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area     666892632 bytes

Fixed Size                     9140568 bytes
Variable Size                599785472 bytes
Database Buffers              50331648 bytes
Redo Buffers                   7634944 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "RACNEW" RESETLOGS ARCHIVELOG
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY      292
 LOGFILE
  GROUP     1  SIZE 200 M ,
  GROUP     2  SIZE 200 M
 DATAFILE
  '+DATA/RACNEW/DATAFILE/system.282.1091356741',
  '+DATA/RACNEW/AA5804C6F40D3D1FE0534738A8C0DEDA/DATAFILE/system.288.1091356807',
  '+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/system.292.1091356841'
 CHARACTER SET AL32UTF8

sql statement: ALTER DATABASE ADD LOGFILE

  INSTANCE 'i2'
  GROUP     3  SIZE 200 M ,
  GROUP     4  SIZE 200 M

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   set newname for clone tempfile  2 to new;
   set newname for clone tempfile  3 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/RACNEW/DATAFILE/sysaux.283.1091356741",
 "+DATA/RACNEW/DATAFILE/undotbs1.284.1091356741",
 "+DATA/RACNEW/AA5804C6F40D3D1FE0534738A8C0DEDA/DATAFILE/sysaux.287.1091356807",
 "+DATA/RACNEW/DATAFILE/users.286.1091356741",
 "+DATA/RACNEW/AA5804C6F40D3D1FE0534738A8C0DEDA/DATAFILE/undotbs1.289.1091356807",
 "+DATA/RACNEW/DATAFILE/undotbs2.285.1091356741",
 "+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/sysaux.291.1091356841",
 "+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/undotbs1.293.1091356841",
 "+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/undo_2.294.1091356841",
 "+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/users.296.1091356843",
 "+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/dboard.290.1091356841",
 "+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/test_ts.295.1091356841";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA in control file

cataloged datafile copy
datafile copy file name=+DATA/RACNEW/DATAFILE/sysaux.283.1091356741 RECID=1 STAMP=1091356952
cataloged datafile copy
datafile copy file name=+DATA/RACNEW/DATAFILE/undotbs1.284.1091356741 RECID=2 STAMP=1091356953
cataloged datafile copy
datafile copy file name=+DATA/RACNEW/AA5804C6F40D3D1FE0534738A8C0DEDA/DATAFILE/sysaux.287.1091356807 RECID=3 STAMP=1091356953
cataloged datafile copy
datafile copy file name=+DATA/RACNEW/DATAFILE/users.286.1091356741 RECID=4 STAMP=1091356953
cataloged datafile copy
datafile copy file name=+DATA/RACNEW/AA5804C6F40D3D1FE0534738A8C0DEDA/DATAFILE/undotbs1.289.1091356807 RECID=5 STAMP=1091356953
cataloged datafile copy
datafile copy file name=+DATA/RACNEW/DATAFILE/undotbs2.285.1091356741 RECID=6 STAMP=1091356953
cataloged datafile copy
datafile copy file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/sysaux.291.1091356841 RECID=7 STAMP=1091356954
cataloged datafile copy
datafile copy file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/undotbs1.293.1091356841 RECID=8 STAMP=1091356954
cataloged datafile copy
datafile copy file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/undo_2.294.1091356841 RECID=9 STAMP=1091356954
cataloged datafile copy
datafile copy file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/users.296.1091356843 RECID=10 STAMP=1091356954
cataloged datafile copy
datafile copy file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/dboard.290.1091356841 RECID=11 STAMP=1091356954
cataloged datafile copy
datafile copy file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/test_ts.295.1091356841 RECID=12 STAMP=1091356955

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=1091356952 file name=+DATA/RACNEW/DATAFILE/sysaux.283.1091356741
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1091356953 file name=+DATA/RACNEW/DATAFILE/undotbs1.284.1091356741
datafile 6 switched to datafile copy
input datafile copy RECID=3 STAMP=1091356953 file name=+DATA/RACNEW/AA5804C6F40D3D1FE0534738A8C0DEDA/DATAFILE/sysaux.287.1091356807
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1091356953 file name=+DATA/RACNEW/DATAFILE/users.286.1091356741
datafile 8 switched to datafile copy
input datafile copy RECID=5 STAMP=1091356953 file name=+DATA/RACNEW/AA5804C6F40D3D1FE0534738A8C0DEDA/DATAFILE/undotbs1.289.1091356807
datafile 9 switched to datafile copy
input datafile copy RECID=6 STAMP=1091356953 file name=+DATA/RACNEW/DATAFILE/undotbs2.285.1091356741
datafile 11 switched to datafile copy
input datafile copy RECID=7 STAMP=1091356954 file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/sysaux.291.1091356841
datafile 12 switched to datafile copy
input datafile copy RECID=8 STAMP=1091356954 file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/undotbs1.293.1091356841
datafile 13 switched to datafile copy
input datafile copy RECID=9 STAMP=1091356954 file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/undo_2.294.1091356841
datafile 14 switched to datafile copy
input datafile copy RECID=10 STAMP=1091356954 file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/users.296.1091356843
datafile 16 switched to datafile copy
input datafile copy RECID=11 STAMP=1091356954 file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/dboard.290.1091356841
datafile 17 switched to datafile copy
input datafile copy RECID=12 STAMP=1091356955 file name=+DATA/RACNEW/AA5834B17FFE6058E0534738A8C0829B/DATAFILE/test_ts.295.1091356841

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
   sql clone "alter pluggable database all open";
}
executing Memory Script

sql statement: alter pluggable database all open
Cannot remove created server parameter file
Finished Duplicate Db at 15-DEC-2021 10:44:06

Now RAC conversion is going on for this database

[oracle@srv1 dbs]$ mv initracnew.ora initracnew1.ora

[oracle@srv1 dbs]$vi initracnew1.ora -->Add the below cluster parameters
*.cluster_database=true
racnew2.thread=2
racnew1.thread=1
racnew2.undo_tablespace='UNDOTBS2'
racnew1.undo_tablespace='UNDOTBS1'
racnew2.instance_number=2
racnew1.instance_number=1

Add the instance number in the oratab file
racnew1:/u01/app/oracle/product/19.0.0/db_1:N

create the audit trail directory on node-2, wherever you want to start the second instance
mkdir -p /u01/app/oracle/admin/racnew/adump

[oracle@srv1 dbs]$
[oracle@srv1 dbs]$ . oraenv
ORACLE_SID = [racnew] ? racnew1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@srv1 dbs]$

[oracle@srv1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 15 11:28:19 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup pfile=initracnew1.ora
ORACLE instance started.

Total System Global Area  721419208 bytes
Fixed Size                  9141192 bytes
Variable Size             654311424 bytes
Database Buffers           50331648 bytes
Redo Buffers                7634944 bytes
Database mounted.
Database opened.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL>
SQL> create spfile='+DATA' from pfile='initracnew1.ora';

File created.

Note the location of SPFILE from ASM

ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   DEC 15 11:00:00  Y    spfile.300.1091360199
ASMCMD> pwd
+DATA/RACNEW/PARAMETERFILE

spfile='+DATA/RACNEW/PARAMETERFILE/spfile.300.1091360199'

Now add the database to cluster

srvctl add database -db racnew -oraclehome $ORACLE_HOME -dbtype RAC -spfile '+DATA/RACNEW/PARAMETERFILE/spfile.300.1091360199' -role PRIMARY -startoption OPEN -stopoption IMMEDIATE -dbname RACNEW -diskgroup DATA,FRA
srvctl add instance -db racnew -i racnew1 -n srv1.localdomain
srvctl add instance -db racnew -i racnew2 -n srv2.localdomain

Now stop the database running with pfile=initracnew1.ora from sql prompt and then start with SRVCTL

srvctl start database -d racnew

[oracle@srv1 dbs]$ srvctl status database -d racnew -v -f
Instance racnew1 is running on node srv1. Instance status: Open.
Instance racnew2 is running on node srv2. Instance status: Open.

at No comments:
Labels:
Subscribe to: Posts (Atom)

FIG project queries

##### Service add & LOad Baclancing on Add Service ####### srvctl add service -s wcccdmt.farmersinsurance.com -r wcccdmtx1,wcccdmtx2,wcc...