Posted in 2017

Snapshot Standby Sandbox

SQL@orcl_stby> alter database recover managed standby database cancel ;

Database altered.

SQL@orcl_stby> alter database convert to snapshot standby ;

Database altered.

SQL@orcl_stby> alter database open ;

Database altered.

SQL@orcl_stby> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/db1/orcl/system01.dbf
/u02/app/oracle/db1/orcl/sysaux01.dbf
/u02/app/oracle/db1/orcl/undotbs01.dbf
/u02/app/oracle/db1/orcl/users01.dbf

SQL@orcl_stby> create tablespace TEST datafile '/u02/app/oracle/db1/orcl/test.dbf' size 100m ;

Tablespace created.

SQL@orcl_stby> alter user psorger default tablespace TEST quota unlimited on test ;

User altered.

SQL@orcl_stby> connect psorger/psorger
Connected.
SQL@orcl_stby> create table TEST (ID number PRIMARY KEY, text clob) ;

Table created.

SQL@orcl_stby> insert into test select rownum, dbms_random.string('c',6000) from dual connect by level<=1000 ;

1000 rows created.

SQL@orcl_stby> commit ;

Commit complete.

SQL@orcl_stby> select index_name from user_indexes ;    

INDEX_NAME
------------------------------
SYS_C004727
SYS_IL0000070440C00002$$

SQL@orcl_stby> alter index SYS_C004727 rename to PK_ID_TEST ;

Index altered.

SQL@orcl_stby> select index_name from user_indexes ;

INDEX_NAME
------------------------------
SYS_IL0000070440C00002$$
PK_ID_TEST

SQL@orcl_stby> delete from test where rownum<=500 ;

500 rows deleted.

SQL@orcl_stby> commit ;

Commit complete.

SQL@orcl_stby> connect / as sysdba
Connected.
SQL@orcl_stby> create table psorger.test_interim as select * from psorger.test where rownum<=0 
  2  ;

Table created.

SQL@orcl_stby> BEGIN
  2  dbms_redefinition.can_redef_table('psorger','test',DBMS_REDEFINITION.CONS_USE_PK);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL@orcl_stby> 
SQL@orcl_stby> BEGIN
  2  DBMS_REDEFINITION.START_REDEF_TABLE('psorger','test','test_interim',dbms_redefinition.cons_use_pk);
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1498
ORA-06512: at line 2


SQL@orcl_stby> desc test_interim
ERROR:
ORA-04043: object test_interim does not exist


SQL@orcl_stby> desc psorger.test_interim
 Name								   Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ID									    NUMBER
 TEXT									    CLOB

SQL@orcl_stby> desc psorger.test       
 Name								   Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ID								   NOT NULL NUMBER
 TEXT									    CLOB

SQL@orcl_stby> drop table psorger.test_interim ;

Table dropped.

SQL@orcl_stby> create table psorger.test_interim (id number primary key, text clob) ;

Table created.

SQL@orcl_stby> BEGIN
  2  DBMS_REDEFINITION.START_REDEF_TABLE('psorger','test','test_interim',dbms_redefinition.cons_use_pk);
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1498
ORA-06512: at line 2

SQL@orcl_stby> begin
  2  dbms_redefinition.start_redef_table(uname=>'PSORGER',orig_table=>'TEST',int_table=>'TEST_INTERIM',options_flag=>dbms_redefinition.cons_use_pk) ;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL@orcl_stby> insert into psorger.test values (1,'KIK') ;

1 row created.

SQL@orcl_stby> commit ;

Commit complete.

SQL@orcl_stby> select count(*) from psorger.test ;

  COUNT(*)
----------
       501

1 row selected.

SQL@orcl_stby> select count(*) from psorger.test_interim ;

  COUNT(*)
----------
       500

1 row selected.

SQL@orcl_stby> DECLARE
  2  errors pls_integer ;
  3  BEGIN
  4  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('PSORGER','TEST','TEST_INTERIM',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, errors) ;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL@orcl_stby> select object_name, base_table_name, ddl_txt from
         DBA_REDEFINITION_ERRORS;  2   

OBJECT_NAME		       BASE_TABLE_NAME
------------------------------ ------------------------------
DDL_TXT
--------------------------------------------------------------------------------
PK_ID_TEST		       TEST
CREATE UNIQUE INDEX "PSORGER"."TMP$$_PK_ID_TEST0" ON "PSORGER"."TEST_INTERIM" ("

SYS_C004727		       TEST
ALTER TABLE "PSORGER"."TEST_INTERIM" ADD CONSTRAINT "TMP$$_SYS_C0047270" PRIMARY


2 rows selected.

SQL@orcl_stby> desc psorger.test_interim
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 ID						       NOT NULL NUMBER
 TEXT								CLOB

SQL@orcl_stby> select index_name from dba_indexes where owner='PSORGER';

INDEX_NAME
------------------------------
PK_ID_TEST
SYS_IL0000070440C00002$$
I_MLOG$_TEST
SYS_C004728
SYS_IL0000070447C00002$$

5 rows selected.

SQL@orcl_stby> exec dbms_redefinition.sync_interim_table('PSORGER','TEST','TEST_INTERIM') 

PL/SQL procedure successfully completed.

SQL@orcl_stby> select count(*) from PSORGER.TEST ;

  COUNT(*)
----------
       501

1 row selected.

SQL@orcl_stby> select count(*) from PSORGER.TEST_interim ;

  COUNT(*)
----------
       501

1 row selected.

SQL@orcl_stby> exec dbms_redefinition.finish_redef_table('PSORGER','TEST','TEST_INTERIM')

PL/SQL procedure successfully completed.

SQL@orcl_stby> select index_name from dba_indexes where owner='PSORGER';

INDEX_NAME
------------------------------
PK_ID_TEST
SYS_IL0000070440C00002$$
SYS_C004728
SYS_IL0000070447C00002$$

4 rows selected.

SQL@orcl_stby> select segment_name, bytes/1024/1024 mb from dba_segments where owner='PSORGER';

SEGMENT_NAME										  MB
--------------------------------------------------------------------------------- ----------
SYS_LOB0000070447C00002$$								   5
SYS_LOB0000070440C00002$$								   9
SYS_C004728									       .0625
SYS_IL0000070447C00002$$							       .0625
PK_ID_TEST									       .0625
SYS_IL0000070440C00002$$								.125
TEST										       .0625
TEST_INTERIM										.125

8 rows selected.


SQL@orcl_stby> drop table psorger.TEST_INTERIM purge ;

Table dropped.

SQL@orcl_stby> select index_name from dba_indexes where owner='PSORGER';

INDEX_NAME
------------------------------
SYS_C004728
SYS_IL0000070447C00002$$

2 rows selected.

SQL@orcl_stby>

SQL@orcl_stby> SELECT 
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;  2    3    4  

PERCENT_FULL
------------
	 5.8

	 
SQL@orcl_stby> select * from v$recovery_file_dest ;

NAME
------------------------------------------------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/u02/app/oracle/arch1/recovery
 3221225472  186630144		       0	       5

SQL@orcl_stby> !ls -l /u02/app/oracle/arch1/recovery/ORCL/flashback
total 153616
-rw-r-----. 1 oracle dba 78651392 Apr 29 12:28 o1_mf_bn1dh6tq_.flb
-rw-r-----. 1 oracle dba 78651392 Apr 29 12:26 o1_mf_bn1dh9qs_.flb

SQL@orcl_stby>

SQL@orcl_stby> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL@orcl_stby> startup mount
ORACLE instance started.

Total System Global Area  367439872 bytes
Fixed Size		    2253344 bytes
Variable Size		  117444064 bytes
Database Buffers	  243269632 bytes
Redo Buffers		    4472832 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode


SQL@orcl_stby> !
Classic layout determined.

+++   MSH - Version 2014.06   +++

cFollowing aliases were set:
cdscr  - change to scriptdir
cdscrl  - change to local scriptdir
cdlog  - change to logdir
oram   - set environment for ORACLE_SID
         interactive or with the ORACLE_SID as parameter
cdoh   - change to ORACLE_HOME of current DB
cdnet  - change to directory ../network/admin of current DB
cdXX   - change to dedicated directories, XX stands for:
         bd(bdump),ud(udump),aud(audit_file_dest),dbs(ORAHOME/dbs)
cddat  - change to parent datafile directory of current DB
cdarc  - change to archive directory of current DB
talert - continous view on alert-Log of current DB
valert - open alert-Log of current DB (view)
vinit  - edit init.ora/spfile.ora of current DB
s+     - command: sqlplus '/ as sysdba' (inkl. Status)
         remote connection via SQL*NET: s+ <ORACLE_SID>
syspw  - set environment variable SYSPW
vicron - editing crontab, with backupfile
do_sql   - SQL Shellfunc / as sysdba, do_sql "select...;" pipe-able
amon     - Oracle Performance Tool AMON
s++      - MySQLPLUS - Comfortable SQL-Plus
rman++   - Comfortable rman
dgmgrl++ - Comfortable dgmgrl
asmcmd++ - Comfortable ascmd
adrci++  - Comfortable adrci
dCurrent ORACLE_SID is orcl_stby.
oracle@test$ cddbs
current directory: /u01/app/oracle/product/11.2.0.4/dbs
oracle@test$ rm lkORCL # lebo mam standby s tym istym dbnamemom na servri a vytvara mi vzdy ten isty lock file - workaround
oracle@test$ exit
exit

SQL@orcl_stby> alter database mount standby database ;
alter database mount standby database
*
ERROR at line 1:
ORA-01665: control file is not a standby control file


SQL@orcl_stby> alter database mount ;

Database altered.

SQL@orcl_stby> alter database convert to physical standby ;

Database altered.

SQL@orcl_stby> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL@orcl_stby> startup mount
ORACLE instance started.

Total System Global Area  367439872 bytes
Fixed Size		    2253344 bytes
Variable Size		  117444064 bytes
Database Buffers	  243269632 bytes
Redo Buffers		    4472832 bytes
Database mounted.
SQL@orcl_stby> alter database recover managed standby database using current logfile disconnect ;

Database altered.

oracle@test$ oram
oram runs with /etc/oratab
SID-SETUP
Choose SID:
NR   SID      RUNNING  # COMMENT
 1 - orcl_stby <*>      
 2 - orcl     <*>      

Enter number or SID (type 'end' to exit):
ORACLE_SID =  ? 1
ORACLE_SID:  orcl_stby
ORACLE_HOME: /u01/app/oracle/product/11.2.0.4
oracle@test$ oram 2
oram runs with /etc/oratab
Temporary init.ora /tmp/pfileorcl_oracle.tmp created
ORACLE_SID:  orcl
ORACLE_HOME: /u01/app/oracle/product/11.2.0.4
oracle@test$ s+
SQL@orcl> select table_name from dba_tables where owner='PSORGER';

no rows selected

SQL@orcl> 

Advertisements

Author:

Database administrator who loves to work with Oracle software. (Sometimes not)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s