ora 01531 a database already open by the instance from Pastebin
SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 6 18:15:34 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01531: a database already open by the instance
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string
SQL> Create Pfile from SPfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> select name, value from v$parameter where name like 'utl_file_dir';
select name, value from v$parameter where name like 'utl_file_dir'
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
ERROR:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup mount
ORACLE instance started.
Total System Global Area 5117050880 bytes
Fixed Size 3056232 bytes
Variable Size 1040190872 bytes
Database Buffers 4060086272 bytes
Redo Buffers 13717504 bytes
Database mounted.
SQL> select name, value from v$parameter where name like 'utl_file_dir';
NAME
--------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
utl_file_dir
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='C:\app\Oracle12\product\12.1.0\dbhome_1\database\INItorcl.ORA';
ORACLE instance started.
Total System Global Area 5117050880 bytes
Fixed Size 3056232 bytes
Variable Size 1040190872 bytes
Database Buffers 4060086272 bytes
Redo Buffers 13717504 bytes
Database mounted.
Database opened.
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string C:\app\backup\logmnr
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL> Alter system set db_recovery_file_dest_size=20G;
System altered.
SQL> Alter system set db_recovery_file_dest='C:\app\backup';
System altered.
SQL> begin
2 dbms_logmnr_d.build(
3 dictionary_filename => 'dictionary.ora',
4 dictionary_location => 'C:\app\backup\logmnr',
5 options => dbms_logmnr_d.store_in_flat_file);
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> conn test/test@pdborcl
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
Warning: You are no longer connected to ORACLE.
SQL> conn test/test@pdborcl
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> conn sys@pdborcl as sysdba
Enter password:
Connected.
SQL> Create user test identified by test
2 Default tablespace users
3 Temporary tablespace temp
4 Quota unlimited on users;
Create user test identified by test
*
ERROR at line 1:
ORA-01109: database not open
SQL> Grant create session, create procedure to test;
Grant create session, create procedure to test
*
ERROR at line 1:
ORA-01109: database not open
SQL> Grant execute on list_libraries to test;
Grant execute on list_libraries to test
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter pluggable database pdborcl open read write;
Pluggable database altered.
SQL> conn test/test@pdborcl
Connected.
SQL> exec sys.list_libraries('NOUSER''||test.get_dba() --');
BEGIN sys.list_libraries('NOUSER''||test.get_dba() --'); END;
*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "TEST.GET_DBA", line 1
ORA-06512: at "SYS.LIST_LIBRARIES", line 9
ORA-06512: at line 1
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter system switch logfile;
System altered.
SQL> begin
2 dbms_logmnr.add_logfile( LogFileName =>’C:/app/backup/archive/ARC0000000240_0922032286.0001’,
3 options => DBMS_LOGMNR.NEW);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_logmnr.add_logfile( LogFileName =>
3 ' C:/app/backup/CDB1/archivelog/2016_10_31/ ARC0000000241_0922032286.0001',
4 options => DBMS_LOGMNR.ADDFILE);
5 end;
6 /
begin
*
ERROR at line 1:
ORA-01284: file C:/app/backup/CDB1/archivelog/2016_10_31/ ARC0000000241_0922032286.0001 cannot be opened
ORA-00308: cannot open archived log ' C:/app/backup/CDB1/archivelog/2016_10_31/ ARC0000000241_0922032286.0001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 123) The filename, directory name, or volume label syntax is incorrect.
ORA-06512: at "SYS.DBMS_LOGMNR", line 68
ORA-06512: at line 2
SQL> begin
2 dbms_logmnr.add_logfile( LogFileName =>
3 'C:/app/backup/archive/ARC0000000241_0922032286.0001',
4 options => DBMS_LOGMNR.ADDFILE);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_logmnr.add_logfile( LogFileName =>
3 'C:/app/backup/archive/ARC0000000242_0922032286.0001',
4 options => DBMS_LOGMNR.ADDFILE);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_redo_logs +
3 dbms_logmnr.ddl_dict_tracking +
4 dbms_logmnr.print_pretty_sql);
5 end;
6 /
begin
*
ERROR at line 1:
ORA-01371: Complete LogMiner dictionary not found
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 2
SQL> Execute dbms_logmnr.start_logmnr( -
> DICTFILENAME => 'C:\app\backup\logmnr\dictionary.ora');
PL/SQL procedure successfully completed.
SQL> Select username, operation, sql_redo, sql_undo from v$logmnr_contents where username =’TEST’;
no rows selected
SQL> Select username, operation, sql_redo, sql_undo from v$logmnr_contents where username =’UNKNOWN’;
no rows selected
SQL> SELECT
2 name
3 , TO_CHAR(first_time, 'DD-MON-YYYY HH24:MI:SS') first_time
4 FROM
5 v$archived_log
6 WHERE
7 name IS NOT NULL
8 ORDER BY
9 sequence#;
NAME FIRST_TIME
-------------------------------------------------- --------------------
C:\APP\BACKUP\ARCHIVE\ARC0000000213_0922032286.000 03-NOV-2016 15:41:59
1
C:\APP\BACKUP\ARCHIVE\ARC0000000214_0922032286.000 03-NOV-2016 16:46:55
1
C:\APP\BACKUP\ARCHIVE\ARC0000000215_0922032286.000 03-NOV-2016 16:52:16
1
C:\APP\BACKUP\ARCHIVE\ARC0000000216_0922032286.000 03-NOV-2016 17:04:33
1
NAME FIRST_TIME
-------------------------------------------------- --------------------
C:\APP\BACKUP\ARCHIVE\ARC0000000217_0922032286.000 03-NOV-2016 18:04:11
1
C:\APP\BACKUP\ARCHIVE\ARC0000000218_0922032286.000 03-NOV-2016 18:08:54
1
C:\APP\BACKUP\ARCHIVE\ARC0000000219_0922032286.000 03-NOV-2016 18:13:25
1
C:\APP\BACKUP\ARCHIVE\ARC0000000220_0922032286.000 03-NOV-2016 18:14:01
NAME FIRST_TIME
-------------------------------------------------- --------------------
1
C:\APP\BACKUP\ARCHIVE\ARC0000000221_0922032286.000 03-NOV-2016 18:14:46
1
C:\APP\BACKUP\ARCHIVE\ARC0000000222_0922032286.000 03-NOV-2016 18:16:06
1
C:\APP\BACKUP\ARCHIVE\ARC0000000223_0922032286.000 03-NOV-2016 18:21:04
1
NAME FIRST_TIME
-------------------------------------------------- --------------------
C:\APP\BACKUP\ARCHIVE\ARC0000000224_0922032286.000 03-NOV-2016 18:21:10
1
C:\APP\BACKUP\ARCHIVE\ARC0000000225_0922032286.000 03-NOV-2016 18:21:22
1
C:\APP\BACKUP\ARCHIVE\ARC0000000226_0922032286.000 03-NOV-2016 22:36:54
1
C:\APP\BACKUP\ARCHIVE\ARC0000000227_0922032286.000 03-NOV-2016 22:42:57
1
NAME FIRST_TIME
-------------------------------------------------- --------------------
C:\APP\BACKUP\ARCHIVE\ARC0000000228_0922032286.000 03-NOV-2016 23:00:51
1
C:\APP\BACKUP\ARCHIVE\ARC0000000229_0922032286.000 03-NOV-2016 23:41:58
1
C:\APP\BACKUP\ARCHIVE\ARC0000000230_0922032286.000 04-NOV-2016 14:09:59
1
C:\APP\BACKUP\ARCHIVE\ARC0000000231_0922032286.000 04-NOV-2016 21:42:25
NAME FIRST_TIME
-------------------------------------------------- --------------------
1
C:\APP\BACKUP\ARCHIVE\ARC0000000232_0922032286.000 04-NOV-2016 22:10:12
1
C:\APP\BACKUP\ARCHIVE\ARC0000000233_0922032286.000 04-NOV-2016 23:08:40
1
C:\APP\BACKUP\ARCHIVE\ARC0000000234_0922032286.000 05-NOV-2016 02:00:46
1
NAME FIRST_TIME
-------------------------------------------------- --------------------
C:\APP\BACKUP\ARCHIVE\ARC0000000235_0922032286.000 05-NOV-2016 11:22:27
1
C:\APP\BACKUP\ARCHIVE\ARC0000000236_0922032286.000 05-NOV-2016 11:27:36
1
C:\APP\BACKUP\ARCHIVE\ARC0000000237_0922032286.000 05-NOV-2016 12:55:34
1
C:\APP\BACKUP\ARCHIVE\ARC0000000238_0922032286.000 05-NOV-2016 20:00:23
1
NAME FIRST_TIME
-------------------------------------------------- --------------------
C:\APP\BACKUP\ARCHIVE\ARC0000000239_0922032286.000 05-NOV-2016 22:00:30
1
C:\APP\BACKUP\ARCHIVE\ARC0000000240_0922032286.000 06-NOV-2016 16:25:41
1
C:\APP\BACKUP\ARCHIVE\ARC0000000241_0922032286.000 06-NOV-2016 17:53:00
1
C:\APP\BACKUP\ARCHIVE\ARC0000000242_0922032286.000 06-NOV-2016 18:03:44
NAME FIRST_TIME
-------------------------------------------------- --------------------
1
30 rows selected.
SQL> COLUMN name FORMAT A66 HEAD "Log File Name"
SQL> COLUMN first_time FORMAT A23 HEAD "First Time"
SQL> COLUMN name FORMAT A66 HEAD "Log File Name"
SQL> COLUMN name FORMAT A66 HEAD "Log File Name"
SQL> SELECT
2 name
3 , TO_CHAR(first_time, 'DD-MON-YYYY HH24:MI:SS') first_time
4 FROM
5 v$archived_log
6 WHERE
7 name IS NOT NULL
8 ORDER BY
9 sequence#;
Log File Name First Time
------------------------------------------------------------------ -----------------------
C:\APP\BACKUP\ARCHIVE\ARC0000000213_0922032286.0001 03-NOV-2016 15:41:59
C:\APP\BACKUP\ARCHIVE\ARC0000000214_0922032286.0001 03-NOV-2016 16:46:55
C:\APP\BACKUP\ARCHIVE\ARC0000000215_0922032286.0001 03-NOV-2016 16:52:16
C:\APP\BACKUP\ARCHIVE\ARC0000000216_0922032286.0001 03-NOV-2016 17:04:33
C:\APP\BACKUP\ARCHIVE\ARC0000000217_0922032286.0001 03-NOV-2016 18:04:11
C:\APP\BACKUP\ARCHIVE\ARC0000000218_0922032286.0001 03-NOV-2016 18:08:54
C:\APP\BACKUP\ARCHIVE\ARC0000000219_0922032286.0001 03-NOV-2016 18:13:25
C:\APP\BACKUP\ARCHIVE\ARC0000000220_0922032286.0001 03-NOV-2016 18:14:01
C:\APP\BACKUP\ARCHIVE\ARC0000000221_0922032286.0001 03-NOV-2016 18:14:46
C:\APP\BACKUP\ARCHIVE\ARC0000000222_0922032286.0001 03-NOV-2016 18:16:06
C:\APP\BACKUP\ARCHIVE\ARC0000000223_0922032286.0001 03-NOV-2016 18:21:04
Log File Name First Time
------------------------------------------------------------------ -----------------------
C:\APP\BACKUP\ARCHIVE\ARC0000000224_0922032286.0001 03-NOV-2016 18:21:10
C:\APP\BACKUP\ARCHIVE\ARC0000000225_0922032286.0001 03-NOV-2016 18:21:22
C:\APP\BACKUP\ARCHIVE\ARC0000000226_0922032286.0001 03-NOV-2016 22:36:54
C:\APP\BACKUP\ARCHIVE\ARC0000000227_0922032286.0001 03-NOV-2016 22:42:57
C:\APP\BACKUP\ARCHIVE\ARC0000000228_0922032286.0001 03-NOV-2016 23:00:51
C:\APP\BACKUP\ARCHIVE\ARC0000000229_0922032286.0001 03-NOV-2016 23:41:58
C:\APP\BACKUP\ARCHIVE\ARC0000000230_0922032286.0001 04-NOV-2016 14:09:59
C:\APP\BACKUP\ARCHIVE\ARC0000000231_0922032286.0001 04-NOV-2016 21:42:25
C:\APP\BACKUP\ARCHIVE\ARC0000000232_0922032286.0001 04-NOV-2016 22:10:12
C:\APP\BACKUP\ARCHIVE\ARC0000000233_0922032286.0001 04-NOV-2016 23:08:40
C:\APP\BACKUP\ARCHIVE\ARC0000000234_0922032286.0001 05-NOV-2016 02:00:46
Log File Name First Time
------------------------------------------------------------------ -----------------------
C:\APP\BACKUP\ARCHIVE\ARC0000000235_0922032286.0001 05-NOV-2016 11:22:27
C:\APP\BACKUP\ARCHIVE\ARC0000000236_0922032286.0001 05-NOV-2016 11:27:36
C:\APP\BACKUP\ARCHIVE\ARC0000000237_0922032286.0001 05-NOV-2016 12:55:34
C:\APP\BACKUP\ARCHIVE\ARC0000000238_0922032286.0001 05-NOV-2016 20:00:23
C:\APP\BACKUP\ARCHIVE\ARC0000000239_0922032286.0001 05-NOV-2016 22:00:30
C:\APP\BACKUP\ARCHIVE\ARC0000000240_0922032286.0001 06-NOV-2016 16:25:41
C:\APP\BACKUP\ARCHIVE\ARC0000000241_0922032286.0001 06-NOV-2016 17:53:00
C:\APP\BACKUP\ARCHIVE\ARC0000000242_0922032286.0001 06-NOV-2016 18:03:44
30 rows selected.
SQL> BEGIN
2 DBMS_LOGMNR.START_LOGMNR (
3 DictFileName => 'C:\app\backup\logmnr\dictionary.ora'
4 );
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> COLUMN username FORMAT A8
SQL> COLUMN operation FORMAT A9
SQL> COLUMN sql_redo FORMAT A25 WORD_WRAPPED
SQL> COLUMN sql_undo FORMAT A25 WORD_WRAPPED
SQL> COLUMN timestamp FORMAT A20
SQL> Select username, operation, sql_redo, sql_undo from v$logmnr_contents where username =’TEST’;
no rows selected
SQL> create tablespace rcatbs
2 datafile 'C:\app\oracle\oradata\orcl\pdbor\rcat01.dbf' size 25M
3 reuse;
create tablespace rcatbs
*
ERROR at line 1:
ORA-01543: tablespace 'RCATBS' already exists
SQL> create tablespace rcatbs2
2 datafile 'C:\app\oracle\oradata\orcl\pdbor\rcat01.dbf' size 25M
3 reuse;
create tablespace rcatbs2
*
ERROR at line 1:
ORA-01119: error in creating database file 'C:\app\oracle\oradata\orcl\pdbor\rcat01.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
SQL> create tablespace rcatbs2
2 datafile 'C:\app\Oracle12\oradata\orcl\pdborcl\rcat01.dbf' size 25M
3 reuse;
Tablespace created.
SQL> CREATE USER rcatowner2 IDENTIFIED BY rcat2
2 DEFAULT TABLESPACE rcatbs2
3 QUOTA UNLIMITED ON rcatbs2;
CREATE USER rcatowner2 IDENTIFIED BY rcat2
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> drop user rcatowner;
drop user rcatowner
*
ERROR at line 1:
ORA-01918: user 'RCATOWNER' does not exist
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> CREATE USER rcatowner2 IDENTIFIED BY rcat2
2 DEFAULT TABLESPACE rcatbs2
3 QUOTA UNLIMITED ON rcatbs2;
CREATE USER rcatowner2 IDENTIFIED BY rcat2
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> conn sys@pdborcl as sysdba
Enter password:
Connected.
SQL> CREATE USER rcatowner2 IDENTIFIED BY rcat2
2 DEFAULT TABLESPACE rcatbs2
3 QUOTA UNLIMITED ON rcatbs2;
CREATE USER rcatowner2 IDENTIFIED BY rcat2
*
ERROR at line 1:
ORA-00959: tablespace 'RCATBS2' does not exist
SQL> create tablespace rcatbs2
2 datafile 'C:\app\Oracle12\oradata\orcl\pdborcl\rcat01.dbf' size 25M
3 reuse;
create tablespace rcatbs2
*
ERROR at line 1:
ORA-01537: cannot add file 'C:\app\Oracle12\oradata\orcl\pdborcl\rcat01.dbf' - file already part of database
SQL> conn sys as sysdba;
Enter password:
Connected.
SQL> drop rcatbs2;
drop rcatbs2
*
ERROR at line 1:
ORA-00950: invalid DROP option
SQL> drop tablespace rcatbs2;
Tablespace dropped.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
BEGIN DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); END;
*
ERROR at line 1:
ORA-01292: no log file has been specified for the current LogMiner session
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1
SQL> begin
2 dbms_logmnr.add_logfile( LogFileName =>’C:/app/backup/archive/ARC0000000240_0922032286.0001’,
3 options => DBMS_LOGMNR.NEW);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_logmnr.add_logfile( LogFileName =>
3 'C:/app/backup/archive/ARC0000000241_0922032286.0001',
4 options => DBMS_LOGMNR.ADDFILE);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> begin
2 dbms_logmnr.add_logfile( LogFileName =>
3 'C:/app/backup/archive/ARC0000000242_0922032286.0001',
4 options => DBMS_LOGMNR.ADDFILE);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
BEGIN DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); END;
*
ERROR at line 1:
ORA-16331: container "SALES" is not open
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1
SQL> alter pluggable database sales open read write;
Pluggable database altered.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
SQL> Select username, operation, sql_redo, sql_undo from v$logmnr_contents where username =’TEST’;
USERNAME OPERATION SQL_REDO SQL_UNDO
-------- --------- ------------------------- -------------------------
TEST START set transaction read
write;
TEST UPDATE update "SYS"."USER$" set update "SYS"."USER$" set
"SPARE6" = "SPARE6" =
TO_DATE('07-NOV-16', TO_DATE('03-NOV-16',
'DD-MON-RR') where 'DD-MON-RR') where
"USER#" = '130' and "USER#" = '130' and
"SPARE6" = "SPARE6" =
TO_DATE('03-NOV-16', TO_DATE('07-NOV-16',
'DD-MON-RR') and ROWID = 'DD-MON-RR') and ROWID =
USERNAME OPERATION SQL_REDO SQL_UNDO
-------- --------- ------------------------- -------------------------
'AAAAAKAABAAAADXAAO'; 'AAAAAKAABAAAADXAAO';
TEST COMMIT commit;
SQL>